Pivot Tables in Excel are one of the most powerful tools, and once you understand how they work, they’re actually quite easy to insert and modify.  In this example we’re going to work with a small amount of data for illustration purposes, but they are in their element with huge amounts of data laid out in a columnar format.

Forget Filters and Subtotal, Pivot Tables can do both of these and more in a few seconds.

Note: if you’d prefer to watch a video tutorial that will teach you how to create Pivot Tables, choose fields, Design, filtering, Modify data, and insert Pivot Charts sign up for our Microsoft Office Online Training

OK, looks like you’re happy to read the tutorial…..take the data below for example.

Excel Pivot Table raw data

Let’s say we wanted to SUM the number of viewers by program, going down the rows, then by Region going across the columns and only show Q1 (you can’t see it here but there is data in this table for Q1 through to Q4). The easiest solution is to insert a Pivot Table like this:

Excel Basic Pivot Table

Note: In the above example we summed the viewers, but instead of, or in addition to SUM, we can COUNT, AVERAGE, PRODUCT and more.

We can also change the formatting and customise the default ‘Row Labels’, ‘Column Labels’ and ‘Sum of Viewer’ headings to make the report more polished.  We’ll get to that soon.

How to Insert a Pivot Table in Excel

Download the workbook and practice as you go.

1)      Click anywhere in your data

2)      On the ‘Insert’ tab click the ‘PivotTable’ button and select ‘PivotTable’.

Insert Pivot Table button

3)      The Create PivotTable dialog box will open.

Create Pivot Table dialog box

a.       Excel will automatically select the range of data, but you can change this here if you need to by modifying the range in the Table/Range field.  You can even choose an external source but for most people using your own data will be all you want, so we’re not going to cover that here.

b.      Tell Excel if you want your Pivot Table in a New Worksheet or in the Existing Worksheet.  If you choose Existing Worksheet you will need to tell Excel the top left cell that you would like your Pivot Table to begin in.  If you choose New Worksheet Excel will insert a new worksheet in your file and insert it there.

4)      I’ve chosen to insert mine in cell F2 on the sheet where my data is for this tutorial.  Below is how your worksheet will look after step 3. In the right hand section of your screen the Pivot Table Field List window will open and a place holder will be entered beginning in the cell you’ve chose in the previous step, in my case F2:H19.

Pivot Table under construction

5)      The first thing you need to do is choose the fields you want included in your Pivot Table Report. We do this in the Field List window.

PivotTable Field List Window

a.       By ticking the Fields from the list you can tell Excel which fields you want in your report.

b.      By default it will add any labels to the ‘Row Labels’ area, and any columns it detects as values will go into the ‘Values‘ area.  To move them, simply drag and drop the fields to the area you want.

c.       If you’re inserting your Pivot Table on the existing sheet you will see it take shape as you make your selections in the Field List.

6)      To insert the Pivot Table shown in the above example, and below, my Field List looks like this:

Excel Basic Pivot Table

You can see I’ve set the Period Q1 (cell G1) as a ‘Report Filter’, the programs are my Row Labels, the Regions are my column labels and the Sum of Viewers are my Values.

Ok, now let’s look at how we can customise it.

Pivot Table Styles

In Styles enable you to make your Pivot Table cool with very little effort.

You’ll notice you now have two new tabs in your Ribbon.  Go to the Design tab and here you can choose from a huge range of predefined styles.  You can even save your own in keeping with your corporate image.

Excel Pivot Table Styles

Just look at how much more professional mine looks with a few clicks of the mouse.

Formatted Pivot Table with Pivot Table Styles

I’ve also changed my ‘Row Labels’ and ‘Column Labels’ headings by typing new names directly into the cell.

Preserve Formatting

You can also format your Pivot Table Report manually using the Fonts etc. on the Home tab of the Ribbon, plus you can resize columns and rows.

If you do this there are a few preferences you should set so that your formats aren’t lost on refresh.

1)      Right click anywhere on the Pivot Table.  Select  ‘PivotTable Options’.  The following dialog box will open.  Make sure you tick the ‘Preserve cell formatting on update’ preference and ‘Autofit column widths on update’ is NOT ticked.

Excel Pivot Table Options

Change Default Value Calculation

Remember at the beginning I said in the Values you aren’t limted to just SUM.  You can also COUNT, AVERAGE, PRODUCT and a few more.

By clicking on the down arrows beside the Report Filter, Column Labels, Row Labels or Values areas you can access tools that will allow you to modify the settings.  This is also where you can change whether the Values are SUM of, COUNT of, AVERAGE of and so on.

