fbpx

Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Excel pivot tables are very useful and powerful feature of MS Excel. They are be used to create instant summaries, reports and data analysis from your raw data

In this page, learn all about how to create an Excel pivot table and customize it.

Table of Contents

What are Excel Pivot Tables?

A pivot table turns your data into report format. Here is a sample Pivot table from sales data, showing total sales by region.

Example of an Excel Pivot Table

How to create a Pivot Table?

We will use 2019 sales data of a fictional company. This data contains 466 rows of sales information in columns – Month, Salesman, Region, Product, No.  Customers, Net Sales, Profit / Loss. Here is a preview of our data.

Sample data - Pivot tables in Excel

Download the sample data & example pivot tables here.

To create a pivot table showing totals sales by region, follow these steps.

  1. Select any cell in the data.
  2. Go to Insert ribbon and click “Pivot Table” button.
    Insert Pivot Table button - Excel ribbon
  3.  Click ok on the next screen.
    Create Pivot Table dialog
  4. You will be taken a new spreadsheet with blank Pivot Table canvas.  Here, using the Pivot Table Fields panel set “Regions” field to row label area, “Products” to “Filter” area and “Net Sales” to values area. See below illustration.
    how to use pivot table fields - setting up a pivot table

Your pivot table will be ready. We can see that “West” is our best region. This is why Pivot tables are easy for finding answers to common business questions.

Two dimensional Pivots - Row & Column fields

You can add fields to both “Row” and “Column” label area of a pivot. Such Pivot Tables are normally called two dimensional pivots. Here is a demo of a two dimensional pivot table showing Total Sales by Region & Sales Person.

Two dimensional pivot - demo

Multi-dimensional Pivots - Row & Column fields

You can also add more than one item to “Row” or “Column” label area. This creates a multi-dimensional Pivot Report. Here is one such pivot report showing total sales by Region, Sales Person & Product for selected months.

Example Pivot Report - A very detailed Pivot Table with sub-totals and totals

 

How to format Pivot Table values?

By default, numbers in Pivot Tables tend to just look like zip codes, without any proper formatting. This is easy to fix though. Simply right click on the values and use “Value Field Settings” to set up the formatting. To set currency formatting for our Total sales by region Pivot Report, 

  1. Go to value field settings
  2. Click on Number Format button
  3. Set up the formatting to “Currency”
  4. Done.

See this illustration.

How to currency format pivot table values

 

Sorting in Excel Pivot Tables

You can easily sort pivot report by ascending or descending order of the value. To do this, just right click on the value, select Sort > and specify the order.

Here is an example of sorted pivot report of Number of customers by Sales person.

Sorting a pivot table in descending order of sales

Filtering Excel Pivot Tables

You are looking at Regional total sales and want to know what the total is for just “RapidZoo” product. You can do this by filtering the pivot table. Excel offers two powerful ways to filter Pivot Tables

  1. Report filters
  2. Slicers

Both methods are illustrated below. Read on to learn how to use them.

Filtering an excel pivot table - two methods

Filtering with Report Filters

Report filter is a great way to restrict the data that is flowing to your pivot. To set them up, just add the field to “Filters” area in the fields panel. Now, using the filter button next to “Product”, select the product you want.

Here is a quick demo of report filters in action.

Report filter demo

Filtering with Slicers

There are a ton of cool features in Excel Pivot Tables, but slicers are hands-down the best feature. At-least, that is what I think. They make filtering and ad-hoc data analysis a breeze.

A slicer is a visual filter. You can add a slicer on any field by right clicking on it from the fields panel. See the illustration “Adding filters to a pivot report” from above. 

Once you have a slicer on Product, simply click on any product name to see the report for that. 

Here is a quick demo of Pivot Table with slicers.

Filtering a pivot table with slicer - demo

Other kinds of filtering - Value & Label Filters

Apart from report filters & Slicers, Pivot Tables also allow you to filter by a field or value. 

Field or Label Filter: If you don’t want to see “Middle” region in a row label area, just click on the filter button next to “Row Labels” and uncheck the region. This type of filtering is called Label Filtering.

Value Filter: If you want to see just the top 2 regions by total sales, then you need a value filter. Simply go to filter button next to row labels and using value filters, apply a top 10 filter but set it to top 2 values by “Sum of net sales.”

Changing Calculations in Pivot Tables

The default calculation in Pivot Tables is SUM for number fields and COUNT for all others. But you can also customize the calculation easily. Just right click on the value field and choose different type of summary from right click menu.

