Excel Fundamentals

Study Guide
Introduction

Knowing how to use Excel is often listed as a required skill for anyone seeking employment
today and for people that want to take on greater responsibilities within their organizations.
Rather than trying to teach you everything there is to know about Excel, this book is focused on
teaching specific skills that will help you achieve three objectives:
1. Improved Job Readiness – Companies do not always have the time or desire to provide
their employees with computer training. A strong knowledge of how to use Excel
demonstrates that you are ready to take on new responsibilities and make valuable
contributions to an organization as soon as you come on board.

2. Increased Productivity – learning how to get more done in less time is an ongoing
process and one of the greatest investments you can make in your career. This book will
introduce you to dozens of fast and easy ways to use Excel to input data, perform
calculations, create visuals and use shortcut key combinations.

3. Become a Solution Provider – In many cases, organizations are already using Excel for
different purposes; however, there’s always room for improvements and enhancements.
Here are some examples of Excel files you’ll be able to create after learning the skills
described in this book:

• Sales performance reports

• Project timeline cost analysis
• Cost and pricing calculators
• Social media impact reports
• Inventory management reports
• Return on investment calculators
• Principal and interest payment calculators
• Employee expense reports
• Customer acquisition cost analysis
• Sales commission payout reports
• Accounts payable and receivable journals

Excel Fundamentals 2
Table of Contents
Chapter 1: Getting Started ...............................................................................................................7
The Ribbon .......................................................................................................................................7
Activity 1.1: Add a new tab to the Ribbon.................................................................................8
Quick Access Toolbar................................................................................................................ 10
Activity 1.2: Customize the Quick Access Toolbar ................................................................ 10
Organize and View Workbooks ................................................................................................. 11
Twelve Major Parts of an Excel Worksheet .............................................................................. 12
Chapter 2: Entering Data and Formatting Cells ............................................................................ 13
List Boxes .................................................................................................................................. 13
Custom Formats ........................................................................................................................ 14
Activity 2.1: Apply Custom Formats to Numbers ................................................................... 14
Activity 2.2: Apply Custom Formats to Dates ........................................................................ 15
Use Autofill to Enter Data .................................................................................................. 15
Create a Custom Fill Series ....................................................................................................... 16
Adding Comments ..................................................................................................................... 16
Adding Hyperlinks ...................................................................................................................... 17
Editing a Hyperlink ................................................................................................................. 18
Excel Camera Tool .................................................................................................................... 18
Activity 2.3: Using the Excel Camera .................................................................................... 19
Using a Template ...................................................................................................................... 20
Cell Alignment............................................................................................................................ 20
Shortcut Keys and Commands.................................................................................................. 22
Text to Columns .........................................................................................................................23
Activity 2.4: Delimited Files - Space ...................................................................................... 23
Activity 2.5: Delimited Files – Special Characters ................................................................. 24
Activity 2.6: Fixed Width File Types....................................................................................... 24
Chapter 3: Using Cut, Copy and Paste ......................................................................................... 25
Paste Special ..............................................................................................................................25
Activity 3.1: Transpose Data.................................................................................................. 26
Chapter 4: Formulas and Functions .............................................................................................. 29
Chapter 4 Activities ................................................................................................................ 30
Inputting Formulas ..................................................................................................................... 30

3 Excel Fundamentals
 Array Formulas ...........................................................................................................................30
Naming a Cell ............................................................................................................................ 31
Naming a Range of Cells .......................................................................................................... 32
Math Functions .......................................................................................................................... 33
SUM ....................................................................................................................................... 33
SUMIF .................................................................................................................................... 33
SUMIFS ................................................................................................................................. 34
PRODUCT, SUMPRODUCT and QUOTIENT ...................................................................... 35
Financial Functions .....................................................................................................................35
PMT, NPER, RATE and PV................................................................................................... 35
PPMT and IPMT .................................................................................................................... 36
NPV and IRR ......................................................................................................................... 36
XNPV and XIRR .................................................................................................................... 37
CUMPRINC and CUMIPMT .................................................................................................. 37
FV .......................................................................................................................................... 37
Statistical Functions ................................................................................................................... 38
AVERAGE, MIN and MAX ..................................................................................................... 38
COUNTA and COUNTIF ....................................................................................................... 38
Logical Functions ....................................................................................................................... 39
IF ............................................................................................................................................ 39
IFERROR ............................................................................................................................... 40
AND and OR .......................................................................................................................... 41
Date and Time Functions .......................................................................................................... 41
NETWORKDAYS ................................................................................................................... 41
DAYS360 ............................................................................................................................... 42
YEARFRAC ........................................................................................................................... 42
Lookup and Reference Functions (VLOOKUP)..................................................................... 42
HLOOKUP ............................................................................................................................. 43
INDEX and MATCH ............................................................................................................... 44
Text Functions ........................................................................................................................... 45
CONCATENATE (or CONCAT for Excel 2016) .................................................................... 45
LEFT, RIGHT and MID .......................................................................................................... 45
Combine Multiple Functions ...................................................................................................... 46
Creating Links Between Worksheets ........................................................................................ 47
Absolute and Relative Cell References ..................................................................................... 48

Excel Fundamentals 4
 Relative Cell References ....................................................................................................... 48
Absolute Cell References ...................................................................................................... 49
Mixed Cell References ........................................................................................................... 50
Chapter 5: Creating Lists and Tables ........................................................................................... 51
Create a Data List ...................................................................................................................... 51
Enter Data using a Form ........................................................................................................... 52
Activity 5.1: Input data using a Form ..................................................................................... 52
Create a Drop-Down List ........................................................................................................... 53
Activity 5.2: Input data using a drop-down list ....................................................................... 53
Create a Table ........................................................................................................................... 54
Activity 5.3: Format Excel data as a Table ............................................................................ 54
Chapter 6: Introduction to Charts and Visuals .............................................................................. 56
Chapter 6 Activities ................................................................................................................ 57
Column Charts ........................................................................................................................... 57
Column Charts with a Numerical Axis ................................................................................... 59
Line Charts ................................................................................................................................ 60
Line Charts with a Secondary Axis ........................................................................................ 62
Pie Charts .................................................................................................................................. 65
Editing a Pie Chart ................................................................................................................. 65
XY Scatter Charts .......................................................................................................................66
Create a Dashboard using Multiple Charts ............................................................................... 68
Sparkline Charts ........................................................................................................................ 68
Conditional Formatting............................................................................................................... 69
Creating a Legend ................................................................................................................. 71
Working with Themes and Styles .............................................................................................. 71
Add a Picture Background to a Worksheet ............................................................................... 72
Create a Watermark .................................................................................................................. 74
Chapter 7: Sorting and Filtering Data ........................................................................................... 76
Filtering Data ............................................................................................................................. 76
Activity 7.1: Using a Custom AutoFilter ................................................................................. 76
Activity 7.2: Sorting Data ....................................................................................................... 79
Activity 7.3: Using the Subtotal Function ............................................................................... 80
Activity 7.4: Advanced Filters ................................................................................................ 81
Activity 7.5: Outlines and Subtotals ....................................................................................... 84
Add a Filter to the Subtotals .................................................................................................. 87

5 Excel Fundamentals
Chapter 8: What-If Analysis .......................................................................................................... 88
Goal Seek .................................................................................................................................. 88
Activity 8.1: Using Goal Seek ................................................................................................ 88
Activity 8.2: Scenario Manager .............................................................................................. 89
Activity 8.3: One Input Data Tables ....................................................................................... 91
Activity 8.4: Two-Input Data Tables ....................................................................................... 92
Chapter 9: Introduction to PivotTables .......................................................................................... 94
Activity 9.1 Create a simple PivotTable ................................................................................. 94
Activity 9.2: Create a PivotTable Report ............................................................................... 98
Activity 9.3: Calculated Fields ...............................................................................................100
Activity 9.4: Calculated Items ...............................................................................................103
Activity 9.5: Organize Data into Groups ...............................................................................105
Activity 9.6: Organize data by date .......................................................................................106
Activity 9.7: Percent of Total .................................................................................................107
Activity 9.8: Multiple Value Calculations ...............................................................................111
Activity 9.9: Apply Conditional Formatting ............................................................................114
Activity 9.10: Inserting Slicers ...............................................................................................115
Activity 9.11: Report Filter Pages .........................................................................................116
Activity 9.12: Create a PivotChart .........................................................................................118
Index .............................................................................................................................................120

Copyright © 2022 by Inspire Learning Solutions. All Rights Reserved. This work may not be
reproduced, transmitted or photocopied without the expressed written permission from Inspire
Learning Solutions.

Excel Fundamentals 6
Chapter 1: Getting Started

Learning Objectives
After completing this chapter, you should be able to:

1. Identify the major parts of a Ribbon.

2. Add a new Tab to the Ribbon.
3. Add Commands to the Quick Access Toolbar.
3. Identify the major parts of an Excel worksheet.

The Ribbon
When you first open Excel, you’ll see the Ribbon at the top of the Excel window. It provides you with the
tools you need to create and modify whatever data has been entered into the cells.

The Ribbon is at the top of the Excel window and provides you with the tools you need to create what’s
referred to as Workbooks:

The part of the Ribbon you’ll be working with the most

will be the Tabs at the top of the Ribbon. Each Tab has
different Groups; each Group has one or more Buttons.
Groups might also include Launchers, which will display
more tools and buttons.

If you click on the Home tab and then click on the

Launcher in the Font group, it would display what’s
referred to as a Dialog Box called Format Cells.

7 Excel Fundamentals
 Activity 1.1: Add a new tab to the Ribbon

1. Open a New Excel Workbook. Click on the File tab, then click on Options. choose Customize
Ribbon.
2. Click on New Tab.

Let’s create a Tab that has tools that can be used to create a list of data.

3. Click on New Tab (custom) then click on Rename button and name the Tab “Data List”; click on
New Group (custom), then click the Rename button and name the group “Editing Tools”.

Excel Fundamentals 8
 4. With Popular Commands selected from where is says “Choose commands from”, use the Add
button to choose Insert Sheet Columns, Insert Sheet Rows, Delete Sheet Columns and Delete
Sheet Rows.

5. Click OK. You can now see that a Data List tab appears next to the Home tab.

9 Excel Fundamentals
Quick Access Toolbar
The Quick Access Toolbar is another way to get things done in less time. By default, it’s located at the top
of the Ribbon, but you can move it to the bottom to make it easier to see.

Activity 1.2: Customize the Quick Access Toolbar (use the same Excel file from Activity 1.1)

