STID1103 – APLIKASI KOMPUTER DALAM PENGURUSAN

PANDUAN LENGKAP MICROSOFT EXCEL

Excel 2019, Excel 2016, Office 365

https://edu.gcfglobal.org/en/excel/

Excel 2013, Excel 2010, Excel 2007, Excel 2003 Excel XP, Excel 2000
https://edu.gcfglobal.org/en/topics/excel/

MS Excel Formulas
https://edu.gcfglobal.org/en/excelformulas/

MS Excel Tips
https://edu.gcfglobal.org/en/excel-tips/

Prepared by: Mohd Tarmizi Musa

STID1103 - Aplikasi Komputer dalam Pengurusan

SENARAI SEMAK TUTORIAL MS EXCEL

Bil Topik ✓
Getting Started with Excel
1
Learn your way around the Excel environment.
Understanding OneDrive
2
Learn all about working with your Microsoft account and OneDrive.
Creating and Opening Workbooks
3
Learn how to create and open Excel workbooks.
Saving and Sharing Workbooks
4
Learn how to save and share Excel workbooks.
Cell Basics
5 Learn how to insert and delete cells and cell content, drag and drop cells, use the
fill handle, and use the cut, copy, and paste functions.
Modifying Columns, Rows, and Cells
6
Learn how to modify rows, columns, and cells in Excel.
Formatting Cells
7
Learn all about formatting text in Excel.
Understanding Number Formats
8
Learn all about formatting numbers in Excel.
Working with Multiple Worksheets
9 Learn how to add, delete, copy, and move worksheets, as well as group and
ungroup worksheets.
Using Find & Replace
10
Learn how to use Excel's Find and Replace features to search for specific data.
Checking Spelling
11
Learn how to use Excel's spell check feature.
Page Layout and Printing
12 Learn how to use Excel commands that let you control the way your content will
appear on a printed page.
Intro to Formulas
13
Learn how to use cell references to create a simple formula in Excel.
Creating More Complex Formulas
14
Learn how to create a complex formula in Excel.
Relative and Absolute Cell References
15 Learn all about absolute and relative cell references and how they're used in
Excel.
Functions
16 Learn the different parts of a function and how to create arguments to calculate
values and cell references.
Basic Tips for Working with Data
17 Learn these basic tips for working with data to help you organize it and find what
you need.
1 Myz
STID1103 - Aplikasi Komputer dalam Pengurusan

Freezing Panes and View Options

18 Learn how to freeze panes and split your worksheet so you're only seeing certain
parts of your worksheet.
Sorting Data
19 Learn how to sort data to better view and organize the contents of your
spreadsheet.
Filtering Data
20 Learn how to filter the data in your worksheet to display only the information
you need.
Groups and Subtotals
21 Learn how to organize data in groups, summarize different groups using the
Subtotal command, and create an outline for your worksheet.
Tables
22
Learn how to use styles to format tables in Excel.
Charts
23
Learn how to insert and modify Excel charts to visualize comparisons in your data.
Conditional Formatting
24
Learn how to apply, modify, and delete conditional formatting rules in Excel.
Comments and Co-authoring
25 Learn how to use Excel's track changes and comments features when
proofreading workbooks.
Inspecting and Protecting Workbooks
26
Learn how to inspect and protect your Excel workbook.
Intro to PivotTables
27
Learn how to use PivotTables in Excel for stronger data analysis.
Doing More with PivotTables
28
Learn how to do more with PivotTables to better summarize data.
What-if Analysis
29
Learn how to use what-if analysis to answer different types of questions.

Formulas

About This Tutorial

30 Learn more about using formulas and functions in Excel or another spreadsheet
application.
Simple Formulas
31
Learn all about creating simple formulas.
Complex Formulas
32
Learn all about creating complex formulas.
Relative and Absolute Cell References
33
Learn how relative and absolute cell references work.
Functions
34
Learn all about using functions.

2 Myz
STID1103 - Aplikasi Komputer dalam Pengurusan

Solving Real-Life Problems in Excel

35 Learn about solving real-life problems using Excel and other spreadsheet
applications.
Double-Check Your Formulas
36
Learn the importance of always double-checking your formulas.
Understanding Number Formats
37 Better understand number formats so you can use them properly in your
spreadsheet.
Practice Reading Formulas
38
Get practice reading formulas.
Interactive: Formula Flashcards
39
Use this interactive to practice an Excel formula.
Over-Budget Items
40
Use a formula to determine which expenses are over budget in this interactive.
Percent-Off Sale
41 Use a formula to determine how much everything will cost after a discount in
this interactive.
Basketball Team: Player Statistics
42 Use a formula to determine who scored the most points overall in this
interactive.
Movie Tickets
43 Use a formula to determine how much is left over after buying movie tickets for
4 in this interactive.
Interactive: Pizza Party Panic
44 Use a formula to determine how much pizza is needed for a party in this
interactive.
Interactive: Birthday Budget Blues
45 Use a formula to determine how much a birthday party will cost in this
interactive.
Using the IF Function
46
Use the IF function to help solve this real-world problem.
Using CONCATENATE to Combine Names
47 Use the CONCATENATE function to practice combining names in your
spreadsheet.
Buying a Printer: Cost Comparison
48
Use a formula to determine which printer is best in this interactive.
Invoice, Part 1: Free Shipping
49 Use a formula to determine free shipping if the subtotal is less than $100 for a
particular order.
Invoice, Part 2: Using VLOOKUP
50
Use the VLOOKUP function to help process invoices.
Invoice, Part 3: Fix Broken VLOOKUP
51 Fix a broken VLOOKUP function someone else created to help pull in new data
correctly.
Invoice, Part 4: More Shipping Options
52
Use the VLOOKUP function to determine new shipping options.
3 Myz
STID1103 - Aplikasi Komputer dalam Pengurusan

Invoice, Part 5: Data Validation

53
Use data validation to help solve a real-world shipping problem.

Excel Quick Tips

Add the Current Date to a Cell

54 Learn how to add a date or time stamp to any cell with this Excel keyboard
shortcut.
A Faster Way to Use the Fill Handle
55
Learn a faster way to use the fill handle in Excel.
Two Ways to Use the Format Painter
56
Learn how to use the Format Painter in Excel.
A Shortcut to AutoSum
57
Learn an Excel keyboard shortcut that makes the AutoSum function even faster.
Quick Access Toolbar Shortcuts
58
Learn how to add shortcuts to the Quick Access Toolbar in Excel.
How to Quickly Duplicate a Sheet
59
Learn a shortcut for making an exact copy of an existing worksheet.
Jump to Any Sheet with a Shortcut
60
Here's a shortcut for quickly jumping to any sheet within a workbook.
Select an Entire Row or Column
61
Learn these Excel shortcuts for selecting an entire row or column at one time.
Find a Sum Without Using a Formula
62
Here's how to find a sum without using a formula.
Why You Should Avoid Merging Cells
63
Use the Center Across Selection feature to easily merge cells in Excel.
Using the Quick Analysis Tool
64 Learn how to browse through different visualization options with the Quick
Analysis tool.
How to Make Charts Auto Update
65
Learn how to use tables to keep charts up to date using this Excel feature.
Use Transpose to Flip Data on its Side
66
Here's how to use the Transpose feature to rearrange your data instantly.
Absolute References with the F4 Key
67
Learn how to convert a cell reference to an absolute reference with the F4 key.
Add a New Row with a Shortcut
68 Learn a keyboard shortcut that lets you add a row anywhere in your
spreadsheet.
Use an Entire Column as a Cell Range
69
Learn how to use an entire column as a cell range in Excel.
What are Named Ranges?
70 Learn how to use named ranges to make your formulas easier to write and
read.
A Trick for Finding Inconsistent Data
71
Here's a trick for quickly finding inconsistent data in a large spreadsheet.

4 Myz
STID1103 - Aplikasi Komputer dalam Pengurusan

How to Use Excel's VLOOKUP Function

72
Use VLOOKUP to find specific information in your worksheet.
Use SUMPRODUCT to Calculate Weighted Averages
73
Use SUMPRODUCT to calculate weighted averages.
Use the PROPER Function to Capitalize Names in Excel
74
Use the PROPER function to capitalize names in your spreadsheet.
How to Count Cells with COUNTA
75 Use the COUNTA function for counting cells and for determining how many cells
have data in them.
CONCATENATE: Excel's Duct Tape
76
Use the CONCATENATE function to combine two or more things in one cell.
5 Tips for Printing Excel Spreadsheets
77
Use these tips for printing Excel spreadsheets.
How to Use Fractions in Excel
78
Learn how to work with fractions in Excel.
How to Add Line Breaks in Excel
79
Learn how to add line breaks in Excel.
Free Resources for Learning Office for Mac
80
Use these free resources for learning Office for Mac.
Understanding Regional Differences in Excel
81
Use regional settings in Excel to use the program in any language.

5 Myz
5/19/2020 Excel: What-if Analysis

Excel
Getting Started with Excel

Introduction
Excel is a spreadsheet program that allows you to store, organize, and analyze
information. While you may think Excel is only used by certain people to process
complicated data, anyone can learn how to take advantage of the program's powerful
features. Whether you're keeping a budget, organizing a training log, or creating an
invoice, Excel makes it easy to work with di erent types of data.

Watch the video below to learn more about Excel.

Excel: Getting Started

About this tutorial

The procedures in this tutorial will work for all recent versions of Microso Excel,
including Excel 2019, Excel 2016, and O ice 365. There may be some slight di erences,
but for the most part these versions are similar. However, if you're using an earlier
version, you may want to refer to one of our other Excel tutorials instead.

The Excel Start Screen

When you open Excel for the first time, the Excel Start Screen will appear. From here,
you'll be able to create a new workbook, choose a template, and access your recently
edited workbooks.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 1/258
5/19/2020 Excel: What-if Analysis

▶ From the Excel Start Screen, locate and select Blank workbook to access
the Excel interface.

The parts of the Excel window

Some parts of the Excel window (like the Ribbon and scroll bars) are standard in most
other Microso programs. However, there are other features that are more specific to
spreadsheets, such as the formula bar, name box, and worksheet tabs.

Click the buttons in the interactive below to become familiar with the parts
of the Excel interface.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 2/258
5/19/2020 Excel: What-if Analysis

Working with the Excel environment

The Ribbon and Quick Access Toolbar are where you will find the commands to
perform common tasks in Excel. The Backstage view gives you various options for
saving, opening a file, printing, and sharing your document.

The Ribbon
Excel uses a tabbed Ribbon system instead of traditional menus. The Ribbon contains
multiple tabs, each with several groups of commands. You will use these tabs to
perform the most common tasks in Excel.

▶ Each tab will have one or more groups.

▶ Some groups will have an arrow you can click for more options.

▶ Click a tab to see more commands.

▶ You can adjust how the Ribbon is displayed with the Ribbon Display
Options.

Certain programs, such as Adobe Acrobat Reader, may install additional

tabs to the Ribbon. These tabs are called add-ins.

To change the Ribbon Display Options:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 3/258
5/19/2020 Excel: What-if Analysis

The Ribbon is designed to respond to your current task, but you can choose to minimize
it if you find that it takes up too much screen space. Click the Ribbon Display Options
arrow in the upper-right corner of the Ribbon to display the drop-down menu.

There are three modes in the Ribbon Display Options menu:

▶ Auto-hide Ribbon: Auto-hide displays your workbook in full-screen mode

and completely hides the Ribbon. To show the Ribbon, click the Expand
Ribbon command at the top of screen.

▶ Show Tabs: This option hides all command groups when they're not in use,
but tabs will remain visible. To show the Ribbon, simply click a tab.

▶ Show Tabs and Commands: This option maximizes the Ribbon. All of the
tabs and commands will be visible. This option is selected by default when
you open Excel for the first time.

The Quick Access Toolbar

Located just above the Ribbon, the Quick Access Toolbar lets you access common
commands no matter which tab is selected. By default, it includes the Save, Undo, and
Repeat commands. You can add other commands depending on your preference.

To add commands to the Quick Access Toolbar:

Click the drop-down arrow to the right of the Quick Access Toolbar.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 4/258
5/19/2020 Excel: What-if Analysis

2 Select the command you want to add from the drop-down menu. To
choose from additional commands, select More Commands.

3 The command will be added to the Quick Access Toolbar.

How to use Tell me:

The Tell me box works like a search bar to help you quickly find tools or commands you
want to use.

1 Type in your own words what you want to do.

2 The results will give you a few relevant options. To use one, click it like you
would a command on the Ribbon.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 5/258
5/19/2020 Excel: What-if Analysis

Worksheet views
Excel has a variety of viewing options that change how your workbook is displayed.
These views can be useful for various tasks, especially if you're planning to print the
spreadsheet. To change worksheet views, locate the commands in the bottom-right
corner of the Excel window and select Normal view, Page Layout view, or Page Break
view.

▶ Normal view is the default view for all worksheets in Excel.

▶ Page Layout view displays how your worksheets will appear when printed.
You can also add headers and footers in this view.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 6/258
5/19/2020 Excel: What-if Analysis

▶ Page Break view allows you to change the location of page breaks, which is
especially helpful when printing a lot of data from Excel.

Backstage view
Backstage view gives you various options for saving, opening a file, printing, and
sharing your workbooks.

To access Backstage view:

1 Click the File tab on the Ribbon. Backstage view will appear.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 7/258
5/19/2020 Excel: What-if Analysis

Click the buttons in the interactive below to learn more about using
Backstage view.

Challenge!
1 Open Excel.

2 Click Blank Workbook to open a new spreadsheet.

3 Change the Ribbon Display Options to Show Tabs.

4 Using the Customize Quick Access Toolbar, click to add New, Quick Print,
and Spelling.

5 In the Tell me bar, type the word Color. Hover over Fill Color and choose
yellow. This will fill a cell with the color yellow.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 8/258
5/19/2020 Excel: What-if Analysis

6 Change the worksheet view to the Page Layout option.

7 When you're finished, your screen should look like this:

8 Change the Ribbon Display Options back to Show Tabs and Commands.

9 Close Excel and Don't Save changes.

Lesson 2: Understanding OneDrive

Introduction
Many of the features in Microso O ice are geared toward saving and sharing files
online. OneDrive is Microso ’s online storage space that you can use to save, edit, and
share your documents and other files. You can access OneDrive from your computer,
smartphone, or any of the devices you use.

To get started with OneDrive, all you need to do is set up a free Microso account if you
don’t already have one.

If you don't already have a Microso account, you can go to the Creating a
Microso Account lesson in our Microso Account tutorial.

Once you have a Microso account, you'll be able to sign in to O ice. Just click Sign in in
the upper-right corner of the Excel window.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 9/258
5/19/2020 Excel: What-if Analysis

Benefits of using OneDrive

Once you’re signed in to your Microso account, there are a few of the things you’ll be
able to do with OneDrive:

▶ Access your files anywhere: When you save your files to OneDrive, you’ll
be able to access them from any computer, tablet, or smartphone that has
an Internet connection. You'll also be able to create new documents from
OneDrive.

▶ Back up your files: Saving files to OneDrive gives them an extra layer of
protection. Even if something happens to your computer, OneDrive will
keep your files safe and accessible.

▶ Share files: It’s easy to share your OneDrive files with friends and
coworkers. You can choose whether they can edit or simply read files. This
option is great for collaboration because multiple people can edit a
document at the same time (this is also known as co-authoring).

Saving and opening files

When you’re signed in to your Microso account, OneDrive will appear as an option
whenever you save or open a file. You still have the option of saving files to your
computer. However, saving files to your OneDrive allows you to access them from any
other computer, and it also allows you to share files with friends and coworkers.

For example, when you click Save As, you can select either OneDrive or This PC as the
save location.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 10/258
5/19/2020 Excel: What-if Analysis

Lesson 3: Creating and Opening Workbooks

Introduction
Excel files are called workbooks. Whenever you start a new project in Excel, you'll need
to create a new workbook. There are several ways to start working with a workbook in
Excel. You can choose to create a new workbook—either with a blank workbook or a
predesigned template—or open an existing workbook.

Watch the video below to learn more about creating and opening
workbooks in Excel.

Excel: Creating and Opening Workbooks

About OneDrive
Whenever you're opening or saving a workbook, you'll have the option of using your
OneDrive, which is the online file storage service included with your Microso account.
To enable this option, you'll need to sign in to O ice. To learn more, visit our lesson on
Understanding OneDrive.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 11/258
5/19/2020 Excel: What-if Analysis

To create a new blank workbook:

1 Select the File tab. Backstage view will appear.

2 Select New, then click Blank workbook.

3 A new blank workbook will appear.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 12/258
5/19/2020 Excel: What-if Analysis

To open an existing workbook:

In addition to creating new workbooks, you'll o en need to open a workbook that was
previously saved. To learn more about saving workbooks, visit our lesson on Saving and
Sharing Workbooks.

1 Navigate to Backstage view, then click Open.

2 Select Computer, then click Browse. You can also choose OneDrive to
open files stored on your OneDrive.

3 The Open dialog box will appear. Locate and select your workbook, then
click Open.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 13/258
5/19/2020 Excel: What-if Analysis

If you've opened the desired workbook recently, you can browse your
Recent Workbooks rather than search for the file.

To pin a workbook:
If you frequently work with the same workbook, you can pin it to Backstage view for
faster access.

1 Navigate to Backstage view, then click Open. Your recently edited

workbooks will appear.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 14/258
5/19/2020 Excel: What-if Analysis

2 Hover your mouse over the workbook you want to pin. A pushpin icon will
appear next to the workbook. Click the pushpin icon.

3 The workbook will stay in Recent Workbooks. To unpin a workbook, simply

click the pushpin icon again.

Using templates

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 15/258
5/19/2020 Excel: What-if Analysis

A template is a predesigned spreadsheet you can use to create a new workbook

quickly. Templates o en include custom formatting and predefined formulas, so they
can save you a lot of time and e ort when starting a new project.

To create a new workbook from a template:

1 Click the File tab to access Backstage view.

2 Select New. Several templates will appear below the Blank workbook
option.

3 Select a template to review it.

4 A preview of the template will appear, along with additional information

on how the template can be used.

5 Click Create to use the selected template.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 16/258
5/19/2020 Excel: What-if Analysis

6 A new workbook will appear with the selected template.

You can also browse templates by category or use the search bar to find
something more specific.

It's important to note that not all templates are created by Microso . Many
are created by third-party providers and even individual users, so some
templates may work better than others.

Compatibility Mode
Sometimes you may need to work with workbooks that were created in earlier versions
of Microso Excel, like Excel 2010 or Excel 2007. When you open these types of
workbooks, they will appear in Compatibility Mode.

Compatibility Mode disables certain features, so you'll only be able to access

commands found in the program that was used to create the workbook. For example, if
you open a workbook created in Excel 2003, you can only use tabs and commands found
in Excel 2003.

In the image below, you can see that the workbook is in Compatibility Mode, which is
indicated at the top of the window to the right of the file name. This will disable some
Excel features, which will be grayed out on the Ribbon.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 17/258
5/19/2020 Excel: What-if Analysis

To exit Compatibility Mode, you'll need to convert the workbook to the current version
type. However, if you're collaborating with others who only have access to an earlier
version of Excel, it's best to leave the workbook in Compatibility Mode so the format will
not change.

To convert a workbook:
If you want access to the newer features, you can convert the spreadsheet to the current
file format.

Note that converting a file may cause some changes to the original layout
of the workbook.

1 Click the File tab to access Backstage view.

2 Locate and select the Convert command.

3 The Save As dialog box will appear. Select the location where you want to
save the workbook, enter a file name for the workbook, and click Save.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 18/258
5/19/2020 Excel: What-if Analysis

4 The workbook will be converted to the newest file type.

Challenge!
1 Open our practice workbook.

2 Notice that our workbook opens in Compatibility Mode. Convert the

workbook to the current file format. A dialog box will appear, asking if you
would like to close and reopen the file in order to see the new features.
Choose Yes.

3 In Backstage view, pin a file or folder.

Lesson 4: Saving and Sharing Workbooks

Introduction
Whenever you create a new workbook in Excel, you'll need to know how to save it in
order to access and edit it later. As with previous versions of Excel, you can save files
locally to your computer. You can also save a workbook to the cloud using OneDrive, as
well as export and share workbooks with others directly from Excel.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 19/258
5/19/2020 Excel: What-if Analysis

Watch the video below to learn more about saving and sharing workbooks
in Excel.

Excel: Saving and Sharing

About OneDrive
Whenever you're opening or saving a workbook, you'll have the option of using your
OneDrive, which is the online file storage service included with your Microso account.
To enable this option, you'll need to sign in to O ice. To learn more, visit our lesson on
Understanding OneDrive.

Save and Save As

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 20/258
5/19/2020 Excel: What-if Analysis

Excel o ers two ways to save a file: Save and Save As. These options work in similar
ways, with a few important di erences:

▶ Save: When you create or edit a workbook, you'll use the Save command to
save your changes. You'll use this command most of the time. When you
save a file, you'll only need to choose a file name and location the first time.
Then you can just click the Save command to save it with the same name
and location.

▶ Save As: You'll use this command to create a copy of a workbook while
keeping the original. When you use Save As, you'll need to choose a
di erent name and/or location for the copied version.

To save a workbook:
It's important to save your workbook whenever you start a new project or make
changes to an existing one. Saving early and o en can prevent your work from being
lost. You'll also need to pay close attention to where you save the workbook so it will be
easy to find later.

1 Locate and select the Save command on the Quick Access Toolbar.

2 If you're saving the file for the first time, the Save As pane will appear in
Backstage view.

3 You'll then need to choose where to save the file and give it a file name. To
save the workbook to your computer, select Computer, then click Browse.
You can also click OneDrive to save the file to your OneDrive.

4 The Save As dialog box will appear. Select the location where you want to
save the workbook.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 21/258
5/19/2020 Excel: What-if Analysis

5 Enter a file name for the workbook, then click Save.

6 The workbook will be saved. You can click the Save command again to save
your changes as you modify the workbook.

You can also access the Save command by pressing Ctrl+S on your
keyboard.

Using Save As to make a copy

If you want to save a di erent version of a workbook while keeping the original, you
can create a copy. For example, if you have a file named Sales Data, you could save it as
Sales Data 2 so you'll be able to edit the new file and still refer back to the original
version.

To do this, click the Save As command in Backstage view. Just like when saving a file for
the first time, you'll need to choose where to save the file and give it a new file name.

To change the default save location:

If you don't want to use OneDrive, you may be frustrated that OneDrive is selected as
the default location when saving. If you find it inconvenient to select Computer each
time, you can change the default save location so Computer is selected by default.

1 Click the File tab to access Backstage view.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 22/258
5/19/2020 Excel: What-if Analysis

2 Click Options.

3 The Excel Options dialog box will appear. Select Save, check the box next
to Save to Computer by default, then click OK. The default save location
will be changed.

AutoRecover

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 23/258
5/19/2020 Excel: What-if Analysis

Excel automatically saves your workbooks to a temporary folder while you are working
on them. If you forget to save your changes or if Excel crashes, you can restore the file
using AutoRecover.

To use AutoRecover:
1 Open Excel. If autosaved versions of a file are found, the Document
Recovery pane will appear.

2 Click to open an available file. The workbook will be recovered.

By default, Excel autosaves every 10 minutes. If you are editing a workbook

for less than 10 minutes, Excel may not create an autosaved version.

If you don't see the file you need, you can browse all autosaved files from
Backstage view. Just select the File tab, click Manage Workbook, then
choose Recover Unsaved Workbooks.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 24/258
5/19/2020 Excel: What-if Analysis

Exporting workbooks
By default, Excel workbooks are saved in the .xlsx file type. However, there may be times
when you need to use another file type, like a PDF or Excel 97-2003 workbook. It's
easy to export your workbook from Excel to a variety of file types.

To export a workbook as a PDF file:

Exporting your workbook as an Adobe Acrobat document, commonly known as a PDF
file, can be especially useful if you're sharing a workbook with someone who does not
have Excel. A PDF will make it possible for recipients to view but not edit the content of
your workbook.

1 Click the File tab to access Backstage view.

2 Click Export, then select Create PDF/XPS.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 25/258
5/19/2020 Excel: What-if Analysis

3 The Save As dialog box will appear. Select the location where you want to
export the workbook, enter a file name, then click Publish.

By default, Excel will only export the active worksheet. If you have multiple
worksheets and want to save all of them in the same PDF file, click Options
in the Save As dialog box. The Options dialog box will appear. Select Entire
workbook, then click OK.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 26/258
5/19/2020 Excel: What-if Analysis

Whenever you export a workbook as a PDF, you'll also need to consider how
your workbook data will appear on each page of the PDF, just like printing
a workbook. Visit our Page Layout and Printing lesson to learn more about
what to consider before exporting a workbook as a PDF.

To export a workbook to other file types:

You may also find it helpful to export your workbook to other file types, like an Excel 97-
2003 workbook if you need to share with people using an older version of Excel, or a
.CSV file if you need a plain-text version of your workbook.

1 Click the File tab to access Backstage view.

2 Click Export, then select Change File Type.

3 Select a common file type, then click Save As.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 27/258
5/19/2020 Excel: What-if Analysis

4 The Save As dialog box will appear. Select the location where you want to
export the workbook, enter a file name, then click Save.

You can also use the Save as type: drop-down menu in the Save As dialog
box to save workbooks in a variety of file types.

Sharing workbooks
Excel makes it easy to share and collaborate on workbooks using OneDrive. In the past,
if you wanted to share a file with someone, you could send it as an email attachment.
While convenient, this system also creates multiple versions of the same file, which can
be di icult to organize.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 28/258
5/19/2020 Excel: What-if Analysis

When you share a workbook from Excel, you're actually giving others access to the exact
same file. This lets you and the people you share with edit the same workbook without
having to keep track of multiple versions.

In order to share a workbook, it must first be saved to your OneDrive.

To share a workbook:
1 Click the File tab to access Backstage view, then click Share.

2 Excel will return to Normal view and open the Share panel on the right side
of the window. From here, you can invite people to share your document,
see a list of who has access to the document, and set whether they can edit
or only view the document.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 29/258
5/19/2020 Excel: What-if Analysis

Challenge!
1 Open our practice workbook.

2 Using the Save As option, create a copy of the workbook and name it
Saving Practice Challenge. You can save the copy to a folder on your
computer or to your OneDrive.

3 Export the workbook as a PDF file.

Lesson 5: Cell Basics

Introduction
Whenever you work with Excel, you'll enter information—or content—into cells. Cells
are the basic building blocks of a worksheet. You'll need to learn the basics of cells and

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 30/258
5/19/2020 Excel: What-if Analysis

cell content to calculate, analyze, and organize data in Excel.

Optional: Download our practice workbook.

Watch the video below to learn more about the basics of working with cells.

Excel: Cell Basics

Understanding cells
Every worksheet is made up of thousands of rectangles, which are called cells. A cell is
the intersection of a row and a column. In other words, it's where a row and column
meet.

Columns are identified by letters (A, B, C), while rows are identified by numbers (1, 2,
3). Each cell has its own name—or cell address—based on its column and row. In the
example below, the selected cell intersects column C and row 5, so the cell address is
C5.

Note that the cell address also appears in the Name box in the top-le corner, and that a
cell's column and row headings are highlighted when the cell is selected.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 31/258
5/19/2020 Excel: What-if Analysis

You can also select multiple cells at the same time. A group of cells is known as a cell
range. Rather than a single cell address, you will refer to a cell range using the cell
addresses of the first and last cells in the cell range, separated by a colon. For example,
a cell range that included cells A1, A2, A3, A4, and A5 would be written as A1:A5. Take a
look at the di erent cell ranges below:

▶ Cell range A1:A8

▶ Cell range A1:F1

▶ Cell range A1:F8

If the columns in your spreadsheet are labeled with numbers instead of

letters, you'll need to change the default reference style for Excel. Review
our Extra on What are Reference Styles? to learn how.

To select a cell:
To input or edit cell content, you'll first need to select the cell.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 32/258
5/19/2020 Excel: What-if Analysis

1 Click a cell to select it. In our example, we'll select cell D9.

2 A border will appear around the selected cell, and the column heading and
row heading will be highlighted. The cell will remain selected until you
click another cell in the worksheet.

You can also select cells using the arrow keys on your keyboard.

To select a cell range:

Sometimes you may want to select a larger group of cells, or a cell range.

1 Click and drag the mouse until all of the adjoining cells you want to select
are highlighted. In our example, we'll select the cell range B5:C18.

2 Release the mouse to select the desired cell range. The cells will remain
selected until you click another cell in the worksheet.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 33/258
5/19/2020 Excel: What-if Analysis

Cell content
Any information you enter into a spreadsheet will be stored in a cell. Each cell can
contain di erent types of content, including text, formatting, formulas, and functions.

