MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Insert 2 Decimals

May 22, 2018 - by Bill Jelen

Insert 2 Decimals

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:

Insert 2 decimal places
Insert 2 decimal places

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

Excel automatically inserts 2 decimal places.
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.

continue reading »

More Reading

Replace Spaces With Empty

Replace Spaces With Empty »

May 18, 2018 - by Bill Jelen

Frank from New Jersey downloads data every day where the blank cells actually contain one or more spaces. This is frustrating because =ISBLANK() won't recognize these cells as empty.

Loan Survey Explosion

Loan Survey Explosion »

May 17, 2018 - by Bill Jelen

Today's question from Quentin who was at my Atlanta Power Excel seminar. Quentin has to generate the same 7 survey questions for each of 1000+ customers in Excel.

read more news »

Featured Products

Power Pivot and Power BI

Power Pivot and Power BI »

Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft’s tightly-related pair of revolutionary analytical tools – tools that are fundamentally changing the way organizations work with data.

Excel Video Medley

Excel Video Medley »

Excel Video Medley covers everything from Pivot Tables to VLOOKUP to VBA. Learn how to create scientific graphs. Use Excel for statistics.

100 Excel Simulations

100 Excel Simulations »

Covering a variety of Excel simulations, from gambling to genetics, this introduction is for people interested in modeling future events, without the cost of an expensive textbook.

MrExcel XL =ROMAN(40)

MrExcel XL =ROMAN(40) »

Fun Excel book in full color. Includes Bill's favorite 40 Excel tips, plus Excel jokes, Excel Tweets, and Excel cocktails. Bonus section includes 40 keyboard shortcuts and more.

VBA & Macros for Microsoft Excel 2016

VBA & Macros for Microsoft Excel 2016 »

Use this guide to automate virtually any routine task: save yourself hours, days, maybe even weeks! Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful.

Pivot Table Data Crunching: Microsoft Excel 2016

Pivot Table Data Crunching: Microsoft Excel 2016 »

Use Excel 2016 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours… understand exactly what’s going on in your business… take control, and stay in control! Even if you’ve never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power.

see more products »