1. Click on the down arrow where it says “Customize the Quick Access Toolbar”; choose Show
Below the Ribbon.

2. From here, you can add more tools by clicking on the down arrow and choosing More
Commands.

3. The Excel Options dialog box will open. With Separator selected, click on the Add button to add
some space between the tools already selected and the new tools that will be added.

Excel Fundamentals 10
 4. Use the scroll bar under Popular Commands to choose Email and Quick Print; click OK. The tools
will now appear as icons on the Quick Access Toolbar.

4
4

Organize and View Workbooks

You can get easy access to workbooks used the most frequently by clicking on the File tab, then clicking
on Excel Options; choose Advanced.

1. Under Display, click inside the Show this number of Recent Workbooks box and choose the
number 10; click OK.

2. When you click on the File tab, then click on Open, you’ll see the most recently used fields and
the ability to “pin” the frequently used documents by clicking on the pin icon.

11 Excel Fundamentals
Twelve Major Parts of an Excel Worksheet
Here is a summary of the Excel worksheet parts you should be familiar with:

s
Name Box Insert Function Formula Bar

Vertical Scroll Bar

Work Area
Active Cell

Insert
Worksheet Horizontal Scroll Bar

Worksheets Status Bar View Buttons Zoom Slider

Description of Worksheet Parts:

1. Name Box – shows the cell location (ex. A1, A2, etc.)
2. Active cell – refers to the cell currently being used
3. Insert Function Button – opens the Insert Function dialog box
4. Formula Bar – space where text, numbers and calculations are entered
5. Work area – shows data inputs and calculations
6. Vertical scroll bar – moves the work area up and down
7. Status Bar – displays information about the open file. You can right-click on Status Bar to open
what’s called the Customize Status Bar to choose display options

8. Worksheets – indicates the number of worksheets open

9. Insert Worksheet – opens a new worksheet tab
10. Horizontal Scroll Bar – moves worksheet area to the right or left
11. View Buttons – switch between Normal, Page Layout and Page Break Preview
12. Zoom Slider – changes the magnification of the worksheet between 10%-400%

Excel Fundamentals 12
Chapter 2: Entering Data and Formatting Cells

Learning Objectives
After completing this chapter, you should be able to:

1. Apply custom formats to numbers and dates

3. Add the Camera tool to the Quick Access toolbar
3. Use Text to Columns tools to separate data that is delimited
4. Use the Text to Columns tool to separate data using break lines

To enter data into a cell, you can either type the data into it, type it into the Formula bar or copy and paste
the data from another software application or a website. After the data is entered, you should be familiar
with the different ways that the text and numbers can be formatted. Some common Number formats used
include the General, Number, Currency and Accounting formats.

Click on the Number Launcher from the Number group, then choose a format.

List Boxes
Several buttons within the Excel groups on the
Ribbon will have a list box associated with them,
which will allow you to choose a format and
apply it to a cell or range of cells.

To format numbers, you can select them and

then click the down arrow from the Number
group from the home tab to change the
formatting.

13 Excel Fundamentals
Cell Formatting

Special Formats can be applied to cells in a data list that includes zip codes, phone numbers or social
security numbers. To apply a format to a list of phone numbers, for example, you would first select them,
then click on the Home tab. Click on the Launcher button in the Number group; click on Special and
choose Phone Number.

You can also apply the format to one or more empty cells, so that any number entered into the cells will
automatically be formatted as phone numbers.

Custom Formats
Custom formats can be used when you want to see text displayed before, after or in between any
numbers that are inputted into the cell.

Activity 2.1: Apply Custom Formats to Numbers

1. Open the Chapter2 Excel file and click on the Activity 2.1 Worksheet
2. Click on cell C8, then click on the Launcher button and choose Custom.
3. Enter 0 and then input the word Clients in quotation marks (0 “clients”); click OK.

Excel Fundamentals 14
Here are some other examples of custom formats:

Activity 2.2: Apply Custom Formats to Dates

Dates that are inputted into cells are typically formatted as month, day and year. For example, if you
wanted to input January 15th, you would type 1/15 and the format 1/15/2019 would be displayed. Custom
formats could also be created so that you could display that same date as Tue, Jan 15.

1. Click on the Activity 2.2 worksheet.

2. Change the Date format to ddd,mmm d.

Use Autofill to Enter Data

Autofill is a tool that can be used to avoid typing or retyping information that is a part of a series, such as
days of the week, months, years or patterns of numbers. If you were to type “January” into a cell, for
example, you could use Autofill to add the rest of the months in the year.

To use Autofill, enter the first word or value of what you want to
Autofill. Select that value and drag the cell to the left or the right
by left clicking on the Fill handle, which is the black cross in the
lower right-hand corner. Dragging the Fill Handle down to the
right will increase the series; dragging up or to the left will
decrease the series. After the data has been entered into the
worksheet cells, you can then use the Autofill Options button.
Fill Handle
In this example, Autofill was used to enter every day of
the week. You could also use the Options button to just input
weekdays, which would exclude Saturday and Sunday.

15 Excel Fundamentals
Create a Custom Fill Series
You can create a list of information that can be used to Autofill a range of cells, such as product lists,
customer lists, classes or price lists.

1. Click on the File tab, then choose Options from Backstage View.
2. Go to the General section; click Edit Custom Lists.

3. Type data for the new list in the List entries box, which each list item separated by commas; BE2281,
CF928, DF732, PA923, RI923,SP377. Click Add, then click OK.

4. If you already have a list of information, you can choose Import list from cells, then choose OK to add
the cell range to your custom lists.

Adding Comments
Comments are helpful when you need to describe your worksheet entries and provide explanations for
others to review. Examples would include product or service descriptions, conditions and disclaimers
(good until, expires on…) and notes to employees or customers. Adding comments will not affect the
worksheet’s functionality. You can enter and manage comments into a worksheet in the following ways:

1. Inserting a comment into a cell:

Click on the Review tab → click New Comment Button.
2. Permanently display one or more comments:
Click on the Review tab → Comments group → click Show/Hide Comments button.
3. Move among comments:
Review tab → Comments group → Previous or Next buttons button.

4. Edit comments:
Click on the comment → click on the Edit Comment button.
5. Size or reposition comments:
Display the comment then click its border → drag sizing handles or drag its frame to reposition.

Excel Fundamentals 16
 6. Printing comments at the end of the worksheet:

Page Layout tab → Page Set-up launcher button.

From here, you would click on the Sheet

Tab. From the Comments drop-down
menu, choose “At End of Sheet”; click OK.

7. Print permanently displayed comments:

Follow the same steps for printing comments at the end of the sheet, then click the “Show All
Comments” button from the Comments group.

8. Deleting a comment:
Review tab → Comments group → Delete button or right click → Delete Comment.

Adding Hyperlinks

Hyperlinks are useful when you want to navigate back and forth between files or other worksheets within
the same workbook. Choose a cell that has the information that will be used for the hyperlink. For
example, you can type “Click Here to Move to Sheet Three” in cell A1:

17 Excel Fundamentals
 1. Click on the Insert tab, then click on Insert Link from the Links group.
2. Choose from the list of Insert Hyperlink options, then click OK.

2
1

3. When you move the cursor over text or numbers, a ScreenTip will appear and describe the full
path of the link.

Editing a Hyperlink

Right click on Hyperlink → Edit Hyperlink → ScreenTip button. Type information into the ScreenTip text
box → click OK → click OK again to close the Edit Hyperlink dialog box:

Excel Fundamentals 18
Excel Camera Tool
The Excel Camera is a way to capture an image of worksheet data and then paste the image into other
Office applications. You can access the camera by first adding it to the Quick Access Toolbar.

Activity 2.3: Using the Excel Camera

1. Click on the Activity 2.3 worksheet.

2. Add the Camera to the Quick Access toolbar by clicking on the File tab, Options, Customize
Ribbon. Locate the Camera by choosing Commands Not in the Ribbon from the Choose
commands from drop-down list:

3. Select the worksheet data in cells B1 to F15. you want to capture and then click on the
Camera icon on the Quick Access Toolbar.

3
4.

19 Excel Fundamentals
 5. When the small cross appears, you can hold down the left click button on the mouse to apply
the Camera image.

Using a Template
A template is a workbook preformatted with information that can be changed or customized. New
information can be typed into cells or placeholders within the template (a placeholder is the section of the
template that can be edited).

Click the Office or File button → New. Choose from one of the available templates or create a new one.

Cell Alignment
There are times when you need text or numbers to appear beyond the limitations of just one cell. The
size, width and length of a cell can be changed by using one of the tools from the Alignment group:

Excel Fundamentals 20
 1. Wrap Text – allows you to view inputted information within a restricted number of cells:

2. Merge & Center – choose number of cells that you want to include in the merge:

3. Rotated Text – display text or numbers on an

angle.

a. Select the number of cells where the rotated

text will appear. For example, if the text is in
cell B2, you may want to select rows B2 to B8.

b. From the Alignment group you will then

choose Merge & Center, Wrap Text and the
Rotate Text Up from the Orientation button.

4. Center Across Selection

This is a Text Alignment tool that can be used when you want to input a title or label onto a
worksheet. Select the cells where you want the text to appear (B7:E7). Click on the Alignment
launcher and choose Center Across Selection from the Horizontal drop-down list.

21 Excel Fundamentals
Shortcut Keys and Commands
There are over 75 different types of shortcut keys within four general categories:

1. CTRL+Shift+
2. CTRL+ 1-10
3. CTRL+ A-Z
4. Function Keys (F1-F12)

You may find that there are only a handful of shortcut keys you will use on a regular basis. Here is a list of
some of the commonly used ones, along with a list of other shortcut key combinations you might find
useful:

Task Command
Open a new worksheet SHIFT+F11
Open a new workbook CTRL+N
Open an existing file CTRL+O
Display worksheets from right to CTRL+PGDN
left
Display worksheets from left to CTRL+PGUP
right
Print a worksheet CTRL+P
Save file currently open CTRL+S
Create a Table CTRL+T
Select an entire row or column SHIFT+CTRL+arrow key
Open the Help menu F1
Cut CTRL+X
Copy CTRL+C
Paste CTRl+V
Past Special dialog box CTRL+ALT+V
Hide the Ribbon Double-click on tab currently open
Redisplay the ribbon Click on any tab
Bold text CTRL+B
Apply italics CTRL+I
Underline text CTRL+U
Insert a Table CTRL+T
Insert Function dialog box Shift+F3
Format cells dialog box CTRL+SHIFT+F

