Pivot Table

Pivot Table

A pivot table is an incredibly powerful tool in Excel that can be used to analyze, explore and summarize your data.

Pivot Tables are in their element with huge amounts of data and allow you to create reports from this data in seconds.

Below is a list of our best tutorials on Pivot Tables.



Comments

  1. Minglei Chen says

    Hi Mynda,

    I created a pivot table, and inserted few new columns next to pivot and put in some comments on each row next to pivot table.

    The root cause of my problem is that those edits on each row outside pivot table is not linked to the row within the pivot table, so when i add some new data and refreshed the pivot, the information move to new rows in pivot table, but the “matching” information outside pivot stayed on the old row, resulting a mis-match.

    I would like to have those comment to move together with the pivot, my question is how do i tag the comment on each row outside pivot with each row within pivot? or is there better alternative to achieve what i want to do? Many thanks

    • Catalin Bombea says

      Hi,
      Please upload a sample pivot table with details, i have to see your formulas , it’s hard to give you an advice without seeing the data structure and a desired outcome.
      Use our Help Desk to upload a sample file, i’m sure we can find a solution for you..
      Catalin

  2. Charles says

    This is my question. I use Pivot Tables. I like them. But this is my problem.
    I open a file on a server at work. Save it to excel. I then create a query to this file and select the columns that I want in my query. Once I do this I open the table, I insert a column. I type in some data.
    I save it.
    The Next day I open the file on the server. Save it to the excel file. Close it.
    I open the excel file that was created from the query. I hit refresh.
    It wipes the data I type in out.

    Is there a way to prevent this from happening?

    • Mynda Treacy says

      Hi Charles,

      I can’t see anywhere in your process where you write the new data back to the ‘file on your server’. So, the next day when you open the file on your server you’re looking at the original data, not the new updated data. Refreshing the query brings in the original data from the server again, not the updated data you created in your Excel file.

      I think it’s the process of ‘inserting a column and typing in some data’ but not sending that data back to the server that seems to be the problem.

      I hope that helps you troubleshoot a solution.

      Kind regards,

      Mynda.

  3. Victor Novoa says

    Hi Mynda. Going through the Dashboard course slowly. I would like to ask if anybody has a dashboard to dynamically capture and present beginning budget, current usage and amount left of overtime for 8 units (users) with a total of 155 employees. This dashboard has to be updated and presented every two weeks. I already have a tabular excel file with all the units, employees, bi-weekly pay periods and overtime hours worked for the first two periods of this fiscal year. I would like to show usage by unit and employee, regardless of the unit, how much overtime used and how much budgeted overtime remains at the end of each pay period. Furthermore, I would be adding tasks by unit and employees and show which units/employees are the most efficient when performing these tasks with overtime. I would appreciate any ideas.

    • Victor Novoa says

      Mynda, the 8 units and 155 employees is the population I am working with right now. I can try to adapt any templates or similar examples to my case. I have gone over the examples in the course and I am far away from producing something similar. Thanks.

      • Mynda Treacy says

        Hi Victor,

        I don’t know of any templates that are similar to your requirements that I could recommend. Since you haven’t finished the course I recommend you complete it so that you have an understanding of how you can use Excel to create the dashboard that you want.

        Once you’ve completed the course start by mocking up the different measures you want to report in your dashboard on a piece of paper. This will give you a layout for your dashboard and you can then go about analysing your data and creating your charts and tables required to complete the dashboard.

        If you have any specific questions along the way I’d be happy to help. You can email me via the help desk.

        Kind regards,

        Mynda.

  4. Jeremy Creech says

    Hi Mynda,

    I am currently working my way through your excellent Dashboard Course and have come across an issue which I haven’t been able to sort out so would appreciate your assistance.

    I have a data table set up with columns containing year, month, date and then columns with the various types of material we mine by each pit (eg Pit 1 Waste, Pit 1 Ore, Pit 2 Waste, etc) with the quantities going down by row against the relevant year, month and date. Is there a way I can set up a slicer in a single pivot chart so that the user can select the desired material mined (ie Pit 1 Waste, etc) against the relevant year and month slicers, which work fine, without me having to create a separate chart for each material type mined.

    I have played around with the format of the data table (I have adopted your suggested tabular format) but so far had no success other than getting one or more of the selected material types appearing in the one pivot graph at the same time.

    Alternatively, is there another way to achieve the desired result.

    cheers Jeremy

    • Mynda Treacy says

      Hi Jeremy,

      Great to hear you’re enjoying my Dashboard course :)

      You need one column that contains the ‘type of material’. Not separate columns.

      I would also recommend having just one column in your source data that contains the date. When you split the year, month and day into separate columns Excel may give you erroneous results. For example, March 2012 and March 2013 are both ‘March’ because you have the year in a separate column.

      I would put your date into one cell and use the Group tool in the PivotTable to group them into months and years.

      If you’re still having trouble please send me your workbook via the help desk and I’ll take a look.

      Kind regards,

      Mynda.

  5. Eric Stut says

    Hi Mynda and collegues,

    Your site, blog posts and tutorials are very helpfull. Nice…thank you!
    I’m rumbling around to find a way to add columns in a Pivot table. Situation: 40 columns named A1, A2, A3 till A10 en then columns B1, B2, B3 till B10 and columns C1, C2, C3 till C10 and D1, D2, D3 till D10. I’ve to add column A1, B1, C1 and D1. Then A2, B2, C2 and D2 and so on. Is there a pivottable trick??
    Thanks in advance for a solution.
    Kind regards. Eric

    • Carlo Estopia says

      Hi Eric,

      You just need to add a calculated field.

      Here’s what you need to do:
      1) Select/Activate your pivot table.
      2) By selecting it, it will expose the Options ribbon.
      3) Select the Options ribbon.
      4) In the Options ribbon, select Formulas-Calculated Field
      5) In creating the formula, simply add your columns/fields like this:
      =c1+c2+c3+c4

      Make sure that there is a unique field/description in your source data
      as this will segregate your calculated field results.

      For example:

      ID	Col1	Col2	Col3	Col4
      A	1	2	3	4
      B	1	2	3	4
      C	1	2	3	4
      D	1	2	3	4
      

      Cheers,

      CarloE

  6. Funmi says

    I find your website quite useful and I have added it to the list of my favorites at work and even on my personal laptop.

    Thanks for all the tips.

  7. Anita says

    Hi — I would like to get the median (instead of mean) calculated in my pivot table because I have highly skewed data. Do you know of any effective work around? Thanks.

    • Carlo Estopia says

      Hi Anita,

      I have tried to search for answers but the best thing to do is calculate median outside of the Pivot Table.

      There may be some VBA workarounds here but it is outside the support we are providing. The good news is that
      we are currently building a VBA program for our site.

      Cheers.

      CarloE

  8. Nik says

    I have many pivot tables and want to assign dynamic print area to all those. The print area should change automatically with the change in data. How to do it. There may be null row values in some case.

  9. Hamilton says

    I have two sets of spreadsheets which at the end of the month i have to integrate, the challenge is for me to integrate the two i need a sum if formular which incorporates two variables

  10. Shekher says

    Please provide me pivot table how to edit and ennter calculation field, and also details of array formula all type, and all details of database function.

    I am regular reader of this site.

    Thanks
    Chandrashekher

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 *