Chrysolites Tech Academy

Video by freeCodeCamp.org · streamed from YouTube. All rights remain with the creator.

Lesson 4: Excel Formulas That Do Real Work

About this lesson

Spreadsheets are where a great deal of everyday data work happens, and formulas are what turn a grid of numbers into answers. This lesson is a practical course in the formulas and functions that matter most in current versions of Excel, arranged in six parts with quizzes and practice problems along the way. It begins with core mechanics: how a formula is built, how cell references behave when a formula is copied, when to lock a reference in place, and how to read and handle the error codes that appear when something goes wrong. The second part covers logic and branching, using IF together with AND and OR, and then IFS for cases with several outcomes. Part three is aggregation, which means counting, adding and averaging only the rows that meet one or more conditions. Part four teaches modern lookups, replacing older techniques with XLOOKUP and with the flexible pairing of INDEX and XMATCH. The fifth part turns to data manipulation, cleaning up untidy text and working correctly with dates. The lesson closes with dynamic arrays, the newer style of formula that returns a whole list of results from a single cell and updates itself as the data changes. Everything is demonstrated on screen, so open a blank workbook and build each formula alongside the teacher; the skills carry over to other spreadsheet programs. When you are done, read Chapter 4 of the course book, which works through the same families of functions on one small sales table, with every result calculated by hand so you can check your own sheet.

Install Chrysolites Tech Academy

Android / Chrome / Edge: open the browser menu and choose Install app or Add to Home screen.

iPhone / iPad (Safari): tap the Share button, then Add to Home Screen.