Are you looking forward to dealing with large datasets in excel? If that's the case then you must consider pivot tables. If you are a novice in this data world then you must focus on the pivot tables as they will be a stepping stone for you as a data analyst. So what is a pivot table? The pivot table allows you to understand the data where you can generate certain tables and graphs. I consider it as a baseline for business intelligence tools. If you look at the features of the pivot table it is quite similar to BI tools with limited options. It is an integral part of many analyses in excel and at our blogs, we have worked in-depth with pivot tables in RFM segmentation.
In today's blog, the focus will be on exploring features that a pivot table can offer for a data analyst. Let's dwell around with it!!! I will be using the Sample Superstore data for the analysis. As you must be aware of inserting a pivot table. You need to select the dataset completely and in the insert tab, you can find the option to insert a pivot table. A toggle will open and you can set the location of the pivot table.
Pro Tip-: The above-mentioned process is only good if you know that this is the final data and there will be no updates associated with it. As an analyst, you don't want to start from scratch for every quarter or month. The solution to this problem is to feed all the data into a table.
(For more pro tips do follow our blog)
After feeding the dataset into the table you can change the name of the table and you can select the option of summarizing using a pivot table which is present in the table design. After selecting that a similar toggle will open where you can see the data range and can select the location for the pivot table.
Once you are done with this now we will be looking to get the Top 10 customers and to do so we will be selecting the customer name in the rows and dragging the sales amount in the values. You will get a simple table where you can see all the customer names to select the Top 10 you need to select the filter drop down in customer name and in value filters, you can see the Top 10 option. When you select the top 10 option a toggle will open.
Now we are looking to make the analysis more interactive in nature. To do so we are adding the slicer along with the table. We are creating a table where sales are highlighted for each and every sub-category and category. If you see in the pivot fields we want to add a slicer of different regions. To achieve that you need to right-click on the region and you can select the add slicer options.
If you work with pivot tables you will a notice peculiar thing i.e. the default sum is attached to the different values (Sum of sales). To change that you need to go to the pivot table field and suppose you need to change the sum to count for quantity which is present in the values section. You need to right-click on the quantity and you can see the value field settings where you can change the sum to count and you can rename the name of that metric.
Isn't that amazing? Let's get to the key highlight of this blog and as an analyst, I try to incorporate this in most of my work. So we are taking subcategories in rows and the sum of sales in values. The idea is to show the values of sales along with the bar graph which will represent the percentage of sales in the same table. To achieve that I will drag the sum of sales again in the values so we will get a duplicate. For the second sales column format it to the percentage of column total.
After converting the second sum of sales in percentage go the conditional formatting which is present on the top. Under conditional formatting select the data bars and solid fill. It will give you a bar for the first row to obtain similar bars for the entire column you need to select the formatting toggle which is present along with the table. In that select apply to the sum of sales2 and you will get the bars for the whole column.
And you almost land up with a nice analysis but we need to focus on the formatting part. We need to select the conditional formatting and in that select manage rules which will open a toggle where select the option to edit the rules. In that change, the type to a number and the value can lie between 0.2-0.3. Also, you can change the color of the bars.
This is some sort of analysis that a stakeholder wants to see because it represents a great picture of your sales and it looks very intuitive in nature. There are a lot of different features that the pivot table offers. As a business intelligence enthusiast, I generally prefer BI tools over excel and pivot. BI tools offer a wide variety of features. In the end, the main objective is to make the data look interesting for everyone and it doesn't matter which tool is used to do that.
Insightful article!
ReplyDeleteThank you!! Stick around for more articles.
ReplyDelete