Excel Fundamentals 22
Text to Columns
Text to Columns is an Excel Data Tool used to divide words, numbers or characters into multiple
columns. It could be data that’s already in the worksheet, external data imported into Excel or data copied
and pasted from another source, such as a web page. The Text to Column tools recognizes data as being
either a Delimited file type or a Fixed Width file type.

Activity 2.4: Delimited Files - Space

1. Click on the Activity 2.4 worksheet.

2. Right-click on column C, then click on Insert

3. Click on Column B; click the Data tab; Choose Text to Columns.

4. Delimited is already selected; click Next.

5. Uncheck Tab, check off Space; Click Finish.

6. Click on cell B1; type “First Name”; Click on cell C1; type “Last Name”.

23 Excel Fundamentals
Activity 2.5: Delimited Files – Special Characters

1. Click on the Activity 2.5 worksheet.

2. Click on Column A.
3. Click on Text to Columns, Delimited; click Next.
4. Uncheck Space, check off Other.
5. Input the @ character, click Finish.

6. Click on the B column.

7. Delimited; input a period (.) in the Other space;
Click Finish.

8. Input new column titles; Name, Provider,

Domain Name.
2
9. Adjust the column widths.

Activity 2.6: Fixed Width File Types

1. Click on the Activity 2.6 worksheet

2. Select columns C and D; right and choose Insert.
3. Select column B, go to Text to Columns.
4. Click on Fixed Width, click Next.
5. Add to Break Lines between the numbers and letters; click Finish.

Excel Fundamentals 24
Chapter 3: Using Cut, Copy and Paste

Learning Objectives
After completing this chapter, you should be able to:

1. Identify three ways to cut, copy and paste Excel data

2. Change the column width using Paste Special
3. Transpose data using Paste Special
3. Paste Excel data onto a worksheet using the Clipboard

There are three different ways you can use the Cut/Copy/Paste tools:

1. Select data to be copied → click on the Home tab → Copy or Cut → click on cell where you want
to copy the data → Home tab → Paste.

2. Select data to be copied → Right-click the mouse → choose Copy or Cut, click on the cell where
you want to copy or cut the data → Home tab → Paste.

3. Select data to be copied → CTRL+C to copy or CTRL+X to cut → click on cell where you want to
paste the data → CTRL+V → Paste.

You can also move data within a cell or an entire range of data without using the Copy/Paste tools:

1. Select data range to be moved using the mouse key.

2. Position the mouse on the border surrounding the range until you see the four-arrow icon.
3. Left click the mouse and use it to move the data to the desired cells within the worksheet.

Paste Special

The Paste Special tools allows you to control how copied data is pasted into a cell or range of cells. The
Dialog box can be accessed by selected the data to be copied, pressing CTRL+C, choosing the area
where the data will be pasted, which in this case will be cells in a new worksheet. Press CTRL+ALT+V:

Paste Special Examples:

• Formulas – Will paste any formulas that are

in the cells
• Paste Values – Will paste only the calculated
values from the cells
• No Borders – Will paste the cells without the
borders from the original cells
• Transpose – Change the orientation from
vertical to horizontal or horizontal to vertical
• Paste Link – Creates a link between the
pasted data to another cell in a worksheet

25 Excel Fundamentals
In this example, the column widths will be pasted first, so that they match the column widths of the
original worksheet. To do this, click on Column widths, click OK and then press the Enter key:

The data will be pasted into the cells with the new column widths.

Activity 3.1: Transpose Data

The Transpose option in Paste Special when you have text or numbers aligned in one way (i.e., columns
A1:H15) and you want to align then in a different way (i.e. rows A1:A15).

1. Open the Chapter3 Excel file, then click on the Activity 3.1 worksheet.
2. Select the data from cells E2 to I5; press CTRL+C.
3. Click on the cell where the data will be pasted, which is cell K12; press CTRL+ALT+V.

Excel Fundamentals 26
 3. Check off Transpose and Values, so that only numbers are pasted, click OK.

4. Change the column widths (refer to page 26) and the number formats (refer to page 13) so that
the numbers are easier to read.

Activity 3.2: Using the Clipboard

Worksheet data that is cut or copied is automatically placed onto the Office Clipboard, which will allow
you to paste the data multiple times into different areas of a worksheet or into other office applications.
The clipboard holds a maximum of 24 items. In this example, we want to copy and paste the expenses
from an income statement onto another worksheet.

1. Click on the Activity 3.2 worksheet. Select the data and press CTRL+C; the copied data will now
appear in the Office Clipboard.

1
1

27 Excel Fundamentals
 2. Press CTRL+N to create a new workbook, then click on the data in the clipboard. Adjust the width
of the columns as needed. In this example, you can double-click between columns A and B to
make column A wider.

Excel Fundamentals 28
Chapter 4: Formulas and Functions

Learning Objectives
After completing this chapter, you should be able to:

1. Understand the difference between a formula and a function

2. Create an array formula with one or more operators
3. Name at least seven categories of Excel functions
4. Input functions using the Function Arguments dialog box
5. Apply names to a cell or a range of cells
6. Create links between worksheets
7. Input formulas and functions that have relative, absolute or mixed cell references

Formulas and functions are used to tell Excel how to perform calculations or execute commands on
inputted data. A formula is a statement that performs calculations based on:

• Constants: =1+2+3
• Cell References: = A1 + B1 + C1
• A combination of both: = A10 + 15

To enter a formula, you would start by inputting an equal sign, followed by a combination of numbers and
operators; addition (+), subtraction (-), multiplication (*) or division (/). You can also separate numbers
with an exponent using the ^ sign (i.e. =3^3, which would display the number 27).

A function has formulas built into it and can represent multiple calculations. Instead of typing in
=A1+A2+A3, for example, you can input =SUM(A1:A3). The =SUM represents the Function Name and
the cell references (A1:A3) represent the Function Arguments. The layout and order used to input the
function name and function arguments into a cell is referred to as syntax.

Formulas and functions can be entered

directly into a cell in the worksheet or
into the formula bar. If you are not
sure of how to perform a calculation,
you can click on the Insert Function
button to open a dialog box.

There are over 300 functions in Excel, divided between 12 different function categories. When you click
on each function, you’ll see a description of what it is does and how it works. Functions are activated
when you start typing the function name into a cell.

29 Excel Fundamentals
If you want to use the SUM function, for example, you
can start typing =SUM into a cell and then press the
Tab key. The function name will appear, along with a
description of what will be calculated (the function
arguments):

You can then input the numbers or click on the cells

to be used to do the calculation.

Chapter 4 Activities

Open each of the Activity files and follow the instructions on each worksheet:

4.1 Formulas 4.2 Math Functions

4.3 Statistical Functions 4.4 Logical Functions
4.5 Financial Functions 4.6 Date and Time Functions
4.7 Lookup and Reference Functions 4.8 Text Functions

Inputting Formulas
Here’s an example of a company that sells computers and supplies to retail stores. They know what their
cost and markup percentage is for each product. A worksheet needs to be created that automatically
calculates what the sale price would be to the retailer:

Formulas were inputted to do the following calculation: Cost x (1+Markup).

