Excel Formulas and Data Analysis Course: Lessons 2026

Lesson 1: Logical formulas with IF, IFS, AND and OR

A logical test is a comparison that is either TRUE or FALSE, such as B2>=50000 or D2="North". You already know that IF returns one value when the test is TRUE and another when it is FALSE. This lesson shows how to check several things at once.

AND and OR can take up to 255 conditions, but Microsoft advises using only a few so the formula stays easy to read. NOT takes only one condition. Put AND, OR or NOT inside IF to choose a result: =IF(AND(B2>=50000,C2>=12),"Bonus","No bonus").

Watch the comparison signs

> means "greater than", so 40>40 is FALSE. Use >= when the limit itself should count. Text in a formula needs double quotation marks. If you type =IF(D2=North,…) without quotes, Excel reads North as a name and usually shows #NAME?.

Nested IF and IFS

To choose between more than two results, you can put one IF inside another: =IF(B2>=60000,"Gold",IF(B2>=50000,"Silver","Bronze")). Excel stops at the first TRUE test, so order matters. If you test B2>=50000 first, a 61,000 sale returns "Silver" and never reaches the Gold test. Always start with the strictest test.

IFS is easier to read. It takes pairs of test and result: =IFS(B2>=60000,"Gold",B2>=50000,"Silver",TRUE,"Bronze"). It can test up to 127 conditions. If no test is TRUE, IFS returns #N/A, so add TRUE as the last test to give a default result. IFS is in Excel 2019 and later, including Microsoft 365.

IF can also do math. =IF(B2>40,(B2-40)*C2*1.5,0) pays time-and-a-half only on hours over 40. 44 hours at $22 gives (44-40) x 22 x 1.5 = 132; exactly 40 hours gives 0.

Themaquiz 1: Logical formulas: IF, IFS, AND and OR

Lesson 2: Conditional totals with SUMIFS, COUNTIFS and AVERAGEIFS

The IFS functions answer questions like "How much did Ana sell?" or "How many North orders were Alarms?" without sorting or filtering. A row is included only when all the conditions are true.

Each function accepts up to 127 range/criteria pairs. Every range in the formula must have the same number of rows and columns, so Excel can compare the cells row by row. If no rows match, AVERAGEIFS returns #DIV/0!.

Writing criteria

• Text goes in quotes: "North".
• Operators go inside the quotes: ">=600", "<>Sensors" (not equal to Sensors).
• Wildcards: ? matches one character and * matches any number of characters. "?e?" matches Ben and Dev; "C*" matches anything starting with C. Put ~ before a real ? or *.
• Cell references: join the operator with &, as in ">"&H2. Writing ">H2" compares with the text H2.
• Dates: ">="&DATE(2026,3,9) and "<="&DATE(2026,3,12) give a date range that includes both ends.

"Or" questions

Because all conditions must be true, =SUMIFS(F2:F11,C2:C11,"North",C2:C11,"East") returns 0: no cell is North and East at once. To total North or East, add two formulas: =SUMIFS(F2:F11,C2:C11,"North")+SUMIFS(F2:F11,C2:C11,"East") returns 4,590.

Themaquiz 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Lesson 3: Lookups with XLOOKUP, VLOOKUP, INDEX and MATCH

A lookup finds a value (such as a product code) in one column and returns the matching value from another column in the same row, such as its price.

XLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). It uses an exact match by default. The return column can be on either side, so =XLOOKUP("Smart lock",B2:B7,A2:A7) returns the code LCK-05 from the column to the left. If nothing matches, XLOOKUP returns #N/A, unless you give if_not_found text such as "Not listed".

match_mode -1 means "exact match, or else the next smaller item". It is useful for rate tables: with tiers starting at 0, 10,000, 25,000 and 50,000, a sale of 32,000 gets the 25,000 rate. Match_mode 1 returns the next larger item, and 2 allows wildcards. XLOOKUP is in Excel for Microsoft 365 and Excel 2021 or later; it is not in Excel 2016 or 2019.

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches only the first column of the table and looks left to right. col_index_num counts columns from the left of the table, starting at 1. =VLOOKUP("LCK-06",A2:E7,4,FALSE) returns 119.

• Use FALSE for an exact match. If you leave range_lookup out, VLOOKUP uses approximate match, which expects the first column to be sorted.
• #N/A means the value was not found (for example, a name searched in the code column).
• #REF! means col_index_num is larger than the number of columns in the table.

INDEX and MATCH

MATCH(lookup_value, lookup_array, 0) returns the position of a value, not the value: =MATCH("LCK-05",A2:A7,0) returns 5. Use 0 for an exact match; match_type 1 (the default) and -1 need sorted data. INDEX(array, row_num) returns the value at a position: =INDEX(D2:D7,3) returns 24.75. Together they work in any direction: =INDEX(B2:B7,MATCH(229,D2:D7,0)) returns Smart lock.

