Strategy: There are two common causes for this. The second most common cause is that you have a few blank cells in the revenue column. More likely: you are selecting all of columns A:H before creating a pivot table instead of selecting a single cell. This technique allows you to paste new records below the data later and you can simply Refresh. But this method is adding a million blank cells to the data range.

If you are selecting the entire column before creating the pivot table, the next topic will allow you to paste new records and have them automatically become part of the pivot table. If you start using a Table as the source, you won’t have to select the entire column anymore and you can simply checkmark Revenue.

Additional Details: Let’s say you want to add new records once a month. After pasting the records, you can use the Change Data Source button on the Analyze tab. Change the data address from ending in 564 to the new last row and click OK. This whole process takes less than 10 keystrokes and is far less hassle than have Revenue appear in the wrong spot.

Alternate Strategy: If the problem is caused by a few blank cells in the revenue column, follow these steps:

1. Select all of the cells that should be numeric. 2. Ctrl+H to display Find & Replace

3. Leave the top box blank. Type a 0 in the second box. Click Replace All.

3

Figure 775 People in my seminars convinced me this is easier than Go To Special, Blanks.

CONVERT YOUR DATA TO A TABLE BEFORE ADDING RECORDS

Problem: I have 100 new records to paste below the original data that is in the pivot table. How do I do that?

Strategy: There are two solutions. I recommend the second one.

●● Paste the new data below your original data. Select a cell in the pivot table. Choose the Change Data Source icon (see Figure 774 above).

●● Select a cell in the original data set. Press Ctrl+T to define the data as a table. Paste new data be- low the original data. The Data Source is automatically updated. Simply click the Refresh button to incorporate the new records.

The table feature began in Excel 2003 as a List. In Excel 2007, it was renamed to be a Table, creating confusion between the Data Table found in the What-If tools and the Format as Table command on the

Home tab.

The Table applies some interesting formatting. The Table adds Filter dropdowns. The Table makes it easier to enter formulas. But the most valuable feature of the table is to have the pivot table data range automatically grow as you add new rows to the underlying data.

Here is an example.

Currently, the pivot table uses rows 1:564 of data.