Array Formulas
There may be times when you have Excel worksheets that are not formatted in a way that makes it easy
to perform calculations. In this example, we want to multiply a range (array) of numbers with another
range of numbers. If we try to do this without using an Array formula, you might get an error message
(#VALUE).

Excel Fundamentals 30
To input an Array formula in this example, you would select cells D8 to J8, input the operator (*), then
select cells D9 to J9. To input the Array formula, hold down the SHIFT+CTRL keys, then press Enter. If
you’re using the latest version of Office 365, you can input the array formula by just selecting the cell and
pressing Enter.

{D8:J8*D9:J9}

Naming a Cell
If you’re creating a worksheet that has more complex formulas, you may want to use named cell
references. This means that when you click on a cell in a worksheet, you’ll see text instead of cell
references. For example, =C4*(1+E4) can be made to appear as =Cost*(1+Markup).

Type a number into a cell, then type a name into the

name box; press Enter. The cell name will appear in
the name box. The cell name cannot start with a number and
should be a single word, two or more words separated by
underscores or a name that uses both upper and lower case
letters.

Named cells can be used to perform calculations.

For example, numbers can be added
together by using the SUM function and the
assigned cell names, separated by a comma. If
more than one word will be used to name a cell,
you can combine them using a mixed case, such
as SalesJune, SalesJuly, etc.

31 Excel Fundamentals
Naming a Range of Cells
In addition to naming individual cells, you can also name a range of cells and perform calculations using
those names. In this next example, we want to find an easy way to add the numbers in columns B
through D and input the grand total into cell D8.

You would use the mouse to select the cells to be included in the name range. Click on the Name Box
and name the range (JanuarySales). Then input the SUM function into cell D8:

Named cell references can be automatically created using a tool called Create from Selection, if the
column or row labels are next to the cells that you want to add names to. Select the cells, then click on
the Formulas tab. Click on Create from Selection.

You’ll see that the Left column is selected as the default; click OK. The named cell reference was created
using an underscore to separate the words in the cell:

Excel Fundamentals 32
Individual cells or a range of cells that have assigned names are stored in what’s called the Name
Manager, which can be accessed by clicking on the Formula tab, then clicking on Name Manager:

Let’s now look at seven different function categories.

Math Functions
Math functions can be used to perform very basic calculations (addition, subtraction, etc.) or combined
with multiple formulas and functions. Here are some examples:

SUM

The SUM function is used to add numbers using inputted values, individual cells or a range of cells. Let’s
look at three examples. Adding rows or columns of numbers (C3:E3), adding a single range of numbers
(C3:E8) and adding multiple ranges of numbers (C4:E4, C8:E8).

Two or more ranges of cells can be added to together by inputting the SUM function, selecting the range,
inputting a comma and then selecting additional ranges. To add the values from Electronics and Small
Appliances, for example, you would input =SUM(C4:E4,C8:E8).

SUMIF

The SUMIF function is used when you need to add numbers in a column that meet a specific criterion. If
you click on cell D2 for example, you can input the SUMIF function to add the expense numbers.

33 Excel Fundamentals
Column A will be where you select the range of data, C2 is the data you’re looking for within that range
(Regular Mail) and column B is the range of numbers that you want to search. In other words, you’re
telling Excel that you only want to see the grand total of expenses for Regular Mail. You can then repeat
these steps for Express Mail.

SUMIFS

This function is used when you have multiple criterion being used to produce results:

This is how the cell references would be inputted into the Function Arguments dialog box:

Excel Fundamentals 34
Each space in the Function Arguments dialog box is asking a question:

Function Argument Question to ask:

Sum_range Where should I look? (column E, the Expense column)
Criteria_range1 What should I look for? (the information in column B)
Criteria1 What should I look for in Column B (the words “Regular Mail”)
Criteria_range2 What should I look for? (the information in column D)
Criteria2 What should I look for in Column D (the word “Domestic”)

PRODUCT, SUMPRODUCT and QUOTIENT

Here is an example of how math functions can be used to help a business make decisions about
inventory. SUMPRODUCT uses what’s called an Array to perform two operations. It will first add the
numbers in each array (J5:J8 and K5:K8), then it will multiple the result.

Financial Functions

PMT, NPER, RATE and PV

These functions can be useful

whenever a major purchase or
investment is going to incur
finance charges. This example
shows a business owner that
created a template that
answers four questions:
PMT Monthly payments?
NPER How many
payments?
RATE Interest rate?
PV How much can
we borrow?

35 Excel Fundamentals
PPMT and IPMT

The PPMT and IPMT functions will tell you how much of a monthly payment is going towards the
principal and how much is going towards interest. You can input an equal sign follow by a minus sign so
that the result will display a positive number:

NPV and IRR

Businesses will often have to evaluate whether major financial investment is worth pursuing and how
much it will cost. It may be as simple as writing a check to make the investment. Some investments,
however, may require that the business get a bank loan or credit line that comes with the expense of
paying interest. The NPV and IRR functions are used help people make decisions about those
investments.

Here’s an example of a daycare center that wants to move into a new facility. The cost to pay for
renovations and other moving expenses will be $19,750. They expect to increase their operating income
(cash flow) by $5,000 per year over the next five years if they get a bank loan that comes with an 8.7%
interest rate (which is sometimes referred to as a required rate of return):

Excel Fundamentals 36
The NPV and IRR functions were used to show the business owner that the Net Present Value, which is
the value of the investment after the interest rate is considered, is a negative number. You’ll also notice
that the Actual Rate of Return or 8.4% (IRR) is less than the bank loan interest rate of 8.7% (this is
sometimes referred to as the cost of capital).

XNPV and XIRR

The XNPV and XIRR functions use

specific dates (1/15/20, 3/15/20, etc.)
instead of fixed time frames (Year 1,
Year 2, etc.) as function arguments to
determine the Net Present Value and
Internal Rate of Return. In this
example, the business is expecting a
high rate of return but must also pay a
higher rate of return.

CUMPRINC and CUMIPMT

The CUMPRINC and CUMIPMT functions will tell you how much of your monthly payment is going
towards the principal and how much is going towards interest within a specific period of time. If you
wanted to know what the principal and interest payment would be for the second year of the loan, for
example, the starting period would be 13 and the ending period would be 24. To calculate the total
interest expense paid on a five-year loan, you would input 1 for the starting period and 60 for the ending
period:

FV

Financial functions can also be used to calculate the interest earned on an investment. A template can be
created based on the questions you want Excel to answer. For example, you can ask, “what is the
estimated value of my retirement account in 18 years?”

37 Excel Fundamentals
For many of the financial functions, it’s easier to input the function (i.e., =PMT), press the Tab key, then
open the Function Arguments dialog box to enter the function arguments, as opposed to typing the
function arguments directly into the cell.

Statistical Functions
AVERAGE, MIN and MAX

This group of statistical functions will provide information about a range of numbers. The data can then be
evaluated to see if the numbers reveal information that can be used to make decisions.

COUNTA and COUNTIF

Statistical functions are also used to

produce results based on data as
opposed to dollar amounts. The
COUNTA function is used to calculate
the number of spaces in a range that
aren’t blank; the COUNTIF function is
used to count the number of times a
string of text (i.e. Marketing) appears
within a range of cells:

The COUNTA and COUNTIF functions can also be inputted

using a text string without referencing a cell address (i.e.
type “Underperformed”) and can be combined with formulas:

Excel Fundamentals 38
Logical Functions
These functions are used to test the values of one or more cells and will produce a result based on the
function arguments inputted. Like other Excel functions, they can be used to return a result based on
numbers inputted into cells or combined with multiple formulas and functions. Logical functions can also
display text based on whether the result is true, or the result is false.

IF

Here are three examples of how the IF function can be inputted:

a. True or False: If the monthly payment amount is less than the Budget, the word “Accept” appears;
if it’s less, the word “Reject” Appears.

=IF(C6<C7,”Accept”,”Reject”)

b. Blank if False: If the budget amount is higher than the payment, the word “Reject” appears; if not,
the cell is left blank (cell C8).

=IF(C6>C7,”Reject”,””)

39 Excel Fundamentals
 c. Nested IF function: This can be used when two or more conditions are inputted.

=IF(C6<C7,”Accept”,IF(C6<=500,”Caution,”Reject”))

IFERROR

The IFERROR function can be inputted in front of other functions to prevent error messages from
appearing. This worksheet has the AVERAGE function inputted into the cells in column H but the cells in
row 8 do not any numbers yet. If =AVERAGE is inputted into the cell H8, the error message #DIV/0! will
appear:

This means that there are empty cells being referenced in the function arguments. IFERROR can be
inputted to say that if an error message appears in the cell, leave it blank; otherwise, perform the
calculation using the AVERAGE function: =IFERROR(AVERAGE(C4:F4),””)

Excel Fundamentals 40
AND and OR

There may be times when you want to create a template that has more complex criterion. In this example,
a company wants to offer a discount to their clients but only if certain conditions are met:

All customers that spend more than $500 or place an order for more than 25 units will receive a 15%
discount. If neither of these conditions are met, a 0 will be inputted

The IF function would be entered first, followed by the OR function, the first condition, the AND function,
then the second condition. You would then input two parentheses, a comma and the Subtotal times the
discount of 15%: =IF(OR(F3>500,AND(E3>25)),F3*0.15,0)

Date and Time Functions

NETWORKDAYS

A company or individual that needs to provide job cost estimates can use this function to estimate the
amount of time needed to complete a job. The NETWORKDAYS function calculates the number of
working days between two dates and excludes weekend dates. You can also choose to exclude holidays:

=NETWORKDAYS(B3,C3)

41 Excel Fundamentals
DAYS360

This function calculates the number of calendar days between two time periods. In this example,
DAYS360 was combined with two other functions. The IF function was inputted into cell F4 so that the
function could be Autofilled to the other cells in column F. The cells will remain blank until a number is
inputted into a cell in column E. The TODAY function was inputted so that the current date will always
show the number of calendar days between the current date and the Invoice date (i.e. B4).

=IF(E4=””,””,DAYS360(B4,TODAY()))

YEARFRAC

The YEARFRAC function returns the number of years between two time periods. The TODAY function is
being used in this example and is combined the math function INT, which rounds down the result to the
nearest whole number:

=INT(YEARFRAC(C3,TODAY()))

Lookup and Reference Functions (VLOOKUP)

This function is used when you need an easy way to retrieve data from a large worksheet. Here’s an
example of a data list with 500 names and VLOOKUP is being used to retrieve email addresses. The
Function Arguments dialog box can be used to input four arguments:

Function Argument Description

Lookup_value The cell with the name of the person we want to search
Table_array The range of data is formatted as a Table, which is named “Contacts”
Col_index_num The number of the column we are referencing (column D)
Range_lookup FALSE was inputted, so that an exact match would be retrieved

Excel Fundamentals 42
=VLOOKUP(F1,Contacts,4,FALSE)

The VLOOKUP in this example uses a Table named

“Contacts” as the Table Array argument. To covert a
range of data into a Table, select the cells to be
included and press CTRL+T; click OK. You can then
click on the Design tab and click inside the Name Box
in the upper left corner to type in the name.

HLOOKUP

The HLOOKUP function uses the same

number of function arguments as
VLOOKUP but counts the number of
rows in the data to find the data instead
of counting the number of columns:

=HLOOKUP(B13,RetailList2,3,FALSE)

HLOOKUP example

43 Excel Fundamentals
INDEX and MATCH

These two functions working together can be used instead of VLOOKUP for both simple and complex
data retrieval. Here’s an example of a worksheet that was created to find a delivery expense based on the
location inputted into a cell location. The Index is looking for a location (B3:B17) and the Match is looking
for the dollar amount (C3:C17):

=INDEX(C3:C17,MATCH(E4,B3:B17,0))

Function Argument - INDEX Description

Lookup_value The range of cells where the delivery expenses are inputted
Input the Match Function Press the Tab key after inputting the Lookup_value argument
Function Argument - MATCH
Lookup_value The Location being looked up (cell E4, Cherry Hill 50)
Lookup_array The range of data is being looked up (B3:B17)
match_type 0 was inputted, so that an exact match would be retrieved

INDEX and MATCH can also be used to retrieve data from multiple columns in a worksheet and use
more complex syntax. This worksheet was created to retrieve delivery expenses based on the location
and the type of delivery service, so the MATCH function was entered twice. The data is also being
retrieved from a separate worksheet in the workbook (Delivery Expense Table):

Excel Fundamentals 44
Text Functions

CONCATENATE (or CONCAT for Excel 2016)

This function can be used to combine content in two or more cells in a worksheet into one cell. In the first
example, the first name is being combined into one cell with the last name by inputting the function
arguments directly into the cells:

Another option is to use the Function Arguments dialog box:

LEFT, RIGHT and MID

In this worksheet, the purchase order numbers in column B are a combination of three different strings of
characters; the invoice number, the item code and a location.

45 Excel Fundamentals
The LEFT function was used to input the first four characters into cell B2, which is =LEFT(B2,4); the MID
function was used to input five characters in the middle of the text string, starting with the 5 th character,
=MID(B2,5,5); the RIGHT function was used to input the last two characters in column B, =RIGHT(B2,2):

Combine Multiple Functions

Another way to perform complex calculations in Excel using functions is to input multiple functions in to
one cell. This is done by inputting the first function, pressing the Tab key, then entering additional
formulas and functions.

The Average Qty per Day, for example, is calculated by using the QUOTIENT to divide the Selling Days
This Month (D16) by the Quantity values in cells D8 to D15.

Excel Fundamentals 46
Creating Links Between Worksheets

There may be times when you want the values in one

worksheet to be displayed in another worksheet. In this
example, the Rental Properties value that will appear in
cell C5 will be based on a calculation from a different
worksheet:

This can be done using a Paste Special tool called Paste Link. Select the cell you want copied (cell D6),
then right-click to select Copy:

Click on tab and cell for worksheet where data is to be pasted (cell C5), right-click and choose Paste
Special:

47 Excel Fundamentals
 The Paste Special dialog box will appear. Recall
from Chapter 3 that you can paste worksheet
formats, operations and formulas in addition to
pasting text and numbers.

Click Paste Link:

You will now see the value pasted into the cell. You will also see a 3D Reference in the formula bar:

A 3D Reference describes:

1. The name of the worksheet (Rental Property)

2. The ! separates the name of the worksheet from the cell address
3. The $ within the cell address indicates that this an absolute reference

Absolute and Relative Cell References

Relative Cell References

One or more cells with relative references means that any formulas or functions within the cells will
change when Autofill or Copy/Paste is used to input data into other cells.

Excel Fundamentals 48
Select and right-click to copy cell B8:

You will see the formula for cell B8 is =SUM(B2:B7)

Select cell C8, right-click and Paste it into cell C8:

Notice that the function was pasted into the cell, as opposed to the value of $129,000.

Absolute Cell References

There may be instances where you don’t want the values to change as you copy and paste information
from one cell to another. For example, a company might create a sales projection worksheet to determine
how much profit they will make over the next three months. They know that regardless of how much they
sell, the cost of producing the product (COGS) will be 65% of their sales revenue.

We can use the Show Formulas button from the Formulas tab to see how this is calculated and that cell
B1 is the absolute reference:

49 Excel Fundamentals
The values in column C3 through C5 all reference cell B1 (20,302 x 0.65, 32,312 x 0.65, 23,121 x 0.65):

Mixed Cell References

A mixed cell reference can be used when you don’t want the cell reference in a column to change ($D4,
$D5, etc.) but you want the row to change as you preform calculations using Autofill (25 x 100, $25 x 100)
or vice versa. This an example of a business that created a price matrix using mixed cell references:

Excel Fundamentals 50
Chapter 5: Creating Lists and Tables

Learning Objectives
After completing this chapter, you should be able to:

1. Add the Form button to the Quick Access Toolbar

2. Create a new data list and input entries using a form
3. Create a drop-down list using Data Validation tools
4. Format a range of data as a Table

In this chapter, we’ll look at the steps needed to create and modify lists and Tables. A list can be
described as a structured collection of information, such and a list of names, address and phone
numbers. Tables are worksheet formats that make it easier to enter data and perform calculations.

Create a Data List

Click on the first cell in your data list and start inputting the column titles, pressing the Tab key after each
entry:

You can then apply custom and special formats to the cells that will have numbers, such as phone
numbers, social security numbers and dates (see page 15).

51 Excel Fundamentals
Enter Data using a Form
Excel has a tool called Form Command that can be added to the Quick Access Toolbar to make it easier
to add, delete or edit records in a data list.

Activity 5.1: Input data using a Form

1. Open the Chap5 file, then click on the Activity 5.1 worksheet
2. Click on the File tab. Go to Options and choose Quick Access Toolbar.
3. Under Choose Command from, select Commands Not on the Ribbon.
4. Find the Form command, double-click on it, then click OK:

The Form Command works best when the data being accessed is formatted as Table.

5. Click anywhere inside the Data List, then press CTRL+T keys to format the data list as a Table.
6. Click on the Form button on the Quick Access Toolbar to open it.

You can see where the first record in the Data List appears. You can click the “New” button whenever you
need to add a new record to the list. You would then start inputting the information into the form, pressing
the Tab key after each entry (Last Name, First Name, etc.). Press the Enter key after each entry; click the
Close button when finished.

Excel Fundamentals 52
To find names within the data list, you would click on the Criteria button and type in the information you’re
looking for, such as Last Name. You will then see all the information associated with the last name:

Create a Drop-Down List

A drop-down list is useful when you have an Excel worksheet being used for data entry. Here’s an
example of a company that created a drop-down list of payment term options:

Activity 5.2: Input data using a drop-down list

To create a drop-down list, click on the cell where the list options will appear, then click on the Data Tab.
Click on the Data Validation down arrow from the Data Tools group and choose Data Validation:

53 Excel Fundamentals
Choose List Under where it says, “Allow”. Type this list into where it says Source; EOM, Net 45, 2% 15
Net 30, Net 30. Click OK:

Create a Table
Data formatted as a Table is can be easier to work with because can automatically perform calculations.
Start by inputting the information to be included in the Table, just like you would for a data list:

Activity 5.3: Format Excel data as a Table

1. Click on the Activity 5.3 worksheet.

2. Use your mouse to select cells B2 to H36.
3. Press CTRL+T. Make sure that “My Table Has Headers” is checked off; click OK.

Click on the Insert tab → Table group → Table button

(or CTRL+T). Excel will automatically select the data
that will appear in the table; click OK.

Excel Fundamentals 54
The list will be formatted as a Table:

To change the appearance of a Table, click on it, then click on the Design tab. Choose a style from the
Styles group.

Add Table Calculations

For this Class Performance Summary worksheet, we want to see the average score automatically
calculated for each column heading in the Table (Final Exam, Final Grade, etc.).

1. Click on one of the cells in the Table. Check off Total Row in the Table Style Options group.
2. Click on cell D11, then Autofill from the Final Exam column to the Class Participation column (cell
H11).

3. Change the cell name in B11 from Total to Average:

3 2

You can now see that the class average for final exams is displayed, along with the student’s names and
averages for the other categories of grade performance.

55 Excel Fundamentals
 Chapter 6: Introduction to Charts and Visuals

Learning Objectives
After completing this chapter, you should be able to:

1. Name five major parts of a chart

2. Identify six different kinds of charts
3. Select the data needed to create combination charts
4. Create a dashboard using multiple charts
5. Create three different kinds of Sparkline charts
6. Identify two different ways to apply Conditional Formatting

Elements of a Chart

A chart is a visual representation of Excel worksheet data. It usually includes one or more of these
elements:

Chart Title

Legend

Vertical (Value)
Axis

Data Series Horizontal (Category) Axis

Excel Fundamentals 56
Chapter 6 Activities

Open each of the Activity files and follow the instructions on each worksheet:

6.1 Column charts 6.2 Column charts

6.3 3D Column chart 6.4 Bar chart
6.5 Pie chart 6.6 Bar and Pie chart
6.7 Line Chart 6.8 Combination Chart
6.9 Sparkline Charts 6.10 Conditional Formatting

Column Charts
Examples of when to use column charts:

• One value, multiple months or years (i.e., one product, twelve months)
• Multiple values, one month or year (i.e., twelve products, one month)
• Comparing multiple data sets (such as income versus expenses) over multiple time periods

To create a simple column chart, start by inputting all the data to be included in the chart:

To create a chart using this data, click on any cell between A1 and D4. Click on the Insert tab and choose
the Column button from the Charts group; let’s choose 3D clustered column:

Chart Title

5,000
4,000
3,000
2,000
1,000
-
January February March

Year 1 Year 2 Year 3

57 Excel Fundamentals
You can also create a chart that includes all the data within the worksheet by clicking on any cell inside
the data and pressing press F11 on the keyboard. Excel will create a chart in a new worksheet called
Chart 1.

Notice that when you click on the chart, you’ll have the ability to change the chart elements, change the
chart style or apply a filter.

You can also experiment with the different style, layout and chart element tools:

Excel Fundamentals 58
Column Charts with a Numerical Axis

In the previous example, both text and numbers were used to create a chart. However, there may be
times when only numbers are being used. Excel will not be able to automatically make a distinction
between dollar amounts and the values being represented as years, so you’ll need to make some
adjustments using options from a dialog box called Select Data Source.

1. Select any cell inside the chart, click Insert tab, click on the Column button from the Charts group.
and choose 2D Clustered Column. Choose Layout #9, click OK.

Chart Title
5000
4000

Axis Title
1 3000
Year
2000 Sales
1000 Expenses
0
1 1 2 3 4 5 6
Axis Title

The chart data must now be edited so that the Horizontal Axis shows the right values.

2. Right click on chart, then double-click on Select Data.

3. Click on the Year, click Remove on the Legend section on the left.
4. Click on Edit under Horizontal (Category) Axis Labels, then select the years (2013, 2014, etc.);
click OK.

3 4

59 Excel Fundamentals
 5. Choose names for the title and horizontal axis.

Sales vs. Expenses 5

5,000
4,000
3,000
2,000 Sales
1,000
Expenses
0
2013 2014 2015 2016 2017 2018
Fiscal Year
5

Line Charts
A fictitious company called ABC Electronics sells computer hardware and software. Let’s create a line
chart that gives them a summary of computer and printer sales from January to June. Create a line chart
by clicking on cell B1; click on the Insert tab, then choose the Line button from the Charts group; choose
2D line:

When comparing two sets of data, you may want to change the appearance of the chart by changing the
format of at least one of the data series lines. In this case we want to change the Printers data series to a
different format, such as dots or dashes.

Excel Fundamentals 60
 1. Click on the Printers data series (blue line), then left-click and choose Format Data Series; a
dialog box will appear on the right side of the worksheet.

2. Click on the Fill & Line icon, then click on the Dash type drop-down arrow; choose Square Dot
Square. Dot from the Dash type drop-down menu; click on the Close button. You now have two
lines in the graph that are distinguished by color and line style.

2 Chart Title
500,000

400,000

300,000

200,000

100,000
2
0
Jan Feb Mar Apr May Jun

Computers Printers

You can also change the format of the chart by changing the values in the vertical axis, which currently
range from 0 to 500,000. Since the lowest value in the chart is over 200,000, we can make the chart area
smaller by making the lowest value 200,000.

61 Excel Fundamentals
 3. Right-click on the vertical axis and choose Format Axis; under Axis Options, change the Minimum
to 200000.0.

The chart area is now more compact and easier to read:

Computer vs. Printer Sales

500,000

450,000

400,000

350,000

300,000

250,000

200,000
Jan Feb Mar Apr May Jun

Computers Printers

Line Charts with a Secondary Axis

A business is looking to buy commercial real estate in a new location. They want to evaluate the market
conditions over the past eight years by comparing the average cost for a small building to the interest
rates banks were charging. Since we want to display two sets of data in a format that’s easy to
understand, we will need to format the line chart with a secondary axis.

Excel Fundamentals 62
 1. Click on the Insert tab and choose, Line Chart from the Charts group; choose Line with
Markers.

2. Click on the Chart, then click on the Chart Filter; uncheck “Year”, then click on “Apply”.
3. Right-click on the Interest Rate Data Series, then click on Format Data Series.

4. Click on Secondary Axis. The chart now displays the percentage values on the right side of
the chart.

Chart Title
300,000 8.0%
250,000
6.0%
200,000
150,000 4.0%
100,000
2.0%
4 50,000
0 0.0%
1 2 3 4 5 6 7 8 9

Average Price Interest rate

63 Excel Fundamentals
 5. Right-click on the Horizontal Axis; choose Select Data.
6. Select cells A2:A10; click OK. The Chart now shows the time periods on the Horizontal axis.

Chart Title
300,000 8.0%
7. 250,000
6.0%
200,000
\
150,000 4.0%
6 100,000
2.0%
50,000
0 0.0%
2010 2011 2012 2013 2014 2015 2016 2017 2018

Average Price Interest rate

8. You can make the time periods easier to read by right-

clicking on the Horizontal Axis a choosing Format Axis.

9. Click on the Size and Properties option under Axis Options.

Under Text Direction, choose Rotate all text 270 degrees.

The chart will now display the time periods vertically. You can also change the way the dollar amounts
are formatted on the Vertical Axis by changing the minimum value displayed to 175,000, the maximum to
250,000 and the Major unit to 25,000:

Commercial Real Estate vs. Interest Rates

250,000 8.0%
7.0%
Interest Rates

6.0%
225,000 5.0%
4.0%
200,000 3.0%
2.0%
1.0%
175,000 0.0%
2010

2011

2012

2013

2014

2015

2016

2017

2018

Average Price Interest rate

Excel Fundamentals 64
Pie Charts
Pie charts are created when you need to show the percentage breakdown of whole numbers, such as
total sales by product or months in the year.

1. Create a worksheet with the following information, then create a 3D Pie Chart. Choose a style
and layout option.

Editing a Pie Chart

2. Right-click on chart, select 3D Rotation; the Format Cart Area dialog box will appear.

3D Rotation Options:

X Rotation: Move the chart around, clockwise

Y Rotation: Move the chart up and down
Perspective: Increase/decrease the chart depth

65 Excel Fundamentals
XY Scatter Charts

Another way to evaluate numbers in a spreadsheet is to create a Scatter chart. This company spends a
lot of money on advertising and they want to see if there’s a relationship between income and monthly ad
expenses.
1. Select cells C3 to D9. Click on Insert, Insert Line or Area Chart, More Line Charts.

2. Click on XY Scatter, choose Scatter, then click OK.

3. Click on the Chart, then click on Chart Elements (the plus sign), then click on the arrow next to
Trend Line. Click on More Options.

Excel Fundamentals 66
 4. Under Trendline Options, check off Display R squared value. Close the dialog box. An R-square
value of .90 or higher means that the two sets of data are highly correlated. In other words, higher
amounts of money spent on advertising in a given time period generally means that revenue
numbers will also be higher.

5. Right-click on the Horizontal axis; choose Format Axis.

6. Under Axis Options, choose 150,000 for the Minimum, 300,000 for the Maximum and 5,000 under
Units, Major.

6
6

7. Click on the chart, then click on the Design tab. Go to Axis Titles and click on Primary Horizontal
and Primary Vertical. Change the chart title and axis names and formatting.

67 Excel Fundamentals
Create a Dashboard using Multiple Charts
A Dashboard can be described as a visual representation of multiple charts, graphs or tables. They can
be used to help organizations keep track of business performance.

To create a dashboard, first create the charts in Excel. Then click on each chart and click on Chart Tools.
From the Design tab, click on the Move Chart button from the Location group:

Sparkline Charts

Excel 2010 introduced Sparklines, which are small charts that take up one cell in the worksheet. They can
be either Line, Column or Win/Loss and useful for summarizing values over a period of time, such as a
month, quarter or a year.

To insert a Sparkline chart, click on a cell (J3), then click on the Insert tab and choose a chart style from
the Sparkline group (i.e., Line). The Create Sparklines dialog box will appear. Select the Data Range
(B3:I3) and where the Sparkline chart will be inputted (cell J3); click OK. You can then Autofill the rest of
the column (cells J4 to J10):

Excel Fundamentals 68
Here are examples of Win/Loss and Column Sparklines:

Conditional Formatting
You can use Conditional Formatting to apply formats to cells that meet one or more conditions. For
example, a company may want to compare the current month’s sales results to the prior month. They can
use Conditional Formatting to indicate whether the value within each product category is less than or
greater than 5%.

1. To apply Conditional Formatting, click on the Home tab, then click on the Conditional Formatting
down arrow. Click on New Rule.

69 Excel Fundamentals
 2. Under New Rule, click on the down arrow under Format Style and choose Icon Sets:

3. Let’s choose the green flag icon to display whenever a value of greater than or equal to 5% is
inputted; you would type .05 for the value and choose Number from the drop-down menu. Do the
same for the yellow flag option by inputting -.05; click OK.

Excel Fundamentals 70
Creating a Legend

Excel does not have specific command for creating a legend, but it is sometimes helpful to create one
when you apply conditional formatting. This is especially helpful if the workbook is going to be shared with
someone who will want to know what the icons represent.

1. Select the cells where the icons for the legend will appear (A1 to A3).
2. Go to the Home tab and click on the Conditional Formatting; choose New Rule.
3. Enter the values and options depicted in the illustration; click OK.

Working with Themes and Styles

You can change the colors, fonts and styles in a worksheet by clicking on the Layout tab and then
choosing a Theme. Here’s an example of what the Wisp theme would look like when applied to a chart:

71 Excel Fundamentals
You could also choose to only change the colors
or fonts of a workbook by using options from a
drop-down list after clicking on the Page Layout tab:

Add a Picture Background to a Worksheet

1. Choose the Page Layout view at the bottom right side of the worksheet.

2. Click inside the box next to where is says “Header”; click on Design, then click on Picture

3. From here you can choose a picture from a hard drive, flash drive or online. Choose a picture
and click Insert.

Excel Fundamentals 72
Click on the Design tab, then click on the Format Picture button from the Header and Footer Elements
group.

When the dialog box appears, you will be able to adjust the size of the picture background and how it will
appear. On the Size tab you make some adjustments or keep the default settings. On the Picture tab, you
can use the Image control to create the background image. Choose Washout under the Color drop-down.
For this picture, the Brightness will be set at a high percentage (85%) and the Contrast at a low
percentage (15%). The “Crop from” options allow you to adjust the placement of the picture background.

Click OK when finished. You will only be able to see the picture image in the Page Layout view and in
Print Preview, which in this example is a picture of a computer monitor:

73 Excel Fundamentals
Create a Watermark
1. Click on the Insert tab, then click on WordArt from the Text group.

2. Choose the White, Warm Matte Bevel option.

3. In this example we will type the word “Confidential” into the text box.

4. Move and stretch the textbox across the desired area of the worksheet. We can rotate the text
box using the small green circle at the top of the text box.

5. Right-click on the text box and choose the Font option; change the font size to 72, then click OK.

6. Right-click on the text box again and choose Format Text Effects.

Excel Fundamentals 74
 7. Choose Text Fill, Solid Fill and then set the Transparency to 80%; click on the Close button.

75 Excel Fundamentals
Chapter 7: Sorting and Filtering Data

Learning Objectives
After completing this chapter, you should be able to:

1. Apply a simple filter to a column of data

2. Use a Custom AutoFilter to filter numbers and dates
3. Sort multiple levels of data using the Sort dialog Box
4. Perform calculations using the SUBTOTAL function
5. Paste data into cells using an Advanced Filter
6. Organize data using Outlines and Subtotals

The Sort and Filter tools are helpful when you have a large amount of text and numbers in a list or table
and you want to be able to view specific details related to them. For example, a retail store may want to
know what the best-selling products were for a given month and will need to sort the data in order to
generate a report.

Filtering Data

Activity 7.1: Using a Custom AutoFilter

1. Open the Chap7 Sort Filter file, then click on Activity 7.1

2. Click on cell A1, then click on the Data tab on the ribbon; click on Filter. You can also turn on
filters by clicking on cell A1 and pressing CTRL+SHIFT+L.

Excel Fundamentals 76
 3. Go to the Date and click on the down arrow and choose Date Filters; click on Between.

4. From here, we can use the calendar button to select the dates, which in this case will be October
15th to October 31st. After the dates are selected, click OK.

77 Excel Fundamentals
A second filter can be applied to only show the sale of desktop computers between those dates.

5. Click on the Product filter down arrow.

6. Uncheck Select All, then check off Desktop; click OK.

7. You will now see the desktop computer sales numbers between October 15th and October 31st:

Excel Fundamentals 78
Activity 7.2: Sorting Data

1. Click on cell C4, then click on the Data tab; click on the Sort dialog box:

2. Under Sort By, choose Product; under Order, choose A to Z.

2
2

3. Click on Add Level.

4. Under Then By, choose Total Revenue; under Order, choose Largest to Smallest; Click OK.

79 Excel Fundamentals
Activity 7.3: Using the Subtotal Function

To perform calculations on filtered data, you must use the SUBTOTAL function to avoid including
numbers you can’t see because of the filter.

1. Go to the Date filter and choose Between. Choose the dates July 1st and September 30th. Sort the
data from A to Z.

2. Input titles in the rows; Sales, Units Sold and Average Sale:

3. Input the SUBTOTAL function into the cell next to Sales; press the Tab key; double-click 9, then
input a comma:

4. Click on the top of column F; press Enter:

4
4

5. Repeat these steps for Units Sold.

6. Input the SUBTOTAL function for Average Sale but this time click on number 1).

