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
