Grouping empty cells in Pivot Table

I recently ran into an issue while trying to group data by date in a pivot table; the error I get from excel when I right click on a date in the date column and select group is "Cannot group that selection." I did some searching on Google and found that you can't group empty cells, of which my pivot table has many.

This is a pivot table for my personal finances: I have 49 spending categories that I'd like to summarize by month/year or quarter/year. The pivot table is generated from a spreadsheet that resembles the following:


| Date | Funds Out | Funds In | Category |
| 01/01/2011 | $2.20 | | Dining:Coffee |


Obviously there are going to be many, many blank cells in the pivot table because not every category will have a transaction on the same day as every other category. Further still, many expense-type transactions are not likely to have many, if any, "Funds In" entries (but those will appear when I've returned an item for refund, for example.) Likewise, income-type transactions aren't likely to have any "Funds Out" entries. I could simplify things a little by combining "Funds Out" and "Funds In" and just using -/+ numbers in a single column, but that still doesn't solve the issue with the categories (in fact, I've tried creating a pivot table leaving out the "Funds In" column.)

Is it totally impossible to create pivot table groups when there are empty cells? As far as I'm concerned, those cells could be "$0.00" when the grouping calculations (sum) are done. I've changed the table options to show 0 for empty cells, but I think that's just a display option and doesn't reflect in the underlying blank cell, so doing that still doesn't let me group the data.

This must be a common type of report for any business (or individual) - a summary of expense by category per month or quarter. How do I deal with the empty cells? I could probably create a pivot table per category, but that would be labour intensive and seems unnecessary. Why can't excel just treat empty cells as $0.00?

I'm using Excel as part of Office for Mac 2008, so VBA macros aren't available. I'm considering buying the latest edition of Office for Mac so if you know that this isn't an issue with the latest version that'd be more incentive for me to update.

I just noticed: It seems I can manually create groups by selecting the range I want to group, blank cells or otherwise.

This still isn't ideal - I'd have to manually create groups for every month and quarter of every year. If I can do it manually why won't it work automatically (with the "group by" dialogue that let's me select how I want to group things.)

Ah... The "blank cells" issue doesn't relate to blank cells in the data, but in the grouping column (ie: date.) I found a blank date cell at the bottom of my pivot table!

I created the table by selecting entire columns (that is, clicking column A and dragging my mouse to column F) and then creating the pivot table. It seems that excel for whatever reason (bug?) includes the first empty cell past the last row of data in the columns I selected.

How can I work around this such that I can append data to the ranges used in the pivot table and have the pivot table updated automatically?