Handling errors

=IFERROR(value, value_if_error) returns your own value when a formula gives #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL!. =IFNA(value, value_if_na) replaces only #N/A, so other errors still show and you can fix them.

Themaquiz 3: Lookups: XLOOKUP, VLOOKUP, INDEX/MATCH and IFERROR

Lesson 4: Text and date functions

Imported lists often have codes you need to split, names typed in different ways, and dates you need to count. Text functions return text, even when the result looks like a number (RIGHT("EDM-0087",4) returns the text 0087).

SEARCH(find_text, within_text) returns the position where text starts and ignores case: =SEARCH("lock","Smart Lock") returns 7. Use FIND when uppercase and lowercase must match. Both return #VALUE! if the text is not found.

How dates work

Excel stores dates as serial numbers: January 1, 1900 is 1, and each day adds 1. That is why =B2+30 gives a date 30 days later and =B2-A2 gives the days between two dates. When you join a date to text with &, Excel drops the date format and shows the number, so use TEXT: ="Due "&TEXT(B2,"yyyy-mm-dd"). TEXT also gives day names with "dddd". Its result is text, so keep the real date in another cell for calculations.

DATEDIF returns #NUM! if the start date is after the end date. Microsoft does not recommend its "MD" unit because it can give wrong results.

Themaquiz 4: Text and date functions

Lesson 5: Conditional formatting, data validation and duplicates

Conditional formatting

Conditional formatting changes how cells look when they meet a rule. Find it on the Home tab, in the Styles group.

• Highlight Cells Rules: format values greater than, less than or between limits, dates, or Duplicate Values.
• Top/Bottom Rules: for example, Top 10 Items to highlight the highest values, or the bottom 10% of values.
• Data Bars: a coloured bar in each cell, longer for larger values.
• Color Scales: the cell colour shows where a value sits between the lowest and highest.
• Icon Sets: three to five groups of values, each with its own icon and threshold.

For your own rule, choose New Rule > Use a formula to determine which cells to format. Write the formula for the active cell (the first cell you selected) and use a relative reference: with B2:B20 selected, =E2>300 checks E2 for B2, E3 for B3, and so on. The formula must start with =. If you leave it out, Excel adds = and quotation marks, so the rule is read as text and does not work.

In Manage Rules, the newest rule is at the top by default. When rules conflict, the rule higher in the list wins. Use Move Up and Move Down to change the order, and tick Stop If True to stop checking lower rules for cells where that rule is true.

Data validation

Choose Data > Data Validation to control what people type. In Allow, pick Whole Number, Decimal, List, Date, Time, Text Length or Custom (a formula). For a drop-down list, choose List and type the items separated by commas in Source, such as Low,Average,High. Use between to set a minimum and a maximum. The Input Message tab shows a tip when the cell is selected.

Validation checks only what people type. Pasted or filled data can get past it, and Excel does not check data that was there before the rule. Use Data Validation > Circle Invalid Data to find those cells. To remove a rule, choose Clear All in the Data Validation box.

Duplicates

First highlight duplicates (Highlight Cells Rules > Duplicate Values) so you can review them. Data > Remove Duplicates then deletes repeated rows permanently, so copy the original data to another sheet first, and remove any outlines or subtotals. In the dialog box, check only the columns that must match: a row is removed when all checked columns match another row.

The UNIQUE function makes a list without changing the data: =UNIQUE(B2:B8) lists each value once, and =UNIQUE(B2:B8,FALSE,TRUE) lists only values that appear exactly once. Its result spills into the cells below.

Themaquiz 5: Conditional formatting, data validation and duplicates

Lesson 6: PivotTables and charts for analysis

A PivotTable summarizes a long list in seconds, for example total sales by rep, or by region and product. Get the data ready first: columns with a single header row, no blank rows or columns, and ideally an Excel table so new rows are included. Select a cell in the data and choose Insert > PivotTable.

When you tick a field, text fields usually go to Rows and numeric fields to Values; drag fields between areas to change the layout. Number fields in Values are shown as a Sum. If a field has text or blank cells, Excel uses Count instead; right-click a value and choose Summarize Values By to change it (for example to Sum, Average, Max or Min).

Right-click a value and choose Show Values As to show % of Grand Total, % of Column Total, % of Row Total, a Running Total, a Difference From another item, or a rank. With the Lesson 2 sales log, Region in Rows and Amount as % of Grand Total, North shows 43.9% (3,310 of 7,540).

A PivotTable does not always update by itself when you change the source data. Right-click it and choose Refresh (or press Alt+F5), or use Refresh All for every PivotTable. Slicers (Insert > Slicer) are buttons that filter a PivotTable or table; hold Ctrl to pick several items, and choose Clear Filter to show everything. Insert > PivotChart makes a chart from the same summary.

Charts for analysis

Themaquiz 6: PivotTables and charts for analysis