Pivot Tables are not able to be formatted as you like.
1) Place your cursor above the top border of the pivot table until it changes to a down arrow, then click (this should select the whole pivot table...play around until you get it)
2) Ctrl + C (copy)
3) in another location, right click and paste special values
4) right click again and paste special formats
5) Select the cells where you want to fill in the blanks, including the top cell which shows the first name (is not blank).
6) With the Selection still active, Ctrl + G (Go To)
7) In the dialog box, click the button for special cells
8) click the box for blanks, then hit okay
9) blanks should now be selected
10) enter an equal sign and an up arrow using your keyboard and then press Ctrl + Enter.
11) cells should be filled in now
12) To filter the data the formulas should be replaced with values. Select the cells again, Right click|Copy, then Right click|PasteSpecial|Values.
When I have a pivot table that I need to do this in every month I will sometimes use formulas to the right of the pivot table to pull values from inside the pivot table. In this case, I will use a formula such as:
=IF(ISBLANK(A8),IF(C8="","",D7),A8). This means setting up the pivot table first, then using columns to the right to work on the data and make it more useable for my needs.
This will pull the value from the column, or if it is blank the value above it. The first row simply pulls the column headers over, so they are never blank, and this also seeds the first row for the formulas below it. Apparently, I want a blank when their is no value for the data, which is why I have a nested if in the formula.
Hope this helps. there is something about this same problem in the book Pivot Table Data Crunching but I can't remember if they provided a macro to handle these steps or not.
Hope this helps.
Note: Edited per some posts that follow.
