Olanrewaju Oyinbooke Profile picture
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.
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.
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
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
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 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.
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.
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.
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.
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.
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 concatenate function combines values from multiple cells into one.
This can come handy in many scenarios.
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 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.
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.
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.
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 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.
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
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 πŸ’ͺ.
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
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.
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 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.
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.
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

Keep Current with Olanrewaju Oyinbooke

Olanrewaju Oyinbooke Profile picture

Stay in touch and get notified when new unrolls are available from this author!

Read all threads

This Thread may be Removed Anytime!


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

Try unrolling a thread yourself!

how to unroll video
  1. Follow @ThreadReaderApp to mention us!

  2. From a Twitter thread mention us with a keyword "unroll"
@threadreaderapp unroll

Practice here first or read more on our help page!

More from @TheOyinbooke

Mar 13
Are you looking for a way to make $300 - $1000 working from home and with your smartphone?
Check this thread 🧡
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
Read 4 tweets
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.
Read 12 tweets
Mar 11
Data Management and Governance is a path people are yet to take advantage of today.
Check this thread 🧡 for more details

#DMOnBirdApp Image
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.

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...
Read 11 tweets
Mar 10
Training and Internship Opportunities for Nigerian Women 🧡
Apply for a paid internship position at Bolt today!
No previous experience needed πŸ‘‰πŸΌ hit (bit.ly/3KcUPHE) to learn more! Image
Announcing the 2022 edition of the 1000 Women In Design scholarship in partnership with @genezatraining

Register - bit.ly/I4Gwomen

Closes - March 18, 2022 Image
Read 4 tweets
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
Read 11 tweets
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
#Kaggle #BlackTechTwitter
Visit Kaggle using the link below
Click on the "<> Code" menu option as illustrated in the image below.

This will take you to the Kaggle IDE Environment
Read 11 tweets

Did Thread Reader help you today?

Support us! We are indie developers!

This site is made by just two indie developers on a laptop doing marketing, support and development! Read more about the story.

Become a Premium Member ($3/month or $30/year) and get exclusive features!

Become Premium

Don't want to be a Premium member but still want to support us?

Make a small donation by buying us coffee ($5) or help with server cost ($10)

Donate via Paypal

Or Donate anonymously using crypto!


0xfe58350B80634f60Fa6Dc149a72b4DFbc17D341E copy


3ATGMxNzCUFzxpMCHL5sWSt4DVtS8UqXpi copy

Thank you for your support!

Follow Us on Twitter!