7. Adjust the number formats as needed.

Excel Fundamentals 80
Activity 7.4: Advanced Filters

1. Click on cell H1. With the Data tab selected, click on Advanced Filter.

2. Click inside the List range space, then click on the top of column C to select it.

3. Select Copy to another location under Action:

81 Excel Fundamentals
 4. Click on the space next to Copy to another location, then click on cell H1.

5. Check off Unique Records Only, Click OK.

6. Column H should now look like this:

Excel Fundamentals 82
 7. Double-click on column H to make the column wider.

8. Click on cell I2 and input the SUMIF function; click on column C.

8
8

9. Input a comma; use the left arrow to select “Desktop” (cell H2).

10. Input a comma; click on column F, then press Enter.

10
10

83 Excel Fundamentals
 11. Double-click on the lower left corner of cell I2 to Autofill the rest of the column.

11

12. Change the cell formats in column I2 and change the column title.

12

Activity 7.5: Outlines and Subtotals

Lists that have multiple instances of the same information, such as names of employees or departments,
can be viewed as an outline. In this example, a wholesale company delivers products to retail stores and
wants to keep track of daily sales. They created a spreadsheet with data sorted by store location, so that
subtotal number of units sold and revenue can be displayed. To do an Outline, however, they want the
data to be organized by product.