Excel Pivot Table Value Field Settings

To change the VALUES from the default, select ‘Value Field Settings’ from the list by clicking the down arrow beside, in our case, Sum of Viewers.

The ‘Value Field Settings’ dialog box will open and you can choose a different calculation from the list on the ‘Summarize by’ tab as shown below.

You can also give the field a custom name.

Excel Pivot Table Value Field Settings

Note: you can have more than one value in your Pivot Table.  For example, you might want to SUM and COUNT the values.  Simply drag the field you want from the ‘Choose Fields to add to report’ list in the Pivot Table Field List into the Values area and alter the Value Field Settings as listed above.  (See the next section for instructions on how to add another value with screen shots.)

Insert a Predefined Calculated Field

Excel has a list of predefined calculations you can select from.  Note: You can also insert a custom calculated field, but we’re not going to cover that here as I think these are better added to your source data and brought into your Pivot Table as a field.  It’s less prone to error with this approach.

To insert a calculated field from the predefined list available:

1)      First you have to add another Value field by dragging, in our case, ‘Viewers’ from the ‘Choose fields to add to report’ section down to the ‘Values’ area. You’ll notice that Excel will put a Values field in the ‘Column Labels’ area as well as the Values areas.  This is because it’s performing the calculation for each column of data.  You can change it to calculate for each row by dragging it to the ‘Row Labels’ area.

2)      Open the ‘Value Field Settings’ dialog box (click the down arrow and select it from the list) and select the ‘Show values as’ tab.

3)      From the drop down list you can choose the type of calculation you want.

4)      Give your calculation a custom name before clicking OK.

Excel Pivot Table Show Values As

In my example below I inserted a ‘% of total’ field and gave it the custom name ‘% of Viewers’.

Excel Pivot Table example of inserted field

Filters

What say we wanted to only show data for a few of our programs?  We can filter our row labels by clicking on the down arrow beside ‘Row Labels’.  And just like regular filters we can instruct Excel to only display the values we choose.

Filter Pivot Table

Note: in this filter we can also sort our row labels.

Filtered Pivot Table example

Filters can also be applied to column labels.

Changing the Orientation

You can have more than one field in each area. For example, what if we wanted to see the data grouped by region down the rows? It would look like this:

Change Orientation of Pivot Table

Simply rearrange the fields in the Field List by dragging and dropping the fields to the area you want.

Tools

You will also notice you now have two new tabs in the Ribbon for Pivot Table Tools; Options and Design.

Pivot Table Design and Options tabs

Source Data Rules

1)      No blank rows or columns.

2)      Each column must have a heading. This heading will be carried over to label your Pivot Table rows and columns.

3)      Make sure your source data is formatted correctly. That is if they’re dates, format them as dates and so on.

4)      If the Field List disappears click anywhere on the Pivot Table and it will reappear.

Download the workbook and practice what you’ve learnt.

Did you find this useful, or did it open a can of worms? Let us know by leaving a comment.

Want More

Why not visit our index of Excel tutorials. You’ll find a huge range of topics incuding formulas (all explained in plain English), plus more on PivotTables and other Excel tools and tricks. Enjoy :)

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 100 comments… read them below or add one }

MARTIN THOMAS July 28, 2014 at 4:24 pm

Respected Madam
how to create pivot table using flat data table

Thanking you
Martin thomas
9971535002

Reply

Mynda Treacy July 29, 2014 at 9:28 am

Hi Martin,

You can create a PivotTable with a flat data table just as you would with a tabular data table. I does have some limitations though, which I explain here under the heading ‘Flat Data Table’.

Kind regards,

Mynda

Reply

Robin July 18, 2014 at 12:24 pm

This website is really a great addition to online free sources of learning excel, I hope it has more and more contents.

Reply

Mynda Treacy July 18, 2014 at 12:33 pm

Thanks, Robin.

Reply

Maria Schneider June 26, 2014 at 12:35 am

Hello,
I am not so new to Pivot tables, but I always love to read all your posts … there is always something new I never tried.
But I did not find anything on “showing text in pivot tables”. Is there a way to force the pivot table to show “text”, not calculate something?
Or am I trying to do something which I could achieve much easier with another excel function?

Example:
I have projects sorted in a table:
Project Project Leader 1 Project Leader 2
ProjectA Justin Thomas
ProjectB Justin Philippe
ProjectC Mynda Philippe
ProjectD Justin Thomas

What I would like to have is like a cross table of the project leader combinations:
Justin Thomas Philippe
Justin Project A Project B
Project D
Mynda ProjectC

