This tutorial shows you how to check for errors in Excel manually, with VBA code and using a special tool. You will learn how to check spelling in cells and sufferings, the active worksheet, and the entire workbook or workbook.
Errors Although Microsoft Excel is not a word processor, it has several features for working with text, including spell-checking or spell checker. However, spell checker in Excel is not exactly the same as Word. This software does not offer advanced features such as grammar checking and also does not specify incorrect words when typing. But Excel still offers a spell checker function, and this tutorial will teach you how to get the most out of it.
How to check the spelling of words in Excel
- Spell checker in cells and sufferings
- Spell checker on selected tabs
- Spell checker throughout the workbook
- Spell checker in macros
- Change spell checker settings in Excel
- Spell checker does not work in Excel
- Find typographical and typographical errors with Fuzzy Duplicate Finder
How to spell checker in Excel
No matter what version you are using, Excel 2016, Excel 2013, Excel 2010 or lower, there are 2 ways to check the spelling of words in Excel: via ribbon and keyboard shortcut.
Simply select the first cell or cells you want to examine and perform one of the following steps:
- Press the F7 key on your keyboard.
- Click the Spelling button on the Review tab in the Proofing group .
This performs a spell check on the active worksheet:
When an error is found, the Spelling dialog box is displayed:
To correct a mistake, select the appropriate option in the Suggestions section and click the Change button. The wrong word will be replaced with the selected word and the next mistake will be noticed.
If “wrong” was not really wrong, choose one of the following:
- Click Ignore Once to ignore the current error .
- Click Ignore All to ignore all errors similar to the current error .
- Click Add to Dictionary to add the current word to the dictionary . This will prevent the word from being mistaken the next time you check the spelling.
- Click Change All to replace all the errors similar to the current error with the selected suggested word .
- Click AutoCorrect to allow Excel to correct the error it deems appropriate.
- To set another correction language, select it from the Dictionary language section.
- Click the… Options button to view or change the spell checker settings .
- Click the Cancel button to stop the editing process and close the conversation .
When the spell checker is complete, Excel will show you the message:
Spell checker in cells and sufferings
Depending on your choice, Excel spell checker processes different areas of the worksheet:
By selecting a single cell, you tell Excel to spell check on the active tab, including the text in the page header, footnotes, comments, and graphics. The selected cell is the starting point:
- If you select the first cell (A1), the whole screen is checked.
- If you select another cell, Excel performs a spell check from that cell to the end of the worksheet. When the last cell is checked, you will be asked to check the spelling at the beginning of your sheet.
To check the spelling of a particular cell, double-click on that cell to enter edit mode, and then start spell checking.
To check spelling in a wide range of cells, select that range and then run spell – checker .
To check only part of the contents of the cell, click on the cell and select the text to check in the formula bar, or double-click on the cell and select the text in the cell.
How to check spelling in a few sheets
To do a spell checker on multiple sheets at once, follow these steps:
1. Select the tabs you want to check. To do this, press and hold the Ctrl key and then select the tabs.
2. Press the spell checker shortcut ( F7 ) or click the Spelling button in the Review tab .
Excel checks spelling mistakes in all selected worksheets:
When the spell check is complete, right-click on the selected tabs and click on Ungroup sheets .
How to check spelling in a workbook
To check spelling on all worksheet tabs, right-click on each tab and select Select all Sheets from the list . Select all tabs, press F7 or click the Spelling button on the ribbon.
How to check spelling in formulas
Usually Excel does not check formula-based text because a cell actually contains a formula, not a text value:
However, if you go into edit mode and then check spelling, this will be done:
Of course, you need to examine each cell individually, which is not very good, but this approach may still help you to correct spelling mistakes in large formulas, for example in nested conditional expressions (IF). ).
Check spelling in Excel using macros
If you like automatic tasks, you can easily automate the process of finding the wrong words in your worksheet.
Macro for spell checking on the active tab
What’s easier than clicking a button? Maybe this line of code 🙂
Macro to check the spelling of all active worksheets
You already know that to check for spelling mistakes on multiple tabs, you select the corresponding tabs. But how do you check for hidden tabs?
Use one of the following macros, depending on your purpose.
To check all visible tabs:
To check all tabs in the active, visible and hidden workbook:
Highlight incorrect words in Excel
This macro allows you to find the wrong words by looking at the page. This highlights cells containing one or more misspellings in red. To use another background color, change the RGB code in this line:
(cell.Interior.Color = RGB (255, 0, 0
How to use misspellings macros
Framework sample books with macros Spell Check from here download and perform the following steps:
1- Open the downloaded workbook and activate the macros if requested.
2. Open your workbook and go to the worksheet you want to review.
3. Press Alt + F8 , select Macro and hit Run .
Sample workbooks include the following macros:
- SpellCheckActiveSheet – Performs a spell check on the active worksheet.
- SpellCheckAllVisibleSheets – Checks all visible tabs in the active workbook.
- SpellCheckAllSheets – Check visible and hidden tabs in the active workbook.
- HighlightMispelledCells – Changes the background color of cells containing incorrect words.
You can also add macros to your personal tab by following these instructions:
For example, to highlight all misspelled cells in the current spreadsheet, run this macro:
The result is as follows:
How to change spell checker settings in Excel
If you want to change spell checker settings in Excel, click File> Options> Proofing and then check the following options:
- Ignore words in uppercase: Ignore words in uppercase
- Ignore words that contain numbers: Ignore words that contain numbers
- Ignore internet files and addresses: Ignore internet files and addresses
- Flag repeated words: Flag duplicate words
The following image shows the default settings:
Spell checker does not work in Excel
If the spell checker does not work properly in Excel, try the following simple troubleshooting tips:
The Spelling button is gray: Most likely your worksheet is protected. Excel spell checker does not work on protected tabs, so you must first unprotect your worksheet.
Being in edit mode: When you are in edit mode, only the spelling mistakes of the cell you are editing are checked. To check the entire page, exit the edit mode and then check the spelling.
Non-check text in formulas: Cells containing formulas are not checked. Be in edit mode to check spelling in formula.
Find typographical and typographical errors with Fuzzy Duplicate Finder
In addition to the ability to check spelling in Excel, users can use special tools in the Ablebits Tools tab under Find and Replace to find and correct typographical and typographical errors:
Clicking on Search for Typos opens the Fuzzy Duplicate Finder page on the left side of your Excel window. You must select the scope for checking for errors and your search settings:
- Max number of different characters – Limits the number of differences to search.
- Min number of characters in a word / cell – removes very short values from the search.
- The cells contain separate words delimited by – Check this box if your cells contain more than one word.
With the settings configured correctly, click the Search for typos button.
This will start searching for values that are different in 1 or more characters, as specified by you. When the search is complete, you will see a list of matching word groups as shown below.
Now you need to set the correct value for each group. To do this, expand the group and click on the review icon in the Action column next to the appropriate value:
If the subgroup does not contain the appropriate word, click Correct Value next to the root option , type the word, and press Enter .
After assigning the correct values to all subgroups, click the Apply button to correct all the errors on your worksheet with one move: