Exploring AutoCorrect Feature in Excel

Let us do one small activity before we start. Select any of the cells and write (c) i.e. the character c within parenthesis and press Enter key. What did you notice? The excel automatically converts this into a copyright symbol ©. Similar is the case with (r) i.e. r within parenthesis. Excel converts it into a registered symbol ®. Likewise, if you type the word ‘ahve’ in the cell and press Enter, you would notice that excel converts it into the correct word ‘have’. What is the reason for this automatic correction of misspelled words? This is what we are going to unlock in this blog. The automatic correction of the misspelled words is done by the Autocorrect feature in Excel.

However, you would be surprised to know that it is just not limited to correcting misspelled words, but you can do many exciting things using the autocorrect feature in excel. To name just a few, enabling autocorrect in Excel would help you to write a short form, and excel automatically convert it into a long-form.

As we proceed with this blog, we would learn to enable autocorrect in Excel. We would also see how one can stop or turn off the autocorrect in Excel, undo autocorrect, and many more things.

So, let us now begin and explore this feature to its fullest.

What and Where is AutoCorrect in Excel

Autocorrect feature in Excel is an amazing feature provided by Microsoft in all its office utilities which automatically corrects the misspelled or wrong words at the time when you are typing.

To access the AutoCorrect feature, follow the below path:

‘File’ tab > ‘Options’ button > ‘Proofing’ section > ‘AutoCorrect Options …’ button.

An alternative way to access this is by using the Keyboard Shortcut keys – ‘Alt’, followed by ‘F’ and then ‘T’. This would open the dialog box – ‘Excel Options’.

Excel Options AutoCorrect Options

As soon as you click on the ‘AutoCorrect Options …’ button, the ‘AutoCorrect: English (United States)’ dialog box would pop out on your screen.

AutoCorrect Dialog Box

There are four tabs in this dialog box – AutoCorrect, AutoFormat As You Type, Actions, and Math AutoCorrect. We would go through each of these in detail in the upcoming sections of this blog.

AutoCorrect Feature in Excel

Understanding ‘AutoCorrect’ Tab Options

This is the tab that actually controls the automatic correction of the misspelled words.

AutoCorrect Dialog Box

The first checkbox Show AutoCorrect Options Button controls the display of the autocorrect logo in the application

The next four checkboxes control the case (upper/lower) of the words.

  1. Correct TWo INitial CApitals – If this option is ticked, excel automatically converts the second letter of the word into the lower case if the first two letters of a word are in upper case and rest are in lower case. (Eg: UNited STates > United States).
  2. Capitalize First Letter of Sentence – If the first letter of a sentence (after a full stop) is in lower case, then excel would itself convert the first letter of the first word in the sentence to upper case.
  3. Capitalize Name of Days – When you type a day of the week with the first letter in lower case, excel would convert it into upper case. (Eg: Today is friday > Today is Friday)
  4. Correct Accidental Use of cAPS LOCK key – This option would check for the words with the first letter in lower case, and rest being capitalized and correct those in the proper case (Eg: eXCELUNLOCKED > Excelunlocked)

You can also see one push-button named Exception. This enables you to add an exception to certain words. This would prevent excel to capitalize the first letter after this word. Likewise, when your sentence contains the word abbr. the excel would not capitalize the first letter of the word which is written after abbr.

The next checkbox Replace Text As You Type enables and disables the autocorrect of misspelled words functionality. You can turn off or stop autocorrect of certain words using this option.

Understanding ‘AutoFormat As You Type’ Tab

This tab has three checkboxes that Microsoft has checked(ticked) by default. 

AutoFormat As You Type Tab

Internet and network paths with hyperlinks – If you uncheck this checkbox, the excel would disable automatic hyperlink creation for web URLs or email addresses.

Writing Email Address - AutoCorrect

Include New Row and Column in Table When this checkbox is enabled, excel automatically expands the excel table if you type anything in the row or the column adjacent to that table. To disable it, uncheck this checkbox.

Excel-Table-AutoCorrect

Fill formulas in tables to create calculated columns – Excel copies the formula to other cells of a column in an excel table when you type in just one of the cells. To disable this, untick the checkbox.

Formulas ExcelTable AutoCorrect

Understanding ‘Actions’ in AutoCorrect in Excel