The only thing I know for sure is the possible combinations of project leaders.

Maybe you have an idea?

Thank you very much!
Maria

Reply

Catalin Bombea June 26, 2014 at 1:06 pm

Hi Maria,
Showing text in data fields is not possible in a pivot table, only in header row or column.
For that cross table, to create a formula to combine project leaders you have to provide the combinations algorithm , if you can upload a sample table with your data and details, i will try to help you to find a solution.
You can use our Help Desk to open a new ticket for support.
Catalin

Reply

gopiraj April 17, 2014 at 9:06 pm

it very useful

Reply

Mynda Treacy April 17, 2014 at 9:11 pm

Thank you, Gopiraj :-)

Reply

Manish February 7, 2014 at 12:57 am

I use pivots extensively to aggregate data at certain levels. Anyways, the input file that I use to create multiple pivots has undergone constant change i.e. column aa through ac are now added and original columns displaced, old columns deleted, and so forth.

Everytime a new column is added within the named_range of Pivot data — the pivot takes in the new column without being intelligent about the header that defines it, meaning it just takes whatever column now replaced the original column even if the orignal column is now next column — which has caused a lot of headache on my side, as I have formulas that are based on these columns. Questions – a) Is this how pivots work or there is something wrong that I am doign while refreshing pivots, and b) is there a smarter way to define formulas so that atleast they capture data from the right column (column with specific header)?

I’d appreciate if someone could shed more light here.

- Manish

Reply

Mynda Treacy February 7, 2014 at 8:05 pm

Hi Manish,

That sounds normal to me. The PivotTable just pulls in the data you give it, it cannot tell if the name of a column has moved/been renamed.

I recommend you use the GETPIVOTDATA function which looks at the field name as opposed to field position in the table. Unfortunately I don’t have any tutorials on this function but you can read about it here.

Kind regards,

Mynda.

Reply

Robert Singh January 12, 2014 at 11:11 pm

Dear Mynda,
Thanks for your wonderful comments for every separate problem. Its really helpful.
Could you assist me to develop a reporting template from where I can generate other reports?

For an example:
Children A (girl) ID: 2002 participated 4 programs in FY 2014
Children B (Boy) ID: 200 participated 8 programs in FY 2014
Children C (girl) ID: 302 participated 3 programs in FY 2014
Children D (girl) ID: 402 participated 6 programs in FY 2014
The programs are as follows: (Health, Education, Sponsorship, Economic, Nutrition, WASH, Rights, Against Violence etc)
Please be noted that there are more than 4000 children involved in different projects. How can I get all the related information at a glance ending the year. How the data import system would be develop?

It would be so kind of you if you kindly assist me out.
Thank you in advance for your kindness.
Robert

Reply

Catalin Bombea January 13, 2014 at 2:44 am

Hi Robert,
Can you upload a sample of your workbook, to see your data structure? What do you mean by “data import system” ? You want to import data from an external source? If so, please give me more details on what you are trying to do, i will gladly help you.
You can use our Help Desk: http://www.myonlinetraininghub.com/helpdesk
Cheers,
Catalin

Reply

abdullah December 19, 2013 at 1:22 am

Thanks for the Pivot table tutorials.
They are an amazing tool with power to filter out and summarize.
it was a situation in a project where nothing except manually filtering would take hours.
I dont know how i would have done without pivot tables. In my situation i used pivot table twice on the same data which gave me summaries which was amazing.
Thanks again for such a wonderful site and the tips.

Reply

Mynda Treacy December 19, 2013 at 11:00 am

:-) You’re welcome, Abdullah. I am a big fan of PivotTables too.

Reply

Kamran November 16, 2013 at 6:01 pm

Please be guided,
Insert a Predefined Calculated Field – Step 3
Drop Down List dosn’t have “% of Total”

Reply

Catalin Bombea November 17, 2013 at 4:07 pm

Hi Kamran,
Can you please be more specific? Predefined Calculated Fields are available for Values field, not for Row/Column Label, or Report Filter, maybe this was the problem;
Also, it’s very useful to know the version of excel you are using; a sample of your workbook would be great help for us to understand the problem, you can upload it via Help Desk
Cheers,
Catalin

Reply

Raza November 14, 2013 at 3:18 pm

@ Mynda,

Thanks for the reply and I re-read the tutorial and yes it is explained very well.

Thanks,

Raza

Reply

Mynda Treacy November 14, 2013 at 4:16 pm

Great :)

Reply

Raza November 13, 2013 at 5:38 pm

Hi Mynda,

