Use PivotTables and other business intelligence tools to analyze your data

Recommended PivotTables for your data

When you want to make sense of large amounts of data—whether it’s in your worksheet or accessible someplace else—Excel 2013 gives you powerful tools.

You can analyze data in one or multiple tables, and create interactive and visual reports you can share with your stakeholders.

Tips before you start

  • Let Excel recommend a PivotTable    To quickly show the data you want to analyze in Excel 2013, start by picking a PivotTable in a layout that Excel recommends for your data.

  • Analyze data in multiple tables   You can analyze data from more than one table in your PivotTable report in Excel 2013, even if you don’t use Power Pivot. The Data Model functionality is built into Excel 2013. Just add data in multiple tables in Excel and then create relationships between them in a PivotTable or Power View sheet. Voila! You now have a Data Model to add more power to your data analysis.

  • Plot your data directly into an interactive PivotChart    In Excel 2013, you can create a standalone (decoupled) PivotChart that lets you interact with and filter data right in the chart.

  • Tap into the full power of Power Pivot and Power View    If you’ve got Office Pro Plus installed, try taking advantage of these powerful add-ins:

    • A built-in Data Model might be all you need to analyze data in multiple tables, but Power Pivot helps you create a sophisticated data model in a separate Power Pivot window. There are a few differences you might want to compare before you get started.

    • Power View helps you turn your Power Pivot data (or any other Excel data in a table) into a powerful interactive report that looks professional. Just click the Power View button on the Insert tab to get started.

Create PivotTable or PivotChart reports

Create a PivotTable to analyze worksheet data

Make better business decisions by looking at your data from different angles in a PivotTable report. Excel helps you get started by recommending one that works for your data.

Create a PivotTable to analyze external data

When the data you want to explore is stored in another file outside of Excel, like an Access database or Online Analytical Processing (OLAP) cube file, you can connect to that external data source to analyze its data in a PivotTable report.

Create a PivotTable to analyze data in multiple tables

If you want to analyze data in multiple tables, you can do that in Excel 2013. Learn about different ways to create relationships between multiple tables in a PivotTable report for powerful data analysis. Behind the scenes, Excel creates a Data Model.

Learn how to use a Data Model in Excel 2013

Before you start out on your own, it might be helpful to follow the instructions in this tutorial to build a sample PivotTable in Excel that combines data from multiple tables into a Data Model.

Use the Field List to show data in a PivotTable

After you create a PivotTable based on worksheet data, external data, or multiple tables, you can access the Field List to add, arrange, and remove fields in your PivotTable report at any time.

Create a PivotChart

For a visual presentation, you can create a PivotChart with interactive filtering controls so you can analyze a subset of your data. Excel can even recommend a PivotChart for your data. If you just want an interactive chart, you don’t need to create a PivotTable first.

Delete a PivotTable

When you want to delete a PivotTable, the trick is to select the entire PivotTable before you press Delete, even when it has a lot of data—this article provides a quick way to select an entire PivotTable.

Change the format of your PivotTable report

Change the data layout of a PivotTable

After you create a PivotTable and add the fields you want, you can change the layout to make the PivotTable easier to read and scan. Just choose a different report layout for instant layout changes.

Change the style of your PivotTable

If you don’t like the look of your PivotTable after you create it, you can pick a different style. For example, when you have a lot of data in your PivotTable, it may help to show banded rows or columns for easy scanning or to highlight important data to make it stand out.

Show PivotTable details

Sort data in a PivotTable

Sorting helps you organize large amounts of PivotTable data so it’s easier to find the items you want to analyze. You can sort data in alphabetical order or from highest to lowest values (or vice versa).

Filter data in a PivotTable

To focus on a smaller portion of a large amount of your PivotTable data for in-depth analysis, you can filter the data. There are several ways to do that. For example, inserting one or more slicers gives you a quick and effective way to filter your data.

Group or ungroup data in a PivotTable report

Grouping data in a PivotTable can help you see a subset of the data you want to analyze.

Drill into PivotTable data

Drilling into large amounts of data in a PivotTable hierarchy has always been a time-consuming task with lots of expanding, collapsing, and filtering.

In Excel 2013, the new Quick Explore feature lets you drill into your OLAP cube or Data Model-based PivotTable hierarchy to analyze data details on different levels. Quick Explore helps you navigate to the data you want to see, and acts like a filter when you drill down. The button shows up whenever you select an item in a field.

Use a PivotTable timeline to filter dates

Instead of playing around with filters to show dates, you can now use a PivotTable timeline. Add a timeline to your PivotTable, filter by time, and zoom in on the period you want.

Calculate PivotTable values

Show subtotals in a PivotTable

In a PivotTable, subtotals are calculated automatically and shown by default. But if you don’t see them, you can add them.

You might also want to know how to calculate a percentage for subtotals, or hide subtotals and totals to remove them.

Sum values in a PivotTable

To summarize values in a PivotTable, use functions like Sum, Count, and Average. Summary functions aren’t available in PivotTables that are based on OLAP source data.

Change or update PivotTable data

Change the source data for a PivotTable

After creating a PivotTable, you might want to change the source data it’s analyzing—for example, to include more or less data.

Update (refresh) data in a PivotTable

When a PivotTable is connected to external data, refresh it from time to time to keep your PivotTable data up to date.

Tap into the power of Power Pivot

Powerful data analysis and data modeling in Excel

If you’ve got Office Professional Plus installed, start the Power Pivot add-in that comes with Excel 2013 to perform powerful data analysis. Then use the Power Pivot window to create a sophisticated Data Model.

Learn how to use a Data Model in Excel 2013

Import multiple tables of sample data at the same time in this tutorial. In the second half of this tutorial, you’ll work with the Data Model in the Power Pivot window.

Get data using the Power Pivot add-in

Try importing relational data in the Power Pivot window as a fast and efficient alternative to importing or connecting to data in Excel.

Create a relationship between two tables

Power up your data analysis by creating relationships between different tables that have matching data, like a similar ID field. Table relationships let you build PivotTable reports that use fields from each table, even when the tables came from different sources.

Use calculations in Power Pivot

Solve data analysis and modeling problems in Power Pivot by using calculations such as AutoSum, calculated column and calculated field formulas, or custom Data Analysis Expressions (DAX) formulas.

Add KPIs to your PivotTable

Use Power Pivot to create key performance indicators (KPIs) you can add to your PivotTables.

Optimize a Data Model for Power View

This tutorial shows you how to make changes in your Data Model to enhance Power View reports.

Explore your data with Power View

Explore, visualize, and present your data with Power View

Use Power View (which comes installed with Office Professional Plus) to create interactive charts and other visualizations in separate, dashboard-like Power View sheets you can present to your stakeholders.

At the end of Learn how to use a Data Model in Excel 2013, you’ll get some helpful instructions for optimizing your Power Pivot data for Power View.

Create a Power View report with Microsoft Azure Marketplace data

In this tutorial, you download free data from Microsoft Azure Marketplace and combine it with other data on the Web to create interesting, interactive Power View reports. Find out which airports and airlines performed best in December 2011!

See Power View and Power Pivot in action

Watch these videos to learn what Power View can do for you with Power Pivot in a supporting role.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!