When you maintain an Excel sheet, I make sure you don’t provide data just for archiving purposes. Most of the times you intend to analyze and
organize that data
, Right? Now MS Excel has got a great feature called
Summary
allows you to summarize large tables as reports. Then you can analyze your data without much manual effort and time. Let us see how.
This is part of a sample data sheet that I maintained during a sporting event at my office. It holds subscription fees for different games, which are collected from different floors of the office building and by different people.
I want to get the total amount i.e. the total amount for each tier, for each event and the total amount that each SPOC collects. The solution is to create a pivot table. A sample for the above data is shown below.
Create a Pivot Table
The minimal effort required to create such a table will not undermine the enormous potential of this tool.
The tutorial is based on MS Excel 2013. The steps are also more or less the same on other versions.
Step 1: Select the data/cells for which you want to create a pivot table.
Step 2: Navigate to Insert tab and click on reading tools PivotTableto the extreme left of the ribbon.
Step 3: The Create a pivot table window will appear. It will display the selected data range. If you want, you can change it here or choose an external data source. In addition, you have an option to create the table on the same sheet or a new table (preferably create a new table to avoid errors).
Step 4: This will activate Summary right panel. Select the fields that you want to base your analysis on.
Step 5: Now, you will start to get a feel for what a Pivot Table is. Try and choose different combinations with different row and column labels to understand the details.
Here, check out another report for the selected fields and corresponding row/column labels. Try and compare the pictures (left and right).
Let me tell you straight that you will understand this feature in detail just by using and practicing. Reading the manual just once is not enough.
Some useful tips
- Choose your row and column labels carefully as they determine how the analyzed data is arranged.
- You can move fields between row and column labels by dragging and dropping.
- For a field, the context menu shows you an option called Value Field Settings that allows you to choose the nature of the report or the field data.
- You can apply filters to the results to get custom reports.
Conclusion
This tutorial is intended to provide basic technique for creating pivot tables, and some rules and standards that can help you get started. There’s a lot more to it though. You will learn ropes just by making lots of ropes and playing around with options.
Categories: How to
Source: thpttranhungdao.edu.vn/en/
