Excel Skill and Concepts a Data Analyst Should Know
Check this π§΅π§΅π§΅ for more details
Kindly RT π
Check this π§΅π§΅π§΅ for more details
Kindly RT π
It is always encouraged that a data analyst should learn how to use Excel or other spreadsheet app to work with data. As a matter of fact, Excel is the closest tool to understanding complex challenges in your Journey.
If you can do it with Excel, you can figure it out for others
If you can do it with Excel, you can figure it out for others
Mastering Excel is a Journey and what you end up knowing still depends hugely on your role.
I will however highlight some concepts you should be familiar with as a Data Analyst in Excel.
Note: It is not sufficient to be familiar with them, you should Know WHEN TO USE THEM.
I will however highlight some concepts you should be familiar with as a Data Analyst in Excel.
Note: It is not sufficient to be familiar with them, you should Know WHEN TO USE THEM.
1. SORT
While working on a large amount of data, there may be cases where you want to group certain type of data and want to sort it in some way. Doing this manually is not advised. You can use the sort function and group the data in few seconds.
While working on a large amount of data, there may be cases where you want to group certain type of data and want to sort it in some way. Doing this manually is not advised. You can use the sort function and group the data in few seconds.
2. FILTER
Fillters bring in a lot of ease and convenience. With the help of filter function you can subgroup your data as you want. It helps you to narrow down your large data and you can view only the information that is relevant to you.
Fillters bring in a lot of ease and convenience. With the help of filter function you can subgroup your data as you want. It helps you to narrow down your large data and you can view only the information that is relevant to you.
3. SUMIF Function
It is easy to add cells in Excel, but what if you want to add only if certain condition is met. That is where the SUMIF function comes to your rescue. SUMIF function helps you to get the sum of a range after fulfilling certain mentioned criteria.
It is easy to add cells in Excel, but what if you want to add only if certain condition is met. That is where the SUMIF function comes to your rescue. SUMIF function helps you to get the sum of a range after fulfilling certain mentioned criteria.
4. TEXT Functions
Basic text functions like LEFT, RIGHT, LEN, SUBSTITUTE etc. can help you a lot when you want to extract only a certain amount of text from a large amount of data.
This is simply the beginning of substring
Basic text functions like LEFT, RIGHT, LEN, SUBSTITUTE etc. can help you a lot when you want to extract only a certain amount of text from a large amount of data.
This is simply the beginning of substring
5. IF Formulas
If you want to calculate bonus on the basis of sales of employee or their productivity with a given matrix, you can easily calculate it through IF functions.
As basic as it may be, knowing when and how to use it is powerful
If you want to calculate bonus on the basis of sales of employee or their productivity with a given matrix, you can easily calculate it through IF functions.
As basic as it may be, knowing when and how to use it is powerful
6. CONDITIONAL FORMATTING
Conditional formatting help you highlight important data and metrics from others.
It can be useful in gauging and measuring quantitative data and doing comparison right within a cell
Conditional formatting help you highlight important data and metrics from others.
It can be useful in gauging and measuring quantitative data and doing comparison right within a cell
7. PIVOT TABLES
As a data analyst, you cannot run away from this feature. How? In fact, this is a very critical concept you must know in Excel. Pivot tables allow you to quickly summarize and analyze large amounts of data in lists and tablesβindependent.
As a data analyst, you cannot run away from this feature. How? In fact, this is a very critical concept you must know in Excel. Pivot tables allow you to quickly summarize and analyze large amounts of data in lists and tablesβindependent.
8. CHARTS
As an Analyst, you will be summarising numbers with charts. Charts simplify the data and make it look more presentable. But if you select a wrong chart to present your data then things can go wrong, so always be careful while selecting the chart for your data.
As an Analyst, you will be summarising numbers with charts. Charts simplify the data and make it look more presentable. But if you select a wrong chart to present your data then things can go wrong, so always be careful while selecting the chart for your data.
9. SPARKLINES
Sparkline charts are one powerful and influential advanced excel chart that will help you make unique dashboards. Since sparklines are small in size, they can easily be embedded in data grids and dashboards to show the pattern of values like sales.
Sparkline charts are one powerful and influential advanced excel chart that will help you make unique dashboards. Since sparklines are small in size, they can easily be embedded in data grids and dashboards to show the pattern of values like sales.
10. POWERVIEW
Power View is a data visualization technology in excel that allows you to instantly create interactive charts, graphs, maps, and others that light up your data. It gives data analysts the option of creating data models and presentations that are more sophisticated.
Power View is a data visualization technology in excel that allows you to instantly create interactive charts, graphs, maps, and others that light up your data. It gives data analysts the option of creating data models and presentations that are more sophisticated.
11. VLOOKUP
This is a recognizable function in MS Excel. It helps to look for a value in a given table and returns information from another column relating to that value. It works by combining data from different lists to one or comparing two lists for matching or missing items.
This is a recognizable function in MS Excel. It helps to look for a value in a given table and returns information from another column relating to that value. It works by combining data from different lists to one or comparing two lists for matching or missing items.
12. QUICK ANALYSIS
Use quick analysis in Excel to format your data into a chart, table, summary formula, highlighted figures, or sparklines with just a few clicks. Formatting data makes it possible to analyze your data quickly and easily instead of going through various tabs.
Use quick analysis in Excel to format your data into a chart, table, summary formula, highlighted figures, or sparklines with just a few clicks. Formatting data makes it possible to analyze your data quickly and easily instead of going through various tabs.
13. REMOVING DUPLICATE VALUE
You should always check for errors such as duplicates. Upon using the remove duplicates feature, the duplicate data will be permanently deleted. Duplicates are annoying and can lead to misinterpretation of data.
You should always check for errors such as duplicates. Upon using the remove duplicates feature, the duplicate data will be permanently deleted. Duplicates are annoying and can lead to misinterpretation of data.
14. TRIM
The trim function helps remove irregular spacing from data, mainly when imported from other applications. Trim removes all the extra spaces except for single spaces to clean up the cells of a given worksheet.
The trim function helps remove irregular spacing from data, mainly when imported from other applications. Trim removes all the extra spaces except for single spaces to clean up the cells of a given worksheet.
15. CONCATENATE
The concatenate function combines values from multiple cells into one.
This can come handy in many scenarios.
The concatenate function combines values from multiple cells into one.
This can come handy in many scenarios.
16. COUNTBLANK
The blank count function counts the number of empty cells in a given range of cells. It is among the excel statistical functions that are built-in and useful in highlighting blanks.
Contents with blanks can be visually counted using the blank count tool.
The blank count function counts the number of empty cells in a given range of cells. It is among the excel statistical functions that are built-in and useful in highlighting blanks.
Contents with blanks can be visually counted using the blank count tool.
17. RANK
THe rank function return the rank of a number in a list of numbers. You can use RANK.AVG to produce the average rank if more than one number has the same status. By default, data analysts use ranks to arrange data values in ascending order (smallest to largest).
THe rank function return the rank of a number in a list of numbers. You can use RANK.AVG to produce the average rank if more than one number has the same status. By default, data analysts use ranks to arrange data values in ascending order (smallest to largest).
18. UNIQUE
The unique function in excel return a list of unique values in a list or range. The values can be numbers, texts, dates, and times. Excel will automatically create the required size range after you press enter.
The unique function in excel return a list of unique values in a list or range. The values can be numbers, texts, dates, and times. Excel will automatically create the required size range after you press enter.
19. LEN
When you want to count the characters in cells, use the len function for accurate results. The function counts letters, numbers, symbols, and all spaces in a given worksheet. You can count both characters in one cell and characters in multiple cells with this function.
When you want to count the characters in cells, use the len function for accurate results. The function counts letters, numbers, symbols, and all spaces in a given worksheet. You can count both characters in one cell and characters in multiple cells with this function.
20. TEXTTOCOLUMN
For data cleaning purposes, TextToColumn is a handy function and our will need it in several occasions that requires splitting a column into multiple columns.
For data cleaning purposes, TextToColumn is a handy function and our will need it in several occasions that requires splitting a column into multiple columns.
21. SLICERS
Slicers are used to filter pivot tables quickly or excel tables in Microsoft excel. Data analysts connect multiple slicers to multiple pivot tables to create excellent reports. Slicers are of great help to users who are not familiar with your worksheet.
Slicers are used to filter pivot tables quickly or excel tables in Microsoft excel. Data analysts connect multiple slicers to multiple pivot tables to create excellent reports. Slicers are of great help to users who are not familiar with your worksheet.
22. POWERPIVOT
This tool enables analysts to manipulate data input and create data models and group tables related to each other. Besides that, power pivot gives you more options for creating presentations from data volumes from different places.
This tool enables analysts to manipulate data input and create data models and group tables related to each other. Besides that, power pivot gives you more options for creating presentations from data volumes from different places.
23. INDEX MATCH
This performs so many incredible tasks in excel. It is the combination of two powerful functions, index, and match. Index returns the value of a cell in a table basing on the column and row number, whereas match returns the position of a cell in a row or column.
This performs so many incredible tasks in excel. It is the combination of two powerful functions, index, and match. Index returns the value of a cell in a table basing on the column and row number, whereas match returns the position of a cell in a row or column.
24. INDIRECT
The Microsoft excel indirect function returns a reference to change. An analyst can use indirect reference to create a reference specified by a certain text from the cell values. Also, it helps lock the specified cell in a formula, so it does not perform calculations
The Microsoft excel indirect function returns a reference to change. An analyst can use indirect reference to create a reference specified by a certain text from the cell values. Also, it helps lock the specified cell in a formula, so it does not perform calculations
25. LOCKING CELL
There are some formulae you will write in Excel that needs to either keep a value constant, iterate over rows or columns.
Knowing how to lock a cell value is a super power πͺ.
There are some formulae you will write in Excel that needs to either keep a value constant, iterate over rows or columns.
Knowing how to lock a cell value is a super power πͺ.
26. SELECTING VISIBLE CELL
Many at times, you have your data filtered an you need to copy the filtered values to another sheet.
Selecting the filtered data range and copying them will automatically copy all the hidden datasets. Learn this feature and change the game
Many at times, you have your data filtered an you need to copy the filtered values to another sheet.
Selecting the filtered data range and copying them will automatically copy all the hidden datasets. Learn this feature and change the game
27. IFERROR
You can use the excel iferror function to trap and handle errors in a given formula. The function works by returning an alternate value if a formula results in an error. It checks for errors such as #N/A, #VALUE!, #REF!, #NAME, and #NULL.
You can use the excel iferror function to trap and handle errors in a given formula. The function works by returning an alternate value if a formula results in an error. It checks for errors such as #N/A, #VALUE!, #REF!, #NAME, and #NULL.
28. XLOOKUP
The MX lookup is a great search tool in MS Excel that allows you to find specific values from various cells. It acts as a modification of the standard lookup functions such as vlookup. Xlookup is the newest model of vlookup with most limitations eliminated.
The MX lookup is a great search tool in MS Excel that allows you to find specific values from various cells. It acts as a modification of the standard lookup functions such as vlookup. Xlookup is the newest model of vlookup with most limitations eliminated.
29. FIND/SEARCH
The MS excel search function returns the location of one text string inside another. The search usually allows wildcards and is not always case-sensitive. Find function is case-sensitive and is used to return the position of a specific cell within a text.
The MS excel search function returns the location of one text string inside another. The search usually allows wildcards and is not always case-sensitive. Find function is case-sensitive and is used to return the position of a specific cell within a text.
30. DATA VALIDATION
This is a handy tool used to set validations on any data entered into your worksheet. It means that data is accepted only when it meets specific validation criteria. Otherwise, you will get an error message that the values are invalid and rejected.
This is a handy tool used to set validations on any data entered into your worksheet. It means that data is accepted only when it meets specific validation criteria. Otherwise, you will get an error message that the values are invalid and rejected.
I know there are other topics that might be essential however, I believe with this, a Data Analyst can be so comfortable working with Excel and the experiences can easily be transferred to Power BI, Tableau, and Python for Data Analysis.
Action Point:
Write out all the concepts shared here in a spreadsheet,
2. Add 2additinal columns "HOW", and "WHEN"
3. For each of the function/feature, tick the ones you know "How" and "When" to Use them.
The gap will be clear.
Write out all the concepts shared here in a spreadsheet,
2. Add 2additinal columns "HOW", and "WHEN"
3. For each of the function/feature, tick the ones you know "How" and "When" to Use them.
The gap will be clear.
Also, Find a sample data that can help you practice some of these features.
I believe this very year is your year and together, we will celebrate your win on this data path! πππ
I believe this very year is your year and together, we will celebrate your win on this data path! πππ
β’ β’ β’
Missing some Tweet in this thread? You can try to
force a refresh
γ
