Learn how to format spreadsheets to make them easy to read, apply conditional formatting to draw attention to critical data, use formulas and functions, build multi-page spreadsheets, and add 3-dimensional formulas and functions across multiple worksheets and workbooks.
In this course, we explore how to format spreadsheet data to make the information easy to read and apply conditional formatting to draw attention to critical data. We will use formulas and functions with absolute and relative references to perform mathematical, statistical, financial, lookup, and logical computations. Then, we will learn how to build multi-page spreadsheets, add three-dimensional formulas to summarize data across several worksheets, and implement formulas and functions to present the consolidated information that exists in multiple workbooks.
This course builds on the concepts and skills covered in Saylor Academy's introductory PRDV004: Spreadsheets course, which introduces the definition of a spreadsheet, how to navigate and enter data, open and save files, format spreadsheets, and enter basic formulas and functions for real-life applications. Each unit in this course builds on the previous unit, so make sure you have mastered each unit before moving on.
- Unit 1: Formatting Spreadsheets
- Unit 2: Mathematical Computations
- Unit 3: Logical Functions
- Unit 4: Lookup Functions
- Unit 5: Multi-Page Spreadsheets
- Use formatting features to format text, cells, and numbers, and use conditional formatting as appropriate;
- Write basic formulas and use functions SUM, AVERAGE, COUNT, MAX, MIN, PMT, and FV to perform basic, statistical, and financial calculations;
- Use logical functions including IF, OR, AND, NOT, COUNTIF, AVERAGEIF(S), and SUMIF(S) to summarize data;
- Use lookup functions, including VLOOKUP, HLOOKUP, and XLOOKUP to retrieve data from a worksheet; and
- Format multiple worksheets at one time and use 3-dimensional references.
- Blank paper for notes or scratch work
- Water or a non-alcoholic beverage