ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

Tutorial: How to Create a Simple Pivot Table in Microsoft Excel

Updated on February 24, 2014

My Example Pivot Table

This is my sample pivot table. It's based on simple data. As you can see, this is a simple layout, but it still proves useful - even for garage sale data!
This is my sample pivot table. It's based on simple data. As you can see, this is a simple layout, but it still proves useful - even for garage sale data!


Pivot Tables are one of Microsoft Excel’s most powerful features. They can help you organize data, see patterns at a glance, group information by category, compare facts on people or categories, summarize amounts, report figures accurately, and effectively analyze your chart data.


Why are Pivot Tables Unique?

Excel pivot tables provide information in a simple manner, because the pivot tables are usually independent from the mountains of data that created them. Note: If you use a pivot table, DO NOT delete the data tab. The pivot table still needs that data in order to pull information.



Note: Decision makers can quickly and easily access information that is in front of them. Pivot tables make this possible. In today's business world, speed is crucial. Management usually appreciates summaries rather than large amounts of data.


Beginner

Do you know how to make a pivot table?

See results

Benefits of Pivot Tables


Excel pivot tables:

  • improve reliability
  • help you when you have more data than you know what to do with
  • provide a wide range of flexibility
  • allow you to arrange data by columns or rows
  • label and sort data as you see fit
  • easily group data with the drag and drop feature
  • more straightforward in analyzing than formula-based spreadsheets


How I Laid Out My Data for My Pivot Table

Column A
Column B
Column C
Who sold the item or who the items belonged to.
Amount that person made off the sale.
The number of items that individual sold during that purchase.

My Garage Sale Data

This is an example of how I laid out my data for my garage sale. This chart and pivot table are based on simple data; however, with a large amount of data, you can really see the benefits of a pivot table.
This is an example of how I laid out my data for my garage sale. This chart and pivot table are based on simple data; however, with a large amount of data, you can really see the benefits of a pivot table.

Creating a New Excel Workbook


Option 1: The Quickest Excel Short-Cut Way

  • Hold down the Control key and the letter “N” (for new workbook).

Option 2: The Excel Short-Cut Way

  • Step One: Open Microsoft Excel
  • Step Two: Touch and let go of the “ALT” key
  • Step Three: Touch and let go of the “F” key (for File menu)
  • Step Four: Touch and let go of the “N” key (choosing “New”)
  • Step Five: When the “New Workbook” table comes up, hit the “Create” button with your mouse.

Option 3: The “Dinosaur” Excel New Workbook Way

  • Press the “File” button at the top of the screen on the left. The newer Excel versions of Microsoft show a circle floating above and to the left of the “Home” menu on the top of the screen.
  • The first picture you will see under the “File” drop-down box is a blank, white sheet called “New.”
  • Click on “New” to create a new workbook.




Tip: The best pivot tables have one column with a value in it with all of the rest of the columns describing that number in some way.


Creating Your Data


  1. Copy and paste your data from another source or type in the raw data without any formatting into an Excel sheet. Just spit it out. (The only formatting you may want to use is capitalization if applicable).
  2. Make Row 1 your titles. For example, the title for Column A could be “Employee” (in Row 1), and the title for Column B could be “Sales” (again, in Row 1).
  3. If desired and/or to make your workbook neater and more organized, you could label this tab “Data.”
  4. Note: For the Sample Pivot table, I have chosen the following headers/titles for my data: Who, “$,” and “#.”


How to Label the Sheets in Your Workbook


Naming a Sheet in Excel: Rename “Sheet 1” to be your desired name for this sheet. You can do this by one of the following methods:

  • Double click on the tab’s name at the bottom and writing over it with your desired name for the sheet. Then, press enter to save your new title.
  • Right click on the bottom of the sheet where it says “Sheet 1.” Write over “Sheet 1” with your desired name for the sheet. Then, press enter to save your new title for the sheet.
  • Use the short-cut: ALT (hit and lift up), “O,” “H,” “R,” and rename the sheet with your desired name.




Cool Tip: Want to Know More About Data in Your Pivot Table? “Drill down.” In other words, select the amount you wish to know more about, double click it, and it will take you straight to the details of the original data. This helps with researching problem areas.


Pivot Table


  1. Click the cursor anywhere in the data you created above. (This tells Excel what table you want to use to make your pivot table).
  2. Choose the “Insert” menu. (Shortcut: [hit and release the button] ALT and then “N”).
  3. Choose “Pivot Table” in the toolbar (Shortcut: [hit and release] “V” and then “T”).
  4. It will automatically select your table, and put it in a new worksheet (You do have the option to have the pivot table in the same place as your data, but it’s a bit cleaner to have it separate), so you only have to press the “OK” button (or ENTER).
  5. A new sheet will open up with all of the tools you will need to make a pivot table.
  6. On the Right, you will see five boxes:

 