1. Click on Column C (Product); right click and choose Cut.

Excel Fundamentals 84
 2. Click on Column A; click on the Home tab and choose Insert from the Cells group.

3. With column A still selected, go to the Data tab, then click on the AZ button.
4. With Expand the selection checked off, click Sort.

5. With the Data tab selected, click on Subtotal.

85 Excel Fundamentals
 6. Under At Each Change In, keep the Product option.
7. Under Use Function, keep the SUM option.
8. Under Add subtotal to, click on Units Sold (Total Revenue should already be selected); click OK.

6
7

The list now shows totals for each store with the total for all stores at the bottom of the list. You will notice
that there are Hide Detail Level buttons on the left:

Level 1: Click to show Grand Totals

Level 2: Displays and hides Subtotals
Level 3: Displays and hides all details

Excel Fundamentals 86
Add a Filter to the Subtotals

1. Click on Cell A1.

2. Go to the Data tab, click on the Filter icon.
3. Under Product, uncheck Select All.
4. Check off Desktop, Desktop Total, click OK.

87 Excel Fundamentals
Chapter 8: What-If Analysis

Learning Objectives
After completing this chapter, you should be able to:

1. Use Goal Seek to find a value based on a set of assumptions

2. Use Scenario Manager to create a What-If analysis report
3. Create a one-input and two-input Data Table
4. Explain how What-If analysis tools can be used to make decisions

