Insert 2 Decimals

May 22, 2018 - by Bill Jelen

A question from my Atlanta live Power Excel seminar: Is there a way to type 12345 and have Excel enter 123.45? I call this Adding Machine mode, because it feels like the 1970's era adding machine with the switch to automatically add two decimal places.

To turn on the setting, go to File, Options, Advanced. The default setting is to insert 2 decimal places:

After changing the setting, you type 12345 and Excel enters 123.45:

Excel automatically inserts 2 decimal places.

But the Excel switch is far more versatile than Mrs. Hergenrother's 1972 adding machine. You can enter anywhere from 300 to -300 decimal places. I ran into a guy once who had to measure tolerances. He would change this setting to automatically insert six decimal places. In his case, 123 would become 0.000123.

The number of digits works just like the ROUND function in Excel. Specifying a negative number of digits allows you to quickly enter numbers in thousands. Specify -3 for the number of digits. Type 123 and you will get 123,000.

Excel will convert you entry to thousands

The follow-up question from Atlanta: Is there a way to make this happen in a certain range? No - the option is global. If you turn it on, it will apply to all cells in all workbooks.

You could add the following three macros to your Personal Macro Workbook and then assign them to buttons on your Quick Access Toolbar:

Sub NoDecimalPlaces()
    Application.FixedDecimal = False
End Sub

Sub AddTwoDecimalPlaces()
    Application.FixedDecimal = True
    Application.FixedDecimalPlaces = 2
End Sub

Sub MultiplyByThousand()
    Application.FixedDecimal = True
    Application.FixedDecimalPlaces = -3
End Sub

Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2207: Insert 2 Decimals.

I got this question in Atlanta, and I love this question because I remember the very first time-- I was probably six or seven-- my parents went over to their friends' house, and their friends owned the local gas station in town, right? And so, in the home office in their house was this amazing machine-- the first time I'd ever seen anything like it. It was an old adding machine, right? With the tape? And I remember there was a switch there, this amazing switch, that when you clicked that switch, it would automatically insert a decimal point. So, if you typed 12345, it would enter 123.45. Alright? And so I was in Atlanta, and someone asks me this question, and says, "Hey, how can we do that? How can we type 12345 and have it change to 123.45?" And I knew there was a setting in Excel to do this.

It's under Excel Options; Advanced; second choice: "Automatically insert a decimal point." So, File; Options; go to Advanced-- Advanced is, usually, you have to scroll all the way down… there's a hundred miles of stuff here; I'm just going to "Automatically insert a decimal point"; leave it at 2 places; click OK; and then I type 12345, and BAM! -- it's 123.45.

I ran into a guy up at the VFW in Ohio who used this for a different thing. [File; Options...] He was always entering something with 6 decimal places-- some sort of measurement. So, 4, 5, 6-- like this-- and he would type 123456, and it would become a decimal. Let's increase the number of decimal places there, so we can see it. And then if it was a measurement, like where it was not all the digits, 1234, it would insert extra spaces. Alright, so, really, really cool thing.

Now, the question that came up in Atlanta, though, is, "Can we make this happen in just one range?" And, no. This is a global thing. Once you change that setting, it's going to happen in all cells, all sheets, until you go change it back. So I guess the answer is: Just use it while you have to key a bunch of numbers in, and then, when you're done keying numbers, then you come in and turn it off, like that. Still-- a cool, cool way to go.

Well, hey, check out my new book, MrExcel LIVe, The 54 Greatest Tips of All Time. Click that "I" in the top right-hand corner for more information.

Woah-- hang on a second-- hold the phone-- did you see that tooltip? File; Options; Advanced; and, when I happened to hover there, they said, "Choose a number between -300 and 300." Which seems to indicate that we can "Automatically insert a decimal point," and if we go to -3, that will mean that I can enter numbers-- if it's 123,000, I can just enter 123, and they should enter it as 123,000-- like that, how cool is that? Alright, it goes both ways, just like the ROUND function does. You can specify 2 for 2 digits after the decimal place, or -2 to round to the nearest 100. So, even better like that.

Alright, question from the Atlanta Power Excel Seminar-- Is there a way to have Excel always shift my numeric entries so the last few digits appear after the decimal place? Yes. It's File; Options; Advanced; second choice-- "Automatically insert a decimal point;" choose that setting and specify the number of digits. To convert all entries to the 1000s, you can put a negative number of digits-- so 123 becomes 123,000. Put 2 digits and 12345 becomes 123.45. One hassle-- you can't do this to just a range. It's global. It's going to work in all cells, all sheets, all workbooks. So turn it on when you need to do some data entry, and then turn it off.

To download the workbook from today's video, in case you want to work along, visit the URL in the YouTube description.

Well, hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.

Download Excel File

To download the excel file: insert-2-decimals.xlsx

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Don’t count your datasets before they’ve been sent over"

Jordan Goldmeier

Title Photo: Rawpixel on Unsplash