Changing from SUM to AVERAGE in a Pivot Table

Here is a quick illustration of how to change calculation type from “SUM” to “AVERAGE”.

how to change pivot table calculations

Pivot Table Layouts & Colors

By default, Excel Pivot Tables are in compact layout. This means, if you add multiple fields to row label area, they will all be shown in same column, with indentation.

You can change the layout of a pivot table to other formats too. 

  • Compact form (default)
  • Outline form
  • Tabular form

You can change the layout from Pivot Table Design ribbon.

Pivot Table layout options

Here is an example of same Pivot Table in both Compact and Tabular layouts. 

Compact vs. Tabular Layouts for Pivot Tables in Excel

Styling & colors of Excel Pivot Tables

You can apply any formatting to the pivot tables. MS Excel has some very good pivot table styles. Just select pivot table cells, go to Pivot Table Design ribbon. See below image to understand various options available.

Pivot table design options

Visualizing with Pivot Charts

You can use Pivot Charts to visualize the same information in a graphic format. Here is a sample Pivot Chart of Net Sales by Region & Product.

Excel Pivot Chart - Example

Steps for creating a Pivot Chart:

  1. Select any cell in the Pivot Table.
  2. Click on Insert > Chart or Analyze > Pivot Chart button.
  3. Insert the type of chart you want.
  4. You will get a Pivot Chart.

Interactive Pivot Chart with Slicers

Slicers make it incredibly easy to create interactive charts. Once you have a regular Pivot Chart, simply add a slicer to it (right click on the field in “Pivot Table Fields” area and select “Add as Slicer”). You now have an interactive Pivot Chart.

Here is a demo of interactive Pivot Chart. 

interactive pivot chart with slicers

Updating Pivot Tables (Refresh)

Whenever you have new data, just use “Refresh” button to update your Pivot Tables. You can find this button in multiple places.

  • Data ribbon
  • Pivot Table Analyze ribbon
  • On right clicking any Pivot Table
  • By pressing ALT+F5 (refreshes single pivot) or CTRL+ALT+F5 (refreshes all pivots)

Refresh and change data source options - Excel Pivot Table Analyze ribbonWhat if you want to point Pivot to new data?

Select any cell in the Pivot Table and from Analyze ribbon, use the “Change Data Source” button. Point input data to a new source. As long as the new data has same fields, everything will work smoothly.

 

 

Pivot Tables in Excel - Complete video tutorial

I have made a 21 minute video explaining how to create, format, customize, visualize, filter and refresh Pivot Tables. This video is packed with many tricks, ideas and inspiration. Check it out below.

Download - Sample data & example Pivot Tables

Please click here to download the sample file for this article. It contains fictional sales data, several example pivot tables, charts and additional resources. 

Examine the pivot table settings and use the data to learn more.

Next Steps

Now that you are familiar with Pivot Tables, explore these additional pages to learn more about data analysis & reporting.

Beginner:

Intermediate & Advanced Users:

Recommended Websites & Books:

These are my favorite places to learn more about Pivot Tables.

Websites:

Books:

Courses:

Happy Learning.

 

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

two level data validation drop down excel

Two-level Data Validation [Excel Trick]

Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.

