Twelve chapters and four levels to go from your first formula to a professional dashboard, then to Power BI: worked exercises, working workbooks and a final project.
Excel is the world’s most widely used analysis tool, yet most users exploit only a small part of it. In twelve chapters and four levels, this course takes you from your first formula to a professional dashboard, then to your first steps in Power BI. From start to finish you work on the same dataset: the sales from the ISHRAQA7 Academy case study (practice dataset) — 2,400 rows over two years.
Excel 2016 or later; Excel 2021 or Microsoft 365 for chapters 5 (XLOOKUP) and 10; Power BI Desktop (free, Windows) for chapter 12
How to follow the course
Download the exercise workbook, the answer workbook and the PDF guide in your language.
Read a chapter, then do its exercise in the matching sheet (Ex01 to Ex11): yellow cells are for you to fill in.
Compare with the answer workbook: each green cell holds the formula, and each sheet explains the reasoning.
Answer the three check questions before moving to the next chapter.
Finish with the final project: the sales dashboard of chapter 11.
Level
Chapters
You become able to…
1 · Beginner
1 to 3
enter, calculate and organise data without errors
2 · Intermediate
4 to 7
answer a business question with formulas or a PivotTable
3 · Advanced
8 to 10
present, automate and write modern formulas
4 · Expert
11 and 12
design a dashboard and a first Power BI report
Level 1 · Beginner — Chapter 1
Getting to grips with Excel: workbooks, cells and first calculations
Excel stores information in a grid. Each box, a cell, has an address made of its column and row: C13 is the cell in column C, row 13. An Excel file is a workbook; it holds one or more sheets, shown as tabs at the bottom of the screen.
Three kinds of content
A cell holds either text (aligned left), a number or a date (aligned right), or a formula, which always starts with =. That default alignment is your first quality check: a number sitting on the left is really text, and calculations will ignore it.
A date is a number in disguise: Excel counts days from 1 January 1900. That is why you can subtract two dates to get a lead time. Always type a date as 15/03/2025 (or in your regional order), never as words in a cell meant for calculation.
Your first formula
Click the cell that should show the result.
Type =, click the quantity cell, type *, click the price cell: you get, for example, =B13*C13.
Press Enter. The cell shows the result; the formula bar keeps the calculation.
Copy down by double-clicking the small square at the bottom-right corner of the cell (the fill handle). Excel adjusts the row numbers by itself: =B14*C14, =B15*C15…
The four basic functions
Need
English Excel
French Excel
Add up a range
=SUM(D13:D20)
=SOMME(D13:D20)
Work out the average
=AVERAGE(D13:D20)
=MOYENNE(D13:D20)
Find the largest value
=MAX(D13:D20)
=MAX(D13:D20)
Count non-empty cells
=COUNTA(A13:A20)
=NBVAL(A13:A20)
The shortcut Alt + = inserts an automatic SUM. The notation D13:D20 is a range: every cell from D13 to D20.
Level 1 · Beginner — Chapter 2
Clean data and structured tables
Everything you do next — formulas, PivotTables, charts, Power Query — depends on the quality of the starting table. A usable table follows five simple rules.
One row = one record (a sale, a customer, an invoice).
One column = one piece of information, with a unique header on a single row.
No empty row or column inside the data, no merged cells.
One data type per column: only dates, or only numbers, or only text.
No totals or subtotals inside the table: they are calculated elsewhere.
Cleaning with functions
Problem
English Excel
French Excel
Extra spaces (leading, trailing, double)
=TRIM(A13)
=SUPPRESPACE(A13)
Irregular case → Proper Case
=PROPER(A13)
=NOMPROPRE(A13)
All capitals
=UPPER(B13)
=MAJUSCULE(B13)
Functions nest: =PROPER(TRIM(A13)) removes the spaces first, then fixes the case. To Excel, “Atlas Conseil” and “atlas conseil” are two different customers; once cleaned, they are one.
Spotting duplicates
The formula =IF(COUNTIFS($D$13:D13,D13,$E$13:E13,E13)>1,"Duplicate","Unique") counts how many times the customer–city pair has already appeared from the top of the list down to the current row. The range $D$13:D13 grows with each copy: the first occurrence stays “Unique”, later ones become “Duplicate”. Data › Remove Duplicates cleans up in one click, but without showing what it removes: flag first, delete afterwards.
The structured table
Click inside your data and press Ctrl + T, or Insert › Table. The range becomes a named object, renamed on the Table Design tab: Sales, Products…
It grows by itself: any added row enters the formulas, charts and PivotTables built on it.
A formula typed in one column is filled automatically down the whole column.
Formulas become readable: =SUM(Sales[Net amount]) instead of =SUM(N2:N2401).
Filters, sorting and a total row are built in.
Level 1 · Beginner — Chapter 3
Formulas and references: relative, absolute, mixed, structured
Copying a formula moves it together with its references. The whole skill lies in deciding what should follow the move and what should stay put.
Type
Notation
Behaviour when copied
Typical use
Relative
B16
Row and column follow
Row-by-row calculation
Absolute
$B$12
Nothing moves
A rate, a single parameter
Mixed, column locked
$A27
Column stays, row follows
Always read from column A
Mixed, row locked
B$26
Row stays, column follows
Always read from row 26
While typing, the F4 key cycles through the four forms. Read $ as “does not move”: it locks whatever follows it.
The single parameter
A VAT rate, a discount or an exchange rate should be entered once, in a labelled cell, then called with an absolute reference: =C16*$B$12. The day the rate changes, you edit one cell and the whole workbook updates. Writing =C16*0.2 forces you to correct every formula, and you are certain to miss some.
One formula for a whole grid
For a scale crossing sales amounts (column A) with commission rates (row 26), the formula =$A27*B$26, typed once, copies right and down: $A always returns to the row’s amount, $26 to the column’s rate.
Structured references
In a structured table, columns are referred to by name. Sales[Net amount] is the whole column; [@Quantity] is the value in the current row. The Net amount column of the Sales table is calculated with =ROUND([@Quantity]*[@[Unit price]]*(1-[@Discount]),2). These references never shift and stay correct as the table grows.
Order of operations
Excel applies the mathematical order: brackets, powers, multiplication and division, then addition and subtraction. =B16*1-$B$13 does not compute a discount; =B16*(1-$B$13) does.
Analysing means answering questions that nearly always contain “if” or “by”: what revenue by zone? How many orders if payment is overdue? Excel answers with two families of functions.
IF: deciding
=IF(test, value_if_true, value_if_false). The test compares two items with =, <> (not equal), >, >=, <, <=. For three outcomes, nest a second IF in place of the “false” value:
Excel stops at the first true test, so the order of tests matters. Since Excel 2019, IFS avoids nesting; AND and OR combine several conditions in one test.
The …IFS functions: aggregating under conditions
Question
English Excel
French Excel
Sum by criteria
SUMIFS(sum_range, range1, criterion1, …)
SOMME.SI.ENS
Number of rows by criteria
COUNTIFS(range1, criterion1, …)
NB.SI.ENS
Average by criteria
AVERAGEIFS(average_range, range1, criterion1, …)
MOYENNE.SI.ENS
Revenue for the zone written in A13: =SUMIFS(Sales[Net amount],Sales[Region],$A13). All criteria combine with a logical “and”: each range–criterion pair narrows the rows a little further.
Comparison and date criteria
A comparison criterion is written in quotation marks with the operator attached to the value: ">=50000". If the value comes from a cell or a function, join it with &: ">="&DATE(2025,1,1). A full year therefore needs two criteria on the same date column:
The Sales table only holds the product code. Its description, category and cost live in the Products table. Linking the two is the job of lookup functions: starting from a key present in both tables, you read a piece of information from the other one.
Example: =XLOOKUP(A13,Products[Code],Products[Description],"Unknown code"). Excel searches for the content of A13 in the Code column and returns the description on the same row. The match is exact by default and the return column may sit left or right of the key. XLOOKUP exists in Excel 2021 and Microsoft 365.
INDEX + MATCH, the universal solution
In every version of Excel, two functions do the same job in two steps:
MATCH(A13,Products[Code],0) returns the position of the code in the column; the final 0 forces an exact match.
INDEX(Products[Description],position) returns the value at that position.
VLOOKUP is still everywhere, but it has three weaknesses: it only reads to the right of the key, it identifies the column by a number that goes wrong as soon as a column is inserted, and its last argument, if omitted, triggers an approximate match. Learn to read it; for new workbooks, prefer XLOOKUP or INDEX/MATCH.
Missing codes
A missing key returns #N/A. Handle it with XLOOKUP’s fourth argument or, universally, with IFERROR(formula,"Unknown code"). Do not return 0: an unknown product does not have a zero price, it has a price to be checked.
Two-way lookup
To read the target for a month and a zone in the Targets table, INDEX takes a row position and a column position:
Dates and text: computing lead times, splitting references
Dates are numbers
Because a date is a day number, the difference between two dates is a number of days: =C13-B13 gives the delivery lead time. If the result displays as a date, set the cell back to General format.
Need
English Excel
French Excel
Year, month, day
YEAR(B13), MONTH(B13), DAY(B13)
ANNEE, MOIS, JOUR
Build a date
DATE(2025,3,15)
DATE(2025;3;15)
Today’s date
TODAY()
AUJOURDHUI()
Last day of the month, n months later
EOMONTH(B13,1)
FIN.MOIS(B13;1)
Working days between two dates
NETWORKDAYS(B13,C13)
NB.JOURS.OUVRES(B13;C13)
EOMONTH(B13,0) returns the last day of the date’s own month; with 1, that of the following month: the “end of next month” due date. NETWORKDAYS counts Monday to Friday, both ends included, and accepts a range of public holidays as its third argument. For a different working week, use NETWORKDAYS.INTL.
Splitting text
A reference such as CMD-2025-00123-CAS holds four pieces of information. When the structure is fixed, three functions are enough:
LEFT(A13,3): the first 3 characters, “CMD”.
RIGHT(A13,3): the last 3, “CAS”.
MID(A13,10,5): 5 characters starting from the 10th, “00123”.
The result of a split is always text. VALUE(MID(A13,10,5)) converts it to a number: 123. LEN gives the length of a text and FIND the position of a character, useful when the structure varies.
Joining
The & operator glues items together: =I13&"-"&D13. To join a list with a separator, TEXTJOIN("-",TRUE,range) is quicker.
Level 2 · Intermediate — Chapter 7
PivotTables: summarising thousands of rows
A PivotTable answers in seconds questions that would need dozens of formulas: revenue by zone and category, monthly trend, sales rep ranking. It never changes the source data: it summarises it.
Creating the PivotTable
Click inside the Sales table.
Insert › PivotTable › New Worksheet. The suggested source is Sales: the table’s name, not a fixed range.
In the pane on the right, drag fields into the four areas.
Area
Role
Example
Rows
What you want to break down
Region
Columns
The second reading axis
Category
Values
What you measure
Sum of Net amount
Filters
What you want to restrict
Channel, Payment
Setting up the values
Right-click a number in the table, then Value Field Settings, to choose the calculation (sum, count, average) and the number format. The Show Values As tab turns amounts into percentages of the grand total, the row total or the column total, with no formula. If Excel offers “Count” instead of “Sum”, the source column contains text or empty cells: fix the source.
Grouping dates
Put the Date field in rows: Excel groups by years, quarters and months. To change the grouping, right-click a date › Group. This only works if every cell in the column is a real date.
Sorting, filtering, slicing
Right-click a value › Sort › Largest to Smallest ranks the sales reps. Slicers (PivotTable Analyze › Insert Slicer) are visible filter buttons; one slicer can drive several PivotTables through “Report Connections”.
Level 3 · Advanced — Chapter 8
Charts: getting a message across
A chart is not decoration: it is a sentence. Before building it, write down the message it must carry (“The fourth quarter beats the target”); the chart type follows from it.
Question asked
Suitable chart
Avoid
How does a value change over time?
Line
Pie
Which category is strongest?
Bars or columns, sorted
Line
What share does each item represent?
Bars; pie only up to 3 or 4 slices
Ten-slice pies, 3D effects
Are two measures related?
Scatter plot
Columns
Where do we stand against the target?
Columns for actuals, line for target
Two separate charts
Building
Prepare a small summary table: one column of labels, one or two columns of values. You never plot 2,400 raw rows.
Select it, headers included, then Insert › Charts.
Sort categories from largest to smallest, unless they have a natural order (months, age bands).
Decluttering
Replace “Chart Title” with the message: “Revenue beats the target from September”.
Remove whatever carries no information: border, background, dense gridlines, the legend when there is only one series.
Start the axis of a bar or column chart at zero: a truncated column exaggerates differences.
Use a neutral colour for the whole and a strong colour for the item you are talking about.
State the unit (MAD, thousands of MAD, %) in the title or on the axis.
Conditional formatting and sparklines
Inside a table, Home › Conditional Formatting › Data Bars turns a column of numbers into a mini chart. Sparklines (Insert › Sparklines) place a line in a single cell: ideal for showing a trend on each row of a table.
Level 3 · Advanced — Chapter 9
Power Query: import and clean without starting over
Cleaning a file by hand means accepting to do it again every month. Power Query records each cleaning action as a step. When a new file arrives, one click on Refresh replays every step. The tool is built into Excel (Data tab) and Power BI; it never changes the source file.
Importing
Data › Get Data › From File › From Text/CSV.
Choose the file. In the preview, check the file origin (65001: Unicode UTF-8) and the delimiter.
Click Transform Data, not Load: the Power Query Editor opens.
The essential transformations
Defect
Command in the editor
Headers sitting in the first data row
Home › Use First Row as Headers
Empty rows, a “TOTAL” row at the end
Filter on the Date column: untick (null) and empty values
Stray spaces
Transform › Format › Trim
Irregular case
Transform › Format › Capitalize Each Word
Unit stuck to the number (“12450.50 MAD”)
Transform › Replace Values: “ MAD” with nothing
Dates and numbers read as text
Click the type icon › Using Locale
Duplicate rows
Home › Remove Rows › Remove Duplicates
The locale trap
“03/04/2025” is 3 April in a British or French file and 4 March in an American one; “12450,50” is a decimal number in France and text elsewhere. By choosing Change Type › Using Locale you tell Power Query the language of the file, regardless of your computer’s settings. It is the only way to get a query that works on every machine.
Applied steps and the M language
The pane on the right lists the Applied Steps. You can click each one to see the data at that point, rename it, delete it or insert another. Behind every step, Power Query writes a line of M language, visible in Home › Advanced Editor. You do not have to write it, but being able to read it helps you understand and fix a query.
Loading and refreshing
Home › Close & Load puts the result into a structured table. Next month, replace the source file with the new one (same name, same folder), then Data › Refresh All.
Level 3 · Advanced — Chapter 10
Dynamic arrays: UNIQUE, SORT, FILTER and LET
In Excel 2021 and Microsoft 365, a formula can return several results: they “spill” into the neighbouring cells. You write the formula in one cell and the result range adjusts to the size of the data. If a cell is in the way, Excel shows #SPILL!: simply clear the space.
The third argument is the message shown when no row matches. To rank the result from largest to smallest, wrap it: =SORT(FILTER(…),,-1). The -1 asks for descending order; the argument left empty means sorting on the first column.
Referring to a spilled range
If the formula is in A19, A19# refers to the whole range it occupies, whatever its size: =COUNTA(A19#) counts the results. A data-validation drop-down can point to =$A$19#: it will update by itself.
LET: naming the steps of a calculation
LET gives a name to an intermediate result, then uses it in the final calculation:
The formula reads like a sentence: “let rev be the zone’s revenue, let n be its number of lines, return rev ÷ n”. Each element is calculated only once, which also makes the workbook faster.
Level 4 · Expert — Chapter 11
KPIs, analysis and dashboards: the final project
A key performance indicator (KPI) is a figure you track regularly because it leads to a decision. A figure nobody uses to decide is not a KPI; it is a statistic.
An indicator’s identity card
Element
Question
Example
Definition
What exactly does it measure?
Share of lines delivered within 5 days
Formula
How is it calculated?
Lines with lead time ≤ 5 ÷ total lines
Source
Where does the data come from?
Sales table, Date and Delivery date columns
Target
What value do we want to reach?
85%
Frequency
When is it read?
Monthly
Owner
Who acts if it deteriorates?
Logistics manager
The basic sales indicators
Revenue and growth: (revenue N − revenue N-1) ÷ revenue N-1.
Average amount per line: revenue ÷ number of sales lines.
Active customers: number of distinct customers who ordered in the period.
Service rate: on-time deliveries ÷ total deliveries.
Target achievement: revenue ÷ target.
A percentage is always calculated from totals, never by averaging percentages: the company’s margin rate is total margin divided by total revenue, not the average of each line’s rate.
Three layers in the workbook
Data: the structured tables, never edited by hand.
Calculations: a sheet of formulas that reads the data and the selectors.
Presentation: the dashboard, which holds only links to the calculation sheet, charts and selectors.
This separation lets you change the presentation without touching the calculations, and check the calculations without getting lost in the layout.
Selectors without macros
A cell with a drop-down list (Data › Data Validation › List) acts as a year or zone selector; formulas refer to it. For the “All” option, convert the choice into a criterion: =IF(B4="All","*",B4). In SUMIFS, the criterion "*" accepts any text.
Layout rules
One page, readable without scrolling.
Key figures at the top, trends in the middle, detail at the bottom.
Every indicator comes with a reference point: target or previous period.
Two main colours, the same throughout; red is reserved for alerts.
The update date and the source appear on the page.
Final project: the sales dashboard
Context: in the ISHRAQA7 Academy case study, sales management wants a one-page sales dashboard, updated monthly, usable by someone who does not know Excel.
Deliverable 1: a “Calculations” sheet reading the Sales, Products and Targets tables, driven by two selectors: year (2024 or 2025) and zone (one zone or All).
Deliverable 3: three charts: monthly revenue compared with the previous year, revenue by category, top five products.
Constraints: no value typed by hand in the calculations; no macro; one page with no scrolling; two main colours.
Check: for 2025 and All zones, revenue must read 6,708,660 MAD and the margin rate 21.1%.
Assessment: accuracy of the figures (40%), three-layer structure (20%), readability and titles (25%), a five-line commentary for management (15%).
Answer: the “final project” workbook in the pack contains a complete model; compare it with yours only after finishing.
Level 4 · Expert — Chapter 12
Introduction to Power BI: from workbook to interactive report
Power BI Desktop is Microsoft’s free application for building interactive reports. You will find two tools you already know: Power Query to prepare the data and the logic of PivotTables to summarise it. What is new is the data model: several related tables, queried by measures.
Excel
Power BI
Strength
Free-form calculation, data entry, simulation
Interactive reports, large volumes, sharing
Comfortable volume
Up to a few hundred thousand rows
Several million rows
Relating tables
Lookup formulas
Relationships in the model
Calculations
Formulas in cells
Reusable DAX measures
The two tools complement each other: Excel to explore and simulate, Power BI to distribute a report that updates itself.
The three views of Power BI Desktop
Report: the page where you place visuals.
Table: the contents of the loaded tables.
Model: the tables and the relationships between them.
The star schema
In the centre, the fact table: Sales, one row per sale, with amounts and codes. Around it, the dimension tables: Products, Customers, Calendar, one row per item described. Each relationship links a unique key in the dimension (Products[Code]) to the matching column in the facts (Sales[Product]): a “one-to-many” relationship. You filter by dimensions and measure in facts. The lookups of chapter 5 become unnecessary: the relationship does the linking.
A Calendar table
Time calculations require a continuous date table. In Modeling › New Table: Dates = CALENDAR(DATE(2024,1,1), DATE(2025,12,31)). Relate Dates[Date] to Sales[Date], then mark it as a date table.
First DAX measures
A measure is a named formula, calculated according to the visual’s context: the same measure gives total revenue in a card, revenue by zone in a chart, the month’s revenue in a line. DAX is written with English function names and commas.
DIVIDE avoids the divide-by-zero error. CALCULATE recomputes a measure after changing the filter: here, the same period one year earlier. It is the most important function in DAX.
Building the page
Click a visual type in the Visualizations pane, then drag fields: a card for each indicator, a line with Dates[Date] on the axis, a bar chart by category, a slicer for the zone. Clicking a bar filters the whole page: that is cross-filtering. The readability rules of chapters 8 and 11 apply unchanged.
Check your knowledge
1. Getting to grips with Excel: workbooks, cells and first calculations2. Clean data and structured tables3. Formulas and references: relative, absolute, mixed, structured4. Conditional functions: IF, SUMIFS, COUNTIFS, AVERAGEIFS5. Looking things up: XLOOKUP, INDEX and MATCH6. Dates and text: computing lead times, splitting references7. PivotTables: summarising thousands of rows8. Charts: getting a message across9. Power Query: import and clean without starting over10. Dynamic arrays: UNIQUE, SORT, FILTER and LET11. KPIs, analysis and dashboards: the final project12. Introduction to Power BI: from workbook to interactive report
Glossary for this page
The key terms used in this content, each with a short definition.
dashboard
A single page gathering the key indicators and charts needed to decide quickly.
Power BI
Microsoft’s platform for analysing data and building interactive, shareable reports and dashboards.
Power Query
A tool in Excel and Power BI to import, clean and reshape data through recorded steps replayed on every refresh.
key performance indicator
A figure tracked regularly to measure progress toward a specific goal, such as margin or delay rate.
DAX
The formula language of Power BI and Excel’s data model, used to write measures such as revenue or growth rate.
XLOOKUP
An Excel function that finds a value in one column and returns the matching value from another; the modern replacement for VLOOKUP.
pivot table
An Excel tool that summarises thousands of rows in seconds: totals, averages and counts by the categories you choose.
CSV
A plain text file with delimiter-separated values; the most common format for moving data between programs.