The tutorial explains how to effectively use AutoCorrect in Excel and how to stop it completely or only disable for specific words.
Excel AutoCorrect is designed to correct misspelled words automatically as you type, but in fact it is more than just correction. You can use this feature to change abbreviations to full text or replace short codes with longer phrases. It can even insert check marks, bullet points and other special symbols on the fly without you having to access anything. This tutorial will teach you how to do all this and more.
To have more control over how Excel performs autocorrection in your worksheets, open the AutoCorrect dialog:
The AutoCorrect dialog will show up and you can switch between the 4 tabs to enable or disable specific corrections.
On this tab, you can view the list of typical typos, misspellings and symbols that AutoCorrect uses by default. You can change and delete any of the existing entries as well as add your own ones. Additionally, you can turn options on or off the following options.
The first option controls the autocorrect logo (lightning bolt) that appears after each automatic correction:
Please note that the autocorrect button does not appear in Excel anyway, clearing this box prevents the lightning bolt from appearing in Word and some other applications.
The next 4 options control the automatic correction of capitalization:
The last option enables or disables all automatic corrections:
Tips and notes:
On this tab, you can disable the following options, which are enabled in Excel by default:
By default, additional actions are disabled. To turn them on, select the Enable additional actions in the right-click menu box, and then select the action you want to enable in the list.
For Microsoft Excel, only the Date (XML) action is available, which opens your Outlook calendar on a given date:
To trigger the action, right-click a date in a cell, point to Additional Cell Actions, and click Show my Calendar:
This tab controls the automatic insertion of special symbols in Excel equations (Insert tab > Symbols group > Equation):
Please note that the math conversions only work in equations, but not in cells. However, there is a macro that allows using Math AutoCorrect outside math regions.
It may sound strange, but AutoCorrect in Excel is not always a benefit. For example, you may want to insert a product code like "1-ANC", but it is automatically changed to "1-CAN" each time because Excel believes you've misspelled the word "can".
To prevent all automatic changes made by AutoCorrect, simply turn it off:
In many situations, you may not want to stop autocorrect in Excel completely, but disable it for particular words. For example, you can keep Excel from changing (c) to the copyright symbol ©.
To stop auto-correcting a specific word, this is what you need to do:
The screenshot below shows how to turn off the autocorrect of (c):
Instead of deleting, you can replace (c) with (c). For this, type (c) into the With box, and click Replace.
If you decide to return autocorrect (c) to copyright in the future, all you'll have to do is open the AutoCorrect dialog and put © in the With box again.
In a similar manner, you can turn off autocorrect for other words and characters, for example, prevent changing (R) to ®.
Tip. If you have difficulties with finding the entry of interest in the auto-correct list, type the word in the Replace box and Excel will highlight the corresponding entry.
Sometimes, you may need to prevent autocorrect of a specific entry just one time. In Microsoft Word, you'd simply press Ctrl + Z to undo the change. In Excel, this deletes the entire cell value instead of reverting the correction. Is there a way to undo AutoCorrect in Excel? Yep, here's how you can do this:
For instance, to undo the autocorrect of (c) to copyright, type (c) and then type a space. Excel performs the auto-correction, and you immediately press Ctrl + Z to have (c) back:
In some situations, you may want to extend the standard list of misspellings used by Excel AutoCorrect. As an example, let see how we can force Excel to replace the initials (JS) with the full name (John Smith) automatically.
In this example, we are adding an entry that will automatically replace "js" or "JS" with "John Smith":
If you'd like to change some entry, select it in the list, type the new text in the With box, and click the Replace button:
To delete an AutoCorrect entry (predefined or your own one), select it in the list, and click Delete.
Note. Excel shares the AutoCorrect list with some other Office applications such as Word and PowerPoint. So, any new entries that you have added in Excel will also work in other Office applications.
The screenshot below shows how you can create an auto-correct entry to insert a bullet point in Excel automatically:
And now, whenever you type bullet1 in a cell, it will immediately be replaced with a bullet point:
Tip. Be sure to use some unique word to name your entry. If you use a common word, you'd often need to revert auto corrections not only in Excel, but in other Office applications.
That's how you use, adjust and stop AutoCorrect in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents