Editor’s Note:

To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column,

click here

to visit CFO.com’s Spreadsheet Tips Page.

Reader Joe S. wins a copy of Pivot Table Data Crunching for this week’s question: “I use pivot tables for data analysis; great tool but I have a problem when my data range includes blank rows. When I select a field for values, the default is to “count” versus “sum”. Easy enough to change, but it would be great to not have to repeat this step for each column of the pivot table. Any suggestions?”

It is true that if you have even one blank cell in a numeric column, Excel will default to counting that column instead of using the sum function. I have to ask Joe why he has the blank rows. I’ve seen a few reasons for this:
• It is the entire row that is blank. The blank rows are there to break sections of the report apart.
• It just happens that there are some blank cells in the data. The person that I get the file from has some blanks in there. It comes from another department. I can’t force them to put zeroes in there.
• I expect my data set to grow in the future and I don’t want to have to redefine the source range when I add new records later.

Fig. 1

If Joe has the blanks because of the last issue, then there is a nice workaround. Build a pivot table based on the original data without any blank rows.
1.Go to the source data. Select one cell in the data. Press Ctrl+T. Confirm that your data has headers and click OK. This defines your data set using the new Excel 2007 Table functionality. Excel might add some formatting, but the formatting is not the important part! (In Excel 2003, use Ctrl+L instead of Ctrl+T). Before Excel 2003, this feature did not exist.
2.When you paste new data in the blank row below your existing data, the definition of the pivot table range will automatically grow to include the new data.
3.Go to the pivot table, click Refresh. The new records will be included.

If the blanks are there because the source of the data includes blank cells, then I have a quick way to replace all the blanks with zeroes. This will prevent the problem in the pivot table.

Select all of the numeric columns in your data set. Perhaps this is range F2:R50000.
1. Press Ctrl+G to display the Go To dialog.
2.In the lower left corner of Go To, choose Special
3. In the Go To Special dialog, choose Blanks. Click OK. You’ve now selected only the blank cells in your data. (See Figure 2)

Fig 2

4. Type a zero. Press Ctrl+Enter. All of the blank cells will be filled with a zero. Now, your pivot tables will sum instead of count.

If the cells aren’t actually empty, but contain some spaces, then in step 3 above, choose Constants, and uncheck Numbers, Logicals, and Errors. (See Figure 3)

If you are in Excel 2007 or newer, steps 2 and 3 can be replaced by selecting Home, Find & Select, Go To Special.

The above method will work whether there are a few blank cells or entire blank rows. If you have entire blank rows, you could consider sorting the data set to move the blank rows to the bottom of the data set. Those blank rows are likely causing strange “(blank)” entries to appear along the row labels of your pivot table.

CFO contributor Bill Jelen is the author of 32 books about Microsoft Excel. You have the chance to win a copy of one of his books by posting a question to the Community Center on the right. If Bill selects your question as the topic of a future column, we’ll send you a book as a thank-you. Bill’s next MrExcel Webcast, “Excel for Forecasting & Planning,” is Thursday, June 23, at 2:00 Eastern Time.


Click here


for more details.