Excel 2019 Bible

Part II: Working with Formulas and Functions

If your formula doesn’t ignore blank cells the way that it should, check to make sure that

the blank cells are really blank. Here’s how to search for cells that contain only blank

characters:

1. Press Ctrl+F. The Find and Replace dialog box appears.


2. Click the Options button to expand the dialog box so that it displays additional
   options.


3. In the Find What box, enter . That’s an asterisk, followed by a space, followed
   by another asterisk.


4. Make sure that the Match Entire Cell Contents check box is selected.


5. Click Find All. If any cells that contain only space characters are found, Excel lists
   the cell address at the bottom of the Find and Replace dialog box.

Extra space characters

If you have formulas or use procedures that rely on comparing text, be careful that your

text doesn’t contain additional space characters. Adding an extra space character is par-

ticularly common when data has been imported from another source.

Excel automatically removes trailing spaces from values that you enter, but trailing spaces

in text entries are not deleted. It’s impossible to tell just by looking at a cell whether it

contains one or more trailing space characters.

You can leverage the TRIM function to identify values that contain leading spaces, trail-

ing spaces, and multiple spaces within a text string. For instance, this formula will return

FALSE if the text in cell A1 contains leading spaces, trailing spaces, or multiple spaces.

=TRIM(A1)=A1

Formulas returning an error

A formula may return any of the following error values:

■ (^) #DIV/0!
■ #N/A
■ (^) #NAME?
■ #NULL!
■ (^) #NUM!
■ #REF!
■ (^) #VALUE!
The following sections summarize possible problems that may cause these errors.