ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

  • misslong123 profile imageAUTHOR

    Michele Kelsey 

    5 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

  • moronkee profile image

    Moronke Oluwatoyin 

    5 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 imageAUTHOR

    Michele Kelsey 

    5 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

  • FlourishAnyway profile image

    FlourishAnyway 

    5 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.

working

This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

Show Details
Necessary
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
Features
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Marketing
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Statistics
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)