Excel tips and tricks Part I

These are a few tips and tricks in excel which I figured I should share. Some are more obvious and others not so and could potentially help anyone using the tool to reduce time while working with excel.

1. IF statements:

This one is a pretty basic one.

Can be used to check for a condition but how I like to use it when I have a lot of rows and quickly need to see which rows meet a particular condition.

I first use create a condition and specify 1 for TRUE and 0 for FALSE.

Condition to check if the Col 8 has an odd number in the cell, if it does then return 1 else 0.

Then after dragging the formula across the rows just select the entire column header (i.e. column I) such that the sum mentioned in the status bar below would give me the number of rows that meet the condition and the average %age of rows meeting the condition.

You can get these functions customized to your status bar by hovering over it and then just right click to get a drop down of all measures. Select the ones you desire — Min, Max, Average, and Sum etc.

As you can see there are 18 out of 28 rows which satisfy the condition and from the average we get 64.28% which is the percentage of the data satisfying the condition.

2. IF, ISNA & VLOOKUP functions in combination:

We all know these functions separately but we can use these functions in conjunction and do really interesting things.

For example you have customer level sales data in an excel work book and a separate list of customers for whom you are not sure if they have ordered before or not. If you wanted to determine which set of customers had sales or not very quickly then IF(ISNA(VLOOKUP())) would be very helpful in this context.

This is our customer sales dataset and this will be our master set from which we will need to check if the customers in the new list have transacted or not.

This is our list of customers for whom we need to check which have placed an order and which have not.

Using IF(ISNA(VLOOKUP())) we can assigned a 1/0 flag and see if the customers have placed an order then they would be marked as 0 or else 1. “Customer product data” is the customer order dataset mentioned above.

And by highlighting the column header you can see the percentage (average) and count (sum) of customers who have never placed an order before.

As you can see there are 2 customers who have not placed an order before. You can also change the outputs to say whatever you want i.e., if I wanted the output to say “Never ordered” or “Ordered before”, then I would tweak the condition as follows:

Then you can make a pivot table to get the count needed.

3. Distinct counts in pivot tables:

This is an extremely helpful function which is not available by default while using pivot tables.

Carrying forward with our customer order data set from the above point, lets explore how to derive distinct count of entries for a particular column in excel. In the above mentioned data set if we wanted to find the distinct count of customers who have placed an order then taking the count would not suffice as the data set is at a Customer Product level implying one customer could buy multiple products so multiple rows for the same customer would exist, as you can see below:

Agnes has purchased two products and is repeated here.

To get the unique count of customers we can use the Distinct Count function in Pivot Tables. Before directly creating the pivot table please make sure to check the box that says “Add this data to the Data Model” in the Create PivotTable dialog box.

After the new sheet, where the pivot table fields are present, is created you can select in the Distinct Count function in the Values Fields setting dialog box.

And then you see the actual count of unique customers who have placed an order.

Please stay tuned for more Excel tips, tricks and cases.