Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you enter data into an Excel worksheet, the program looks at what you entered to determine if you made any errors and if it should change anything. For example, if you type an e-mail address or web link into a cell, Excel formats that address as a hyperlink. In this movie, I'll show you how to control and reverse the changes Excel makes automatically. First, I'll give you a quick demonstration of how the AutoCorrect feature works. AutoCorrect is when you type in something that Excel has flagged in a list as an error and it changes it to what it thinks is the correct term.
So let's say that in this brief little customer list you want to add an entry for ACN. That's ACN Incorporated. If you type in ACN and then press Tab, or Return, you see that Excel changed ACN, which I typed in, to CAN. So why did that happen? Well that's because ACN is in the AutoCorrect list. You can see that list by clicking the Excel menu and opening the Preferences dialog, and in there clicking AutoCorrect.
The AutoCorrect page of the Preferences dialog gives you tools so that you can manage how Excel applies AutoCorrect and AutoReplace rules. So, for example, what I mentioned before about Excel replacing Internet and network paths, such as e-mail addresses, with hyperlinks, you can see that rule is in place here. It also corrects anything that you type in with two initial capitals. It leaves the first letter capitalized, but lowercases the second. It also capitalizes the first letter of sentences, the names of days, and this option, Replace text as you type, applies to the Replace list.
So remember the term that I typed in earlier, ACN, which was the name of a corporation? If you type acn in the Replace list, you'll see here on the left that it is a term that is part of the list, and you have the replacement value. If you don't want Excel to make that particular change, you have two options. First, you can delete this entry and to do that you make sure the entry is highlighted here in the list, and then you click the Delete button. So now when I click OK and I type ACN and press tab, now Excel won't replace it because it's no longer on the list. And I will press Command+Comma to bring the Preferences dialog back, and go to the AutoCorrect page.
If you want to add an entry to the list, you can do so by typing an entry in the Replace box, and in this case I'm going to type in a misspelling for this customer's name. In other words, the scenario is that I use this customer's name a lot in my worksheets, but I, and maybe a few of my other employees, tend to type it with one L. So if anyone types it in with one L, I want to correct it automatically to two. So we are replacing Cancelari, with one L, and I press the tab key to go to With, and I'll type it correctly Cancellari.
With that entry in, I can click Add, and now Excel will replace any time I type in Cancelari with one L with the version with two Ls, and I'll show you how that works in the worksheet. I'll type Cancelari, press Return, and Excel corrects it for me. If you go back in to the Preferences dialog box one more time, and I clear the Replace text as you type check box, and click OK, now Excel won't automatically replace any values at all.
So if I were to type in Cancelari - and I'm ignoring the AutoComplete underneath it - with one i, and I press tab, Excel does not make the correction because I turned it off. AutoCorrect is an useful feature that captures many common errors, but you might find that something the program thinks is an error is actually correct. In that case, you can prevent Excel from making that change in the future, or you can turn off AutoReplace entirely.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64848 Viewers
80 Video lessons · 124392 Viewers
52 Video lessons · 60319 Viewers
59 Video lessons · 46142 Viewers