Excel Auditing Checklist: 2 Auto Features and 4 Tools to Quickly Identify and Rectify Errors BEFORE You Publish

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

The Auditing Checklist below provides a brief overview of the tools you can utilise to pick up and rectify any errors before you share or publish your spreadsheet.

Plus, a couple of useful things that Excel just does for you because it likes you!

1. Auditing checklist #1: Formula AutoCorrect

When a formula is typed incorrectly (e.g. a bracket is missed), Excel displays a message box stating the error and proposes a correction.

The proposed correction should be examined before it is accepted as Excel simply takes a best guess and is sometimes wrong.

For example, this formula doesn’t have a closing bracket …

Formula autocorrect kicks in when, for example, you miss a closing bracket on a formula

… and when « Enter is pressed the message box appears.

Auditing Checklist: Formula AutoCorrect in action

2. Auditing Checklist #2: Spell Check

Before distributing a spreadsheet, always run a Spell Check which checks each word in the worksheet against a build-in dictionary.

Unrecognised words are flagged as potential errors.

1.  Display the Spelling dialog using one of the following techniques.

  • Press F7
  • Select the Review tab then click the Spelling icon (far-left-hand side).
Auditing tool: Spell-checking a worksheet

2.  If the flagged word is indeed spelt incorrectly, choose a suggestion from the list then click Change or Change All.

3.  If there are no suggestions for the flagged, click back to the worksheet and make the change manually.

4.  If the flagged word is spelt incorrectly and is likely to be used again, click AutoCorrect and populate the With box with the correct spelling.

Every time you mistype the flagged word in the future, it will be automatically swapped to the correct spelling.

5.  If the flagged word is spelt correctly but just not recognised, click Add to Dictionary to stop it being flagged again or click Ignore Once or Ignore All to let it pass.

When Spell Check flags a word it doesn't recognise, you can ignore it once or every occurence, change it once or every occurence, add it to the custom dictionary or add it to the AutoCorrect library so it corrects automatically in the future.

If you were not in cell A1 when you initiated the spell check, Excel asks you if you would like to recheck from the beginning of the worksheet. Choose Yes.

Spell checl: Do you want to continue checking at the beginning of the sheet?

When Excel has checked everything, Excel will provide you with confirmation.

Spell check complete. You're good to go!

3. Auditing Checklist #3: Find a word or phrase

The Find & Replace tool is available to all Office applications. In Excel, it searches the current worksheet and if requested, replaces the selected text with something else.

1.  Display the Find and Replace dialog using one of these methods:

  • Press Ctrl F.
  • Select the Find & Select icon on the Home tab, then choose Find.
locating the Find and Replace tool on the Home ribbon

2.  Enter the word or phrase you want to locate.

3.  Click Find Next to find the next occurrence of the search word in the worksheet.

4.  Click or Find All to list all occurrence of the search word.

Find Next or Find All

4. Auditing Checklist #4: Replace a word or phrase

If you want to do a swapsie:

1.  Display the Find and Replace dialog using one of these methods:

  • Press Ctrl H.
  • Select the Find & Select icon on the Home tab, then choose Replace.
Auditing Checklist Tool 4: locating the Find and Replace tool on the Home ribbon

2.  Enter the word or phrase you want to locate.

3.  In the Find What box, enter the word you wish to find.

4.  In the Replace With box, enter the word or phrase that will replace what is found.

5.  Click Replace to replace the current entry.

6.  Click Replace All to replace all entries within the current worksheet.

5. Auditing Checklist #5: Fine-tuning Find and Replace

In the Find or Replace dialogs, click the Options button to expand the options.

Fine-tuning the find-and-replace feature

To conduct a case sensitive Find or Replace

  • Tick the MATCH CASE box. If searching for 'Book', then 'book' with a lower case 'b' is then excluded.

To find or replace an exact phrase

  • Tick the MATCH ENTIRE CELL CONTENTS box. Now when searching for 'book', the words 'books', bookend', bookkeeper and bookshelf are all excluded.

To find or replace FORMATS as well as or instead-of just CONTENT

1.  Click the Format... button on the Find line or the Replace line. This displays the Find Format dialog which looks and behaves exactly the same as the Format Cells dialog.

2.  In the lower left corner, click the Choose Format From Cell... button which gives you an eye dropper tool .

Click any cell to use all the formatting attributes from that cell (e.g. font, colour, size, borders etc.)

Find and Replace: Choose Format From Cell

3.  Click each tab and make any further changes you need.

Make any further changes to the format you wish to find or replace witgh, using teh standard Format Cells dialog

6. Auditing Checklist #6: AutoCorrect

AutoCorrect is a feature that allows text to be automatically changed as soon as it is typed.

1.  Click the File tab, then Options from the left-hand-panel. Click the Proofing option.

2.  Click the AutoCorrect Options button.

3.  The tick boxes (see 3) do what they say on the tin. By default, they are all ticked. Select or clear each checkbox as appropriate.

How to locate the AutoCorrect options: File tab | Options | Proofing | Autocorrect Options

The bottom half of the dialog box is the focus of this section.

There are 2 lists. In your document, when you type something that matches an entry in the Replace list, Word automatically replaces it with the corresponding entry in the With list

There are a number of ways Autocorrect can be used:

  • Inserting symbols such as the copyright symbol instead of (c).
  • Automatically correcting common spelling errors such as accommodate and separating conjoined words like about the and about it
  • Inserting long items of frequently used text, such as disclaimer statements.

To add your own entry:

1.  Place the cursor in the Replace box and type an entry.

2.  Move the cursor to the With box and type an entry, then click Add

7. Auditing Checklist: Key takeaways

  • If you miss the closing bracket off a simple formula, Excel will ad if for you.
  • Press F7 or click the Review tab, then the Spelling icon (far left) to start a spell check.
  • Press Ctrl F to find an item. Press Ctrl H to replace one item with another.
  • In the dialog, click the Options button to get extra options.
  • Tock Match Case for a case sensitive search
  • Tick Match entire cell contents to search a specific exact phrase.
  • Click the Format button on the Find or Replace line to find or replace formatting as well as or instead of just words.
  • Use AutoCorrect (File | Options | Proofing) to automatically swap one item for another.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Check the RELATED articles below

Read More
The Best Way to Create Drop-Down Lists in Excel That Are Easy to Maintain, Re-use and Put YOU in Full Control
Read More
5 Excel Functions for Beginners: Simplify Your Formulas and Release the Magic
Read More
Excel Formulas for Beginners: Bring Your Spreadsheet to Life
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>