Goal Seek
This What-If analysis tool helps find the values necessary to achieve a particular result. Let’s say you own
a small business and you want to earn a total of $250,000 this year in operating income (also referred to
as before-tax income). What sales revenue amount would the company need to earn in the last quarter of
the year in order to achieve that goal?

Activity 8.1: Using Goal Seek

1. Choose the cell where the data is to be inputted. This cell must contain a formula or a function, which
in this case is cell G7:

2. Click on the Data tab → Data tools group → What-If Analysis button → Goal Seek…

Excel Fundamentals 88
 3. The Goal Seek Dialog box will open. The dialog box is where you input the data needed to get the
result.

Set cell: G7 is where the current value of

$(238,707) is located
To value: Input the goal of $250,000
By changing cell: We want to see what sales
revenue number in the fourth quarter would be
to generate an operating income of $250,000,
so we will click on cell F3; click OK.

The results will appear in cells F3 and G7:

The Goal Seek solution finds that in order to achieve an operating income of $250,000 (cell G7), the sales
forecast number would need to be $488,706 (cell F3).

Activity 8.2: Scenario Manager

The Scenario Manger What-if analysis to will create spreadsheets that show you how changes in one or
more values will change other values.

89 Excel Fundamentals
A business owner put together a break-even analysis
for their restaurant, but they would also like to see
what the numbers would look in a best-case and
worst-case scenario. Let’s start by applying what’s
referred to as Named Cell References numbers to the
cells. 1
1. Open the Chap9 file; click on Activity 9.2
2. Select cells B5 to C17.
3. Click on the Formulas tab.

4. Under Defined Names, Click on Create from Selection:

5. Click on the Data tab, then click on Scenario Manager from the What-if analysis group

6. The Scenario Manager dialog box will open; Click Add…

7. Under Scenario Name, type Best Case, then press the Tab key
8. Under Changing Cells, click on cell C9, then press the Enter key

You’ll now see a Scenario Manager dialog box appear. We need to enter the number of transactions that
would happen under a best-case scenario. Input 300, then click on Add.

Excel Fundamentals 90
 9. Repeat these steps to create a Worse-Case scenario of 200. After you click OK, you’ll see the
different Scenarios you created. Double-click on Best Case, then double-click to see how the
numbers change in the worksheet. Click the Summary button, then click OK.

10. The Scenario Summary report will appear. You can then make changes to the colors and font
sizes:

Activity 8.3: One Input Data Tables

Data tables are used when you need to evaluate a large number of values at one time. For example, you
can summarize the result of several different interest rates or growth rates and how they will impact
projected sales.

To create a Data Table that calculates growth rates, you can first input a growth rate formula into cell
B4, which would be =(B3+1)*B2. This formula will also be used in the Data Table, so you can then click
on cell B7 and input =B4.

91 Excel Fundamentals
 1. Select all the cells where the results will be inputted. Click on What-If Analysis, then click on Data
Table…

From here you want to use the Data Table dialog box and tab down to where it says Column input cell.

2. Click on cell B3; click OK. Cells B7 to B16 show the project sales numbers automatically
multiplied by the corresponding growth rate figures:

Activity 8.4: Two-Input Data Tables

For a Two-Input Data Table, we want to look at both the rate and the time period in order to get a
summary of different scenarios. Cell C3 has the PMT function, which gives us the monthly payment
amounts based on the inputs from cells B3 to B5.

1. Select cells C3 to F12, then choose Data Tables from the What-If Analysis Group.

Excel Fundamentals 92
 2. Click on the Row input cell will be the Term (cell B5); press the Tab key, then click on the Column
input, which will be the Borrowing Rate (cell B4); click OK.

93 Excel Fundamentals
 Chapter 9: Introduction to PivotTables

Learning Objectives
After completing this chapter, you should be able to:

1. Insert a simple PivotTable and create a report

2. Use PivotTable values to create calculated fields and items
3. Organized Pivotable data into groups
4. Calculate percent of total values using PivotTable data
5. Add new columns to PivotTables that perform calculations
6. Apply Conditional Formatting to PivotTable data
7. Filter PivotTable data by inserting a Slicer
8. Create multiple worksheets using the Report Filter Pages tools
9. Create a simple PivotChart

PivotTables are used to display complex lists of information and allow the user to control how data is
organized in a worksheet. For example, a wholesale distribution company might want to keep product
sales information on a list based on invoice numbers, product codes, units sold, price per unit, date sold
and sales revenue totals. In addition, they may also want to associate product sales with the name of the
salesperson that sold the product.

PivotTables must be linked to a data source and are read-only, meaning that you cannot make changes
by typing into the cells of the Pivottable. The data source can be an Excel list, a database or another
Pivot Table.

Activity 9.1 Create a simple PivotTable

1. Click on the data, then press CTRL+T. Click on the Name Box and input a name for the Table,
which in this case will be ptable.

Excel Fundamentals 94
 2. Click insert and choose PivotTable:

A PivotTable Field List dialog box will appear where you can see that the column labels from the data list
now appear as data fields.

95 Excel Fundamentals
 3. Add the “Customer” to the Row area, “Product” to the Row area, “Units Sold” to the
Values area, and “Total Revenue” to the Values area.

4. You could also choose to have the Products displayed in columns instead of rows. Remove the
fields from PivotTable Fields, then add “Customer” to the Rows area, add “Product” to the
Columns area, then add “Revenue” to the Values area.

Excel Fundamentals 96
 5. Add Borders to the cells by selecting them and then choosing All Borders from the Font group.

6. Change the number format by selecting all the numbers, then right click and choose Number
Format.

97 Excel Fundamentals
 7. Choose Accounting without the dollar sign or decimal points.

Activity 9.2: Create a PivotTable Report

Objective: To create a summary of employee performance, based on number of units sold and the
amount of revenue they generated.

1. Click on the data, click on Insert, PivotTable. Choose Existing worksheet and then click on I5;
click OK.

Excel Fundamentals 98
 2. Add the Fields:

Employee Name – Rows Area

Product – Columns Area
Units – Values Area

3. De-select where it says Field Headers in the Show Group.

4. Go to the Design Tab and choose Light Green style.

5. Make a copy of this PivotTable by selecting it, press CTRL+C, then press CTRL+V.

99 Excel Fundamentals
 6. We want the second PivotTable to show us the amount of revenue generated by each employee,
so press the Esc key to de-select the first PivotTable, click anywhere inside the second
PivotTable, un-check Units Sold, and check off Revenue.

Activity 9.3: Calculated Fields

Objective: Calculate a 7% commission payout for the salespeople.

1. Input the following Fields; Employee to the Rows area, Product Name to the Rows Area
and Revenue to the Values Area.

2. Click on the Analyze tab, then Calculations group; choose Fields, Items and Sets, Calculated
Field:

Excel Fundamentals 100

 3. Enter Sales Commission in the Name Field; press the Tab key. Enter =Revenue*.07. Click Add,
then click OK.

4. Let’s change the Number Format to Accounting with two decimal places and change the Revenue
numbers to Accounting with no decimal places.

4
4

101 Excel Fundamentals

Calculated Field Report (second example)

Objective: Calculate the variance between two time periods.

1. Open the file and check off all three Fields.

2. Change the Number Formats in columns B and C to Accounting with no decimal points.

3. Go to Fields, Items and Sets, then go to Calculated Field.

Excel Fundamentals 102

 4. Add a formula in the spaces inside the dialog box: (YTD Rev. – YTD Rev Last Year)/YTD Rev
Last Year; click OK.

5. Format all the dollar figures to Accounting with one decimal point without dollar signs.

Activity 9.4: Calculated Items

Objective: Create new Fields (Computer, Hardware and Printers) that display the total number of units
sold and revenue of four items (Desktops, Laptops, Monitors and Printers).

1. Insert a PivotTable, then add the Fields:

Product Name – Rows area

Units Sold – Values area
Revenue – Values area

2. Change the numbers in the Revenue Field to Accounting without dollar signs or decimal points.

103 Excel Fundamentals

 3. Change the column labels to Products, Units 3
Sold and Sales Revenue. For the Units Sold
label, you need to insert a space before the
word Units, so that you can avoid getting a
message that says, “PivotTable field name
already exists”.

4. Click on any cell in Column A. Under PivotTable Tools, click on the Fields, Items and Sets drop-
down arrow in the Calculate group. Choose Calculated Item.

5. Under Name, type “Computers and Printers”. Click on the space next to Formula. With “Product”
selected under Fields, double-click on Desktop, add a plus sign, double-click on Laptop, add a
plus sign, double-click on Monitor, add a plus sign, then double-click on Printer; click OK:

