0

I'd like to create a publishable data table in Excel. Numbers may range from 0.010 to 1,000. I need to have similar significant figures and aligned decimal points. This is what I am looking for as display:

0.131 -> 0.1310

1.3 -> 1.300


130 -> 130.0


13.1 -> 13.10


1300 -> 1,300

For the 1300, I got a decimal to not be on the end (e.g. 1,300.) using the below strategy. But this does not align the over 1000 with the other decimal point areas.

    0.1582

1.582




15.82


158.2


1,582

This is what I have used: Format cell to be 0.????. Then also used Conditional Formatting Rule of:

Cell Value greater than or equal to 1000 Format -> Custom -> ###,###

It also needs to keep sig figs at 4, which it is set at whatever number you type in to begin with. I would like this to be automatic.

Any help here would be greatly appreciated.

Destroy666
  • 12,350
Jeff Kaufman
  • 3

2 Answers2

1

You need to overlay two techniques. First, using >value ranges in your number formatting, and then (because you need more than three formats), a conditional format rule to apply a second format with similar approach. For the main number format, use this:

[>9.999]00.00_0_0;[>0.999]0.000_0;0.0000

This translates as:

For numbers greater than 9.999, use a format which always has two digits after the decimal point, then pad with spaces for the "missing" zeroes; then for numbers >0.999 (but less than 10, because of the order of precedence from left to right), apply a similar thing one place to the right; "otherwise" show four decimal places with a leading zero before the decimal point.

These ranges effectively replace the default breakdown of a number format into positive, negative and zero sections.

Now apply a conditional format rule to the data range, configured for cells with values "greater than or equal to" 100 and apply this number format:

[>999.999]0,000_._0_0_0_0;###.0_0_0_0

Note just two sections here: >999.999 and "otherwise", because we already know this only applies to numbers >=100

If your numbers are actually much more precise (ie have lots more decimals) then you might need more 9's after the decimal in the formats to avoid issues, eg use >999.999999

AdamV
  • 6,396
0

To format a number in Excel to align decimals but also optionally remove the decimal point based on the size of the number, you can use a custom number format.

Here's how to do it:

Select the range of cells you want to format.

Right-click and select "Format Cells".

In the "Format Cells" dialog box, select "Number" under the "Number" tab.

Under "Category", select "Custom".

In the "Type" box, enter the following custom number format:

0.00,"K";[>=1000]0.00,,"M";[>=1000000]0.00,,,"B";

This number format will display two decimal places for numbers less than 1000, but remove the decimal point for numbers greater than 1000 and add a letter to indicate thousand, million, or billion.

Click OK to apply the custom number format to the selected range of cells.

I hope this helps! You can also check out this link for more information

naravas karim
  • 1