How to Create a Pivot Table in Excel 2013


Last updated: January 25, 2017

Learning how to make a pivot table in Excel 2013 will give you a new tool in your Excel utility belt that can make sorting and analyzing data much simpler. A pivot table in Excel 2013 offers a variety of options for comparing data in columns, and grouping similar data in a way that would otherwise be difficult to do manually. When you create a pivot table in Excel 2013, you are taking data from your spreadsheet and inserting it into, by default, a new format in another sheet of your Excel workbook. Once the data has been reconfigured for the pivot table, you can adjust the way that it is organized and displayed to help you better understand the information that the spreadsheet contains. So read below to learn how to make a pivot table in Excel 2013.

If you are planning to install Office 2013 on a second computer, consider getting an Office 2013 subscription instead. This allows you to install your copy of Office on up to five computers, with the ability to remove and add those licenses if you get a new computer, or want to switch a license to a different computer.

 

How to Make a Pivot Table in Excel 2013

If you find yourself asking the question “what is a pivot table,” then know you are not alone. According to Wikipedia, a pivot table is “a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot table can automatically sort, count, total or average the data stored in one table or spreadsheet, displaying the results in a second table showing the summarized data”.

The way that I use pivot tables the most often it to quickly total amounts that are associated with different rows. For example, I have created a very simple spreadsheet below that lists sales amounts for three different members of a sales team. There is only a small amount of data in this table in an effort to keep this as simple as possible, but this same process can easily be expanded to handle much larger amounts of data, and prevent you from needing to write any formulas or macros to get the information that you want. We are going to create a simple pivot table that takes these 8 sales and combines them into a three-row report that shows the name of the salesperson and their total sales. So read below to learn how to create this type of pivot table in Excel 2013.

 

Step 1: Open the Excel 2013 spreadsheet that contains the data that you want to put into a pivot table.

 

 

Step 2: Use your mouse to highlight all of the data that you want to include in the pivot table.

Highlight the data you want to include in the pivot table

Highlight the data you want to include in the pivot table

 

 

Step 3: Click the Insert tab at the top of the window, then click the PivotTable icon in the Tables section of the ribbon. This opens a Create PivotTable window.

Click Insert, then PivotTable

Click Insert, then PivotTable

 

 

Step 4: I am going to leave these settings at their defaults, which will create a new worksheet for the pivot table. Click the OK button to continue.

Click the OK button

Click the OK button

 

 

Step 5: Check the box to the left of each of your column names in the PivotTable Fields column at the right side of the window.

Check the box to the left of each column to include in the PivotTable

Check the box to the left of each column to include in the PivotTable

 

 

Step 6: View your pivot table in the spreadsheet. You will see that the initial data from my spreadsheet has been combined into easy to read totals that give me the totals that I wanted from my data.

Sample PivotTable

Sample PivotTable

 

 

Now that your data is in the pivot table, you can manipulate it by double-clicking a column heading, then changing a setting on that screen. For example, if I wanted to view the number of sales instead of the total sales amount, I could change the option under Summarize value field by to Count instead of Sum.

Change your PivotTable options

Change your PivotTable options

 

 

Now that you have seen how a basic pivot table works, take some time to experiment with the feature to see what different types of data you can produce from your spreadsheets. Pivot tables are incredibly helpful for reducing the amount of manual addition that anyone should have to do with Excel data that can’t be easily totaled with a formula, so it can be a real time-saver. If you need more help with Excel 2013 pivot tables, visit Microsoft’s Excel 2013 help site, where they provide a very comprehensive view of the capabilities of pivot tables in Excel 2013.

 

Some helpful tips for working with pivot tables in Excel 2013

  • The order that you click the boxes in step 5 can matter. If Excel 2013 isn’t displaying your data the way that you would want, try clicking the boxes next to each column in a different order.
  • You can format your pivot table cells in a similar manner to how you would a regular cell. Simply right-click the cell, then choose the Format Cells option.
  • There is an option on the Design tab under PivotTable Tools call Banded Rows. If you check that option Excel will automatically alternate the fill color for each row in your pivot table. This can make the data a little easier to read.
  • Additionally, there are options on that Design tab for Subtotals, Grand Totals, Report Layout and Blank Rows. Once you have your data showing correctly in the pivot table, these options can help you create well-formatted reports that you can share with your colleagues.

 

For more information on Excel 2013, read our article about changing the default save location in Excel 2013. You can choose to save your spreadsheets to your computer by default, instead of the SkyDrive option that many Office 2013 installations will do by default.

solveyourtech.com newsletter
vintage t rex t-shirt

Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.