159 Responses to “Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one”

  1. Glen Feechan says:

    Nice clear article, good to see someone else trumpeting the value of pivot tables. If you read this and are still not sure, you can learn by video:

    Excel 2003 version

    Excel 2007 version

    It is well worth making the effort to learn pivot tables - you won't look back once you do.

  2. [...] week we have posted a simple to understand excel pivot table tutorial here. Today I am going to supplement the tutorial with a 15 second video tutorial on Making excel [...]

  3. [...] good is a bunch of data when you can analyze it? That is where Pivot tables come in to picture [pivot table tutorial]. Thankfully, you dont need to do much. Just click a button and your table goes to pivot [...]

  4. Andrea says:

    Dear Expert,
    How can I replace the "Sum of" to "Different of" in a pivot table? I have a set of data that consists of both 2007 and 2008 sales, however these info were in one same column that name "period". Could you kindly show me the formula that I can add into the Pivot table to show the comparisions?
    Thanks a zillion

  5. Meikel says:

    Hi Chandoo,

    Well structured and presented as usual.
    I'm doing a lot with pivot tables. What I am looking for is a tool where you can change the data directly in the pivot table.
    Do you have an idea?

    br, Meikel

  6. louis says:

    I want to apply the same type of pivot table to a different file I get each month (but same format and type of data). Can I "save" a custom pivot table format ? (excel 2003)

    Thanks

  7. [...] means anyone can analyze and visualize millions of rows of data using powerpivot. It is like pivot tables on SUMPRODUCT (oh, I could totally say steroids, but who wants a cliche on a [...]

  8. srikanth says:

    hi chandoo,
    thanks. it was very simple and clear. really it is very usefull. it was well presented. great job.

  9. TMS says:

    Thanks for posting this info. I've been struggling with pivot tables for some time now and this has cleared up a lot of my confusion. Nice job.

  10. Chandoo says:

    @Louis... yes, you can save custom pivot formats...

    @Srikant & TMS: thank you very much. I am happy you liked this tutorial.

  11. [...] (If you are not familiar with basic pivot tables, you should check out this excellent pivot tables tutorial) [...]

  12. [...] multiple locations divided into various regions. It is recommended that you be familiar with the concept of pivot tables and also familiar with basic accounting [...]

  13. Jeremiah Minifield says:

    Will this cover Excel 97 and Excel 2002?

  14. Chandoo says:

    @Jeremiah... The steps are more or less similar in Excel 2002. I dont remember Excel 97 so cant say for it.

  15. [...] Excel pivot tutorial — wer es einmal begriffen hat, benutzt Pivot Tabellen ständig [...]

  16. payal says:

    I have two data point per condition, is it possible to display both the data points either bewlo one another or next to each other in a pivot table. If so, how would i do it.
    Since right now I can just display average, count or sum but not the actual raw data points.

    Also can we have error bar in the pivot chart for a bar graph?
    thanks for the help

  17. baidi says:

    mantataf excelnya................. top markotop

  18. [...] can make a pivot report from the data and then group dates in that to find totals by [...]

  19. michaelG says:

    My question is:

    I have a body of data that I have placed in a pivot table.. I understand how to manipulate it to shpow me eithert the min, sum, average etccc of the data
    however I dont know how I can have the pivot table express 2 different options like the min and the average??

    in the corresponding chart i would like to see the min amount in each period however I would like for the graph to also show me the average in each period

    for example the min would be displayed as a bar chart and the avg would be a line running through,

    can anyone help me with this??

  20. Chandoo says:

    @MichaelG: Just add the value field again to the "value field area" of pivot table. Now set value field settings to change the summary type to "average" for first value and "min" for second value. That is all.

  21. Hemant says:

    i want to learn excel, best and also focus on audit and accounts point of view so pls suggest me what should i do?
    is there any simple but strong book or any other on line site for it.

    • Rennie says:

      Hi Hemant,

      I am a qualified accountant with excel skills wanting to help fellow accountants.

      The area you should focus on are:
      1. Pricing
      2. Profit and Loss, Balance Sheet and Cash Flow
      3. Variance analysis

      If you have specific questions I am happy to answer them.

      Thanks,
      Rennie
      http://pivottabletips.com/

  22. Chandoo says:

    @Hemant: Try one of these books Excel Accounting books

    PS: I have not read any of them

  23. Shefeeque K A says:

    Its a great skill to present these complicated thing in such an easy way.
    I tried a lots of Ebooks from online non of them give me currect use of pivot table but ur presentation helped me a lot. Good Job.

    Thanks a lot.

  24. rajesh says:

    how to create pivot table,macros, vlookups with examples please send

  25. Glen Feechan says:

    In reply to Rajesh:

    I recently posted the following post that has links to video tutorials on pivot tables, vlookups amd macros:

    Top 5 Tips to make Excel Spreadsheets work for you

    I hope they're helpful.

  26. 6tel says:

    Excellent information! My congratulations on this post. However, I was wondering how to edit a pivot table, because I'm having troubles on a pivot table I created four days ago... My pivot table is doing just fine (it's a list of people to an event I'm organizing), but I'd like to add the information of a column I forgot to include days ago.

    This pivot table has the people who's most likely to come in a row category named "Yes", and the ones that are most likely not coming on a row category named "No". The pivot table shows the results of these two categories (final add of "yes" and "no", both separately and altogether), and the names of each people on the "yes" and "no" category, but I'd like to add their e-mail information just beside the name of the person who's coming and not coming without messing up the final total of the "yes" and "no" adding...

    I tried editing all the conventional way (dragging and dropping the "e-mail" tag on the pivot table I once did), but it says I can't overwrite (?) a pivot table with another pivot table (when I say "overwrite" I mean some verb that sounds like it but I'm not sure... My Excel software launches these alerts in spanish, since english is not my native language). I also tried dragging the "e-mail" tag to other parts of the pivot table but final result is not the one I'm looking for. Can anyone please help me? Thanks in advance.

  27. John says:

    Hi 6tel,

    Add the email column to the source table, this way...

    Name Coming email
    John Yes John@gmail.com
    Ana Yes Ana@gmail.com
    Will Yes Will@gmail.com
    Albert No Albert@gmail.com
    Susan Yes Susan@gmail.com

    And then include the email column in the Pivot Table (in the Row Labels area)

    You will have a summary report like this one...

    Row Labels Count of Name
    No 1
    Albert@mm.com 1
    Yes 4
    Ana@mm.com 1
    John@mm.com 1
    Susan@mm.com 1
    Will@mm.com 1
    Grand Total 5

    Give a look at this screen I captured for you...
    http://www.excelcream.com/images/helpingothers/PivotTableAddField.jpg

    Let me know if this helped you?

  28. 6tel says:

    Wonderful, John. ¡Muchas gracias!

  29. John says:

    Very nice tutorial. Your article along with a video I saw on YouTube [ http://www.youtube.com/watch?v=HVa7PIDfi5A ] has now made me a champion of pivot tables!

  30. Top10Lists says:

    This is a great tutorial with nice graphic to visualize the whole process. I would like to complement this post by pointing you to some great videos that teach you Excel pivot tables: http://www.squidoo.com/excel-pivot-tables

    Hope this helps!

  31. Anita says:

    Great tutorial.... Very precise & easy to understand. Thank you Chandoo....

  32. Jeremy says:

    Excellent tutorial. I supplemented this to the video @ http://www.youtube.com/watch?v=HVa7PIDfi5A .. learned PivotTables in less than 30 mins after struggling for over a week!

  33. SHRI says:

    I want to prepare a pivot table from data in an excel spreadsheet.
    The data table lists activities in Col. A and dates for each of the activities in 2-set columns for each project across the sheet.
    I want to be able to make a pivot table which will enable me to list activities and projects corresponding to each date.
    How can I do this? Will you be able to show me how to if I send you the spreadsheet by email?

  34. GVK says:

    Chandu sir,

    You have very clearly explanined the pivot tables concept. And, it is very easy learn.I got the confident with one example that you have given, that I can do big things with data pilots. Thank you sir.

  35. [...] have used pivot tables, SUMPRODUCT, SUMIFS, INDEX+MATCH, VLOOKUP formulas to process the [...]

  36. rediet says:

    thank u for the useful information

  37. Shalini says:

    Hi, I am trying to use Pivot and i need the count of a field status (which has options sich as Filled, Offered, Will Hire, Will not Hire, On Hold Positions), now i need a count of these Status under different departments however I don't want to include the "Will Not Hire" and "On Hold" numbers in the total, can that be done.

    • Chandoo says:

      @Shalini.. Set up the pivot table with departments in row label area and status in column area, and dropping names in value field to show counts.
      Now, just filter-out the status codes you do not want by applying filters on column labels.

  38. Jack Wilsons says:

    Here is a nice explanation of a pivot table. You might take a look too.
    http://books.zkoss.org/wiki/ZK_Pivottable_Essentials/Quick_Start/Concept

  39. [...] all know that Pivot Tables help us analyze and report massive amount of data in little time. Excel has several useful pivot [...]

  40. Mohamed Mahfouz says:

    Thank you for your help very nice site

  41. Dave Zachritz says:

    Thanks for making such a great site with lots of neat tricks. I'm sorry if this has been covered before, but I couldn't find it.

    I've got a speadsheet that has 5 columns of activity-type codes as text data. Column A will always have data, B will usually have data, C sometimes, D rarely and E almost never. Any of the fields can have one of about 15 different activity-type codes in it.

    Each time I add a column to the column labels the counts get more and more complicated because it seems to want to add in all of the possibilities of blank cells as well.

    It doesn't matter which column my activities come from, I just want a count of each activity type from the range Column A - Column E.

    It seems like this should be doable and simple, but I'm stumped.

  42. Jatin says:

    Hii 2 all,

    an anyone tell me ho eto use pivot table to show two data fieled (with their individual sum) & pivot also show their differences.

    Regards

    Jatin

  43. Hui... says:

    @Dave Zachritz
    You can use Countif
    eg:
    =COUNTIF(A:E,"Cat") will count how many times Cat is in Column A to E
    =COUNTIF(A:E,F5) will count how many times the value in F5 is in Column A to E

  44. Dave Zachritz says:

    Thanks, Hui.

    That's a good solution, but I need this in a Pivot table. How can I use Countif in that scenario?

  45. Hui... says:

    @Dave
    Add another field to the source of the data and put an IF formula to set a value to 1 when your conditions are met
    Then count that field in the PT

  46. Andie Nguyen says:

    Hi Chandoo,

    Just love your tutorials! Everything is very easy to understand and follow, well structured and visualized.

    Thank you!

  47. Sandeep says:

    Hey all the tips are too helpful and awesome. Keep the good work for us so that we will not find any problem. 🙂

  48. Courtney says:

    Hi!
    This post is great. Is there any way to do a pivot table with lots of text? I have a large excel file of comments from a survey that I would like to sort out. Can excel give me trending information or somehow help me sort the comments?

    Thanks!

  49. Hemlatha M says:

    i have to add two colum labels and also grand total . how to create in pivot tables, pls send any example to my mail id varma.hema25@gmail.com

  50. [...] is beyond the scope for this post.  However, you can find a good tutorial on Pivot Tables here or here. Figure 2 - vLookup for Zip [...]

  51. Nusrat zahan says:

    i want to be a expert in excel but how i can be good in excel? can you please help me out? with excel how many operations or work i can do? please tell me.

  52. AJ Labate says:

    Excellent tutorial. Was able to learn pivot tables in less than 30 minutes. Greatly appreciate this.

  53. k Shankar says:

    1) In excel 2007 I am not able to display field adjacent coloumn in pivot table.

    Ex. Product and description to be displayed to gether as on line then display as qty.
    This able to do in excel 2003 but not in 2007.

    2) Not able to convert xls file to dbf direct saving method in excel 2007.

    Please help

    regards
    shankar

  54. mohamed says:

    how to use pivot table & their uses

  55. Manohar says:

    Hi Chandoo,
    I have data written to excel through Java code into the columns say for eg as the shown above i.e. "Salesman, Region, Product, Revenues..etc". Is there any means to create the pivot table and pivot chart dynamically through code ? such tat any time i run the java code stores new data to excel sheet and generates the pivot table n pivot chart for that automatically.
    Further, once the data is written on to excel, can we refresh the pivot table n chart automatically through code ? if yes please suggest OR if there is any means to refresh automatically in excel which updates the pivot table n chart accordingly.

    --
    Thanks,
    Manohar

  56. @mit says:

    thanks for gide line. thanks .......................................................................................

  57. TJC says:

    Thanks Chandoo! This helped me prep for an interview I have tomorrow. Keep up the good work!

  58. supriya kannan says:

    hello, I have a data which includes students name, date of joining (year,month and date), fees paid and invoice number. i want to prepare a pivot table which shows me the details like the total number of students joined in each month of every year. Im not able to get it. Can you please help me out. y ID supi.roses@gmail.com

  59. Shekhar adiwal says:

    hiii cahndoo i really appreciated u for give the excellent knowledge of excel it will help lots of people like me as i learn from your knowledge i feel very happy thanks for gide line. thanks

  60. Jean-pierre Kabuiku says:

    Hello, Jack Wilsons,

    I liked your April 2, 2011 ZK documentation. God bless you.

  61. Brendan says:

    Thanks for the info.
    I love the catchphrase on the logo.

  62. Brendan says:

    Also, the data provided is great. I am taking the expert exam soon, and need something to experiment with. Thanks!

  63. Gina says:

    Hi does anyone know how to drill down on a pivot table and instead of the data generating on a new tab, I want the data to be in the same tab as my pivot. e.g. Cell B40.

    I have a second pivot table in that tab, and when i double click it, i want the drilldown data to be placed immediately after the dilldown data from the first pivot.

    Is there a way of doing this? Your help is much appreciated!

  64. [...] links that might help Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Micros... Excel Blog - Using PowerPivot with Excel 2010 my 2 cents 🙂 [...]

  65. Art says:

    Hi,
    I am trying to learn pivot table making adn I came across this website. I followed your instructions. I have 3 columns with student scores in 3 respective tests. I am making a pivot table and pivot chart in EXCEL 2010. Student number may change for each test. However, if I update my existing data columns to include 10 more students, the pivot table and chart do not update the additional values despite clicking 'refresh' many times. The data defines still is what was originally set up. Could you tell me what I am missing out here?
    Thanks much
    Art

    • Hui... says:

      @Art
      Unfortunately Pivot Tables do not natively expand as you add data.
      And Microsoft, I think this is a shortcoming in the Pivot Table model

      You have 2 options:
      1. Select the Pivot Table
      goto the Pivot Table Tools, Option Tab
      Select Change Data Source
      Update the range manually

      2. Use a Named Formula to define the Data Source
      Instead of using a Range as the source for the data use a Named Formula like:
      =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$!:$1))
      Then as you add data
      Right Click on the Pivot Table, Refresh

  66. Life Line says:

    how to use pivot table & their uses.

  67. Chomba Chibwe says:

    Hai Am new in the use of Pivot tables but am enjoying. How can i have repeated colum labels but with different functions e.g count and sum.

    see:

            A           B            C            D
    withdrawal Deposit Withdrawal Deposit 
    Count        Count     Sum         Sum

  68. prasad says:

    Great
    An excellent excel learning.....
     
    Thank you Purna
     
    prasad
     
     
     

  69. [...] least 5 cups of coffee, 2 hours of thinking, several hours of SQL, VBA, Pivot & SUMIFS, an hour of formatting & conditional formatting and may be 10 minutes on [...]

  70. ko says:

    @Hui,
    but when i tried keying the below function
    =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$!:$1)) it shows:
    data source reference is not valid. why is this so?

    • Hui... says:

      @Ko
      Looking at your pasted formula you have
      =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$!:$1))

      It is either the last Counta which is: COUNTA(Sheet1!$!:$1)
      Should be : COUNTA(Sheet1!$1:$1)

      or Sheet1 is the wrong name

  71. Rt says:

    Hello sir, i want to make the pivot chart in ex.2010
    so pls give me the link from where i can understand how to make this char...i have no any knowledge about this.......

  72. [...] 2002): Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Micros... Excel Pivot Table -- Dynamic Data [...]

  73. lazyvba says:

    Hello,

    Thanks for this great site Chandoo.
    Just to add a (still) missing feature in Excel pivot tables: count of unique elements.

    I did code some vba to paste to the pivot tables calculates the unique count.

    http://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html

    Hoping it can help users who want to get unique count quickly, or people who wants to get interested in vba coding for pivots ( lot of fun and possibilities)

    have a great day.

    Lazyvba
     

  74. [...] Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make …Jeremy Barn Hizer is on Facebook. Join Facebook to connect with Jeremy Barn Hizer and others you may know. Facebook gives people … Saving Privot Rian … [...]

  75. Lisa says:

    Thanks,

    This post was really helpful!
     

  76. [...] to show average times for products and ones over the agreed date Have you read those? link. link. link. link. [...]

  77. [...] Download a keyword report for the last 30 days with the the Segment Top vs Side.2. Create a pivot table with the data from the report and then build the pivot table like this.This will allow you to see [...]

  78. [...] and have a more reliable sheet, regarding costs that is. You can have a look at dyanmic tables at Tutorial on dyanmic Tables. But let’s keep going [...]

  79. Jiju says:

    You are awsme man...:)

  80. Arun Garg says:

    Very Nice presentation. Thanks for sharing

  81. Rohit says:

    Excel file name itself contains all letters of April fool.....hahahahh...awesoe chandoo and your microsoft friend.....@

  82. irfan says:

    hi dear, its very helpful to new user. Any one can learn easely from this example.

  83. Mallik says:

    Dear Friends,

    Anyone can you explain me about

    What is the use of Pivot Tabla and when it will uses

  84. Poonam Kapoor says:

    Pivot tables makes it easy to organize and extract information from a large tables of data without the use of formulas in Excel

  85. Sans says:

    Nice explanation!

  86. paulos says:

    thank you

  87. [...] Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one [...]

  88. Siegfried says:

    hello, thanks for this tutorial! I was having problem with my pivot table and it was really helpful! great blog about excel!
    best regards!

  89. [...] Hi, Have you seen the ExcelIsFun videos: Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube In the description there is a link to where you can get the files. Also maybe try this tutorial on Chandoo.org, which includes a sample workbook: Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Micros... [...]

  90. Pradeep D says:

    Hi,

    I am trying to add conditional formatting to a pivot table and am getting an error "Cannot apply a conditional format to a range that has cells outside of a PivotTable data region. Make sure that all the cells are in your selection are inside the PivotTable data region.". I want to enter the emp# in O1 and based I want to highlight the complete row/record in the pivot table. Data is in the same sheet from $A:$K and pivot table is at N4. Can someone please help me on this?

    Thanks and regards,
    Pradeep D

  91. THANK YOU! Now I understand pivot tables!

  92. www.dbennett2.wordpress.com says:

    I constantly spent my half an hour to read this website's articles everyday along with a cup of coffee.

  93. [...] Windows For 2. PErsonally, I would suggest trying a Pivot table and seeing what that gets you. Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Micros... You can summarize by Account Expiry time and filter for everyone with more than one position on [...]

  94. Nilesh says:

    Thank you so much Chandoo. It's a great site to learn Pivot online for a bigginer. The success of this site is in giving a feeling that pivot is quite simple to work on and use.

    Great job. thank you so much. cheers.

  95. hardeep says:

    thanks...!!! : )

  96. Nellster says:

    WOW! This really helped me a lot THANKS

  97. Muneca says:

    Anyone have a good work around to the problem of not being able to use Pivot tables in a shared document. I built a spreadsheet where 5 different locations are entering data but we can't use pivot unless we un-share the document first which defeats the purpose.

  98. chintan trivedi says:

    plzzz teach me pivot excell table...

  99. David says:

    Hi Community, it is the first time I see this level of knowledge shared around Pivot table.
    Unfortunataley, I have not found the way to cover one need I have for a long time.
    Let me explain :
    I have a list of Ticket number, their opened date and their closed date.
    I would like to get a pivot counting number of tickets opened and closed grouped by days for example.
    I can count the number of tickets opened and the number of tickets closed. But I do not know how to have both entries in the same chart.
    Here a sample of my data :
    Case Number Opened Date Closed Date
    7465186 04/01/2010 11/12/2010
    7476194 11/01/2010 11/12/2010
    7477523 11/01/2010 11/12/2010
    7491526 15/01/2010 11/12/2010
    7603990 10/03/2010 15/04/2013
    7603991 10/03/2010 15/04/2013
    7625938 22/03/2010 11/12/2010
    7634930 26/03/2010 11/12/2010
    7672871 21/04/2010 11/12/2010
    7681605 28/04/2010 15/12/2010
    7681607 28/04/2010 15/12/2010
    7681610 28/04/2010 15/12/2010

    Any thought ?

  100. […] to the Board! Sounds like a good job for a Pivot Table. Here's a good tutorial: Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Micros… […]

  101. […] 559,208] Excel Dashboards – Information, examples & tutorials [Visitors: 386,066] Excel Pivot Tables Tutorial [Visitors: 487,794] Project Management using Excel – Information, examples & tutorials […]

  102. […] Object Model PivotTable Reports 101 Excel Pivot Tables Tutorial – Contextures Excel Pivot Tables Tutorial – Chandoo Referencing Pivot Table Ranges In […]

  103. Mollie says:

    I like reading through a post that can make people think.
    Also, many thanks for allowing me to comment!

    Look into my website; install metal roof - Mollie,

  104. Ajay says:

    Its Really really Details learning system.
    thanks & keep it up.

  105. Kabelo says:

    1.Can you directly change the data in a PivoTable? If not, how do you change the data?

    2. Which PivotTable feature is used to easily identify the current filters applied to the data?

  106. Eric W says:

    Are there any examples of pivot tables that don't use sales data? I'm not a businessman, and sales info just doesn't resonate with me.

    I do a lot of time-series data collection and have to reduce large quantities of data, but I'm not sure how the pivot table can help.

    For example, I have a year of weather data on an hour basis, with 8 different parameters. Can a pivot table help me organize this data set? I can't get my mind around this.

    Thanks

  107. William says:

    Thank you for your guidance. my reports have a new format and meaning. I love to think with charts and graphs

  108. […] Chandoo – Excel Pivot Table Tutorial Chandoo gives a good overview of pivot tables, with helpful tips and links. […]

  109. DaveB says:

    A question please.
    In the tutorial workbook, I notice that when the filter selections are changed and the pivot table shrinks, that the cells around the new table maintain the white background.
    Can anyone explain how to do this? When I create a pivot tbale and the size changes, the surrounding area loses the background color and shows the default formatting with grid.
    I have searched online and when I have found a similar question on other boards, it was not answered.

    Many thanks, in advance.

  110. Radhe says:

    Dear Mr. Chandoo g,
    Kindly resolve my problem. i have 1000 data sheet there are many types of data with many column and sr. no. in a excel file. I am making a new file which have headings in a column and other column is blank. can it possible i give a input in that column and its related data is automatically type in the blank space. please feel free to call me on 9669694051

  111. hafiz says:

    thanq very much bro.

  112. Dear Chandoo,
    Happy new year and all the best wishes for you and your family. all i have in Excel are from your lessons.
    God save you and give you the best.

  113. adulapuram Ramadevi says:

    pls send the formula how to devide one particular character ex: jayaprakash Answer is “jaya” is once cell and “prakash” is another cell

  114. […] Steps to create pivot tables are provided here. This is just an elaboration on my part, with input from other websites like chandoo.org. […]

  115. shashank verma says:

    Dear Chandoo,
    i would like to meet regarding MIS topic discussion.If u r free then plz call me back on 8287260105

    Regards,
    Shashank Verma

  116. Mangesh says:

    Hi Team,

    I Need macro to extract data from excel and create Pvarious Pivots. also if i can name the different pivot tabs it would be of great help.

    i tried to record one macro to create pivot but it dint work and gave below error, please need your urgent help:
    Sub Macro3()
    '
    ' Macro3 Macro
    '
    '
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Actual Time booking- Nav!R1C1:R2134C8", Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:="Sheet8!R3C1", TableName:="PivotTable3" _
    , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet8").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Work Item Set")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Resource")
    .Orientation = xlRowField
    .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Work Item")
    .Orientation = xlPageField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
    "PivotTable3").PivotFields("Actual Hours"), "Sum of Actual Hours", xlSum
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Time Per")
    .Orientation = xlColumnField
    .Position = 1
    End With
    Sheets("Sheet8").Select
    End Sub

  117. […] Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one […]

  118. Summer says:

    Hi,
    I'm really new to pivot tables - just started learning a couple of hours ago, but can't seem to find an answer to my question. So please excuse me if there's a very obvious solution to my question. I still have a lot to learn.

    My company uses a 13-digit barcode to identify items. When I try to add it to my pivot table, Excel treats it as a number value and sums it. How do I stop it from being added up?

    Thank you 🙂

    • Hui... says:

      @Summer
      It sounds like it should be a Row or Column Header instead of a value Field
      or
      Change that field to Count instead of Sum

      • Summer says:

        Hi Hui

        Thanks for the reply. It worked! 🙂 Changing it to Count just showed me how many times that item showed up, but changing it to a Row or Col Header gave me the barcode no. Thanks so much!! 🙂

  119. […] Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one […]

  120. Archa Nair says:

    Hi ,

    If I need to create multiple pivot tables but with the same filter data how would I do that ? As of now I am creating separate pivot tables giving filter data separately.I want to separate the filter data and make it common for my different pivot tables.

    Thank You ,
    Archa

  121. […] How To Create Pivot Tables Whats’s a PivotTable and how to create it? YouTube Video (6 min) Tutorial of PivotTables with some useful tipsLink […]

  122. […] How To Create Pivot Tables Whats’s a PivotTable and how to create it? YouTube Video (6 min) Tutorial of PivotTables with some useful tipsLink […]

  123. Tech Khalifa says:

    Thank you for providing a tutorial with MS Excel pivot table functions. very helpful in supporting my daily work.

  124. Krt says:

    I couldn't get the row Grand total in pivot table. What to do

  125. Writer says:

    Hi Chandoo,

    I have a query regarding dynamically selecting Print area in Pivot Table. On web I found the Offset option widely discussed and recommended. I tried it but it fails as it expands the columns as well.

    My problems is this selects columns where I have slicers placed. Also at times the rows are not correctly adjusted

    I need to have a solution where only Pivot Table Data is selected depending on Rows numbers having data in them.

    Your wisdom in providing the solution is solicited. Please note I am a Novice in VB so I would prefer the whole code step by step for me to easily implement the same.

    Thank you for your precious time.

  126. SolfulSista says:

    Chandoo,

    A relative told me about your website since I need help creating pivot tables, I learned how to do it in a matter of minutes thanks to your tutorial. Where have you been all of my excel troubled life, I look forward to viewing your videos on youtube as well as on your site. You are going to make me EXCELLENT at Excel!

  127. MOHAMMED MOHADDIS says:

    Hi Chandoo,

    Thank you so much for the helpful information, and an amazing way of explanation. Can't thank you more awesome website for beginners to advance in Excel.

  128. Venkatesh says:

    While using pivots..At times there is no data for certain criteria the. The looks goes awry.. can the column be fixed in way that even if data is not available for a certain field day employee name..The structure displayed remains

  129. zhangshuai says:

    hi,chandoo
    I have bought the Excel dashboard template?but download link has expired?Because I am from China to visit the amazon cloud, speed is slow to retry the cause for many times?I very try so hard, now already sent you E-mail, don't get your reply in a timely manner?
    I hope you to give a download link, don't limit the number of times, I thank you very much

Leave a Reply