PRDV006: Spreadsheets II: Formatting and Functions

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.

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

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.

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.

Watch this tutorial on formatting tabs in a workbook.

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.

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

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

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.

Watch this video if you need to review the order of operations.

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.

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

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.

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

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.

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.

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.

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

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.

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.

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

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.

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.

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.

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

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

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