Mar 12 β’ 36 tweets β’ 7 min read
Excel Skill and Concepts a Data Analyst Should Know
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
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.
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.
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.
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.
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
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
6. CONDITIONAL FORMATTING

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.
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.
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.
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.
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.
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.
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.
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.
15. CONCATENATE
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.
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).
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.
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.
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.
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.
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.
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.
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
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 πͺ.
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
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.
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.
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.
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.
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,
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! πππ

β’ β’ β’

Missing some Tweet in this thread? You can try to force a refresh
γ

This Thread may be Removed Anytime!

Twitter may remove this content at anytime! Save it as PDF for later use!

# More from @TheOyinbooke

Mar 13
Are you looking for a way to make \$300 - \$1000 working from home and with your smartphone?
You should be tired of this type of headlines π€£π€£.
I am still looking for such headline that is genuine.
Please show ME the way π
@emeka_boris Na you I copy o π€£π. Even the copy should sell
Mar 11
As a Data Analyst or Scientist, it is good to Netflix and chill sometimes.
This is why I will be recommending 10 Movies on Data Science for your viewing pleasure π

Curled from DataScienceDojo Blog and some by myself
No 1. I, Robot (2004)
Will Smith stars as Chicago policeman Del Spooner in the year 2035. He is highly suspicious of the A.I technology and robots being used as household helpers
No 2. 2001: A Space Odyssey (1968)
This classic film by Stanley Kubrick addresses the most interesting possibilities that exist within the field of Artificial Intelligence.
Mar 11
Data Management and Governance is a path people are yet to take advantage of today.
Check this thread π§΅ for more details

#DMOnBirdApp
As a Certified Data Management Professional (CDMP) and having worked in different data roles as Data Analyst, Data Scientist, Data Architect, and even led Data Architecture Project, I can see before me a major challenge for the African Market.

Data!
Data!!
Data!!!
The same Data that is spurring new opportunities in the continent might lead to the fall of others or make them lose the game to their competitors.

Businesses in Africa believe so much in revenue without attention to Technical excellence. We have been growing double digits...
Mar 10
Training and Internship Opportunities for Nigerian Women π§΅
Apply for a paid internship position at Bolt today!
INGRESSIVE FOR GOOD
Announcing the 2022 edition of the 1000 Women In Design scholarship in partnership with @genezatraining

Register - bit.ly/I4Gwomen

Closes - March 18, 2022
Mar 2
Dear BEGINNER Data Analyst, Ask yourself these questions to avoid learning Data Analysis for 2 Years π§΅
As a BEGINNER Data Analyst, to get the best while learning, you should have a strong reason for learning and give it the right level of commitment and dedication.
Be sincere with yourself and ask the following questions...
1. What can make it impossible for me to learn this skill?
2. What are my potential source of distractions?
3. Who will be my accountability partner?
4. What are the basic concepts every beginner should know?
5. How long do I have to learn the basics?
6... and many more
Mar 1
Kaggle is not just a competitive data science platform, it's one of the largest repositories for datasets, it is also a Cloud IDE where you can build and deploy a model.
Check details in this π§΅

RT Appreciated