▶ Text: Cells can contain text, such as letters, numbers, and dates.

▶ Formatting attributes: Cells can contain formatting attributes that

change the way letters, numbers, and dates are displayed. For example,
percentages can appear as 0.15 or 15%. You can even change a cell's text or
background color.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 34/258
5/19/2020 Excel: What-if Analysis

▶ Formulas and functions: Cells can contain formulas and functions that
calculate cell values. In our example, SUM(B2:B8) adds the value of each
cell in the cell range B2:B8 and displays the total in cell B9.

To insert content:
1 Click a cell to select it. In our example, we'll select cell F9.

2 Type something into the selected cell, then press Enter on your keyboard.
The content will appear in the cell and the formula bar. You can also input
and edit cell content in the formula bar.

To delete (or clear) cell content:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 35/258
5/19/2020 Excel: What-if Analysis

1 Select the cell(s) with content you want to delete. In our example, we'll
select the cell range A10:H10.

2 Select the Clear command on the Home tab, then click Clear Contents.

3 The cell contents will be deleted.

You can also use the Delete key on your keyboard to delete content from
multiple cells at once. The Backspace key will only delete content from
one cell at a time.

To delete cells:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 36/258
5/19/2020 Excel: What-if Analysis

There is an important di erence between deleting the content of a cell and deleting the
cell itself. If you delete the entire cell, the cells below it will shi to fill in the gaps and
replace the deleted cells.

1 Select the cell(s) you want to delete. In our example, we'll select A10:H10.

2 Select the Delete command from the Home tab on the Ribbon.

3 The cells below will shi up and fill in the gaps.

To copy and paste cell content:

Excel allows you to copy content that is already entered into your spreadsheet and
paste this content to other cells, which can save you time and e ort.

1 Select the cell(s) you want to copy. In our example, we'll select F9.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 37/258
5/19/2020 Excel: What-if Analysis

2 Click the Copy command on the Home tab, or press Ctrl+C on your
keyboard.

3 Select the cell(s) where you want to paste the content. In our example,
we'll select F12:F17. The copied cell(s) will have a dashed box around
them.

4 Click the Paste command on the Home tab, or press Ctrl+V on your
keyboard.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 38/258
5/19/2020 Excel: What-if Analysis

5 The content will be pasted into the selected cells.

To access additional paste options:

You can also access additional paste options, which are especially convenient when
working with cells that contain formulas or formatting. Just click the drop-down
arrow on the Paste command to see these options.

Instead of choosing commands from the Ribbon, you can access commands quickly by
right-clicking. Simply select the cell(s) you want to format, then right-click the mouse.
A drop-down menu will appear, where you'll find several commands that are also
located on the Ribbon.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 39/258
5/19/2020 Excel: What-if Analysis

To cut and paste cell content:

Unlike copying and pasting, which duplicates cell content, cutting allows you to move
content between cells.

1 Select the cell(s) you want to cut. In our example, we'll select G5:G6.

2 Right-click the mouse and select the Cut command. You can also use the
command on the Home tab, or press Ctrl+X on your keyboard.

3 Select the cells where you want to paste the content. In our example, we'll
select F10:F11. The cut cells will now have a dashed box around them.

4 Right-click the mouse and select the Paste command. You can also use the
command on the Home tab, or press Ctrl+V on your keyboard.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 40/258
5/19/2020 Excel: What-if Analysis

5 The cut content will be removed from the original cells and pasted into the
selected cells.

To drag and drop cells:

Instead of cutting, copying, and pasting, you can drag and drop cells to move their
contents.

1 Select the cell(s) you want to move. In our example, we'll select H4:H12.

2 Hover the mouse over the border of the selected cell(s) until the mouse
changes to a pointer with four arrows.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 41/258
5/19/2020 Excel: What-if Analysis

3 Click and drag the cells to the desired location. In our example, we'll move
them to G4:G12.

4 Release the mouse. The cells will be dropped in the selected location.

To use the fill handle:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 42/258
5/19/2020 Excel: What-if Analysis

If you're copying cell content to adjacent cells in the same row or column, the fill handle
is a good alternative to the copy and paste commands.

1 Select the cell(s) containing the content you want to use, then hover the
mouse over the lower-right corner of the cell so the fill handle appears.

2 Click and drag the fill handle until all of the cells you want to fill are
selected. In our example, we'll select G13:G17.

3 Release the mouse to fill the selected cells.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 43/258
5/19/2020 Excel: What-if Analysis

To continue a series with the fill handle:

The fill handle can also be used to continue a series. Whenever the content of a row or
column follows a sequential order, like numbers (1, 2, 3) or days (Monday, Tuesday,
Wednesday), the fill handle can guess what should come next in the series. In most
cases, you will need to select multiple cells before using the fill handle to help Excel
determine the series order. Let's take a look at an example:

1 Select the cell range that contains the series you want to continue. In our
example, we'll select E4:G4.

2 Click and drag the fill handle to continue the series.

3 Release the mouse. If Excel understood the series, it will be continued in the
selected cells. In our example, Excel added Part 4, Part 5, and Part 6 to
H4:J4.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 44/258
5/19/2020 Excel: What-if Analysis

You can also double-click the fill handle instead of clicking and dragging. This can be
useful with larger spreadsheets, where clicking and dragging may be awkward.

Watch the video below to see an example of double-clicking the fill handle.

Excel Quick Tip: A Faster Way to Use the Fill Handle

Challenge!
1 Open our practice workbook.

2 Select cell D6 and type hlee.

3 Clear the contents in row 14.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 45/258
5/19/2020 Excel: What-if Analysis

4 Delete column G.

5 Using either cut and paste or drag and drop, move the contents of row 18
to row 14.

6 Use the fill handle to put an X in cells F9:F17.

7 When you're finished, your workbook should look like this:

Lesson 6: Modifying Columns, Rows, and Cells

Introduction
By default, every row and column of a new workbook is set to the same height and
width. Excel allows you to modify column width and row height in di erent ways,
including wrapping text and merging cells.

Optional: Download our practice workbook.

Watch the video below to learn more about modifying columns, rows, and
cells.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 46/258
5/19/2020 Excel: What-if Analysis

Excel: Modifying Columns, Rows, and Cells

To modify column width:

In our example below, column C is too narrow to display all of the content in these cells.
We can make all of this content visible by changing the width of column C.

1 Position the mouse over the column line in the column heading so the
cursor becomes a double arrow.

2 Click and drag the mouse to increase or decrease the column width.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 47/258
5/19/2020 Excel: What-if Analysis

3 Release the mouse. The column width will be changed.

