Press enter to see results or esc to cancel.

Excellent Excel Functions That Every Data Analyst Should Know

“In God we trust; all others must bring data.”, said a great American statistician.

Data Analytics are the eyes and ears to the humongous data that companies generate. As analysts, we try to make sense out of these numbers at every step. We find the missing pieces of the puzzles, connect the dots and bring out a story. This storytelling helps the parent (companies) understand their child (data) better. We help the parents plan their kid’s present and future better so that it grows to become a top shot winner!

What can be measured can be managed and if not then we cannot improve it.

So think about this analogy: fuel goes into the engine, combustion happens and the vehicle runs. Neat and simple. So your information is the oil and analytics your combustion engine. Now there are various types of engines used according to the information fed. Statistical modelling, big data analytics, forecasting tools, machine learning and what not.

Heavy sounding words right? Let’s introduce you to one of the simplest and perhaps the most important tools to make sense of all this. Remember making those colourful pie charts as a kid for your summer assignments? You used Excel to do this, didn’t you? Well, Excel does way more than that! It is like the gospel for data analysts and everyone should be well versed with it!

So let us unravel some of the lesser known Excel secrets from an analyst’s eyes!

No, you’re wrong. I am not going to take you through the pivots and lookups; you already know them. Let’s move on to more interesting stuff.

Index Match

VLOOKUP and HLOOKUP are both great functions but both of them come with some flaws. Being an analyst you would for sure resort to techniques that are less error prone. The Index-Match function allows you a dynamic column reference. In simple words, you can choose the column you want to pull the data from. It also leads to fewer errors as you can click on the field containing the value you want to return. The syntax of other functions might be simpler but this gives more accurate results. It’s much faster than the other lookup functions and so there is no reason not to use it!

INDEX_MATCH_GIF-1

 

Paste Special

Every Excel user comes across this problem of copy paste. CTRL+C and CTRL+V might seem easy but in reality, it’s a bit tricky. Transposing the data from one sheet to another might lead to anomalies in the data. And if filters/functions/pivots have been applied on the base data then the chances of a copy paste error increases. The paste special can paste content by retaining the characteristics of the source data as per your wish.  A simple CTRL+ALT+V instead of a CTRL+V could make a lot of difference. It is also helpful in auto calculations.

 

Paste Special

 

Use-Paste-Special-Operations-for-a-Quick-Multiply-in-Excel-5

3D sum

I recently came across this function and it is superb. Let’s say you keep a track of the sales of various categories of product in a store for 4 weeks in a spreadsheet. You have 4 tabs for 4 weeks and 1 totals tab.

Now you want to find out the total sales of each category in the Totals tab. One thing you should keep in mind is to arrange the rows and columns in the same fashion throughout all tabs. Now let us say I have the sales of grocery saved in B1 of each tab for each of the 4 weeks. We have saved the names of the tabs as WEEK1, WEEK2, WEEK3… and so on.

By writing =SUM(“WEEK1:WEEK4′!B2) I can get the totals of grocery products for all 4 weeks in a flash! Now all that is needed is to apply it to all categories. No looking back and forth and copy pasting data for grand totals.

3d-spreadsheet-formulas-100299272-orig

 

Goal Seek

Well, this is a magic function which forecasters, analysts, consultants, and other strategic wizards use to change the game. Let us suppose that I am a phone manufacturer, say Micra. Now Micra is launching a new phone and has fixed its presales price as $49. The post sale first month price has been fixed to $69, for the second month it is $59 and for the third month, it is back to $49. Now, if I want to know how many units Micra needs to move in the 3rd month to reach sales of $100 million, all I have to do is to use Goal Seek.  I will just need to change my total revenue to $100 million and its done. The Goal Seek function is very important from a strategic point of view too.

There are a wide variety of other functions which you can google up and use in your daily analysis. Pivots and lookups being the preferable ones. There is another function TABLE which I would like you guys to find out by yourself. CTRL+T on a selection of rows sure builds it into a table but do you know what CTRL+SHIFT+T does after you have created a table? Figure it out!

goal seek

 

 

 

break-even-goal-seek-2

Comments

Leave a Comment

IBM

About Author

Pratik Saurav

| Small town guy, big dreams | Wandering wordsmith with a thousand stories to tell and listen, tell me yours? |
| Foodaholic, foodpornographer, loves cooking and feeding people | Digital marketer | Talented enough to roll out a captivating blog, infographic, eDM or stuff that people will actually read |
| Human by birth, engineer by education, Data Scientist by profession and a Musician by passion | In God I trust, rest all must bring data |