Thanks, it is very easy to understand and learn, but please clarify, how did you get “Values” field under Column Labels as shown in the diagram above. Thanks a lot.

Reply

Mynda Treacy November 13, 2013 at 9:28 pm

Hi Raza,

Glad you liked it.

I explained how to get ‘Values’ under the column labels in Step 1 for Insert a Predefined Calculated Field.

Kind regards,

Mynda.

Reply

mano November 6, 2013 at 1:53 am

Hi mynda
thanks so much behalf website ,excellent
Regard

Reply

Mynda Treacy November 6, 2013 at 9:47 am

You’re welcome, Mano :-)

Reply

jdxdub November 1, 2013 at 9:30 pm

Interesting but daunting. Presume that I could use this for Sage and other accounting data.
Think it would need lots & lots of practice and not sure I have the time.

Reply

Mynda Treacy November 5, 2013 at 9:36 am

Hi Jdxdub,

It’s worth practicing to master PivotTables. I highly recommend it. Once you know how to use then you’ll find all sorts of applications for them.

Kind regards,

Mynda

Reply

manuchehr soleimani October 7, 2013 at 4:49 am

hi mynda,thanks so mauch

Reply

Mynda Treacy October 7, 2013 at 10:25 pm

You’re most welcome, Manuchehr :)

Reply

blessed October 3, 2013 at 6:02 pm

i have a challenge on coming up with a dashboard where do i start?

Reply

Philip Treacy October 3, 2013 at 9:01 pm

Hi Blessed,

If you want to learn Dashboards then you should take our course http://www.myonlinetraininghub.com/excel-dashboard-course which will teach you everything you need.

Regards

Phil

Reply

Tracey Davies August 7, 2013 at 3:51 am

Thank you so much for the tutorial it was brilliant!!

Not had to deal with Pivot Tables for about 16 years until I started my new college course in accounting, you’ve made my course so much easier now I’ve had a refresher.

Definitely going to recommend this site to the other students on the course

Reply

Mynda Treacy August 7, 2013 at 8:32 am

Thanks, Tracey :) It’s great to know we could help. Good luck with your course.

Reply

Rahul Moharil July 22, 2013 at 6:35 am

I really found it very useful and en jong the learning process at this website,thanks a lot for wonderful way of teaching,it is helping me to increase knowledge of excel in easier way

Reply

Mynda Treacy July 22, 2013 at 11:44 am

Thank you, Rahul. Glad you’re enjoying it.

Reply

Elkhan May 3, 2013 at 2:38 pm

Very useful and so accessible. Thanks a lot

Reply

Mynda Treacy May 3, 2013 at 3:21 pm

You’re welcome, Elkhan :)

Reply

bapai June 13, 2013 at 3:17 pm

very helpful. loolking for more tips

Reply

Mynda Treacy June 13, 2013 at 4:54 pm

You’re welcome, Bapai :)

You can find a list of Excel Formula tutorials here.

Reply

Nishal April 25, 2013 at 7:07 pm

Hi,
Could you please help me how we can use Getpivotdata and its uses . I have a standard download for sales.

Thanks
Nishal

Reply

Mynda Treacy April 25, 2013 at 7:53 pm

Hi Nishal,

The GETPIVOTDATA function extracts data stored in a PivotTable. Unfortunately I don’t have any free tutorials I can point you to at this stage but here is some information from Microsoft.

I hope that helps.

Kind regards,

Mynda.

Reply

Nicola Wilson March 31, 2013 at 10:31 pm

Hi

This was really useful and in the main, pretty clear. I’m sure if I practiced it would all seem very obvious very quickly but I never seem to have a working example when I need one and then when I do have an example, I’m not sure it’s a pivot table I need to solve the problem. My main issue is terminology – I still think I’d struggle with a “cold” exercise (nothing to copy) with deciding what to put in each “Field”. Also, in this example I went with the table range that it auto selected but I’d really like to know how to choose this.

However, overall, very grateful for the course and I will store the link somewhere for future reference and practice!!

Reply

Carlo Estopia April 1, 2013 at 10:40 pm

Hi Nicola,

On behalf of Mynda,
You’re welcome!

Cheers,

CarloE

Reply

Nguyen Thi Hong Chau March 29, 2013 at 2:39 pm

Thank you very much!

Reply

Carlo Estopia March 30, 2013 at 10:40 pm

Hi Nguyen,

You’re welcome on behalf of Mynda.

Cheers.

CarloE

Reply

Madalene Ransom March 20, 2013 at 2:10 am

gosh. These were very good examples. I am looking forward to your Pivot Tables explanation. Microsoft should hire you! Thank you very much!

