How To Create A Pivot Table In Microsoft Excel And Google Sheets

Pivot Tables are meant to simplify (and partially automate) the ways you can organize and interpret the various data points in your spreadsheets. Think of it as a way to make either Excel or Sheets refine the available information down to a more digestible amount, while also letting you see ways in which some of your data points might be connected or overlap. They're not unlike one of the many other spreadsheet "hacks" out there you can take advantage of, should the occasion call for it.

That said, Pivot Tables do have their limitations. As you might have guessed, they're not something you can simply check off in an option window and then let the software do all the work — you're going to need to manually prep them, as well as adjust various settings in order to get the precise distillation of data you're looking for. Another caveat is that any errors or inaccuracies in your source data can (and likely will) skew the Pivot Table results, which can be tricky to troubleshoot with larger sets. Pivot Tables also aren't guaranteed to update themselves as new information is added to the source spreadsheet, which means you'll probably need to continually update them manually as the details in the spreadsheet grow and change.

With all of that said, here's how to start using Pivot Tables.

Before you get ready to set up any Pivot Tables in Excel, it's important to make sure your spreadsheet doesn't contain any formatting quirks that could disrupt the automated part of the process. Make sure you don't have any blank columns or rows, and use the same date formatting for all dates in your spreadsheet (avoid something like "4/15" and "April 15" in the same row or column). These steps apply to Excel in Windows, macOS, and on the web.

1. Open the spreadsheet you want to use as a data source, then manually select all of the rows and columns you want to include in your Pivot Table.
2. Click on Insert, then select Pivot Table.
3. If you want to add or otherwise change source data, click the Source text box and either type a range of tables or columns into the box or manually select them on the grid. Press Enter or Return when finished.
4. Choose whether to add the Pivot Table to an existing worksheet or a new one. If you choose an existing worksheet, you'll also need to select the cell you want the new table to appear.
5. Click OK when you're finished.

Getting rid of Pivot Tables you don't want or need anymore is also very simple. Just select the Pivot Table range and Delete it. Or, if the Pivot Table was saved as a separate document, you can delete the document instead.

Using Pivot Tables in Google Sheets is slightly more limited than Excel, simply because you cannot set them up through Android or iOS — it must be done via a web browser on a computer.

1. Open the spreadsheet you wish to use in Google Sheets, then manually select all of the cells containing data you want to use as your source.
2. Select Insert from the menu bar at the top of the Google Sheets page, then choose Pivot Table.
3. Manually type in additional data ranges in the text box (or select them on the spreadsheet) if needed.
4. Choose whether to save the Pivot Table as a New sheet or add to an Existing sheet. If you save to an existing sheet, manually type in the sheet name and cell you want it to display in, or select the cell from the open sheet.
5. Click Create when ready.
6. To remove a Pivot Sheet, select the cell it's located in (the top-left corner of the table) and press Delete.

Once your Pivot Table is set up, you can also edit it later by moving the cursor over the Pivot Table and clicking on the Edit icon that appears in the bottom-left corner of the table itself. Though this may not be necessary most of the time as Google Sheets will automatically update the Pivot Table when the data in any of the source cells is changed.