# Pivot table question

#### kristylee

##### New Member
OK - I am stumped on this one and need help. I have a pivot table and I am wondering how to do the following.

I have 3 pieces of data: employee name, customer, and units sold to customer. I have the pivot table set up to give me total units sold by employee then customer so it is listing the employee's name once then all of the customers with totals. I am wondering how I can get the employee's name to appear on each line (even though I still want to totals to work properly).

Help![/code]

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.

I'll try it - thanks!!

Thanks to both fr your help.

Alexander - I followed your steps but in #10, I think it should be press Control key and Enter - then it works like a charm!

Replies
4
Views
236
Replies
1
Views
214
Replies
0
Views
311
Replies
1
Views
175
Replies
2
Views
356

1,212,131
Messages
6,106,121
Members
447,996
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back