Choose Fields to Add to Report
Report Filter
Column Labels
Row Labels
Values


  • Under #1, move the “Who” category (by holding down your mouse and dragging it) to make it appear below #4 (Row Labels).
  • Under #1, move the “$” category (hold and drag) to make it appear below #5 (Values).
  • Under #1, move the “#” category (hold and drag) to make it appear below #5 (Values) underneath “$.” So, under “Values” you should have $ with # below it.
  • As instructed above under “How to Label the Sheets in Your Workbook,” you can rename the tab with the Pivot Table as “Summary” (or whatever title you want).



Great Feature of Pivot Tables: If you update your data (perhaps you are selling flowers online, and your column headers are: Source, Volume, and Amount, you will want to continuously update the table with each sale) frequently, all you have to do is right click on the table and choose “Refresh,” and your new data will be included in the pivot table.


Editing the Pivot Table


  1. The Titles of the Excel Pivot Table – Rename the titles in your pivot table to be what is applicable to your data. For the same pivot table I used: A4 = Name, B4 = Amount, and C4 = Volume.
  2. Hide Row 3. Right click on the row, and select “Hide.”
  3. Format Your Excel Pivot Table:
  • Change the Amount and Volume to CENTER alignment. (Or choose “Center Text”).
  • Highlight Column B to ($) United States Dollar. Choose the “$” in the toolbar, or do it manually by holding down CTRL+1 to get to “Format Cells.” At this point, you can alter the format of that column to be dollar signs.
  • Click anywhere in your pivot table in order to see the pivot table menu at the top. Slide your mouse over the “Pivot Table Tools.” To the right of the “Options” toolbar, there is a “Designs” tab. Click once on it to open it up in the toolbar. Under PivotTable Styles, you can pick the style that suites your needs. Note: I choose Pivot Style – Medium 4.
  • Highlight Column A. Right click. Choose “Insert.”
  • Change the Column Width to 20. Highlight columns A through C. Click on the “Format” button on the Home toolbar. In the drop-down menu, choose “Column Width.” Enter “20” and “OK.”
  • Highlight Row 1. Right click. Choose “Insert.” (Do this one more time.).




Feature: With pivot tables, you can apply filter criteria or advanced subtotaling calculations.



4. Creating the Title of Your Pivot Table:

  • Step 1: Highlight the cells in a box around B2:D3.
  • Merge and Center (easy: button on Home toolbar).
  • Choose “Outside” border.
  • Pick fill color for title (Olive Green – Accent 3, Darker 25%)
  • Pick WHITE as your font color.
  • Enter the name of the title you want for your pivot table.




Note: My Example has “Garage Sale.” Change size of this font to be 18. Adjust the font to middle alignment. Don’t forget to save this Excel spreadsheet so you do not lose your data. Delete any other tabs besides “Summary” (your pivot table) and “Data.”



This video briefly goes over the basics of a pivot table and points out more benefits of pivot tables.


A Pivot Table Tutorial


This tutorial has taught you how to do many basic skills in Microsoft Excel (along with short-cuts) in regards to creating a pivot table. It has also provided an in-depth step-by-step instructional guide on how to create an Excel pivot table through the use of an example. Once you are comfortable with pivot tables, you will be able to see new views of your data in seconds. The simple lists provided by pivot tables make them user-friendly and help with decision-making.


Comments

Submit a Comment

  • FlourishAnyway profile image

    FlourishAnyway 3 years ago from USA

    When working with Excel, there was pretty much life before Pivot tables and life after. It is an invaluable tool. You present good information for people on how to pick up this important skill.

  • misslong123 profile image
    Author

    Michele Kelsey 3 years ago from Edmond, Oklahoma

    Thank you. Yes, I have been playing with some other Excel concepts that are older, but I still lean back on the pivot table. Thanks for the read! Michele

  • moronkee profile image

    Moronke Odugbesan 3 years ago

    This is a very good tutorial. I need to learn all the basic skills in Microsoft.

    Thanks and I'll try to make my pivot table.

  • misslong123 profile image
    Author

    Michele Kelsey 3 years ago from Edmond, Oklahoma

    I am glad you found it helpful. If you are just starting out in Excel, pivot tables may be a bit much to learn. You might try =SUMIF or =VLOOKUP first if you cannot navigate around a pivot table. Once you have the hang of it, it will be very easy! Let me know if you have any questions. Thanks again! Michele

Click to Rate This Article