Excel Report Tutorial
Microsoft Excel is an excellent program for organizing and manipulating data. Its columnar structure makes it easy to input data, while its many built-in tools are available to perform analysis. Functions, formulas and more provide robust opportunities to glean more from your data. Pivot chart reports are particularly powerful methods for discovering relationships in your data that are otherwise not obvious. Once understood, the Pivot Report is an indispensable tool for data analysis.
-
Concept
-
The most difficult aspect of creating a Pivot Report is in first understanding what the report displays. The technical process of building the report is relatively straightforward. But many Excel users are confused and thus intimidated by the unusual display of information in a Pivot Report, and this complicates the process.
A Pivot Report is a graphic representation of your data that divides it into groups and provides similar summary information for each group. For example, if you had a list of countries in one column and their form of government in another column, you could "pivot" the data around the different government types to arrive at summary information. The Pivot Report might yield that 100 countries in your list are democracies, while 10 are dictatorships. If a third column contained the population of each country, the Pivot Report could tally the total number of citizens in your list that live under each form of government. By creating these subgroups of your data, the Pivot Report then provides calculations on each group.
Creation
-
Load an Excel spreadsheet that contains a set of data that could be pivoted around a central variable. Any data set that intrinsically contains smaller subsets of data is a good candidate for a Pivot Report. Make sure that the spreadsheet contains header rows at the top of each column to label that column's information.
Click anywhere in the data, and choose the Data menu and its "Pivot Chart Report" command. A pop-up box will appear. Select the second option, "Pivot Chart Report," and press the "Finish" button. A blank Pivot Report template will appear. A "Field List" box will float over the window. The items in this box will be identical to the column headers in your spreadsheet.
Pick a field to use as the grouping variable. In the previous example, the field was "Government Type." Drag that field from the "Field List' into the bottom portion of the window in the area marked "Drop Category Fields Here."
Pick another field for the summary calculation. In the previous example, if you wanted to sum up the total number of citizens living in each form of government, the summary field was "Population," which corresponds to the column header from the original spreadsheet. Drag this field into the middle of the Pivot Report in the "Drop Data" section.
A bar graph is immediately constructed to generate the sub-groups and the information for each group. The type of calculation may be changed by double-clicking the button on the top left of the chart, which by default says "Sum."
The Pivot Report is interactive. Drag the fields off the chart back onto the field list if you wish to pivot the report around different criteria. It can be illuminating to play with your data in this fashion and learn information quickly using this interface.
-
