Excel Pivot Tables Explained

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 :)

FREE PDF Download
100 Excel Tips & Tricks

Excel Tips & Tricks E-Book
Just enter your details below
* indicates required

We respect your email privacy

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *

Comments

    • Mynda Treacy says

      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

  1. Robin says

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

  2. Maria Schneider says

    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

    • Catalin Bombea says

      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

  3. Manish says

    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

    • Mynda Treacy says

      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.

  4. Robert Singh says

    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

    • Catalin Bombea says

      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

  5. abdullah says

    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.

    • Catalin Bombea says

      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

  6. Raza says

    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.

    • Mynda Treacy says

      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.

  7. jdxdub says

    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.

    • Mynda Treacy says

      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

  8. Tracey Davies says

    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

  9. Rahul Moharil says

    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

  10. Nishal says

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

    Thanks
    Nishal

  11. Nicola Wilson says

    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!!

  12. says

    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

    • Carlo Estopia says

      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

  13. Danielle Christou says

    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

    • Carlo Estopia says

      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.

  14. Cheryl says

    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

    • Carlo Estopia says

      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

  15. Quel says

    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!

  16. says

    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!

    • says

      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.

    • says

      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.

  17. Erick says

    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!

    • says

      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.

  18. Gina Lopez says

    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.

  19. Ruth says

    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 :-)

    • says

      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,

  20. Lita says

    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

    • Mynda Treacy says

      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.

  21. says

    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.

    • Mynda Treacy says

      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.

  22. abdul says

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

    • Mynda Treacy says

      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.

  23. Susan says

    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

    • Mynda Treacy says

      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.

  24. Joe Schlosser says

    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

    • Mynda Treacy says

      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.

      • Joe Schlosser says

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

  25. Peter Beutler says

    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

    • Mynda Treacy says

      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.

  26. mohammed zainal sarwar says

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

  27. Sara V says

    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.

    • says

      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.

  28. lc says

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

    • Mynda Treacy says

      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.

  29. Karen Stacey says

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

    • Mynda Treacy says

      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.

    • Mynda says

      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.

  30. Jolene says

    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

Trackbacks