Reply

Carlo Estopia March 20, 2013 at 10:08 am

Hi Madalene,

On behalf of Mynda,
You’re welcome!

Cheers.

CarloE

Reply

susan March 17, 2013 at 12:20 am

It’s such a great help. Thanks a lot.

Reply

Carlo Estopia March 17, 2013 at 9:09 pm

Hi Susan,

On behalf of Mynda, You’re welcome.

Cheers.

CarloE

Reply

john nicholson March 7, 2013 at 3:00 am

Very useful website, explain in clear concise terms. One minor area which I encounter is the control of the Value field when I want it to go across as a series of sum of’s… but it seems to insist on going down the column leaving only a single total column. Maybe others have asked about it too.

Regards

John

Reply

Carlo Estopia March 7, 2013 at 6:00 pm

Hi Jack,

I suspect you don’t have column labels.
To avoid this, you must put your column label ahead
of your values when setting up pivot tables.

Cheers.

CarloE

Reply

Danielle Christou February 26, 2013 at 2:36 am

That was very useful! it does not however help me. I need to show the values as they are rather that a count or a sum. I have a list of dates and names with a score (1 – 4) i need to show in date order that score each name got – some names have more than 1 score per day! Can anyone help me with this?

D

Reply

Carlo Estopia February 26, 2013 at 11:05 am

Hi Daniel,

I have here a not so elegant solution. In your source data/table, you should add one more column.
The column is for creating a uniqueness to each of your score within the day. You may do this
by adding 1 to the first entry in that column then in the second entry of that column
add a formula like =D2+1 then get the handle and drag it down to the last entry– I think you know
this already.lol

To illustrate using our given example: I added the column ‘Index’.

Program	Region	Period	Index	 Viewers 
Bat Man	North	Q1	1	 91 
Bat Man	South	Q1	2	 87 
Bat Man	West	Q1	3	 99 
Bat Man	East	Q1	4	 102 
Ben Ten	South	Q1	5	 125 
Ben Ten	West	Q1	6	 140 
Ben Ten	East	Q1	7	 107 
Ben Ten	North	Q1	8	 133 

Now, you add the Period to the Report Filter, The Region to the Columns,
Add Index(As Count) and Program to Row Labels, and Viewers to Values.
PT would look like this.

Period	(All)				
					
Sum of Viewers	Column Labels				
Row Labels	East	North	South	West	Grand Total
Bat Man	        523	435	476	718	2152
1		        91			91
2			        87		87
3				        99	99
4	        102				102
29		        136			136
30			        81		81
31		            		118	118
32	         97				97
57		        127			127
58			        256		256
59				        260	260
60	        237				237
85		        81			81
86			         52		52
87				        241	241
88	         87				87
Ben Ten	        539    530	557	476	2102
5			        125		125
6				        140	140
7	        107				107

It’s not elegant but It will give you the results. Be sure to sort your tables properly before
putting that unique index.

Cheers.

CarloE

PS: In your case, you might want to add Names and the Index as Count to the row Labels. Dates to your Report Filter perhaps.

Reply

Karen Orr February 24, 2013 at 12:16 am

excellent presentation!

Reply

Mynda Treacy February 24, 2013 at 6:34 pm

Thanks, Karen :)

Reply

Cheryl February 23, 2013 at 6:24 am

I’ve used pivot tables to analyze large amounts of data. However, I am wondering if there is any way to use something similar to a pivot table or formulas to add repetitive data to a table so I don’t have to manually copy it. I have approximately 19,300 rows of data and I am trying to organize by groups of similar data based on the NAIC code. The fields I want to populate are: [a]Segment number (2-digit number to identify the segment), [b]Segment description, [c]NAIC code (this is a 2 to 6 digit number which identifies a product or service) and [d]NAIC description. Columns [c] and [d] are provided. I want to groupd these columns and add the repeating data in columns [a] segment number and [b] segment description. I started with an IF formula as follows: =IF(C49<119999,"11"," ") which retuned the segment number "11" for everything with a code from 11-119999. However, there are hundreds of rows that fall in this range. There are also thousands of rows with codes from 11 through 922180. The first two digits represent the segment. Is there any way to apply the formula to the entire column and pull out the specific 2-digit segment number to place in column [a] other than repeating the formula for each group of numbers and copying the formula over the many rows which isn't very automated. HELP!!!!

Thanks,
Cheryl

Reply

Carlo Estopia February 23, 2013 at 9:00 pm

Hi Cheryl,

Maybe you need to use LEFT function.

Try this one.

