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

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.

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.

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

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.

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 “#.”

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.

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:

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

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.

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.