Data analytics cheat sheet (Excel and Power BI)

1. 1What analysts doThe job before the tools: turning a vague business worry into a question, deciding how it will be measured and choosing the kind of analysis that answers it.7 concepts

   1. 1What a data analyst doesAnswers business questions with data: finds and cleans the data, analyses it, and explains what it means and what to do next, usually through reports…
   2. 2A good business questionA specific, answerable question tied to a decision: who is asking, what they will do with the answer, which metric, which period and which comparison.
   3. 3Metrics and KPIsA metric is a precisely defined number, such as weekly active users.
   4. 4Types of analysisDescriptive says what happened, diagnostic asks why, predictive estimates what will happen, and prescriptive suggests what to do.
   5. 5The analysis workflowThe loop most analyses follow: ask the question, prepare and clean the data, analyse it, share the result and help people act, then check whether the action…
   6. 6Tidy dataA table where each row is one observation, each column is one variable and each cell holds one value.
   7. 7Personal data and privacyInformation that identifies a person, such as names, phone numbers, emails or national ID numbers, and the rules for collecting, storing and sharing it.

   7 in this chapterOpen this chapter
2. 2Spreadsheets properlyExcel or Google Sheets used like an analyst: structured tables, formulas you can audit, lookups, pivot tables and cleaning that someone else can follow.9 concepts

   1. 1Relative and absolute referencesA relative reference (A2) shifts when you copy a formula; an absolute one ($A$2) stays fixed; a mixed one ($A2 or A$2) fixes only the column or row.
   2. 2Structured tablesA range turned into a named table, whose formulas refer to columns by name and which grows automatically when you add rows.
   3. 3SUMIFS and COUNTIFSFunctions that sum, count or average only the rows that meet one or more conditions.
   4. 4XLOOKUPLooks up a value in one column and returns the matching value from another, with a fallback when nothing is found.
   5. 5Text and date cleaning functionsFunctions that fix the usual mess: extra spaces, mixed case, text that should be numbers or dates, and codes that need splitting.
   6. 6FILTER, UNIQUE and SORTFunctions that return a whole range of results that spill into neighbouring cells: a filtered list, the distinct values or a sorted copy.
   7. 7Pivot tablesA tool that summarises a table by dragging fields into rows, columns, values and filters, such as total sales by region and month.
   8. 8Data validationRules on cells that limit what can be entered, such as a drop-down of allowed regions or dates within a range.
   9. 9Raw, working and output tabsKeeping untouched source data on one tab, cleaning and calculations on another, and the summary or chart on a third, with a notes tab describing each.

   9 in this chapterOpen this chapter
3. SQL for analysisQuerying data where it lives: filtering, aggregating, joining tables correctly, and window functions for rankings and running totals.10 conceptsOpens with credits
4. Statistics you needEnough statistics to summarise data honestly, spot misleading comparisons and read an A/B test result without fooling yourself.10 conceptsOpens with credits
5. Power BI in depthPower Query for repeatable cleaning, a star schema model, DAX measures with CALCULATE and time intelligence, report design and sharing safely.7 conceptsOpens with credits
6. Cleaning and modelling dataTurning messy real-world data into tables you can trust: profiling, fixing values, choosing the grain and shaping facts and dimensions.10 conceptsOpens with credits
7. Charts, dashboards and storytellingChoosing the right chart, removing what distracts, designing dashboards people use, and presenting a finding so it leads to a decision.11 conceptsOpens with credits
8. Portfolio and first analyst roleProving you can do the work with public data, writing it up well, and finding a first role, internal move or freelance project without falling for traps.9 conceptsOpens with credits
9. 9Glossary, A to ZEvery concept in the sheet and the smaller terms it uses, in one list.129 terms

SQL, spreadsheets, dashboards, insight.