Please follow the instructions step-by-step; if you just do the project without following the instructed steps, that will be a failing grade. The professor will track back the steps to ensure good comprehension. All the materials are included; it is a matter of following the steps.
1
Microsoft Excel
Overview
Microsoft Excel is a spreadsheet application and one of the Microsoft Office products.
Excel is used to organize, analyze, and interpret data.
In this lesson, you’ll learn about the features of Excel and how they’re used to produce
spreadsheets that include formulas, functions, charts, and tables. You’ll also learn about
spreadsheet planning and how to format and present data for the greatest impact. Whether you
use Excel professionally, academically, or personally, the techniques you’ll learn in this lesson
will help you use spreadsheets effectively.
Objectives
•
Identify the features of the Microsoft Excel interface
•
Describe how to create a spreadsheet
•
Explain how to apply formatting to make worksheet data easier to understand
•
Identify the steps needed to produce calculations using formulas and functions
•
Describe how to interpret data through charts
•
Analyze data organized in tables
Identify the Features of the Microsoft Excel Interface
Getting Started
Read this assignment.
Microsoft Excel is a spreadsheet application that’s used to store, analyze, and present
data. It can be used for budgets, payroll, inventory, sales figures, and much more.
2
The instructions to start Excel will vary depending on your installation. However, you’ll
most likely need to click the Excel icon in the Taskbar at the bottom of the screen or double-click
the icon on the Desktop.
Excel is a popular program in many workplace settings. Because of its versatility, it can
be used to organize data in a number of ways. Here are two professions where Excel might be
used daily.
•
Veterinary Assistant
o
Record client appointments, admissions, information (such as breed, coat color,
gender), treatments, medications, and payments.
o
Organize data to create charts and graphs from this information. For example, the
veterinary assistant could see which type of heartworm medication is the most
popular with their clients or whether the number of spayed or neutered cats
changes over time.
•
Guest Services Agent
o
Record room or restaurant reservations made online or over the phone to see
changing trends over time.
o
Create a graph showing which month the hotel had the most reservations, to be
prepared for the next year. Data can also be organized showing when families
booked rooms versus individuals or couples; when and how often clients used the
concierge, restaurant, or room service options; or which amenities clients found
satisfactory or unsatisfactory.
3
The Excel Window
After starting Excel, the first thing you’ll see is a screen with links to recently opened
documents on the left. On the right are links to create a new blank workbook or one based on a
template.
You’ll see
this screen after starting Excel.
Click Blank workbook to display the Excel window with a new
spreadsheet. Workbook refers to the entire Excel file, which contains one or more worksheets.
The Excel window displays a single worksheet, also called a sheet, which is the Excel term for a
spreadsheet.
An Excel Window with a Blank Workbook
4
Other features of the Excel window include the following:
•
The Quick Access Toolbar, which is located on the upper left of the Excel window,
contains shortcuts to commonly used commands. The default shortcuts are Save,
Undo, and Redo. You can click the last icon, Customize Quick Access Toolbar, to add
or remove shortcuts.
•
The filename (the workbook name) is displayed in the top center of the window. When
you save a new workbook, the name you provide will be displayed here.
•
Window controls in the upper-right corner are the Minimize button, which hides a
window; the Restore button, which reduces or expands the window size; and
the Close button, which closes the application window.
•
The Ribbon contains Excel commands. Commands are organized into tabs that run along
the top of the Ribbon. Clicking a tab displays a different set of commands. Within a tab,
commands are grouped. Some groups include a dialog box launcher—a small arrow
icon—in the lower-right corner that you can click to display a dialog box or task pane
with additional options related to the group. Some commands on the Ribbon have an
arrow you click to display a menu of additional options.
•
The File tab is different from other Ribbon tabs. You click File to display the Backstage
view with options for opening, saving, printing, sharing, and closing a file. To get back to
the spreadsheet window without executing a command on the File screen, click the Back
Arrow in the upper left of the screen or press the Esc key.
•
The Tell Me box is a search tool and help feature. When you can’t locate a command, or
when you want to learn how to perform an action, click Tell Me or press Alt+Q and type
any word or phrase in the box to display a menu of related search results. The results will
5
include related commands and an option to display a help dialog box with more
information.
•
Columns run vertically and are identified with letters.
•
Rows run horizontally and are identified with numbers.
•
A cell is the intersection of a row and column and has a cell name, which is made up of
the cell’s location in a column and row, such as A1. The name of the selected cell is
displayed above the sheet in the Name Box. The cell name is also called the cell
reference.
•
The active cell is also called the selected cell. It has a bold border and is where the data
you type will appear.
•
The formula bar is displayed above the column letters and displays the contents of a
cell.
•
Scroll bars are located along the right side of the window and below the worksheet. You
use them to bring unseen cells into view.
•
The Sheet tab is displayed at the bottom of a worksheet. Clicking the New Sheet button
adds an additional sheet to the workbook.
•
The status bar runs along the bottom of the window. On the right side of the status bar is
a zoom slider you can use to change the magnification of your worksheet. Icons for
changing the worksheet view are also near the zoom slider.
•
ScreenTips are small boxes that pop up when pointing to a command on the Ribbon or
other features on the Excel document window. ScreenTips display information about a
feature, including keyboard shortcuts for executing the feature without having to remove
your hands from the keyboard.
6
Working with Input Devices
The input devices you’ll most likely use with Excel are the keyboard and the mouse or
touchpad. You use the mouse or touchpad to point to commands and cells, to click commands
and cells, and to drag cells.
The keyboard is used to enter data into your worksheet. The data you type appears in the
active cell. Along with keys for typing data, the keyboard has keys for selecting a cell and
communicating with Excel:
•
The Tab key moves to the next cell in a row.
•
The Enter key moves to the next cell in a column.
•
The arrow keys select the next cell in the direction of the arrow. Ctrl+arrow selects the
last cell with data.
•
The Home key selects the cell at the beginning of a
row. Ctrl+Home and Ctrl+End move the insertion point to the first or last cell of data.
•
The Delete key is used to remove the active cell contents.
•
The Page Up and Page Down keys are used to scroll a sheet within the window.
•
The Esc key’s function varies depending on the action, but it’s commonly used to cancel
the current operation.
Freeze Worksheet Rows and Columns
Freezing panes allows you to keep one area of a worksheet static while letting you scroll
to a different area within that worksheet. To freeze panes, go to the View tab and lock your
selected rows and columns so the data is stationary on the sheet. The split panes view will create
separate windows within the same worksheet.
7
Freeze the first column by selecting Freeze First Column from the Freeze Panes group
in the View tab. A line will appear after the first column, between A and B, to show that the first
column is frozen.
To freeze the first two columns, select the third column and click Freeze Panes from
the Freeze Panes group in the View tab.
To freeze columns and rows, select the cell below the rows and to the right of the column
or columns you want to keep visible when you scroll. Then select Freeze Panes from the Freeze
Panes group in the View tab.
Change Window Views
When working in Excel, especially with multiple sheets, it may become necessary to
view data from multiple sheets together at one time to compare and contrast data more easily. To
change the windows view of workbooks and spreadsheets from the View and Window group, use
the options Arrange All, Split, and Side by Side.
•
Arrange All allows for all open windows to be arranged in an easy-to-see format,
cascading the sheets so they’re individually accessible.
•
Split view splits the section of the current worksheet into four sections, allowing you to
scroll to different areas in the spreadsheet while freezing other areas.
•
The Side by Side view arranges the window so two worksheets are arranged side by side
for easy comparison and viewing.
The toolbar contains various functions.
8
Modify Workbook Properties
In Excel, Workbook properties can identify key pieces of information like who created
a file, when it was created, when it was last modified, and what the current status is. Workbook
properties include a function that allows you to add tags to a workbook file. Tags are short
descriptions, or keywords, that help identify the kind of content contained within a file.
Tell Me How
The Microsoft Excel application has many features. You can use the Tell Me box to help
you understand these features. Explore the features of Excel by typing questions or phrases
related to document creation into the Tell Me box. Click the Get Help option at the bottom of the
menu to learn more.
Key Points and Links
Key Points
•
Excel has many features to help you organize data.
•
When you start Excel, you’ll see the Start screen with links to spreadsheets and a link to
create a blank workbook.
•
To help you perform an action quickly, the Quick Access Toolbar is located in the upper
left of the Excel window.
•
You can use the Tell Me box to learn more about the commands and features of Excel.
•
A workbook consists of columns that run vertically and rows that run horizontally.
•
Columns are labeled with letters, and rows are labeled with numbers. You can enter data
into the active cell, also called the selected cell, which has a bold border.
•
ScreenTips display information about a command or feature.
9
•
The Tab and Enter keys are used to move from one cell to another. When you type data,
it appears in the selected cell.
Describe How to Create a Spreadsheet
Entering and Editing Data
Read this assignment.
Microsoft Excel makes entering and editing data easy and efficient. It has features such as
AutoCorrect, AutoComplete, and the Spelling command to help keep data accurate. Other
features such as Cut, Copy, Paste, and the ability to move cells help organize data without having
to retype it.
Planning Your Worksheet
A Work Schedule
A worksheet is rows and columns of data; therefore, it’s important to develop a plan based on
this structure. To avoid needing to make changes later, you should decide beforehand which data
belongs in rows and which will be better in columns. In most cases, columns are for groups and
rows are for singular items. For example, a work schedule will probably be best organized with
employees in rows and those working on a given day grouped into columns.
Watch this video to learn about rows and columns. Download the transcript here.
Entering Data into a Worksheet
When you’ve gathered the information you want to organize, it’s time to enter data into your
spreadsheet. In addition to the actual data, you should enter labels to identify the purpose of the
worksheet, columns, and rows. Labels are text used for descriptions and aren’t used in
calculations. Data can also be text, but more often data are in the form of values or dates and
times. A value is numeric and can be used in calculations. Date and time can also be used in
calculations and is a code that represents a date, a time, or both.
It’s important to use labels to define rows, columns, and cells in your worksheets. The
information you enter might make sense to you when you enter it, but you might forget which
values were which over time or as more information is entered. Additionally, you’ll rarely be the
10
only person to use the worksheet, so labeling rows and columns will help others make sense of
your data.
To enter information into a cell, select a cell to make it the active cell, and then type your label or
data. When typing, the contents appear in the Formula bar and the Cancel and Enter buttons are
active. You can find the Cancel and Enter buttons above the cells right underneath the ribbon.
The Formula bar can be found in the same location.
The Cancel and Enter buttons and the Formula bar
appear above the cells.
You can select Enter to change the cell contents to the typed data or choose Cancel to leave the
cell as it was.
The most efficient way to enter data is to keep your hands on the keyboard. As you type,
press Tab to move to the next cell in the row or press Enter to move to the next cell in the
column. If you’ve entered data into a row of cells, pressing Enter selects the first cell in the next
row.
Differentiate Between Cell Name and Cell Contents
A cell name refers to the column and row of the cell’s location. For example, cell A9 is the cell
at the intersection of column A and row 9. This is also called a cell reference. The cell
contents are what a cell contains, such as data or a label. You can compare a cell to a mailbox
with an address and a storage area.
Change Cell Width and Height
Truncated cells appear as pound signs.
Sometimes the information you include won’t fit within the cell width because it’s longer than the
given space. If the next cell doesn’t contain data, your information will extend into that empty
cell. However, if the next cell contains data, then long data are truncated, or shortened. When
you type a numeric value that’s too large for a cell, the cell displays #####, as in the example
figure.
11
Change the column width by dragging the column
boundary.
You can display data entirely by changing the column width for the cell. You do this by dragging
a column boundary at the top of the sheet. A boundary is simply the line between columns or
rows. When pointing to a column boundary, your pointer changes to a double-headed arrow.
A long title in column A causes the data to display
across several cells.
You can size a column to best fit the data it contains by double-clicking a column boundary. This
formatted sheet has best-fit column widths for columns B, C, and D. Note that if you try to bestfit column A, the long title will cause a very wide column A.
Similarly, you can change a row height by double-clicking a boundary or by dragging.
Use AutoComplete and AutoCorrect
When you type data, Excel makes a best guess at what you’re trying to enter. If the cells in the
same column have entries similar to what you’re typing, Excel tries to complete the entry for
you. For example, if the words “any town” already exist in the Excel sheet and you start typing
the words “any town” again in a different cell, Excel will autocomplete these words in that cell.
An Example of AutoComplete
12
You can choose Tab or Enter to accept the suggested text or continue typing for a different
entry.
When you type labels and other text, Excel uses the AutoCorrect feature to automatically change
obvious misspellings such as “thier” for “their.” Always double-check data entry for unwanted
AutoComplete and AutoCorrect changes.
Excel doesn’t automatically flag misspelled words as you type. For professional results, you
should always check the spelling in a worksheet before sending it off or printing it. You can
check the whole sheet at once with the Spelling command on the Review tab, which displays a
dialog box allowing you to correct unrecognized words.
Editing Cell Contents
Corrections and updates are common with spreadsheets. To change the data in a cell, simply
select the cell and type a new entry to replace the current one. If you want to edit an entry, select
the cell and press F2 to place the insertion point in the cell. You can also select a cell and then
click in the Formula bar to display the insertion point there. When the insertion point is
displayed in your data, you can use the Backspace key and arrow keys to correct just a portion
of the entry.
To remove data from a cell, click the cell to make it active and then press Delete. To remove a
recent entry, click Undo on the Quick Access Toolbar or press Ctrl+Z to reverse the last
action.
Insert and Delete Rows and Columns
When you need to expand a worksheet, right-click the column letter that’s to the right of where
the new column should appear, and then click Insert. You can also click a column header to
select the entire column and then click Insert on the Home tab. You can also select a column
and press both Ctrl and the plus sign key (+) on the number pad or Ctrl, Shift, and the equal
sign key (=) on the number pad.
Rows are inserted similarly. Right-clicking on a row number and selecting Insert will create a
row above the selected row. You can also use the Insert command on the Home tab to add a row.
You can also select a row and press both Ctrl and the plus sign key (+) on the number pad
or Ctrl, Shift, and the equal sign key (=) on the number pad.
Delete a column or row by right-clicking the column letter or row number and clicking Delete or
by pressing both Ctrl and the minus sign key (-) on the number pad. Be careful that this is what
you want to do—the entire column or row and all of its data will be removed. To reverse this
action, either press Ctrl+Z or click Undo on the Quick Access Toolbar.
Move and Copy Cells
You can make edits to a spreadsheet by copying and moving cells.
13
1. Select the cell to be moved or duplicated.
2. On the Home tab, in the Clipboard group, click Cut (Ctrl+X) to move data
or Copy (Ctrl+C) to duplicate it. A moving, dashed border will appear.
3. Select the cell to receive the data.
4. On the Home tab, in the Clipboard group, click Paste (Ctrl+V). If data was cut, then the
previously selected cell will be blank. Copying a cell keeps the data in the original cell.
When you move or duplicate data with Cut and Copy, it’s placed in an area of memory called
the Clipboard. You can view Clipboard items from the Home tab in the Clipboard group where
you can click the dialog box launcher to access the Clipboard task pane. Select any item in the
task pane to place it into the selected cell. You can delete items from the Clipboard by pointing
to the item, clicking the drop-down arrow, and then clicking Delete.
Excel warns you if you’re about to move a cell onto
one that already has data in it.
Cells can also be moved by clicking and dragging them to a new location. First, point to the cell
border until the cursor changes to a four-headed arrow, then drag the cell to a new location. If
you try to place the cell at a location that already has data, a warning pops up. Click OK if you
want to replace the contents or Cancel to leave the cell in the previous location.
Use Special Paste Options
Paste Special has features to allow special paste options for mathematical operations, values,
formats, validations, and many other options. For example, text copied from the internet or a
PDF file often retains much or all its original formatting, which may not match the colors, fonts,
or other text formatting being used in a document or spreadsheet. When using Paste Special, you
can paste the text into a document without its original formatting. Unformatted text may have a
different font theme, bolding, or hyperlinks.
Access the Paste Special dialog box by selecting the dropdown menu arrow next to Paste in
the Clipboard group in the Home tab and choosing Paste Special.
Use Autofill
The Autofill feature attempts to see an existing pattern in the data entered and then apply that
pattern to filling in additional cells. The following steps show how to complete an Autofill.
1. Select the sequence of cells that the pattern will be based on. When selected, the cell or
range will display within a green border.
2. At the bottom-right corner of the cell or range, a Fill Handle will display. Drag the Fill
Handle until the border surrounds the desired range of cells and then release it.
The Autofill feature captures duplicate text entries in the same column. If text is typed into a cell
that matches the beginning of another cell’s content, Excel will automatically complete the entry
to match the existing content.
14
You can insert cells in an Excel sheet
by using the Insert options.
You can
15
delete cells in an Excel sheet by using the Delete options.
Insert and Delete Cells
In your data entry process, the amount of text you need to display in a cell may be more than the
cell can hold. For example, your finance team might create a new metric to which certain
commission dollar amounts are tracked.
To include information on the new metric, you may have to add a row or a column right in the
middle of the existing data. Or maybe you want to view your data in a different way to focus
more on one element of a system. When situations like this arise, you’ll need to be able to modify
the cells, columns, and rows in your worksheet to best fit your needs. The Insert Cells/Delete
Cells commands and features allow you to make these kinds of changes.
The Insert and Delete options are for inserting and deleting cells. Insert or delete a cell or group
of cells by using either the Insert Cells or the Delete Cells command from the drop-down menus
or with the Insert or Delete dialog boxes.
Page Layout Features
You have many options on the Page Layout tab for controlling how your worksheet is printed:
•
•
•
•
•
•
Select Margins to change the space around the worksheet when printed. By making
margins smaller, you can get more rows and columns on a page.
You can change the Orientation to Landscape to print more columns on a page (but
fewer rows).
Select Breaks and then Insert Page Break to insert a break above the selected cell.
When you control where one page ends and the next begins, you can provide more useful
printouts.
In the Page Setup group, click the dialog box launcher and then click
the Header/Footer tab for options to add information to be printed at the top or bottom
of each printed page. Headers and footers are especially helpful with worksheets that
print on multiple pages.
Scale to Fit options are used to format your worksheet to fit on a designated number of
pages.
Select the Gridlines Print option to make data more readable on a printout. Without this
option selected, a printout doesn’t include the cell borders. The Headings Print option
works similarly to add column letters and row numbers to a printout.
Save as an Alternative File Format
The default file for Excel workbook files is the .xlsx format. There are other file formats for
Excel to share with other users, to upload to websites, or for other uses. These alternative file
formats will save the current Excel file per the Save As feature command.
Watch this video to learn how to save workbooks in other formats. Download the transcript here.
Inspect Workbooks
16
The Excel Compatibility Checker is an option to check the compatibility of objects and data in a
workbook when the file may have been saved in an earlier file format. Selecting the Check
Compatibility command opens the Microsoft Excel – Compatibility Checker dialog box. To
access this, select File > Info > Check for Issues. Excel will automatically run this check when
a current file is saved in a previous file format.
Import Data into Workbooks
Importing is the process of bringing information or data into an application or database from an
outside source. Excel has functionality for importing several types of data sources such as
Access databases, text files, or files from the web.
Import Data from .txt and .cvs Files
Importing text files is an option that allows you to import data from simple text files that are
delimited in a variety of ways.
Paste Text file (.txt) Data—Text files contain plain text and are commonly created with
NotePad. If you paste content into a text file, all the styles and formatting are stripped
out, leaving you with plain text. This makes it easy to copy and paste data without
formatting issues. If you paste spreadsheet data into a .txt file, it’s separated by tabbed
spaces.
• Paste Comma Separated Value (CSV) Data—A CSV file also contains plain text that can
be opened in Excel. If you paste spreadsheet data into a CSV file, it’s separated by
commas.
• Import Delimited text files (.txt)—A delimited text file is a plain text file, typically
created with NotePad, with data separated by tabbed spaces. It can be imported into
Excel.
• Import Comma separated values text files (.csv)—A CSV file, with the data separated by
commas, can be imported into Excel.
There are two ways that you can import data from text files into Excel. You can either open the
text file within Excel or you can import the data from the text file as a data range. If the file is
opened directly in Excel, you don’t need to create a link to the text file. If you import the data,
you’ll need to link the workbook to the text file for changes to the text file to reflect in the Excel
workbook.
•
Import Data Using the Text Import Wizard
There’s an easy Text Import Wizard process that will walk you through the process of
identifying the type of data a file contains and where the import should start, the type of
characters that separate entries in delimited files, and the selected cell formatting options for the
columns that will contain the data after importing. You can access the Text Import Wizard in two
ways:
17
1. Open a text file using File > Open.
2. Select Data > Get External Data > From Text, and then select the desired text
document from the Import Text File dialog box.
Search for Data within a Workbook
There are various options to find and search for data, values, formulas, or formatting in an Excel
worksheet or workbook. One option is to use the Find command to locate specific content within
worksheets and workbooks. When the Find command is selected, the Find and Replace dialog
box opens with the Find tab automatically selected. To access the Find command,
select Home > Editing > Find & Select > Find, or press Ctrl+F.
Reflect and Respond
Spreadsheets often have large amounts of data. What mistakes might you make
Key Points and Links
Key Points
•
•
•
•
•
•
•
Develop a plan for your data based on the rows-and-columns structure of a spreadsheet.
A worksheet typically contains labels as well as data.
Labels describe cell data, while data can be text, numbers, dates, or times.
The typed data appear in the active cell, also called the selected cell. When you type data,
press Tab to move to the next cell in a row or Enter to move to the next cell in a column.
A cell name refers to the column and row of the cell’s location.
If a cell doesn’t display its contents entirely, you can drag the cell boundary to increase
the width of the entire column.
To change the data in a cell, click the cell and then type a new entry. If you want to edit
existing data in a cell, click the cell and press F2 or click on the Formula bar to place the
insertion point.
Links
•
Microsoft Excel File Extension Types
Guided Practice: Organizing a Worksheet
(Semester Grades)
Complete this guided practice activity to learn the steps for organizing a worksheet.
18
You’ve decided to keep track of your semester grades with a spreadsheet. Follow the directions
below to organize your grades in a worksheet.
1. Create a new Excel workbook.
2. Select cell A1 in the upper-left corner of the worksheet if it’s not already selected. (Note
the cell name in the Name box.)
3. Type “Semester Grades” in cell A1 and select Enter. (Notice how the cell A1 entry
extends into the next cell.)
4. Type “Class” in cell A3 and select Tab.
5. Type “Instructor” in cell B3 and select Tab.
6. Type “Grade 1” in cell C3 and select Enter. (Notice that the next cell selected is the first
cell in row 4.)
7. Type “English 1” in cell A4 and select Tab.
8. Type “Cohen” in cell B4 and select Enter.
9. Type “Sociology” in cell A5 and select Tab.
10. Type “Foster-Smith” in cell B5 and press Enter.
11. Type “Biology” in cell A6 and select Tab.
12. Type “Lopez” in cell B6 and select Enter.
13. Type “College Math,” in cell A7 and select Tab.
14. Type “Kim” in cell B7 and select Enter.
15. Point to the boundary between columns A and B until the pointer changes to the doubleheaded arrow shape.
16. Drag the column A boundary with the double-headed arrow pointer until the cell
boundary is approximately halfway through the “d” in Grades in cell A1.
17. Release the mouse. (Note that column A should now be wide enough to entirely display
“College Math” in cell A7. )
18. Point to the boundary between columns B and C until the pointer changes to the doubleheaded arrow shape.
19. Double-click the column B boundary with the double-headed arrow pointer. (Note
that column B widens to a best fit for the data it contains.)
20. Right-click cell A7 and select Insert to add a blank row between Biology and College
Math.
19
21. Type “Biology Lab” in cell A7 and select Tab.
22. Type “Douglas” in cell B7.
23. Select cell B8.
24. Type “Soto” in cell B8 and select Enter to replace the original data.
25. Select cell B4 and use the Formula bar to display the insertion point.
26. Use the arrow keys to move the insertion point to the end of Cohen, if necessary.
27. Type an “s” at the end of Cohen and select Enter. “Cohens” is now displayed in cell B4.
28. Select the entire row 6.
29. Insert a blank row on the Home tab.
30. Select cell A9 (“Biology”).
31. Select Cut (Ctrl+X) on the Home tab. (A moving, dashed border appears.)
32. Select cell A6.
33. Select Paste (Ctrl+V) on the Home tab to move the data. (College Math is now right
above Biology.)
34. Select cell B9 (“Soto”).
35. Point to cell B9’s border until the cursor changes to a four-headed arrow.
36. Drag the cell to cell B6 (next to College Math).
37. Save your workbook, naming it “Semester Grades.”
Your worksheet should look similar to the table below.
Semester Grades
Class
Instructor
English 1
Cohens
Sociology
Foster-Smith
College Math
Soto
Biology
Lopez
Biology Lab
Douglas
Grade 1
Explain How to Apply Formatting to Make Worksheet Data Easier to Understand
Formatting Data
Read this assignment.
Spreadsheets must convey organized thinking in an understandable way to be useful. Making
sure the numbers are correct is a priority, but how those numbers are presented determines if a
reader can make sense of your data.
20
Formatting Cell Contents
Your worksheet isn’t the place to experiment with numerous fonts and styles, but you should
make titles larger, and bold or colored labels might be more eye-catching. Visually, your
spreadsheet will be easier to read if data is aligned properly. For example, numbers should be
right-aligned, headings should align with their data or at least be centered above the data, and
labels that are considerably longer than their data should be rotated or wrapped within a cell.
Selecting Cells
The first step to change the look of a cell is to select the cell to be formatted. You can select
several cells at once to apply formats faster.
When cells are selected together, they form a range. To select cells, drag from one to another.
When you select more than one cell at a time, Excel
highlights the range of cells in your selection.
For ranges that extend beyond the window, click the first cell in the range, scroll until the last
cell is visible, and hold the Shift key while clicking the last cell in the range. If you want to use
the keyboard to select a range, select the first cell of the range, hold the Shift key, and press the
arrow keys until the last cell in the range is reached.
To select all the cells in a row or column, select the row number or column letter.
Fonts and Alignment
Changing the font in a worksheet should be based on readability or company standards. Aside
from the worksheet title, which may need to be in a font that reflects the company logo, other
labels will be easier to read in a sans serif font, such as Calibri or Arial.
Bold and italic words are useful for drawing the reader’s eye to the information. Some styles,
such as underline, are usually not a good choice because the style competes with the cell border
and is also associated with hyperlinks. Color can also be used for cell data but should be used
with thought to how it will print.
21
The Font and
Alignment groups on the Home tab allow customization of how your cells look.
Cell alignment can be left, center, or right. Since numbers are right-aligned in a cell by default,
your column labels for the numbers will be best right-aligned or centered.
To apply font or alignment formats, first select the cells to format, and then use commands in the
Font group and the Alignment group on the Home tab.
Hyperlinks
A hyperlink is a link within a document that, when clicked, navigates to a certain point in a
document, opens another document, navigates to a webpage, or opens to start an email.
Hyperlinks can be added or modified within a worksheet using the Insert Hyperlink dialog box
or the Edit Hyperlink dialog box. You can also press Ctrl + K to access the Insert
Hyperlink dialog box. Access the Insert Hyperlink and Edit Hyperlink dialog boxes by
selecting Insert > Links > Hyperlink.
A hyperlink can be removed by using the Remove Link button. Another option for removing
hyperlinks is the Clear command, which is in the Editing group on the Home tab.
•
•
Clear Hyperlinks clears hyperlinks from cell contents. Cell formatting remains,
including formatting applied to text when the hyperlink was created.
Remove Hyperlinks removes hyperlinks and all formatting.
Format Cells Using Format Painter
Use Format Painter when you want to copy formatting from one item to another. The Format
Painter copies and pastes formatting (font type, color, and size) from one cell or range to
another cell or range.
If you have a large number of cells in a worksheet to which you would like to apply formatting
changes, the Format Painter feature allows for a broad paintbrush effect on a large number of
cells at one time. By using the Format Painter, you can reapply existing formatting to other cells
on the same worksheet, to other cells in the same workbook, and in other open workbook files.
Access the Format Painter from the Clipboard group on the Home tab.
22
Clear Cell Formatting
In addition to using the Delete or Backspace key, to delete cell contents, you can use the Clear
feature. This removes formulas and data, formats such as number formats, conditional formats,
and borders, and any attached comments. The remaining cells are blank or unformatted once the
Clear feature has been applied.
To clear the cells of content or formatting, choose the desired cell, row, or column of data that
you want to remove. On the Home tab, under the Editing group, an arrow next to
the Clear button reveals a list of options:
•
•
•
•
•
Clear All removes the contents in the selected cells, rows, or columns, along with any
formatting and comments.
Clear Formats removes the formatting that’s associated with the selected cells, rows, or
columns.
Clear Content removes the contents within the selected cell, row, or column.
Clear Comments and Notes removes the comments or notes associated with the selected
cell, row, or column.
Clear Hyperlinks removes any formatted hyperlinks that are associated with the selected
cell, row, or column.
Format and Modify Text
As you work with Excel, you’ll either enter data yourself or be provided data or text from a
database or other systems and applications. With the Text function in Excel, you can manipulate
text in cells to extract sections of text to other cells or combine them with various formatting
options to create a list or report.
The more common text formatting functions are RIGHT, LEFT, MID, UPPER, LOWER,
CONCAT, and TEXTJOIN.
The RIGHT function returns the first character(s) in a text string based on the number of
characters specified. For example, for the name Huckleberry Finn in cell A2, you can use the
RIGHT function in cell B2 to extract five characters of that text from the right, resulting in a cell
with the last name Finn. For the name Luke Skywalker, use the RIGHT function for cell A3 to
extract ten characters from the right for the next result in the Last Name column, cell B2, of
Skywalker.
The LEFT function returns the first characters in a text string based on the number characters
selected. For the full name Huckleberry Finn in cell A1, use the LEFT function to extract the
first ten characters of the text string, resulting in the first name Huckleberry.
The MID function returns the characters from the middle of a text string, starting at an identified
position, based on the number of characters chosen. For example, an inventory list can be used to
extract information from one column. In this instance, the Description column is used to extract
the fourth character and return the next four characters. The description column C3 for
XCA9883619CBA with the MID function applied results in 9883.
23
The UPPER function converts a text string to uppercase or capitalizes all the text in the string.
The LOWER function coverts a text string to lowercase or capitalizes all the text in a string.
Note that numbers don’t change when the LOWER function is used.
Format Text Using the CONCAT() and TEXTJOIN()
Functions
One of the most powerful text functions in Excel is the concatenate, or CONCAT function.
This function allows you to join together text strings from multiple cells on a worksheet into a
single cell. This function can save time when you need to pull data from multiple cells that
already exist within your worksheets.
For example, imagine that you’re the coordinator for a large event with hundreds of attendees,
and the attendee list you received from the vendor is an Excel worksheet with attendee first and
last names in separate columns, but you would prefer the names to be in a single column. The
CONCAT function is a great option for this task.
Change a cell border using the Border
command on the Home tab.
24
The TEXTJOIN function combines the text from multiple ranges or strings. It includes a
specified delimiter between each text value that will be combined. If the delimiter is an empty
text string, this function will effectively concatenate the ranges.
Applying Borders
Spreadsheets are about presenting data in cells, and Excel has many cell-border options.
Applying solid borders to all sides of cells containing data makes printouts easier to read. Other
border formats, such as Bottom Double Border, are useful for specific purposes like totals. To
change a cell border, select the cells to format and then select the arrow in the Border command
on the Home tab to display choices.
Wrapping and Rotating Text
When you have long labels, you can end up with columns that are much too wide and affect the
readability of your worksheet. Before you consider formatting options, carefully think about the
label. Can it be just as descriptive with fewer words? Shorter labels are easier to read. Should the
data be divided into more columns? For example, a First and Last Name column may not be a
good idea. A cell should contain a single data item.
You can rotate words that are too long for your cells.
But if a long label is needed, you can either wrap the text in a cell or rotate it with Wrap
Text and Orientation commands in the Alignment group on the Home tab. The cells below
display both formats.
Merging Cells
Your worksheet is more descriptive with a title centered at the top. Centering data across many
cells is accomplished by using merged cells, which are cells that have been combined into one
larger cell. To combine two or more cells, you first select the cells to merge and then
select Merge & Center on the Home tab. To reverse the merge, select the merged cell, select the
arrow in the Merge & Center command, and then select Unmerge Cells.
Themes and Cell Styles
You can change the overall look of your worksheet in one step with options in the Themes group
on the Page Layout tab. A theme is a named set of colors, fonts, and effects. You may even be
asked to use a specific theme for your company.
A theme has several cell styles associated with it. A cell style is used to apply several formats at
once to a selected cell or cell range. Cell styles are applied with options in the Styles group on
the Home tab. You can use the More button at the end of the group to expand the list to see
the Themed Cell Styles.
25
Themed Cell Styles
If you use a themed cell style, it will be applied to the selected cell or cell range. Later, if you
change the theme, color, font, or effect from the Page Layout tab, any cells with applied styles
will be updated to match automatically.
Adding Graphics
A company logo or other related graphic can be used on your worksheet to give it a better overall
presentation. You can do this with commands on the Insert tab. It doesn’t matter which cell is
selected when you insert a graphic because images are by default freely moving objects.
A handle allows you to rotate the image.
26
To insert an image from your computer, select Pictures on the Insert tab, and then navigate to
the image you want. For a general-purpose image, you can select Online Pictures on
the Insert tab to display a dialog box where you can search for a clip art image online. Clip art
from the internet is protected by copyright through a Creative Commons license. A link to this
license is available in the Insert Pictures dialog box.
After you insert an image, you can select it to change its size or rotate it by dragging a handle.
To move the image, drag it to a new location. When a picture is selected, the Picture Tools
Format tab is added to the Ribbon. You can use this tab to change picture styles, borders, and
effects.
Formatting Numeric Data
Apply a format to an empty cell using
the Number Format list on the Home tab.
Your worksheets will be easier to understand if you format numeric data appropriately. For
example, a number for the items in stock can’t have a decimal portion, and dollar amounts should
display a dollar sign. Taking care to make sure data are displayed correctly gives your worksheet
credibility.
27
You can apply numeric formats after data are entered, but formatting empty cells before you type
the data can help improve accuracy. To apply a format, select a cell or range of cells and use
commands in the Number Format list on the Home tab.
The Number group also contains Increase Decimal and Decrease Decimal options for easily
changing the decimal places for a value. You can see all available number formats by launching
the Number Format dialog box.
Cell Values vs. Cell Formats
Formats applied to a cell determine how a value is displayed, but they don’t change the stored
value. For example, if you enter 123.456 into a cell and then format it to display two decimal
places, you’ll see 123.46.
If you want to know the value stored in a cell, select the cell and look at the Formula bar. This is
important because when using formulas, which you’ll learn about later in this lesson, the
calculations will use the nonrounded numbers, and might show different results than if the
rounded numbers were used.
Conditional Formatting
Highlight Cells Rules
provides simple formatting options for cells.
Effective use of color can make it possible to evaluate data quickly. When a cell displays a color
format based on a value, it has a conditional format.
To apply conditional formatting, select the cell or cell range to format and then
select Conditional Formatting on the Home tab to display a menu. Conditional formatting is
based on rules that range from simple to complex. The Highlight Cells Rules provide some
simple but effective formatting.
28
When you click one of the Highlight Cells Rules, a dialog box is displayed.
Clicking on Less
Than displays this dialog box.
You type a value, select a color format from the list, and then select OK. When the cell contains
a value less than the one you specified, the cell color appears.
Using themed cells and conditional formatting can be a great way to represent data visually. Two
of the default Themed Cell Styles are “Bad,” which had red text on a light red fill, and “Good,”
which has green text on a light green fill. These styles are commonly used in business when
displaying money values such as those from sales, purchases, expenditures, and other transfers.
Good, as the name suggests, represents a positive total—money earned for the company—while
Bad represents a negative total—money lost. Before even reading the data on a spreadsheet,
someone could tell how the company is performing simply by seeing the number of Good and
Bad cells.
Using Quick Analysis
You can also quickly add conditional formatting with the Quick Analysis button, which appears
when you select a range of data. Use the Quick Analysis button in the lower-right corner of the
range to display options.
You can point to an option to preview the results. Click any option to apply it to the selected
range. When the Quick Analysis formats aren’t what you’re looking for, use the Conditional
Formatting command on the Home tab.
Watch this video to learn how to use the conditional formatting command. Download the
transcript here.
Define a Name Range
By using a defined Name Range, formulas will be much easier to understand and maintain. This
command can define a name for a cell range, function, constant, or table. Using Names in the
Excel workbook allows for easy updates, auditing, and management of the names.
This is how you define names from a selected range.
1. Choose the range and include any row or column names.
2. On the Formulas tab, select Create from Selection from the Defined Names group.
29
3. In the Create Names from Selection dialog box, identify the labels by selecting the
appropriate check box where the data is located. The options include Top row, Left
column, Bottom row, and Right column. You can make multiple selections.
4. Select OK.
Name a Table
When a new table is created, a default name will be provided, such as Table1, Table2, Table3,
and so on. However, these names can be changed or updated.
To rename a table, do the following:
1. Select the table.
2. Go to Table Tools > Design > Properties > Table Name.
3. Highlight the table name and enter a new name
Reflect and Respond
What kind of problems could you run into if you format a cell with the wrong format for the data
type?
Key Points and Links
Key Points
•
You can use commands on the Home tab to change the font, style, alignment, wrap, and
orientation of your cell data.
•
More than one cell can be selected at a time by dragging and highlighting a range of cells.
•
Cell borders can be used to distinguish one cell from another for printouts that are easier
to read.
•
You can use Merge & Center to combine cells to create a larger cell for titles and labels.
•
Themes on the Page Layout tab are used to change several formats at once on your
worksheet.
•
If you want to add a company logo or graphic, click Pictures or Online Pictures on
the Insert tab.
•
You can size or rotate an image using its handles.
•
Numbers can be formatted appropriately using commands in the Number Format list on
the Home tab.
30
•
Conditional Formatting on the Home tab makes use of color to help you evaluate data.
•
The Quick Analysis button can also be used to apply conditional formatting.
Apply Your Knowledge: Creating a
Worksheet (Soccer League)
Complete this Apply Your Knowledge activity to learn the steps for creating a worksheet.
As secretary of the youth soccer league, you need to keep a record of the team coaches. Follow
the steps below to create a worksheet with coach information.
1. Create a new Excel workbook.
2. Type “Any Town Youth Soccer League” in cell A1.
3. Point to cell A1 and then drag from A1 to cell I1.
Point and drag to select the indicated cells.
4. Select Merge & Center in the Home tab to merge the cells and center the title.
5. Ensure cell A1 is selected.
6. Use commands on the Home tab to change the text in cell A1 to Arial, size 24, bold, and
dark orange.
7. Select Online Pictures in the Insert tab to open a dialog box.
8. Type “soccer ball” in the search box and select the magnifying glass to display search
results.
The
Inserted Soccer Ball Image
9. Select a soccer ball image.
10. Select Insert to place the graphic on the worksheet.
11. Drag the image to center it below the title.
12. Size the image appropriately.
13. Enter the following data, starting at row 10.
31
Last Name
Coach
First Name
Assistant Coach
Last Name
Assistant Coach
Tornados Reese
Jackson
Bay
Nichols
Canes
James
Greene
Max
Castillo
Riptides
Casey
Barnes
Lou
Nguyen
Sharks
Jamie
Dixon
Dale
Logan
Team
First Name
Coach
14. Select the entire row 10.
15. Bold the contents of row 10.
16. Select Wrap Text from the Home tab.
17. Widen column B so that “First Name” is on the top line and “Coach” is on the second line
in cell B10.
18. Widen columns C, D, and E so that they match those in the image titled The Formatted
Worksheet Header.
The
Formatted Worksheet Header
19. Select cells B10 through C14.
20. Select the 20%–Accent1 style from the More Styles group in the Home tab.
21. Select cells D10 through E14.
22. Select the 20%–Accent4 style from the More Styles group in the Home tab.
The Table
with Borders
23. Bold the contents of row 10 again.
32
24. Select cells A1 through I14.
25. Select All Borders from the arrow beside Borders in the Home tab.
26. Select Themes from the Page Layout tab.
27. Select the theme titled Ion.
28. Note that while your worksheet fonts and colors have changed to match the theme, the
title that you previously formatted remains in the font you selected. Only Themed Cell
Styles and default fonts are changed when a new theme is applied.
29. Adjust the columns to display the labels appropriately, if necessary.
30. Save the workbook, naming it “Any Town Soccer Coaches.”
Your finished workbook should look similar to the figure below.
The
Finished Soccer Coaches Worksheet
Apply Your Knowledge: Creating an
Inventory Worksheet (Clever Acme Corp.)
Complete this Apply Your Knowledge activity to learn the steps for creating an inventory
worksheet.
As the owner of Clever Acme Corp., you want to keep better track of inventory. Your employees
aren’t reordering early enough, so you want to have your worksheet alert you to low stock
numbers. Follow the directions below to create an inventory worksheet.
1. Create a new Excel workbook.
2. Enter the data as shown below.
Clever Acme Corp. Inventory
Item Name
Stock #
Color
Cost
Last Shipment
Cu
33
3. Format the title as Arial, size 22, bold, and purple.
4. Format the other labels as Calibri, size 14, bold.
5. Widen columns as needed.
6. Select cell B3 (“Stock #”).
7. Select Center from the Alignment group in the Home tab.
8. Select cell D3 (“Cost”).
9. Select Align Right from the Alignment group in the Home tab.
10. , the Cost label. This column will display dollar amounts, which are right-aligned in a
cell. On the Home tab, in the Alignment group, select Align Right.
11. Right-align cells E3 and F3 (“Last Shipment” and “Current Stock”).
12. Select cells D4 through D9.
13. Select Currency from the Number Format list in the Home tab.
14. Select cells E4 through E9.
15. Select Long Date from the Number Format list in the Home tab.
16. Type “Doodad” in cell A4 and select Tab.
17. Type “Green” in cell B4 and select Tab.
18. Type “.24” in cell C4 and select Tab. (Note that .24 was automatically converted to $0.24
because the cell is formatted to display values as dollar amounts.)
19. Type “6/10/20” in cell E4 and select Tab. (Note that the date is converted to the long
format.)
20. Widen column E if ###### shows in cell E4 instead of the date.
21. Type “122” in cell F4 and select Enter.
22. Ensure cell A5 is selected.
23. Type “D” in cell A5.
24. Select Tab to accept AutoComplete’s suggestion of “Doodad” for the entry.
25. Complete the data entry as shown below. (Remember that you need only type the
numerical value for the dollar amounts because the cell is already formatted. Also, you
need only enter the shortened date because the formatting will determine the day of the
week to display.)
Item
Name
Stock #
Color
Cost Last Shipped
Current
Stock
Doodad
12-456900
Green
$0.24 Friday, June 10, 2020
122
Doodad
12-456901
Orange $0.24 Friday, June 10, 2020
98
Gizmo
12-456989
Blue
12
Widget
12-477897
Yellow $1.33 Tuesday, May 24, 2020
Widget
12-477894
Brown
$0.92 Wednesday, March 2, 2020
$1.33
Wednesday, February 10,
2020
451
10
34
Widget
12-477892
White
$1.33 Tuesday, May 24, 2020
219
26. Widen any columns if necessary.
27. Select cells F4 through F9.
28. Select Home > Conditional Formatting > Highlight Cells Rules > Less Than to open
a dialog box.
29. Type “20.”
30. Select Light Red Fill from the list.
31. Select OK. (The spreadsheet will now alert you when stock is low. You can see that two
items are low on stock.)
32. Select cells F4 through F9. (Notice the Quick Analysis button that appears in the lower
right of the selected range.)
33. Select Quick Analysis.
34. Select the Format tab in the gallery, if necessary, to reveal several conditional formatting
options.
35. Select Data Bars. (Now all of the values reflect their relative stock.)
36. Select the cells A1 through F9.
37. Apply All Borders to cells A1 through F9.
38. Save the workbook, naming it “Clever Acme Corp. Inventory.”
Identify the Steps Needed to Produce Calculations Using Formulas and Functions
Formulas and Functions
Read this assignment.
The power of a spreadsheet is generating data using formulas and functions that calculate values.
Formulas become even more meaningful when the results are based on data from your
worksheet. For example, when you want to know the total of the expenses in your budget
worksheet, you can use a formula to add the expenses and display the total.
Using Formulas
A formula is a mathematical statement that calculates a value. To create a formula in your
worksheet, you must begin with an equal sign (=). For example, if you type “=25+2” in a cell, it
will display “27”.
Use the following symbols to represent operators:
•
•
•
Exponentiation: ^
Multiplication: *
Division: /
35
Addition: +
Subtraction: The exponentiation symbol raises a number to a power, as in 32. To perform this same
calculation in Excel, you use the formula =3^2 to display 9.
•
•
Formulas are evaluated using the mathematical order of operations, which states that
exponentiation is calculated first, followed by multiplication and division, and then addition and
subtraction. The following formulas demonstrate the order of operations:
Formula
Value
=3*4^2
48
=10*2^2
40
=8+6/3-1
9
To change the order of operations, you can put parentheses around calculations to be performed
first.
Formula
Value
=3*(4+2)
18
=(10*2)^2
400
=(8+6)/(3-1)
7
Display Formulas
When you enter a formula into a cell, the result of the calculation is displayed. To review or edit
the actual formula, select the cell and use the Formula bar to access the actual cell contents. This
can be inconvenient when reviewing an entire worksheet. To see all the formulas in a
spreadsheet at once, select Show Formulas on the Formulas tab, or press both Ctrl and
the backtick key (`) located next to the number 1 key on your keyboard.
When formulas are displayed, column widths adjust automatically to display the entire formula.
Selecting Show Formulas again reverts back to the original formatting.
Worksheet Formula
36
Worksheet Formula Bar
Check for Errors
Dividing by 0 causes this error to appear in the cell.
Excel checks formulas when you enter them. When a formula can’t be calculated, an error is
displayed and a green triangle is added to the upper-left corner of the cell. For example, the
formula “=4/0” displays “#DIV/0!”. This displays because numbers can’t be divided by 0. You
can select the cell with the error and use Error Checking to display a description of the error
and a list of options.
You can click Show Calculation Steps to display a dialog box that examines the formula.
If errors are displayed in your worksheet but the Error Checking menu isn’t available, you can
click Error Checking on the Formulas tab to check all the cells in your worksheet.
Using Cell References
When you want to make calculations based on the data in your spreadsheet, you can include
a cell reference in your formula rather than the actual number. For example, in the figure, cell
C1 displays a value (total of $540) based on multiplying the numbers in cells A1 (Quantity of
12) and B1 (Cost is $45).
37
Cell C2 shows a value based on cells A2 and B2.
When you use cell references in formulas, your spreadsheet becomes easier to update. If you
change a value in a cell referenced in a formula, the formula automatically recalculates.
Although you can type the cell name in a formula to create the cell reference, you can avoid
typing errors by clicking the cell instead. To do this, type a formula up to the point where the cell
name appears, and then click on the cell to be used in the calculation. You can also use the arrow
key to select the cell.
You’ll almost always want to use cell references in worksheet formulas rather than actual
numbers. It’s much easier to have the calculation results change automatically when values are
entered than to update each formula manually with the correct numbers. Whether your data are
sales figures, age ranges, horsepower, voltage, or grades, your data will likely change over time,
and formulas with cell references make sure your spreadsheet stays up to date and accurate.
You can’t create a formula in a cell that’s referenced by that formula because this will cause
a circular reference error—the formula can’t be calculated until the cell has a value, but the cell
can’t have a value until the formula is entered.
Display Formulas with Cell References
Double-clicking a cell displays the formula and
highlights the referenced cells in that formula.
When you select Show Formulas on the Formulas tab, selecting a cell with a formula outlines
the cells referenced in that formula. If you want to examine just one formula, double-click the
cell to display the formula and outline referenced cells.
Copy Formulas
Often you want to copy the formula you’ve created to the next cell in a row or column. For this,
you can use Copy and Paste, but when copying to adjacent cells, you can also use the Fill
Handle, a point in the lower-right corner of the active cell that you can drag to other cells. Select
the cell with the formula you want to copy, and then drag its Fill Handle to copy the formula to a
range of cells.
38
Cell C2 shows a value based on cells A2 and B2.
When a formula is copied, cell references automatically change relative to the new row or
column. For example, in the spreadsheet above, the formula in cell C2 is =B2*A2. When copied
to the cells below, it changes to =B3*A3 and =B4*A4, respectively. When cell references can
change in this way, they’re called relative cell references.
Relative references change when a formula is moved or copied from one cell to another. Relative
references are a powerful tool in applying the same calculation to hundreds of rows and columns
of data.
Use Absolute and Mixed Cell References
If you want a cell reference in a formula to remain the same when you copy it to another cell,
then you’ll need to make it an absolute cell reference, which is a cell reference that doesn’t
change if a formula is copied to other cells. For example, in the figure below, the formula
(=C5*$B$2) refers to a tax rate of six percent in cell B2.
The selected cell’s formula includes an absolute cell
reference, shown with dollar signs in front of the cell reference.
The cell reference has dollar signs in front of the column letter and row number to indicate that
neither should change when you copy the formula to another cell. You create an absolute
reference by pressing F4 after you type or click on the cell to be referenced.
In some cases, you may need a mixed cell reference in your formula where either the column or
row doesn’t change when copied. In this case, you can press F4 until the appropriate reference
appears.
Excel references column headings and table names instead of cell references when formulas are
inserted into a table. Cell names and range names assigned to a given cell or range make it easier
to understand what calculations are being performed in a formula and to reuse the references as
necessary.
Watch this video to learn relative and absolute cell references. Download the transcript here.
39
Using Functions in Formulas
You might be thinking about all the work needed to create something as simple as a formula that
sums the values in five cells. From what you’ve learned so far, the formula might look like
=A1+A2+A3+A4+A5. That’s a long formula for a simple calculation! But Excel is a spreadsheet
application with many features for analyzing data, so it includes built-in functions for use in
formulas.
A function is a named set of operations that take one or more values and produce a single
output. For example, the SUM function accepts a cell range and returns the sum of the values in
those cells, as in =SUM(A1:A5). With this simple formula, you can calculate the sum of the
values in cells A1 through A5.
Functions are always used as part of a formula and usually require data,
called arguments, inside parentheses after the function name. The SUM function, for example,
requires a cell range with the first and last cells separated by a colon (A1:A5) or a set of cell
names separated by commas (A1,A3,A5).
The blue outline shows the range in the formula in cell
C9.
To add a cell range to a function using a mouse, type the function up to the point where the cell
range is needed, and then click and drag from the first cell in the range to the last before typing
the closing parenthesis. To help you, Excel displays a colored outline showing the range.
If you want to edit the range using the mouse after you’ve entered the formula, click the cell with
the formula and then drag the cell outline. You could also simply delete the range and select a
new one.
Watch this video to learn how to use functions to perform calculations. Download the transcript
here.
Commonly Used Functions
The following are some of the commonly used Excel functions:
•
SUM adds the values in a range and returns the sum.
40
AVERAGE adds the values in a range and then divides the total by the number of
values.
• COUNT returns the number of cells in a range that contains values.
• COUNTA counts the number of cells that aren’t empty in a range of text or in logical or
error values.
• COUNTBLANK is a statistical function on the Formulas ribbon of the Functional
Library group that will count the number of empty cells in a range of cells.
• MAX returns the maximum value in a range of cells.
• MIN returns the minimum value in a range of cells.
In many cases, you’ll need to create the formula with one of these functions to make the
calculation you need. However, the Totals tab in the Quick Analysis gallery is another option
for creating totals.
•
The Totals tab
gives you a quick way to create totals. Pointing to a total allows you to preview the results.
Watch this video to learn how to use functions to count cells. Download the transcript here.
The IF Function
Another commonly used function is IF, which displays a value based on a comparison. For
example, if you want to display STUDY when the grade average in cell G7 is below 85 or
GREAT otherwise, use =IF(G7 Conditional Formatting > Data Bars > Gradient Fill Blue Data Bar to
apply conditional formatting so you understand your grades at a glance.
20. Save the modified workbook.
44
The Finished Semester Grades Worksheet
Apply Your Knowledge: Creating a Payroll
Workbook with Formulas (Clever Acme
Corp.)
Complete this Apply Your Knowledge activity to learn the steps for creating a payroll workbook
with formulas.
As the owner of Clever Acme Corp., you need to keep track of payroll. You’ll use the payroll
spreadsheet to perform the following tasks:
Ensure the overtime pay is 50 percent higher than the hourly pay rate
Discover how many hours employees are working at their standard pay rate for 40 hours
a week or less (known as straight time hours)
• Calculate overtime hours by subtracting straight time hours from the total number of
hours worked
• Determine employees’ straight pay, which is the product of their hourly rate and their
straight time hours
• Calculate employees’ overtime pay, which is the product of their hourly overtime rate and
their overtime hours
• Calculate the employees’ gross pay (the sum of their straight pay and overtime pay)
• Determine employee bonuses based on their week’s pay
• Analyze the payroll data by determining the maximum, minimum, and average values for
all the columns
Follow these steps to modify the Clever Acme Corp Payroll workbook:
•
•
1. Start Excel.
2. Open Clever Acme Corp Payroll.xlsx, one of the files you downloaded at the beginning
of this section.
3. Note the cell titles with values that need to be calculated.
4. Type “=” in cell C4.
45
5. Type “*150%” in B4 and select Enter.
6. Select cell C4.
The Worksheet with Inserted
Formula
7. Drag the Fill Handle in the lower right of cell C4 to cell C14 to copy the formula for all
employees. (Alternately, select cell C4 and press Ctrl+C. Then, select the range C5
through C14, and press Ctrl+V.)
8. Select cell C10. (Note that the cell reference in the formula bar has changed relative to
the row.)
9. Type the formula “=IF(D4
