Hello all. Let’s look at Excel’s built-in Data interpretation tool – Pivot Table. Pivot table is easy to use and can interactively explore the given data. Pivot table is one of the commonly used tools and being a pro in Pivot table is key to be pro in excel. Pivot is powerful and a easy data analysis tool that doesn’t need any explicit formula. If you already have some basic knowledge about pivot table, check out the advanced section here.
Let’s start with simple pivot table and explore it’s in-depth capabilities. We are going to work with this datasheet.
Let’s quickly look at how to create a pivot table. Select the relevant / required data (A2:F100 in our example) -> Insert tab -> Pivot table. This would bring the “Create PivotTable” dialog box. For now, let’s choose the existing range (A2:F100) and choose the destination as existing sheet.
Let’s bring in total sold units into the pivot table. In the Pivot table fields, simple drag “Sale Unit” to the values section
Now, let’s break the total units sold into each product. For this, let’s bring in the product names to the table. For this, simply drag “Product” into Rows section of the PivotTable fields
Now, this gives a good information on which product has sold how many units over the year. We need to get the revenue each product has generated. For this, let’s drag Sales value to the Values section of Pivot Table Fields
By now, you must be appreciating how easy Pivot table is for data analysis. Now, let’s format the data. The sales value is currency. Let’s change the formatting by right clicking anywhere in the “Sum of Sales Value” column and do the formatting in regular way.
Let’s sort the products which has yielded more revenue. Let’s right click anywhere in the “Sum of Sales Value” -> click sort -> Sort Largest to Smallest
Do you need the total revenue of each product in terms of percentage? No problem – right click anywhere on “Sum of sales value” -> Show values as -> % of Column total
Let’s look at how to see monthly sales for each product. Very simple – drag Date field into Column section of the PivotTable Field. Note: In this case, we have Product in Rows, Sales Value in Values, Date in columns.
You may ask columns have month, date etc instead of “Date” which we dragged. Pivot automatically breaks the Date value into Months, Date and year – again to give the power for the user to break the data into more granular way.
Now, Let’s group the monthly data into Quarterly. Right click on the month cells (Jan to Dec) -> Group -> Select Quarters. Shown below
Now, let’s see how much these cars have sold each quarter region wise. Simply drag “Region” to Rows section of the PivotTable Fields window
Want to check region wise total sales? Simply drag Region and Date under columns, Keep product in Rows and Sales value as Values
By now you would have understood how pivot table comes handy for any data interpretation – all this without any formula being written. Please let us know your feedback. Thanks for reading!