How to create a pivot table in Excel

Enter your basic data to get started

The first step in any pivot table how-to is entering your data. The method of entry will depend on your situation. Some users will simply type all their data in manually. If you have an existing document, you also have the option of using the File and Open tabs to upload a document. For ease of use, ensure the first row or column of your data has labels that explain what the data is. For example, if you’re tracking employee sales at your pizza restaurant, make sure you have an “Employee name” label above where you list each name.

Highlight your cells and open the pivot table creation box

Use your mouse or keyboard keys to highlight the complete range of cells you want to turn into a pivot table. Then go to the Insert tab at the top of the page and click the PivotTable icon. This opens up a whole option box on the side of your Excel document. Your first option will be to create your own custom pivot table with all your own desired parameters. However, scrolling down will show a whole list of recommended pivot tables. They aren’t always useful, but it’s worthwhile to check them out. There’s a chance the recommended pivot table will have everything you need.

Whatever option you choose, you’ll be able to pick between launching your new table on your current sheet or on a new sheet.

Choose how you want your data arranged

Once you create the pivot table, you have many different ways of looking at your data. You’re able to select one of your labeled fields as either the new column or row label by dragging the label to the Rows or Columns section. Another very helpful arrangement is dragging your fields into the Values section. This allows you to count and summarize large amounts of data. For example, dragging “Employee” into the Rows section and “Pizza” into the Values section tells you how many pizzas each employee sold.

Alter settings to get even more nuanced pivot tables in Excel

The Values section is one of the most useful parts of a pivot table in Excel. Once you drag and drop a field into the values area, you have the option of hovering over the field and clicking a drop-down menu. Selecting the Value Field Settings allows you to adjust how your data is calculated. Excel defaults to adding your numbers together, but it will also perform other calculations. It will do things like rank from largest to smallest. So for our pizza example, we would use the settings to find the average amount each employee makes for each sale.

Learn how to navigate between various types of pivot tables in Excel

Once you’ve figured out the basics of creating a pivot table in Excel, the only thing left to do is integrate it into the rest of your spreadsheet. You are able to close the pivot table option box by clicking on any blank cell or reopen it by clicking on the existing pivot table. Excel always saves all data associated with that table, so you have the option of going back to the table and adjusting it to show previously hidden data. You also have the option of adding multiple pivot tables to a single document or adding plain data to another area of the document.