Sunflower Intelligence
Business Analytics & Data Science

B03 · Introductory

Excel for Business Analytics

10 lessons

B03 · Introductory · 10 lessons in four modules · about 12 hours

Excel for Business Analytics

Formulas, lookups, tables and cleaning, PivotTables, charts, descriptive statistics and regression output, what-if analysis and dashboards in Excel.

What you will learn

  • Build formulas with the right relative, absolute and mixed references, and lay out a workbook so its inputs, calculations and results are separate and can be checked.
  • Use logical, text, date and conditional functions (IF, IFS, AND, OR, IFERROR, SUMIFS, COUNTIFS, AVERAGEIFS) to answer business questions from transaction data.
  • Retrieve values with XLOOKUP, INDEX and MATCH, and VLOOKUP, choosing exact or approximate matching correctly, and organise data in Excel tables with structured references.
  • Clean imported data with text, date and value functions in a documented, repeatable workflow, and extract sorted, filtered and unique lists with FILTER, SORT and UNIQUE.
  • Summarise data with PivotTables, choose the chart that answers the question and present it honestly, and use conditional formatting to show what matters.
  • Calculate descriptive statistics in Excel and read regression output (coefficients, R², standard error, p-values, confidence intervals) without claiming more than it shows.
  • Model decisions with Goal Seek, data tables, scenarios and Solver, and design a dashboard that answers its audience’s questions.

Prerequisites

How it works

Each lesson teaches the idea from first principles, works examples step by step, lets you explore it in an interactive, then has you practise with feedback. A lesson is complete when you have worked its guided problems and scored 80% on its knowledge check. Mastery is tracked separately and comes back for review before you are likely to forget. Each module ends with a test; the course ends with a project and a final assessment.

Fictional case

Lantern Coffee Co. A fictional coffee business founded by Ana Lim. It starts as a catering and barista-training service, opens its own cafés and an online shop for beans, and plans a canned cold brew. Every name and figure in it is invented for teaching.

Not sure where to start?

The diagnostic has 12 questions across the course. It doesn’t count toward anything; it suggests the module to start from. Take the diagnostic.

Modules and lessons

Module 1 Formulas that work

  1. Cells, references and formulasA spreadsheet is only as trustworthy as its formulas. Learn how Excel reads a formula, how references move when a formula is filled or copied, why a number can look rounded without being rounded, what each error value means, and how to lay out a worksheet that someone else can check.70 min · not started
  2. Functions, logic, text and datesExcel’s built-in functions do the heavy lifting: counting exactly what you mean, rounding by a business rule, making decisions with IF, taking text apart and joining it, and calculating with dates. Learn how each one works, where each one trips people up, and how to handle errors without hiding them.65 min · not started
  3. Conditional sums, counts and averagesMost business questions about a table of transactions are “how much, how many, or how good, for the rows that meet a condition”. Learn SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS and SUMPRODUCT, the criteria that make them precise (operators, cell references, wildcards, blanks and date windows), and how to build a summary table with one formula.60 min · not started
  4. Module 1 test12 questions on this module. 70% passes.not taken

Module 2 Finding and summarising data

  1. Lookups: VLOOKUP, INDEX and MATCH, XLOOKUPBring a value from one table into another by matching a key: a product’s cost from a price list, a delivery charge from a table of bands. Learn VLOOKUP, INDEX and MATCH, and XLOOKUP, when to match exactly and when approximately, and how to find out why a lookup returns #N/A or, worse, a wrong answer.70 min · not started
  2. Excel tables and cleaning dataTurn a range into an Excel table, so that formulas refer to columns by name and grow with the data; sort, filter and validate it; and clean an imported export with TRIM, VALUE, DATE and the dynamic array functions, keeping the raw data untouched and every step on record.70 min · not started
  3. PivotTablesSummarise hundreds of rows by dragging fields: order value by month and channel, events by type, each item’s share of a total. Learn how a PivotTable groups and aggregates its source, how to show percentages and months, how to keep it up to date, and when SUMIFS formulas are the better choice.70 min · not started
  4. Module 2 test11 questions on this module. 70% passes.not taken