=LEFT(C49,2)

However, I really did not quite get what you were asking here.
So I suggest you send your file via HELP DESK if you have further clarifications.

Cheers.

Carlo

Reply

Quel February 22, 2013 at 12:23 pm

Hi Mynda… GREAT SITE!!

i’ve been working with large data set and using pivot tables… one thing that would really help is to do other calcs on these data sets. specifically, i’m trying to get median value from a specific subset (filtered).

for example, I filter out all entries that are <1mi distance, and from those results of the filter, i want to determine median value of a specific column (i.e., sale price).

is there a way to calculate median based on criteria without recreating the data table and doing the manual calc of =median() for a column of that data table?

thanks!

Reply

Carlo Estopia February 22, 2013 at 7:40 pm

Hi Quel,

I tried but when in times like this one, I usually suggest VBA.

So why don’t you send that file to me through HELP DESK and I’ll solve
your problem.

Cheers.

CarloE

Reply

Erika February 15, 2013 at 1:08 am

Hello,

The example charts are missing beginning at #5 and below. Once you put the examples on the screen, the information will be even more helpful.

Thank you!

Reply

Mynda Treacy February 15, 2013 at 9:05 am

Hi Erika,

I’m not sure what you mean by ‘example charts’. This is about PivotTables not charts. Do you mean images? If so I can see several images below point #5. Can you please be more specific about where information is missing for you.

Thanks,

Mynda.

Reply

Michelle December 9, 2012 at 9:12 am

Why use pivot tables when information could simply be copied and pasted to a new list?

Reply

Mynda Treacy December 9, 2012 at 8:14 pm

Hi Michelle,

PivotTables summarize the data. They aren’t simply a duplication of the original underlying data therefore copying and pasting source data wouldn’t achieve the same as a PivotTable.

Kind regards,

Mynda.

Reply

Erick December 6, 2012 at 1:02 am

I couldn’t download the workbooks. Was really looking forward to practicing with them. I like the way you explain the pivot table process. Good job!

Reply

Mynda Treacy December 6, 2012 at 8:18 pm

Cheers, Erick.

Sorry you couldn’t download the workbooks. This problem usually occurs with Internet Explorer. It changes the file extension to a .zip file but the actual file extension is .xlsx

Perhaps you can try to download them again and at the ‘save as’ screen make sure the file extension is .xlsx before saving.

Kind regards,

Mynda.

Reply

Gina Lopez October 16, 2012 at 1:58 am

I know how to pivot, but now I need to filter and pivot the same data source between a set of dates and a set of values. For instance. date – anything less than 9/01/12 and values, price – anything less than $5000.00. I have been stuck on this database for quite some time and would greatly appreciate any help you can give me.

Reply

Mynda Treacy October 16, 2012 at 1:25 pm

Hi Gina,

You can use the filter buttons on the PivotTable to filter the dates and then the values. If you’re stuck you can send it to me and I’ll take a look.

Kind regards,

Mynda.

Reply

Ruth September 13, 2012 at 12:18 am

Hello. I just came accross this website and I am finding it very helpful!! Great tips and all are explained really well. I have a question which I hope you can help me with – my apologies if this has been asked and answered before. If you receive a list which has the information that you want to filter by only listed at the very top and not on each row is there any way of getting this information on every row without copying and pasting the information to each row?

I have an example included below which I hope shows up correctly. So I want to be able to filter by the title ‘ABB001, CustomerA’ but that only appears in one row and all of the sales invoices details for this customer and in the rows after it. Is there any quick way of getting ABB001,Customer A on each row without copying and pasting it down? And is it possible that whatever formula might help with this would also be able to work the whole way down through a list – so would put ABB001,Customer A beside the first 3 rows and then recognise the break to customer ABB002, Customer B and change to pasting ABB002, Customer B to the next rows that relate to Customer B? The lists I am dealing with usually have thousands of line items with hundreds of customers and it would be great if there was someway to do this quickly.

Our Ref Your Ref Trans. Date Total
ABB001, Customer A
SI001 1 12/09/2012 100.00
SI002 2 12/09/2012 100.00
SI003 3 12/09/2012 100.00

ABB002, Customer B
SI009 21 12/09/2012 100.00
SI010 22 13/09/2012 100.00
SI011 23 14/09/2012 100.00
SI012 24 15/09/2012 100.00
SI013 25 16/09/2012 100.00

I hope this query makes sense and any help you might be able to provide would be greatly appreciated!!! Thank you :-)

Reply

Mynda Treacy September 13, 2012 at 7:07 pm

Hi Ruth,