With numerical data, the cell will display pound signs (#######) if the
column is too narrow. Simply increase the column width to make the data
visible.

To AutoFit column width:

The AutoFit feature will allow you to set a column's width to fit its content
automatically.

1 Position the mouse over the column line in the column heading so the
cursor becomes a double arrow.

2 Double-click the mouse. The column width will be changed automatically

to fit the content.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 48/258
5/19/2020 Excel: What-if Analysis

You can also AutoFit the width for several columns at the same time. Simply
select the columns you want to AutoFit, then select the AutoFit Column
Width command from the Format drop-down menu on the Home tab. This
method can also be used for row height.

To modify row height:

1 Position the cursor over the row line so the cursor becomes a double
arrow.

2 Click and drag the mouse to increase or decrease the row height.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 49/258
5/19/2020 Excel: What-if Analysis

3 Release the mouse. The height of the selected row will be changed.

To modify all rows or columns:

Instead of resizing rows and columns individually, you can modify the height and width
of every row and column at the same time. This method allows you to set a uniform size
for every row and column in your worksheet. In our example, we will set a uniform row
height.

1 Locate and click the Select All button just below the name box to select
every cell in the worksheet.

2 Position the mouse over a row line so the cursor becomes a double arrow.

3 Click and drag the mouse to increase or decrease the row height, then
release the mouse when you are satisfied. The row height will be changed
for the entire worksheet.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 50/258
5/19/2020 Excel: What-if Analysis

Inserting, deleting, moving, and

hiding
A er you've been working with a workbook for a while, you may find that you want to
insert new columns or rows, delete certain rows or columns, move them to a di erent
location in the worksheet, or even hide them.

To insert rows:
1 Select the row heading below where you want the new row to appear. In
this example, we want to insert a row between rows 4 and 5, so we'll select
row 5.

2 Click the Insert command on the Home tab.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 51/258
5/19/2020 Excel: What-if Analysis

3 The new row will appear above the selected row.

When inserting new rows, columns, or cells, you will see a paintbrush icon
next to the inserted cells. This button allows you to choose how Excel
formats these cells. By default, Excel formats inserted rows with the same
formatting as the cells in the row above. To access additional options, hover
your mouse over the icon, then click the drop-down arrow.

To insert columns:
1 Select the column heading to the right of where you want the new column
to appear. For example, if you want to insert a column between columns D
and E, select column E.

2 Click the Insert command on the Home tab.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 52/258
5/19/2020 Excel: What-if Analysis

3 The new column will appear to the le of the selected column.

When inserting rows and columns, make sure to select the entire row or
column by clicking the heading. If you select only a cell in the row or
column, the Insert command will only insert a new cell.

To delete a row or column:

It's easy to delete a row or column that you no longer need. In our example we'll delete a
row, but you can delete a column the same way.

1 Select the row you want to delete. In our example, we'll select row 9.

2 Click the Delete command on the Home tab.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 53/258
5/19/2020 Excel: What-if Analysis

3 The selected row will be deleted, and those around it will shi . In our
example, row 10 has moved up, so it's now row 9.

It's important to understand the di erence between deleting a row or

column and simply clearing its contents. If you want to remove the
content from a row or column without causing others to shi , right-click a
heading, then select Clear Contents from the drop-down menu.

To move a row or column:

Sometimes you may want to move a column or row to rearrange the content of your
worksheet. In our example we'll move a column, but you can move a row in the same
way.

1 Select the desired column heading for the column you want to move.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 54/258
5/19/2020 Excel: What-if Analysis

2 Click the Cut command on the Home tab, or press Ctrl+X on your keyboard.

3 Select the column heading to the right of where you want to move the
column. For example, if you want to move a column between columns E
and F, select column F.

4 Click the Insert command on the Home tab, then select Insert Cut Cells
from the drop-down menu.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 55/258
5/19/2020 Excel: What-if Analysis

5 The column will be moved to the selected location, and the columns
around it will shi .

You can also access the Cut and Insert commands by right-clicking the
mouse and selecting the desired commands from the drop-down menu.

To hide and unhide a row or column:

At times, you may want to compare certain rows or columns without changing the
organization of your worksheet. To do this, Excel allows you to hide rows and columns
as needed. In our example we'll hide a few columns, but you can hide rows in the same
way.

1 Select the columns you want to hide, right-click the mouse, then select
Hide from the formatting menu. In our example, we'll hide columns C, D,
and E.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 56/258
5/19/2020 Excel: What-if Analysis

2 The columns will be hidden. The green column line indicates the location
of the hidden columns.

3 To unhide the columns, select the columns on both sides of the hidden
columns. In our example, we'll select columns B and F. Then right-click the
mouse and select Unhide from the formatting menu.

4 The hidden columns will reappear.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 57/258
5/19/2020 Excel: What-if Analysis

Wrapping text and merging cells

Whenever you have too much cell content to be displayed in a single cell, you may
decide to wrap the text or merge the cell rather than resize a column. Wrapping the text
will automatically modify a cell's row height, allowing cell contents to be displayed on
multiple lines. Merging allows you to combine a cell with adjacent empty cells to create
one large cell.

To wrap text in cells:

1 Select the cells you want to wrap. In this example, we'll select the cells in
column C.

2 Click the Wrap Text command on the Home tab.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 58/258
5/19/2020 Excel: What-if Analysis

3 The text in the selected cells will be wrapped.

Click the Wrap Text command again to unwrap the text.

To merge cells using the Merge & Center command:

1 Select the cell range you want to merge. In our example, we'll select A1:F1.

2 Click the Merge & Center command on the Home tab. In our example, we'll
select the cell range A1:F1.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 59/258
5/19/2020 Excel: What-if Analysis

3 The selected cells will be merged, and the text will be centered.

To access additional merge options:

If you click the drop-down arrow next to the Merge & Center command on the Home
tab, the Merge drop-down menu will appear.

From here, you can choose to:

▶ Merge & Center: This merges the selected cells into one cell and centers
the text.

▶ Merge Across: This merges the selected cells into larger cells while keeping
each row separate.

▶ Merge Cells: This merges the selected cells into one cell but does not
center the text.

▶ Unmerge Cells: This unmerges selected cells.

Be careful when using this feature. If you merge multiple cells that all
contain data, Excel will keep only the contents of the upper-le cell and
discard everything else.

Centering across selection

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 60/258
5/19/2020 Excel: What-if Analysis

Merging can be useful for organizing your data, but it can also create problems later on.
For example, it can be di icult to move, copy, and paste content from merged cells. A
good alternative to merging is to Center Across Selection, which creates a similar e ect
without actually combining cells.

Watch the video below to learn why you should use Center Across Selection
instead of merging cells.

Excel Quick Tip: Why You Should Avoid Merging Cells

To use Center Across Selection:

1 Select the desired cell range. In our example, we'll select A1:F1. Note: If you
already merged these cells, you should unmerge them before continuing to
step 2.

2 Click the small arrow in the lower-right corner of the Alignment group on
the Home tab.

3 A dialog box will appear. Locate and select the Horizontal drop-down
menu, select Center Across Selection, then click OK.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 61/258
5/19/2020 Excel: What-if Analysis

4 The content will be centered across the selected cell range. As you can see,
this creates the same visual result as merging and centering, but it
preserves each cell within A1:F1.

Challenge!
1 Open our practice workbook.

2 Autofit Column Width for the entire workbook.

3 Modify the row height for rows 3 to 14 to 22.5 (30 pixels).

4 Delete row 10.

5 Insert a column to the le of column C. Type SECONDARY CONTACT in cell

C2.

6 Make sure cell C2 is still selected and choose Wrap Text.

7 Merge and Center cells A1:F1.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 62/258
5/19/2020 Excel: What-if Analysis

8 Hide the Billing Address and Phone columns.

9 When you're finished, your workbook should look something like this:

Lesson 7: Formatting Cells

Introduction
All cell content uses the same formatting by default, which can make it di icult to read
a workbook with a lot of information. Basic formatting can customize the look and feel
of your workbook, allowing you to draw attention to specific sections and making your
content easier to view and understand.

Optional: Download our practice workbook.

Watch the video below to learn more about formatting cells in Excel.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 63/258
5/19/2020 Excel: What-if Analysis

Excel: Formatting Cells

To change the font size:

1 Select the cell(s) you want to modify.

2 On the Home tab, click the drop-down arrow next to the Font Size
command, then select the desired font size. In our example, we will choose
24 to make the text larger.

3 The text will change to the selected font size.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 64/258
5/19/2020 Excel: What-if Analysis

You can also use the Increase Font Size and Decrease Font Size
commands or enter a custom font size using your keyboard.

To change the font:

By default, the font of each new workbook is set to Calibri. However, Excel provides
many other fonts you can use to customize your cell text. In the example below, we'll
format our title cell to help distinguish it from the rest of the worksheet.

1 Select the cell(s) you want to modify.

2 On the Home tab, click the drop-down arrow next to the Font command,
then select the desired font. In our example, we'll choose Century Gothic.

3 The text will change to the selected font.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 65/258
5/19/2020 Excel: What-if Analysis

When creating a workbook in the workplace, you'll want to select a font

that is easy to read. Along with Calibri, standard reading fonts include
Cambria, Times New Roman, and Arial.

To change the font color:

1 Select the cell(s) you want to modify.

2 On the Home tab, click the drop-down arrow next to the Font Color
command, then select the desired font color. In our example, we'll choose
Green.

3 The text will change to the selected font color.

Select More Colors at the bottom of the menu to access additional color
options. We've changed the font color to a bright pink.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 66/258
5/19/2020 Excel: What-if Analysis

To use the Bold, Italic, and Underline commands:

1 Select the cell(s) you want to modify.

2 Click the Bold (B), Italic (I), or Underline (U) command on the Home tab. In
our example, we'll make the selected cells bold.

3 The selected style will be applied to the text.

You can also press Ctrl+B on your keyboard to make selected text bold,
Ctrl+I to apply italics, and Ctrl+U to apply an underline.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 67/258
5/19/2020 Excel: What-if Analysis

Cell borders and fill colors

Cell borders and fill colors allow you to create clear and defined boundaries for
di erent sections of your worksheet. Below, we'll add cell borders and fill color to our
header cells to help distinguish them from the rest of the worksheet.

To add a fill color:

1 Select the cell(s) you want to modify.

2 On the Home tab, click the drop-down arrow next to the Fill Color
command, then select the fill color you want to use. In our example, we'll
choose a dark gray.

3 The selected fill color will appear in the selected cells. We've also changed
the font color to white to make it more readable with this dark fill color.

To add a border:
1 Select the cell(s) you want to modify.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 68/258
5/19/2020 Excel: What-if Analysis

2 On the Home tab, click the drop-down arrow next to the Borders
command, then select the border style you want to use. In our example,
we'll choose to display All Borders.

3 The selected border style will appear.

You can draw borders and change the line style and color of borders with
the Draw Borders tools at the bottom of the Borders drop-down menu.

Cell styles
Instead of formatting cells manually, you can use Excel's predesigned cell styles. Cell
styles are a quick way to include professional formatting for di erent parts of your
workbook, like titles and headers.

To apply a cell style:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 69/258
5/19/2020 Excel: What-if Analysis

In our example, we'll apply a new cell style to our existing title and header cells.

1 Select the cell(s) you want to modify.

2 Click the Cell Styles command on the Home tab, then choose the desired
style from the drop-down menu.

3 The selected cell style will appear.

Applying a cell style will replace any existing cell formatting except for text
alignment. You may not want to use cell styles if you've already added a lot
of formatting to your workbook.

Text alignment
By default, any text entered into your worksheet will be aligned to the bottom-le of a
cell, while any numbers will be aligned to the bottom-right. Changing the alignment of
your cell content allows you to choose how the content is displayed in any cell, which
can make your cell content easier to read.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 70/258
5/19/2020 Excel: What-if Analysis

Click the arrows in the slideshow below to learn more about the di erent
text alignment options.

Le Align: Aligns content to the le border of the cell

To change horizontal text alignment:

In our example below, we'll modify the alignment of our title cell to create a more
polished look and further distinguish it from the rest of the worksheet.

1 Select the cell(s) you want to modify.

2 Select one of the three horizontal alignment commands on the Home tab.
In our example, we'll choose Center Align.

3 The text will realign.

To change vertical text alignment:

1 Select the cell(s) you want to modify.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 71/258
5/19/2020 Excel: What-if Analysis

2 Select one of the three vertical alignment commands on the Home tab. In
our example, we'll choose Middle Align.

3 The text will realign.

You can apply both vertical and horizontal alignment settings to any cell.

The Format Painter

If you want to copy formatting from one cell to another, you can use the Format Painter
command on the Home tab. When you click the Format Painter, it will copy all of the
formatting from the selected cell. You can then click and drag over any cells where you
want to paste the formatting.

Watch the video below to learn two di erent ways to use the Format
Painter.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 72/258
5/19/2020 Excel: What-if Analysis

Excel Quick Tip: Two Ways to Use the Format Painter

Challenge!
1 Open our practice workbook.

2 Click the Challenge worksheet tab in the bottom-le of the workbook.

3 Change the cell style in cells A2:H2 to Accent 3.

4 Change the font size of row 1 to 36 and the font size for the rest of the rows
to 18.

5 Bold and underline the text in row 2.

6 Change the font of row 1 to a font of your choice.

7 Change the font of the rest of the rows to a di erent font of your choice.

8 Change the font color of row 1 to a color of your choice.

9 Select all of the text in the worksheet, then change the horizontal
alignment to center align and the vertical alignment to middle align.

10 When you're finished, your worksheet should look something like this:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 73/258
5/19/2020 Excel: What-if Analysis

Lesson 8: Understanding Number Formats

What are number formats?

Whenever you're working with a spreadsheet, it's a good idea to use appropriate
number formats for your data. Number formats tell your spreadsheet exactly what type
of data you're using, like percentages (%), currency ($), times, dates, and so on.

Watch the video below to learn more about number formats in Excel.

Excel: Understanding Number Formats

Why use number formats?

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 74/258
5/19/2020 Excel: What-if Analysis

Number formats not only make your spreadsheet easier to read, but they also make it
easier to use. When you apply a number format, you're telling your spreadsheet exactly
what types of values are stored in a cell. For example, the date format tells the
spreadsheet that you're entering specific calendar dates. This allows the spreadsheet
to better understand your data, which can help ensure that your data remains consistent
and that your formulas are calculated correctly.

If you don't need to use a specific number format, the spreadsheet will
usually apply the general number format by default. However, the general
format may apply some small formatting changes to your data.

Applying number formats

Just like other types of formatting, like changing the font color, you'll apply number
formats by selecting cells and choosing the desired formatting option. There are two
main ways to choose a number format:

▶ Go to the Home tab, click the Number Format drop-down menu in the
Number group, and select the desired format.

▶ Click one of the quick number-formatting commands below the drop-down

menu.

You can also select the desired cells and press Ctrl+1 on your keyboard to
access additional number-formatting options.

In this example, we've applied the Currency number format, which adds currency
symbols ($) and displays two decimal places for any numerical values.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 75/258
5/19/2020 Excel: What-if Analysis

If you select any cells with number formatting, you can see the actual value of the cell in
the formula bar. The spreadsheet will use this value for formulas and other calculations.

Using number formats correctly

There's more to number formatting than selecting cells and applying a format.
Spreadsheets can actually apply number formatting automatically based on the way
you enter data. This means you'll need to enter data in a way the program can
understand, then ensure that cells are using the proper number format. For example,
the image below shows how to use number formats correctly for dates, percentages,
and times:

Now that you know more about how number formats work, we'll look at a few number
formats in action.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 76/258
5/19/2020 Excel: What-if Analysis

Percentage formats
One of the most helpful number formats is the percentage (%) format. It displays values
as percentages, like 20% or 55%. This is especially helpful when calculating things like
the cost of sales tax or a tip. When you type a percent sign (%) a er a number, the
percentage number format will be be applied to that cell automatically.

As you may remember from math class, a percentage can also be written as a decimal.
So 15% is the same thing as 0.15, 7.5% is 0.075, 20% is 0.20, 55% is 0.55, and so on. You
can review this lesson from our Math tutorials to learn more about converting
percentages to decimals.

There are many times when percentage formatting will be useful. For example, in the
images below notice how the sales tax rate is formatted di erently for each
spreadsheet (5, 5%, and 0.05):

As you can see, the calculation in the spreadsheet on the le didn't work correctly.
Without the percentage number format, our spreadsheet thinks we want to multiply
$22.50 by 5, not 5%. And while the spreadsheet on the right still works without
percentage formatting, the spreadsheet in the middle is easier to read.

Date formats
Whenever you're working with dates, you'll want to use a date format to tell the
spreadsheet that you're referring to specific calendar dates, like July 15, 2014. Date
formats also allow you to work with a powerful set of date functions that use time and
date information to calculate an answer.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 77/258
5/19/2020 Excel: What-if Analysis

Spreadsheets don't understand information the same way a person would. For instance,
if you type October into a cell, the spreadsheet won't know you're entering a date so it
will treat it like any other text. Instead, when you enter a date, you'll need to use a
specific format your spreadsheet understands, like month/day/year (or
day/month/year depending on which country you're in). In the example below, we'll
type 10/12/2014 for October 12, 2014. Our spreadsheet will then automatically apply
the date number format for the cell.

Now that we have our date correctly formatted, we can do di erent things with this
data. For example, we could use the fill handle to continue the dates through the
column, so a di erent day appears in each cell:

If the date formatting isn't applied automatically, it means the spreadsheet did not
understand the data you entered. In the example below, we've typed March 15th. The
spreadsheet did not understand that we were referring to a date, so this cell is still using
the general number format.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 78/258
5/19/2020 Excel: What-if Analysis

On the other hand, if we type March 15 (without the "th"), the spreadsheet will
recognize it as a date. Because it doesn't include a year, the spreadsheet will
automatically add the current year so the date will have all of the necessary information.
We could also type the date several other ways, like 3/15, 3/15/2014, or March 15 2014,
and the spreadsheet would still recognize it as a date.

Try entering the dates below into a spreadsheet and see if the date format is applied
automatically:

▶ 10/12

▶ October

▶ October 12

▶ October 2016

▶ 10/12/2016

▶ October 12, 2016

▶ 2016

▶ October 12th

If you want to add the current date to a cell, you can use the Ctrl+; shortcut,
as shown in the video below.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 79/258
5/19/2020 Excel: What-if Analysis

Excel Quick Tip: Add the Current Date to a Cell

Other date formatting options

To access other date formatting options, select the Number Format drop-down menu
and choose More Number Formats. These are options to display the date di erently,
such as including the day of the week or omitting the year.

The Format Cells dialog box will appear. From here, you can choose the desired date
formatting option.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 80/258
5/19/2020 Excel: What-if Analysis

As you can see in the formula bar, a custom date format not only changes the actual date
in our cell but also the way it's displayed.

Number formatting tips

Here are a few tips for getting the best results with number formatting:

▶ Apply number formatting to an entire column: If you're planning to use

one column for a certain type of data, like dates or percentages, you may
find it easiest to select the entire column by clicking the column letter and
applying the desired number formatting. This way, any data you add to this
column in the future will already have the correct number format. Note that
the header row usually won't be a ected by number formatting.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 81/258
5/19/2020 Excel: What-if Analysis

▶ Double-check your values a er applying number formatting: If you

apply number formatting to existing data, you may have unexpected
results. For example, applying percentage (%) formatting to a cell with a
value of 5 will give you 500%, not 5%. In this case, you'd need to retype the
values correctly in each cell.

▶ If you reference a cell with number formatting in a formula, the spreadsheet

may automatically apply the same number formatting to the new cell. For
example, if you use a value with currency formatting in a formula, the
calculated value will also use the currency number format.

▶ If you want your data to appear exactly as entered, you'll need to use the
text number format. This format is especially good for numbers you don't
want to perform calculations with, like phone numbers, zip codes, or
numbers that begin with 0, like 02415. For best results, you may want to
apply the text number format before entering data into these cells.

Increase Decimal and Decrease Decimal

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 82/258
5/19/2020 Excel: What-if Analysis

The Increase Decimal and Decrease Decimal commands allow you to control how
many decimal places are displayed in a cell. These commands don't change the value of
the cell; instead, they display the value to a set number of decimal places.

Decreasing the decimal will display the value rounded to that decimal place, but the
actual value in the cell will still be displayed in the formula bar.

The Increase Decimal and Decrease Decimal commands don't work with
some number formats, like Date and Fraction.

Challenge!
1 Open our practice workbook.

2 In cell D2, type today's date and press Enter.

3 Click cell D2 and verify that it is using a Date number format. Try changing it
to a di erent date format (for example, Long Date).

4 In cell D2, use the Format Cells dialog box to choose the 14-Mar-12 date
format.

5 Change the sales tax rate in cell D8 to the Percentage format.

6 Apply the Currency format to all of column B.

7 In cell D8, use the Increase Decimal or Decrease Decimal command to

change the number of decimal places to one. It should now display 7.5%.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 83/258
5/19/2020 Excel: What-if Analysis

8 When you're finished, your spreadsheet should look like this:

Lesson 9: Working with Multiple Worksheets

Introduction
Every workbook contains at least one worksheet by default. When working with a large
amount of data, you can create multiple worksheets to help organize your workbook
and make it easier to find content. You can also group worksheets to quickly add
information to multiple worksheets at the same time.

Optional: Download our practice workbook.

Watch the video below to learn more about using multiple worksheets.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 84/258
5/19/2020 Excel: What-if Analysis

Excel: Working with Multiple Worksheets

To insert a new worksheet:

1 Locate and select the New sheet button near the bottom-right corner of the
Excel window.

2 A new blank worksheet will appear.

By default, any new workbook you create in Excel will contain one
worksheet, called Sheet1. To change the default number of worksheets,
navigate to Backstage view, click Options, then choose the desired
number of worksheets to include in each new workbook.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 85/258
5/19/2020 Excel: What-if Analysis

To copy a worksheet:
If you need to duplicate the content of one worksheet to another, Excel allows you to
copy an existing worksheet.

1 Right-click the worksheet you want to copy, then select Move or Copy from
the worksheet menu.

2 The Move or Copy dialog box will appear. Choose where the sheet will
appear in the Before sheet: field. In our example, we'll choose (move to
end) to place the worksheet to the right of the existing worksheet.

3 Check the box next to Create a copy, then click OK.

4 The worksheet will be copied. It will have the same title as the original
worksheet, as well as a version number. In our example, we copied the
November worksheet, so our new worksheet is named November (2). All
content from the November worksheet has also been copied to the new
worksheet.

You can also copy a worksheet to an entirely di erent workbook. You can
select any workbook that is currently open from the To book: drop-down

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 86/258
5/19/2020 Excel: What-if Analysis

menu.

To rename a worksheet:
1 Right-click the worksheet you want to rename, then select Rename from
the worksheet menu.

2 Type the desired name for the worksheet.

3 Click anywhere outside the worksheet tab, or press Enter on your

keyboard. The worksheet will be renamed.

To move a worksheet:
1 Click and drag the worksheet you want to move until a small black arrow
appears above the desired location.

2 Release the mouse. The worksheet will be moved.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 87/258
5/19/2020 Excel: What-if Analysis

To change the worksheet tab color:

1 Right-click the desired worksheet tab, and hover the mouse over Tab Color.
The Color menu will appear.

2 Select the desired color.

3 The worksheet tab color will be changed.

The worksheet tab color is considerably less noticeable when the

worksheet is selected. Select another worksheet to see how the color will
appear when the worksheet is not selected.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 88/258
5/19/2020 Excel: What-if Analysis

To delete a worksheet:
1 Right-click the worksheet you want to delete, then select Delete from the
worksheet menu.

2 The worksheet will be deleted from your workbook.

If you want to prevent specific worksheets from being edited or deleted,

you can protect them by right-clicking the desired worksheet and selecting
Protect Sheet from the worksheet menu.

Switching between worksheets

If you want to view a di erent worksheet, you can simply click the tab to switch to that
worksheet. However, with larger workbooks this can sometimes become tedious, as it

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 89/258
5/19/2020 Excel: What-if Analysis

may require scrolling through all of the tabs to find the one you want. Instead, you can
simply right-click the scroll arrows in the lower-le corner, as shown below.

A dialog box will appear with a list of all of the sheets in your workbook. You can then
double-click the sheet you want to jump to.

Watch the video below to see this shortcut in action.

Excel Quick Tip: Jump to Any Sheet with a Shortcut

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 90/258
5/19/2020 Excel: What-if Analysis

Grouping and ungrouping

worksheets
You can work with each worksheet individually, or you can work with multiple
worksheets at the same time. Several worksheets can be combined into a group. Any
changes made to one worksheet in a group will be made to every worksheet in that
group.

To group worksheets:
1 Select the first worksheet you want to include in the worksheet group.

2 Press and hold the Ctrl key on your keyboard. Select the next worksheet
you want in the group.

3 Continue to select worksheets until all of the worksheets you want to group
are selected, then release the Ctrl key. The worksheets are now grouped.

While worksheets are grouped, you can navigate to any worksheet within
the group. Any changes made to one worksheet will appear on every
worksheet in the group. However, if you select a worksheet that is not in
the group, all of your worksheets will become ungrouped.

To ungroup worksheets:
1 Right-click a worksheet in the group, then select Ungroup Sheets from the
worksheet menu.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 91/258
5/19/2020 Excel: What-if Analysis

2 The worksheets will be ungrouped. You can also click any worksheet not
included in the group to ungroup all worksheets.

Challenge!
1 Open our practice workbook.

2 Insert a new worksheet, and rename it Q1 Summary.

3 Move the Expenses Summary worksheet to the far right, then move the Q1
Summary worksheet so it is between March and April.

4 Create a copy of the Expenses Summary worksheet by right-clicking the

tab. Don't just copy and paste the content of the worksheet into a new
worksheet.

5 Change the color of the January tab to blue and the color of the February
tab to red.

6 Group the worksheets September, October, and November.

7 When you're finished, your workbook should look something like this:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 92/258
5/19/2020 Excel: What-if Analysis

Lesson 10: Using Find & Replace

Introduction
When working with a lot of data in Excel, it can be di icult and time consuming to locate
specific information. You can easily search your workbook using the Find feature, which
also allows you to modify content using the Replace feature.

Optional: Download our practice workbook.

Watch the video below to learn more about using Find and Replace.

Excel: Using Find and Replace

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 93/258
5/19/2020 Excel: What-if Analysis

To find cell content:

In our example, we'll use the Find command to locate a specific department in this list.

1 From the Home tab, click the Find and Select command, then select Find
from the drop-down menu.

2 The Find and Replace dialog box will appear. Enter the content you want
to find. In our example, we'll type the department's name.

3 Click Find Next. If the content is found, the cell containing that content will
be selected.

4 Click Find Next to find more instances or Find All to see every instance of
the search term.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 94/258
5/19/2020 Excel: What-if Analysis

5 When you are finished, click Close to exit the Find and Replace dialog box.

You can also access the Find command by pressing Ctrl+F on your
keyboard.

Click Options to see advanced search criteria in the Find and Replace dialog
box.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 95/258
5/19/2020 Excel: What-if Analysis

To replace cell content:

At times, you may discover that you've repeatedly made a mistake throughout your
workbook (like misspelling someone's name) or that you need to exchange a particular
word or phrase for another. You can use Excel's Find and Replace feature to make quick
revisions. In our example, we'll use Find and Replace to correct a list of department
names.

1 From the Home tab, click the Find & Select command, then select
Replace... from the drop-down menu.

2 The Find and Replace dialog box will appear. Type the text you want to find
in the Find what: field.

3 Type the text you want to replace it with in the Replace with: field, then
click Find Next.

4 If the content is found, the cell containing this content will be selected.

5 Review the text to make sure you want to replace it.

6 If you want to replace it, select one of the replace options. Choosing
Replace will replace individual instances, while Replace All will replace
every instance of the text throughout the workbook. In our example, we'll
choose this option to save time.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 96/258
5/19/2020 Excel: What-if Analysis

7 A dialog box will appear, confirming the number of replacements made.

Click OK to continue.

8 The selected cell content will be replaced.

9 When you're finished, click Close to exit the Find and Replace dialog box.

Generally, it's best to avoid using Replace All because it doesn't give you
the option of skipping anything you don't want to change. You should only
use this option if you're absolutely sure it won't replace anything you didn't
intend it to.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 97/258
5/19/2020 Excel: What-if Analysis

Challenge!
1 Open our practice workbook.

2 Click the Challenge tab in the bottom-le of the workbook.

3 Crystal Lewis got married and changed her last name to Taylor. Use Find
and Replace to change Crystal's last name from Lewis to Taylor. Be careful
to only change Crystal's last name!

4 Find and replace Bio with Biology. Be careful not to change the Biomedical
Engineering major!

5 Use Find and Replace All to replace the Physics major to Physical Science.

6 When you're finished, your worksheet should look like this:

Lesson 11: Checking Spelling

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 98/258
5/19/2020 Excel: What-if Analysis

Introduction
Before sharing a workbook, you'll want to make sure it doesn't include any spelling
errors. Fortunately, Excel includes a spell check tool you can use to make sure
everything in your workbook is spelled correctly.

If you've used the spell check feature in Microso Word, just be aware that the spell
check tool in Excel, while helpful, is not as powerful. For example, it won't check for
grammar issues or check spelling as you type.

Optional: Download our practice workbook.

To use spell check:

1 From the Review tab, click the Spelling command.

2 The Spelling dialog box will appear. For each spelling error in your
worksheet, it will try to o er suggestions for the correct spelling. Choose a
suggestion, then click Change to correct the error.

3 A dialog box will appear a er reviewing all spelling errors. Click OK to close
spell check.

If there are no appropriate suggestions, you can also enter the correct
spelling manually.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 99/258
5/19/2020 Excel: What-if Analysis

Ignoring spelling "errors"

Spell check isn't always correct. It will sometimes mark certain words as incorrect even
if they're spelled correctly. This o en happens with names, which may not be in the
dictionary. You can choose not to change a spelling "error" using one of the following
three options:

▶ Ignore Once: This will skip the word without changing it.

▶ Ignore All: This will skip the word without changing it and also skip all
other instances of the word in your worksheet.

▶ Add: This adds the word to the dictionary so it will never appear as an error
again. Make sure the word is spelled correctly before choosing this option.

Challenge!
1 Open our practice workbook.

2 Click the Challenge worksheet tab in the bottom-le of the workbook.

3 Run the spell check to correct any spelling errors in the workbook.

4 Correct the words co e and medum using the suggested spelling.

5 Ignore the spelling suggestion for the word Amanecer.

6 When you're finished, your worksheet should look like this:

7 Bonus Step! There is one error spell check didn't catch. Can you spot it?
Hint: It's in one of the item descriptions.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 100/258
5/19/2020 Excel: What-if Analysis

Lesson 12: Page Layout and Printing

Introduction
There may be times when you want to print a workbook to view and share your data
o line. Once you've chosen your page layout settings, it's easy to preview and print a
workbook from Excel using the Print pane.

Optional: Download our practice workbook.

Watch the video below to learn more about page layout and printing.

Excel: Page Layout and Printing

To access the Print pane:

1 Select the File tab. Backstage view will appear.

2 Select Print. The Print pane will appear.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 101/258
5/19/2020 Excel: What-if Analysis

Click the buttons in the interactive below to learn more about using the
Print pane.

To print a workbook:
1 Navigate to the Print pane, then select the desired printer.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 102/258
5/19/2020 Excel: What-if Analysis

2 Enter the number of copies you want to print.

3 Select any additional settings if needed (see above interactive).

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 103/258
5/19/2020 Excel: What-if Analysis

4 Click Print.

Choosing a print area

Before you print an Excel workbook, it's important to decide exactly what information
you want to print. For example, if you have multiple worksheets in your workbook, you
will need to decide if you want to print the entire workbook or only active worksheets.
There may also be times when you want to print only a selection of content from your
workbook.

To print active sheets:

Worksheets are considered active when selected.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 104/258
5/19/2020 Excel: What-if Analysis

1 Select the worksheet you want to print. To print multiple worksheets,

click the first worksheet, hold the Ctrl key on your keyboard, then click any
other worksheets you want to select.

2 Navigate to the Print pane.

3 Select Print Active Sheets from the Print Range drop-down menu.

4 Click the Print button.

To print the entire workbook:

1 Navigate to the Print pane.

2 Select Print Entire Workbook from the Print Range drop-down menu.

3 Click the Print button.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 105/258
5/19/2020 Excel: What-if Analysis

To print a selection:
In our example, we'll print the records for the top 40 salespeople on the Central
worksheet.

1 Select the cells you want to print.

2 Navigate to the Print pane.

3 Select Print Selection from the Print Range drop-down menu.

4 A preview of your selection will appear in the Preview pane.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 106/258
5/19/2020 Excel: What-if Analysis

5 Click the Print button to print the selection.

If you prefer, you can also set the print area in advance so you'll be able to
visualize which cells will be printed as you work in Excel. Simply select the
cells you want to print, click the Page Layout tab, select the Print Area
command, then choose Set Print Area. Keep in mind that if you ever need
to print the entire workbook, you'll need to clear the print area.

Adjusting content
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 107/258
5/19/2020 Excel: What-if Analysis

On occasion, you may need to make small adjustments from the Print pane to fit your
workbook content neatly onto a printed page. The Print pane includes several tools to
help fit and scale your content, including scaling and page margins.

To change page orientation:

Excel o ers two page orientation options: landscape and portrait. Landscape orients
the page horizontally, while portrait orients the page vertically. In our example, we'll
set the page orientation to landscape.

1 Navigate to the Print pane.

2 Select the desired orientation from the Page Orientation drop-down menu.
In our example, we'll select Landscape Orientation.

3 The new page orientation will be displayed in the Preview pane.

To fit content before printing:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 108/258
5/19/2020 Excel: What-if Analysis

If some of your content is being cut o by the printer, you can use scaling to fit your
workbook to the page automatically.

1 Navigate to the Print pane. In our example, we can see in the Preview pane
that our content will be cut o when printed.

2 Select the desired option from the Scaling drop-down menu. In our
example, we'll select Fit All Columns on One Page.

3 The worksheet will be condensed to fit onto a single page.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 109/258
5/19/2020 Excel: What-if Analysis

Keep in mind that worksheets will become more di icult to read as they are scaled
down, so you may not want to use this option when printing a worksheet with a lot of
information. In our example, we'll change the scaling setting back to No Scaling.

To include Print Titles:

If your worksheet uses title headings, it's important to include these headings on each
page of your printed worksheet. It would be di icult to read a printed workbook if the
title headings appeared only on the first page. The Print Titles command allows you to
select specific rows and columns to appear on each page.

1 Click the Page Layout tab on the Ribbon, then select the Print Titles
command.

2 The Page Setup dialog box will appear. From here, you can choose rows or
columns to repeat on each page. In our example, we'll repeat a row first.

3 Click the Collapse Dialog button next to the Rows to repeat at top: field.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 110/258
5/19/2020 Excel: What-if Analysis

4 The cursor will become a small selection arrow, and the Page Setup dialog
box will be collapsed. Select the row(s) you want to repeat at the top of
each printed page. In our example, we'll select row 1.

5 Row 1 will be added to the Rows to repeat at top: field. Click the Collapse
Dialog button again.

6 The Page Setup dialog box will expand. To repeat a column as well, use the
same process shown in steps 4 and 5. In our example, we've selected to
repeat row 1 and column A.

7 When you're satisfied with your selections, click OK.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 111/258
5/19/2020 Excel: What-if Analysis

8 In our example, row 1 appears at the top of every page, and column A
appears at the le of every page.

To adjust page breaks:

1 Click the Page Break Preview command to change to Page Break view.

2 Vertical and horizontal blue dotted lines denote the page breaks. Click and
drag one of these lines to adjust the page break.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 112/258
5/19/2020 Excel: What-if Analysis

3 In our example, we've set the horizontal page break between rows 21 and
22.

4 In our example, all of the pages now show the same number of rows due to
the change in the page break.

To modify margins in the Preview pane:

A margin is the space between your content and the edge of the page. Sometimes you
may need to adjust the margins to make your data fit more comfortably. You can modify
page margins from the Print pane.

1 Navigate to the Print pane.

2 Select the desired margin size from the Page Margins drop-down menu. In
our example, we'll select Narrow.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 113/258
5/19/2020 Excel: What-if Analysis

3 The new page margins will be displayed in the Preview pane.

You can adjust the margins manually by clicking the Show Margins button
in the lower-right corner, then dragging the margin markers in the Preview
pane.

Challenge!
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 114/258
5/19/2020 Excel: What-if Analysis

1 Open our practice workbook.

2 Click the East Coast tab at the bottom of the workbook.

3 In the Page Layout tab, use the Print Titles feature to repeat row 1 at the
top and column A at the le .

4 Using the Page Break Preview command, move the break between rows 47
and 48 up so it's between rows 40 and 41.

5 In Backstage view, open the Print pane.

6 In the Print pane, change the orientation to Landscape.

7 Change the margins to Narrow.

8 Change the scaling to Fit All Columns on One Page.

9 When you're finished, your print preview should look like this:

Lesson 13: Intro to Formulas

Introduction
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 115/258
5/19/2020 Excel: What-if Analysis

One of the most powerful features in Excel is the ability to calculate numerical
information using formulas. Just like a calculator, Excel can add, subtract, multiply, and
divide. In this lesson, we'll show you how to use cell references to create simple
formulas.

Optional: Download our practice workbook.

Watch the video below to learn more about creating formulas in Excel.

Excel: Intro to Formulas

Mathematical operators
Excel uses standard operators for formulas: a plus sign for addition (+), minus sign for
subtraction (-), asterisk for multiplication (*), forward slash for division (/), and caret
(^) for exponents.

All formulas in Excel must begin with an equals sign (=). This is because the cell
contains, or is equal to, the formula and the value it calculates.

Understanding cell references

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 116/258
5/19/2020 Excel: What-if Analysis

While you can create simple formulas in Excel using numbers (for example, =2+2 or
=5*5), most of the time you will use cell addresses to create a formula. This is known as
making a cell reference. Using cell references will ensure that your formulas are always
accurate because you can change the value of referenced cells without having to rewrite
the formula.

In the formula below, cell A3 adds the values of cells A1 and A2 by making cell
references:

When you press Enter, the formula calculates and displays the answer in cell A3:

If the values in the referenced cells change, the formula automatically recalculates:

By combining a mathematical operator with cell references, you can create a variety of
simple formulas in Excel. Formulas can also include a combination of cell references and
numbers, as in the examples below:

To create a formula:
In our example below, we'll use a simple formula and cell references to calculate a
budget.

1 Select the cell that will contain the formula. In our example, we'll select cell
D12.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 117/258
5/19/2020 Excel: What-if Analysis

2 Type the equals sign (=). Notice how it appears in both the cell and the
formula bar.

3 Type the cell address of the cell you want to reference first in the formula:
cell D10 in our example. A blue border will appear around the referenced
cell.

4 Type the mathematical operator you want to use. In our example, we'll
type the addition sign (+).

5 Type the cell address of the cell you want to reference second in the
formula: cell D11 in our example. A red border will appear around the
referenced cell.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 118/258
5/19/2020 Excel: What-if Analysis

6 Press Enter on your keyboard. The formula will be calculated, and the
value will be displayed in the cell. If you select the cell again, notice that the
cell displays the result, while the formula bar displays the formula.

If the result of a formula is too large to be displayed in a cell, it may appear

as pound signs (#######) instead of a value. This means the column is not
wide enough to display the cell content. Simply increase the column
width to show the cell content.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your
worksheet without having to rewrite formulas. In the example below, we've modified
the value of cell D10 from $1,200 to $1,800. The formula in D12 will automatically
recalculate and display the new value in cell D12.

Excel will not always tell you if your formula contains an error, so it's up to
you to check all of your formulas. To learn how to do this, you can read the

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 119/258
5/19/2020 Excel: What-if Analysis

Double-Check Your Formulas lesson from our Excel Formulas tutorial.

To create a formula using the point-and-click method:

Instead of typing cell addresses manually, you can point and click the cells you want to
include in your formula. This method can save a lot of time and e ort when creating
formulas. In our example below, we'll create a formula to calculate the cost of ordering
several boxes of plastic silverware.

1 Select the cell that will contain the formula. In our example, we'll select cell
D4.

2 Type the equals sign (=).

3 Select the cell you want to reference first in the formula: cell B4 in our
example. The cell address will appear in the formula.

4 Type the mathematical operator you want to use. In our example, we'll
type the multiplication sign (*).

5 Select the cell you want to reference second in the formula: cell C4 in our
example. The cell address will appear in the formula.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 120/258
5/19/2020 Excel: What-if Analysis

6 Press Enter on your keyboard. The formula will be calculated, and the
value will be displayed in the cell.

Copying formulas with the fill handle

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot
of time and e ort if you need to perform the same calculation multiple times in a
worksheet. The fill handle is the small square at the bottom-right corner of the selected
cell(s).

1 Select the cell containing the formula you want to copy. Click and drag the
fill handle over the cells you want to fill.

2 A er you release the mouse, the formula will be copied to the selected cells.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 121/258
5/19/2020 Excel: What-if Analysis

To edit a formula:
Sometimes you may want to modify an existing formula. In the example below, we've
entered an incorrect cell address in our formula, so we'll need to correct it.

1 Select the cell containing the formula you want to edit. In our example,
we'll select cell D12.

2 Click the formula bar to edit the formula. You can also double-click the cell
to view and edit the formula directly within the cell.

3 A border will appear around any referenced cells. In our example, we'll
change the first part of the formula to reference cell D10 instead of cell D9.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 122/258
5/19/2020 Excel: What-if Analysis

4 When you're finished, press Enter on your keyboard or select the Enter
command in the formula bar.

5 The formula will be updated, and the new value will be displayed in the
cell.

If you change your mind, you can press the Esc key on your keyboard or
click the Cancel command in the formula bar to avoid accidentally making
changes to your formula.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 123/258
5/19/2020 Excel: What-if Analysis

To show all of the formulas in a spreadsheet, you can hold the Ctrl key and
press ` (grave accent). The grave accent key is usually located in the top-le
corner of the keyboard. You can press Ctrl+` again to switch back to the
normal view.

Challenge!
1 Open our practice workbook.

2 Click the Challenge tab in the bottom-le of the workbook.

3 Create a formula in cell D4 that multiplies the quantity in B4 by the price

per unit in cell C4.

4 Use the fill handle to copy the formula in cell D4 to cells D5:D7.

5 Change the price per unit for the fried plantains in cell C6 to $2.25. Notice
that the line total automatically changes as well.

6 Edit the formula for the total in cell D8 so it also adds cell D7.

7 When you're finished, your workbook should look like this:

Lesson 14: Creating More Complex Formulas

Introduction
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 124/258
5/19/2020 Excel: What-if Analysis

You may have experience working with formulas that contain only one operator, like
7+9. More complex formulas can contain several mathematical operators, like 5+2*8.
When there's more than one operation in a formula, the order of operations tells Excel
which operation to calculate first. To write formulas that will give you the correct
answer, you'll need to understand the order of operations.

Optional: Download our practice workbook.

Watch the video below to learn more about complex formulas.

Excel: Creating More Complex Formulas

The order of operations

Excel calculates formulas based on the following order of operations:

1 Operations enclosed in parentheses

2 Exponential calculations (3^2, for example)

3 Multiplication and division, whichever comes first

4 Addition and subtraction, whichever comes first

A mnemonic that can help you remember the order is PEMDAS, or Please
Excuse My Dear Aunt Sally.

Click the arrows in the slideshow below to learn how the order of
operations is used to calculate formulas in Excel.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 125/258
5/19/2020 Excel: What-if Analysis

While this formula may look complicated, we can use the order of operations step by step to find the
right answer.

Creating complex formulas

In the example below, we'll demonstrate how Excel uses the order of operations to solve
a more complex formula. Here, we want to calculate the cost of sales tax for a catering
invoice. To do this, we'll write our formula as =(D3+D4+D5)*0.075 in cell D6. This
formula will add the prices of our items, then multiply that value by the 7.5% tax rate
(which is written as 0.075) to calculate the answer.

Excel follows the order of operations and first adds the values inside the parentheses:
(45.80+68.70+159.60) = 274.10. It then multiplies that value by the tax rate:
274.10*0.075. The result will show that the sales tax is $20.56.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 126/258
5/19/2020 Excel: What-if Analysis

It's especially important to follow the order of operations when creating a

formula. Otherwise, Excel won't calculate the results accurately. In our
example, if the parentheses are not included, the multiplication is
calculated first and the result is incorrect. Parentheses are o en the best
way to define which calculations will be performed first in Excel.

To create a complex formula using the order of

operations:
In the example below, we'll use cell references along with numerical values to create a
complex formula that will calculate the subtotal for a catering invoice. The formula will
calculate the cost of each menu item first, then add these values.

1 Select the cell that will contain the formula. In our example, we'll select cell
C5.

2 Enter your formula. In our example, we'll type =B3*C3+B4*C4. This

formula will follow the order of operations, first performing the
multiplication: 2.79*35 = 97.65 and 2.29*20 = 45.80. It then will add these
values to calculate the total: 97.65+45.80.

3 Double-check your formula for accuracy, then press Enter on your

keyboard. The formula will calculate and display the result. In our example,
the result shows that the subtotal for the order is $143.45.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 127/258
5/19/2020 Excel: What-if Analysis

You can add parentheses to any equation to make it easier to read. While it
won't change the result of the formula in this example, we could enclose
the multiplication operations within parentheses to clarify that they will be
calculated before the addition.

Excel will not always tell you if your formula contains an error, so it's up to
you to check all of your formulas. To learn how to do this, you can read the
Double-Check Your Formulas lesson from our Excel Formulas tutorial.

Challenge!
For this challenge, you will work with another invoice like the one in our example. In the
invoice, you will find the amount of tax for the order, the order's total, and the order's
total if you were given a 10% discount.

1 Open our practice workbook.

2 Click the Challenge worksheet tab in the bottom-le of the workbook.

3 In cell D7, create a formula that calculates the tax for the invoice. Use a
sales tax rate of 7.5%.

4 In cell D8, create a formula that finds the total for the order. In other words,
this formula should add cells D3:D7.

5 In cell D9 create a formula that calculates the total a er a 10% discount. If

you need help understanding how to take a percentage o of a total, review
our lesson on Discounts, Markdowns, and Sales.

6 When you're finished, your spreadsheet should look like this:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 128/258
5/19/2020 Excel: What-if Analysis

Lesson 15: Relative and Absolute Cell References

Introduction
There are two types of cell references: relative and absolute. Relative and absolute
references behave di erently when copied and filled to other cells. Relative references
change when a formula is copied to another cell. Absolute references, on the other
hand, remain constant no matter where they are copied.

Optional: Download our practice workbook.

Watch the video below to learn more about cell references.

Excel: Relative and Absolute Cell References

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 129/258
5/19/2020 Excel: What-if Analysis

Relative references
By default, all cell references are relative references. When copied across multiple cells,
they change based on the relative position of rows and columns. For example, if you
copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative
references are especially convenient whenever you need to repeat the same calculation
across multiple rows or columns.

To create and copy a formula using relative references:

In the following example, we want to create a formula that will multiply each item's
price by the quantity. Instead of creating a new formula for each row, we can create a
single formula in cell D4 and then copy it to the other rows. We'll use relative references
so the formula calculates the total for each item correctly.

1 Select the cell that will contain the formula. In our example, we'll select cell
D4.

2 Enter the formula to calculate the desired value. In our example, we'll type
=B4*C4.

3 Press Enter on your keyboard. The formula will be calculated, and the
result will be displayed in the cell.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 130/258
5/19/2020 Excel: What-if Analysis

4 Locate the fill handle in the bottom-right corner of the desired cell. In our
example, we'll locate the fill handle for cell D4.

5 Click and drag the fill handle over the cells you want to fill. In our example,
we'll select cells D5:D13.

6 Release the mouse. The formula will be copied to the selected cells with
relative references, displaying the result in each cell.

You can double-click the filled cells to check their formulas for accuracy.
The relative cell references should be di erent for each cell, depending on
their rows.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 131/258
5/19/2020 Excel: What-if Analysis

Absolute references
There may be a time when you don't want a cell reference to change when copied to
other cells. Unlike relative references, absolute references do not change when copied
or filled. You can use an absolute reference to keep a row and/or column constant.

An absolute reference is designated in a formula by the addition of a dollar sign ($). It

can precede the column reference, the row reference, or both.

You will generally use the $A$2 format when creating formulas that contain
absolute references. The other two formats are used much less frequently.

When writing a formula, you can press the F4 key on your keyboard to switch between
relative and absolute cell references, as shown in the video below. This is an easy way to
quickly insert an absolute reference.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 132/258
5/19/2020 Excel: What-if Analysis

Excel Quick Tip: Absolute References with the F4 Key

To create and copy a formula using absolute references:

In the example below, we'll use cell E2 (which contains the tax rate of 7.5%) to calculate
the sales tax for each item in column D. To make sure the reference to the tax rate stays
constant—even when the formula is copied and filled to other cells—we'll need to make
cell $E$2 an absolute reference.

1 Select the cell that will contain the formula. In our example, we'll select cell
D4.

2 Enter the formula to calculate the desired value. In our example, we'll type
=(B4*C4)*$E$2, making $E$2 an absolute reference.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 133/258
5/19/2020 Excel: What-if Analysis

3 Press Enter on your keyboard. The formula will calculate, and the result will
display in the cell.

4 Locate the fill handle in the bottom-right corner of the desired cell. In our
example, we'll locate the fill handle for cell D4.

5 Click and drag the fill handle over the cells you want to fill (cells D5:D13 in
our example).

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 134/258
5/19/2020 Excel: What-if Analysis

6 Release the mouse. The formula will be copied to the selected cells with an
absolute reference, and the values will be calculated in each cell.

You can double-click the filled cells to check their formulas for accuracy.
The absolute reference should be the same for each cell, while the other
references are relative to the cell's row.

Be sure to include the dollar sign ($) whenever you're making an absolute
reference across multiple cells. The dollar signs were omitted in the
example below. This caused Excel to interpret it as a relative reference,
producing an incorrect result when copied to other cells.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 135/258
5/19/2020 Excel: What-if Analysis

Using cell references with multiple

worksheets
Excel allows you to refer to any cell on any worksheet, which can be especially helpful if
you want to reference a specific value from one worksheet to another. To do this, you'll
simply need to begin the cell reference with the worksheet name followed by an
exclamation point (!). For example, if you wanted to reference cell A1 on Sheet1, its cell
reference would be Sheet1!A1.

Note that if a worksheet name contains a space, you'll need to include

single quotation marks (' ') around the name. For example, if you wanted
to reference cell A1 on a worksheet named July Budget, its cell reference
would be 'July Budget'!A1.

To reference cells across worksheets:

In our example below, we'll refer to a cell with a calculated value between two
worksheets. This will allow us to use the exact same value on two di erent worksheets
without rewriting the formula or copying data.

1 Locate the cell you want to reference, and note its worksheet. In our
example, we want to reference cell E14 on the Menu Order worksheet.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 136/258
5/19/2020 Excel: What-if Analysis

2 Navigate to the desired worksheet. In our example, we'll select the

Catering Invoice worksheet.

3 Locate and select the cell where you want the value to appear. In our
example, we'll select cell C4.

4 Type the equals sign (=), the sheet name followed by an exclamation
point (!), and the cell address. In our example, we'll type ='Menu
Order'!E14.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 137/258
5/19/2020 Excel: What-if Analysis

5 Press Enter on your keyboard. The value of the referenced cell will appear.
Now, if the value of cell E14 changes on the Menu Order worksheet, it will be
updated automatically on the Catering Invoice worksheet.

If you rename your worksheet at a later point, the cell reference will be
updated automatically to reflect the new worksheet name.

If you enter a worksheet name incorrectly, the #REF! error will appear in the
cell. In our example below, we've mistyped the name of the worksheet. To
edit, ignore, or investigate the error, click the Error button beside the cell
and choose an option from the menu.

Challenge!
1 Open our practice workbook.

2 Click the Paper Goods tab in the bottom-le of the workbook.

3 In cell D4, enter a formula that multiplies the unit price in B4, the quantity
in C4, and the tax rate in E2. Make sure to use an absolute cell reference for
the tax rate because it will be the same in every cell.

4 Use the fill handle to copy the formula you just created to cells D5:D12.

5 Change the tax rate in cell E2 to 6.5%. Notice that all of your cells have
updated. When you're finished, your workbook should look like this:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 138/258
5/19/2020 Excel: What-if Analysis

6 Click the Catering Invoice tab.

7 Delete the value in cell C5 and replace it with a reference to the total cost of
the paper goods. Hint: The cost of the paper goods is in cell E13 on the
Paper Goods worksheet.

8 Use the same steps from above to calculate the sales tax for each item on
the Menu Order worksheet. The total cost in cell E14 should update. Then,
in cell C4 of the Catering Invoice worksheet, create a cell reference to the
total you just calculated. Note: If you used our practice workbook to follow
along during the lesson, you may have already completed this step.

9 When you're finished, the Catering Invoice worksheet should look

something like this:

Lesson 16: Functions

Introduction
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 139/258
5/19/2020 Excel: What-if Analysis

A function is a predefined formula that performs calculations using specific values in a

particular order. Excel includes many common functions that can be used to quickly find
the sum, average, count, maximum value, and minimum value for a range of cells. In
order to use functions correctly, you'll need to understand the di erent parts of a
function and how to create arguments to calculate values and cell references.

Optional: Download our practice workbook.

Watch the video below to learn more about working with functions.

Excel: Functions

The parts of a function

In order to work correctly, a function must be written a specific way, which is called the
syntax. The basic syntax for a function is the equals sign (=), the function name (SUM,
for example), and one or more arguments. Arguments contain the information you want
to calculate. The function in the example below would add the values of the cell range
A1:A20.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 140/258
5/19/2020 Excel: What-if Analysis

Working with arguments

Arguments can refer to both individual cells and cell ranges and must be enclosed
within parentheses. You can include one argument or multiple arguments, depending
on the syntax required for the function.

For example, the function =AVERAGE(B1:B9) would calculate the average of the values
in the cell range B1:B9. This function contains only one argument.

Multiple arguments must be separated by a comma. For example, the function

=SUM(A1:A3, C1:C2, E1) will add the values of all of the cells in the three arguments.

Creating a function
There are a variety of functions available in Excel. Here are some of the most common
functions you'll use:

▶ SUM: This function adds all of the values of the cells in the argument.

▶ AVERAGE: This function determines the average of the values included in

the argument. It calculates the sum of the cells and then divides that value
by the number of cells in the argument.

▶ COUNT: This function counts the number of cells with numerical data in the
argument. This function is useful for quickly counting items in a cell range.

▶ MAX: This function determines the highest cell value included in the
argument.

▶ MIN: This function determines the lowest cell value included in the
argument.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 141/258
5/19/2020 Excel: What-if Analysis

To create a function using the AutoSum command:

The AutoSum command allows you to automatically insert the most common functions
into your formula, including SUM, AVERAGE, COUNT, MAX, and MIN. In the example
below, we'll use the SUM function to calculate the total cost for a list of recently ordered
items.

1 Select the cell that will contain the function. In our example, we'll select cell
D13.

2 In the Editing group on the Home tab, click the arrow next to the AutoSum
command. Next, choose the desired function from the drop-down menu.
In our example, we'll select Sum.

3 Excel will place the function in the cell and automatically select a cell
range for the argument. In our example, cells D3:D12 were selected
automatically; their values will be added to calculate the total cost. If Excel
selects the wrong cell range, you can manually enter the desired cells into
the argument.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 142/258
5/19/2020 Excel: What-if Analysis

4 Press Enter on your keyboard. The function will be calculated, and the
result will appear in the cell. In our example, the sum of D3:D12 is $765.29.

The AutoSum command can also be accessed from the Formulas tab on
the Ribbon.

You can also use the Alt+= keyboard shortcut instead of the AutoSum command. To use
this shortcut, hold down the Alt key and then press the equals sign.

Watch the video below to see this shortcut in action.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 143/258
5/19/2020 Excel: What-if Analysis

Excel Quick Tip: A Shortcut to AutoSum

To enter a function manually:

If you already know the function name, you can easily type it yourself. In the example
below (a tally of cookie sales), we'll use the AVERAGE function to calculate the average
number of units sold by each troop.

1 Select the cell that will contain the function. In our example, we'll select cell
C10.

2 Type the equals sign (=), then enter the desired function name. You can
also select the desired function from the list of suggested functions that
appears below the cell as you type. In our example, we'll type =AVERAGE.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 144/258
5/19/2020 Excel: What-if Analysis

3 Enter the cell range for the argument inside parentheses. In our example,
we'll type (C3:C9). This formula will add the values of cells C3:C9, then
divide that value by the total number of values in the range.

4 Press Enter on your keyboard. The function will be calculated, and the
result will appear in the cell. In our example, the average number of units
sold by each troop is 849.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 145/258
5/19/2020 Excel: What-if Analysis

Excel will not always tell you if your formula contains an error, so it's up to
you to check all of your formulas. To learn how to do this, read the Double-
Check Your Formulas lesson from our Excel Formulas tutorial.

The Function Library

While there are hundreds of functions in Excel, the ones you'll use the most will depend
on the type of data your workbooks contain. There's no need to learn every single
function, but exploring some of the di erent types of functions will help as you create
new projects. You can even use the Function Library on the Formulas tab to browse
functions by category, including Financial, Logical, Text, and Date & Time.

To access the Function Library, select the Formulas tab on the Ribbon. Look for the
Function Library group.

Click the buttons in the interactive below to learn more about the di erent
types of functions in Excel.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 146/258
5/19/2020 Excel: What-if Analysis

To insert a function from the Function Library:

In the example below, we'll use the COUNTA function to count the total number of items
in the Items column. Unlike COUNT, COUNTA can be used to tally cells that contain data
of any kind, not just numerical data.

1 Select the cell that will contain the function. In our example, we'll select cell
B17.

2 Click the Formulas tab on the Ribbon to access the Function Library.

3 From the Function Library group, select the desired function category. In
our example, we'll choose More Functions, then hover the mouse over
Statistical.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 147/258
5/19/2020 Excel: What-if Analysis

4 Select the desired function from the drop-down menu. In our example,
we'll select the COUNTA function, which will count the number of cells in
the Items column that are not empty.

5 The Function Arguments dialog box will appear. Select the Value1 field,
then enter or select the desired cells. In our example, we'll enter the cell
range A3:A12. You can continue to add arguments in the Value2 field, but in
this case we only want to count the number of cells in the cell range A3:A12.

6 When you're satisfied, click OK.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 148/258
5/19/2020 Excel: What-if Analysis

7 The function will be calculated, and the result will appear in the cell. In our
example, the result shows that 10 items were ordered.

The Insert Function command

While the Function Library is a great place to browse for functions, sometimes you may
prefer to search for one instead. You can do so using the Insert Function command. It
may take some trial and error depending on the type of function you're looking for, but
with practice the Insert Function command can be a powerful way to find a function
quickly.

To use the Insert Function command:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 149/258
5/19/2020 Excel: What-if Analysis

In the example below, we want to find a function that will calculate the number of
business days it took to receive items a er they were ordered. We'll use the dates in
columns E and F to calculate the delivery time in column G.

1 Select the cell that will contain the function. In our example, we'll select cell
G3.

2 Click the Formulas tab on the Ribbon, then click the Insert Function
command.

3 The Insert Function dialog box will appear.

4 Type a few keywords describing the calculation you want the function to
perform, then click Go. In our example, we'll type count days, but you can
also search by selecting a category from the drop-down list.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 150/258
5/19/2020 Excel: What-if Analysis

5 Review the results to find the desired function, then click OK. In our
example, we'll choose NETWORKDAYS, which will count the number of
business days between the ordered date and received date.

6 The Function Arguments dialog box will appear. From here, you'll be able
to enter or select the cells that will make up the arguments in the function.
In our example, we'll enter E3 in the Start_date field and F3 in the
End_date field.

7 When you're satisfied, click OK.

8 The function will be calculated, and the result will appear in the cell. In our
example, the result shows that it took four business days to receive the
order.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 151/258
5/19/2020 Excel: What-if Analysis

Like formulas, functions can be copied to adjacent cells. Simply select the
cell that contains the function, then click and drag the fill handle over the
cells you want to fill. The function will be copied, and values for those cells
will be calculated relative to their rows or columns.

To learn more:
If you're comfortable with basic functions, you may want to try a more advanced one
like VLOOKUP. Review our lesson on How to Use Excel's VLOOKUP Function for more
information.

To learn even more about working with functions, visit our Excel Formulas tutorial.

Challenge!
1 Open our practice workbook.

2 Click the Challenge tab in the bottom-le of the workbook.

3 In cell F3, insert a function to calculate the average of the four scores in
cells B3:E3.

4 Use the fill handle to copy your function in cell F3 to cells F4:F17.

5 In cell B18, use AutoSum to insert a function that calculates the lowest
score in cells B3:B17.

6 In cell B19, use the Function Library to insert a function that calculates the
median of the scores in cells B3:B17. Hint: You can find the median
function by going to More Functions > Statistical.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 152/258
5/19/2020 Excel: What-if Analysis

7 In cell B20, create a function to calculate the highest score in cells B3:B17.

8 Select cells B18:B20, then use the fill handle to copy all three functions you
just created to cells C18:F20.

9 When you're finished, your workbook should look like this:

Lesson 17: Basic Tips for Working with Data

Introduction
Excel workbooks are designed to store a lot of information. Whether you're working with
20 cells or 20,000, Excel has several features to help you organize your data and find
what you need. You can see some of the most useful features below. And be sure to
review the other lessons in this tutorial to get step-by-step instructions for each of these
features.

Freezing rows and columns

You may want to see certain rows or columns all the time in your worksheet, especially
header cells. By freezing rows or columns in place, you'll be able to scroll through your
content while continuing to view the frozen cells. In this example, we've frozen the top
two rows, which allows us to view the dates no matter where we scroll in the
spreadsheet.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 153/258
5/19/2020 Excel: What-if Analysis

Sorting data
You can quickly reorganize a worksheet by sorting your data. Content can be sorted
alphabetically, numerically, and in several other ways. For example, you can organize a
list of contact information by last name.

Filtering data
Filters can be used to narrow down the data in your worksheet, allowing you to view
only the information you need. In this example, we're filtering the worksheet to show
only rows that contain the words Laptop or Projector in column B.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 154/258
5/19/2020 Excel: What-if Analysis

Summarizing data
The Subtotal command allows you to quickly summarize data. In our example, we've
created a subtotal for each T-shirt size, which makes it easy to see how many we'll need
in each size.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 155/258
5/19/2020 Excel: What-if Analysis

Formatting data as a table

Just like regular formatting, tables can improve the look and feel of your workbook, but
they'll also help organize your content and make your data easier to use. For example,
tables have built-in sorting and filtering options. Excel also includes several predefined
table styles, allowing you to create tables quickly.

Visualizing data with charts

It can be di icult to interpret Excel workbooks that contain a lot of data. Charts allow
you to illustrate your workbook data graphically, which makes it easy to visualize
comparisons and trends.

Adding conditional formatting

Let's say you have a worksheet with thousands of rows of data. It would be extremely
di icult to see patterns and trends just from examining the raw information.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 156/258
5/19/2020 Excel: What-if Analysis

Conditional formatting allows you to automatically apply cell formatting—including

colors, icons, and data bars—to one or more cells based on the cell value.

Using Find and Replace

When working with a lot of data, it can be di icult and time consuming to locate specific
information. You can easily search your workbook using the Find feature, which also
allows you to modify content using the Replace feature.

Lesson 18: Freezing Panes and View Options

Introduction

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 157/258
5/19/2020 Excel: What-if Analysis

Whenever you're working with a lot of data, it can be di icult to compare information in
your workbook. Fortunately, Excel includes several tools that make it easier to view
content from di erent parts of your workbook at the same time, including the ability to
freeze panes and split your worksheet.

Optional: Download our practice workbook.

Watch the video below to learn more about freezing panes in Excel.

Excel: Freeze Panes and View Options

To freeze rows:
You may want to see certain rows or columns all the time in your worksheet, especially
header cells. By freezing rows or columns in place, you'll be able to scroll through your
content while continuing to view the frozen cells.

1 Select the row below the row(s) you want to freeze. In our example, we
want to freeze rows 1 and 2, so we'll select row 3.

2 On the View tab, select the Freeze Panes command, then choose Freeze
Panes from the drop-down menu.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 158/258
5/19/2020 Excel: What-if Analysis

3 The rows will be frozen in place, as indicated by the gray line. You can
scroll down the worksheet while continuing to view the frozen rows at the
top. In our example, we've scrolled down to row 18.

To freeze columns:
1 Select the column to the right of the column(s) you want to freeze. In our
example, we want to freeze column A, so we'll select column B.

2 On the View tab, select the Freeze Panes command, then choose Freeze
Panes from the drop-down menu.

3 The column will be frozen in place, as indicated by the gray line. You can
scroll across the worksheet while continuing to view the frozen column on
the le . In our example, we've scrolled across to column E.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 159/258
5/19/2020 Excel: What-if Analysis

If you only need to freeze the top row (row 1) or first column (column A) in
the worksheet, you can simply select Freeze Top Row or Freeze First
Column from the drop-down menu.

To unfreeze panes:
If you want to select a di erent view option, you may first need to reset the spreadsheet
by unfreezing panes. To unfreeze rows or columns, click the Freeze Panes command,
then select Unfreeze Panes from the drop-down menu.

Other view options

If your workbook contains a lot of content, it can sometimes be di icult to compare
di erent sections. Excel includes additional options to make your workbooks easier to
view and compare. For example, you can choose to open a new window for your
workbook or split a worksheet into separate panes.

To open a new window for the current workbook:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 160/258
5/19/2020 Excel: What-if Analysis

Excel allows you to open multiple windows for a single workbook at the same time. In
our example, we'll use this feature to compare two di erent worksheets from the same
workbook.

1 Click the View tab on the Ribbon, then select the New Window command.

2 A new window for the workbook will appear.

3 You can now compare di erent worksheets from the same workbook across
windows. In our example, we'll select the 2013 Sales Detailed View
worksheet to compare 2012 and 2013 sales.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 161/258
5/19/2020 Excel: What-if Analysis

If you have several windows open at the same time, you can use the
Arrange All command to rearrange them quickly.

To split a worksheet:
Sometimes you may want to compare di erent sections of the same workbook without
creating a new window. The Split command allows you to divide the worksheet into
multiple panes that scroll separately.

1 Select the cell where you want to split the worksheet. In our example, we'll
select cell D6.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 162/258
5/19/2020 Excel: What-if Analysis

2 Click the View tab on the Ribbon, then select the Split command.

3 The workbook will be split into di erent panes. You can scroll through each
pane separately using the scroll bars, allowing you to compare di erent
sections of the workbook.

4 A er creating a split, you can click and drag the vertical and horizontal
dividers to change the size of each section.

To remove the split, click the Split command again.

Challenge!
Within our example file, there is A LOT of sales data. For this challenge, we want to be
able to compare data for di erent years side by side. To do this:

1 Open our practice workbook.

2 Open a new window for your workbook.

3 Freeze First Column and use the horizontal scroll bar to look at sales from
2015.

4 Unfreeze the first column.

5 Select cell G17 and click Split to split the worksheet into multiple panes.
Hint: This should split the worksheet between rows 16 and 17 and columns
F and G.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 163/258
5/19/2020 Excel: What-if Analysis

6 Use the horizontal scroll bar in the bottom-right of the window to move the
worksheet so Column N, which contains data for January 2015, is next to
Column F.

7 Open a new window for your workbook and select the 2012-2013 Sales
tab.

8 Move your windows so they are side by side. Now you're able to compare
data for similar months from several di erent years. Your screen should
look something like this:

Lesson 19: Sorting Data

Introduction
As you add more content to a worksheet, organizing this information becomes
especially important. You can quickly reorganize a worksheet by sorting your data. For
example, you can organize a list of contact information by last name. Content can be
sorted alphabetically, numerically, and in several other ways.

Optional: Download our practice workbook.

Watch the video below to learn more about sorting data in Excel.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 164/258
5/19/2020 Excel: What-if Analysis

Excel: Sorting Data

Types of sorting
When sorting data, it's important to first decide if you want the sort to apply to the
entire worksheet or just a cell range.

▶ Sort sheet organizes all of the data in your worksheet by one column.
Related information across each row is kept together when the sort is
applied. In the example below, the Contact Name column (column A) has
been sorted to display the names in alphabetical order.

▶ Sort range sorts the data in a range of cells, which can be helpful when
working with a sheet that contains several tables. Sorting a range will not
a ect other content in the worksheet.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 165/258
5/19/2020 Excel: What-if Analysis

To sort a sheet:
In our example, we'll sort a T-shirt order form alphabetically by Last Name (column C).

1 Select a cell in the column you want to sort. In our example, we'll select cell
C2.

2 Select the Data tab on the Ribbon, then click the A-Z command to sort A to
Z, or the Z-A command to sort Z to A. In our example, we'll sort A to Z.

3 The worksheet will be sorted by the selected column. In our example, the
worksheet is now sorted by last name.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 166/258
5/19/2020 Excel: What-if Analysis

To sort a range:
In our example, we'll select a separate table in our T-shirt order form to sort the number
of shirts that were ordered on di erent dates.

1 Select the cell range you want to sort. In our example, we'll select cell
range G2:H6.

2 Select the Data tab on the Ribbon, then click the Sort command.

3 The Sort dialog box will appear. Choose the column you want to sort. In our
example, we want to sort the data by the number of T-shirt orders, so we'll
select Orders.

4 Decide the sorting order (either ascending or descending). In our example,

we'll use Largest to Smallest.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 167/258
5/19/2020 Excel: What-if Analysis

5 Once you're satisfied with your selection, click OK.

6 The cell range will be sorted by the selected column. In our example, the
Orders column will be sorted from highest to lowest. Notice that the other
content in the worksheet was not a ected by the sort.

If your data isn't sorting properly, double-check your cell values to make
sure they are entered into the worksheet correctly. Even a small typo could
cause problems when sorting a large worksheet. In the example below, we
forgot to include a hyphen in cell A18, causing our sort to be slightly
inaccurate.

Custom sorting
Sometimes you may find that the default sorting options can't sort data in the order you
need. Fortunately, Excel allows you to create a custom list to define your own sorting
order.

To create a custom sort:

In our example, we want to sort the worksheet by T-Shirt Size (column D). A regular sort
would organize the sizes alphabetically, which would be incorrect. Instead, we'll create a
custom list to sort from smallest to largest.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 168/258
5/19/2020 Excel: What-if Analysis

1 Select a cell in the column you want to sort. In our example, we'll select cell
D2.

2 Select the Data tab, then click the Sort command.

3 The Sort dialog box will appear. Select the column you want to sort, then
choose Custom List... from the Order field. In our example, we will choose
to sort by T-Shirt Size.

4 The Custom Lists dialog box will appear. Select NEW LIST from the Custom
Lists: box.

5 Type the items in the desired custom order in the List entries: box. In our
example, we want to sort our data by T-shirt size from smallest to largest,
so we'll type Small, Medium, Large, and X-Large, pressing Enter on the
keyboard a er each item.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 169/258
5/19/2020 Excel: What-if Analysis

6 Click Add to save the new sort order. The new list will be added to the
Custom lists: box. Make sure the new list is selected, then click OK.

7 The Custom Lists dialog box will close. Click OK in the Sort dialog box to
perform the custom sort.

8 The worksheet will be sorted by the custom order. In our example, the
worksheet is now organized by T-shirt size from smallest to largest.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 170/258
5/19/2020 Excel: What-if Analysis

Sorting levels
If you need more control over how your data is sorted, you can add multiple levels to
any sort. This allows you to sort your data by more than one column.

To add a level:
In our example below, we'll sort the worksheet by T-Shirt Size (Column D), then by
Homeroom # (column A).

1 Select a cell in the column you want to sort. In our example, we'll select cell
A2.

2 Click the Data tab, then select the Sort command.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 171/258
5/19/2020 Excel: What-if Analysis

3 The Sort dialog box will appear. Select the first column you want to sort. In
this example, we will sort by T-Shirt Size (column D) with the custom list
we previously created for the Order field.

4 Click Add Level to add another column to sort.

5 Select the next column you want to sort, then click OK. In our example,
we'll sort by Homeroom # (column A).

6 The worksheet will be sorted according to the selected order. In our

example, the orders are sorted by T-shirt size. Within each group of T-shirt
sizes, students are sorted by homeroom number.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 172/258
5/19/2020 Excel: What-if Analysis

If you need to change the order of a multilevel sort, it's easy to control
which column is sorted first. Simply select the desired column, then click
the Move Up or Move Down arrow to adjust its priority.

Challenge!
1 Open our practice workbook.

2 Click the Challenge tab in the bottom-le of the workbook.

3 For the main table, create a custom sort that sorts by Grade from Smallest
to Largest and then by Camper Name from A to Z.

4 Create a sort for the Additional Information section. Sort by Counselor

(Column H) from A to Z.

5 When you're finished, your workbook should look like this:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 173/258
5/19/2020 Excel: What-if Analysis

Lesson 20: Filtering Data

Introduction
If your worksheet contains a lot of content, it can be di icult to find information quickly.
Filters can be used to narrow down the data in your worksheet, allowing you to view
only the information you need.

Optional: Download our practice workbook.

Watch the video below to learn more about filtering data in Excel.

Excel: Filtering Data

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 174/258
5/19/2020 Excel: What-if Analysis

To filter data:
In our example, we'll apply a filter to an equipment log worksheet to display only the
laptops and projectors that are available for checkout.

1 In order for filtering to work correctly, your worksheet should include a

header row, which is used to identify the name of each column. In our
example, our worksheet is organized into di erent columns identified by
the header cells in row 1: ID#, Type, Equipment Detail, and so on.

2 Select the Data tab, then click the Filter command.

3 A drop-down arrow will appear in the header cell for each column.

4 Click the drop-down arrow for the column you want to filter. In our
example, we will filter column B to view only certain types of equipment.

5 The Filter menu will appear.

6 Uncheck the box next to Select All to quickly deselect all data.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 175/258
5/19/2020 Excel: What-if Analysis

7 Check the boxes next to the data you want to filter, then click OK. In this
example, we will check Laptop and Projector to view only these types of
equipment.

8 The data will be filtered, temporarily hiding any content that doesn't match
the criteria. In our example, only laptops and projectors are visible.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 176/258
5/19/2020 Excel: What-if Analysis

Filtering options can also be accessed from the Sort & Filter command on
the Home tab.

To apply multiple filters:

Filters are cumulative, which means you can apply multiple filters to help narrow
down your results. In this example, we've already filtered our worksheet to show laptops
and projectors, and we'd like to narrow it down further to only show laptops and
projectors that were checked out in August.

1 Click the drop-down arrow for the column you want to filter. In this
example, we will add a filter to column D to view information by date.

2 The Filter menu will appear.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 177/258
5/19/2020 Excel: What-if Analysis

3 Check or uncheck the boxes depending on the data you want to filter, then
click OK. In our example, we'll uncheck everything except for August.

4 The new filter will be applied. In our example, the worksheet is now filtered
to show only laptops and projectors that were checked out in August.

To clear a filter:
A er applying a filter, you may want to remove—or clear—it from your worksheet so
you'll be able to filter content in di erent ways.

1 Click the drop-down arrow for the filter you want to clear. In our example,
we'll clear the filter in column D.

2 The Filter menu will appear.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 178/258
5/19/2020 Excel: What-if Analysis

3 Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our
example, we'll select Clear Filter From "Checked Out".

4 The filter will be cleared from the column. The previously hidden data will
be displayed.

To remove all filters from your worksheet, click the Filter command on the
Data tab.

Advanced filtering
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 179/258
5/19/2020 Excel: What-if Analysis

If you need a filter for something specific, basic filtering may not give you enough
options. Fortunately, Excel includes several advanced filtering tools, including search,
text, date, and number filtering, which can narrow your results to help find exactly
what you need.

To filter with search:

Excel allows you to search for data that contains an exact phrase, number, date, and
more. In our example, we'll use this feature to show only Saris brand products in our
equipment log.

1 Select the Data tab, then click the Filter command. A drop-down arrow
will appear in the header cell for each column. Note: If you've already
added filters to your worksheet, you can skip this step.

2 Click the drop-down arrow for the column you want to filter. In our
example, we'll filter column C.

3 The Filter menu will appear. Enter a search term into the search box.
Search results will appear automatically below the Text Filters field as you
type. In our example, we'll type saris to find all Saris brand equipment.
When you're done, click OK.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 180/258
5/19/2020 Excel: What-if Analysis

4 The worksheet will be filtered according to your search term. In our

example, the worksheet is now filtered to show only Saris brand
equipment.

To use advanced text filters:

Advanced text filters can be used to display more specific information, like cells that
contain a certain number of characters or data that excludes a specific word or number.
In our example, we'd like to exclude any item containing the word laptop.

1 Select the Data tab, then click the Filter command. A drop-down arrow
will appear in the header cell for each column. Note: If you've already
added filters to your worksheet, you can skip this step.

2 Click the drop-down arrow for the column you want to filter. In our
example, we'll filter column C.

3 The Filter menu will appear. Hover the mouse over Text Filters, then select
the desired text filter from the drop-down menu. In our example, we'll
choose Does Not Contain... to view data that does not contain specific text.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 181/258
5/19/2020 Excel: What-if Analysis

4 The Custom AutoFilter dialog box will appear. Enter the desired text to the
right of the filter, then click OK. In our example, we'll type laptop to exclude
any items containing this word.

5 The data will be filtered by the selected text filter. In our example, our
worksheet now displays items that do not contain the word laptop.

To use advanced number filters:

Advanced number filters allow you to manipulate numbered data in di erent ways. In
this example, we'll display only certain types of equipment based on the range of ID

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 182/258
5/19/2020 Excel: What-if Analysis

numbers.

1 Select the Data tab on the Ribbon, then click the Filter command. A drop-
down arrow will appear in the header cell for each column. Note: If you've
already added filters to your worksheet, you can skip this step.

2 Click the drop-down arrow for the column you want to filter. In our
example, we'll filter column A to view only a certain range of ID numbers.

3 The Filter menu will appear. Hover the mouse over Number Filters, then
select the desired number filter from the drop-down menu. In our example,
we'll choose Between to view ID numbers between a specific number
range.

4 The Custom AutoFilter dialog box will appear. Enter the desired number(s)
to the right of each filter, then click OK. In our example, we want to filter for
ID numbers greater than or equal to 3000 but less than or equal to 6000,
which will display ID numbers in the 3000-6000 range.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 183/258
5/19/2020 Excel: What-if Analysis

5 The data will be filtered by the selected number filter. In our example, only
items with an ID number between 3000 and 6000 are visible.

To use advanced date filters:

Advanced date filters can be used to view information from a certain time period, such
as last year, next quarter, or between two dates. In this example, we'll use advanced date
filters to view only equipment that has been checked out between July 15 and August
15.

1 Select the Data tab, then click the Filter command. A drop-down arrow
will appear in the header cell for each column. Note: If you've already
added filters to your worksheet, you can skip this step.

2 Click the drop-down arrow for the column you want to filter. In our
example, we'll filter column D to view only a certain range of dates.

3 The Filter menu will appear. Hover the mouse over Date Filters, then
select the desired date filter from the drop-down menu. In our example,
we'll select Between... to view equipment that has been checked out
between July 15 and August 15.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 184/258
5/19/2020 Excel: What-if Analysis

4 The Custom AutoFilter dialog box will appear. Enter the desired date(s) to
the right of each filter, then click OK. In our example, we want to filter for
dates a er or equal to July 15, 2015, and before or equal to August 15,
2015, which will display a range between these dates.

5 The worksheet will be filtered by the selected date filter. In our example, we
can now see which items have been checked out between July 15 and
August 15.

Challenge!
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 185/258
5/19/2020 Excel: What-if Analysis

1 Open our practice workbook.

2 Click the Challenge tab in the bottom-le of the workbook.

3 Apply a filter to show only Electronics and Instruments.

4 Use the Search feature to filter item descriptions that contain the word
Sansei. A er you do this, you should have six entries showing.

5 Clear the Item Description filter.

6 Using a number filter, show loan amounts greater than or equal to $100.

7 Filter to show only items that have deadlines in 2016.

8 When you're finished, your workbook should look like this:

Lesson 21: Groups and Subtotals

Introduction
Worksheets with a lot of content can sometimes feel overwhelming and can even
become di icult to read. Fortunately, Excel can organize data into groups, allowing you
to easily show and hide di erent sections of your worksheet. You can also summarize
di erent groups using the Subtotal command and create an outline for your worksheet.

Optional: Download our practice workbook.

Watch the video below to learn more about groups and subtotals in Excel.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 186/258
5/19/2020 Excel: What-if Analysis

Excel: Groups and Subtotals

To group rows or columns:

1 Select the rows or columns you want to group. In this example, we'll select
columns B, C, and D.

2 Select the Data tab on the Ribbon, then click the Group command.

3 The selected rows or columns will be grouped. In our example, columns B,

C, and D are grouped.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 187/258
5/19/2020 Excel: What-if Analysis

To ungroup data, select the grouped rows or columns, then click the
Ungroup command.

To hide and show groups:

1 To hide a group, click the minus sign, also known as the Hide Detail button.

2 The group will be hidden. To show a hidden group, click the plus sign, also
known as the Show Detail button.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 188/258
5/19/2020 Excel: What-if Analysis

Creating subtotals
The Subtotal command allows you to automatically create groups and use common
functions like SUM, COUNT, and AVERAGE to help summarize your data. For example,
the Subtotal command could help to calculate the cost of o ice supplies by type from a
large inventory order. It will create a hierarchy of groups, known as an outline, to help
organize your worksheet.

Your data must be correctly sorted before using the Subtotal command, so
you may want to review our lesson on Sorting Data to learn more.

To create a subtotal:
In our example, we'll use the Subtotal command with a T-shirt order form to determine
how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This
will create an outline for our worksheet with a group for each T-shirt size and then
count the total number of shirts in each group.

1 First, sort your worksheet by the data you want to subtotal. In this example,
we'll create a subtotal for each T-shirt size, so our worksheet has been
sorted by T-shirt size from smallest to largest.

2 Select the Data tab, then click the Subtotal command.

3 The Subtotal dialog box will appear. Click the drop-down arrow for the At
each change in: field to select the column you want to subtotal. In our
example, we'll select T-Shirt Size.

4 Click the drop-down arrow for the Use function: field to select the function
you want to use. In our example, we'll select COUNT to count the number of
shirts ordered in each size.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 189/258
5/19/2020 Excel: What-if Analysis

5 In the Add subtotal to: field, select the column where you want the
calculated subtotal to appear. In our example, we'll select T-Shirt Size.
When you're satisfied with your selections, click OK.

6 The worksheet will be outlined into groups, and the subtotal will be listed
below each group. In our example, the data is now grouped by T-shirt size,
and the number of shirts ordered in that size appears below each group.

To view groups by level:

When you create subtotals, your worksheet it is divided into di erent levels. You can
switch among these levels to quickly control how much information is displayed in the
worksheet by clicking the Level buttons to the le of the worksheet. In our example,
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 190/258
5/19/2020 Excel: What-if Analysis

we'll switch among all three levels in our outline. While this example contains only three
levels, Excel can accommodate up to eight.

1 Click the lowest level to display the least detail. In our example, we'll select
level 1, which contains only the Grand Count, or total number of T-shirts
ordered.

2 Click the next level to expand the detail. In our example, we'll select level
2, which contains each subtotal row but hides all other data from the
worksheet.

3 Click the highest level to view and expand all of your worksheet data. In
our example, we'll select level 3.

You can also use the Show Detail and Hide Detail buttons to show and hide
the groups within the outline.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 191/258
5/19/2020 Excel: What-if Analysis

To remove subtotals:
Sometimes you may not want to keep subtotals in your worksheet, especially if you
want to reorganize data in di erent ways. If you no longer want to use subtotaling, you'll
need remove it from your worksheet.

1 Select the Data tab, then click the Subtotal command.

2 The Subtotal dialog box will appear. Click Remove All.

3 All worksheet data will be ungrouped, and the subtotals will be removed.

To remove all groups without deleting the subtotals, click the Ungroup
command drop-down arrow, then choose Clear Outline.

Challenge!
1 Open our practice workbook.

2 Click the Challenge tab in the bottom-le of the workbook.

3 Sort the workbook by Grade from smallest to largest.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 192/258
5/19/2020 Excel: What-if Analysis

4 Use the Subtotal command to group at each change in Grade. Use the SUM
function and add subtotals to Amount Raised.

5 Select level 2 so you only see the subtotals and grand total.

6 When you're finished, your workbook should look like this:

Lesson 22: Tables

Introduction
Once you've entered information into your worksheet, you may want to format your
data as a table. Just like regular formatting, tables can improve the look and feel of
your workbook, and they'll also help organize your content and make your data easier
to use. Excel includes several tools and predefined table styles, allowing you to create
tables quickly and easily.

Optional: Download our practice workbook.

Watch the video below to learn more about working with tables.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 193/258
5/19/2020 Excel: What-if Analysis

Excel: Tables

To format data as a table:

1 Select the cells you want to format as a table. In our example, we'll select
the cell range A2:D9.

2 From the Home tab, click the Format as Table command in the Styles
group.

3 Select a table style from the drop-down menu.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 194/258
5/19/2020 Excel: What-if Analysis

4 A dialog box will appear, confirming the selected cell range for the table.

5 If your table has headers, check the box next to My table has headers, then
click OK.

6 The cell range will be formatted in the selected table style.

Tables include filtering by default. You can filter your data at any time using
the drop-down arrows in the header cells. To learn more, review our lesson
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 195/258
5/19/2020 Excel: What-if Analysis

on Filtering Data.

Modifying tables
It's easy to modify the look and feel of any table a er adding it to a worksheet. Excel
includes several options for customizing tables, including adding rows or columns and
changing the table style.

To add rows or columns to a table:

If you need to fit more content into your table, you can modify the table size by
including additional rows and columns. There are two simple ways to change the table
size:

▶ Enter new content into any adjacent row or column. The row or column
will be roped into the table automatically.

▶ Click and drag the bottom-right corner of the table to create additional
rows or columns.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 196/258
5/19/2020 Excel: What-if Analysis

To change the table style:

1 Select any cell in your table, then click the Design tab.

2 Locate the Table Styles group, then click the More drop-down arrow to see
all available table styles.

3 Select the desired table style.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 197/258
5/19/2020 Excel: What-if Analysis

4 The table style will be applied.

To modify table style options:

You can turn various options on or o to change the appearance of any table. There are
several options: Header Row, Total Row, Banded Rows, First Column, Last Column,
Banded Columns, and Filter Button.

1 Select any cell in your table, then click the Design tab.

2 Check or uncheck the desired options in the Table Style Options group. In
our example, we'll check Total Row to automatically include a total for our
table.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 198/258
5/19/2020 Excel: What-if Analysis

3 The table style will be modified. In our example, a new row has been added
to the table with a formula that automatically calculates the total value of
the cells in column D.

Depending on the type of content you have—and the table style you've
chosen—these options can a ect your table's appearance in various ways.
You may need to experiment with a few options to find the exact style you
want.

To remove a table:
It's possible to remove a table from your workbook without losing any of your data.
However, this can cause issues with certain types of formatting, including colors, fonts,
and banded rows. Before using this option, be prepared to reformat your cells if
necessary.

1 Select any cell in your table, then click the Design tab.

2 Click the Convert to Range command in the Tools group.

3 A dialog box will appear. Click Yes.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 199/258
5/19/2020 Excel: What-if Analysis

4 The range will no longer be a table, but the cells will retain their data and
formatting.

To restart your formatting from scratch, click the Clear command on the
Home tab. Next, choose Clear Formats from the menu.

Challenge!
1 Open our practice workbook.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 200/258
5/19/2020 Excel: What-if Analysis

2 Click the Challenge tab in the bottom-le of the workbook.

3 Select cells A2:D9 and format as a table. Choose one of the light styles.

4 Insert a row between rows 4 and 5. In the row you just created, type
Empanadas: Banana and Nutella, with a unit price of $3.25 and a quantity
of 12.

5 Change the table style to Table Style Medium 10.

6 In Table Style Options, uncheck banded rows and check banded

columns.

7 When you're finished, your workbook should look like this:

Lesson 23: Charts

Introduction
It can be di icult to interpret Excel workbooks that contain a lot of data. Charts allow
you to illustrate your workbook data graphically, which makes it easy to visualize
comparisons and trends.

Optional: Download our practice workbook.

Watch the video below to learn more about charts.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 201/258
5/19/2020 Excel: What-if Analysis

Excel: Charts

Understanding charts
Excel has several types of charts, allowing you to choose the one that best fits your
data. To use charts e ectively, you'll need to understand how di erent charts are used.

Click the arrows in the slideshow below to learn more about the types of
charts in Excel.

Excel has a variety of chart types, each with its own advantages. Click the arrows to see some of the
di erent types of charts available in Excel.

In addition to chart types, you'll need to understand how to read a chart. Charts contain
several elements, or parts, that can help you interpret data.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 202/258
5/19/2020 Excel: What-if Analysis

Click the buttons in the interactive below to learn about the di erent parts
of a chart.

To insert a chart:
1 Select the cells you want to chart, including the column titles and row
labels. These cells will be the source data for the chart. In our example,
we'll select cells A1:F6.

2 From the Insert tab, click the desired Chart command. In our example,
we'll select Column.

3 Choose the desired chart type from the drop-down menu.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 203/258
5/19/2020 Excel: What-if Analysis

4 The Selected chart will be inserted into the worksheet.

If you're not sure which type of chart to use, the Recommended Charts
command will suggest several charts based on the source data.

Chart and layout style

A er inserting a chart, there are several things you may want to change about the way
your data is displayed. It's easy to edit a chart's layout and style from the Design tab.

▶ Excel allows you to add chart elements—including chart titles, legends,

and data labels—to make your chart easier to read. To add a chart element,
click the Add Chart Element command on the Design tab, then choose the
desired element from the drop-down menu.
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 204/258
5/19/2020 Excel: What-if Analysis

▶ To edit a chart element, like a chart title, simply double-click the

placeholder and begin typing.

▶ If you don't want to add chart elements individually, you can use one of
Excel's predefined layouts. Simply click the Quick Layout command, then
choose the desired layout from the drop-down menu.

▶ Excel also includes several chart styles, which allow you to quickly modify
the look and feel of your chart. To change the chart style, select the desired

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 205/258
5/19/2020 Excel: What-if Analysis

style from the Chart styles group. You can also click the drop-down arrow
on the right to see more styles.

You can also use the chart formatting shortcut buttons to quickly add chart
elements, change the chart style, and filter chart data.

Other chart options

There are many other ways to customize and organize your charts. For example, Excel
allows you to rearrange a chart's data, change the chart type, and even move the chart
to a di erent location in a workbook.

To switch row and column data:

Sometimes you may want to change the way charts group your data. For example, in the
chart below Book Sales data is grouped by genre, with columns for each month.
However, we could switch the rows and columns so the chart will group the data by
month, with columns for each genre. In both cases, the chart contains the same data—
it's just organized di erently.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 206/258
5/19/2020 Excel: What-if Analysis

1 Select the chart you want to modify.

2 From the Design tab, select the Switch Row/Column command.

3 The rows and columns will be switched. In our example, the data is now
grouped by month, with columns for each genre.

To change the chart type:

If you find that your data isn't working well in a certain chart, it's easy to switch to a new
chart type. In our example, we'll change our chart from a column chart to a line chart.

1 From the Design tab, click the Change Chart Type command.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 207/258
5/19/2020 Excel: What-if Analysis

2 The Change Chart Type dialog box will appear. Select a new chart type and
layout, then click OK. In our example, we'll choose a Line chart.

3 The selected chart type will appear. In our example, the line chart makes it
easier to see trends in sales data over time.

To move a chart:
Whenever you insert a new chart, it will appear as an object on the same worksheet that
contains its source data. You can easily move the chart to a new worksheet to help keep

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 208/258
5/19/2020 Excel: What-if Analysis

your data organized.

1 Select the chart you want to move.

2 Click the Design tab, then select the Move Chart command.

3 The Move Chart dialog box will appear. Select the desired location for the
chart. In our example, we'll choose to move it to a New sheet, which will
create a new worksheet.

4 Click OK.

5 The chart will appear in the selected location. In our example, the chart
now appears on a new worksheet.

Keeping charts up to date

By default, when you add more data to your spreadsheet, the chart may not include the
new data. To fix this, you can adjust the data range. Simply click the chart, and it will
highlight the data range in your spreadsheet. You can then click and drag the handle in
the lower-right corner to change the data range.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 209/258
5/19/2020 Excel: What-if Analysis

If you frequently add more data to your spreadsheet, it may become tedious to update
the data range. Luckily, there is an easier way. Simply format your source data as a table,
then create a chart based on that table. When you add more data below the table, it
will automatically be included in both the table and the chart, keeping everything
consistent and up to date.

Watch the video below to learn how to use tables to keep charts up to date.

Excel Quick Tip: How to Make Charts Auto Update

Challenge!
1 Open our practice workbook.

2 Click the Challenge tab in the bottom-le of the workbook.

3 Select cells A1:E6 and insert a 2D Clustered Column chart.

4 Change the chart title to September to December Sales.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 210/258
5/19/2020 Excel: What-if Analysis

5 Use the Switch Row/Column command. The columns should now be

grouped by month, with a di erent color for each salesperson.

6 Move the chart to a new sheet.

7 Change the chart type to line with markers.

8 Use the Quick Layout command to change the layout of the chart.

9 When you're finished, your workbook should look something like this:

Lesson 24: Conditional Formatting

Introduction
Let's say you have a worksheet with thousands of rows of data. It would be extremely
di icult to see patterns and trends just from examining the raw information. Similar to
charts and sparklines, conditional formatting provides a way to visualize data and
make worksheets easier to understand.

Optional: Download our practice workbook.

Watch the video below to learn more about conditional formatting in Excel.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 211/258
5/19/2020 Excel: What-if Analysis

Excel: Conditional Formatting

Understanding conditional formatting

Conditional formatting allows you to automatically apply formatting—such as colors,
icons, and data bars—to one or more cells based on the cell value. To do this, you'll
need to create a conditional formatting rule. For example, a conditional formatting
rule might be: If the value is less than $2000, color the cell red. By applying this rule,
you'd be able to quickly see which cells contain values less than $2000.

To create a conditional formatting rule:

In our example, we have a worksheet containing sales data, and we'd like to see which
salespeople are meeting their monthly sales goals. The sales goal is $4000 per month, so
we'll create a conditional formatting rule for any cells containing a value higher than
4000.

1 Select the desired cells for the conditional formatting rule.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 212/258
5/19/2020 Excel: What-if Analysis

2 From the Home tab, click the Conditional Formatting command. A drop-
down menu will appear.

3 Hover the mouse over the desired conditional formatting type, then select
the desired rule from the menu that appears. In our example, we want to
highlight cells that are greater than $4000.

4 A dialog box will appear. Enter the desired value(s) into the blank field. In
our example, we'll enter 4000 as our value.

5 Select a formatting style from the drop-down menu. In our example, we'll
choose Green Fill with Dark Green Text, then click OK.

6 The conditional formatting will be applied to the selected cells. In our

example, it's easy to see which salespeople reached the $4000 sales goal for
each month.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 213/258
5/19/2020 Excel: What-if Analysis

You can apply multiple conditional formatting rules to a cell range or

worksheet, allowing you to visualize di erent trends and patterns in your
data.

Conditional formatting presets

Excel has several predefined styles—or presets—you can use to quickly apply
conditional formatting to your data. They are grouped into three categories:

▶ Data Bars are horizontal bars added to each cell, much like a bar graph.

▶ Color Scales change the color of each cell based on its value. Each color
scale uses a two- or three-color gradient. For example, in the Green-

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 214/258
5/19/2020 Excel: What-if Analysis

Yellow-Red color scale, the highest values are green, the average values
are yellow, and the lowest values are red.

▶ Icon Sets add a specific icon to each cell based on its value.

To use preset conditional formatting:

1 Select the desired cells for the conditional formatting rule.

2 Click the Conditional Formatting command. A drop-down menu will

appear.

3 Hover the mouse over the desired preset, then choose a preset style from
the menu that appears.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 215/258
5/19/2020 Excel: What-if Analysis

4 The conditional formatting will be applied to the selected cells.

Removing conditional formatting

To remove conditional formatting:
1 Click the Conditional Formatting command. A drop-down menu will
appear.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 216/258
5/19/2020 Excel: What-if Analysis

2 Hover the mouse over Clear Rules, then choose which rules you want to
clear. In our example, we'll select Clear Rules from Entire Sheet to remove
all conditional formatting from the worksheet.

3 The conditional formatting will be removed.

Click Manage Rules to edit or delete individual rules. This is especially

useful if you've applied multiple rules to a worksheet.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 217/258
5/19/2020 Excel: What-if Analysis

Challenge!
1 Open our practice workbook.

2 Click the Challenge worksheet tab in the bottom-le of the workbook.

3 Select cells B3:J17.

4 Let's say you're the teacher and want to easily see all of the grades that are
below passing. Apply Conditional Formatting so it Highlights Cells
containing values Less Than 70 with a light red fill.

5 Now you want to see how the grades compare to each other. Under the
Conditional Formatting tab, select the Icon Set called 3 Symbols
(Circled). Hint: The names of the icon sets will appear when you hover over
them.

6 Your spreadsheet should look like this:

7 Using the Manage Rules feature, remove the light red fill, but keep the icon
set.

Lesson 25: Comments and Co-authoring

Introduction
There may be times when you're working on a workbook and find that you need the
help of others. Excel o ers two powerful features that allow you to work with others on
the same spreadsheet: comments and co-authoring.

Optional: Download our practice workbook.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 218/258
5/19/2020 Excel: What-if Analysis

Watch the video below to learn more about comments and co-authoring.

Excel: Comments and Co-authoring

The Track Changes feature can also be helpful to review changes before
making them permanent. It's still available in O ice 365, but it's now
hidden by default. You can learn more about Track Changes in our Excel
2016 lesson here.

Sharing a workbook with others

In order for others to collaborate on a workbook, you'll need to share it with them first. 

1 Click the Share button in the top-right corner.

2 Click the OneDrive option associated with your account in order to upload
the workbook.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 219/258
5/19/2020 Excel: What-if Analysis

3 The Share pane will appear on the right side of the screen. Type the email
address of the person you'd like to share the workbook with.

4 Select Can edit from the drop-down menu to allow this person to edit the
workbook.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 220/258
5/19/2020 Excel: What-if Analysis

5 Type a message if you want to include one, then click Share.

6 Your collaborators will now be able to access the workbook.

Comments
One way to collaborate on a workbook is through commenting. Sometimes you may
want to provide feedback or ask a question without editing the contents of a cell. You
can do this by adding a comment.

To add a comment:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 221/258
5/19/2020 Excel: What-if Analysis

1 Select the cell where you want the comment to appear. In our example,
we'll select cell D17.

2 From the Review tab, click the New Comment command.

3 A comment box will appear. Type your comment, then click anywhere
outside the box to close the comment.

4 The comment will be added to the cell, represented by a red triangle in the
top-right corner.

5 Select the cell again to view the comment.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 222/258
5/19/2020 Excel: What-if Analysis

To edit a comment:
1 Select the cell containing the comment you want to edit.

2 From the Review tab, click the Edit Comment command.

3 The comment box will appear. Edit the comment as desired, then click
anywhere outside the box to close the comment.

To show or hide comments:

1 From the Review tab, click the Show All Comments command to view
every comment in your worksheet at the same time.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 223/258
5/19/2020 Excel: What-if Analysis

2 All comments in the worksheet will appear. Click the Show All Comments
command again to hide them.

You can also choose to show and hide individual comments by selecting the
desired cell and clicking the Show/Hide Comment command.

To delete a comment:
1 Select the cell containing the comment you want to delete. In our example,
we'll select cell E13.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 224/258
5/19/2020 Excel: What-if Analysis

2 From the Review tab, click the Delete command in the Comments group.

3 The comment will be deleted.

Co-authoring
Another collaboration tool is co-authoring, which allows others to view and edit your
workbook in real time. This makes it easier and quicker to collaborate on a workbook
with your team. A er sharing a workbook with others, they'll be able to co-author it.

Real-time co-authoring requires an O ice 365 subscription.

When you co-author a workbook, you can see others at work because each will have
their own unique color. If you want to see who's currently editing a workbook, you can
hover over the activity to see their name.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 225/258
5/19/2020 Excel: What-if Analysis

Restoring a previous version

When you or anyone else makes changes to a workbook, the changes are saved
automatically. However, if you're unhappy with the changes, you can always restore a
previous version.

1 Click the clock icon next to the Share button.

2 A Version History pane will appear on the right side of the screen. Double-
click the version you'd like to restore.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 226/258
5/19/2020 Excel: What-if Analysis

3 Once you've decided this is the version you want, click Restore.

4 The previous version will be restored.

Challenge!
1 Open our practice workbook.

2 Add four comments to the worksheet.

3 Delete one of the comments.

4 Make all of the comments visible, using Show All Comments.

5 When you're finished, your workbook should look something like this:

6 Optional: Share the document with someone you know, and experiment
with some of the various co-authoring features.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 227/258
5/19/2020 Excel: What-if Analysis

Lesson 26: Inspecting and Protecting Workbooks

Introduction
Before sharing a workbook, you'll want to make sure it doesn't include any spelling
errors or information you want to keep private. Fortunately, Excel includes several tools
to help finalize and protect your workbook, including the Document Inspector and
Protect Workbook feature.

Optional: Download our practice workbook.

Watch the video below to learn more about inspecting and protecting
workbooks.

Excel: Inspecting and Protecting Workbooks

The Document Inspector

Whenever you create or edit a workbook, certain personal information may be added
to the file automatically. You can use the Document Inspector to remove this
information before sharing a workbook with others.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 228/258
5/19/2020 Excel: What-if Analysis

Because some changes may be permanent, it's a good idea to save an

additional copy of your workbook before using the Document Inspector to
remove information.

To use the Document Inspector:

1 Click the File tab to access Backstage view.

2 From the Info pane, click Check for Issues, then select Inspect Document
from the drop-down menu.

3 You may be prompted to save your file before running the Document
Inspector.

4 Document Inspector will appear. Check or uncheck boxes, depending on

the content you want to review, then click Inspect. In our example, we'll
leave everything selected.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 229/258
5/19/2020 Excel: What-if Analysis

5 The inspection results will appear. In our example, we can see that our
workbook contains comments and some personal information, so we'll
click Remove All on both items to remove this information from the
workbook.

6 When you're done, click Close.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 230/258
5/19/2020 Excel: What-if Analysis

Protecting your workbook

By default, anyone with access to your workbook will be able to open, copy, and edit its
content unless you protect it. There are several ways to protect a workbook, depending
on your needs.

To protect your workbook:

1 Click the File tab to access Backstage view.

2 From the Info pane, click the Protect Workbook command.

3 In the drop-down menu, choose the option that best suits your needs. In
our example, we'll select Mark as Final. Marking your workbook as final is a
good way to discourage others from editing the workbook, while the other
options give you even more control if needed.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 231/258
5/19/2020 Excel: What-if Analysis

4 A dialog box will appear, prompting you to save. Click OK.

5 Another dialog box will appear. Click OK.

6 The workbook will be marked as final.

Marking a workbook as final will not prevent others from editing it. If you
want to prevent people from editing it, you can use the Restrict Access
option instead.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 232/258
5/19/2020 Excel: What-if Analysis

Challenge!
1 Open our practice workbook.

2 Use the Document Inspector to check the workbook and remove anything
it finds.

3 Protect the workbook by Marking As Final.

4 When you're finished, your workbook should look something like this:

Lesson 27: Intro to PivotTables

Introduction
When you have a lot of data, it can sometimes be di icult to analyze all of the
information in your worksheet. PivotTables can help make your worksheets more
manageable by summarizing data and allowing you to manipulate it in di erent ways.

Optional: Download our practice workbook.

Watch the video below to learn more about PivotTables.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 233/258
5/19/2020 Excel: What-if Analysis

Excel: Intro to PivotTables

Using PivotTables to answer questions

Consider the example below. Let's say we wanted to answer the question What is the
amount sold by each salesperson? Answering it could be time consuming and di icult;
each salesperson appears on multiple rows, and we would need to total all of their
di erent orders individually. We could use the Subtotal command to help find the total
for each salesperson, but we would still have a lot of data to work with.

Fortunately, a PivotTable can instantly calculate and summarize the data in a way that
will make it much easier to read. When we're done, the PivotTable will look something
like this:

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 234/258
5/19/2020 Excel: What-if Analysis

Once you've created a PivotTable, you can use it to answer di erent questions by
rearranging—or pivoting—the data. For example, let's say we wanted to answer What is
the total amount sold in each month? We could modify our PivotTable to look like this:

To create a PivotTable:
1 Select the table or cells (including column headers) you want to include in
your PivotTable.

2 From the Insert tab, click the PivotTable command.

3 The Create PivotTable dialog box will appear. Choose your settings, then
click OK. In our example, we'll use Table1 as our source data and place the
PivotTable in a new worksheet.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 235/258
5/19/2020 Excel: What-if Analysis

4 A blank PivotTable and Field List will appear in a new worksheet.

5 Once you create a PivotTable, you'll need to decide which fields to add.
Each field is simply a column header from the source data. In the
PivotTable Fields list, check the box for each field you want to add. In our
example, we want to know the total amount sold by each salesperson, so
we'll check the Salesperson and Order Amount fields.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 236/258
5/19/2020 Excel: What-if Analysis

6 The selected fields will be added to one of the four areas below. In our
example, the Salesperson field has been added to the Rows area, while
Order Amount has been added to Values. You can also drag and drop
fields directly into the desired area.

7 The PivotTable will calculate and summarize the selected fields. In our
example, the PivotTable shows the amount sold by each salesperson.

Just like with normal spreadsheets, you can sort the data in a PivotTable
using the Sort & Filter command on the Home tab. You can also apply any
type of number formatting you want. For example, you may want to
https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 237/258
5/19/2020 Excel: What-if Analysis

change the number format to Currency. However, be aware that some

types of formatting may disappear when you modify the PivotTable.

If you change any of the data in your source worksheet, the PivotTable will
not update automatically. To manually update it, select the PivotTable
and then go to Analyze > Refresh.

Pivoting data
One of the best things about PivotTables is that they can quickly pivot—or reorganize—
your data, allowing you to examine your worksheet in several ways. Pivoting data can
help you answer di erent questions and even experiment with your data to discover
new trends and patterns.

To add columns:
So far, our PivotTable has only shown one column of data at a time. To show multiple
columns, you'll need to add a field to the Columns area.

1 Drag a field from the Field List into the Columns area. In our example, we'll
use the Month field.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 238/258
5/19/2020 Excel: What-if Analysis

2 The PivotTable will include multiple columns. In our example, there is now
a column for each person's monthly sales, in addition to the grand total.

To change a row or column:

Changing a row or column can give you a completely di erent perspective on your data.
All you have to do is remove the field in question, then replace it with another.

1 Drag the field you want to remove out of its current area. You can also
uncheck the appropriate box in the Field List. In this example, we've
removed the Month and Salesperson fields.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 239/258
5/19/2020 Excel: What-if Analysis

2 Drag a new field into the desired area. In our example, we'll place the
Region field under Rows.

3 The PivotTable will adjust—or pivot—to show the new data. In our example,
it now shows the amount sold by each region.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 240/258
5/19/2020 Excel: What-if Analysis

To learn more:
Once you're comfortable with PivotTables, review our Doing More with PivotTables
lesson for additional ways to customize and manipulate data.

Challenge!
1 Open our practice workbook.

2 Create a PivotTable in a separate sheet.

3 We want to answer the question What is the total amount sold in each
region? To do this, select Region and Order Amount. When you're finished,
your workbook should look like this:

4 In the Rows area, remove Region and replace it with Salesperson.

5 Add Month to the Columns area.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 241/258
5/19/2020 Excel: What-if Analysis

6 Change the number format of cells B5:E13 to Currency. Note: You might
have to make columns C and D wider to see the values.

7 When you're finished, your workbook should look like this:

Lesson 28: Doing More with PivotTables

Introduction
As you learned in our previous lesson, PivotTables can be used to summarize and
analyze almost any type of data. To manipulate your PivotTable—and gain even more
insight into your data—Excel o ers three additional tools: filters, slicers, and
PivotCharts.

Optional: Download our practice workbook.

Watch the video below to learn more about enhancing PivotTables.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 242/258
5/19/2020 Excel: What-if Analysis

Excel: Doing More with PivotTables

Filters
Sometimes you may want focus on a certain section of your data. Filters can be used to
narrow down the data in your PivotTable so you can view only the information you
need.

To add a filter:
In the example below, we'll filter out certain salespeople to determine how their
individual sales are impacting each region.

1 Drag a field from the Field List to the Filters area. In this example, we'll use
the Salesperson field.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 243/258
5/19/2020 Excel: What-if Analysis

2 The filter will appear above the PivotTable. Click the drop-down arrow,
then check the box next to Select Multiple Items.

3 Uncheck the box next to any item you don't want to include in the
PivotTable. In our example, we'll uncheck the boxes for a few salespeople,
then click OK.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 244/258
5/19/2020 Excel: What-if Analysis

4 The PivotTable will adjust to reflect the changes.

Slicers
Slicers make filtering data in PivotTables even easier. Slicers are basically just filters but
are easier and faster to use, allowing you to instantly pivot your data. If you frequently
filter your PivotTables, you may want to consider using slicers instead of filters.

To add a slicer:
1 Select any cell in the PivotTable.

2 From the Analyze tab, click the Insert Slicer command.

3 A dialog box will appear. Check the box next to the desired field. In our
example, we'll select Salesperson, then click OK.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 245/258
5/19/2020 Excel: What-if Analysis

4 The slicer will appear next to the PivotTable. Each selected item will be
highlighted in blue. In the example below, the slicer contains all eight
salespeople, but only five of them are currently selected.

5 Just like filters, only selected items are used in the PivotTable. When you
select or deselect an item, the PivotTable will instantly reflect the change.
Try selecting di erent items to see how they a ect the PivotTable. Press
and hold the Ctrl key on your keyboard to select multiple items at once.

You can also click the Filter icon in the top-right corner of the slicer to
select all items at once.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 246/258
5/19/2020 Excel: What-if Analysis

PivotCharts
PivotCharts are like regular charts, except they display data from a PivotTable. Just like
regular charts, you'll be able to select a chart type, layout, and style that will best
represent the data.

To create a PivotChart:
In the example below, our PivotTable is showing a portion of each region's sales figures.
We'll use a PivotChart so we can see the information more clearly.

1 Select any cell in your PivotTable.

2 From the Insert tab, click the PivotChart command.

3 The Insert Chart dialog box will appear. Select the desired chart type and
layout, then click OK.

4 The PivotChart will appear.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 247/258
5/19/2020 Excel: What-if Analysis

Try using filters or slicers to narrow down the data in your PivotChart. To
view di erent subsets of information, change the columns or rows in your
PivotTable. In the example below, we've changed the PivotTable to view the
monthly sales for each salesperson.

Challenge!
1 Open our practice workbook.

2 In the Rows area, remove Region and replace it with Salesperson.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 248/258
5/19/2020 Excel: What-if Analysis

3 Insert a PivotChart and choose the type Line with Markers.

4 Insert a slicer for Regions.

5 Use the slicer to only show the South and East regions.

6 Change the PivotChart type to Stacked Column.

7 In the PivotChart Fields pane to the right, add Month to the Legend
(Series) area. Note: You can also click the PivotTable and add Month to the
Columns area for the same results.

8 When you're finished, your workbook should look something like this:

Lesson 29: What-if Analysis

Introduction
Excel includes powerful tools to perform complex mathematical calculations, including
what-if analysis. This feature can help you experiment and answer questions with
your data, even when the data is incomplete. In this lesson, you will learn how to use a
what-if analysis tool called Goal Seek.

Optional: Download our practice workbook.

Watch the video below to learn more about what-if analysis and Goal Seek.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 249/258
5/19/2020 Excel: What-if Analysis

Excel: What-if Analysis

Goal Seek
Whenever you create a formula or function in Excel, you put various parts together to
calculate a result. Goal Seek works in the opposite way: It lets you start with the
desired result, and it calculates the input value that will give you that result. We'll use a
few examples to show how to use Goal Seek.

To use Goal Seek (example 1):

Let's say you're enrolled in a class. You currently have a grade of 65, and you need at
least a 70 to pass the class. Luckily, you have one final assignment that might be able to
raise your average. You can use Goal Seek to find out what grade you need on the final
assignment to pass the class.

In the image below, you can see that the grades on the first four assignments are 58, 70,
72, and 60. Even though we don't know what the fi h grade will be, we can write a
formula—or function—that calculates the final grade. In this case, each assignment is
weighted equally, so all we have to do is average all five grades by typing
=AVERAGE(B2:B6). Once we use Goal Seek, cell B6 will show us the minimum grade
we'll need to make on that assignment.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 250/258
5/19/2020 Excel: What-if Analysis

1 Select the cell with the value you want to change. Whenever you use Goal
Seek, you'll need to select a cell that already contains a formula or
function. In our example, we'll select cell B7 because it contains the
formula =AVERAGE(B2:B6).

2 From the Data tab, click the What-If Analysis command, then select Goal
Seek from the drop-down menu.

3 A dialog box will appear with three fields. The first field, Set cell:, will
contain the desired result. In our example, cell B7 is already selected.

The second field, To value:, is the desired result. In our example, we'll enter
70 because we need to earn at least that to pass the class.

The third field, By changing cell:, is the cell where Goal Seek will place its

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 251/258
5/19/2020 Excel: What-if Analysis

answer. In our example, we'll select cell B6 because we want to determine

the grade we need to earn on the final assignment.

4 When you're done, click OK.

5 The dialog box will tell you if Goal Seek was able to find a solution. Click OK.

6 The result will appear in the specified cell. In our example, Goal Seek
calculated that we will need to score at least a 90 on the final assignment to
earn a passing grade.

To use Goal Seek (example 2):

Let's say you're planning an event and want to invite as many people as you can without
exceeding a budget of $500. We can use Goal Seek to figure out how many people to
invite. In our example below, cell B5 contains the formula =B2+B3*B4 to calculate the
total cost of a room reservation, plus the cost per person.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 252/258
5/19/2020 Excel: What-if Analysis

1 Select the cell with the value you want to change. In our example, we'll
select cell B5.

2 From the Data tab, click the What-If Analysis command, then select Goal
Seek from the drop-down menu.

3 A dialog box will appear with three fields. The first field, Set cell:, will
contain the desired result. In our example, cell B5 is already selected.

The second field, To value:, is the desired result. In our example, we'll enter
500 because we only want to spend $500.

The third field, By changing cell:, is the cell where Goal Seek will place its
answer. In our example, we'll select cell B4 because we want to know how
many guests we can invite without spending more than $500.

4 When you're done, click OK.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 253/258
5/19/2020 Excel: What-if Analysis

5 The dialog box will tell you if Goal Seek was able to find a solution. Click OK.

6 The result will appear in the specified cell. In our example, Goal Seek
calculated the answer to be approximately 18.62. In this case, our final
answer needs to be a whole number, so we'll need to round the answer up
or down. Because rounding up would cause us to exceed our budget, we'll
round down to 18 guests.

As you can see in the example above, some situations will require the
answer to be a whole number. If Goal Seek gives you a decimal, you'll need
to round up or down, depending on the situation.

Other types of what-if analysis

For more advanced projects, you may want to consider the other types of what-if
analysis: scenarios and data tables. Instead of starting from the desired result and
working backward, like with Goal Seek, these options allow you to test multiple values
and see how the results change.

▶ Scenarios let you substitute values for multiple cells (up to 32) at the same
time. You can create as many scenarios as you want and then compare
them without changing the values manually. In the example below, we're
using scenarios to compare di erent venues for an upcoming event.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 254/258
5/19/2020 Excel: What-if Analysis

For more information on scenarios, read this article from Microso .

▶ Data tables allow you to take one or two variables in a formula and replace
them with as many di erent values as you want, then view the results in a
table. This option is especially powerful because it shows multiple results
at the same time, unlike scenarios or Goal Seek. In the example below, we
can view 24 possible results for a car loan.

For more information on data tables, read this article from Microso .

Challenge!
1 Open our practice workbook.

2 Click the Challenge tab in the bottom-le of the workbook.

3 In cell B8, create a function that calculates the average of the sales in
B2:B7.

4 The workbook shows Dave's monthly sales amounts for the first half of the
year. If he reaches a $200,000 mid-year average, he will receive a 5% bonus.

https://edu.gcfglobal.org/en/excel/whatif-analysis/1/ 255/258
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Excel Formulas
About This Tutorial

About this tutorial

Welcome to our Excel Formulas
tutorial! If you want to learn more
about using formulas and
functions in Excel or another
spreadsheet application (such as
Google Sheets), you've come to the
right place! Whether you're new to
spreadsheets or are a more
advanced spreadsheet user, this
tutorial is designed to show you
how to use di erent formulas and
functions to solve real-life
problems.

Formulas work almost exactly the same way in most spreadsheet programs, so you can
work along with this tutorial even if you don't have Excel. These skills will work in
Google Sheets, LibreO ice, OpenO ice, and many other spreadsheet programs.
However, keep in mind that we'll be using Excel 2016 and 2013 throughout this tutorial,
so you may notice some slight di erences if you're using a di erent program.

If you've never created a formula before, that's OK! This tutorial starts with the absolute
basics of creating formulas and gradually works up to to some more advanced formulas.
But before you begin, you'll want to have a basic understanding of how to navigate your
spreadsheet program. You can review one of our other spreadsheet tutorials to learn
more:

▶ Excel

▶ Excel 2016

▶ Excel 2013

▶ Excel 2010

▶ Excel 2007

▶ Google Sheets

If you already have a basic understanding of how spreadsheets work, you're ready to
dive in and get started. Throughout this tutorial, you'll find a mix of written lessons,
videos, and interactives that will explain the basics of formulas and guide you through
real-life scenarios where you'll use formulas to solve problems!

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 1/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Lesson 2: Simple Formulas

Introduction
One of the most powerful features in Excel is the ability to calculate numerical
information using formulas. Just like a calculator, Excel can add, subtract, multiply, and
divide. In this lesson, we'll show you how to use cell references to create simple
formulas.

Optional: Download our practice workbook.

Watch the video below to learn how to create formulas in Excel.

Excel: Intro to Formulas

Mathematical operators
Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus
sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/),
and a caret (^) for exponents.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 2/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

All formulas in Excel must begin with an equals sign (=). This is because the cell
contains, or is equal to, the formula and the value it calculates.

Understanding cell references

While you can create simple formulas in Excel manually (for example, =2+2 or =5*5),
most of the time you will use cell addresses to create a formula. This is known as
making a cell reference. Using cell references will ensure that your formulas are always
accurate because you can change the value of referenced cells without having to rewrite
the formula.

By combining a mathematical operator with cell references, you can create a variety of
simple formulas in Excel. Formulas can also include a combination of cell references and
numbers, as in the examples below:

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 3/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

To create a formula:
In our example below, we'll use a simple formula and cell references to calculate a
budget.

1 Select the cell that will contain the formula. In our example, we'll select cell
B3.

2 Type the equals sign (=). Notice how it appears in both the cell and the
formula bar.

3 Type the cell address of the cell you want to reference first in the formula:
cell B1 in our example. A blue border will appear around the referenced
cell.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 4/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

4 Type the mathematical operator you want to use. In our example, we'll
type the addition sign (+).

5 Type the cell address of the cell you want to reference second in the
formula: cell B2 in our example. A red border will appear around the
referenced cell.

6 Press Enter on your keyboard. The formula will be calculated, and the
value will be displayed in the cell.

Let's practice!

Question 1 of 1
Which formula would add the January and
February profits?

=B2+B3 B3+B4=

B2+B3= B2+B3

Submit

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 5/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

If the result of a formula is too large to be displayed in a cell, it may appear

as pound signs (#######) instead of a value. This means the column is not
wide enough to display the cell content. Simply increase the column
width to show the cell content.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your
worksheet without having to rewrite formulas. In the example below, we've modified
the value of cell B1 from $1,200 to $1,800. The formula in B3 will automatically
recalculate and display the new value in cell B3.

Excel will not always tell you if your formula contains an error, so it's up to
you to check all of your formulas. To learn how to do this, you can read the
Double-Check Your Formulas lesson from our Excel Formulas tutorial.

To create a formula using the point-and-click method:

Rather than typing cell addresses manually, you can point and click on the cells you
want to include in your formula. This method can save a lot of time and e ort when
creating formulas. In our example below, we'll create a formula to calculate the cost of
ordering several boxes of plastic silverware.

1 Select the cell that will contain the formula. In our example, we'll select cell
D3.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 6/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

2 Type the equals sign (=).

3 Select the cell you want to reference first in the formula: cell B3 in our
example. The cell address will appear in the formula, and a dashed blue
line will appear around the referenced cell.

4 Type the mathematical operator you want to use. In our example, we'll
type the multiplication sign (*).

5 Select the cell you want to reference second in the formula: cell C3 in our
example. The cell address will appear in the formula, and a dashed red
line will appear around the referenced cell.

6 Press Enter on your keyboard. The formula will be calculated, and the
value will be displayed in the cell.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 7/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Formulas can also be copied to adjacent cells with the fill handle, which
can save a lot of time and e ort if you need to perform the same
calculation multiple times in a worksheet. Review our lesson on Relative
and Absolute Cell References to learn more.

To edit a formula:
Sometimes you may want to modify an existing formula. In the example below, we've
entered an incorrect cell address in our formula, so we'll need to correct it.

1 Select the cell containing the formula you want to edit. In our example,
we'll select cell B3.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 8/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

2 Click the formula bar to edit the formula. You can also double-click the cell
to view and edit the formula directly within the cell.

3 A border will appear around any referenced cells. In our example, we'll
change the second part of the formula to reference cell B2 instead of cell
C2.

4 When you're finished, press Enter on your keyboard or click the checkmark
in the formula bar.

5 The formula will be updated, and the new value will be displayed in the
cell.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 9/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

If you change your mind, you can press the Esc key on your keyboard to
avoid accidentally making changes to your formula.

To show all of the formulas in a spreadsheet, you can hold the Ctrl key and
press ` (grave accent). The grave accent key is usually located in the top-le
corner of the keyboard. You can press Ctrl+` again to switch back to the
normal view.

Challenge!
1 Open an existing Excel workbook. If you want, you can use our practice
workbook.

2 Create a simple addition formula using cell references. If you are using the
example, create the formula in cell B4 to calculate the total budget.

3 Try modifying the value of a cell referenced in a formula. If you are using
the example, change the value of cell B2 to $2,000. Notice how the formula
in cell B4 recalculates the total.

4 Try using the point-and-click method to create a formula. If you are using
the example, create a formula in cell G5 that multiplies the cost of napkins
by the quantity needed to calculate the total cost.

5 Edit a formula using the formula bar. If you are using the example, edit the
formula in cell B9 to change the division sign (/) to a minus sign (-).

Lesson 3: Complex Formulas

Introduction
A simple formula is a mathematical expression with one operator, such as 7+9. A
complex formula has more than one mathematical operator, such as 5+2*8. When
there is more than one operation in a formula, the order of operations tells your
spreadsheet which operation to calculate first. In order to use complex formulas, you
will need to understand the order of operations.

Optional: Download our example file for this lesson.

Watch the video below to learn more about complex formulas.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 10/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Excel: Creating More Complex Formulas

The order of operations

All spreadsheet programs calculate formulas based on the following order of
operations:

1 Operations enclosed in parentheses

2 Exponential calculations (3^2, for example)

3 Multiplication and division, whichever comes first

4 Addition and subtraction, whichever comes first

A mnemonic that can help you remember the order is PEMDAS, or Please
Excuse My Dear Aunt Sally.

Click the arrows in the slideshow below to learn more about how the order
of operations is used to calculate complex formulas.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 11/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Let's practice!

Question 1 of 1
What value would the following formula
return?

17 13

11 19

Submit

Using parentheses within a formula can be very important. Because of the order of
operations, it can completely change an answer. Let's try the same problem from above,
but this time we'll add parentheses to the last part.

Let's practice!

Question 1 of 1
Now that the formula has parentheses, what
value would the following formula return?

19 13

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 12/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

17 11

Submit

Creating complex formulas

In the example below, we'll demonstrate a complex formula using the order of
operations. Here, we want to calculate the cost of sales tax for a catering invoice. To do
this, we'll write our formula as =(D2+D3)*0.075 in cell D4. This formula will add the
prices of our items together and then multiply that value by the 7.5% tax rate (which is
written as 0.075) to calculate the cost of sales tax.

The spreadsheet then follows the order of operations and first adds the values inside the
parentheses: (44.85+39.90) = $84.75. Then it multiplies that value by the tax rate:
$84.75*0.075. The result will show that the sales tax is $6.36.

It is especially important to enter complex formulas with the correct order

of operations. Otherwise, the spreadsheet will not calculate the results
accurately. In our example, if the parentheses are not included, the
multiplication is calculated first and the result is incorrect. Parentheses are
the best way to define which calculations will be performed first in a
formula.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 13/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

To create a complex formula using the order of

operations:
In our example below, we will use cell references along with numerical values to create
a complex formula that will calculate the total cost for a catering invoice. The formula
will calculate the cost for each menu item and add those values together.

1 Select the cell that will contain the formula. In our example, we'll select cell
C4.

2 Enter your formula. In our example, we'll type =B2*C2+B3*C3. This

formula will follow the order of operations, first performing multiplication:
2.29*20 = 45.80 and 3.49*35 = 122.15. Then it will add those values
together to calculate the total: 45.80+122.15.

3 Double-check your formula for accuracy, then press Enter on your

keyboard. The formula will calculate and display the result. In our
example, the result shows that the total cost for the order is $167.95.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 14/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

You can add parentheses to any equation to make it easier to read. While it
won't change the result of the formula in this example, we could enclose
the multiplication operations within parentheses to clarify that they will be
calculated before the addition.

Your spreadsheet will not always tell you if your formula contains an error,
so it's up to you to check all of your formulas. To learn how to do this, check
out the Double-Check Your Formulas lesson.

Challenge!
1 Open an existing Excel workbook. If you want, you can use the example file
for this lesson.

2 Create a complex formula that will perform addition before multiplication.

If you are using the example, create a formula in cell D6 that first adds the
values of cells D3, D4, and D5 and then multiplies their total by 0.075. Hint:
You'll need to think about the order of operations for this to work correctly.

Lesson 4: Relative and Absolute Cell References

Introduction
There are two types of cell references: relative and absolute. Relative and absolute
references behave di erently when copied and filled to other cells. Relative references
change when a formula is copied to another cell. Absolute references, on the other
hand, remain constant no matter where they are copied.

Optional: Download our example file for this lesson.

Watch the video below to learn more about cell references.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 15/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Excel: Relative and Absolute Cell References

Relative references
By default, all cell references are relative references. When copied across multiple cells,
they change based on the relative position of rows and columns. For example, if you
copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative
references are especially convenient whenever you need to repeat the same calculation
across multiple rows or columns.

To create and copy a formula using relative references:

In the following example, we want to create a formula that will multiply each item's
price by the quantity. Rather than create a new formula for each row, we can create a
single formula in cell D2 and then copy it to the other rows. We'll use relative references
so the formula correctly calculates the total for each item.

1 Select the cell that will contain the formula. In our example, we'll select cell
D2.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 16/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

2 Enter the formula to calculate the desired value. In our example, we'll type
=B2*C2.

3 Press Enter on your keyboard. The formula will be calculated, and the
result will be displayed in the cell.

4 Locate the fill handle in the lower-right corner of the desired cell. In our
example, we'll locate the fill handle for cell D2.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 17/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

5 Click, hold, and drag the fill handle over the cells you wish to fill. In our
example, we'll select cells D3:D12.

6 Release the mouse. The formula will be copied to the selected cells with
relative references and the values will be calculated in each cell.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 18/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

You can double-click the filled cells to check their formulas for accuracy.
The relative cell references should be di erent for each cell, depending on
its row.

Let's practice!

Question 1 of 1
Which of the following is a relative cell reference?

$C$2 C2

!C2 $C2

Submit

Absolute references
There may be times when you do not want a cell reference to change when filling cells.
Unlike relative references, absolute references do not change when copied or filled. You
can use an absolute reference to keep a row and/or column constant.

An absolute reference is designated in a formula by the addition of a dollar sign ($)

before the column and row. If it precedes the column or row (but not both), it's known as
https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 19/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

a mixed reference.

You will use the relative (A2) and absolute ($A$2) formats in most formulas.
Mixed references are used less frequently.

When writing a formula in Microso Excel, you can press the F4 key on your keyboard to
switch between relative, absolute, and mixed cell references, as shown in the video
below. This is an easy way to quickly insert an absolute reference.

Excel Quick Tip: Absolute References with the F4 Key

To create and copy a formula using absolute references:

In our example, we'll use the 7.5% sales tax rate in cell E1 to calculate the sales tax for all
items in column D. We'll need to use the absolute cell reference $E$1 in our formula.
Because each formula is using the same tax rate, we want that reference to remain
constant when the formula is copied and filled to other cells in column D.

1 Select the cell that will contain the formula. In our example, we'll select cell
D3.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 20/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

2 Enter the formula to calculate the desired value. In our example, we'll type
=(B3*C3)*$E$1.

3 Press Enter on your keyboard. The formula will calculate, and the result will
display in the cell.

4 Locate the fill handle in the lower-right corner of the desired cell. In our
example, we'll locate the fill handle for cell D3.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 21/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

5 Click, hold, and drag the fill handle over the cells you wish to fill, cells
D4:D13 in our example.

6 Release the mouse. The formula will be copied to the selected cells with an
absolute reference, and the values will be calculated in each cell.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 22/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

You can double-click the filled cells to check their formulas for accuracy.
The absolute reference should be the same for each cell, while the other
references are relative to the cell's row.

Be sure to include the dollar sign ($) whenever you're making an absolute
reference across multiple cells. The dollar signs were omitted in the
example below. This caused the spreadsheet to interpret it as a relative
reference, producing an incorrect result when copied to other cells.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 23/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Let's practice!

Question 1 of 1
If you wanted to make an absolute reference to cell E2, what would it look like?

E2 [E2]

$E$2 @E2

Submit

Using cell references with multiple

worksheets
Most spreadsheet programs allow you to refer to any cell on any worksheet, which can
be especially helpful if you want to reference a specific value from one worksheet to
another. To do this, you'll simply need to begin the cell reference with the worksheet
name followed by an exclamation point (!). For example, if you wanted to reference cell
A1 on Sheet1, its cell reference would be Sheet1!A1.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 24/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Note that if a worksheet name contains a space, you will need to include
single quotation marks (' ') around the name. For example, if you wanted
to reference cell A1 on a worksheet named July Budget, its cell reference
would be 'July Budget'!A1.

To reference cells across worksheets:

In our example below, we'll refer to a cell with a calculated value between two
worksheets. This will allow us to use the exact same value on two di erent worksheets
without rewriting the formula or copying data between worksheets.

1 Locate the cell you wish to reference, and note its worksheet. In our
example, we want to reference cell E14 on the Menu Order worksheet.

2 Navigate to the desired worksheet. In our example, we'll select the

Catering Invoice worksheet.

3 The selected worksheet will appear.

4 Locate and select the cell where you want the value to appear. In our
example, we'll select cell B2.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 25/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

5 Type the equals sign (=), the sheet name followed by an exclamation
point (!), and the cell address. In our example, we'll type ='Menu
Order'!E14.

6 Press Enter on your keyboard. The value of the referenced cell will appear.
If the value of cell E14 changes on the Menu Order worksheet, it will be
updated automatically on the Catering Invoice worksheet.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 26/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

If you rename your worksheet at a later point, the cell reference will be
updated automatically to reflect the new worksheet name.

Challenge!
1 Open an existing Excel workbook. If you want, you can use the example file
for this lesson.

2 Create a formula that uses a relative reference. If you are using the
example, use the fill handle to fill in the formula in cells E4 through E14.
Double-click a cell to see the copied formula and the relative cell
references.

3 Create a formula that uses an absolute reference. If you are using the
example, correct the formula in cell D4 to refer only to the tax rate in cell E2
as an absolute reference, then use the fill handle to fill the formula from
cells D4 to D14.

4 Try referencing a cell across worksheets. If you are using the example,
create a cell reference in cell B3 on the Catering Invoice worksheet for cell
E15 on the Menu Order worksheet.

Lesson 5: Functions

Introduction
A function is a predefined formula that performs calculations using specific values in a
particular order. All spreadsheet programs include common functions that can be used
for quickly finding the sum, average, count, maximum value, and minimum value for
a range of cells. In order to use functions correctly, you'll need to understand the
di erent parts of a function and how to create arguments to calculate values and cell
references.

Watch the video below to learn more about using functions in Excel.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 27/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Excel: Functions

The parts of a function

In order to work correctly, a function must be written a specific way, which is called the
syntax. The basic syntax for a function is an equals sign (=), the function name (SUM,
for example), and one or more arguments. Arguments contain the information you want
to calculate. The function in the example below would add the values of the cell range
A1:A20.

Working with arguments

Arguments can refer to both individual cells and cell ranges and must be enclosed
within parentheses. You can include one argument or multiple arguments, depending
on the syntax required for the function.

For example, the function =AVERAGE(B1:B9) would calculate the average of the values
in the cell range B1:B9. This function contains only one argument.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 28/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Multiple arguments must be separated by a comma. For example, the function

=SUM(A1:A3, C1:C2, E2) will add the values of all cells in the three arguments.

Let's practice!

Question 1 of 1
Which of the following functions is using the correct syntax?

=SUM(A2:A8,E2:E8) SUM=(A2:A8,E2:E8)

SUM(A2:A8,E2:E8)= =A2:A8(SUM)E2:E8

Submit

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 29/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Using functions
There are a variety of functions. Here are some of the most common functions you'll use:

▶ SUM: This function adds all the values of the cells in the argument.

▶ AVERAGE: This function determines the average of the values included in

the argument. It calculates the sum of the cells and then divides that value
by the number of cells in the argument.

▶ COUNT: This function counts the number of cells with numerical data in the
argument. This function is useful for quickly counting items in a cell range.

▶ MAX: This function determines the highest cell value included in the
argument.

▶ MIN: This function determines the lowest cell value included in the
argument.

To use a function:
In our example below, we'll use a basic function to calculate the average price per unit
for a list of recently ordered items using the AVERAGE function.

1 Select the cell that will contain the function. In our example, we'll select cell
C11.

2 Type the equals sign (=) and enter the desired function name. In our
example, we'll type =AVERAGE.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 30/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

3 Enter the cell range for the argument inside parentheses. In our example,
we'll type (C3:C10). This formula will add the values of cells C3:C10 and
then divide that value by the total number of cells in the range to determine
the average.

4 Press Enter on your keyboard. The function will be calculated, and the
result will appear in the cell. In our example, the average price per unit of
items ordered was $15.93.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 31/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Your spreadsheet will not always tell you if your function contains an
error, so it's up to you to check all of your functions. To learn how to do this,
check out the Double-Check Your Formulas lesson.

Let's practice!

Question 1 of 1
In this example, we want to use the formula
=MAX(B3:D7)

What would that formula find?

The average number of cakes sold in The total number of items sold
March between January and March

The largest amount of an item The smallest amount of an item

ordered between January and March ordered in March

Submit

Working with unfamiliar functions

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 32/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

If you want to learn how a function works, you can start typing that function in a blank
cell to see what it does.

You can then type an open parenthesis to see what kind of arguments it needs.

Understanding nested functions

Whenever a formula contains a function, the function is generally calculated before any
other operators, like multiplication and division. That's because the formula treats the
entire function as a single value—before it can use that value in the formula, it needs to
run the function. For example, in the formula below, the SUM function will be calculated
before division:

Let's take a look at a more complicated example that uses multiple functions:

=WORKDAY(TODAY(),3)

Here, we have two di erent functions working together: the WORKDAY function and the
TODAY function. These are known as nested functions, since one function is placed, or
nested, within the arguments of another. As a rule, the nested function is always
calculated first, just like parentheses are performed first in the order of operations. In
this example, the TODAY function will be calculated first, since it's nested within the
WORKDAY function.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 33/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Other common functions

There are many other functions you can use to quickly calculate di erent things with
your data. Learning how to use other functions will allow you to solve complex problems
with your spreadsheets, and we'll be talking more about them throughout this tutorial.
You can also check out our articles below to learn about specific functions:

▶ How to Use Excel's VLOOKUP Function

▶ How to Count Cells with COUNTA

▶ Use SUMPRODUCT to Calculate Weighted Averages

▶ CONCATENATE: Excel's Duct Tape

▶ Use the PROPER Function to Capitalize Names in Excel

Lesson 6: Solving Real-Life Problems in Excel

Solving real-life problems in Excel

Excel can be used to solve all kinds of real-life problems. But how do you turn those
problems into formulas that Excel can understand? All it takes is a little bit of planning
(and some basic math). To learn more, check out the video below!

Solving Real-Life Problems in Excel

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 34/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Lesson 7: Double-Check Your Formulas

Double-check your formulas

One of the most powerful features of Excel is the ability to create formulas. You can use
formulas to calculate new values, analyze data, and much more. But formulas also have
a downside: If you make even a small mistake when typing a formula, it can give an
incorrect result.

To make matters worse, your spreadsheet will not always tell you if a formula is
wrong. It will usually just go ahead and run the calculations and give you the wrong
answer. It's up to you to double-check your formulas whenever you create them.

We've put together a list of tips you can use to help check formulas for accuracy. These
tips won't help you solve every problem you encounter, but they should provide you
with the tools to identify many common errors.

Check the references

Most formulas use at least one cell reference. When you double-click a formula, it will
highlight all of the referenced cells. You can then double-check each one to make sure
they are correct.

Look for mix-ups

A common mistake is to use the correct cell references, but in the wrong order. For
example, if you want to subtract C2 from C3, the formula should be =C3-C2, not =C2-C3.

Break it up
If a formula is too complicated to check, try breaking it up into several smaller
formulas. This way, you can check each formula for accuracy, and if there are any
problems you will know exactly where they are.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 35/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Ballpark it
You can use your own experience, critical-thinking skills, and common sense to estimate
what the answer should be. If Excel gives you a much larger or smaller value than
expected, there may be a problem with your formula (or with the values in the cells).

For example, if you are calculating the total price of 8 items that are 98 cents each, the
answer should be slightly less than $8. In the example below, the formula calculated the
answer as $784.00, which is incorrect. That's because the price in A2 was entered as 98,
and it should have been 0.98. As you can see, even the smallest details can make a
huge di erence.

Note that this tip does not always work. In some cases, the wrong answer
may be fairly close to the correct answer. However, in many situations it can
help you quickly catch a problem in your formula.

Check the arguments

If you're using a function, make sure each required argument is included. A small
dialog box should appear as you're typing the function to let you know what arguments
are needed.

This can be especially useful when you're trying to fix a function that's not working
correctly. For example, let's look at the function below:

In the example above, the NETWORKDAYS function is returning an error. If we enter the
NETWORKDAYS function into a new cell, the reason becomes clear:
https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 36/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

The NETWORKDAYS function requires at least two arguments—a start date and an end
date. Our original function only has one argument, so we'll edit our function to include
both arguments:

Now our function is working correctly!

Walk through the order of operations

Remember the order of operations from math class? If not (or if you want a refresher),
you can check out our Complex Formulas lesson. Your spreadsheet will always use this
ordering, which means it doesn't just calculate a formula from le to right. In the
example below, the multiplication is calculated first, which isn't what we wanted. We
could fix this formula by enclosing D2+D3 in parentheses.

Switch to formula view

If you have a lot of formulas and functions in your spreadsheet, you may want to switch
to formula view to see all of them at the same time. Just hold the Ctrl key and press `
(grave accent). The grave accent key is usually located in the upper-le corner of the
keyboard. Press Ctrl+` again to switch back to normal view.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 37/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Remember, it takes a lot of practice to master writing formulas. Even the most
experienced spreadsheet users encounter formula errors. If your formula doesn't work
or produces an incorrect value, don't panic! More o en than not, there's probably a
simple reason your formula resulted in an error—once you find it, you'll be able to get
your formula working correctly.

Let's practice!

Question 1 of 1
Someone is trying to find the average profit
amount between January and July, using
the formula in cell B9.

What mistake did this person make?

Using the incorrect range Using parentheses in the formula

Using an incorrect formula The location of the equals sign

Submit

Lesson 8: Understanding Number Formats

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 38/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

What are number formats?

Whenever you're working with a spreadsheet, it's a good idea to use appropriate
number formats for your data. Number formats tell your spreadsheet exactly what type
of data you're using, such as percentages (%), currency ($), times, dates, and so on.

Watch the video below to learn more about number formats in Excel.

Excel: Understanding Number Formats

Why use number formats?

Number formats don't just make your spreadsheet easier to read—they also make it
easier to use. When you apply a number format, you're telling your spreadsheet exactly
what kinds of values are stored in a cell. For example, the date format tells the
spreadsheet that you're entering specific calendar dates. This allows the spreadsheet
to better understand your data, which can help ensure that your data remains consistent
and that your formulas are calculated correctly.

If you don't need to use a specific number format, the spreadsheet will
usually apply the general number format by default. However, the general
format may apply some small formatting changes to your data.

Applying number formats

Just like other types of formatting, such as changing the font color, you'll apply number
formats by selecting cells and then choosing the desired formatting option. Every
spreadsheet program allows you to add number formatting, but the process will vary
depending on which application you're using:
https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 39/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

▶ For Microso Excel 2007-2019, go to the Home tab, click the Number
Format drop-down menu in the Number group, and select the desired
format. You can also click one of the quick number-formatting commands
below the drop-down menu.

▶ For Excel 2003 and earlier, go to Format > Cells.

▶ For Google Sheets, click the More Formats button near the le side of the
toolbar—this button will look like the numbers 123. To the le of the
command, you can also click the Currency or Percent commands to
quickly apply those formats.

For most versions of Microso Excel, you can also select the desired cells
and press Ctrl+1 on your keyboard to access more number-formatting
options.

In this example, we've applied the Currency number format, which adds currency
symbols ($) and displays two decimal places for any numerical values.

If you select any cells with number formatting, you can see the actual value of the cell in
the formula bar. The spreadsheet will use this value for formulas and other calculations.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 40/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Using number formats correctly

There's more to number formatting than selecting cells and applying a format.
Spreadsheets can actually apply a lot of number formatting automatically based on the
way you enter data. This means you'll need to enter data in a way the program can
understand, and then ensure that those cells are using the proper number format. For
example, the image below shows how to use number formats correctly for dates,
percentages, and times:

Now that you know more about how number formats work, we'll look at a few di erent
number formats in action.

Percentage formats
One of the most helpful number formats is the percentage (%) format. It displays values
as percentages, such as 20% or 55%. This is especially helpful when calculating things
like the cost of sales tax or a tip. When you type a percent sign (%) a er a number, the
percentage number format will be be applied to that cell automatically.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 41/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

As you may remember from math class, a percentage can also be written as a decimal.
So 15% is the same thing as 0.15, 7.5% is 0.075, 20% is 0.20, 55% is 0.55, and so on. You
can review this lesson from our Math tutorial to learn more about converting
percentages to decimals.

There are many times when percentage formatting will be useful. For example, in the
images below, notice how the sales tax rate is formatted di erently for each
spreadsheet (5, 5%, and 0.05):

As you can see, the calculation in the spreadsheet on the le didn't work correctly.
Without the percentage number format, our spreadsheet thinks we want to multiply
$22.50 by 5, not 5%. And while the spreadsheet on the right still works without
percentage formatting, the spreadsheet in the middle is easier to read.

Date formats
Whenever you're working with dates, you'll want to use a date format to tell the
spreadsheet that you're referring to specific calendar dates, such as July 15, 2014. Date
formats also allow you to work with a powerful set of date functions that use time and
date information to calculate an answer.

Spreadsheets don't understand information the same way a person would. For instance,
if you type October into a cell, the spreadsheet won't know you're entering a date so it
will treat it like any other text. Instead, when you enter a date, you'll need to use a
specific format your spreadsheet understands, such as month/day/year (or
day/month/year depending on which country you're in). In the example below, we'll
type 10/12/2014 for October 12, 2014. Our spreadsheet will then automatically apply
the date number format for the cell.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 42/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Now that we have our date correctly formatted, we can do lots of di erent things with
this data. For example, we could use the fill handle to continue the dates through the
column, so a di erent day appears in each cell:

If the date formatting isn't applied automatically, it means the spreadsheet did not
understand the data you entered. In the example below, we've typed March 15th. The
spreadsheet did not understand that we were referring to a date, so this cell is still using
the general number format.

On the other hand, if we type March 15 (without the "th"), the spreadsheet will
recognize it as a date. Since it doesn't include a year, the spreadsheet will automatically

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 43/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

add the current year so the date will have all of the necessary information. We could also
type the date several other ways, such as 3/15, 3/15/2014, or March 15 2014, and the
spreadsheet would still recognize it as a date.

Try entering the dates below into a spreadsheet and see if the date format is applied
automatically:

▶ 10/12

▶ October

▶ October 12

▶ October 2014

▶ 10/12/2014

▶ October 12, 2014

▶ 2014

▶ October 12th

If you want to add the current date to a cell, you can use the Ctrl+; shortcut,
as shown in the video below.

Excel Quick Tip: Add the Current Date to a Cell

Other date formatting options

Some programs have more date formatting options, which can change the way dates
appear in your spreadsheet. Again, this process this may vary slightly based on the
https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 44/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

spreadsheet program you're using. To access these options in Excel 2007-2019, select
the Number Format drop-down menu and choose More Number Formats.

A dialog box will appear. From here, you can choose the desired date formatting option.

As you can see in the formula bar, a custom date format doesn't change the actual date
in our cell—it just changes the way it's displayed.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 45/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Number formatting tips

Here are a few tips for getting the best results with number formatting:

▶ Apply number formatting to an entire column: If you're planning to use

one column for a certain type of data, like dates or percentages, you may
find it easiest to select the entire column by clicking the column letter and
then applying the desired number formatting. This way, any data you add
to this column in the future will already have the correct number format.
Note that the header row usually won't be a ected by number formatting.

▶ Double-check your values a er applying number formatting: If you

apply number formatting to existing data, you may have unexpected
results. For example, applying percentage (%) formatting to a cell with a
value of 5 will give you 500%, not 5%. In this case, you'd need to retype the
values correctly in each cell.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 46/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

▶ If you reference a cell with number formatting in a formula, the spreadsheet

may automatically apply the same number formatting to the new cell. For
example, if you use a value with currency formatting in a formula, the
calculated value will also use the currency number format.

▶ If you want your data to appear exactly as entered, you'll need to use the
text number format. This format is especially good for numbers you don't
want to perform calculations with, such as phone numbers, zip codes, or
numbers that begin with 0: for example, "02415". For best results, you may
want to apply the text number format before entering data in those cells.

To learn more about applying number formatting in a specific spreadsheet

application, review the appropriate lesson from our tutorials below:

▶ Excel 2007-2019

▶ Excel 2003

▶ Google Sheets

Let's practice!

Question 1 of 1
What format is best for things like ID numbers, ZIP codes, and phone numbers?

Currency Date

Percentage Text

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 47/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Submit

Lesson 9: Practice Reading Formulas

Practice reading formulas

If you ever use a spreadsheet that was created by someone else, you'll also need to read
the formulas in that spreadsheet. Reading a formula written by someone else can be
tricky—it may not be immediately obvious why someone chose a certain formula, or
how it's working. If you're struggling to read a formula, don't panic! Many formulas are
actually less complicated than they appear, but it may take some time and e ort to
figure out how they work.

With that in mind, we've put together a list of tips to help you read formulas.

You may also find the tips in our Double-Check Your Formulas lesson to be
helpful.

Determine which cells contain formulas

It's important to remember that any cell can contain a formula, and it won't always be
obvious which cells contain them. You may want to get in the habit of checking the
formula bar to see if the currently selected cell contains a formula.

You can also double-click a cell that contains a formula to see what cells it's referencing.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 48/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

You can also switch to formula view, which will display all of the formulas in the
spreadsheet. This can help you understand how the spreadsheet is put together and
where the formulas are stored. Just hold the Ctrl key and press ` (grave accent). The
grave accent key is usually located in the upper-le corner of the keyboard. Press Ctrl+`
again to switch back to normal view.

Try to understand what the formula is doing

Whenever you read a formula, you'll need to think about why that formula was used,
and what it's doing in the spreadsheet.

Let's practice!

Question 1 of 1
What is the formula in cell B3 doing?

Subtracting inventory costs from the

Finding the average budget total
total budget

Multiplying inventory costs by the Subtracting the total budget from

total budget inventory costs

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 49/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Submit

The same is true for functions. For example, let's take a look at this spreadsheet—a
running log our friend Sophie created:

It looks like Sophie didn't label the calculated value in cell B8. However, if we take a look
at the formula we can see that she used the AVERAGE function to find the average of the
values in cell range B3:B7. Based on the data in column B, we can reasonably guess that
she used this formula to find the average distance for these di erent runs.

Let's practice!

Question 1 of 1
In this example, what information would this
formula find:

=MAX(B2:B6)

Shortest distance Longest distance

Average distance Total distance

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 50/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Submit

Think about the order of operations

Remember the order of operations from math class? If not (or if you want a refresher),
you can check out our Complex Formulas lesson. Your spreadsheet will always use this
ordering, which means it doesn't just calculate a formula from le to right. If you're not
sure how a formula is working, try walking through the order of operations to figure out
how it's calculating a value.

Let's practice!

Question 1 of 1
In this example, what's wrong with the
formula in cell B5?

It should use division instead of

Nothing. This formula is correct.
multiplication.

Without parentheses, multiplication is It should use subtraction instead of

performed too early. addition.

Submit

It's also important to remember that whenever a formula contains a function, the
function is generally calculated before any other operators, like multiplication and
division. For example, in the formula below, the SUM function will be calculated before
division:

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 51/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

If one function is nested inside another function, the innermost function will be
calculated first. For example, in the formula =WORKDAY(TODAY(),3), the TODAY
function will be calculated first.

You can review our lesson on Functions to learn more about how functions
work within formulas.

Let's practice!

Question 1 of 1
Which function will be performed first in this formula?

=IF(SUM(C2:C4)>500,"Yes","No")

SUM IF

Submit

Lesson 10: Interactive: Formula Flashcards

Interactive: Formula Flashcards

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 52/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Let's briefly review what we have learned in the past few lessons. Then, you'll answer a
few questions to test your knowledge.

Simple Formulas
Simple formulas combine relative cell references and mathematical operators to
perform helpful calculations.

Let's practice!

Question 1 of 1
Select the formula that will subtract the
inventory costs from the monthly budget.

=B2-B3 =B3*B2

=B3-B2 =B3/B2

Submit

Complex Formulas
Like simple formulas, complex formulas combine cell references and mathematical
operators. However, in complex formulas, we usually perform more than one
mathematical operation. The order of operations helps us to determine which operation
to perform first.

Let's practice!

Question 1 of 1
Which formula will subtract the inventory
costs from the annual budget and then

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 53/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

divide by 12?

=B2-B1/12 =B2-(B1/12)

=(B1-B2)/12 =(B2-B1)/12

Submit

Functions
A function is a predefined formula that performs calculations using specific values in a
particular order. The first step, however, is determining which function to use. To review
the di erent types of functions, you can check out our Functions lesson.

Let's practice!

Question 1 of 1
Which formula would find the fastest time
for this running log?

=AVERAGE(B2:B6) =MIN(B2:B6)

=MAX(B2:B6) =SUM(B2:B6)

Submit

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 54/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

The second step to using a function is determining the correct arguments. Arguments
determine which cells are analyzed by a function.

Let's practice!

Question 1 of 1
In this example, which formula would find
the average time for a 3-mile run?

=AVERAGE(B2:C6) =AVERAGE(B2,B6)

=AVERAGE(B2:B6) =AVERAGE(B2:B5)

Submit

Now that you know the basics of formulas and functions, you're ready to learn some
more advanced Excel formulas!

Lesson 11: Over-Budget Items

"Hey, buddy. What's happening? So…

as you know, we're preparing our
monthly budget report.
Could you tell me which of our expenses are over budget?
You can probably use our budget spreadsheet to figure it
out. Thanks!"

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 55/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. As you can see, our coworker Carter has given us a pretty
simple budget: Each category is on a separate row, and there are columns for the
projected costs and actual costs.

What are we trying to find out?

Before we try to create our formulas, let's think about the question we're trying to
answer: "Which expenses are over budget?"

How do we know if something is over budget? In this example, we can compare the
projected and actual costs. If the actual cost is higher than the projected cost, then it is
over budget.

Writing the formula

Now we just need to decide how to represent this idea in a formula. One way to compare
two values is to subtract one from the other. In our example, we could simply subtract
the actual cost from the projected cost.

If the result is negative, then we know that category is over budget. In our example, we
expected to spend $14,000 on Facilities, and we actually spent $15,000. If we subtract
$15,000 from $14,000, the result would be -$1,000 (negative $1,000). We could also say
this category is $1,000 over budget.

Our projected expenses are in column B, and our actual expenses are in column C. So for
the first category, the formula to compare these values would be:

=B2-C2

Let’s type this formula into cell D2:

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 56/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Next, we can just drag the fill handle down to add the formula to the other cells in the
column:

Now we can see that several of the categories have negative values, which means they
are over budget.

"Hmm… this works, but it's hard to tell

which items are over budget at a
glance…
…Do you think you could make the over-budget items stand
out more?"

Carter is right: Although this spreadsheet gives us an answer, it isn't very easy to read.
There are a few di erent ways we could make the over-budget items more noticeable.
For example:

▶ We could filter the data to only show rows that have a negative result.

▶ We could add conditional formatting to make the negative cells red.

▶ We could select the data and create a chart, which will make the data more
visual.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 57/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

All of these solutions will work—there isn't a right or wrong answer. You may want to
experiment with di erent methods to see which one you think is the clearest. In our
example, we’ll add conditional formatting.

First, make sure the values in column D are selected. Next, we'll need to create a
conditional formatting rule that looks for cells containing a value less than zero. The
process will vary depending on which spreadsheet program you're using:

▶ For Excel 2007-2019: From the Home tab, select Conditional Formatting >
Highlight Cells Rules > Less Than.

▶ For Excel 2003 and earlier: Go to Format > Conditional Formatting.

▶ For Google Sheets: Go to Format > Conditional Formatting.

Make sure the rule is set to "Less than" and that the value is set to "0" (to look for cells
that are less than zero), and choose a formatting option that will stand out. In this case,
a red background with red text is a good choice.

If you're using Microso Excel, another option would be to apply a

Currency number format that will make the negative numbers appear as

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 58/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

red text.

That's it! Now we can easily see at a glance which items are over budget.

"Hey hey, this is great!

I'm going to show this to the Accounting department. The
formatting really makes it easy to see where we need to cut
back on spending. Which…looks to be just about
everywhere this month. Anyway, thanks again!"

Lesson 12: Percent-Off Sale

"Hey, could you help me out? I want to

buy some new stuff for my bike, and
there's this great sale today—
everything is 15% off.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 59/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Can you help me figure out how much it will cost a er the
discount? I added the basic info to a spreadsheet if that
makes it any easier."

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. Here, we have two main columns: one for the item name, and
another for the item price. There's also a formula in cell B5 that calculates the original
total cost of the items: =SUM(B2:B4)

What are we trying to figure out?

Let's think about the problem we're trying to solve. We want to know the total cost a er
the discount. A discount is a percentage of the original price. In this example, the
discount is 15% of the original total.

To make this problem easier to solve with a spreadsheet, we'll break it down into two
steps:

1 Find the discount, which is 15% of $78.77.

2 Subtract that value from the original total to find the final cost.

Step 1: Find the discount

We know that we need a formula that will calculate 15% of $78.77. Whenever you see the
phrase "of something" in a percentage problem, it's a hint that you need to multiply.
So we'll need to multiply $78.77 by 15% to find the discount. When you're using
percentages within formulas, you'll want to write them as decimals.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 60/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Let's practice!

Question 1 of 1
How would we type 15% as a decimal?

0.15 0.015

1.5 15

Submit

Excellent! Now that we know how to write 15% as a decimal, we're ready to write our
formula:

=B5*0.15

We'll type this formula in cell B6 and then press Enter:

And here's our answer: The discount is $11.82.

Step 2: Subtract the discount from the original total

Now that we've calculated our discount, we'll subtract that value from the original total.
Here's our formula:

=B5-B6

We'll type this formula in cell B7 and press Enter:

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 61/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

The final price a er the discount will be $66.95.

"So, I'll save about $12?

Nice! I figured it'd be around that much, but I always like to
know exactly how much I'm spending! Thanks so much—
and let me know if you want to hit the bike trail soon!"

Bonus Section
In the example above, we found the discount and then subtracted that from the original
total. To make this simpler, we broke the problem down into two smaller steps.
However, if you're comfortable working with percentage problems, we could have found
the same answer with only one formula.

Can you figure out which formula we could have used instead? Here's a hint: Because we
know we'll get 15% o , you could also say the final cost would be 85% of the original
total.

Let's practice!

Question 1 of 1
Which formula would calculate the price a er a 15% discount?

=B5*0.85 =B5/0.85

=B5-0.15 =B5*0.15

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 62/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Submit

Lesson 13: Basketball Team: Player Statistics

"Hey! There you are! So, I've been

keeping track of my daughter's
basketball team with a spreadsheet…
…and I want to help the coach calculate some di erent
stats. I've recorded how many points each player scored
during each game. Could you help me figure out who's
scored the most points overall?"

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. You may want to take a moment to see how the data is
organized. As you can see, there is a di erent row for each of the players and di erent
columns for the number of points they scored in each game.

What are we trying to figure out?

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 63/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Before we write our formulas, let's think about the problem we're trying to solve. Our
friend wants to find out which player scored the most points total over these five
games.

Let's practice!

Question 1 of 1
Which function could we use to find the total points scored?

AVERAGE TOTAL

SUM COUNTA

Submit

Writing the formula

Now that we know what function to use, we'll start by calculating the total points scored
for Tonya in row 2.

Before we write the formula, we'll need to decide what cell range to include in the
argument.

Writing the formula

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 64/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Now that we know what function and cell range to use, we're ready to enter our
formula, =SUM(B2:F2), in cell G2:

It looks like Tonya scored 51 points total. Next, we'll drag the fill handle down to add this
formula for the other players.

There we go! It looks like Bella scored the most points overall. However, we could take it
a bit further to make our data really stand out. For example, we could:

▶ Apply conditional formatting to highlight the highest values

▶ Create a chart to visually compare the results

▶ Sort the results

There's really no right answer in this case—the most important thing is to make your
data clear and easier to understand. In this example, it might be especially helpful to
sort the data by the total points scored from highest to lowest—this will show who the
top scorer is and make it easier to rank the other players on the team. Sorting works a
bit di erently, depending on which spreadsheet program you're using:

▶ For Excel 2007-2019: Select column G, then click the Descending Sort (Z-
A) command on the Data tab.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 65/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

▶ For Excel 2003 and earlier: Select column G, then click Data > Sort >
Descending.

▶ For Google Sheets: Select column G, then click Data > Sort sheet by
column G > Descending.

OK, now it's really easy to read our results! We can tell at a glance that Bella is the
highest scorer, and we can also rank the other players.

"Wow! Thank you!

You know, I'd love to calculate some other statistics so I can
tell each of the players about something they've
accomplished. Could you help me figure out how many
points each of the players scored in their best-scoring
game?"

Highest-scoring game for each player

Now we'll need to answer this question: How many points did each of the players score
in their best-scoring game?

Let's practice!

Question 1 of 1
https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 66/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Which function could we use to find the highest-scoring game?

MIN BEST

MAX MVP

Submit

Now that we know what function to use, we'll start by finding the highest-scoring game
for Bella in row 2.

Before we write our formula, we'll need to decide what cell range to include in the
argument.

Let's practice!

Question 1 of 1
Using the example above, what cell range should we use for our function?

B2:F2 2

B2:F9 B2:G2

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 67/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Submit

Go to the next page to see our formula in action!

Highest-scoring game
Now that we know what function and cell range to use, we'll type our formula,
=MAX(B2:F2), in cell H2 and then use the fill handle to add it to the other cells in
column H.

Now we can easily see how many points each of the players scored in their highest-
scoring game!

"Say, this is just great!

It's interesting to see that Hope had the highest-scoring
game, even though she didn't score the most points overall.
And every player scored at least 10 points in one of their
games, which is something they can all be proud of! I can't
wait to show this to the team. Thanks again!"

Lesson 14: Movie Tickets

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 68/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

"Hey, could you help me out here?

I'd like to take three of my friends to
see a movie—that includes you, of
course!
I also don't want to spend more than $50.00. The
tickets cost $11.75, so I want to know how much
money I'll have le over for drinks and popcorn a er
buying the tickets. I'll bet you could use a spreadsheet
to crunch the numbers."

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. We'll just need to enter a few di erent values to get started.
Our friend Darien told us that a ticket costs $11.75, so we'll enter that in cell B1. He'll
need 4 tickets (one for himself and three for his friends), so we'll enter 4 into cell B2.
Finally, Darien said he didn't want to spend more than $50.00, so we'll enter that in cell
B3.

How can we solve this problem?

Darien wants to know how much money he'll have a er buying the tickets. To simplify
the problem, we're going to break it down into two steps:

1 Figure out how much the tickets will cost.

2 Subtract that amount from the total budget to find our answer.

Writing the formulas

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 69/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Let's start by calculating the cost of the tickets. To find the answer, we'll multiply the
cost of the tickets by the number of tickets needed. Those values are stored in cells B1
and B2. So here's our formula:

=B1*B2

We'll type this formula into cell B4:

OK, the tickets will cost $47.00. Now, we'll subtract that value from the total budget in
cell B3. Here's our formula:

=B3-B4

We'll type this formula into cell B5:

So if Darien buys 4 regularly priced tickets, he'll have $3.00 le over for popcorn and
drinks.

"Whoa—only $3.00!? I won't be able

to buy any extra snacks with that!!
You know, there's also a matinee show for this movie,
and the tickets only cost $7.25. How much would I have
le over if we went to the matinee instead of the
evening show?"

Since we used cell references to write our formulas, we can simply change the ticket cost
in cell B1 from $11.75 to $7.25—our formulas will then recalculate automatically.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 70/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

OK, Darien will have $21.00 le over if he chooses to go to the matinee showing of the
movie! Let's send this back to him.

"Cool—thanks for doing this!

I was thinking the matinee would be less expensive,
but I didn't realize just how much I'd save overall! I
hope you like action movies because one of these
tickets has your name on it!"

Bonus Section
In the example above, we used two di erent formulas to make the problem easier to
solve. But if you're already familiar with the order of operations, you might have realized
that we could have written it as one formula. In the interactive below, see if you can
select a formula that would do this.

Let's practice!

Question 1 of 1
Which formula would calculate the le over
amount?

=B3+B1*B2 =B3-B1/B2

=B3-B1*B2 =B1*B2-B3

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 71/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Submit

This formula gives us the same answer as the example above: $21.00. As you may
remember from the lesson on Complex Formulas, spreadsheets use the order of
operations to determine which operations to perform first in a formula. Because
multiplication comes before subtraction, this formula will first multiply the values in
cells B1 and B2 and then subtract that value from the budget total in cell B3.

Of course, it's up to you to determine how you want to solve problems in a spreadsheet.
However, as you become more confident with using the order of operations, you may
find it easier to write only one formula to solve complex problems, as in the example
above.

Lesson 15: Interactive: Pizza Party Panic

Interactive: Pizza Party Panic

Ready to use formulas to solve a real-life problem? Click Start to play through the
interactive below!

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 72/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

If you want to try another interactive scenario, check out Birthday Budget
Blues.

Lesson 16: Interactive: Birthday Budget Blues

Interactive: Birthday Budget Blues

Ready to use formulas to solve a real-life problem? Click Start to play through the
interactive below.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 73/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

If you want to try another interactive scenario, check out Pizza Party Panic.

Lesson 17: Using the IF Function

"Hey! So as you know, we recently

completed our semi-annual fundraiser.
If anyone gave at least $50, they'll receive a free tote bag. I
was going to type Free Gi for each person who donated at
least $50, but I'm really busy today. Do you think you could
handle this for me?"

Our spreadsheet
https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 74/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. As you can see, our coworker Jordan has already added a ton
of information—there are several di erent columns, and each row contains the contact
information for one person, along with the amount of money that person donated.

How can we solve this problem quickly?

So if a person donated at least $50, we need to write the words Free Gi in that donor's
row. We could go through each row, look at the value, and then write Free Gi if the
value is at least $50. But there are a lot of people on the spreadsheet, so it could be really
time consuming to do this manually. What we really need is way to make our
spreadsheet do this automatically.

While you might never have used it before, the IF function can simplify these kinds of
problems. The IF function can look at a value in a cell to see if it meets a basic condition
(in our example, if the value is greater than or equal to 50). Then it can then add text
to an empty cell. Here, we can use the IF function to look at each person's donation,
then add the words Free Gi if they donated at least $50. Basically, the IF function can
do the same thing we were planning to do, only it can do it much faster.

Prepare the spreadsheet

If you've never used the IF function before, don't worry—it's not complicated, although
the syntax is a bit di erent from other formulas you may have used. Before you get
started, it's best to decide where the results from the function will go. In our case, we'll
go ahead and insert a new column to the right of column G.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 75/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Writing the function

Now that we have our new column, we'll start building our formula. We'll need to have a
formula on each row, so we'll start by typing the formula in cell H2. As with any formula,
you'll start with an equals sign (=). Then type the function name, followed by an open
parenthesis. So far, it should look like this:

=IF(

Now, we'll add our arguments. The arguments will tell the IF function what to look for
and what to write in the cell depending on the outcome.

The first argument sets up the condition we want to test. For this formula, we want to
know if the value in cell G2 is greater than or equal to 50. We'll use some
mathematical operators ( > for greater than and = for equals ) to write the condition:

=IF(G2>=50

The next argument will tell the function what to write if the condition is true (that is, if
the value is greater than or equal to 50). This means the person will be getting a free gi ,
so we'll have it write Free Gi . Because this argument is text, we'll need to put it in
double quotes (" "). And, as with any function, we'll need to use a comma to separate
each argument:

=IF(G2>=50, "Free Gi "

The final argument will tell the function what to write if the condition is false. This
means the person won't get a free gi . We could make the function write something like
None or No, but in this case we actually just want the cell to remain blank. To do this,
we'll just type double quotes ("") with no text inside. This is also our last argument, so
go ahead and close the parentheses:

=IF(G2>=50, "Free Gi ", "")

OK, you're done! When you press Enter, the words Free Gi should appear in the cell.

Now we can just drag the fill handle down to add the formula to the other cells in
column H:

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 76/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Excellent—our function is working perfectly! If we wanted to go a step further, we might

sort or filter the results to show only those donors who earned the free gi . For now, it
looks like we're ready to send this back.

"Perfect! This is exactly what I wanted!

I don't know how you did it so fast, but it really saves me a
lot of trouble. You know, I think we probably have a spare
tote bag if you want one for yourself!"

Bonus Section

Let's practice!

Question 1 of 1
Let's say our company is based in North Carolina and we want to know which of our donors are
local. Which of the following IF functions would place the word "Yes" for donors from North
Carolina and "No" for those who live out of state?

=IF(E2=NC, "No", "Yes") =IF(E2="NC", "Yes", "No")

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 77/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

=(IF E2=NC, "No", "Yes") =IF("NC", "Yes", "No")

Submit

Lesson 18: Using CONCATENATE to Combine Names

"Hey, what's up? So, I know we were

supposed to grab some Thai food
tonight, but I'm stuck at work.
My boss wants me to copy a list of names from a
spreadsheet to a text document. But when I copy and paste,
the formatting ends up all weird. I think it'd work better if
the first and last names were in the same column, but it'll
take forever to type all that."

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. It looks like we have a list of contact information. Each person
has his or her own row, and there are columns for each person's first name, last name,
and other contact information.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 78/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

How can we solve this problem quickly?

We want the information from the Last Name and First Name column to appear together
in the same cell, but it would take a long time to type everything by hand. Rather than
combining this data manually, we can use the CONCATENATE function to do it
automatically.

How it works
The word concatenate is just another way of saying "to combine" or "to join together".
The CONCATENATE function allows you to combine text from di erent cells into one cell.
In our example, we can use it to combine the text in column A and column B to create a
combined name in a new column.

Before we start writing the function, we'll need to insert a new column in our
spreadsheet for this data. In our example, we'll insert it to the right of column B.

Writing the function

We're ready to enter our function into cell C2. As usual, we'll start with the equals sign
(=) followed by the function name and an open parenthesis:

=CONCATENATE(

In the current version of Excel, you can use the new CONCAT function
instead of CONCATENATE. The two functions work the same way.

Now we're ready to enter our arguments. The arguments tell the CONCATENATE function
what cells to combine. In our example, we want to combine the text in cells A2 and B2,
so we'll make each of those an argument:

=CONCATENATE(B2, A2)

OK, let's run this function!

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 79/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

You may have noticed that the first and last names don't have a space in between them.
That's because CONCATENATE will combine exactly what you tell it to combine, and
nothing more. If you want punctuation, spaces, or any other details to appear in the
cell, you’ll need to tell CONCATENATE to include it.

To add a space, we can simply add another argument: " " (two double quotes around a
space). Make sure the three arguments are separated by commas:

=CONCATENATE(B2," ",A2)

Perfect! Now we can use the fill handle to copy the formula to the remaining cells in this
column.

That's it! We're ready to hand this back to our friend:

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 80/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

"Hey, this is sweet! That was so easy,

and now I can copy and paste these
names as a single column.
Thanks for helping me figure this out so fast! Your Pad Thai
is on me tonight!"

Bonus Section

Let's practice!

Question 1 of 1
In this example, what would be returned if
we put the following function into a cell?

=CONCATENATE(A2, " ", C2)

Hohenstein Bingo Wa les Hernandez

Morales Wa les Wa les, Hernandez

Submit

Lesson 19: Buying a Printer: Cost Comparison

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 81/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

"Hey hey! So, we're buying a new

printer for the office, and we're trying
to decide between these two: the Nidi
2600 and the Ace X1.
Can you help us figure out which one would be cheaper in
the long run? Keep in mind that we probably won't buy
another printer for at least three years. Also, we're printing
an average of about 12,000 sheets per year."

Building the spreadsheet

It looks like our coworker Carter has already done some research and collected
information about each of these printers:

This is all useful information, but it's not very consistent or well organized. In order to
compare these two printers, we'll need to make sure our spreadsheet organizes the data
consistently, which means we'll need to move some things around.

If you download our spreadsheet, you'll see that we've rearranged the printer cost,
cartridge cost, and number of sheets per cartridge so we have a clear, side-by-side
comparison of the two printers. We've also added the total number of sheets printed per
year, since we'll need to use that value in our formulas.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 82/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

See how this format makes it easy to compare the di erent information for each printer?
Now that we have our spreadsheet organized, we're ready to start solving this problem.

How can we answer this question?

We'll use the spreadsheet to answer this question: Which printer will be less expensive
over the course of three years? The cost of each printer will be determined by two
things:

▶ The original cost of the printer

▶ The cost of toner cartridges needed over a three-year period

Writing the formulas

We already know the original costs of the printers—those values are stored in cells B2
and C2. This means we'll just need to figure out the total cost of the cartridges over
three years. There are many di erent ways to go about this, but to make it simpler we'll
break it down into multiple steps.

First, we'll calculate how many toner cartridges we'd need to purchase each year for
each printer. We know the o ice prints about 12,000 sheets of paper per year (that
value is stored in cell F2). If we divide that by the number of sheets per cartridge, we can
determine how many cartridges we'll need over one year. Here are our formulas:

▶ In cell B5, type =$F$2/B4

▶ In cell C5, type =$F$2/C4 (Note: You can also just copy and paste the
formula from cell B5.)

OK, it looks like we'd need to purchase 24 cartridges over one year for the Nidi and only
12 for the Ace.

Next, we'll calculate how much money we'll spend each year on cartridges. Because
we just calculated the number of cartridges, we just need to multiply that value by the
cartridge price:

▶ In cell B6, type =B5*B3

▶ In cell C6, type =C5*C3 (or copy the formula from cell B6)

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 83/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

It looks like cartridges would cost $420 each year for the Ace and $600 each year for the
Nidi. Next, we'll multiply those values by 3 to determine the cost for three years. Here
are our formulas:

▶ In cell B7, type =B6*3

▶ In cell C7, type =C6*3

Finally, we'll add the printer cost and the cartridge cost over three years to determine
the total cost over three years for each printer. Here are our formulas:

▶ In cell B8, type =B7+B2

▶ In cell C8, type =C7+C2

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 84/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

And there's our answer! As you can see, the Ace X1 is cheaper than the Nidi 2600 in the
long term, even though the upfront costs for the Nidi are much lower. And because the
yearly cartridge cost for the Ace X1 is much lower, we'll continue to save money the
longer we keep this printer.

"Excellent work here! I didn't expect

the Ace to be such a good deal…
…but I guess the savings from those bigger toner cartridges
really add up over time! Maybe we can have a pizza party
with the extra money we'll save!"

Lesson 20: Invoice, Part 1: Free Shipping

"Howdy! I heard that you're good with

spreadsheets, so I thought I'd ask for a
favor.
You probably know that we currently charge $5.99 for
shipping on all of our orders. Well, we're going to start
o ering free shipping if the subtotal of an order is at least
$100. Do you think you could update our invoice to calculate
this automatically?"

This lesson is part 1 of 5 in a series. The other lessons in this unit will build
on the same example and add various types of functionality. Before moving
on, you'll want to make sure you are comfortable creating formulas. If you'd
like more practice, you can work through some of the previous Excel
Formulas lessons.

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. It looks like we have an order invoice. There's information

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 85/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

about the order, along with some formulas to calculate the tax, order subtotal, and total
order cost.

How can we solve this problem?

If the subtotal for an order is at least $100, the shipping should be free. If not, it should
cost $5.99. We could use the IF function to solve this problem. As you may remember
from our lesson on Using the IF Function, we can use it to test a condition and then
write something in a cell depending on the result. In this example, the IF function can
tell us whether the subtotal is at least $100 and then set the shipping cost depending
on the result.

Writing the function

▶ The first argument sets up the condition the IF function will test: In this
example, if the value in cell D6 is greater than or equal to 100. So our first
argument will be D6>=100.

▶ The second argument tells the function what to write if the condition is
true. In this example, if the value is greater than or equal to 100, we want it
to write "0" in the cell. So our second argument will be 0.

▶ The third argument tells the function what to write if the condition is false:
In this example, if the value is less than 100, we want it to use the value
from cell G3 (5.99). So our third argument will be $G$3 (we'll use an
absolute reference here just in case we move this formula in the future).

Here's our function: =IF(D6>=100,0,$G$3). We'll enter this function into cell D7.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 86/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

If you entered the function correctly, the shipping cost should change from $5.99 to
$0.00. That's because the Order Subtotal is more than $100.

If you want to test the function, change the value in cell C2 from 2 to 1. Because the
Order Subtotal is now less than $100, the Shipping cost should change from $0.00 to
$5.99.

OK—our function is working really well! We're ready to send this back!

"Perfect-o! Thanks for doing this!

This kind of automation is going to make everything a lot
simpler. I'll be sure to send all of my complicated
spreadsheet problems your way from now on."

If you'd like to continue on to the next part in this series, go to Invoice, Part
2: VLOOKUP.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 87/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Lesson 21: Invoice, Part 2: Using VLOOKUP

"Say…you know, you did such a nice

job figuring out that free shipping
formula, I thought I'd throw this your
way.
We're starting to process a lot more invoices these days. It
gets tedious to look up the product name and price from
the Products worksheet a er adding the product ID. Do you
know if there's a way to have our spreadsheet pull in this
info automatically?"

This lesson is part 2 of 5 in a series. You can go to Invoice, Part 1: Free

Shipping if you'd like to start from the beginning.

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. It looks similar to the invoice from the last lesson, but now we
have two di erent worksheets: one for the Invoice, and one for the Products.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 88/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

What are we trying to do with this spreadsheet?

Our coworker asked if we could use the Product ID number to find the the product name
and price from the Products worksheet. Luckily, the VLOOKUP function can do this
automatically. If you've never used VLOOKUP before, don't worry—we'll show you how
to use it below.

VLOOKUP is a bit more advanced than some functions, so you'll need to be

familiar with functions and cell references before you begin. You can review
our lessons on Functions and Relative and Absolute Cell References to
learn more.

How it works
Before we start using VLOOKUP, it will be helpful to know what it does. In our example, it
will search for the Product ID number on the Products worksheet. It first searches
vertically down the first column (VLOOKUP is short for "vertical lookup"). When it finds
the desired product ID, it moves to the right to find the product name and product
price.

Before we write our function, we'll need to take a moment to think carefully about each
argument and collect some information from our spreadsheet. The arguments will tell
VLOOKUP what to search for and where to look. We'll need to use four arguments:

1 The first argument tells VLOOKUP what to search for. In our example,
we're searching for the product ID number, which is in cell A2, so our first
argument is A2.

2 The second argument is a cell range that tells VLOOKUP where to look for
the value from our first argument. In our example, we want it to search for
this value in cell range A2:C13 on the Products worksheet, so our second
argument is Products!$A$2:$C$13. Note that we're using absolute

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 89/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

references so this range won't change when we copy the formula to other
cells.

3 The third argument is the column index number, which is simpler than it
sounds: The first column in the cell range from the previous argument is 1,
the second column is 2, and so on. In our example, we're looking for the
Product Name. The names are stored in the second column of the cell
range from the previous argument, so our our third argument is 2.

4 The fourth argument tells VLOOKUP if it should look for approximate or

exact matches. If it is TRUE, it will look for approximate matches. If it is
FALSE, it will look for exact matches. In this example, we're only looking for
exact matches, so our fourth argument is FALSE.

It's important to know that VLOOKUP will always search the le most
column in the cell range. Since our cell range is A2:C13, it will search
column A.

Writing the function

Now that we have our arguments, we'll write our function in cell B2. We'll start by typing
an equals sign (=), followed by the function name and an open parenthesis:

=VLOOKUP(

Next, we'll add each of the four arguments, separate them with commas, then close the
parentheses:

=VLOOKUP(A2, Products!$A$2:$C$13, 2, FALSE)

If you entered the function correctly, the product name should appear: Measuring cups.
If you want to test your function, change the Product ID number in cell A2 from MEA7879
to CHE7888. The product name should change from Measuring cups to Cheesecloth.

Note: Be sure to type the product ID exactly as it appears above, with no

spaces. Otherwise, the VLOOKUP function will not work correctly.

Adding the product price

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 90/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Next, we also want the Product ID to pull in the product price, so we'll use the
VLOOKUP function again. Since we're using the same data, this function will be very
similar to the one we just added. In fact, all we have to do is change the third argument
to 3. This will tell VLOOKUP to pull in the data from the third column, where the product
price is stored:

=VLOOKUP(A2, Products!$A$2:$C$13, 3, FALSE)

OK, let's enter our new formula in cell C2:

We've got our formulas working, so we can just select cells B2 and C2 and then drag
the fill handle down to copy the formulas to the other rows in the invoice. Now, each
row is using VLOOKUP to find the Product Name and Product Price.

OK—our functions look good. Let's send this back!

"Excellente! This is going to save the

invoice team a boatload of time each
day.
You're really a whiz with this kind of stu ! Thanks a bunch!"

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 91/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Bonus Section
Let's say a veterinarian's o ice is creating a spreadsheet to look up patient information.
Here's the patient directory. This is where information will be pulled from:

Here's the patient lookup sheet. This is where the function will be inserted.

Let's practice!

Question 1 of 1
Which of the following VLOOKUP functions could be inserted into cell B4 of the Lookup sheet in
order to insert the pet type?

=(VLOOKUP $B$1, 4, False, =VLOOKUP(Directory!$A$1:$H$150, 4,

Directory!$A$1:$H$150) $B$1, False)

=VLOOKUP($B$1, =VLOOKUP(Directory!$A$1:$H$150,
Directory!$A$1:$H$150, 4, False) $B$1, False, 4)
https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 92/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Submit

If you'd like to continue on to the next part in this series, go to Invoice, Part
3: Fix Broken VLOOKUP.

Lesson 22: Invoice, Part 3: Fix Broken VLOOKUP

"Hey, it's me again…so, I think I might

have broken that VLOOKUP formula
you created.
I put a few new products into the Products worksheet, but
the VLOOKUP function is having trouble pulling in the new
data. Could you take a look and see if you can fix it?"

This lesson is part 3 of 5 in a series. You can go to Invoice, Part 1: Free

Shipping if you'd like to start from the beginning.

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. The VLOOKUP function in row 2 looks good, but row 3 doesn't
seem to be working. And since the formulas in column E depend on the values in row 3,
they're not able to calculate correctly.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 93/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

What are we trying to fix?

Let's see if we can figure out what went wrong. Our coworker added some new products
to the Products worksheet, but they don't work with the existing VLOOKUP functions. If
we look at the Products worksheet, we can see that there are a few new products listed
here. The data spans cell range A2:C16.

But if we look at the function in cell B3, we can see that the VLOOKUP function is only
searching for information in cell range A2:C13.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 94/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

As you can see, the VLOOKUP function isn't searching the full list of products; it's only
searching down to row 13. So it has no trouble finding the Ramekin, but it won't be able
to find the Zester, Grater, or Wire whisk.

To fix this, we'll need to change the second argument in VLOOKUP so it searches the
entire product list. There are two ways to do this:

▶ Update the cell ranges

▶ Use a table (Microso Excel only)

Update the cell ranges

One way to fix this problem is to simply update the cell ranges in our VLOOKUP
functions. Let's try editing the function in cell B3, where it's stopped working.

We'll edit the function to include all of the cells in the Products table. And just in case
more products are added later, we'll increase the number of rows in the cell range to
100. This means we're including a lot of empty rows, but that's OK—the VLOOKUP
function will still work the same way. So here's our new function:

=VLOOKUP(A3, Products!$A$2:$C$100, 2, FALSE)

The function will recalculate, and the correct product name should appear: Zester. We'll
also make the same correction to the remaining VLOOKUP functions in the spreadsheet:

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 95/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

And that's it! Now our spreadsheet's working correctly, and it'll be harder to break these
functions in the future.

Use a table
Above, we fixed our VLOOKUP function by extending the cell range to row 100. However,
if we continue adding more products, we will eventually break the VLOOKUP function
again, so this is not a perfect solution. If you're using Microso Excel, a much better
solution is to use a table. This will ensure that any new data will be included
automatically.

Tables work with all recent versions of Excel, but they are not currently
available in Google Sheets.

First, we'll select our product information and then convert it to a table. Note that this
will work a bit di erently depending on which version of Excel you're using:

▶ For Excel 2007-2019: Go to Insert > Table.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 96/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

▶ For Excel 2003 and earlier: Go to Data > List > Create List (tables were
known as lists in Excel 2003 and earlier).

Next, we'll name the table. In this example, we'll give it the name: ProductsList. Note
that table names cannot contain spaces.

Now let's go back to our VLOOKUP function. Rather than telling VLOOKUP to search in a
specific range on the Products worksheet (Products!$A$2:$C$100), we can simply type
the name of the table (ProductsList).

So here's our new function:

=VLOOKUP(A3, ProductsList, 2, FALSE)

If anyone ever adds information to the table, the VLOOKUP function will include it
automatically—no need to worry about updating the cell range later on. Again, you'll
need to update all of the VLOOKUP functions in the spreadsheet to refer to the table
rather than the cell range.

"It's working again? Fantastico!

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 97/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

I guess I should have realized that adding more products

could break the formula, but I'm glad that won't be an issue
in the future. Thanks!"

If you'd like to continue on to the next part in this series, go to Invoice, Part
4: More Shipping Options.

Lesson 23: Invoice, Part 4: More Shipping Options

"I know, I know. It's always something

right? Anyway, we're going to start
offering some new shipping options.
I put all of the options into a new worksheet. I remembered
that you were able to use that VLOOKUP function to pull in
the product name and price. Do you think you could do the
same thing for the shipping option?"

This lesson is part 4 of 5 in a series. You can go to Invoice, Part 1: Free

Shipping if you'd like to start from the beginning.

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. It looks similar to our spreadsheet from the previous lesson,
but now there are some new rows for the shipping options, along with a new Shipping
worksheet with di erent shipping options and costs.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 98/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

What are we trying to do?

We'll need to use the Shipping Option (in cell E6) to automatically pull in the Shipping
Cost for cell E7. We can use a VLOOKUP function to do this automatically.

If you've never used VLOOKUP before, you can go to Invoice, Part 2: Using
VLOOKUP to learn the basics.

Writing the function

This VLOOKUP function will actually be similar to the VLOOKUP function we created in
Invoice, Part 2: Using VLOOKUP. We'll start by typing the equals sign (=), followed by
the function name and an open parenthesis:

=VLOOKUP(

Next, we'll add our arguments. The first argument tells VLOOKUP what to search for. In
our example, it will search for the Shipping Option, which we will be typing in cell E6 of
our Invoice.

=VLOOKUP(E6

The second argument tells VLOOKUP where to look for the value in our first argument.
In our example, that's in the Shipping worksheet, in cell range A2:B4.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 99/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

We'll include some extra rows in the cell range in case more shipping options are added
later.

=VLOOKUP(E6, Shipping!A2:B10

Finally, we'll add our third and fourth arguments. In this example, the Shipping Cost is in
column B (the second column), so our third argument is 2. And because we're only
looking for exact matches, the fourth argument is FALSE.

=VLOOKUP(E6, Shipping!A2:B10, 2, FALSE)

Type the formula into cell E7 of the Invoice and press Enter to see the result.

If you typed the formula correctly, the correct shipping price should appear: $9.99. If you
want to make sure your formula is working correctly, change the Shipping Option in cell
E6 from 3-Day to 2-Day. The Shipping Cost should change from $9.99 to $13.99.

Be sure to type the shipping option exactly as it appears in the worksheet,

or the VLOOKUP function won't work correctly.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 100/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

"Nice work here! But…I just

remembered that we'll still be offering
free standard shipping on orders over
$100.
So if shoppers spend at least $100, they should get a credit
for the cost of standard shipping ($5.99) toward their total
order cost. Maybe you could just modify the IF function you
added before to do this automatically."

As you may remember from Invoice, Part 1: Free Shipping, we used the IF function to
change the shipping cost to $0 if the cost of the order was at least $100. This time, we'll
do it a bit di erently: If the order is at least $100, we'll give them a credit of $5.99, which
is the cost of standard shipping. This means standard shipping will be free, and the more
expensive shipping options will also get a discount of $5.99.

Just like before, our new formula will look at the Order Subtotal in cell E5 to see if the
value is greater than or equal to $100, so our first argument will be E5>=100.

=IF(E5>=100

The second argument looks to see if the statement in the first argument is true. If it's
true, they'll receive a credit for the cost of standard shipping (5.99). Since the credit will
be subtracted from the total order cost, we'll make it a negative number: -5.99.

=IF(E5>=100,-5.99

The third argument looks to see if the statement in the first argument is false. If it's false,
they won't receive the $5.99 shipping credit. This means our third argument will be 0
(zero). We'll also add a close parenthesis a er the last argument. Here's our new IF
function:

=IF(E5>=100, -5.99, 0)

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 101/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

When you press Enter, the correct discount should appear in cell E8.

If we look at the formula that calculates the Total Order Cost in cell E9, we can calculate
the total value of Order Subtotal, Shipping Cost, and Discount. We'll simply add all
three values; since the Discount is a negative number, that value will be subtracted from
the Total Order Cost.

OK—our formulas look good. Let's send this back!

"Perfect-a-mundo! Thanks as always! I

don't know what we'd do without you!
https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 102/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

I really appreciate your help—our invoice is working like a

well-oiled machine!"

If you'd like to continue on to the next part in this series, go to Invoice, Part
5: Data Validation.

Lesson 24: Invoice, Part 5: Data Validation

"Oh, hey. Um…we noticed an issue

with that new VLOOKUP function you
added for the shipping options.
If we don't type the exact name of the shipping option, the
VLOOKUP function stops working. Think you can fix this?
This is the last thing I'll ask for, I swear!"

This lesson is part 5 of 5 in a series. You can go to Invoice, Part 1: Free

Shipping if you'd like to start from the beginning.

Our spreadsheet
Once you've downloaded our spreadsheet, open the file in Excel or another
spreadsheet application. Cell E7 is using the same VLOOKUP formula we added in
Invoice, Part 4: More Shipping Options, but it's no longer pulling in the Shipping Cost
correctly. And since the formula in cell E9 depends on that value, it's not able to
calculate correctly either.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 103/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

What are we trying to fix?

If we look at cell E6, we can see that the word Standard is misspelled. The Shipping
worksheet doesn't contain the word "Standrd", so our VLOOKUP function doesn't return
any results. While we could simply type in the correct spelling, that wouldn't prevent
someone from making the same mistake in the future. To make sure this doesn't happen
again, we can add data validation to our spreadsheet.

Data validation
Data validation allows you to control exactly what a user can enter into a cell. In our
example, we can use data validation to ensure that the user chooses one of the three
possible shipping options. To make things even easier, we can insert a drop-down list of
the possible options.

This kind of data validation allows you to build a powerful, fool-proof spreadsheet. Since
users won't have to type in data manually, the spreadsheet will be faster to use, and
there's a much lower chance that someone can introduce an error. Depending on what
spreadsheet program you're using, the process of adding data validation will vary. We'll
show you how to add data validation in Microso Excel and Google Sheets.

Data validation in Excel

Since we already have a list of shipping options in the Shipping worksheet, we're going
to tell Excel to use the data in that list to control which values a user can select. But
before we do this, we'll actually need to name the cell range first. Naming cell ranges is
one way to keep track of important cell ranges in your spreadsheet.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 104/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

To name a cell range (Excel 2007-2019):

1 Select the cell range you wish to name. In this example, we'll select the cell
range A2:A10 on the Shipping worksheet (we're including empty rows just
in case more shipping options are added later on).

2 Go to the Formulas tab, and then click Define Name.

3 A dialog box will appear. Type a name for the cell range in the Name: field
and then click OK. In our example, we'll name it ShipRange.

4 Click OK. The cell range will be named.

If you ever add more data to your spreadsheet, you can easily check all of
your named ranges with the Name Manager to make sure they are
including all of the data.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 105/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

To create a data validation drop-down list (Excel 2007-

2019):
1 Select the cell where you want the drop-down list to appear. In our
example, that's cell E6 on the Invoice worksheet.

2 On the Data tab, click the Data Validation command.

3 A dialog box will appear. In the Allow: field, select List.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 106/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

4 In the Source: field, type the equals sign (=) and the name of your range,
and then click OK. In our example, we'll type: =ShipRange.

5 A drop-down arrow will appear next to the selected cell. Click the arrow to
select the desired option. In our example, we'll select Standard.
Alternatively, you can type the shipping option, but Excel will only accept it
if it is spelled correctly.

6 The selected value will appear in the cell. Now that we're searching for the
exact name of a shipping option, our VLOOKUP function is working
correctly again.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 107/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Data validation with Google Sheets

Creating a drop-down list for Google Sheets is actually a little simpler than Excel,
because you don't need to name a cell range beforehand.

To create a data validation drop-down list (Google

Sheets):
1 Right-click the cell where you want the drop-down list to appear and select
Data validation. In our example, we'll select cell E6.

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 108/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

2 A dialog box will appear. Click Select data range in the Criteria: field.

3 Select the data that will appear in the drop-down list, and then click OK. In
our example, the shipping options are stored in cell range A2:A4 on the
Shipping worksheet, but we'll select A2:A10 (we're including empty rows
just in case more shipping options are added later on).

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 109/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

4 Click Save to close the dialog box.

5 A drop-down arrow will appear within the selected cell. Click the arrow to
select the desired option. In our example, we'll select Standard.
Alternatively, you can type the shipping option, but Google Sheets will only
accept it if it is spelled correctly.

6 The selected value will appear in the cell.

There we go—with our new data validation drop-down list, it'll be a lot harder for
someone to break our function in the future!

"Good golly, I didn't even know this

was possible in a spreadsheet!
You know, it's amazing to think about how much this
spreadsheet does now! Some of our competitors pay for
expensive invoicing so ware, but you've created something
really powerful with just a few well-placed spreadsheet
formulas."

Lesson 25: Excel Formulas Quiz

Quiz

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 110/111
5/19/2020 Excel Formulas: Invoice, Part 5: Data Validation

Question 1 of 14
A group of cells is called a ________.

multicell cell cluster

chart cell range

Submit

https://edu.gcfglobal.org/en/excelformulas/invoice-part-5-data-validation/1/ 111/111