6. Click on the Product filter icon. Uncheck the

Desktop, Laptop Monitor and Printer Fields,
since the Units Sold and Revenue numbers
corresponding to those Fields are also in the
Computer and Printers Field; click OK.

Excel Fundamentals 104

Activity 9.5: Organize Data into Groups

Objective 1: Organize the product names into groups.

1. Insert a PivotTable. Check off Product and Revenue under PivotTable Field Names.

2. Click on Desktop, hold down the CTRL key and click on the PivotTable; then click on Group
Selection.

105 Excel Fundamentals

 3. Click on Group1 and change the name to “Computers”.

3 3

4. Select Monitor, hold down the CTRL key and choose Printers; name the group “Peripherals”.
Repeat these steps for the other products and name the group “Other”.

Activity 9.6: Organize data by date

1. Insert a PivotTable; check off Date, Product and Revenue. You’ll notice that the Date Field is
displayed as Years and Quarters.

Excel Fundamentals 106

 2. Right-click on Qtr1, then go to Expand/Collapse. Choose Expand Entire Field. Click on the
PivotTable, then click on the Design tab. Change the PivotTable format by selecting different style
and layout options.

al
2

Activity 9.7: Percent of Total

Objective: To display product and employee numbers as a percentage of total revenue.

1. With the PivotTable selected, move Product to the Rows area and Revenue to the Values area.
2. Change the Number Format to Accounting without decimal points.
3. Make a copy of the PivotTable by selecting it, press CTRL+C, click on the cell just beneath it;
press CTRL+V.

107 Excel Fundamentals

 4. Press the Esc key; with the second PivotTable selected, uncheck Product and check off
Employee.

5. Select both PivotTables, press CTRL+C, click on cell D3, then press CTRL+ALT+V.

Excel Fundamentals 108

 6. Use the up arrow on your keyboard in the PivotTable dialog box to select Column Widths; click
OK, then press the Enter key.

7. Right-click on the first revenue number; choose Value Field Settings.

109 Excel Fundamentals

 8. Under the Show Values As tab, choose % of Column Total; then click on the Number Format
button.

9. Choose Percentage with one decimal place; click OK.

10. Under Custom Name, type Percentage Contribution; click OK.

10

Excel Fundamentals 110

Activity 9.8: Multiple Value Calculations

Objective: To display the total, average and highest sales revenue numbers for each month

1. Click on the PivotTable. Move the Date to the Rows Area, then move Revenue to the Values area
three times:

2. Click on first Revenue value; right-click and choose Value Field Setting.

3. Input the word “Total” in Custom Name, click OK.

111 Excel Fundamentals

 4. Click on the second Revenue value; right click and choose Value Field Setting. Click on Average
under Summarize value field by first; then rename it Average Sale in Custom Name.

5. Click on third Revenue value; right click and choose Value Field Setting.

5
6

6. Repeat steps to choose Max; rename it Highest Sale, then click OK.

Excel Fundamentals 112

 7. Select columns B through D; right-click and choose Format Cells.

8. Choose Accounting with two decimal places; click OK.

The PivotTable should now look like this:

113 Excel Fundamentals

Activity 9.9: Apply Conditional Formatting

Objective: To create a visual that show a comparison between individual product sales revenue and total
sales revenue.

1. Insert a PivotTable, then add these Fields:

Product Name – Rows area

Revenue – Values area
Revenue – Values area (again)

2. Select cells C4 to C13. Click on the Home tab, then click on the Conditional Formatting down
arrow and choose Data Bars, Green Data Bar, Gradient Fill.

3. With the cells in column C still selected (cells C4 to C13), click on the Conditional Formatting
down arrow, click on Manage Rules, then Edit Rule. Check off where is says Show Bar Only, then
click OK. Change the style, column labels and number formatting:

Excel Fundamentals 114

Activity 9.10: Inserting Slicers

Objective: To filter data in one or more PivotTables.

Slicers allow users to filter data in a PivotTable by clicking on buttons instead of using a drop-down list.
The numbers in this PivotTable lists the same invoice number for multiple items that were purchased and
recorded in a sales log. A PivotTable was created so that Slicers can be used to view the employee
name, products and dollar amounts, by invoice number.

1. Click on the PivotTable, then choose the Slicer button from the Filter group. Choose the Invoice #
checkbox, click OK. The Slicer options will appear; check the box for Invoice #, click OK:

115 Excel Fundamentals

When you click on invoice # 81924, for example, the employee that created the invoice will appear. The
PivotTable will now only display the products and revenue numbers associated with that number. Later
versions of Excel will also have a button called Multi-Select next to the Filter icon, which allows you to
select multiple invoices.

Activity 9.11: Report Filter Pages

Objective: To create an individual worksheet for each employee, which shows the units sold and sales
revenue numbers.

1. Create a PivotTable using the following Fields:

Employee – Filters area

Units Sold – Values area
Revenue – Values area

Excel Fundamentals 116

 2. Click on any cell within the PivotTable, then click on the Analyze tab. Click on the Options drop-
down in the PivotTable group. Choose Show Report Filter Pages.

3. A dialog box will appear that displays the Field being used to create the reports; click OK. You will
now see multiple worksheets with an employee name on each tab. If you double-click on cell A3
or B3, a new worksheet will be created that displays a summary of all the numbers used to
calculate the total:

117 Excel Fundamentals

Activity 9.12: Create a PivotChart

Objective: To create a chart using PivotTable data.

1. Click on the data, then click on Insert, Chart, PivotChart.

2. Check off Product and Total Revenue.

3. Click on the Design tab, then choose Layout 7 under Chart Styles; choose Layout 4 under Quick
Layouts; then experiment with the formatting and date filter options (see example below).

Excel Fundamentals 118

 4. To make the PivotChart interactive, click on the PivotTable, then click on the Analyze tab; click on
Insert Slicer.

5. Check off one of the PivotTable Fields (i.e. Product); click OK.

6. Click on the Slicer, then click on Options under Slicer Tools to change the number of columns,
height or width. Click on the different products to see the changes to the PivotChart:

119 Excel Fundamentals

Index
Active cell............................................................... 12 IPMT ...................................................................... 36
AND & OR ............................................................. 41 Legend
Array Formulas....................................................... 30 for Excel data with Conditional Formatting .......... 71
Autofill .................................................................... 15 Line Charts............................................................. 60
Cell Alignment ........................................................ 20 with a secondary axis ......................................... 62
Cell References Links Between Worksheets ..................................... 47
3D reference ...................................................... 48 List Boxes .............................................................. 13
absolute ....................................................... 49, 50 Logical Functions ................................................... 39
mixed................................................................. 50 Lookup and Reference Functions............................ 42
relative ............................................................... 48 Math Functions ....................................................... 33
Center Across Selection ......................................... 21 Merge & Center ...................................................... 21
Charts Name Box ........................................................ 12, 32
create using Excel.............................................. 56 Naming a Cell......................................................... 31
Clipboard Naming a Range of Cells ........................................ 32
cut or paste to the .............................................. 27 NETWORKDAYS ................................................... 41
Column Charts ....................................................... 57 NPER..................................................................... 35
Comments NPV and IRR.......................................................... 36
add to an Excel worksheet ................................. 16 Numerical Axis
Conditional Formatting ............................................ 69 columns charts with a......................................... 59
COUNTA ............................................................... 38 Organize and view workbooks ................................ 11
COUNTIF ............................................................... 38 Paste Special ......................................................... 25
Create from Selection ..... See Named Cell References Picture Background
CUMIPMT .............................................................. 37 add to an Excel worksheet.................................. 72
CUMPRINC............................................................ 37 Pie Charts .............................................................. 65
Custom Fill Series .................................................. 16 PivotCharts ........................................................... 118
Cut, Copy and Paste .............................................. 25 PivotTables
Dashboard Calculated Fields.............................................. 100
create using Excel.............................................. 68 Calculated Items .............................................. 103
Data Tables ............................... See What-If Analysis Conditional Formatting...................................... 114
Date and Time Functions ........................................ 41 Organize Data into Groups ............................... 105
DAYS360 ............................................................... 42 Percent of Total................................................. 107
Drop-Down List Slicers............................................................... 115
in Excel .............................................................. 53 PMT ....................................................................... 35
Editing a Hyperlink .............................. See Hyperlinks PPMT..................................................................... 36
Editing a Pie Chart .............................. See Pie Charts PRODUCT ............................................................. 35
Entering Data ......................................................... 13 PV ......................................................................... 35
Excel Camera ........................................................ 19 Quick Access Toolbar
Excel Templates ..................................................... 20 for Excel ............................................................ 10
Excel Worksheet QUOTIENT ............................................................ 35
twelve major parts .............................................. 12 RATE ..................................................................... 35
Filtering Data.......................................................... 76 Ribbon ..................................................................... 7
Financial Functions ................................................ 35 Rotated Text........................................................... 21
Formatting Cells ..................................................... 13 Shortcut Keys ......................................................... 22
Formula Bar ........................................................... 12 Sparklines .............................................................. 68
Function Arguments ............................................... 29 Statistical Functions ................................................ 38
Function Name....................................................... 29 Styles
HLOOKUP ............................................................. 43 for Excel worksheets .......................................... 71
Hyperlinks .............................................................. 17 SUM....................................................................... 33
IF ........................................................................... 39 SUMIF.................................................................... 33
IFERROR............................................................... 40 SUMIFS ................................................................. 34
INDEX and MATCH................................................ 44 SUMPRODUCT...................................................... 35
Inputting Formulas .................................................. 30 Syntax
Insert Function Button ............................................ 12 definition of ........................................................ 29
Insert Worksheet Table
button to create a new worksheet ....................... 12 format Excel data as a........................................ 54

Excel Fundamentals 120

Text Functions ....................................................... 45 What-If Analysis ..................................................... 88
Text to Columns ..................................................... 24 Data Tables ....................................................... 91
what is ............................................................... 23 Goal Seek .................................................... 88, 89
Themes Work area
for Excel worksheets .......................................... 71 of an Excel worksheet ........................................ 12
Transpose Data Wrap Text .............................................................. 21
within Excel........................................................ 26 XNPV and XIRR ..................................................... 37
View Buttons .......................................................... 12 XY Scatter Charts .................................................. 66
VLOOKUP ............................................................. 42 YEARFRAC ........................................................... 42
Watermark Zoom Slider............................................................ 12
create using WordArt ......................................... 74

121 Excel Fundamentals