As a business analyst, you must know the Excel functions and
formula thoroughly. After working on something for hours, if you choose an
incorrect formula, data analysis fails. It might seem easier to do a manual
calculation than falling into the trap again.
Excel has over hundreds of formulas and functions. It is overwhelming to find the right match for correct analysis. However, if you get the basics right, it shouldn’t be a problem.
#1: SUMIF
In an Excel sheet, you can add
cells easily. However, if you want additional cells only under certain
conditions, SUMIF function can help.
It
allows you to get the sum total of a range only after fulfilling certain
specific criteria. If you want to add multiple conditions, use SUMIFs.
Simply put, there are many such
logic functions in Excel, which you can use according to a requirement. For
example, Excel IF, AND, OR,
AVERAGEIF, AVERAGEIFS, COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.
However, SUMIF is the most
commonly used formula.
#2: VLOOKUP
VLOOKUP is a useful data analysis
function. When you use Excel, you must get data together. For instance,
accounts receivable data would know the per
product cost, whereas the shipping department would only be able to
provide data of units shipped. In such cases, VLOOKUP is the appropriate
formula.
It is the most versatile function
to assign categories and replace complicated IF formulas.
#: INDEX MATCH
This formula is an advanced
alternative to VLOOKUP and HLOOKUP having certain limitations. INDEX MATCH is a
combination of Excel function that can take the financial analysis and modeling
to another level.
INDEX returns the cell value in a
table based on the row and column number. MATCH returns cell position in a row
or column.
#4: Conditional Formatting
There are important and less
important data. It is crucial to distinguish the two. You cannot confuse the two
as it would make your excel sheets more confusing. Conditional formatting is a
savior that can guard you against all of
this.
You can use it for highlighting
good and bad data, percentage changes, duplicate values, top search volume
keywords, etc.
However, you must understand the
rules of conditional formatting to understand its true power.
#5: Pivot Tables
A
pivot table is one of the most important functions for data analysts. It
allows analysts to summarize as well as analyze bulk data in tables and lists –
independent of its original layout in the spreadsheet, simply by dragging
columns and dropping them in different columns, rows, or the summary position.
#6: Concatenation
It allows you to combine
different texts into a single cell. It is the easiest formula, yet a powerful
one to conduct data analysis.
You can combine text, dates,
numbers, and more from several cells into one cell. It is great for creating
product SKUs, Java queries, and API endpoints.
#7: Find and Replace
This formula is the new go-to
function. It works as the name suggests finds a text and replaces it with the
other specified text.
You can use it to remove AdWords markup on the keyword list for ease of SEO, to replace references to the past month and update current month’s report, update folder structure list, update bulk meta descriptions and title tags, convert HTTP into https, remove extra space, etc.
#8: Filter and Sort
This is the most common feature
in Excel that still makes data analysis easier. You can use this set to
prioritize your SEO jobs.
Filtering is useful when you look
at a data list and organize using common terms. When SEO is overwhelming and
you must pair down on priority, use this formula.
There are many other formulas in
Excel, which you can understand, as you get deeper into data analysis.
Sohail Anwar is a PMO/PM who happens to teach best practice
Excel to PMs, PMOs, Analysts and pretty much any professionals who work with
Excel, you can learn more over at Earn And
Excel
