Figure 720 The default settings are fine.

Now if you accidentally try to enter something in a formula cell, Excel will prevent you from entering the data.

FIND DIFFERENCES IN TWO LISTS

Figure 721 Did anything change between these lists?

Strategy: Use the Go To Special dialog’s Row Differences. 1. Select B2:B12.

2. Hold down the Ctrl key while selecting G2:I12.

3. Choose Home, Find and Select, Go To Special.

4. In the Go To Special dialog, choose Row Differences. Click OK.

Figure 722 Select Row Differences.

Gotcha: Highlight row differences can only compare one column to another column. Initially, it will only compare column B to column G and highlight what changed. If you then press the F4 key once for each additional column, the program will redo the compare for the next column and then the next column.

After step 4, you will have this:

Figure 723 Two customers changed.

5. Press the F4 key to compare column C to column H. 6. Press the F4 key to compare column D to column I.

7. Open the Paint Bucket icon on the Home tab and choose a fill color.

Result: All of the changed cells will be highlighted, in one list or the other.

Figure 724 Changed cells are highlighted.

Alternate Strategy: You can use the Formula method of conditional formatting to highlight differences.

Follow these steps: 1. Select G2:I12.

2. Type Alt+O followed by D. (That is O the letter).

3. Click New Rule.

4. Choose Use a Formula to Determine Which Cells to Format. 5. Type a formula of =G2<>B2.

6. Click the Format… button.

7. Click the Fill tab.

8. Choose a red format.

9. Click OK. Click OK.

3

Figure 725 Highlight differences using conditional formatting.

The changed cells on the right are highlighted.

Figure 726 The highlights only appear in the second data set.

NUMBER EACH RECORD, STARTING AT 1 FOR A NEW CUSTOMER

Problem: I have a list of invoice data. I want to number the records in such a way that the first invoice number for Ford is 1. The next

Ford invoice is 2, and so on. When I get to a new customer, I want to start over at 1.

Figure 727 You want to add sequence numbers within each customer.

Strategy: Use a formula in a new column A to add the record number. Follow these steps. 1. Select one cell in the customer column and select Data, AZ to sort the data by customer. 2. Insert a new temporary column A and add the heading Rec # to A1.

In A2, enter the formula =IF(C2=C1,1+A1,1). In plain language, this formula says, “If the customer in C is equal to the customer above me, then add 1 to the cell above me. Otherwise, start at 1.” Copy the formula down to all rows. Excel will number each group of customer invoices from 1 to N. When a new customer starts, the numbers will restart.

Figure 728 The live formulas work while the data is sorted.

Change the formulas in A using Ctrl+C, Home, Paste, Paste Values before sorting by invoice number. Alternate Strategy: You can use the formula =COUNTIF(C$2:C2,C2) without sorting.