What Is AutoCorrect Feature In MS Excel?
The feature AutoCorrect in Excel fixes misspelled words, typos, and capitalization mistakes. And it helps replace short texts with long text strings and formulas automatically. The feature works based on an editable list of common misspellings and symbols to autocorrect.
Users can use the Excel AutoCorrect feature to show the full forms of abbreviations and enter lengthy and complex formulas quickly in a worksheet
For example, the table below shows the required data in column A.
And we must enter the given data in column B cells, B3:B5.
Consider that we enter the data in column B cells, with misspellings and capitalization errors. And we might not know how to enter the specified symbol.
Then, as we enable AutoCorrect in Excel by default, the program will auto-fix the issues when we enter the data with any of the above mentioned issues and press Enter.
In the above example, cell B3 data correction represents a spelling AutoCorrect in Excel scenario. We misspelled the word Achieve while typing it in cell B3. However, once we press Enter, Excel corrects the spelling automatically.
It is because Excel contains the phrase “Acheive” in its existing autocorrection words list in the Excel Options – Proofing – AutoCorrect Options – AutoCorrect window. And it specifies replacing the phrase with “Achieve” automatically.
Further, the AutoCorrect tab includes an option to capitalize the names of days. And hence, when we enter Monday in lowercase in cell B4 and press Enter, Excel capitalizes the word automatically.
Next, we must enter the copywrite symbol. In such scenarios, we can check the default words list to confirm the text to enter to obtain the required symbol.
Excel lists the phrase ‘(c)’ in its autocorrection words list and specifies replacing it with the copywrite symbol, ©. So, when we enter the character “c” within brackets and press Enter, Excel replaces it with ©.
Please note if we enter any data in a cell with issues like those mentioned above. And then, we press the Space Bar to continue typing data in the cell. Then, Excel auto-fixes the issue the moment we press the Space Bar.
So, the AutoCorrect feature helps one to focus more on data analysis and manipulation instead of correcting petty mistakes manually.
Table of contents
Key Takeaways
- The AutoCorrect in Excel feature rectifies and replaces misspelled words with correct spellings, phrases, and symbols and corrects capitalization errors automatically. It uses the default, but editable words and exception lists, containing phrases to consider and ignore during autocorrection.
- Users can use the Excel AutoCorrect feature to enter short text strings, which will get replaced with the required long text strings and formulas.
- We can open the AutoCorrect window from the Proofing tab in the Excel Options window, which we can open using the File tab. Otherwise, pressing the keyboard shortcut Alt + T + A will open the AutoCorrect window.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How To Select AutoCorrect Option In Excel?
The steps to select or enable AutoCorrect in Excel are:
- First, click on the File tab in the active worksheet and then, select Options to access the Excel Options window.
- Next, choose the Proofing option from the left menu in the Excel Options window to open the Proofing tab.
- Then, click the AutoCorrect Options button to access the AutoCorrect Window.
[Alternatively, we can press the AutoCorrect in Excel shortcut, Alt + T + A, to open the AutoCorrect window]
Now, the AutoCorrect tab in the AutoCorrect window will show the default autocorrection settings with all the options chosen.
- Finally, let the required autocorrection settings in the AutoCorrect tab in the AutoCorrect window.
Other Important Options
Meanwhile, we can add the symbols and words we often misspell while using them in Excel, and remove unwanted ones to and from the words list using the Add, Replace and Delete options.
For adding a phrase to replace automatically, enter the phrase to correct in the Replace field and type in the phrase that must appear after autocorrection in the With field. And press Add.
Further, if we want to change an entry in the default words list, we must click the specific row. The Replace and With fields will show the chosen phrase and its replacement, which we can edit and press Replace.
And to remove a phrase to avoid its autocorrection, we must select the row containing the phrase and its autocorrected version in the list. And click Delete to remove the entry from the list.
Furthermore, we can uncheck all the options in the AutoCorrect tab to avoid all possible autocorrections.
But, if the requirement is to only turn off automatic text replacements or spelling AutoCorrect in Excel. Then, in that case, we can uncheck the option, Replace text as you type, box in the AutoCorrect tab.
Also, we can click the Exceptions option to access the AutoCorrect Exceptions window, where we can set the required autocorrection exceptions for capitalization.
The exception lists are customizable. W can add and remove the phrases to and from the lists in the two tabs in the AutoCorrect Exceptions window using the Add and Delete options.
Next, click OK to close the AutoCorrect Exceptions window.
And then click OK to confirm the updates and close the AutoCorrect window.
Finally, clicking OK in the Excel Options window will close it. And the chosen autocorrection settings will get applied to the sheet.
AutoCorrect Options Tab
The AutoCorrect Options tab shows the default autocorrection settings that resolve the typical issues while entering data into cells. And it is here from where we can customize and, if required, turn off AutoCorrect in Excel to suit our requirements.
- Show AutoCorrect Options buttons: This option helps display or hide the AutoCorrect icon (lightning bolt) in MS Word and other Microsoft applications. And checking or unchecking this option does not affect MS Excel, as the program does not show the AutoCorrect icon.
- Correct TWo INitial CApitals: This option changes the second capital letter in a phrase into lowercase.
- Capitalize first letter of sentences: This option ensures the first letter after a period, exclamation or question mark ( ‘.’, ‘!’, ‘?’) is in uppercase.
- Capitalize names of days: This option changes the first letter of the day name into uppercase when entered in lowercase.
- Correct accidental use of CAPS LOCK key: Consider the Caps Lock is on, and we start typing a word. Then, except for the first character, the remaining letters in the word will be in uppercase.
In such a case, this option will correct the word’s case when we press Enter or the Space Bar if we continue typing in the cell. So, the first letter will be in uppercase and the remaining in lowercase, negating the effect of the Caps Lock. - Replace text as you type: This option helps enable and turn off AutoCorrect in Excel. The program lists the common words and symbols, typing which users tend to make typo errors. And it contains the replacement symbols and words that must appear after autocorrection.
And the list is editable. We can append and remove phrases to and from the list to suit our spelling autocorrection requirements.
AutoCorrect Exceptions
Next, the AutoCorrect Options tab also contains the Exceptions option to prevent Excel from auto-fixing specific data.
And clicking the option opens the AutoCorrect Exceptions window. It contains two tabs, First Letter and Initial CAps.
- First Letter: This tab includes an editable exception list of phrases containing a period in the end. And the list ensures Excel does not capitalize the letter entered after the period in the listed phrases into a cell.
- INitial CAps: This tab includes an editable exception list of phrases with their first two letters in uppercase. And the list ensures Excel does not autocorrect the first two letters’ cases in the listed phrases when entered into a cell.
Examples
Check out the following illustrations to use the AutoCorrect feature in Excel effectively.
Example #1
We shall see an example for each autocorrection option in the AutoCorrect Options tab.
The table below lists the autocorrection options in column C. And column D shows the text without enabling the corresponding autocorrect option.
The requirement is to enable the AutoCorrect feature to show the text values correctly when entered in column E, according to the specified autocorrection options. Then, the steps are as follows:
Step 1: Follow the path File – Options to open the Excel Options window.
Step 2: Choose Proofing in the Excel Options window to open the Proofing tab.
And click the AutoCorrect Options button to access the AutoCorrect window.
[Alternatively, we can use the AutoCorrect in Excel shortcut, Alt + T + A, to access the AutoCorrect window]
The above image shows all the autocorrection options unchecked, except the first one, in the AutoCorrect tab. So, we must enable them to proceed with the autocorrection steps.
Step 3: Check the boxes against all the options in the AutoCorrect tab to select them.
And click OK in the AutoCorrect window.
Step 4: Select cell E3 and enter the text as in the image below.
Next, press Enter to view the text with the second character in lowercase, corrected automatically.
Step 5: Choose cell E4 and type the cell D4 text till the period (‘.’). Next, enter the word “it” in lowercase.
Next, press the Space Bar to continue typing the remaining text. And we will see the first letter of the word, “it”, after the period in uppercase.
And then, we can complete the sentence and press Enter.
Step 6: Choose cell E5 and enter the text as shown below.
And pressing Enter will capitalize the first letter in the word as it is a day name.
Step 7: Select cell E6 and assume the Caps Lock is on. And we start typing. While the first letter is in lowercase, the remaining letters are in uppercase.
But, once we enter the first word and press the Space Bar to continue typing the remaining text, Excel will correct the text case as the Caps Lock is no longer on.
And then, we can continue typing the remaining text and press Enter.
Step 8: Select cell E7 and enter the text as shown below.
And press Enter to view the required word with the spelling autocorrected.
Example #2
The following table contains the descriptions to display texts in column D cells, D3:D5.
We can set the required autocorrection and exception settings to achieve the desired output.
Step 1: Press Alt + T + A to access the AutoCorrect window.
And then, enter the phrase “NYSE” in the Replace field in the AutoCorrect tab.
Next, enter the phrase “New York Stock Exchange” in the With field, and click Add.
And click OK to apply the specified AutoCorrect setting in the active sheet.
Step 2: Choose cell D3 and enter “NYSE”.
And when we press Enter, the phrase “NYSE” gets replaced by the words “New York Stock Exchange” in the target cell, according to the specified AutoCorrect setting.
Step 3: The next text requires us to enter the phrase “(e)”, which, typically, with the default AutoCorrect setting, gets replaced with the Euro symbol (€).
Thus, we must prevent Excel from autocorrecting the term “(e)”. And for that, we must remove the phrase from the default wordslist in the AutoCorrect tab.
So, use Alt + T + A keys to open the AutoCorrect window, and click the row containing the phrase “(e)” and its replacement phrase in the default wordslist in the AutoCorrect tab.
And then press Delete.
Next, click OK in the AutoCorrect window.
Step 4: Choose cell D4 and enter the text as shown below.
Even after entering “(e)” and pressing the Space Bar, we see that the Euro symbol does not replace the phrase.
Next, we can complete entering the text and press Enter.
Step 5: We must ensure the text after “exp.” should remain in lowercase in cell D5. So, we must add the phrase “exp.” in the autocorrection exceptions list.
And for that, use Alt + T + A keys to open the AutoCorrect window à click the Exceptions in the AutoCorrect tab.
The AutoCorrect Exceptions window will open.
Next, enter the phrase “exp.” in the field specified in the First Letter tab. And click Add to add the term to the exceptions list.
Next, click OK.
And click OK in the AutoCorrect window.
Step 6: Choose cell D5 and enter the text as shown below.
We find that after entering the phrase “exp.” and pressing the Space Bar, the next entered letter is in lowercase. Thus, the final table will be as depicted below once we finish entering the remaining text.
Usage
The uses of the Excel AutoCorrect feature are:
- Text Values Corrections: The feature helps correct and replace texts automatically. But the default words list in the AutoCorrect tab in the AutoCorrect window must include the specific texts and their replacements.
- Short Texts Replacement with Long Texts: The feature enables one to enter a short text such as an acronym. And the result will be the short text being replaced with the long text specified in the default words list in the AutoCorrect tab in the AutoCorrect window.
- Short Texts Replacement with Long Formulas: The feature allows one to enter a short text. And the result will be the short text being replaced with the long formula specified in the existing words list in the AutoCorrect tab in the AutoCorrect window.
- Add New Characters and Symbols: The default words list is editable. So, we can add new phrases and their replacements in the list to add and use new characters and symbols in spreadsheets.
- Manage Text Capitalization Settings: The feature helps show the specified text with the correct letters capitalized automatically.
Drawbacks
The drawbacks of the Excel AutoCorrect feature are:
- The feature does not autocorrect all misspelled texts in a worksheet. It only auto-fixes those misspelled words, which are included in the default words list in the AutoCorrect tab in the AutoCorrect window.
- The feature does not show the letter after every abbreviation in lowercase. It only considers those abbreviations listed in the AutoCorrect Exceptions window.
Important Things To Note
- AutoCorrect in Excel works according to the default words list, containing the phrases and symbols to replace and correct.
- Ensure to unselect the Replace text as you type option in the AutoCorrect tab in the AutoCorrect window to turn off automatic spelling corrections and replacements.
- Edit the default autocorrect words list and exceptions list using the Add, Replace, and Delete buttons in the corresponding dialog boxes.
Frequently Asked Questions
We can change AutoCorrect in Excel using AutoCorrect Options in the Proofing tab in the Excel Options window.
For example, the table below contains the abbreviation of a company name, ABC, in cells C2:C3.
And, if we must enter the abbreviation of the company name in cell D2 to display its complete name, ABC Inc.
Then, the steps are:
Step 1: Select File – Options – Proofing – AutoCorrect Options or press Alt + T + A to open the AutoCorrect window.
And enter “ ABC” in the Replace field in the AutoCorrect tab in the AutoCorrect window.
Next, enter the phrase to replace the company name abbreviation in the With field. And press Add.
And then press OK in the AutoCorrect window.
Step 2: Choose cell D2 and enter the phrase “ABC”.
And when we press Enter, we see the phrase “ABC” replaced with “ABC Inc.”, as specified in the AutoCorrect tab.
Next, assume we must change the set AutoCorrect setting for “ABC” to “ABC Corp.” and display the new company name in cell D3. Then, the process is as follows:
Step 3: Press Alt + T + A to open the AutoCorrect window.
Next, enter “ABC” in the Replace field in the AutoCorrect tab in the AutoCorrect window. Excel will show the corresponding entry in the existing list.
And then, update the With field with the new company name, “ABC Corp.”, and click Replace in the AutoCorrect tab.
Next, Excel will ask us to confirm the change. Click Yes.
And then click OK in the AutoCorrect window.
Step 4: Choose cell D3 and enter the phrase “ABC”.
Next, press Enter to view the abbreviation of the company name being replaced with the new company name, ABC Corp.
AutoCorrect may not work because of the following reasons:
• The AutoCorrect feature is turned off in the AutoCorrect tab in the AutoCorrect window.
• The texts and symbols we want to replace or auto-fix are not included in the default words list in the AutoCorrect tab in the AutoCorrect window.
• The data we wish to autocorrect is in the exception lists in the AutoCorrect Exceptions window.
The difference between AutoText and AutoCorrect in Excel is that AutoText is useful for managing massive text fragments. And it auto-completes text as we type in MS Word.
But AutoCorrect works well for replacing and correcting shorter text strings in Excel and texts we aim to correct automatically and use in other MS applications.
Download Template
This article must be helpful to understand the AutoCorrect In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to AutoCorrect In Excel. Here we explain how to enable and use Autocorrect features along with examples & turn off/stop. You can learn more from the following articles –
Leave a Reply