Excel - Most used formulas needed for a Data Analyst.
“Nail the basics first, detail the details later — Chris Anderson”
This will be the 4th and last “Explained Like you are 5” series for Must have skills for Data Analytics that I am writing about. (Please watch the other ones — Power BI, Python, and SQL from). Now,We will have all the basic skills that are required and then can move to the next stages in the field of data analytics
Excel, Excel, Excel. I saw a lot of people either on LinkedIn or on Twitter talking about learning excel. I used to work on Power BI at that time and I knew the basics of excel so I never cared about learning formulas for excel that are used for data analytics. Until I saw one of my colleagues actually doing analysis on excel itself.
One of the advantages of using excel is you can make changes in the data itself while doing analysis. Whether it is making your data models or doing cursory analysis of the data and for that matter making dashboards themselves. There are a lot of things that can be learned in excel. I started learning those and here are the most used formulas that you can use for data analysis in excel
- XLOOKUP - XLookup is an upgrade compared to VLOOKUP or Index & Match. Use the LOOKUP function to find things in a table or range by row.
Formula: =XLOOKUP (lookup value, lookup array, return array)
In the below example, we are finding the dial code for brazil(Which is present in the F2 cell and will be checked from cells B2:B11, and values are to be found from D2:D11.
2. IF, SUMIF, SUMIFS, COUNTIF, COUNIFS, IFerror- Just remember them as if this then this. In the below example, We are finding the value(Sum, Count, single as dependent on the function). the major difference between IF and IFS is in “IFS” we can use multiple if conditions from different columns as used below.
The “Iferror” function is used if and when a condition is not met from the parameters what would be the values in the cell? Typically it is used for unknown values like infinity and NA values.
3. Transpose - This will transform items in rows, to instead be in columns or vice versa. To transpose a column to a row:
1. Select the data in the column,
2. Select the cell you want the row to start,
3. Right click, choose to paste special, select transpose
4. PIVOT TABLE - It is basically used to analyze the data. Select the fields you want to analyze and summarize your data.You can create charts based on that table, analyze that data, etc.
5.UPPER, LOWER, PROPER, TRIM - UPPER, Converts text to all uppercase, =LOWER, Converts text string to lowercase, =PROPER, Converts text to proper case, TRIM - Removes all spaces between any strings except between words.
Conclusion:
We have covered the main concepts that are needed to learn in excel for data analysis. There are other tips and tricks that you can use in Excel that I will cover in the next article. The best thing about using excel is it is pretty intuitive. Once you start using it, there is no going back.
That’s It. Hope It helped in getting started in Excel. Once you are comfortable using these concepts. It is quite interesting once you actually find insights from the data.
If you have any questions or any inputs you want to share, just comment or hit me up on LinkedIn or Twitter. I am pretty much active there.
All the best.
Please Start. You will not regret it.
Thanks,
Piyush Jain
