5 Excel Tricks You’ll Love Working with as an Analyst

Overview

• Pivot tables are a powerful Excel feature for quickly analyzing data and extracting business insights
• Here are five Pivot Table tips in Excel to make you an efficient analyst

Introduction

Let’s say you have a huge dataset with thousands of rows and multiple columns. You need to quickly analyze what you have in Excel and come up with a few insights before facing your supervisor or stakeholder. Sounds familiar? It’s a situation I’ve been in plenty of times as an analyst.

There’s one powerful Excel feature that always delivers – Pivot Tables!

I am at a stage where I instinctively think of Pivot Tables in Excel when I’m given large datasets. It’s such a wonderfully flexible and in-depth tool – there’s simply no comparison. Pivot tables have helped me summarize massive amounts of data into simple and powerful reports. These tables reveal a lot about the data by converting any column-based data into reports.

Pivot Tables are the nervous system of analytics in Excel.

Here, I will be sharing some of the most useful Pivot Table tricks that I have used in my career. These will help you analyze your data at an even more granular level with just the click of a few buttons.

This is the third article in my Excel hacks, tips, and tricks series. I highly recommend going through the previous articles to become a more efficient analyst:

• 5 Handy Excel Tricks for Conditional Formatting Every Analyst Should Know
• 3 Classic Excel Tricks to Become an Efficient Analyst

I encourage you to check out the below resources if you’re a beginner in Excel and Business Analytics:

• Microsoft Excel: Formulas and Functions (Free Course!)
• Certified Business Analytics Program

Table of Contents

1. Pivot Table Trick #1 – Average of Values
2. Pivot Table Trick #2 – Percentage of Row Total
3. Pivot Table Trick #3 – Pivot Charts
4. Pivot Table Trick #4 – Add Number Format
5. Pivot Table Trick #5 – Pivot Table Slicers

Pivot Table Trick #1 – Average of values

Pivot Tables provide plenty of options for calculations to summarize your data. From the count of values to the variance of values, the options are endless. Excel provides the sum of values as the default calculation so let us see how can we use other useful calculations.

Here, we will see how to use the average of a set of values.

Let’s take up an interesting problem statement that will help you in your daily life! We are provided with the day-wise expenditure of a particular family. We need to calculate the average amount spent by each family member (sounds familiar?).

You can refer to the below video or follow the steps after this:

https://player.vimeo.com/external/463713844.sd.mp4?s=c3f22e21e10e67db24c8162274e81eab0903efd7&profile_id=165

Step 1 – Select a cell in the table

Click on any cell present in the table:

Step 2 – Locate the Pivot table

We’ll locate the pivot table in the Excel ribbon. Go to Insert -> Pivot Table:

Step 3 – Create a Pivot Table

Finally, we create our pivot table. In the Create Pivot Table window, you’ll notice that the entire range of the table is automatically selected.

You also have the flexibility to form this pivot table in the existing sheet or in a new sheet. Here, we’ll form it in a new sheet:

Step 4 – Select the Pivot Table fields

We don’t require all the columns to complete our analysis so we’ll choose only the necessary fields. On the right side, a toolbar will appear – PivotTable Fields. We will choose the required field, in our case, these are Member and Amount:

Step 5 – Drag fields between areas.

Since we are using only two fields in our analysis, Excel automatically detects the Member field as a row and the Amount field as a value so we don’t need to drag fields in this case.

Step 6 – Choose Average as value field calculation

If you notice, Excel has taken the sum of amount as the default calculation but we require the average of values in our analysis. So let us see how can we do this.

In the PivotTable Fields pane, click on the dropdown – Sum of Amount. Go to Value Field Settings. You’ll find a bunch of different calculations here. Let us select Average:

There you go! We can see the average expenditure of everyone in the family. Congratulations on making your first pivot table!

But do you wonder why mom spent the most amount out of all the family members? What if I want to check the reason behind the extremely high expenditure?

Pro Tip – Double click on the cell item to see more information

To know where mom spent her money, simply double click on the Mom cell. You will get options to choose the detail field. We will select the field – Spent on and press ‘OK’:

We can see that her major expenditure went towards a donation for a relief fund. What a great cause!