Thanks for your kind words.

Let’s say your data starts in cell B1 with your headers. In column A, which is blank, you can enter the following formula starting in cell A3:

=IF(ISBLANK(C2),B2,A2)

Copy it down > paste column A as values > sort data to group blanks and customer numbers in column B together and delete them.

I hope that helps.

Kind regards,

Reply

Ruth September 14, 2012 at 12:27 am

That worked!!! Thank you so so much :-) You have just saved me hours of work :-)

Reply

Trainer September 11, 2012 at 6:16 am

great site, easy to follow and very easy to understand.

Reply

Mynda Treacy September 11, 2012 at 9:00 am

:) Thanks.

Reply

srini August 19, 2012 at 4:36 pm

Hi

Excellent presentation. Very Very useful. Thanks a lot.

Reply

Mynda Treacy August 19, 2012 at 9:41 pm

You’re welcome, Srini.

Reply

Lita July 31, 2012 at 12:30 pm

Hi! I would like to know why the fields of a pivot table I create show up as rows beneath another row instead of columns.

My fields are dates, proposal numbers (5 digits), name of the salesmaner, the proposal amount and the description of what was sold. Instead of having 5 columns, rows show up. I would prefer 5 column headings not 5 rows which show up in hierarchy.

Thanks.
Lita

Reply

Mynda Treacy August 2, 2012 at 10:51 pm

Hi Lita,

If you want fields to show up as columns then you need to drag the field into the Column Label area. If you click the check box to choose your fields Excel will insert them where it thinks they fit best. I like to click and drag them into the area I want them in.

I hope that helps.

Kind regards,

Mynda.

Reply

Jennifer Barajas July 24, 2012 at 11:55 pm

I downloaded the zip file but I am not sure which fo,lder or file I am supposed to click on or open to get the sample workbook. Al the files in the zip folder have an .xml extension.

Reply

Mynda Treacy July 25, 2012 at 8:29 am

Hi Jennifer,

The file that you download is not a zip file, it’s an Excel file with a .xlsx file extension. If you hover your mouse over the link you can see in the bottom right/left corner of your browser that the file extension is .xlsx

Some browsers, including Internet Explorer, change the file extension to a .zip when you save the file. All you need to do is download the file again except at the ‘save file as’ window (or similar depending on your browser), change the file extension by deleting the .zip and inserting .xlsx

Once downloaded you should be able to open the Excel file as you would any other Excel file.

Kind regards,

Mynda.

Reply

abdul June 30, 2012 at 3:12 pm

this is good, but in pivot table we raise any iformation then format. if you show tips pivot table with dashboard report

Reply

Mynda Treacy July 1, 2012 at 7:32 pm

Hi Abdul,

I don’t like to use PivotTables in a Dashboard report as I find them too inflexible and chunky most of the time. I prefer to use them for my analysis and then link to the PivotTable results from my Dashboard. You can see some examples of my dashboards in our Dashboard Course.

Kind regards,

Mynda.

Reply

Susan June 1, 2012 at 4:56 am

Hi I am wondering whether you know if the data source for a pivot table can have 2 HEADER ROWS? I am desperately trying to work out how to do this.

Thanks

Reply

Mynda Treacy June 1, 2012 at 8:44 am

Hi Susan,

As far as I’m aware you can’t have 2 header rows. PivotTables use the header row to create the list of fields. The second row in your table is interpreted as part of your data.

Kind regards,

Mynda.

Reply

Joe Schlosser May 24, 2012 at 11:46 am

