Home Velixo Excel Tips: Why you should be creating a pivot table to analyze data.

Velixo Excel Tips: Why you should be creating a pivot table to analyze data.

Tips & Tricks
7 min

As companies continue to collect an ever-expanding amount of data, they’re actively seeking more ways to derive additional value from it. Companies of all sizes are investigating new analytic tools to tackle an array of business goals. However, we don’t need to reinvent the wheel with new tools, because you already have one that can do the job. The next step is being able to use it to it’s full functionality.

So what can Excel do to help? 

Excel is an easy, well-known, cheap and versatile tool which can be used to create a variety of reports and spreadsheets. By leveraging the pre existing asset of Excel, your business will be able to get more out of it’s data.

What are Pivot Tables? 

Pivot Tables are a great way to summarize, analyze, explore, and present your data, and you can create them with just a few clicks. Pivot Tables are highly flexible and can be quickly adjusted depending on how you need to display your results. You can also create Pivot Charts based on Pivot Tables that will automatically update when your Pivot Tables do.

Note: Some of the screen shots in this article were taken in Excel 2016, so while the view may be different for some, the functionality is the same.

For example, here’s a simple list of household expenses, and a Pivot Table based on it:

Before you get started:

Create a Pivot Table

If you have limited experience with Pivot Tables, or are not sure how to get started, a Recommended Pivot Table is a good choice. When you use this feature, Excel determines a meaningful layout by matching the data with the most suitable areas in the Pivot Table. This helps give you a starting point for additional experimentation. After a recommended Pivot Table is created, you can explore different orientations and rearrange fields to achieve your specific results. The Recommended Pivot Tables feature was added in Excel 2013, so if you have an earlier version, follow the instructions below for how to manually create a Pivot Table instead.

You can also download our interactive Make your first Pivot Table tutorial.

Recommended Pivot Table

  1. Click a cell in the source data or table range.
  2. Go to Insert > Tables > Recommended Pivot Table.

  3. Excel analyzes your data and presents you with several options, like in this example using the household expense data.

  4. Select the Pivot Table that looks best to you and press OK. Excel will create a Pivot Table on a new sheet, and display the Pivot Table Fields List.

Manually create a Pivot Table

  1. Click a cell in the source data or table range.
  2. Go to Insert > Tables > Pivot Table.

  3. Excel will display the Create Pivot Table dialog with your range or table name selected. In this case, we’re using a table called “tbl_Household Expenses”.

  4. In the Choose where you want the Pivot Table report to be placed section, select New Worksheet, or Existing Worksheet. For Existing Worksheet, you’ll need to select both the worksheet and the cell where you want the Pivot Table placed.
  5. If you want to include multiple tables or data sources in your Pivot Table, click the Add this data to the Data Model check box.
  6. Click OK, and Excel will create a blank Pivot Table, and display the Pivot Table Fields list.

Working with the Pivot Table Fields list

In the Field Name area at the top, select the check box for any field you want to add to your Pivot Table. By default, non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the Pivot Table fields, or if you no longer want an item in your Pivot Table, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the Pivot Table features that makes it so easy to quickly change its appearance.

Pivot Table Values

Refreshing Pivot Tables

If you add new data to your Pivot Table data source, any Pivot Tables that were built on that data source need to be refreshed. To refresh just one Pivot Table you can right-click anywhere in the Pivot Table range, then select Refresh. If you have multiple Pivot Tables, first select any cell in any Pivot Table, then on the Ribbon go to Pivot Table Tools > Analyze > Data > click the arrow under the Refresh button and select Refresh All.

Deleting a Pivot Table

If you created a Pivot Table and decide you no longer want it, you can simply select the entire Pivot Table range, then press Delete. It won’t have any affect on other data or Pivot Tables or charts around it. If your Pivot Table is on a separate sheet that has no other data you want to keep, deleting that sheet is a fast way to remove the Pivot Table

We hope you found this article helpful!

Velixo Newsletter

Subscribe to our newsletter to receive news and announcements.