Guides for data analysts
Worked examples for the formulas, queries and measures analysts use every day.
/
Start here
New to Excel? Start with these
New to SQL? Start with these
Excel formula guidesAll Excel guides →
- Excel9 minChange Case in Excel: UPPER, LOWER and PROPERExcel has no Change Case button. Convert text with UPPER, LOWER and PROPER, paste the results as values, and fix the names PROPER gets wrong.
- Excel12 minGreater Than or Equal To in Excel: >=, <= and <>Type >= for greater than or equal to in Excel. Use >=, <=, <> and = in IF and COUNTIF, combine tests with AND and OR, and match text with wildcards.
- Excel12 minHow to Extract Text From a Cell in ExcelExtract text from a cell in Excel with LEFT, RIGHT and MID, use FIND or SEARCH to cut text before or after a character, and get the first or last word.
- Excel9 minMIN, MAX, LARGE and SMALL in Excel: Highest and LowestHow to find min and max in Excel, the 2nd or 3rd highest value with LARGE and SMALL, the lowest per group with MINIFS and MAXIFS, plus ties and blanks.
- Excel9 minConditional Formatting Formula: Write Rules That WorkHow to write a conditional formatting formula: a TRUE/FALSE rule for the top-left cell, $ signs to highlight a whole row, and how to test it in a helper column.
- Excel7 minDate Formulas in Excel: DATE, EOMONTH, EDATE and MoreEvery Excel date formula you need for reports: YEAR, MONTH and DAY, DATE, adding days and months with EDATE, month ends with EOMONTH, and WEEKDAY.
- Excel7 minDays Between Dates in Excel: DAYS, DATEDIF, NETWORKDAYSCount days between dates in Excel by subtracting, with DAYS, in months and years with DATEDIF, and in working days with NETWORKDAYS and a holiday list.
- Excel8 minFILTER Function in Excel: AND, OR and If EmptyUse the FILTER function in Excel to pull matching rows: one condition, AND with *, OR with +, an if_empty message, and FILTER inside SORT.
- Excel8 minHow to Build a Summary Table in Excel With SUMIFSBuild a summary table in Excel without a pivot table: a label column with COUNTIF and SUMIFS, a two-way grid with mixed references, and % of total.
- Excel7 minHow to Concatenate in Excel: &, CONCAT, TEXTJOINCombine text from two or more cells in Excel with &, CONCAT and TEXTJOIN, keep number formats with TEXT, and fix capitals with PROPER, UPPER and LOWER.
- Excel7 minHow to Remove Spaces in Excel: TRIM, CLEAN, SUBSTITUTERemove spaces in Excel with TRIM, strip every space with SUBSTITUTE, and deal with line breaks and non-breaking spaces that TRIM leaves behind.
- Excel9 minIFS Function in Excel: Replace Nested IF CleanlyHow the IFS function in Excel works: IFS vs nested IF, the TRUE catch-all for the last case, why test order matters, and when SWITCH is the better fit.
- Excel7 minLET Function in Excel: Name the Steps of a FormulaUse the LET function in Excel to name the steps of a long formula, so it is easier to read and check, and repeated parts are calculated only once.
- Excel7 minPMT Function in Excel: Loan Payments Step by StepHow the PMT function in Excel works: monthly rate, number of payments, why the result is negative, and when to use FV, PV and NPER instead.
- Excel8 minRANK Function in Excel: Order, Ties and Top NHow the RANK function in Excel works: RANK.EQ, ascending vs descending order, ties, unique ranks, rank within a group, and LARGE and SMALL for a top N.
- Excel7 minSUMPRODUCT in Excel: Weighted Averages and CriteriaHow SUMPRODUCT works: multiply ranges row by row and add the results, for weighted averages, sums with several conditions, and the -- that makes TRUE count.
- Excel8 minTwo-Way Lookup in Excel: INDEX MATCH MATCH, XLOOKUPHow to do a two-way lookup by row and column: INDEX with two MATCH functions, nested XLOOKUP, and approximate-match bands for rate tables.
- Excel7 minUNIQUE Function in Excel: Distinct Lists That GrowUse the UNIQUE function in Excel for distinct lists, unique counts, by_col and exactly_once, then a SORT, UNIQUE and SUMIFS summary that grows.
- Excel8 min10 Practice Exercises for Excel Beginners (With Data)Ten Excel practice exercises on one small sales table, from your first formula to SUMIFS, VLOOKUP and spotting an AI's mistake. Check values included.
- Excel7 minAbsolute Reference in Excel: How to Lock a CellWhat an absolute reference does in Excel, how to lock a cell in a formula with $ and F4, and when to use relative, absolute or mixed references.
- Excel6 minAverage Formula in Excel: AVERAGE, MIN, MAX, MEDIANThe average formula Excel uses is =AVERAGE(range). Worked examples for MIN and MAX, AVERAGEIF, average vs median and averages that go wrong.
- Excel6 minCAGR Formula in Excel: Compound Annual Growth RateThe CAGR formula Excel users need is =(end/start)^(1/years)-1. Worked example, the RRI function, average annual growth rate and compound growth.
- Excel6 minCOUNTIF in Excel: Count Text, Contains and BlanksHow COUNTIF in Excel counts cells: exact text, cells that contain a word, wildcards and blank cells. Short worked examples with every result shown.
- Excel7 minCOUNTIF With Multiple Criteria: COUNTIFS ExplainedCount rows that meet several conditions. COUNTIF multiple criteria with COUNTIFS, greater than, not blank and OR logic, on a small worked example.
- Excel8 minData Analyst Excel Interview Questions and AnswersExcel interview questions for data analyst roles, with short answers: lookups, SUMIFS, IF logic, pivot tables, and a sample Excel interview test.
- Excel7 minHow to Pass an Excel Assessment Test for a JobWhat an Excel assessment test usually covers, the question types by level, a short sample test with answers and how to prepare in a week.
- Excel6 minHow to Sum a Column in Excel: SUM and AutoSumHow to sum a column in Excel with the SUM formula or AutoSum, how to add a whole column, and what to check when Excel SUM is not working.
- Excel7 minHow to Use INDEX MATCH in Excel (Step by Step)How to use INDEX MATCH in Excel: what each function does, left lookups, two-way lookups and INDEX MATCH with multiple criteria, with worked examples.
- Excel6 minHow to Use VLOOKUP in Excel (and Fix #N/A)How to use VLOOKUP step by step with an exact match, plus a checklist for when VLOOKUP is not working or returns the #N/A error.
- Excel7 minIF With AND and OR in Excel: Formulas That WorkHow to combine IF and OR, IF and AND, and nested IF in Excel, with worked examples, the right order for tiers and the logic mistakes to check for.
- Excel6 minIFERROR in Excel: Hide Errors Without Hiding BugsHow IFERROR works in Excel, with examples for #DIV/0! and IFERROR VLOOKUP, when to use IFNA instead, and why a blanket IFERROR can hide real mistakes.
- Excel7 minPercent Change Formula in Excel (With Examples)The percent change formula in Excel is (new - old) / old. Worked examples for percentage increase, month over month change and percentage difference.
- Excel6 minPercentage of Total in Excel: Formula and $ SignsThe percentage of total formula in Excel: divide each value by a locked total cell. Absolute references, F4, pivot tables and the mistakes to check.
- Excel6 minRound to 2 Decimal Places in Excel: ROUND vs FormatHow to round to 2 decimal places in Excel with ROUND, when to use ROUNDUP or ROUNDDOWN, and why formatting a cell does not change the stored value.
- Excel6 minSUMIF With Multiple Criteria: How SUMIFS WorksHow SUMIFS multiple criteria formulas work, with worked examples: SUMIF vs SUMIFS, not equal to, not blank, OR logic and date ranges.
- Excel7 minTrace Precedents in Excel: How to Check FormulasHow to use Trace Precedents, Trace Dependents and Evaluate Formula to check formulas in Excel, find formula errors and catch ranges that are off.
- Excel8 minVLOOKUP vs XLOOKUP vs INDEX MATCH: Which to UseVLOOKUP vs XLOOKUP vs INDEX MATCH compared on one small table: syntax, exact and approximate match, left lookups and multiple criteria.
- Excel7 minXLOOKUP in Excel: How to Use It (With Examples)How to use XLOOKUP in Excel step by step: exact match, a custom value if not found, returning multiple columns and finding the last match.
SQL guidesAll SQL guides →
- SQL10 minShould You Learn Excel or SQL First?Should you learn Excel or SQL first for a data analyst job? A direct answer by starting point, the same task solved in both tools, and a learning order.
- SQL7 minCohort Analysis in SQL: Retention by Signup MonthBuild a cohort analysis in SQL step by step: signup-month cohorts, first orders, activity by month number and a retention table that doesn't overclaim.
- SQL7 minSQL Anti Join: NOT EXISTS vs NOT IN vs LEFT JOINFind rows in one table with no match in another: the SQL anti join three ways, and the NULL that makes NOT IN return nothing at all.
- SQL7 minSQL LAG Function: Month-Over-Month ChangeHow the SQL LAG function works: previous-row values, month-over-month change in percent, PARTITION BY per group, LEAD, defaults and the missing-month trap.
- SQL7 minSQL Self Join: Employee and Manager ExamplesHow a SQL self join works: join a table to itself with two aliases, list each employee's manager, keep the top boss, and avoid the reversed ON clause.
- SQL7 minSQL String Functions: TRIM, SUBSTRING, CONCATThe SQL string functions analysts use to clean text: LOWER, TRIM, SUBSTRING, LENGTH, || and CONCAT, REPLACE, LIKE and ILIKE, regex and STRING_AGG.
- SQL7 minUNION vs UNION ALL in SQL (Plus INTERSECT, EXCEPT)UNION vs UNION ALL with real outputs: when duplicates disappear, why a total can drop, plus INTERSECT and EXCEPT for rows in both or only one query.
- SQL6 minSQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAXSQL aggregate functions explained: COUNT, SUM, AVG, MIN and MAX on real rows, how they treat NULLs, conditional totals, and the average-of-averages trap.
- SQL7 minSQL CASE WHEN: Multiple Conditions, ELSE, GROUP BYHow SQL CASE WHEN works: multiple conditions with AND/OR, what happens without ELSE, using CASE WHEN in GROUP BY, and why branch order matters.
- SQL7 minSQL COALESCE and NULL: Replace NULL With 0How SQL COALESCE works, how to test for NULL with IS NULL, replace NULL with 0 in totals, and COALESCE vs ISNULL across databases.
- SQL6 minSQL COUNT DISTINCT: Count Unique Values, Find DupesHow SQL count distinct works, COUNT(*) vs COUNT(column) vs COUNT(DISTINCT), counting per group, and how to find duplicates. Real examples and outputs.
- SQL7 minSQL CTE: The WITH Clause Explained SimplyWhat a SQL CTE is, how the WITH clause works, CTE vs subquery, and how to chain multiple CTEs, with real outputs and a grain mistake AI makes.
- SQL6 minSQL Extract Month From Date (and Group by Month)How to extract month from date in SQL with EXTRACT, MONTH() and DATE_TRUNC, group revenue by month, and the year mistake that merges months.
- SQL7 minSQL for Data Analysis: The Skills Analysts UseSQL for data analysis in practice: the core skills data analysts use, a worked analysis from first look to checked answer, and a learning order.
- SQL7 minSQL GROUP BY Multiple Columns, With ExamplesHow to GROUP BY multiple columns in SQL: one row per combination, sorting with ORDER BY, grouping after a JOIN, and the averaging trap to avoid.
- SQL6 minSQL HAVING vs WHERE: The Difference, With ExamplesHAVING vs WHERE in SQL: WHERE filters rows before GROUP BY, HAVING filters groups after it. Worked examples, HAVING COUNT, and a common AI mistake.
- SQL7 minSQL INNER JOIN: How to Join Two Tables (Examples)How to join two tables in SQL with INNER JOIN, then join 3 tables and join on multiple columns. Real example tables, queries and outputs.
- SQL7 minSQL Interview Questions for Data Analysts, AnsweredSQL interview questions for data analyst roles with worked answers: joins, HAVING, ranking, running totals, NULLs, plus spot-the-bug query questions.
- SQL7 minSQL LEFT JOIN Explained (vs INNER JOIN, IS NULL)What a SQL left join does, left join vs inner join side by side, how to use LEFT JOIN with IS NULL, and the WHERE mistake that breaks it.
- SQL6 minSQL ORDER BY: DESC, LIMIT and Top 10 RowsHow SQL ORDER BY sorts results: ascending vs DESC, sorting by several columns, LIMIT and SQL Server TOP for a top 10, ties and NULLs.
- SQL7 minSQL Order of Execution: How a Query Really RunsSQL order of execution explained: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Why an alias in the WHERE clause fails, with examples.
- SQL7 minSQL Practice Online: Problems With Real AnswersHow to get useful SQL practice online: what good practice looks like, six SQL practice problems from easy to harder, and how to check your own answers.
- SQL6 minSQL RANK OVER PARTITION BY: Top N Per GroupHow RANK() OVER (PARTITION BY ...) works in SQL, rank vs dense_rank vs row_number with ties, and how to get the top N rows per group.
- SQL7 minSQL Running Total: SUM OVER ORDER BY ExplainedHow to calculate a running total in SQL with SUM() OVER (ORDER BY ...): cumulative sum per group, ties on the same date, and the mistakes to check for.
- SQL7 minSQL Subquery: WHERE, FROM and Correlated ExamplesHow a SQL subquery works: subquery in WHERE, subquery in FROM, correlated subqueries, subquery vs join, and the NOT IN trap that returns nothing.
- SQL7 minSQL WHERE Clause: Multiple Conditions, IN, BETWEENHow the SQL WHERE clause filters rows: multiple conditions with AND and OR, WHERE IN, BETWEEN dates without missing the last day, and common traps.
- SQL7 minSQL Window Functions Explained: OVER, LAG, LEADSQL window functions explained for analysts: what OVER does, PARTITION BY, LAG and LEAD for month-over-month change, with real outputs.
Power BI and DAX guidesAll Power BI guides →
- Power BI8 minDAX ALL vs ALLEXCEPT vs ALLSELECTED vs REMOVEFILTERSDAX ALL, ALLEXCEPT, ALLSELECTED and REMOVEFILTERS compared on one sales model: % of grand total, % of region, % of selection, and the slicer trap.
- Power BI7 minDAX CALCULATE Explained With 6 ExamplesDAX CALCULATE explained with 6 worked examples: calculate sum with filter, multiple filters, CALCULATE with ALL for % of total, KEEPFILTERS and FILTER.
- Power BI6 minDAX IF Statement, Nested IF and SWITCH(TRUE) ExplainedHow to write a DAX IF statement, when to replace nested IF with SWITCH or SWITCH(TRUE()), and why the order of conditions changes the result.
- Power BI8 minDAX LOOKUPVALUE: Multiple Criteria and vs RELATEDLOOKUPVALUE in DAX with worked examples: lookups on multiple criteria, what happens with no match or duplicates, and when to use RELATED instead.
- Power BI8 minDAX Practice Exercises: 10 Questions on a Sales ModelFree DAX practice exercises on a small sales dataset you can type into Power BI: 10 DAX practice questions with expected results, hints and solutions.
- Power BI8 minDAX RANKX: Ties, Dense Ranking and Rank by GroupHow RANKX works in DAX: rank customers by a measure, handle ties with SKIP or DENSE, rank within a category, and fix the 'every row is 1' bug.
- Power BI7 minDAX RELATED, RELATEDTABLE and USERELATIONSHIP ExplainedHow the DAX RELATED function gets a value from a related table, how RELATEDTABLE goes the other way, and how USERELATIONSHIP uses an inactive relationship.
- Power BI7 minDAX vs Power Query: Which One Should Do the Work?DAX vs Power Query explained: M shapes data at load time, DAX calculates over the model. When to add a column in Power Query vs a DAX calculated column.
- Power BI7 minDAX vs SQL: A Translation Guide for SQL UsersDAX vs SQL for analysts who know SQL: how WHERE, JOIN, GROUP BY, HAVING and CASE WHEN map to DAX, with the DAX equivalent of GROUP BY and worked results.
- Power BI6 minHow to Create a Dashboard in Power BI (Step by Step)How to create a dashboard in Power BI: build the report, publish it, pin tiles, and design tips. Plus dashboard vs report and the KPI mistakes to avoid.
- Power BI7 minHow to Create a Date Table in Power BI (DAX and M)Create a date table in Power BI with DAX CALENDAR or Power Query, mark as date table, then calculate YTD with TOTALYTD and DATESYTD. Worked example.
- Power BI6 minHow to Learn Power BI: A Practical Plan for BeginnersHow to learn Power BI as a beginner: what to learn in which order, a six-week plan, how long it takes, and how to learn Power BI free with practice data.
- Power BI7 minInterview Questions for Power BI Data AnalystsPower BI interview questions for data analyst roles: DAX interview questions, modeling basics and scenario-based questions, with short model answers.
- Power BI8 minMeasure vs Calculated Column in Power BI: When to Use EachMeasure vs calculated column in Power BI DAX: what each one does, when to use a calculated column, and the ratio mistake that breaks totals.
- Power BI8 minPL-300 Practice Test: Sample Questions With AnswersA free PL-300 practice test: 12 sample questions with answers across the four exam areas, what the PL-300 exam covers, and a short study guide plan.
- Power BI7 minStar Schema and Relationships in Power BI, ExplainedPower BI relationships explained on a small star schema: cardinality, cross filter direction, what breaks without a relationship, and data modeling tips.
- Power BI7 minStep-by-Step Power Query Tutorial for BeginnersA Power Query tutorial for beginners: open the Power Query editor, clean a messy customer file step by step, and read and fix the applied steps.
- Power BI6 minSUM vs SUMX in DAX: How Iterator Functions WorkSUMX vs SUM in DAX: SUM adds one column, SUMX computes an expression row by row and then adds it up. Worked examples with SUMX, AVERAGEX and MAXX.
- Power BI6 minTableau vs Power BI for Data Analysts: How to ChoosePower BI vs Tableau for data analysts: how they differ in data prep, calculations, platforms and free options, which is easier to learn, and how to choose.
- Power BI6 minWhen to Use Power BI vs Excel for Data AnalysisPower BI vs Excel for data analysis: what each does best, when to use Power BI instead of Excel, and the same revenue report built in both tools.