Once you have a large list of data in a spreadsheet you want to be able to analyse and present this information in the fastest & most concise manner. Excel Pivot Tables are a powerful Excel tool in extracting information from a large set of data in Microsoft Excel.
Excel Pivot Tables will extract and summarise information quickly and automatically calculate Grand Totals and Sub Totals for you.
With the use of Excel Pivot Tables, complex reports can be created in seconds and tweaked to analyse the data based upon multiple criteria with the drag of a mouse.
Charts can also be produced easily based upon the data in the Excel Pivot Table at the click of a button. Furthermore, the charts can also be changed interactively depending upon the results you require, again at the drag of a mouse.
Using the example data below we can produce meaningful reports to answers to the following questions in a few easy steps.
- Which Region has sold the most units in 2003.
- Which month produced the highest sales figures in 2003
Using the raw data above, the following Excel Pivot Table report was produced to answer which region has sold the most units in 2003.
The following sections provide an overview of producing and using Excel Pivot Tables reports and Excel Pivot Table Charts.
An Excel Pivot Table report can summarize large amounts of data in a table. Excel Pivot Tables can sum, average, count and use percentages of ‘n’ without entering a single formula.
With an Excel Pivot Table you can very quickly;
- Add, change or remove data.
- Change the layout and format
- Apply filters to quickly narrow the criteria of your report.
- Add depth to your reports by including multiple categories.
Once you have created an Excel Pivot Table report it is easy to change the emphasis
If you wanted to alter the original report shown earlier to show which ‘Months’ were the most prosperous rather than which ‘Manager’ we just simply swap the Manager field, with the Month – see below.
If you want to locate the ‘Regions’ down the left column and have the Month field across the top, then these are easily swapped in seconds to alter the layout of your report.
Add depth to your Excel Pivot Table reports by including multiple categories.
Adding an extra field/s to your Excel Pivot Table is easy and can add depth to your report.
For example, adding the Manager field to the column of your Excel Pivot Table will transform the emphasis of your report.
Using data tables with a large variety of values can mean your Excel Pivot Table soon grows in size.
It is easy to filter these values to make the Excel Pivot Tables easier for the intended audience to digest.
This has already been done in our original example by adding the 2003 Year to the Page Field at the top of the Excel Pivot Table. We can use the drop down menu to change this for a different Year such as 2010, or even a combination of years.
|
