Master Excel Day to Day: Formulas, PivotTables and Charts

Course outline

A sample lesson, then eight modules: 43 lessons in all. Open a module to see its lessons.

1. 0Module 0Sample lesson1 lesson · 8 min

   1. 0.1What you see, what the cell holds Free sample lesson8 min
2. 1Module 1Get started with Excel and set it up for your language5 lessons · 38 min

   1. 1.1Your Excelversion, display language, separators7 min
   2. 1.2Find your wayworkbook, sheet, cell, range, ribbon, formula bar, Name Box8 min
   3. 1.3Enter data without trapsnumbers, text, dates, percentages8 min
   4. 1.4Copy and fillfill handle, series, Flash Fill7 min
   5. 1.5Organize and saverows, columns, sheets, frozen panes, file formats, useful shortcuts9 min
3. 2Module 2Formulas and references6 lessons · 50 min

   1. 2.1First formulathe equals sign, operators, parentheses, and your Excel’s separator8 min
   2. 2.2Basic functionsSUM, AVERAGE, COUNT, COUNTA, MIN, MAX, and AutoSum8 min
   3. 2.3Copy a formularelative references8 min
   4. 2.4Lock a referencethe dollar sign, absolute and mixed references, the F4 key9 min
   5. 2.5Round without cheating, and calculate percentages9 min
   6. 2.6Read an error#####, #DIV/0!, #NAME?, #VALUE!, #REF!, and error checking9 min
4. 3Module 3Formatting and printing4 lessons · 32 min

   1. 3.1Number and date formatscurrency, percentage, dates, a simple custom format10 min
   2. 3.2Conditional formattingthresholds, data bars, a formula rule8 min
   3. 3.3A readable tablestyles, alignment, “Center Across Selection” rather than merging7 min
   4. 3.4Print and export to PDFpage layout, print area, repeated titles, header and footer8 min
5. 4Module 4Structured tables and clean data5 lessons · 40 min

   1. 4.1A well-built listone row per record, one header per column, nothing merged6 min
   2. 4.2Create and name a table; calculated columns and structured references9 min
   3. 4.3Sort, filter, Total Row8 min
   4. 4.4Drop-down lists and data validation8 min
   5. 4.5Clean up a list you receiveduplicates, spaces, numbers and dates stored as text9 min
6. 5Module 5Calculate with conditions5 lessons · 44 min

   1. 5.1IFone condition, two results8 min
   2. 5.2AND, OR, IFSseveral conditions without getting lost9 min
   3. 5.3Count and sum by criteriaCOUNTIFS, SUMIFS, AVERAGEIFS9 min
   4. 5.4Criteria in cellsthresholds, periods, partial text8 min
   5. 5.5A formula you can rereadIFERROR used wisely, LET, Evaluate Formula10 min
7. 6Module 6Look up, extract and work with dates6 lessons · 51 min

   1. 6.1XLOOKUPfind a price in another list9 min
   2. 6.2When the lookup fails, and older workbooks#N/A, VLOOKUP, INDEX and MATCH9 min
   3. 6.3TextLEFT, RIGHT, MID, LEN, TEXTJOIN; TEXTSPLIT and TEXTBEFORE10 min
   4. 6.4Dates are numbersTODAY, DATE, EOMONTH, EDATE, due dates8 min
   5. 6.5Working days and schedulesNETWORKDAYS, WORKDAY, a list of holidays8 min
   6. 6.6Lists that calculate themselvesSORT, FILTER, UNIQUE, and the #SPILL! error8 min
8. 7Module 7PivotTables and charts6 lessons · 49 min

   1. 7.1Your first PivotTablea source in a table, fields, four areas9 min
   2. 7.2Group and calculatemonths and quarters, percentage of the total, several values9 min
   3. 7.3Slicers, timeline, refreshwhat keeps a PivotTable from following your data9 min
   4. 7.4Choose the right chartthe question before the type8 min
   5. 7.5A chart that follows the data and tells the truthsource, axes, titles, labels8 min
   6. 7.6A summary pagePivotChart, shared slicers, sparklines7 min
9. 8Module 8AI and your formulas, then what comes next5 lessons · 35 min

   1. 8.1Ask an AI assistant for a formuladescribe your table without real data8 min
   2. 8.2Check the formula you receive before you use it9 min
   3. 8.3Copilot in Excelwhat it takes to see it, what it does, its limits7 min
   4. 8.4Read a tutorial in another languagetranslate a formula for your Excel6 min
   5. 8.5What formulas do not doa look at Power Query, the course review and what comes next6 min

Objectives

By the end of this course, you will be able to:

• Read your Excel’s settings (version, language, separator) and adapt a formula found elsewhere to them
• Enter numbers, dates and text so that Excel recognizes them, and spot a number stored as text
• Write formulas that copy without errors, with well-chosen relative, absolute or mixed references
• Read and fix common errors (#####, #DIV/0!, #NAME?, #VALUE!, #REF!, #N/A, #SPILL!)
• Turn a list into a table, sort it, filter it, total it and control data entry with drop-down lists
• Calculate with one or more conditions (IF, IFS, SUMIFS, COUNTIFS) and write formulas that are easy to read
• Find information in another list with XLOOKUP, and read older workbooks that use VLOOKUP
• Split and join text, calculate due dates and working days, and produce lists that update themselves
• Summarize a list with a PivotTable and choose a chart that tells the truth
• Ask an AI assistant for a formula without sharing real data, then check it before you use it