Support formatting or rounding to userspecified number of significant figures
I work in a research organization and it's common for us to need to display values to a prespecified number of significant figures. If you search the internet for "Excel significant figures" you will see a variety of hacks which work to varying degrees. It would be great to have built in support to display to a userspecified number of sig figs. E.g. 1234, displayed to 2 sigfigs, is 1200. Alternately, a built in function which rounds to a specified number of sigfigs would also get the job done.
11 comments

Madelaine Montilla commented
I agree that this feature has been missing for far too long. Not only is it important to be able to show significant figures easily, but there should be a way for formulas to calculate the significant figures of a CALCULATED field. Could you please consider adding this?

Talis commented
Microsoft is a rentiership machine. They have no interest in improving their products. They just change their GUI and produce huge dislocations for their user base and often regress in usability. They don't know that users want functions and real features, and not GUIs.
Bill Gates is too busy fighting Covid or Malaria or trying to fix the world. If he can't even fix his own products, how can he fix the world? Microsoft Word is an abomination that should be terminated  but it totally dominates the market due to Microsoft monopoly. The only good thing about Microsoft is Excel.

Anonymous commented
Years later, and still no format for this. I don't think this would be a complicated thing to add to excel.

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)

Thomas commented
Rounding and formatting are really two different things. Excel should have both options: (1) a "Significant Figures" cell format and (2) a "RoundSignificant(value, numFigures)" function.
The "Significant Figures" cell format needs two settings: (1) the desired number of significant figures to display and (2) what I might call an 'exponential threshold', which is the number of characters at which you switch to exponential notation. I try to avoid exponential notation (especially the format with an "E" instead of 'times ten to a power') because it is harder to read; however, for very large or very small numbers, I prefer to give up and switch to exponential display.
I have struggled with this for 20 years and written functions in VBA and C# to work around this, please add support for significant figures. Microsoft, if you want help, please contact me.

Jan Karel Pieterse commented
@S.Johnson: It is possible to create a custom validation rule that handles situations like this nicely without a problem.
For instance, this rule prohibits the user from entering anything than is not a multiple of 1000: =INT(A1/1000)*1000=A1

Phillip Johnson commented
similar to these two

Phillip Johnson commented
I agree that this feature has been missing for way too long. 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.

AsbestosJen commented
Would love to have this, either as a formula function or as a box. Macros are a nono in my organization, so UDFs are out of the question. All of the workarounds I've found by google search have the issue of truncated trailing (significant) zeroes. E.g. 0.00296333 to 2 sig figs should be 0.0030, but most of the formulas I've tried result in 0.003. My results can range from 1e6 to 1e6, so nested ifs with mround get too big for older versions of excel.
JoeU's suggestion returned 0.00: no different from using the "show decimals" formatting option.

JoeU commented
Errata.... TEXT(A1,"0."&REPT("0",B11)&"E+0") rounds to B1 significant digits.

JoeU commented
Sorry for the late comment.... The builtin function to do this is TEXT with a format of the form "0.0***0E+0", where "***" is metasyntax for the ellipsis. For example, TEXT(A1,"0E+0") rounds to 1 significant digit, and TEXT(A1,"0.00000000000000E+0") rounds to 15 significant digits (14 zeros after the decimal point). More generally, TEXT(A1,"0."&REPT("0",B1)&"E+0") rounds to B1 significant zeros. The double negate ("") converts text to numeric. Arguably, users might prefer a new builtin that is easier to use, e.g. ROUND.SIG. That can even be expanded to included ROUNDUP.SIG and ROUNDDOWN.SIG.