Excel cheatsheet for data analysts

 

Excel secrets for data analysts

IF	Adds decision-making logic to your spreadsheet.	Tests a condition and returns one value if true and another if false. This can be nested to handle multiple conditions.	.=IF(A1>50, "Pass", "Fail")
VLOOKUP	Quickly finds and retrieves specific data from a table.	Searches for a value in the first column of a table and returns a value from a specified column in the same row. It is a "vertical" lookup.	.=VLOOKUP(A2, C2:E10, 3, FALSE)
SUMIFS	Calculates a sum based on one or more criteria.	Adds up numbers in a range only if they meet specific conditions, such as "sum all sales for the West region for Product A."	.=SUMIFS(C2:C10, A2:A10, "West", B2:B10, "Product A")
COUNTIFS	Counts cells that meet multiple criteria.	Counts the number of cells in a range that satisfy a specific set of conditions. Useful for frequency analysis.	.=COUNTIFS(A2:A10, ">100", B2:B10, "Completed")
AVERAGEIFS	Calculates an average with multiple criteria.	Computes the average of a range of cells that meet all specified conditions.	.=AVERAGEIFS(C2:C10, A2:A10, ">=50", B2:B10, "<=75")
IFERROR	Handles and hides formula errors.	Returns a custom value (like a blank cell or a message) if a formula results in an error, otherwise it returns the original result.	.=IFERROR(VLOOKUP(A2, C2:D10, 2, FALSE), "Not Found")
INDEX MATCH	A more flexible alternative to VLOOKUP.	Combines the INDEX and MATCH functions to perform a lookup that is not restricted to the first column. This is very powerful and often preferred over VLOOKUP.	.=INDEX(B2:B10, MATCH(E2, A2:A10, 0))
TEXTJOIN	Combines text from multiple cells with a delimiter.	Joins text strings from a range of cells, allowing you to specify a separator and to ignore empty cells.	.=TEXTJOIN(", ", TRUE, A1:A5)
LEFT, RIGHT, MID	Extracts specific portions of a text string.	LEFT extracts characters from the start, RIGHT from the end, and MID from a specified position within the string.	.=LEFT(A1, 3), =RIGHT(A1, 4), =MID(A1, 2, 5)
TRIM	Cleans up text by removing extra spaces.	Removes all leading and trailing spaces from a text string, and replaces multiple spaces between words with a single one.	.=TRIM(A1)

Nested ifs 

One condition:

if cell Z is above x, then show value y 

if (logical_test, [value_true], [value_false] ) 

if (Z>15000, “good”, “”)      “only for text”

Double condition:
if (and (logical_1, [logical_2],”Good”, “”)

Rev>15000

Rev>=20000 , then Exceptional

Rev 15-20 = value

if (and (logical_1, [logical_2],”Good”, if B5>=B3,”Exceptional”, cell_ref)

Bigger than or smaller than 10%

Vlookup / Hlookup

Function for fetching data in a large sheet (vertically, horizontally). 

Function	Benefit	Example
INDEX/ MATCH	This is a more flexible and powerful alternative to VLOOKUP/HLOOKUP. It can look up values in any column, not just the first one, and is more efficient with large datasets.	You need to find the ad spend for a specific campaign name. MATCH finds the row number of the campaign name in a list, and INDEX uses that row number to return the value from the ad spend column. =INDEX(B2:B50, MATCH("Campaign X", A2:A50, 0))
PIVOT TABLE	Summarizes, organizes, and analyzes large amounts of data in a table. It lets you quickly group data by different categories, calculate sums, averages, and counts, and see trends at a glance.	You have a large dataset of website traffic with columns for date, source, and page views. A Pivot Table can quickly show you the total page views for each source (e.g., Google, Facebook, etc.) over time.
SUMIFS/ COUNTIFS/ AVERAGEIFS	These functions perform calculations (sum, count, or average) based on multiple criteria. This allows for precise analysis of filtered data.	You want to find the total revenue from users who are in a specific region and purchased a specific product. =SUMIFS(C:C, A:A, "Product A", B:B, "Region X") sums values in column C where column A is "Product A" and column B is "Region X".
CONCATENATE TEXTJOIN	Combines the text from multiple cells into one. Useful for creating unique identifiers or full names from separate data fields. TEXTJOIN is a newer, more efficient version that can handle a range of cells and a delimiter.	You have a first name in one column and a last name in another. You can use =CONCATENATE(A2, " ", B2) or =TEXTJOIN(" ", TRUE, A2:B2) to combine them into a full name.