• Do you want to impress your boss?

    Do you want to see the bigger picture at work more quickly?

    If you answered “Yes” to either or both of these questions, then read on.

    You may be thinking…..”What is a pivot table”? Well, a Pivot Table summarizes a large amount of data into a simple and easy way that allows the user to see what the important numbers are in the data. You may have thousands of rows of data, with some rows that are less significant than others – so  Pivot Table will let you choose the important summary metrics and summarize the data according to the metrics you choose.

    Learn how to use Pivot Tables and become an instantly more valuable employee!

    This is a step by step guide to creating Pivot Tables in Excel. There are only 7 steps! 

    It is a tutorial for Pivot Tables in Excel 2007 and 2010.

    We’ve deliberately used a SIMPLE example, as it easier to learn things with simple examples.

    To start, open the file for the Pivot Table tutorial by clicking here: link to file

    A screenshot of what the file looks like is below:


    You will see that the Excel table shown in sheet 1 in the file shows the expenses paid by company ABC Ltd over a period of time, starting on the 8th of January 2013. The table has over 81 rows and you can’t see at a glance how much was paid for Pens and Pencils in each month  – there are 4 months in it January to April, and given that there are so many rows, this is where a Pivot Table comes in handy! The columns in the spreadsheet are:

    1. Column A – Month –  the month that goods were purchased in
    2. Column B – Date –  the date within that month that the goods were purchased
    3. Column C – Supplier – the company that the goods were purchased from
    4. Column D onwards – the goods that were actually purchased – pencils in column D and pens in column E. I had added more “Goods” / “Products” but let’s keep it simple.

    We’ll use a pivot table to summarise

    1. The expenditure by MONTH and
    2. The expenditure by GOODS purchased

    The Pivot table will make it easy to see these figures at a glance – that’s their function – summarising large amounts of information in a smaller, digestible format.

    Step 1

    Click on sheet 2 in the spreadsheet – we want to have the Pivot Table in a separate tab, so that it it is easier to look at when we’re finished.

    Step 2

    You will automatically be in cell A1

    Click on the Insert tab in the toolbar – it is to the right of the Home tab (see image 1)

    Image 1

    Step 2

    On the left hand side, you will see an icon called “pivot table”  (see image 2 below)– click on it, then click “Pivot Table” (instead of “pivot chart”)

    Image – 2

    Step 3

    The “Create Pivot Table” window will have popped up (see image 3 below) and here you will need to specify the range of data that you want to create a Pivot Table for.

    Image – 3

    Step 4

    At this point, you need to click on “Sheet 1” in the spreadsheet (because that’s where the data is that we want to create the pivot table for).  The cursor will already be in the right place in the “Create Pivot Table” window, so all you have to do is highlight cells A4 to F86  – Excel will automatically put in $ signs for you when you do this, as it is fixing the range of data you are looking at – that’s what dollar signs do! See image 4 below. NB if the numbers and letters in your “Table/Range” field shown in the screenshot below are in any way different, then you’ve made an error and you’’ll need to ensure they match. But if you followed the instructions, there’s no reason why they shouldn’t!

    Image – 4

    Step 5

    Click “Ok” – at which point Excel will take you back to “Sheet 2” (see image 5 below). Now go to the right hand side of the screen, where it says “Pivot Table Field list” / “Choose Fields to add to report” and tick MONTH. Your screen should look like IMAGE 6 below – there is a grid of four squares and MONTH should appear in the “Row Labels” square.

    Image – 5



    Image – 6

    Step 6

    This is the tricky part – drag your mouse over to the word “Pencils” in the “Pivot Table Field list” / “Choose Fields to add to report” and drag it down to the “Values” square out of the four squares. Your screen should now look like IMAGE 7 below where the values square has “Count of Pencils”

    Image – 7

    Step 7

    The “Count of Pencils” calculation counts the number of times Pencils were purchased each month, but we want to change that to “Sum of Pencils” so we can see how much was spent on Pencils each month. To do this, click on “Count of Pencils” then click “Value Field Settings” (see IMAGE 8 below) then in the “Value Field Settings” window that pops up (see IMAGE 9 below), click on “Sum” then click on “Number Format” in the same window – also shown in the screenshot, and change it from “General” to “Currency”. Click ok – that will return you to the “Value Field Settings” table. Click ok again, then repeat Steps 6 and 7 for the “Pens” in the “Pivot Table Field list” / “Choose Fields to add to report”. When you repeat Steps 6 and 7 for “Pens” you will notice that the sigma sign and the word “Values” will appear in the “Column Labels” square (see image 10 below).But don’t panic, this is ok! It’s simply the titles for your columns, which will now be “Sum of Pencils” and “Sum of Pencils”, respectively. This will now complete our Pivot Table – ie it shows at a glance how much money was spent in each month on “Pens” and “Pencils” as well as the Grand Total for each in row 6. If you want to test this, you can go back to Sheet 1 and check the Totals in row 87.

    OPTIONAL STEPwith reasons why a Pivot Table may disappear and another field to add.

    You can also manually check how much was spent on “Pens and “Pencils” for each month in Sheet 1, but that beats the point. The Pivot Table’s done that for you! To make the Pivot Table more “flashy”, and gain a better understanding of how they work, go to Sheet 2 and tick the “Supplier” box in the “Pivot Table Field list” / “Choose Fields to add to report” window. If it’s disappeared, don’t worry – it will only have disappeared if you clicked outside of the Pivot Table in Sheet 2 eg if you clicked cell E1, it would disappear! But if you clicked on cell A1, the table on the right would magically re-appear! And voila, you can tick “Supplier” to see how much was spent on each “Supplier” each month for the different products. When you do this, it will look like IMAGE 12 below. That’s it! The comments box is below if you wish to post a comment! Peace out!


    Image – 8



    Image – 9



    Image – 10



    Image – 11



    Image – 12
    Image - 12


    We ask that you take a moment to read our Terms and Conditions and Privacy Policies.


    1. Gwen
      Posted February 12, 2014 at 4:28 pm | Permalink

      This is a very helpful and easy step by step process. Thank you

    2. Analyst
      Posted February 17, 2014 at 7:23 pm | Permalink

      You’re welcome.

    3. Anu wilson
      Posted February 21, 2014 at 1:59 am | Permalink


    4. Indranil Ghosh
      Posted March 3, 2014 at 2:00 pm | Permalink

      Your teaching style is excellent.

    5. Van
      Posted March 5, 2014 at 3:04 am | Permalink


    6. Aneesh Nair
      Posted March 6, 2014 at 9:34 am | Permalink

      Excellent tutorial. Thanks so much, it was really helpful

    7. Sunita Cardozo
      Posted March 27, 2014 at 6:57 am | Permalink

      Excellent way to learn Vlookup and PivotTables. just a suggestion please can you add some worksheets for practising and learning

    8. Analyst
      Posted March 27, 2014 at 2:45 pm | Permalink

      Hi Sunita

      There are two different examples on the vlookup site at the moment.

      The basic one shown on one sheet here: http://howtovlookupinexcel.com/
      And the more “advanced” one shown between two workbooks here: http://howtovlookupinexcel.com/vlookup-between-two-workbooks

      I will look into adding another example on the Pivot Tables site in the next few weeks.

    9. Amazing
      Posted March 28, 2014 at 7:02 pm | Permalink

      amazing thank you

    10. mangal
      Posted April 9, 2014 at 10:43 am | Permalink

      Great job very easy to learn. thank you.

    11. Analyst
      Posted April 21, 2014 at 6:42 pm | Permalink

      Thanks for the feedback, I appreciate it.

    12. Olayinka Mann
      Posted May 3, 2014 at 8:27 pm | Permalink

      You did an amazing job with the tutorial on Pivot Tables and Vlookups. I took both and you made it so easy! I cant thank you enough!

    13. Vinita
      Posted May 13, 2014 at 7:18 am | Permalink

      Awesome that was easy and clear.Thank You

    14. mithlesh
      Posted June 28, 2014 at 9:20 am | Permalink


    15. Adriana
      Posted July 2, 2014 at 11:01 am | Permalink

      Great, simple tutorial. Thanks a lot!!!!

    16. Analyst
      Posted July 8, 2014 at 7:24 pm | Permalink

      You’re welcome.

    17. madeleine
      Posted July 10, 2014 at 2:59 pm | Permalink

      Excellent tutorial first VLookUps then Pivot Tables all made very simple. Well done

    18. Kathleen
      Posted July 16, 2014 at 6:42 pm | Permalink

      Fabulous! Thank you so much for this. Easy to follow and very well written.

    19. Lise
      Posted July 17, 2014 at 10:32 am | Permalink

      Utterly brilliant!

    20. Indranil
      Posted July 26, 2014 at 2:52 pm | Permalink

      Thank you so much for this. Easy to follow and very well written.

    21. Beth
      Posted August 4, 2014 at 4:55 pm | Permalink

      I trembled when someone asked if I know how to do pivot tables – I feel more confident now!! Thank you…..on to the dreaded vlookup :)

    22. Analyst
      Posted August 4, 2014 at 7:05 pm | Permalink


    23. Danni
      Posted August 6, 2014 at 10:41 am | Permalink

      I have been embarrassed in my job for so long, always having to ask colleagues for assistance. When explained to me I have still struggled to understand.
      This tutorial has finally enabled me to understand for myself!
      I can’t thank you enough!

    24. Analyst
      Posted August 6, 2014 at 8:37 pm | Permalink

      You’re most welcome. Thanks for the compliment.

    25. ashish singh
      Posted August 11, 2014 at 9:42 am | Permalink

      That was really awsomeeeee..

    26. Taskir
      Posted August 11, 2014 at 9:58 pm | Permalink

      Brilliant way to teach. Thank you very much

    27. saty
      Posted August 29, 2014 at 6:29 pm | Permalink

      good article

    28. ANNE
      Posted September 10, 2014 at 8:49 am | Permalink

      hi, i forgot how to reselect or refresh the pivot table range without repeating the process..

    29. Analyst
      Posted September 13, 2014 at 8:25 pm | Permalink

      Hi Anna

      Apologies for the delay. You just need to right click on the Pivot Table then click “Refresh”.

    30. Sibongile
      Posted September 15, 2014 at 9:31 am | Permalink

      Thanks,but my table did not look like yours while i was counting my pencils mine was 23 urs was 16. How is that possible? and I cound’nt highlight my table to i86 but to f86 because the was no information from column g to i

    31. Analyst
      Posted September 15, 2014 at 7:17 pm | Permalink

      Hi Sibongile

      Yes, you’re correct – the data only goes to column F, rather than I – thanks for spotting that. I’d originally created several columns, up to column I, but decided to remove the data in columns G to I to make the tutorial simpler to understand.

      If you’re not getting the same result as the tutorial, please send me the file and I’ll have a look to see what’s gone wrong. Many other visitors have been able to replicate the results (as you’ll note from the comments below) but I’m happy to take a look at your file.


    32. Gautam Kumar
      Posted December 3, 2014 at 8:00 am | Permalink

      nice way of explaining….

    33. Analyst
      Posted December 22, 2014 at 5:51 pm | Permalink


    34. Anoop
      Posted January 6, 2015 at 12:30 pm | Permalink

      This is the most simplest and the most efficient way to learn Pivot Tables. I also learned VLOOKUP on this forum. Great job guys! You are awesome.

    35. Dawana
      Posted January 6, 2015 at 6:26 pm | Permalink

      You are wonderful! Thank you for the easy step by step examples

    36. Analyst
      Posted January 6, 2015 at 9:57 pm | Permalink

      Thank you for your kind comments. Glad I was able to help.

    37. Analyst
      Posted January 6, 2015 at 9:58 pm | Permalink

      Thank you for your kind words. I appreciate it and I’m, of course, glad that the examples were helpful.

    38. Kj
      Posted January 8, 2015 at 4:14 pm | Permalink

      Thanks for Simplifying Pivot Tables.

    39. Analyst
      Posted January 8, 2015 at 11:33 pm | Permalink

      No worries. You’re welcome.

    40. AJISH
      Posted January 16, 2015 at 9:13 am | Permalink


    41. Ravi
      Posted January 16, 2015 at 9:31 am | Permalink

      Thanks for the simple & easy to learn steps

    42. Analyst
      Posted January 17, 2015 at 12:26 pm | Permalink

      You’re welcome.

    43. Swapnil D Jadhav
      Posted January 24, 2015 at 3:21 pm | Permalink

      Thanks a lot

    44. Jaekoo
      Posted February 5, 2015 at 10:13 am | Permalink

      Used to live in Richmond upon Thames several years ago before relocating to Sydney. Your example indeed reminded me of my good old days in LDN.

      Job well done once again! =) Thank you!

      P.S: Quick Qs for you.

      Q1: Do you offer excel on-line tutorial on topics other than pivot table & vlookup by any chance?

      Q2: Once I play around with pivot tables, I might feel like to make a graphical / visual representation of the fabricated data. In such case, do you recommend one like me to copy & paste the figures as “value only” in other sheet and plot the graph, perhaps? Any tips, recommendation on that part as an expert please?

    45. Analyst
      Posted February 12, 2015 at 10:35 pm | Permalink

      Hi Jaekoo

      London is great, and Richmond is very posh! Good choice!

      Q1) I don’t offer tutorials on other topics at the moment (besides vlookups and pivot tables), but can certainly look into other areas. Is there anything in particular that you’d like to be covered? I created the Pivot Tables tutorial as a result of demand for it on my vlookup website.

      Q2) You can actually create charts with Pivot Tables without having to copy and paste the data anywhere else. I’m writing an ebook and plan to include a Pivot Chart in it, but if you want help on a specific question with dummy data, let me know and I’ll be happy to assist.



    46. diramo
      Posted March 31, 2015 at 4:00 pm | Permalink

      Thanks a lot. Bravo, good teacher

    47. Analyst
      Posted April 3, 2015 at 3:33 pm | Permalink

      Thank you Diramo.

    48. AM
      Posted April 21, 2015 at 8:33 pm | Permalink

      This is by far the best and easiest to follow tutorial for pivot tables that I have found. Such a relief to finally “get it”!

    49. Analyst
      Posted April 21, 2015 at 10:09 pm | Permalink

      Awesome!! Thanks ever so much for the compliment.

      And let me know if you have any questions in future.


    50. Nayer
      Posted April 24, 2015 at 7:43 pm | Permalink

      Analyst you are the best. All the time I was thinking that I am not good at Excel and no way I can learn Pivot. Now I know exactly what to do. You did a great job. Thanks

    51. Analyst
      Posted April 25, 2015 at 3:53 pm | Permalink

      Thanks Nayer – glad I could help.

    52. gourav
      Posted June 13, 2015 at 11:19 am | Permalink

      Thanks for the help

    53. SSein
      Posted June 16, 2015 at 1:37 am | Permalink

      Thanks so much! What a terrifically simple explanation for Pivot Tables. You are my FAVORITE!

    54. vikash
      Posted June 18, 2015 at 10:23 am | Permalink

      Thanks a lot. Great Example. You make it very easy to understand.

    55. Asit Mukherjee
      Posted June 26, 2015 at 4:05 pm | Permalink

      I did’t know that it was so simple! Thank you very much.

    56. Analyst
      Posted July 6, 2015 at 10:16 pm | Permalink

      Cool! Thanks for the feedback.

    57. Analyst
      Posted July 6, 2015 at 10:17 pm | Permalink

      No problem.

    58. Mike Scheuermann
      Posted July 17, 2015 at 7:09 pm | Permalink

      This was great but started in the middle for me, sort of. I was looking into this for my wife, actually. I was a bit confused when I saw the drop-downs already created in A4 through F4. THAT is what I thought the essence of a pivot table was, frankly.

      So, this sort of taught me step-2 and now I have to go learn how to do that step-1, I’d call it.

      Thank you! I forwarded this tutorial’s link on to my wife, for her use.

    59. Analyst
      Posted July 17, 2015 at 8:05 pm | Permalink

      Hi Mike

      Step 1 is just a spreadsheet with lots of data – this will be the case with all Pivot Tables – you’ll have lots of data that easy isn’t to read or glean summaries from. So a Pivot Table will help you summarize it so the info is easier to digest.

      In this case, I have summarized the data from the opening screenshot (before image 1) by Month and Product (Pens and Pencils), so you can see how much was spent on Pens and Pencils in each month (image 11)

      I have then added in Supplier – so you can see how much was spent with each supplier in each month and how much was spent on Pens or Pencils with each supplier in each month (image 12)

      You can’t see these summaries in the first screenshot in the tutorial (before image 1), hence the reason we use a Pivot Table to give us a quick snapshot of what’s happening overall. Let me know if still unclear.


    60. Kathy
      Posted August 31, 2015 at 5:43 pm | Permalink

      Excellent tutorial. Thanks soooo much for your help. Its much appreciated :)

    61. Analyst
      Posted September 2, 2015 at 8:42 pm | Permalink

      No problem. I am glad it helped you.

      Let me know if you want me to cover anything else.

    62. Don Dator
      Posted September 7, 2015 at 9:34 pm | Permalink

      Excellent! This is very helpful.

    63. Analyst
      Posted September 13, 2015 at 9:25 pm | Permalink

      Cool! Glad it helped, Don. And thanks for the feedback.


    64. Ed
      Posted September 22, 2015 at 8:13 am | Permalink

      Thank you for making pivots and VLOOKUPs easy to understand.

    65. Analyst
      Posted September 22, 2015 at 9:04 pm | Permalink

      You’re welcome, Ed.

    66. Pallavi
      Posted September 24, 2015 at 9:15 am | Permalink


      Thanks a lot, it’s very useful and easy to understand. Great work.

    67. Freddy Rakhombe
      Posted October 4, 2015 at 10:30 am | Permalink

      Thank you for simplifying this, I never thought I would master it. You just proved how wrong I was! Just need more and more practice.


    68. Chitra
      Posted December 9, 2015 at 2:04 am | Permalink

      Thank you so much! You are a teacher who genuinely wants every student, irrespective of their prior knowledge, to master this topic. I am so grateful to you!

    69. Analyst
      Posted December 22, 2015 at 12:58 am | Permalink

      You’re welcome, Chitra.

    70. Thomas
      Posted January 23, 2016 at 4:24 pm | Permalink

      Thank you for creating the Excel VLOOKUP and Pivot Table tutorials with corresponding workbooks.

      I am taking an employment placement test next week that I hope will lead to a new employment opportunity!

      How are you with Leverage and Earnings per Share (EPS)? I have a workbook from University that has a homework assignment that I could not solve and the professor would not help.

      Thank you, Thomas

    71. Analyst
      Posted January 25, 2016 at 8:56 pm | Permalink

      Hi Thomas

      Thanks for your comment.

      However, I’m quite busy working on other projects, at the moment.


    72. Shakeel Siddiqui
      Posted February 11, 2016 at 7:21 am | Permalink

      I am truly truly grateful to you for providing this tutorial on line. I am now confident to do some advanced stages too. Thanks once again.

    73. Sir T
      Posted March 19, 2016 at 1:14 am | Permalink

      Light bulb moment, I feel smarter!!! Thank you!

    74. Trisha
      Posted April 13, 2016 at 5:52 pm | Permalink

      Need to learn this for a new job. Your article was the best/easiest to understand that I found. Thank you!!

    75. Analyst
      Posted April 13, 2016 at 8:51 pm | Permalink

      You’re most welcome, Sir T.

      Glad I could help!

    76. Analyst
      Posted April 13, 2016 at 8:52 pm | Permalink

      You’re welcome, Trisha.

    77. Thomas
      Posted May 25, 2016 at 12:19 pm | Permalink

      I’d like to follow up with you on my post of 01-23-16. Redundant use of your PivotTable tutorial provided me with the in-depth knowledge of creating a PivotTable that was required for the placement exam and subsequent interview question ‘Tell us how to create a PivotTable.’

      Fast forward four months and the employer is requesting that I develop an internal protocol for staff to follow on… yes, creating PivotTables for our work.

      Thank you again!

    Post a Comment

    Your email is never shared. Required fields are marked *