PRDV006: Spreadsheets II: Formatting and Functions

You can access most formatting commands in Excel from the Format Cells dialog box. To open this box, click the dialog launcher button – the small arrow in the lower right corner of the Number group of the Home tab. This document provides step-by-step instructions on using the Format Cells dialog box to perform standard formatting functions. Each tab in the dialog box controls a different formatting feature. Read this document, which focuses on using the Number, Alignment, Font, Border, and Fill formatting features.

Watch this video for an overview of formatting methods in Google Sheets. The commands are very similar to the formatting tools used in Excel.

Some data types require extra care when putting them into a spreadsheet. For example, when you insert dates, you must make sure they are formatted as dates, not as text or numbers. Watch this video to learn how to input dates properly and use them in formulas.

Use borders and shading to highlight individual and groups of cells or data in your spreadsheet. For example, you may want to highlight a header row or column to organize the message you want to convey and make the information and the message you want to convey stand out. 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 time and help you avoid mistakes when you must retype the same formula several times. It also can give your information a more organized and consistent look. Watch these two tutorials for tips on how to group worksheets and use the same formula across several groups of worksheets.

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

Watch this video, which describes the formatting tools in Google Sheets.

Read this overview of creating formulas in Excel. Pay close attention to the figures showing 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). Copying the formula cell into the next column (column B) will automatically change to =Sum(B1:B10). This feature is useful when performing the same type of calculation on multiple data sets.

The third section discusses the order of operations. When performing complex calculations, your formula must follow the correct sequence. For example, to calculate (2+8)/5, calculate the parentheses (2+8) first and 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 in Excel.

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 to review the order of operations.

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

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

Read this text, which details how loans work and the different parts of the PMT function. Pay attention to Table 2.6, which shows the PMT function's arguments (or inputs).

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

Before we go any further, here is a tip for viewing large spreadsheets that can be difficult to manage because the column or row headings scroll off the screen. Use the Freeze Panes tool to lock rows or columns in place. For example, you can use Freeze Panes to lock columns A and 1–3 in place so the headings in your spreadsheet are always visible at the top or left of the screen as you scroll through the data that follows.

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

Use the IF function to determine whether a given condition is met. For example, you can use the IF function to determine if a student passed a course based on their final grade. Watch this tutorial for another example of using the IF function.

The AND function tells you if more than one set of conditions are met. If all conditions in a set are met, you get the output TRUE. If any condition 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. Watch this tutorial for another example using the AND function.

The OR function tells you if at least one of the input conditions is met. If one or more 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 their two courses.

The NOT function changes TRUE to FALSE or FALSE to TRUE. This is useful when you want to see when a condition is not met, or you want to perform an additional calculation. For example, you can use the NOT function to determine whether a student did not pass the final exam.

The AND, OR, and NOT functions are often used with the IF function to assess multiple conditions. We accomplish this by nesting functions. Nesting occurs when one function is placed as an argument in another function. For example, if a student's midterm grade is in C2 and the final grade is in F2, the following formula would determine whether the student passed both the midterm and final exams:
=IF(AND(C2>=60%,F2>=60%),"passed both midterm and final","did not pass both midterm and final")

In this example, we say the AND function is nested inside the IF function. Nesting adds another layer of complexity to formulas. Simple mistakes can generate errors or incorrect results. As you watch this tutorial, pay close attention to the proper syntax for each type of function.

Read these examples of how to use these different spreadsheet functions in the workplace. The learning objectives review how to use each type of function; the tables describe the syntax for each command type in detail.

Watch this tutorial for some examples of using these functions and the proper input syntax.

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

Use the VLOOKUP function to search for data in vertical columns and the HLOOKUP function to search for data in horizontal rows.

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.

The XLOOKUP function is an update to VLOOKUP and HLOOKUP that can search data horizontally or vertically using the same function. Watch this video on the XLOOKUP function and its optional arguments.

Watch this video for examples of how to use and set up the VLOOKUP and HLOOKUP functions.

Watch this demonstration of how to use Power Query to import data from the web into your spreadsheet. This function will update your data automatically as the information on the website changes.

Watch this tutorial, which demonstrates how to use 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 across multiple worksheets.

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