Free Microsoft Excel 2013 Quick Reference

Pivot table - data in columns

I have what I hope is an easy question for one of you gurus. I have a large spreadsheet with data arranged as follows:

Column A - contains all of my headers (approx 100)
Columns B - X - contains values associated with the headers

The reason for setting up the sheet this way was so that the person performing data entry does not have to scroll or tab over 100 columns (she feels it is easier to scroll down 100 rows instead.. don't ask)

At any rate, in order to quantify the results, I want to put the data into a pivot table for reporting purposes. Is there a way to setup a pivot table using the information in column A as the headers? When I go through the wizard, it currently only allows me to choose the headers from row A.

Hope I explained this well enough so that someone can answer.

Thanks in advance.


Post your answer or comment

comments powered by Disqus
Dear Friends,

I have a pivot table (originally some 600 records). But after filtering some columns the rows comes around 150.

When I am to generate the report in another sheet, I need these data. How to get these data into that sheet?

Now I am using the formula to retrieve it as mentioned below:
If the sheet name (where the pivot table is also there) is: DATASHEET
Report sheet name is: REPORTSHEET

In Report sheet, I am entering as:
=Datasheet!B2  =Datasheet!C2  =Datasheet!D2  =Datasheet!E2
=Datasheet!B3  =Datasheet!C3  =Datasheet!D3  =Datasheet!E3
=Datasheet!B4  =Datasheet!C4  =Datasheet!D4  =Datasheet!E4
=Datasheet!B5  =Datasheet!C5  =Datasheet!D5  =Datasheet!E5
But, I think some other easiest/advanced way is there to retrive/get the pivot table data.

May someone please help me to find that way.

Thanks in advance,

I am trying to create a checklist from a pivot table report. the data is essentially a list of software that exists on a set of workstations. The checklist should indicate the name of the software a selected workstation has on it. Iíve created a working copy in the attachment using data on Sheet2 however using a Pivot Table will change the structure of the data to what is shown on PivotStyleReport tab.

The issue I have is that the resultant data from a pivot table will be a digit underneath a heading of the name of the software, and Iím not certain how to capture the software title from the column heading to place onto the checklist when a digit appears in the row for each machine.

Attached is an example of what Iím trying to achieve. Sheet 2 has the data that Iím able to make work, the PivotStyleReport sheet is what I expect the same data to look like in a Pivot table. Iíd like to be able to select a workstation name from the dropdown and the checklist fills in based on the Pivot Table data.

In the attachment, Workstation 1-10 and 11-22 fill in the checklist differently and is a result of eliminating or allowing blanks in the data. Iíd prefer to eliminate the blanks in this using the Pivot Table data.

Hello all,

My pivot table wants to total 2 sets of data and put it in rows. I would like it to be in columns.

The small attached sample shows what I would like.

Thanks for looking at it.



Re: Pivot Table Data Fields

Does anyone know how to show text data in the data field?
We have reached the data volume limit for rows so are basically trying to add an additional column item into the data area.
What ever I try I can not get text data to show.
I know this is not how it is supposed to work... but..

Thanks in advance

[ This Message was edited by: andytripp on 2002-12-18 15:39 ]


I'm looking for a way to count the number of distinct items (as opposed to just number of rows/observations) in a Pivot table data field. For instance, I'm loading the following data in -

order customer a2
1 11 1
1 11 1
1 11 1
2 22 1
2 22 1
3 33 0
3 33 0
3 33 0
3 33 0
4 11 1
4 11 1
4 11 1
5 44 0
5 44 0
5 44 0
6 66 1
6 66 1

As you can see, there are really only 5 different customers (11,22,33,44,66), and I want to categorize them by the third column (a2). Dropping "a2" into the column field and "customer" into the data items field and changing the field settings on the "customer" field to count instead of sum yields something that looks roughly like -

Count of customer
a2 total
0 7
1 10

Instead of the 7 and 10, I would like it to only count the distinct customers so that it looks more like -

Count of customer
a2 total
0 2
1 3

Is there a way to get the pivot table to do this?

Thanks in advance!

I am using Excel 2007.

I have a table in one field that is the source for my Pivot Table.

First under Pivot Table Options on the Totals and Filters Tab I have
the Use Custom Lists when sorting disabled.

The Pivot table has three columns:

Customer, Customer Num, Gross Sales.

Customer and Customer Num are in the row labels portion of the pivot
field list and Gross Sales is in the Value portion.

Under the Layout & Print tab of the Field Setting for Customer and
Customer Num are set to show item labels in tabular form.

I click on PivotTable tools on the ribbon then on the Sort Icon. The
Sort By Value window shows Largest to Smallest under sort options and
Sort Customer Num by Gross Sales in descending order.

Despite all of this my pivot table will not sort by the Gross Sales in
either Largest to Smallest or Smallest to largest.

When I select a cell in the Gross Sales column and click on the sort
buttons (A -> Z and Z -> A) in the Pivot Table Tools ribbon, the
screen blinks as if the pivot table is being recalculated but the data
is not sorted. As a result I am having to copy the pivot table data
and paste elsewhere to sort it.. what a pain!!



I have a pivot table with the columns:




Number of Stores with Item out of Stock

Quantity Required

Extended Cost

Model Stock

Order At

For each vendor I want to show the total order cost, however I do not
want subtotals on any of the other columns.

That is I need to know that I have to place an order for $1000 to a
vendor but it doesn't make sense to say 15 hammers plus 30 drills
equals 45.

I found the following macro which was very helpful however I want to
know if there is a way to be selective about which data fields should
be subtotaled and which should not

Sub NoSubtotals()
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt
End Sub


Jim P


I would like to run a two factor ANOVA. The factors are called "density" and "target". Each has two levels: density (high and low) and target (long and short).

The dependent variable was "slope". Each subject performed in each condition of the experiment, so each condition (high density long target, high density low target, etc), had thirty data points.

I just want to run a two factor repeated measures ANOVA on these slopes. An online tutorial told me to prepare the data using pivot table.

I used pivot table to put these data into a table. I had several problems.

First, pivot table summed together all of my slopes. To prevent this, I used "subject number" as a field. This prevented excel from summing together the slopes (across subjects) within the conditions, but it caused another problem as now I have this other column adjacent to the columns of the slopes.

Anyway, I tried running the two factor ANOVA on these data, now sorted into the (four) columns of data. I just entered the whole array as a field, and then wrote the appropriate number of rows per column. But then Excel produced the error message ("each field must have an equal number of rows") and would not perform the ANOVA.

Can anyone help me with this? My email is I would greatly appreciate an example of some sort.

Thanks very much!

Hi all,

I don't really play around too much with Pivot Tables... and I can't seem to figure this one out.

Is it possible to show the DATA fields and their respective Counts in separate columns instead of each field below each other?


I'm trying to find a way of using VBA to open a pivot tables data in a new form by double clicking a cell that is not within the pivot table.

For example if i have a financial report and in a back sheet i have a pivot containing the same data I would like to be able to double click a number in the report and then this would activate the "drill down" of a particular cell within the pivot. The results would then be shown in a new User Form that has popped up (Form would be designed to show the same columns as the pivot table data).

I have seen this done and at one time had the file containing code, however this has been lost at some point between jobs.

The 2 steps I really need are:

- How to activate a drill down in a pivot by clicking a different cell
- How to show those pivot results in a user form rather than just a new sheet.

Hope this made sense, and hopefully someone can help even a little.



I have come up with a template to analyse data using a considerable number of pivot tables spread across multiple tabs. I wonder if there is a clever way to refresh pivot table data in the sheet, either when the tab is clicked or when the file is opened?


Excel 2003:

The PAGE filter feature in Pivot Tables, I am used to the field that you drop in there appear as a stacked set in each subsequent row in column A. I have picked up an old staff members pivot tables and the Field dropped into the PAGE filter area as running across a single row with a cell space between each.

Has anyone seen this before, and do you know where I can reset this to the way I am used too.

I have a report that where I cut and paste data source from another report. Sometime there are less rows then the day before and sometimes there are more row. When there are less row, the data source in the pivot table automatically change to the smaller number. However, when there are more rows added, it does not change and I have to manually change. It will not allow me to just select columns A:F. Is there a formula I can add that will allow me to select a large amount of rows and then it not change?

Currently The pivot table Data Source Table/Range : Sheet1!$A$2:$F$72
I would like it to read: Sheet1!$A$2:$F$1000 and not change.

Am I making sense??

I am unable to replicate Figure 5.1 in Chapter 5 (Pivot Table Data Crunching) using the source data as I am finding that the Sum of the Units Sold appears below the Sum of the Revenue.

How do I get the summed totals in two columns as in the picture (where Units is one column and Revenue is another). This would be most helpful yet I am unable to replicate.


Hello all.

I have created a pivot table and am attempting to have data in columns, but
I do not want the columns to total... rather just display the data.

For example... I have am tracking donations from events, and want the
donations to total, but do not want the columns containing the date of the
event or the contributor ID # to total.


[-]Campaign 1 - $140

[-] Event 1 - $65

Donor 1 - 11/18/08 - 13638 - $15.00

Donor 2 - 11/18/08 - 12450 - $50.00

[-] Event 2 - $75

Donor 1 - 10/25/08 - 13638 - $75.00

I need these columns in the "sum values" because I do not want a separate
column for each contributor ID or hire date. I also do not want to add all
of the extra rows if I were to add these fields to the "Row Labels" section.

How do I get the only specific columns to total?


How do I get columns to display different data in the same column and not
create a new column for each unique data?

Thanks in advance!

Canton, GA

I have 8 Pivot tables in a spreadsheet containing identical data with months in columns, account numbers in rows.. These Pivot tables represent the monthly expenses for 8 departments.I have created a standard Report in the same spreadsheet, to view the data with percentages and variance to Budget figures. The Report has a pick list, using data validation. By choosing any department in the list, I would like the report to look for the data in the specified Pivot table and produce the results.I have used "Name" for each Pivot table data range. I used Index and Match to look for a specific column number in the Pivot table. But in my formula I am limited to 7 "If" only. How could I achieve my requirements?


I have created a pivot table. Next to the pivot table are columns to enter
data and then additional clolumns to add the pivot table and not pivot table
data. I am running a filter on a column in the pivot table, and the non
pivot table data entered does not hold with the pivot table line. (For
example, I have an line in the pivot table (say line 20) call "item 1". I
enter a value in a Column outside the pivot table to correspond to item 1 on
line 20. When I filter on "item 1" - it now moves to line 5; however, the
amount I manually added to line 20 outside the pivot table stays on line 20,
but I want it to move to line 5 with the pivot table data

I create a pivot table from three columns of data, using the Wizard.In the
Layout dialogue, I drag one of my fields to the Rows; I drag anther field to
the Columns.
I need to drag a third field to the Data area. There's no conflict there,
each Data cell of the resulting matrix would be unique.
Excel doesn't let me do this. It insists on using a calculation, e.g. Sum of
[field3], Count of [field3], etc.

How do I convince Excel to insert just the value of Field3 in the Data?


Gershon Shamay

Hi ,

I have a doubt in pivot table that i am trying to solve it for a very long time, if anyone can help in this regard it will be really greatful. I have attached my excel file along with this thread.

I have two seperate datas that i would like to concatenate in a single pivot table.
Here in this sheet two columns are available one is 'name' and another is 'yes'. i just need to count the number of yes for the distinct name. Like that i would like to get the 'name' and 'no' column as well.

Here the question is whether its possible to get the two datas together and make a count of number of yes and no together.

Please lookin to the attachment...

I have a Pivot table with 40 names. Each has some numeric data in columns.
The names and data are extracted from other worsheets in the same workbook.
The Pivot table is then used to produce a Pivot Chart.

When I delete the names and data to start a new analysis I refresh the Pivot
Table and the Pivot Chart but the names still remain on the field name drop
down box. How can i remove these permanently?


In this pivot table report, I would like to show the data with standardized column format such as:

Reporting Quarter Reporting Period Site CCM targets total encounters.

However, in the pivot table report in the attached file, I couldn't get the "total encounters" column to display actual total number of encounters because it is using the same CCM target filter, which is set to = "yes".

That is why CCM targets column show exactly the same value as the total encounters column, which is NOT desirable.

So my question it possible to do what I need to do using pivot table? or is there any other way to do it that I'm not aware of?

Could anybody point me in the right direction please?

Thank you

I've got this pivot table, and for column headers I've got different weeks, like 821/11-8/27/11. Each week the pivot table gets updated and a new column gets added, and I'm wondering if there's an easy way to get a variance column between each of them showing the variance between any two weeks. Since I'm not trying to find the variance between two different fields, I haven't had any luck using the Pivot Table formulas---each week is not a separate field in the data set that is used for the pivot. There's just a field called 'week', and the values of that field form the column headers in the table. Is there any way to do this easily inside the pivot table?

Hi guys - this seems fairly simple and I should be able to figure this out, but I'm stuck and would appreciate any help.

My boss needs me to convert data that he has currently in two columns that looks like this:

A_________ B
Name____ John Smith
Company___ ABC Co.
Name_____ Jane Brown
Company___ ACME Inc.

There's a few hundred names that just repeat like this in "blocks" of 3 fields and he needs the data in columns for a data import into our CRM system i.e. in this format:

A___________ B___________ C
Name______ Company______ Email
John Smith___ ABC Co.______
Jane Brown___ ACME Inc.______

I've tried the TRANSPOSE function as well as messing with a Pivot table but am not having any luck. Would VLOOKUP be any good? Any help appreciated, thanks!

EDIT: Using Excel 2007.

I've got a pivot table with 26 columns and 1532 rows (based on data of 60
cols by 30,000 rows). In version 2000, when I add another field I get the
error message "Excel cannot make this change because there are too many row
or column items...". I've obviously hit a limit (clearly higher than the
8000 items mentioned in Help). If I upgrade to version 2002 or 2003, will
this problem go?

No luck finding an answer? You could always try Google.