How to Dynamically Remove Scientific Notation in Excel 2016

So you have a workbook with text, integers, decimals, currency and an assortment of data. Not all layouts will place data of one type conveniently into a single column or row.

Then, you get this:

This is scientific notation. Excel automatically formats integers longer than 12 characters like this to reduce the overall characters present in a cell.

And you can't turn it off.

Which pretty much leaves you with the long and arduous task of manually reformatting potentially hundreds of individual cells as "number" to remove the notation. This has been scientifically proven to lead to heavy drinking and short but potent bursts of uncontrollable crying.

But wait! Put down that bottle! We can fix this. And here's how:

Excel 2016 has a neat little feature, called Conditional Formatting. This allows the application to hunt down cells that match a set of criteria, and apply formatting to them. This could be text size, colour or other visual style. But it can also set the cell content format to one of your choice.

1) Highlight the cells affected (or the whole sheet if required)

2) Select the "Conditional Formatting" drop-down from the Styles block on the menu bar and click "New Rule..."

3) Select "Format only cells that contain" as a Rule Type. Then set the rule description to: Format only cells with: Cell Value | greater than | 100000000000

Then click on "Format"

4) In the Format menu, click the "Number" tab, and select "Number" from the category list.

You will probably want to set the decimal places to 0.

 

5) Once you click OK, your rule should look something like the image below. Click "OK" to confirm the rule creation.

 

6) Done! You can probably do a little dance now to celebrate.

 

Conditional formatting can be used for a variety of purposes, but this is one example of how it can help turn long and arduous tasks into a a few simple clicks.

Remember, always check a subset of your data to make sure your rule is performing as expected.

For more help, advice or consultancy, contact Binary Blue. Experts in Data.

Tagged under