To save time, create AutoCorrect entries for words, phrases, and even symbols that you type frequently. Then, type a short code, and Excel automatically changes it to the full text. See how to create an entry, then print a list of all your entries, and copy them to a different computer, using the AutoCorrect macros below.
Create Excel AutoCorrect Entries
The programs in Microsoft Office share a common list of AutoCorrect entries. Any entries that you create or change in Excel, will also be available in Word, Outlook, and PowerPoint.
To see the AutoCorrect list:
- On the Excel Ribbon, click the File tab, and then click Options
- Click the Proofing category, and then click the AutoCorrect Options button.
To add a text entry:
- In the Replace box, type a short code that will be easy to remember.
- In the With box, type the full word or phrase that will replace the short code, after you type it.
For example, I entered the short code: .dd
When I type that on a worksheet, it will be replaced by my full name: Debra Dalgleish
Create AutoCorrect Symbols
In the AutoCorrect Options window, there is a tab for Math AutoCorrect. Unfortunately, those shortcuts don’t work in Excel.
However, you can copy the symbols from that tab, and create regular AutoCorrect entries for them.
This animated screen shot shows the steps – type a short code, copy and paste a symbol from the Math tab, then use the short code on your worksheet, any time you need it.
List of Excel AutoCorrect Entries
If you’d like to see everything that’s in your AutoCorrect list, run this macro, written long ago by Dana DeLouis, and he shared it in the Excel newsgroups.
I added a line to insert a new sheet, so you don’t accidentally overwrite anything. The macro lists the short codes (Replace) items in column A, and the full text (With) in column B.
Copy this macro, and paste it into a regular code module.
Sub AutoCorrectEntries_Display() '// Dana DeLouis '// Backup AutoCorrect to Worksheet '2018-01-30 added line to insert new sheet Dim ACE ACE = Application.AutoCorrect.ReplacementList Sheets.Add Range(Cells(1), Cells(UBound(ACE), 2)) = ACE Columns("A:B").AutoFit End Sub
Edit the AutoCorrect Entries List
After the list is created on a worksheet, you can manually edit it, before you import the list to a different computer.
For example,
- add new codes and replacement text
- modify the replacement text for personal entries that you previously created
- delete any personal entries that won’t be needed on the other computer
Or, just leave the list as is, then save and close the workbook.
Add AutoCorrect Entries From List
After you create your list of AutoCorrect entries, use the following macro (also by Dana DeLouis)Â to add those entries on a different computer.
- Open the workbook with the list,
- Activate the sheet that has the exported list of AutoCorrect items.
- Run the macro below, to add those items to the new computer’s list.
What the macro does:
- adds any new items
- overwrites any matching codes
- does NOT remove any existing entries that are not in the list
Copy this macro, and paste it into a regular code module.
Sub AutoCorrectEntries_Add() '// Dana DeLouis '// Add AutoCorrect entries. '// Column A -> Wrong Word '// Column B -> Correct Word Dim rng As Range With Application.AutoCorrect For Each rng In Columns(1) _ .SpecialCells(xlConstants, _ xlTextValues).Cells .AddReplacement rng, rng.Offset(0, 1) Next End With End Sub
_____________________________________
____________________
It’s greatly useful, thanks.
In the sub to add entries, it bugs if there is a space at the end of the word. So I will add a line to remove that blank space.
Evelyne
Dear Debra, I have a large list of autocorrect words (7117 entries) in a list in excel that I am trying to get to work using code from this site. In column A of my list I have the wrongly-spelled words and in column B I have the correct names. I can’t figure out how to do it. I tried your code listed above as follows:
Sub AutoCorrectEntries_Add()
‘// Dana DeLouis
‘// Add AutoCorrect entries.
‘// Column A -> Wrong Word
‘// Column B -> Correct Word
Dim rng As Range
With Application.AutoCorrect
For Each rng In Columns(1) _
.SpecialCells(xlConstants, _
xlTextValues).Cells
.AddReplacement rng, rng.Offset(0, 1)
Next
End With
End Sub
However when I run it, it returns an error 1004 at ‘.AddReplacement rng, rng.Offset(0, 1)’.
Not sure what I am doing wrong – very frustrating!
Can you help please?
Regards
Charles