Many of you may already know that Microsoft Excel is a brilliant data analysis tool. Microsoft Excel has several functions and add-ins which you can use to analyze data and the possibilities are limitless! Here we will see how to use a pivot table to analyze data in Excel.
A pivot table is an interactive table to summarize, cross-tabulate, analyze and explore large amounts of data. There are also pivot chart reports that can help you visualize the PivotTable report’s summary data, so that you can easily see comparisons, patterns, and trends. The beauty of pivot tables lie in the fact that you can easily rotate rows and columns of data to view various data summaries. These data summaries help you to make informed decisions about critical data.
The PivotTable works by summarizing data using a summary function that you specify, such as “Sum”, “Count”, or “Average”. You can automatically include subtotals and grand totals or use your own formulas by adding calculated fields and items.
In Microsoft Excel 2010 , the PivotTable and PivotChart Wizard which was available in microsoft Excel 2007 and earlier versions of Excel, has been replaced with PivotTable and PivotChart commands on the “Insert” tab in “Tables” group. The PivotTable and PivotChart Wizard is still available, and you can use it as needed by adding it to the Quick Access Toolbar, or by pressing ALT + D + P to start it.
A Pivot Table wizard provides help in creating pivot tables. A Pivot table report can be created on an external data source i.e. data stored outside the Excel workbook. Examples include a web server, or a databases in MS Access, SQL Server etc. Here we will see the most common usage i.e. creating a pivot table report for the current worksheet data.
How to create a Pivot table report
- 1. On the Insert tab, in Tables group, click PivotTable. To create a PivotChart which is based on the PivotTable report, click the arrow below PivotTable and then click PivotChart.
- 2. In the Create PivotTable dialog box, make sure that “Select a table or range” is selected, and then in the Table/Range box, verify the range of cells. Excel automatically determines the range for the PivotTable report, but you can replace it by typing a different range or a name that you defined for the range.If the source data is in another worksheet or workbook, include the workbook and worksheet name by using the following syntax [workbookname]sheetname!range.
- 3. To place the PivotTable report in a new worksheet starting at cell A1, click New Worksheet. To place the PivotTable report at a specific location in an existing worksheet, select “Existing Worksheet”, and then in the Location box, specify the first cell in the range of cells where you want to position the PivotTable report. In this example, we will position the pivot table in the same worksheet at Sheet1!$A$13
- 4.Finally Click OK. Excel adds an empty PivotTable report to the specified location and displays the PivotTable Field List so that you can add fields, create a layout, and customize the PivotTable report.If you also created a PivotChart, it is displayed on top of the associated PivotTable report i.e the PivotTable report which supplies the data for the PivotChart report. This is created automatically, when you create a new PivotChart report. When you change the layout of either report, the other report also changes.However, a pivotChart and its associated PivotTable report must always be in the same workbook.
How to do Excel data analysis with Pivot tables?
To analyze data, you can do one or more of the following in the PivotTable Field List.
- To place a field in the default area of the layout section, select the check box next to the field name in the field section.By default, non numeric fields are added to the “Row Labels” area, numeric fields are added to the “Values” area, and Online Analytical Processing (OLAP). Date and time hierarchies are added to the “Column Labels” area.
- To place a field in a specific area of the layout section, right-click the field name in the field section, and then select “Add to Report Filter’, “Add to Column Label”, “Add to Row Label”, or “Add to Values”.
- It is much more easier to drag and drop fields in the layout section, as you desire.
Pivot Table improvements in Excel 2010
In Excel 2010, working with pivot tables on large amounts of data is a pleasure. Multi-threading in Excel 2010 helps improve the overall PivotTable performance. This means that you’ll notice faster results when you work on large amounts of data.
Click the options and Design of the PivotTable Tools which becomes available when you click anywhere in a PivotTable and explore what you can achieve in a pivot table report. Excel 2010 has slicers to filter the data. Slicers provide buttons that you can click to filter PivotTable data.
In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable report. In earlier versions of Microsoft Excel, you can use “report filters” to filter data in a PivotTable report, but it is not easy to see the current filtering state when you filter on multiple items.
Enjoy Excel data analysis with pivot tables, charts and slicers!