Microsoft Excel: How to turn off Excel's AutoFormat feature

By J. Carlton Collins, CPA

Q. Is it possible to disable the AutoFormat feature in Excel?

A. A few options exist for controlling some of ­Excel's AutoFormat behavior from the File tab by selecting Options, Proofing, AutoCorrect options, AutoFormat As You Type (as pictured below).

techqa-6


However, these adjustment options do not completely disable Excel's automatic application of number formats as data are entered. This is because, as a default, Excel worksheets are globally formatted using the General format, which automatically adopts the number format you use to initially enter numbers into a cell. As examples, if you type $45.55 into a cell with General formatting, the cell automatically becomes formatted as currency with two decimal places, or if you enter 37.1%, the cell becomes formatted as a percentage with one decimal place. Once a cell adopts the new number format, the General number format no longer applies, and the cell will keep the newly applied number format even if numbers with differing formats are subsequently entered. Usually, CPAs tend to appreciate the General format because it can save time, but sometimes this adoptive format behavior can be counterproductive—for example, when such format changes aren't desired.

While there isn't a specific option for disabling the General format's auto-formatting functionality, you can effectively disable it with a simple trick: Change your workbook's default number format to something other than the General number format. To do this, select the entire workbook as follows: Select all sheet tabs by right-clicking a worksheet tab and clicking the Select All Sheets option, and then clicking the leftmost top corner of one of the worksheets. Next, apply a different number format (such as the Accounting format with zero decimal places and no dollar sign symbols) from the Home tab's Number group. Thereafter, the auto-formatting functionality is effectively disabled.

You could then take matters one step further by saving a blank copy of this altered workbook (with this global format change) as your default workbook. This is done by saving the workbook as an Excel Template named Book.xltx in the XLStart folder. Thereafter, each new Excel workbook you create will no longer automatically apply the auto-formatting functionality.

Important caveat: Because additional worksheets created in those new workbooks (created using the New Sheet command) will contain General formatting by default, you should consider adding any new worksheets by duplicating an existing worksheet that already bears the non-general format (using the Ctrl+Drag Tab method described in the April 2011 Technology Q&A topic "Creating a New Worksheet Can Be a Drag"). This approach will ensure that any newly added worksheets don't contain General number formatting.


About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.