Module 3 Showing and describing data

  1. Charts and conditional formattingA chart or a highlighted cell should answer one question at a glance. Choose the chart from the question, build it honestly in Excel (a title that states the finding, bars from zero, colour that never carries the meaning alone), and use conditional formatting, including formula rules with mixed references, to make the cells that matter stand out.80 min · not started
  2. Descriptive statistics and regression in ExcelDescribe a column of numbers with Excel’s statistics functions and the Analysis ToolPak, see what one large order does to the mean and the standard deviation, build a frequency table, and fit a straight line through catering invoices. Then read every line of the ToolPak’s regression output, and say what it does and doesn’t show.85 min · not started
  3. Module 3 test12 questions on this module. 70% passes.not taken

Module 4 Deciding with a spreadsheet

  1. What-if analysis: data tables, Goal Seek, scenarios and SolverA well-built model answers questions. Use one to see how a catering job’s contribution responds to guests, hours and costs (data tables and scenarios), to find the price that reaches a target margin (Goal Seek), and to choose the month’s best mix of events and workshops within limited time (Solver).80 min · not started
  2. Dashboards that answer questionsA dashboard is one page that tells its readers where things stand. Learn to start from the audience’s questions, give every number a comparison, lay the page out so the most important measure comes first, build it on tables, formulas and PivotTables so it refreshes, and add the documentation and checks that make it trustworthy.70 min · not started
  3. Module 4 test12 questions on this module. 70% passes.not taken

Project and final assessment

  1. Project: Lantern’s summer workbookAnalyse Lantern’s spring shop orders and its 2026 catering events in a spreadsheet: build the formulas and summaries Ana needs, read a regression of catering invoices, find the relaunched shop’s break-even with Goal Seek and a data table, plan July’s stock order with Solver, and recommend what her shop dashboard should show.not taken
  2. Final assessment24 questions across the whole course. 70% passes.not taken

The course is complete when every lesson is complete, every module test is passed, the project is finished and the final assessment is passed.

Resources

  • Glossary: every term this course defines.
  • Formulas: every formula with its symbols and units.
  • Review: flashcards and the questions you missed, when they are due.