When I create a pivot table with multiple Entries under Row Labels (for example Item Number and description), I can get them to be together by choosing field settings / Layout & Print / Show item labels in tabular form. The problem is that it does not replicate the first item (in this example Item Number on every line but leaves the line blank if it’s a duplicate. How can I get the table to fill in every line even if it’s a duplicate?
Thanks,
Joe

Reply

Mynda Treacy May 24, 2012 at 7:49 pm

Hi Joe,

This is one of the limitations of PivotTables pre Excel 2010. If you’ve got Excel 2010 then you can edit the Field Settings > Layout & Print tab > Repeat item labels.

If you don’t have Excel 2010 then your only option (aside from buying it) is to copy and paste the PivotTable as a value, so that what you’re left with is no longer a PivotTable.

> hightlight the range you want the labels repeated in
> CTRL+G to open the Go To dialog box
> Press the ‘Special ..’ button at the bottom of the Go To dialog box
> Select ‘Blanks’ OK
> Press = then press the up arrow, and then press CTRL+ENTER.

This will populate your blank cells with the data from the cells above.

I hope one of those options is suitable.

Kind regards,

Mynda.

Reply

Joe Schlosser May 25, 2012 at 2:12 am

Thanks very much for your help. I’ll look forward to getting 2010 but in the meantime try what you have suggested.

Reply

Peter Beutler April 29, 2012 at 10:33 am

When I try to download the workbook from the lessons, I’m only getting the the XML language, not the actual Excel file.

Can you give me some help. I don’t know how to work in the XML format nor I’m I a programer.

Thank you
Peter

Reply

Mynda Treacy April 30, 2012 at 10:29 pm

Hi Peter,

Your browser is changing the file extension. The file is an Excel workbook so the file extension is .xlsx

Make sure when you choose ‘save file as’ (or similar depending on your browser) that you ensure the file extension is .xlsx and all should be fine. If not let me know via the Contact Us page and I’ll email it to you.

Kind regards,

Mynda.

Reply

mohammed zainal sarwar April 27, 2012 at 7:50 am

I would really appreciate if i will be able to learn pivot table with the assistance of this site.thanks.

Reply

Sara V April 1, 2012 at 11:54 pm

1. I am collapsing rows but this feature automatically collapses rows that contain the same information. Can this feature be turned off.

2. For some reason when I save my pivot table and reopen all my filters change to all and information is lost.

Reply

Philip Treacy April 4, 2012 at 6:57 am

Hi Sara,

I’d say there are some settings in your PivotTable Options that are the reason for these issues. To edit your PivotTable Options right click anywhere on the PivotTable and select PivotTable Options.

Otherwise without seeing your file I can’t tell what the cause is.

I hope that gives you some direction.

Kind regards,

Phil.

Reply

manoj kumar dabgotra February 19, 2012 at 2:14 pm

thanx, its realy help us too co op up in the copreate world

Reply

lc October 27, 2011 at 10:21 pm

Pivot table sorts the row labels in alphabetical order. How can I customise this eg. show
wiggles
bat man
night garden etc
Thanks

Reply

Mynda Treacy October 28, 2011 at 10:23 am

Hi lc,

You can manually customise the order of your row labels:

1. select the cell containing the row label you want to move
2. move your mouse to the bottom edge of the cell until your mouse pointer changes to a 4 sided arrow.
3. left click your mouse and drag your row label to the new position.

Now when you refresh your PivotTable it will keep your order.

Kind regards,

Mynda.

Reply

mike October 10, 2011 at 4:09 am

thnnx

Reply

Lori October 7, 2011 at 4:38 am

Helpful tutorial – thanks

Reply

Mynda Treacy October 7, 2011 at 2:13 pm

Thanks Lori :) Glad you liked it.

Kind regards,

Mynda.

Reply

Sadiq Ali August 21, 2011 at 8:35 pm

I found the website very productive and supportive. I can’t stay without appreciating the site.

Reply

Karen Stacey August 16, 2011 at 11:44 pm

can pivot tables count the times names/words appear in cells of rows or columns rather than count or sum numbers

Reply

Mynda Treacy August 17, 2011 at 9:11 am

Hi Karen,

Thanks for your question.

PivotTables can count the instances of the same text string. A text string can contain one word or many. However they can’t single out a word from a text string and count it.

For example:

Cell A1: The quick brown fox jumped over the log
Cell A2: The quick brown fox jumped over the log
Cell A3: The quick brown fox jumped over the big log

A PivotTable would count 2 instances of “The quick brown fox jumped over the log” and one instance of “The quick brown fox jumped over the big log” but it couldn’t count how many times the word “brown” appeared.

Formula solution:

To count the instances of “brown” you could use the following array formula (where cell B2 contains the word “brown”):

=COUNT(IF(FIND(B2,A1:A3)),1,”"))

Remember: because it’s an array formula you need to enter it with CTRL+SHIFT+ENTER for Excel to calculate the result correctly.

Kind regards,

Mynda.

Reply

Niki March 18, 2011 at 10:32 pm

is it possible to delete certain cells from a column of a pivot table and insert your own formula?

Reply

Mynda March 30, 2011 at 12:39 pm

The short answer is no. You can however add a custom calculation column with your own formula, or you can add a column to your source data that contains the custom calculation you want to include.

Reply

Jolene November 10, 2010 at 12:12 pm

oww. my head hurts. I knew about pivot tables but have never really used them, but will read through this again and do some test scenarios.

thanks

J

Reply

Natalie November 5, 2010 at 10:52 am

great diagrams, makes it v clear. thanks

Reply

Previous post:

Next post: