Pivot table question

kristylee

New Member
Joined
Aug 28, 2006
Messages
20
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]
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,797
Office Version
  1. 2019
Platform
  1. Windows
Pivot Tables are not able to be formatted as you like. :cry:

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.
 

kristylee

New Member
Joined
Aug 28, 2006
Messages
20
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,078
Members
414,500
Latest member
kevdragon1

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?

Disable AdBlock

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
Top