Topic Name Description
Course Syllabus Page Course Syllabus
1.1: Formatting Cells and Numbers File Formatting Cells in Microsoft Excel

Let's begin by reviewing the tools available in the Microsoft Excel toolbar. Read this document, which provides step-by-step instructions on using the Microsoft Excel dialog box to perform common formatting functions. Each tab in the dialog box controls a different formatting feature. This document focuses on using the Number, Alignment, Font, Border, and Fill formatting features.

Page Formatting Cells in Google Sheets

Watch this video for an overview of formatting methods in Google Sheets. You will see it is similar to Excel.

Page Formatting Special Data

Some special types of data require extra care when inputting them into a spreadsheet. For example, when you input dates, you need to make sure they are actually formatted as dates. Watch this video to learn how to properly input dates and how to use them in formulas.

Page Borders and Shading

When you create a spreadsheet, adding borders or shading can highlight certain cells for the people who use the spreadsheet. For example, you may want to highlight a specific row or column to make your information easier to read. Watch this tutorial on how to use these formatting techniques.

1.2: Formatting Worksheet Tabs Page Adding Tabs to a Workbook

Watch this tutorial on formatting tabs in a workbook.

Page Grouping Worksheets in Microsoft Excel

Grouping worksheets within a workbook can be helpful, especially if you want to use the same formula over multiple worksheets. This can save you from having to retype a formula several times. Watch these tutorials on how to group worksheets and use a formula in multiple grouped worksheets.

1.3: Conditional Formatting Page Conditional Formatting in Microsoft Excel

Watch this tutorial for some examples of using conditional formatting in Microsoft Excel.

Page Conditional Formatting in Google Sheets

Watch this video, which describes the same process in Google Sheets.

2.1: Formulas Book Formulas, Relative References, Order of Operations, and Auditing Formulas

Read this overview of how to create formulas in Excel. Pay close attention to the figures that show how to enter formulas in a budget spreadsheet.

The second section explains relative referencing. Excel will adjust the cell reference used in a formula when you copy and paste the formula into a new location. For example, if you are summing numbers in column A, you may have a cell with the formula =SUM(A1:A10). If you copy the formula cell into the next column (column B), it will automatically change to =Sum(B1:B10). This feature is useful when you are performing the same type of calculation on multiple sets of data.

The third section discusses the order of operations. When you perform complex calculations, your formula must follow the correct order of operations. For example, to calculate (2+8)/5, you first calculate the parentheses (2+8) and then divide its result by 5. Spreadsheet programs have formatting requirements for the order of operations. Pay special attention to Table 2.3, which shows the order of operations Excel follows.

The final section discusses auditing formulas, which allows you to check or review the formulas you use in a spreadsheet using a simple command. You can also see which cells are referenced in each spreadsheet formula.

Page Order of Operations Review

Watch this video if you need to review the order of operations. There is no audio for this video.

2.2: Statistical Functions Book Statistical Functions in Microsoft Excel

As you read, review Table 1, which outlines the most common statistical functions in Microsoft Excel. You can apply these functions to a large set of data entered in a spreadsheet. The rest of this section details how to use each type of function in a budget spreadsheet.

Page Statistical Functions in Google Sheets

You can also use Google Sheets to perform statistical analysis. Watch this overview of performing simple statistical analysis on a small set of data.

2.3: Functions for Personal Finance Book Functions for Personal Finance

This section details how loans work and the different parts of the PMT function. Pay attention to Table 2.6, which shows the arguments (or inputs) of the PMT function.

Page How to Calculate a Monthly Payment in Microsoft Excel

Watch this demonstration of how to use the PMT function in your Microsoft Excel spreadsheet.

3.1: Logical Functions Page Freezing Panes

Read this section which explains how the Freeze Panes command lets you scroll across a spreadsheet while keeping specific rows or columns locked in place. This feature is useful if you need to keep track of certain rows or columns while working on other parts of the spreadsheet.

Page Logical Functions

The most commonly-used logical functions are IF, AND, OR, and NOT.

  • We use the IF function to determine if a given condition is met. A course with pass/fail grading is an example of using the IF function.
  • The AND function tells you if more than one set of conditions are met or not. If everything in the set of conditions is met, you get the output TRUE. If anything in the set of conditions is not met, you get the output FALSE. For example, you can use the AND function to determine if a student passed both the midterm and final exam in a course.
  • The OR function tells you if at least one of the input conditions are met. If one or more of the conditions are met, you get the output TRUE. If all conditions are not met, you get the output FALSE. For example, you can use the OR function to determine if a student passed at least one of the two courses they took.
  • The NOT function changes TRUE to FALSE or FALSE to TRUE. Each of these functions has unique and has more complex input syntax than the other functions we have studied.

As you watch this tutorial, note the proper input syntax for each type of function.

3.2: Statistical IF Functions Book Statistical IF Functions

Read these examples of how to use these different functions in business. The learning objectives review each type of function's use, and the tables describe the syntax for each type of command in detail.

Page Using COUNTIF, SUMIF, and AVERAGEIF in Microsoft Excel

Watch this tutorial to see some simple examples of how to use these functions and the proper input syntax.

Page Using IF, COUNTIF, and SUMIF in Google Sheets

Logical functions in Google Sheets work similarly to Excel. Watch this video to see how to input logical functions and statistical logical functions in Google Sheets.

4.1: Lookup Functions Page Lookup Functions

Read these two sections on the VLOOKUP and HLOOKUP functions. The input for lookup functions is complicated, and you must be precise. Pay close attention to Tables 3.10 and 3.11, which detail the inputs for the two functions. Also, note the Skills Refreshers, which review the steps for entering the functions in Microsoft Excel.

Page VLOOKUP and HLOOKUP in Microsoft Excel

This video gives examples of how to use and set up the VLOOKUP and HLOOKUP functions.

4.2: Web Queries Page Web Queries

Read the section on web queries, which explains how to import stock prices directly into an Excel spreadsheet. The Skills Refresher summarizes the steps to creating a web query in Microsoft Excel.

Page Creating a Web Query in Microsoft Excel

Watch this demonstration of how to create a web query to use in your spreadsheet. This allows you to insert data that will automatically update as the information on the website changes.

4.3: Goal Seek Page Using Goal Seek in Microsoft Excel

Watch this tutorial, which goes through an example of using Goal Seek to tally votes in an election. You can apply Goal Seek in many different ways.

4.4: Printing Formulas and Functions Page Printing in Formulas View

Read this document, which shows how to print formulas so they are readable.

5.1: 3D Formulas Page 3D Formulas

Watch this tutorial, which uses the example of quarterly sales to show how to use 3D formulas over multiple worksheets.

5.2: Formulas Across Workbooks Page Formulas Across Workbooks

Watch this video, which uses the same quarterly sales example to show how to use formulas across multiple workbooks.

Course Feedback Survey URL Course Feedback Survey