MS Excel is an excellent and extensively programmed software which performs mighty calculations, arrange and analyze large amounts of data and visually represents statistical information. PivotTable is a powerful tool of MS Excel.
What is a pivot table?
Pivot tables are meant to analyze and arrange data without the use of formulas. It summarizes the data of a detailed table.
Uses of pivot tables:
They are utilized in situations where data needs to be sorted, arranged according to a data field, and counted. Moreover, it is used to:
• Calculate percentages of the totals
• Organize data into columns
• Arrange data in an ascending or descending order
• Take average of different totals of the fields
If we put this in simple words: You are a retail store owner dealing in bottles. Every day, you note down the sales of your bottles in terms of brand’s name, quantity sold, amount received and the date of sale. After a year, you plan to analyze your sales, with respect to month and brand. Simple! Make a PivotTable and you can have all the entries arranged according to months and brands. You can total them up to see which brand and month was most the most profitable. You can implement this basic concept on extensive spreadsheets related with revenue statements, inventory details and order placements in just the same way.
Creating a PivotTable
Learning pivot tables is very easy because it doesn’t involve any coding or application of special skills; rather it is based on intuitive thinking and general use of software settings. Although YouTube and video tutorials are the most effective means of learning about the use of PivotTable however, here are some basic steps you need to know about creating it in MS Excel:
1. Go to the ‘Insert’ tab and click on PivotTable
2. You will be prompted to click on the range. Simply select all the entries on which you need processing
3. Drag a field name in the ‘Row Label’ area, for example, month’s name
4. Drag a numeric field name in the ‘Values’ area, for example, sales
5. You can go in the ‘Value Field Settings’ by left clicking the numeric field name and use multiple functions like Average, Max, Min, Variance and Standard Deviation. (Many of you might have studied the course on Further Mathematics in A-levels. It would require at least 10 minutes of calculations and usage of multiple formulas to get the standard deviation but here, it’s so easy and quick)
6. You can also use the Filter option of PivotTable to view the data according to a specific entry for example, if you have a number of brands like Samsung, Apple, Nokia and want to review the PivotTable with respect to Nokia only, mark the ‘Nokia’ label from the ‘Filter’ option table.
Learn this useful feature and become a more productive employee for your company.