If we had a dime for every time AutoCorrect saved us from first and secondhand embarrassment, we’d be minted. We’d also be equally rich for every dime when AutoCorrect was over-efficient and replaced our words with words uncalled for. Show
Anyway, today we’re going into how AutoCorrect works, how to make it stop working, how to alter the way it works, how to make it work our way, and how to reverse its work. Reading that, you’d think this tutorial is about “work” but it’s about the Excel feature called AutoCorrect. At its most fundamental level, AutoCorrect (or replace-as-you-type) is known to us all as a correction tool that edits spelling errors, typos, and incorrect grammar. Let’s see a small demonstration here. In Excel, if you were to type “should of been”: It will automatically be corrected to “should have been”: This is the doing of AutoCorrect but how does it know what to correct? AutoCorrect works based on the entries made in the AutoCorrect settings where, let’s say, text2 replaces text1. That means we can change, add and delete AutoCorrect entries. Recommended Reading: How to Spell Check in Excel
Uses of AutoCorrect
Note: The AutoCorrect feature applies to all Microsoft Office applications. Excel, get your correcting pen out! AutoCorrect OptionsLet’s have a look at the prime AutoCorrect options. In the steps below we highlight the path that leads to the AutoCorrect options:
From this window, you will have access to all the AutoCorrect options in Excel. In this section, we will explore the tabs in the AutoCorrect window. Let’s start with the first one. AutoCorrect Options TabThis is the main tab with all the key functions of autocorrecting. Have a look at the items in the AutoCorrect tab below and we’ll explain the function of each one: Correct Two Initial Capitals If the first two letters of a word are entered in uppercase, checking this item will correct it so that only the initial letter is capital. For example, note the first two letters in the first instance: If visual learning is your thing, this example shows the effect of using the AutoCorrect items in Excel. More descriptions of the items are below. Capitalize First Letter of Sentences With this item checked, the first letter of sentences after a sentence-closing punctuation mark (period, question mark, exclamation mark) will be corrected to an uppercase letter even if it is typed in lowercase. Capitalize Names of Days If the name of a day has been typed without the initial letter being capital, checking this item will automatically make the initial a capital letter. Correct Accidental Use of CAPS-LOCK Key When typing a word with the initial as a capital letter, you might hit the Caps Lock key for the initial and then turn it off for the rest of the word. If you end up doing the opposite, your word will end up looking like tHIS. Keeping this item checked will correct the letter case, swapping the initial as a capital and the remaining letters in the word will take on lowercase letters. Also, the Caps Lock key will automatically be turned off. This change applies once the space bar or the Enter key is pressed. Replace text as you type While the other 4 items above are capitalization corrections, this one is about word replacement. Checking this item replaces a typed text string with another text string or symbol as per the entries in the AutoCorrect tab. E.g. according to a preset entry in Excel, if you type ™, the trademark symbol ™ will be returned (bear in mind that this is not case sensitive). AutoCorrect Exceptions TabThe Exceptions settings pertain to the first two items in the AutoCorrect tab; Correct Two Initial Capitals and Capitalize First Letter of Sentences. If these two items are checked, you can use the Exceptions tab to feed your own exceptions (other than the ones already fed) so that AutoCorrect doesn’t correct them. To access the Exceptions tab, click on the Exceptions button in the AutoCorrect tab. The two types of entries the Exceptions settings apply to are mentioned below. First Letter Since the first letter of a sentence proceeding a sentence-closing punctuation mark will be capitalized, by that rule, AutoCorrect will be capitalizing the initials of the words after the period at the end of abbreviations. Excel already has a list of the text strings (mainly you will find abbreviations here) for which the initial of the next word will not be autocorrected: Note that whatever entry you make, it will be added with a trailing period so this setting is majorly for abbreviation type words. This would also be helpful e.g., if you have a brand name that ends in a period. Let’s say the brand name is “Dot.”. You will need to make an entry in the Don’t capitalize after section and click on the Add button. When Dot. is used as part of a phrase or sentence, the next word's initial will not be capitalized. Initial Caps While AutoCorrect will rectify two initial capital letters, this tab in the Exceptions settings can be used to excuse the entered words. By default, there is only one entry in this tab which is apt since AutoCorrect does not correct plurals of most abbreviations and acronyms anyway. If there is such a word (with the first two letters in uppercase and the rest in lowercase) that you'd rather AutoCorrect wouldn’t correct the initial two capitals of, make an entry in this tab. You may want to add words that have mixed case letters like RCed. Autoformat As You Type TabThe second tab in the AutoCorrect window is the Autoformat As You Type tab. Excel automatically formats certain actions e.g. if Excel picks up that you are trying to type a date, it will autoformat it to a date with forward slashes even if you have used other separators. This autoformatting is a part of AutoCorrect. Internet and Network Paths with Hyperlinks With this option enabled, when an internet or network path (e.g. URL) is entered in Excel, it is autoformatted to a hyperlink. It may be convenient to disable this option in the case that the created hyperlinks are accidentally clicked on. Include New Rows and Columns in Table Typing in a cell in a row or column adjacent to a table will include that row or column in the table. Basically, the adjoining row or column with the target cell is autoformatted as a part of the table. Unchecking this option will stop Excel from automatically extending the table to include the new adjacent cell. Fill Formulas in Tables to Create Calculated Columns Working in an Excel Table, when a formula is entered in any cell of a new column, the formula is copied to the entire column of the Table to create a calculated column. To prevent this action and keep the formula only to the target cell, uncheck this option. Actions TabIn the Actions tab, there is currently only one feature available to Excel and that is the date feature. By default, this feature is disabled. To enable this additional action, check the Enable additional actions in the right-click menu checkbox in the Actions tab. Marking this checkbox also gives access to select the Date action and adds date options in the right-click menu. If you are curious about the More Actions button, it only leads to the Microsoft website and confirms that there are no further actions available for the document. Now to apply the action, click on the OK button in the AutoCorrect and Excel Options windows. When a date will be entered in the worksheet and right-clicked, the context menu will have an added option of Additional Cell Actions. Further in this option, you’ll have Schedule a meeting and Show my Calendar both of which redirect you to your Outlook calendar to complete the actions. The third item is Options which goes back to the Actions tab in the AutoCorrect window. Math AutoCorrect TabAnd finally the Math AutoCorrect tab. The tab enlists the shortcuts you can use to add mathematical characters and symbols. Using the Add and Delete buttons, you can make, edit or delete entries. To disable Math AutoCorrect, uncheck the Replace text as you type box which you will find is checked by default. If you’ve headed to your Excel file to test entering mathematical symbols, have been unsuccessful, and are starting to doubt your command on Excel, let us tell you there’s nothing to worry about there. These symbols cannot be used in cells, only in equations. To enter an equation, go to the Insert tab > Symbols group > Equations icon. This will insert a text box to enter mathematical equations that will accept Math AutoCorrect to replace text with mathematical symbols and characters. So let’s test an entry from Math AutoCorrect. We’ll give entering the theta symbol a go. According to the entries in the Math AutoCorrect tab, to enter the theta symbol, we need to use the code theta: Now let’s add a space character to see if it works. Testing 1, 2, 3…… It works! Note: Be mindful of keeping the equation in the color fill of the text box otherwise Excel won’t count the text as an equation and the math autocorrecting won’t work and you’ll start questioning your Excel abilities again. How to Disable or Enable AutoCorrectAs per Excel’s default settings, AutoCorrect will be enabled and you may choose to change this. Apply the steps below to disable/reenable AutoCorrect in Excel.
Now AutoCorrect will be disabled for Excel which is not limited to the opened file but applied to the whole application. To enable AutoCorrect again, use the same path to check all the 5 checkboxes and reactivate AutoCorrect for your Excel files. How to Add, Delete and Edit Entries in AutoCorrectWhile AutoCorrect is a pretty cool feature and can help one save face in many situations, it is not all-inclusive and can do with additions that are best suited to your work. E.g. it would be helpful to add certain symbols and proper nouns that make part of your regular Excel usage to ensure that the pronoun’s initial is capitalized without fail every time or so that there’s quick-keying to a symbol at the ready. See how to add entries to AutoCorrect in the steps below:
Now, to have the infinity symbol on our sheet, we will enter the relevant code: Once the code is in place and another character is entered or Enter is pressed, the code will be AutoCorrected to the set text string or symbol. In our case, the code (inf) will be replaced by the infinity symbol. Notes: While you’re setting up the code, make sure it is unique and isn’t used for anything else in other MS Office applications as the AutoCorrect settings apply to all Office apps. If you need a source to copy the intended symbol of character from, you can find it on the internet or use the Excel’s Symbol feature from the Insert tab > Symbols group > Symbol icon. This path will open a Symbol window where you can choose from an array of various symbols and characters. Editing and Deleting EntriesTo edit an entry, select the entry you want to change and edit the details in the Replace and/or With text box. And then select the Add or Replace button, whichever is enabled. For deleting entries, read the next section. Note: The AutoCorrect list is pretty impressive and if you feel your entry of choice is that little bit harder to find, you can start typing it in the Replace text box which will work as a quick search bar and pinpoint the entry for you. How to Disable AutoCorrect for Certain WordsAutoCorrect can be disabled for some words. Well, to tell you the truth, there’s no on and off button for only some words and not the others. It’s more like removing AutoCorrect for some words. The way to do that is to delete the entries from the AutoCorrect options. To reactivate AutoCorrect for those words, new entries will have to be made again in the AutoCorrect options. For now, let’s see how to disable AutoCorrect for an entry:
AutoCorrect will be disabled for the deleted entry. In the same way, you can select whichever entry you want to delete. However, if you want to remove multiple entries, it has to be done one by one. How to Undo AutoCorrect ChangesThis one’s simple. We all know that undoing anything is synonymous with Ctrl + Z. There are two aspects of undoing AutoCorrect changes. One is to enter the code, allow it to be autocorrected by pressing the Enter key, and then press the Ctrl + Z keys to undo the action. What this will do is erase the contents of the cell and disable AutoCorrect temporarily for that particular cell. This is considering that nothing is entered in the cell other than the AutoCorrect content. You don’t have to exit the cell to undo AutoCorrect and have a small window to undo the changes after you have typed the code. The steps for this are shown ahead:
After pressing Ctrl + Z, the changes will be reverted and the cell contents will be (inf) and a space character. You reckon you’ve seen everything about the feature today? Correct! That was AutoCorrect turned inside out for you and we hope you picked up on how you can utilize the feature for your work at hand. It might have been a discovery for you that AutoCorrect can operate as not only a text correcting tool but also for entering symbols and other characters. Let’s leave you to tinker with this discovery while we prepare to entrap you in another Excel discovery. |