The tutorial shows how to perform spell check in Excel manually, with VBA code, and by using a special tool. You will learn how to check spelling in individual cells and ranges, active worksheet and the entire workbook.
Although Microsoft Excel is not a word processing program, it does have a few features to work with text, including the spell-checking facility. However, spell check in Excel is not exactly the same as in Word. It does not offer advanced capabilities like grammar checking, nor does it underline the misspelled words as you type. But still Excel provides the basic spell checking functionality and this tutorial will teach you how to get most of it.
No matter which version you are using, Excel 2016, Excel 2013, Excel 2010 or lower, there are 2 ways to spell check in Excel: a ribbon button and a keyboard shortcut.
Simply, select the first cell or the cell from which you'd like to start checking, and do one of the following:
This will perform a spelling check on the active worksheet:
When a mistake is found, the Spelling dialog window shows up:
To correct a mistake, choose an appropriate opting under Suggestions, and click the Change button. The misspelt word will be replaced with the selected one and the next mistake will be brought to your attention.
If the "mistake" is not really a mistake, pick one of the following options:
When the spell check is complete, Excel will show you the corresponding message:
Depending on your selection, Excel Spell check processes different areas of the worksheet:
By selecting a single cell, you tell Excel to perform spell check on the active sheet, including text in the page header, footer, comments, and graphics. The selected cell is the starting point:
To spell check one particular cell, double-click that cell to enter the edit mode, and then initiate spell check.
To check spelling in a range of cells, select that range and then run the spell-checker.
To check only part of the cell contents, click the cell and select the text to check in the formula bar, or double click the cell and select the text in the cell.
To check several worksheets for spelling mistakes at a time, do the following:
Excel will check spelling mistakes in all the selected worksheets:
When the spell check is completed, right click the selected tabs and click Ungroup sheets.
To check spelling in all the sheets of the current workbook, right click on any sheet tab and pick Select all Sheets from the context menu. With all the sheets selected, press F7 or click the Spelling button on the ribbon. Yep, it's that easy!
Normally, Excel does not check formula-driven text because a cell actually contains a formula, not a text value:
However, if you get in the edit mode and then run spell check, it will work:
Of course, you will need to check each cell individually, which is not very good, but still this approach may help you eliminate spelling errors in big formulas, for example, in multi-level nested IF statements.
If you like automating things, you can easily automate the process of finding wrongly spelled words in your worksheets.
What can be simpler than a button click? Maybe, this line of code :)
Sub SpellCheckActiveSheet() ActiveSheet.CheckSpelling End Sub
You already know that to search for spelling mistakes in multiple sheets, you select the corresponding sheet tabs. But how do you check hidden sheets?
Depending on your target, use one of the following macros.
To check all visible sheets:
Sub SpellCheckAllVisibleSheets() For Each wks In ActiveWorkbook.Worksheets If wks.Visible = True Then wks.Activate wks.CheckSpelling End If Next wks End Sub
To check all sheets in the active workbook, visible and hidden:
Sub SpellCheckAllSheets() For Each wks In ActiveWorkbook.Worksheets wks.CheckSpelling Next wks End Sub
This macro allows you to find the misspelled words simply by viewing the sheet. It highlights the cells containing one or more spelling mistakes in red. To use another background color, change the RGB code in this line: cell.Interior.Color = RGB(255, 0, 0).
Sub HighlightMispelledCells() Dim count As Integer count = 0 For Each cell In ActiveSheet.UsedRange If Not Application.CheckSpelling(Word:=cell.Text) Then cell.Interior.Color = RGB(255, 0, 0) count = count + 1 End If Next cell If count > 0 Then MsgBox count & " cells containing misspelled words have been found and highlighted." Else MsgBox "No misspelled words have been found." End If End Sub
Download our sample workbook with Spell Check macros, and perform these steps:
The sample workbook contains the following macros:
You can also add the macros to you own sheet by following these instructions: How to insert and run VBA code in Excel.
For example, to highlight all the cells with spelling errors in the current spreadsheet, run this macro:
And get the following result:
If you'd like to tweak the behavior of spell check in Excel, click File > Options > Proofing, and then check or uncheck the following options:
All the options are self-explanatory, maybe except the language-specific ones (I can explain about enforcing strict ё in the Russian language if someone cares :)
The screenshot below shows the default settings:
If spell check does not work properly in your worksheet, try these simple troubleshooting tips:
Most likely your worksheet is protected. Excel spell check does not work in protected sheets, so you will have to unprotect your worksheet first.
When in edit mode, only the cell you are currently editing is checked for spelling errors. To check the whole worksheet, exit the edit mode, and then run spell check.
Cells containing formulas are not checked. To spell check text in a formula, get in the edit mode.
In addition to the built-in Excel spell check functionality, the users of our Ultimate Suite can quickly find and fix typos by using a special tool that resides on the Ablebits Tools tab under Find and Replace:
Clicking the Search for Typos button opens the Fuzzy Duplicate Finder pane on the left side of your Excel window. You are to select the range to check for typos and configure the settings for your search:
With the settings properly configured, click the Search for typos button.
The add-in starts searching for values that differ in 1 or more characters, as specified by you. Once the search is finished, you are presented with a list of the found fuzzy matches grouped in nodes like shown in the screenshot below.
Now, you are to set the correct value for each node. For this, expand the group, and click the check symbol in the Action column next to the right value:
If the node doesn't contain the right word, click in the Correct Value box next to the root item, type the word, and press Enter.
Upon assigning the correct values to all the nodes, click the Apply button, and all the typos in your worksheet will be fixed in one go:
That's how you perform spell check in Excel with Fuzzy Duplicate Finder. If you are curious to try this and 60+ more professional tools for Excel, you are welcome to download a trial version of our Ultimate Suite. If you like the tools and decide to get a license, we are happy to make you this special offer:
2 responses to "How to spell check in Excel"
Hi,
I have been trying to find a solution for some time, without success, to a problem I have with a spell check macro on a protected worksheet. What I am trying to achieve is a macro that will spell check only unlocked cells while highlighting the cell where an error is found.
The CheckSpelling method will check only the unlocked cells but will not highlight the cell where an error is found.
The Application.CommandBars("Tools").Controls("Spelling...").Execute method will highlight the cell where an error is found but checks ALL cells including unlocked.
I have posted this to my usual forum but nobody appears to have a solution.
I found this code and it works for selecting and checking only cells that are unlocked. It doesn't highlight it, but you may be able to alter it using the above information to do what you want.
Sub SelectUnlockedCells_Spellcheck()
ActiveSheet.Unprotect Password:="****"
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
If Cell.Locked = False Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
If FoundCells Is Nothing Then
MsgBox "All cells are locked."
Else
FoundCells.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081
End If
ActiveSheet.Protect Password:="****"
End Sub