The Autocorrect ‘Actions’ tab enables and disables some additional actions that you can perform using Microsoft Excel. 

By default, Excel has provided with only one action – Date (XML) which enables you to open the Microsoft Calendar on a specific date directly from excel.

You can search for more actions using the ‘More Actions …’ button as highlighted in the below image.

Actions Tab AutoCorrect

‘Math AutoCorrect’ in Excel

There is a specific tab to autocorrect the mathematical functions. For example, to insert an angle symbol – ∠, type the text \angle in an equation.

Math AutoCorrect

It is important to note that the Math AutoCorrect only works in equations and not in the cell.

Add Your Own AutoCorrect

With the ‘AutoCorrect’ feature in Excel, you can create your own custom AutoCorrect. By creating your own autocorrect, you can save a lot of time and get rid of typing long-tailed repeated words. 

Let us take one example. Suppose you have to repeatedly write the name of your company ‘Any Body Can Excel Private Limited’ in excel. Writing such a long company name each time is a quite tiresome task.

The AutoCorrect feature would be a life-saver in this case. Follow the below steps:

Open the ‘AutoCorrect’ dialog box using the navigation path File > Options > Proofing > AutoCorrect Options button.

In the ‘AutoCorrect’ tab, under the ‘Replace’ input box enter a short name like ABCE. In the ‘With’ input box enter the full name of the company ‘Any Body Can Excel Private Limited’.

Now, click on the ‘Add’ button to add this customized autocorrect in the list and click ‘OK’ to exit the dialog box.

The below image illustrates the same.

Create Custom AutoCorrect Entry

Now, type the short word ABCE and press Enter key and you would see that excel replaces the ABCE with the full company name.

Demonstration of Custom AutoCorrect Entry

I use this technique in my organization to quickly write the name Excel Unlocked in a cell by entering EU.

In a similar manner, you can use this technique to insert a repetitively used symbol (without using the ‘Symbol’ in Excel). Just copy the required symbol from the ‘Symbols dialog box and place it in the ‘AutoCorrect’ dialog box.

How to Change or Delete AutoCorrect

In the above section, we learned to add a new autocorrect entry so in order to make our task easy and quick. In this section, we would learn how to change the existing autocorrect entry. Also, we would look into deleting the entry.

Suppose, we converted our company ‘Any Body Can Excel Private Limited’ into a Limited company with a new name as ‘Any Body Can Excel Limited’. Therefore, we need to change the autocorrect, so that excel now picks up the new company name when we type ABCE.

Follow the undermentioned steps to achieve the same:

Open the ‘AutoCorrect’ dialog box using path – File > Options > Proofing > AutoCorrect Options button.

Under the ‘AutoCorrect’ tab, search for the entry, click on it and you would notice that it would appear on the input box over there.

Selecting AutoCorrect Entry

Make the change in the input box and click on the ‘Replace’ button, as demonstrated in the image below:

Change Name and Replace AutoCorrect Entry

Click ‘OK’ to exit the screen. Now, when you type the word ABCE, it would return the new value ‘Any Body Can Excel Limited’.

Similarly, if you do not want to autocorrect a specific word, select the entry and click on the ‘Delete’ button.

How to Stop and Turn Off AutoCorrect

To entirely prevent autocorrect in Excel, just untick the relevant options under the ‘AutoCorrect’ dialog box.

Open the ‘AutoCorrect’ dialog box using path – File > Options > Proofing > AutoCorrect Options button.

Under the ‘AutoCorrect’ tab, uncheck the checkboxes.

Turn Off AutoCorrect Feature Excel

The ‘Replace Text as you type’ option if unchecked, would prevent correcting all the ‘Replace’ words available below this checkbox.

Undo AutoCorrect in Excel

Many a time, you just want to autocorrect a particular text that you type once. For example, when you are typing the serial numbers like (a), (b), (c), (d) and so on, excel would automatically convert the letter (c) into © (copyright symbol).

Now, you do not want to remove this autocorrect entry from the ‘AutoCorrect Options’, and at the same time want that the serial numbers should work.

In order to perform this, type the text (c) and then press the Space key on your keyboard. Without moving further, just press Ctrl + Z to undo the action.

This brings us to the end of this blog. Share your views and comments in the comments section below.

Leave a Comment