So far, we have only understood the average amount spent by each family member. However, it doesn’t provide a clear picture. We as humans tend to understand much more intuitively in terms of percentages so let us see how we can get that in Excel.

Pivot Table Trick #2 – Percentage of Row Total

This simple trick can provide really interesting insights and it is definitely my all-time favorite! Let’s check out the percentage distribution of the amount spent by the individual family members.

You can refer to the video or follow the steps after the video:

Step 1 – Add Amount Field Again

To get the percentage distribution, we need to add the Amount field again to the value field. We’ll do this by dragging the field into the values area:

Step 2 – Show value as

In the newly added column, select one of the cells and right-click on that.

You’ll find an option of Show Value As. It has a bunch of options that you can explore. We’ll be choosing “% of parent row total” for our analysis:

In this analysis, we realize that the member – Me, perhaps the person who made the data, has actually spent 46.5% of the money. That’s almost half of the expenditure of the entire family! To understand more about the underlying trend, you can double click and get more details!

Pivot Table Trick #3 – Pivot Charts

Now here’s a challenge – what if other family members make a demand for the result of this analysis? We assume that they are not well versed in numbers as we are. What is a better way to present this analysis to a non-techical audience?

A visual representation which we can easily generate using Pivot Charts!

Note: We are using the same pivot table and working in the same sheet as that of the pivot table.

Step 1 – Choose the columns for analysis

Since we require the percentage of the sum of amount spent by each family member, we’ll remove the average of amount field from the analysis:

Step 2 – Locate Pivot Charts

Go to Insert -> Pivot Charts:

Step 3 – Select a desirable plot

Excel has plenty of plots to choose from. We’ll go with a pie chart as it is the most suitable for our analysis:

Step 4 – Customize the design

You can customize your pie chart in just one click and choose from a variety of beautiful options provided by Excel.

Now our analysis is summarized in the form of a pivot chart which makes our analysis really easy to understand!

Did you notice that there is something missing in the analysis? Can you guess what that is? The amount field doesn’t really have a format and that might make it confusing. Let us learn how to add a number format in Excel in just a few clicks.

Pivot Table Trick #4 – Add Number Format

Number formats are very important and they provide the identity to the numbers present in the pivot table. In the data we are using, the family belongs to an Indian household so the expenditure should be in Rupees. Let’s add this to our existing values!

You may follow the steps or refer to this video:

Step 1 – Locate Value Field Settings

In the existing pivot table, click on the cell for which you want to change the format. Go to Value Field Settings:

Step 2 – Change the number format

In the Value field settings, go to Number Format.

On the right-hand side, a category list will appear. We’ll go to Currency and choose the Rupees symbol:

Awesome! Now our pivot table contains the number format as well.

Pivot Table Trick #5 – Pivot Table Slicers

The Pivot Table Slicer provides a very simple way to filter our data in a fast and efficient manner. All we have to do is press buttons! It’s that simple!

Let us take up a problem statement where we are provided with data of an e-commerce company. This data contains different products and their respective sales information. We want to see the sum of sales of individual categories. Let’s check how to do it.

Follow this concise video or refer to the steps below:

Follow step 1 and step 2 to make a pivot table on this data. Here we will be choosing the fields as Products (Row) and Total (Value).

Step 3 – Locate Insert Slicer

In the top ribbon, go to Analyze -> Insert Slicers:

Step 4 – Slicer

Choose the field you want to use as a filter. As we require the total amount earned for each category,  we will filter data on the field – Category:

Now you have the category in the form of a button. Just press the button to filter data!

Pro Tip – Make your Pivot Table dynamic

Add your data in the form of a table. Usually, the data gets updated time after time and it is desirable that our pivot table is dynamic.

The solution to making your pivot table dynamic is to add data in the form of a table and then simply refresh to get the updated pivot table.

End Notes

In this article, we covered five pivot table hacks, tips, and tricks for Excel. I hope these tricks will help you with day-to-day niche tasks and save you a lot of time as a business analyst or a data science professional.

Do you have your own Pivot Table tricks to share? Or any other Excel tricks, in general, you would want the community to know? Share them in the comments section below!

Ram Dewani

Product Growth Analyst at Analytics Vidhya. I'm always curious to deep dive into data, process it, polish it so as to create value. My interest lies in the field of marketing analytics.