Feature Request: Automatic Significant figures.
Problem: When I put a set of data in excel, lets say a an X and Y column each with 30 rows, and I do several calculations and calculations with the results of those calculations I then have to go through and check each cell for the proper number of sig figs( many times they are not the same with in a row or column).
Solution: I want a automatic sig fig feature that can be applied to the cells which displays the proper number of sig figs.
13 comments

Michael commented
That would be awesome though

Mike commented
I've not seen anything in over two years, despite multiple threads and a comprehensive solution framework (see below).
I would also like to see the outlined options dynamically enabled. By this I mean:
round A1 to match the significant digits of cell A2, preferably without having to resort to some LEN formula.Something like SIGFIG(A1, A2) or SIGFIG(2017, A2)

LS commented
Has this been done, yet? It needs to be a cell format choice. I deal with laboratory results (so many lines) and literally have to make sure that the significant figures are correct for every affected cell. The laboratories are generating spreadsheets with the cell values formatted to text, but as the client I can't manipulate and also have the resulting cells report in the same significant figures.
Also the labs can do this? so surely the path exists?

Mike commented
There are three ways to handle this, as a formula, as a number format category and as a custom number format. I think that all three are necessary and easy.
The formula would work just like ROUND but truncate to number of significant digits rather than just number of digits. Notice the trailing zeros, they are important.
SIGFIG(2017, 2) = 2000
SIGFIG(2.017, 2) = 2.0
SIGFIG(0.2017, 2) = 0.20The number format would operate like the Number, Currency, Accounting, and Percentage categories, with a simple selector to determine the number of digits.
The custom number format should look something like: “ !! “ to round to 2 significant figures and “ !!!!! “ to round to 5 significant figures. It would not matter if the number was an integer or a decimal.
2017 with “ !! “ = 2000
2017 with “ !!!!! “ = 2017.0
2.017 with “ !! “ = 2.0The formula should absolutely truncate the resulting number such that future calculations only use the digits displayed. Whether the number formats truly truncate or just alter what is displayed (while leaving the original value alone) I will leave to brighter minds.
I would also like to see the outlined options dynamically enabled. By this I mean:
round A1 to match the significant digits of cell A2, preferably without having to resort to some LEN formula.Something like SIGFIG(A1, A2) or SIGFIG(2017, A2)

Anonymous commented
Excel, PowerPoint, and Word need to have a format with
a) specified number of significant digits, and
b) decimal point align for maximum readability,such as (this works poorly for proportionalwidth fonts)
3.14E4
0.00314
0.0314
0.314
3.14
31.4
314.
3.14E4 
Phillip Johnson commented
similar to these two

Phillip Johnson commented
@DoctorMoshe: scientific notation with a user set number of digits displayed does achieve the same result as the significant figure format for cell display, however scientific notation is too cumbersome for some purposes and some audiences (nontechnical readers)

Phillip Johnson commented
I agree that a feature like this has been missing for way too long, although I do not understand the request to make it automatic. Normally calculations should be carried out using all available significant figures (i.e. all 15 digits available in Excel), and then only the final result for display purposes is rounded to an appropriate number of significant digits. Note that it is important that the display be rounded, while the cell value not be rounded since it may be used in subsequent calculations. I would be satisfied if the number of significant figures to display was determined by the user; but amazed if more functionality was added to trace all precedence inputs to a cell result and automatically round to the appropriate number of sigfigs (assuming that all cells with a number value rather than a formula have a format set to identify the number of significant digits).
Please add (1) capability to format the display of cell values to a user specified number of significant figures (also known as significant digits), and (2) functions that round, rounddown, roundup to a number of significant figures as an input argument. For reference reading see the following web page which includes links to published papers and more detailed explanation: http://www.angelfire.com/oh/cmulliss/. I am tired of implementing hacks like this: https://www.vertex42.com/ExcelTips/significantfigures.html.

Brian Johnson commented
A full implementation should allow a workbook, worksheet, or range of cells to be set into a "significant figures" mode where the rules of significant figure usage are automatically applied to all relevant mathematical and logical operations contained in that range. The user should never be forced to apply the rules manually to choose the significant figures to be displayed, as those rules get complex for multiplestep operations like a rootmeansquare calculation.

DoctorMoshe commented
Isn't that what scientific notation is all about? and should be used?
If you really prefer to view it in nonscientific notation you could add a column with the formula:
=VALUE(TEXT(A1,"0.00E+00"))
Where A1 would refer to the original data and the number of 0's in 0.00 gives you the number of significant digits 
Kenneth Barber commented
It'll be interesting to see how Microsoft handles this. You are basically describing dynamic formatting, which currently can only be done using conditional formatting, but specifying the significant digit rules would be too difficult.

Anonymous commented
Similarly, please have excel automatically keep trailing zeroes that are typed in (i.e. they are significant), and add functionality to restore trailing zeroes in pasted data. This would of course be overriden if I have already defined how many decimal places to show.

Andrew Griffiths commented
This is a great idea. We're currently working with data that ranges from 0.0029654 to 151432. Being able to specify # significant figures would be very helpful, as would controlling the # of decimal places according to the # of significant figures.
With 3 significant figures in the 1st scenario the values displayed would be 0.00297 and 151,000. In the 2nd scenario, the values would be 0.00297 and 151,432.