PRDV006: Spreadsheets II: Formatting and Functions

This course is intended for students who have a basic understanding of spreadsheets and are ready to delve into formatting, formulas, functions, multi-page spreadsheets, charting data, creating tables with database features, and pivot tables. This course builds on PRDV004: Spreadsheets, so be sure to review basic formatting commands of Excel and Google Sheets. Each unit in this course builds on the previous unit, so make sure you have mastered each unit before you move on.

First, read the course syllabus. Then, enroll in the course by clicking "Enroll me in this course". Click Unit 1 to read its introduction and learning outcomes. You will then see the learning materials and instructions on how to use them.

When making a spreadsheet, it is important to use formatting techniques to improve readability for the user. We use formatting tools to highlight information we want to emphasize and clearly organize large amounts of data. In this unit, we discuss how to format cells, text, numbers, and dates in depth. We also introduce conditional formatting tools to format text and numbers based on specific criteria or conditions.

Completing this unit should take you approximately 1 hour.

Spreadsheet programs shine when used to handle formulas and functions. Formulas are mathematical equations you write yourself, while functions refer to the pre-built formulas that Microsoft Excel or Google Sheets include for you to use. Examples of functions are calculations of the average or standard deviation of a set of data. In this unit, we review basic functions and writing formulas. Then, we discuss special types of functions and formulas used in statistics and business.

Completing this unit should take you approximately 2 hours.

In this unit, we explore logical functions, which are more advanced Microsoft Excel functions. Logical functions in Microsoft Excel allow you to assess the text or numerical content within cells and produce custom outputs.

For example, imagine you are an instructor with a class of 250 students. If a student's final grade is 60 percent or higher, they pass. If their final grade is below 60 percent, they fail. We can use the logical function IF to automatically determine if each student passed or failed based on their final grades. Logical functions can make these types of determinations for large data sets.

Completing this unit should take you approximately 1 hour.

In this unit, we focus on lookup functions, which let you find a given value from a large set of data in another worksheet and display the value where you want to. We also introduce the VLOOKUP and HLOOKUP functions and explore how to create a web query that imports stock prices to your worksheet from a website. You can use web queries to import any data you may need. We also examine how to use Goal Seek to change function inputs to give the desired result.

Completing this unit should take you approximately 1 hour.

We often have to use multi-page spreadsheets to manage large sets of data. For example, you may want to use a different worksheet or page for each month for a budget spreadsheet. In this situation, we often use the same formulas on multiple worksheets within one workbook. You can accomplish this without having to rewrite the formula in each worksheet. In this unit, we learn how to work with multi-page spreadsheets.

Completing this unit should take you approximately 1 hour.

Please take a few minutes to give us feedback about this course. We appreciate your feedback, whether you completed the whole course or even just a few resources. Your feedback will help us make our courses better, and we use your feedback each time we make updates to our courses.

If you come across any urgent problems, email contact@saylor.org or post in our discussion forum.

Take this exam if you want to earn a free Course Completion Certificate.

To receive a free Course Completion Certificate, you will need to earn a grade of 70% or higher on this final exam. Your grade for the exam will be calculated as soon as you complete it. If you do not pass the exam on your first try, you can take it again as many times as you want, with a 7-day waiting period between each attempt.

Once you pass this final exam, you will be awarded a free Course Completion Certificate.