Excel AutoCorrect: A Complete Guide + Time Saving Examples - KING OF EXCEL

Tuesday, January 7, 2020

Excel AutoCorrect: A Complete Guide + Time Saving Examples

Excel AutoCorrect: A Complete Guide + Time Saving Examples

What happens when you type the word ‘Drnik’ instead of ‘Drink’ in Excel?
You would notice that Excel will autocorrect that misspelled word to Drink (as shown below).
Excel Autocorrect in Action
Somehow, Excel knew that this is not the correct spelling and autocorrected it to the right one.

Now, it won’t autocorrect all the misspelled words.
Just a few!
For example, try the word ‘dirnk’.
It would not be auto-corrected.
The reason some words are autocorrected and others aren’t is because there is already a fix list of words that are prefilled in Excel to autocorrect.
Note: Autocorrect is enabled by default in Excel.
In this tutorial, I will explain what Autocorrect options are and then show you some examples where you can use it to save time. I will also cover how you can disable it (i.e., turn off autocorrect)
This Tutorial Covers:

Understanding the Autocorrect Options

Autocorrect was created to help you iron out small issues you may face while typing in Excel or while doing data entry (such as replacing some commonly misspelled words with correct words).
It also allows you to get some more control when using Excel (as we will see in the examples later in this tutorial).
But let’s first understand where are the autocorrect options and what is available by default.
To get the autocorrect options, click on the File tab.
File Tab in Ribbon
In the File screen, click on ‘Options’.
Click on Options
In the Excel Options dialog box, click on Proofing.
Click on Proofing option
In the Proofing options, click on the Autocorrect Options.
Click on the Autocorect Options Button
This will open the Autocorrect Options dialog box.
Autocorrect Options dialog box
Let me explain the different tabs in the Autocorrect dialog box and the options in these.

Autocorrect Options Tab

In the Autocorrect Options tab, there are some options that are enabled by default and take care of some common issues.
Autocorrect Options tab
  • Show Autocorrect Options buttons: This one is not relevant for Excel but it is for other MS applications. When this option is enabled, you see the autocorrect options in MS Word or MS PowerPoint (as shown below).
  • Correct two initial capitals: This option when enabled will automatically correct the two capital initials in Excel. For example, if you type ‘HEllo’, it will automatically change it to ‘Hello’
  • Capitalize first letter of sentences: When enabled, this option ensures that a new sentence starts with a capital letter. For example, if you type, ‘Hello. how are you?’, it will be autocorrected to ‘Hello. How are you?’
  • Capitalize names of days: This will automatically change the first letter of the day name if you enter in lowercase. For example, wednesday would be changed to Wednesday.
  • Correct accidental use of Caps lock key: In case you have the Caps lock on and you write a sentence, it will automatically correct the text and disable the Caps lock. For example, if you enter hELLO, it will automatically change it to Hello.
  • Replace text as you type: This is where Excel already has some commonly misspelled words (or shortcodes for some symbols). For example, if you type (c), it automatically gets converted into the copyright symbol. That is because it has been specified in the list in this option. You can add or remove words from the list (more on this in an example below).

Autocorrect Exceptions

While these autocorrect options are amazing, sometimes you may want it to not act super smart and correct these automatically.
For example, if you have the brand name ATs (where the ‘s’ is in lower case), Excel would automatically convert it into ‘Ats’.
While you like the autocorrect happening in all other cases, if you want to exclude this particular case, you can do that.
In the Autocorrect tab, click on the Exceptions tab.
Autocorrect Exceptions
In the Autocorrect Exceptions dialog box, you can have two types of exception:
  1. First Letter: By default, Excel capitalizes the alphabet after the period (dot). You can provide some exceptions here (there is already a list for common exceptions).
  2. Initial Caps: If you don’t want ATs to be converted to Ats, you can specify that here.
Autocorrect Exceptions dialog box

Autoformat As You Type Tab