Sources used in this course

  1. Microsoft Support. Excel specifications and limits. support.microsoft.com/en-us/excel/excel-specifications-and-l…
  2. Microsoft Support. Calculation operators and precedence in Excel. support.microsoft.com/en-us/excel/calculation-operators-and-…
  3. Microsoft Support. Switch between relative, absolute, and mixed references (Windows; the Excel for Mac page, Switch between relative and absolute references, is linked in the text). support.microsoft.com/en-us/Excel/switch-between-relative-ab…
  4. Microsoft Learn. Floating-point arithmetic may give inaccurate results in Excel. learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/ex…
  5. Microsoft Support. Formula auditing: Display the relationships between formulas and cells, with Evaluate a nested formula one step at a time and Remove or allow a circular reference in Excel (linked in the text). support.microsoft.com/en-us/excel/display-the-relationships-…
  6. Panko, R. R. (2000). Spreadsheet errors: What we know. What we think we can do. Proceedings of the Spreadsheet Risk Symposium, European Spreadsheet Risks Interest Group (EuSpRIG), Greenwich. arXiv:0802.3457. arxiv.org/pdf/0802.3457
  7. Herndon, T., Ash, M. & Pollin, R. (2013). Does high public debt consistently stifle economic growth? A critique of Reinhart and Rogoff. Political Economy Research Institute, University of Massachusetts Amherst, Working Paper 322. peri.umass.edu/publication/does-high-public-debt-consistentl…
  8. Microsoft Support. COUNT function (with the COUNTA and COUNTBLANK pages). support.microsoft.com/en-us/excel/count-function
  9. Microsoft Support. IF function – nested formulas and avoiding pitfalls. support.microsoft.com/en-US/Excel/if-function-nested-formula…
  10. Microsoft Support. IFS function. support.microsoft.com/en-us/excel/ifs-function
  11. Microsoft Support. IFERROR function. support.microsoft.com/en-us/office/iferror-function-c526fd07…
  12. Microsoft Support. FIND, FINDB functions. support.microsoft.com/en-gb/office/find-findb-functions-c791…
  13. Microsoft Support. DATE function. support.microsoft.com/en-us/excel/date-function
  14. Microsoft Support. DATEDIF function. support.microsoft.com/en-us/excel/datedif-function
  15. Microsoft Support. SUMIFS function. support.microsoft.com/en-us/excel/sumifs-function
  16. Microsoft Support. SUMIF function. support.microsoft.com/en-US/Excel/sumif-function
  17. Microsoft Support. COUNTIF function (criteria, wildcards and case). support.microsoft.com/en-us/office/countif-function-e0de10c6…
  18. Microsoft Support. COUNTIFS function. support.microsoft.com/en-us/excel/countifs-function
  19. Microsoft Support. AVERAGEIFS function. support.microsoft.com/en-US/Excel/averageifs-function
  20. Microsoft Support. MAXIFS function. support.microsoft.com/en-US/excel/functions/maxifs-function
  21. Microsoft Support. SUMPRODUCT function. support.microsoft.com/en-US/Excel/sumproduct-function
  22. Microsoft Support. VLOOKUP function. support.microsoft.com/en-us/Excel/vlookup-function
  23. Microsoft Support. XLOOKUP function. support.microsoft.com/excel/functions/xlookup-function
  24. Microsoft Support. MATCH function. support.microsoft.com/en-au/office/match-function-e8dffd45-c…
  25. Microsoft Support. XMATCH function. support.microsoft.com/en-us/office/xmatch-function-d966da31-…
  26. Microsoft Support. How to correct a #N/A error. support.microsoft.com/en-us/excel/how-to-correct-a-n-a-error
  27. Microsoft Learn. WorksheetFunction.VLookup method (Excel). Describes the worksheet function’s handling of duplicate keys and sorted data. learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunc…
  28. Microsoft Support. Using structured references with Excel tables. support.microsoft.com/en-us/excel/using-structured-reference…
  29. Microsoft Support. SUBTOTAL function. support.microsoft.com/en-us/excel/subtotal-function
  30. Microsoft Support. More on data validation. support.microsoft.com/en-US/Excel/more-on-data-validation
  31. Microsoft Support. Filter for unique values or remove duplicate values. support.microsoft.com/en-US/excel/get-started/filter-for-uni…
  32. Microsoft Support. DATEVALUE function. support.microsoft.com/office/datevalue-function-df8b07d4-776…
  33. Microsoft Support. Power Query data sources in Excel versions. support.microsoft.com/office/power-query-data-sources-in-exc…
  34. Microsoft Support. Dynamic array formulas and spilled array behavior. support.microsoft.com/excel/dynamic-array-formulas-and-spill…
  35. Microsoft Support. Create a PivotTable to analyze worksheet data. support.microsoft.com/office/18fb0032-b01a-4c99-9a5f-7ab09ed…
  36. Microsoft Support. Use the Field List to arrange fields in a PivotTable. support.microsoft.com/en-us/Excel/get-started/use-the-field-…
  37. Microsoft Support. Sum values in a PivotTable. Lists the summary functions, the Count default and Distinct Count. support.microsoft.com/en-us/excel/sum-values-in-a-pivottable
  38. Microsoft Support. Show different calculations in PivotTable value fields. support.microsoft.com/en-US/Excel/show-different-calculation…
  39. Microsoft Support. Refresh PivotTable data. support.microsoft.com/office/refresh-pivottable-data-6d24cec…
  40. Microsoft Support. GETPIVOTDATA function. support.microsoft.com/en-us/Excel/functions/getpivotdata-fun…
  41. Microsoft Support. When PivotTables don’t sort as expected. support.microsoft.com/en-gb/office/when-pivottables-don-t-so…
  42. Microsoft Support. “Available chart types in Office”, with “Present your data in a scatter chart or a line chart” and “Use sparklines to show data trends”. support.microsoft.com/en-us/excel/available-chart-types-in-o…
  43. Microsoft Support. “Use conditional formatting to highlight information in Excel”. support.microsoft.com/en-us/excel/use-conditional-formatting…
  44. Microsoft Support. “Create a histogram” and “Create a box and whisker chart”. support.microsoft.com/en-us/excel/create-a-histogram
  45. Cleveland, W. S. & McGill, R. (1984). Graphical perception: theory, experimentation, and application to the development of graphical methods. Journal of the American Statistical Association, 79(387), 531–554. doi.org/10.1080/01621459.1984.10478080
  46. W3C (2023). Understanding WCAG 2.2: success criteria 1.4.1 Use of Color, 1.4.3 Contrast (Minimum) and 1.4.11 Non-text Contrast. w3.org/WAI/WCAG22/Understanding/non-text-contrast.html
  47. data.europa.eu. Data Visualisation Guide: “Choosing charts”, on the Financial Times’ Visual Vocabulary. data.europa.eu/apps/data-visualisation-guide/choosing-charts…
  48. Microsoft Support. “QUARTILE.EXC function”, with the pages for STDEV.S, PERCENTILE.EXC, QUARTILE.INC, MODE.SNGL, KURT and SKEW. support.microsoft.com/en-us/excel/functions/quartile-exc-fun…
  49. Microsoft Support. “Load the Analysis ToolPak in Excel” and “Use the Analysis ToolPak to perform complex data analysis”. support.microsoft.com/en-us/excel/use-the-analysis-toolpak-t…
  50. Cameron, A. C. (2009). “Excel 2007: Descriptive Statistics”. University of California, Davis, Department of Economics. cameron.econ.ucdavis.edu/excel/ex21descriptivestatistics.htm…
  51. Microsoft Support. “Perform a regression analysis”, with “LINEST function” and “FORECAST and FORECAST.LINEAR functions”. support.microsoft.com/en-us/excel/perform-a-regression-analy…
  52. BetterSolutions.com. “Analysis ToolPak: Descriptive Statistics”. bettersolutions.com/excel/add-ins/analysis-toolpak-descripti…
  53. NIST/SEMATECH. e-Handbook of Statistical Methods, section 1.3.5.11, “Measures of Skewness and Kurtosis”. itl.nist.gov/div898/handbook/eda/section3/eda35b.htm
  54. Holmes, A., Illowsky, B. & Dean, S. (2017). Introductory Business Statistics, section 13.4, “The Regression Equation”. OpenStax. openstax.org/books/introductory-business-statistics/pages/13…
  55. Microsoft. “Introduction to What-If Analysis”. Microsoft Support. support.microsoft.com/en-us/Excel/introduction-to-what-if-an…
  56. Microsoft. “Use Goal Seek to find the result you want by adjusting an input value”. Microsoft Support. support.microsoft.com/office/use-goal-seek-to-find-the-resul…
  57. Microsoft. “Calculate multiple results by using a data table”. Microsoft Support. support.microsoft.com/en-us/excel/calculate-multiple-results…
  58. Microsoft. “Change formula recalculation, iteration, or precision in Excel”. Microsoft Support. support.microsoft.com/office/change-formula-recalculation-it…
  59. Microsoft. “Define and solve a problem by using Solver” and “Load the Solver Add-in in Excel”. Microsoft Support. support.microsoft.com/en-us/excel/define-and-solve-a-problem…
  60. Frontline Systems. “Excel Solver Tutorial: Step by Step Easy to use guide for Excel’s Solver”. solver.com. solver.com/node/181
  61. Frontline Systems. “Module 2: Reports and Sensitivity” (the Sensitivity Report) and “Excel Solver: How Integer, Binary and Alldifferent constraints affect solving” (solver.com/node/192, Integer Optimality). solver.com. solver.com/courses/optimization/module-2/reports-and-sensiti…
  62. Few, S. (2007). Why Most Dashboards Fail. Perceptual Edge. (Copy hosted for a UC Berkeley School of Information course.) blogs.ischool.berkeley.edu/i247s13/files/2013/02/WhyMostDash…
  63. Government Analysis Function (2026). “Data visualisation: testing dashboards for design and accessibility” and “Data visualisation: building and managing dashboards” (analysisfunction.civilservice.gov.uk/policy-store/data-visualisation-building-and-managing-dashboards/). UK Government Statistical Service. analysisfunction.civilservice.gov.uk/policy-store/data-visua…
  64. Microsoft. “Use slicers to filter data” and “Create a PivotTable timeline to filter dates”. Microsoft Support. support.microsoft.com/office/249f966b-a9d5-4b0f-b31a-1265178…
  65. Microsoft. “Create a drop-down list”. Microsoft Support. support.microsoft.com/office/7693307a-59ef-400a-b769-c5402dc…
  66. Microsoft. “SUBTOTAL function”. Microsoft Support. support.microsoft.com/en-us/excel/subtotal-function
  67. Microsoft. “Accessibility best practices with Excel spreadsheets”. Microsoft Support. support.microsoft.com/en-us/office/accessibility-best-practi…
  68. Microsoft. “View previous versions of Office files”. Microsoft Support. support.microsoft.com/topic/5c1e076f-a9c9-41b8-8ace-f77b9642…