This tab has three options (all of which are enabled). I find all these three options useful.
  1. Replace as you type: When you enter any hyperlinks in a cell, it would automatically be converted into a hyperlink. I sometimes disable this option as I often end up clicking the cell which then takes a few seconds and then opens the link in the browser.
  2. Apply as you work: This will automatically add new rows and columns in an Excel Table when you enter anything in the cell adjacent to the one in the table.
  3. Automatically as your work: When you enter a formula in a column in an Excel Table, this option will enter the same formula (with cell references adjusted) into all the cells in the column.
Autoformat as you type

Actions Tab

In Microsoft applications, you can create an action based on a specific word or text.
In Excel, there is only one type of action available – which is date action.
A date action means that if you have a date in a cell in Excel, you can right-click and you’ll get a few actions that you can take for that date (such as show that date in calendar or schedule a meeting in Outlook).
This could be useful if you have a list of dates and want to quickly save some in your calendar or want to schedule a meeting (using Outlook).
This option is disabled by default and you have to enable it to be able to use it in Excel.
Autocorrect Actions option needs to be enabled
Once enabled, you will be able to use it Excel. If you have a date in a cell, just right-click and you’ll find the actions options at the bottom.
Date Action in Excel Autocorrect

Math Autocorrect Tab

Just like you can insert symbols in an Excel cell (such as Delta, Degree, or Checkmark), you can also insert math symbols in an equation.
This tab has some text that automatically converts into the specified math symbol. For example, if you type \sigma, it will replace it with the σ symbol.
Autocorrect Math Symbols
Note that this will not work in the cells in the worksheet. It only works with equations.

How to Add Your Own Words to Excel Autocorrect?

Wish there were some words that were a part of autocorrect?
For example, let’s say you want to add the word ‘drikn’ to autocorrect so that it corrects it to ‘drink’.
You can use the below steps to add a word to autocorrect:
  1. Click on the File tab.
  2. Click on Options.
  3. In the Options dialog box, select Proofing.
  4. Click on the ‘AutoCorrect Options’ button.
  5. In the Autocorrect dialog box, enter the following:
    • Replace: drikn
    • With: drinkAutocorrect dialog box - replace with word
  6. Click ADD
  7. Click OK.
Now, when you type ‘drikn’ in Excel, it will autocorrect it to ‘drink’.
Before I show you some cool examples to use this, here are a few things you need to know about Autocorrect in Excel:
  • Autocorrect list is case sensitive. This means that you have added the word ‘drikn’ to be replaced by ‘drink’, it would only work with the lower case word. If you enter ‘Drikn’ or ‘DRIKN’, it will not be corrected.
  • This change is saved in Excel and would exist even if you close the workbook and open again. If you no longer want this, you need to go and delete it manually.
  • The change happens only when the exact word is used. For example, if you use ‘drikns’, it will not be autocorrected. For it to work, the word must not have characters just before or after it.
  • When you specify an autocorrect in Excel, it automatically gets activated in other MS applications such as MS Word or MS PowerPoint.
Autocorrect was created as a way to correct common spelling errors. But you can also use it in some awesome ways to save time.
Related: Spell Check in Excel.
Below are some useful examples to use Autocorrect (other than correcting a misspelled word).

Example 1: Use Autocorrect to Complete Abbreviations

Imagine you work for a company ‘ABC Technology Corporation Limited’.

Your work involves a lot of data entry in Excel and you have to manually type full company name many times in a day.
No matter how fast you type, this would feel like a waste of time.
Wouldn’t you wish there was a way where you can just enter ABC (or whatever you want), and excel replaces it with the company’s name?
This is where the awesomeness of Autocorrect can help.
You can specify an abbreviation in Autocorrect, and whenever you use that abbreviation, Excel would automatically convert that into the specified text.
For example, you can specify that whenever you type ABC, Excel should automatically replace it with ‘ABC Technology Corporation Limited’.
Something as shown below:
Excel Complete Abbreviations
This happens when you add an autocorrect in Excel where ABC should be corrected to ” (as shown belo
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment