CHAPTER 3

(Spreadsheets) Microsoft Excel

(COMPUTER LITERACY)
CHAPTER 1: Introduction To Computers

Worksheet Organization
The Excel "Big Grid" increases the maximum number of rows per
worksheet from 65,536 to over 1 million, and the number of columns
from 256 to 16,384. When you open a new workbook there 1
worksheet by default.

1.1. Using Workbook Templates

A template is a predesigned spreadsheet you can use to create a
new workbook quickly. Templates often include custom formatting
and predefined formulas, so they can save you a lot of time and
effort when starting a new project.

To create a new workbook from a template

1. Click the File tab to access Backstage view

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

3. Select a template to review it.

 Worksheet Organization
4. A preview of the template will appear, along with
additional information on how the template can be used.

5. Click Create to use the selected template.

 Worksheet Organization
6. A new workbook will appear with the selected
template.

TIP: You can also browse templates by category or

use the search bar to find something more specific.
 Worksheet Organization
1.2. View Options

If your workbook contains a lot of content, it can

sometimes be difficult to compare different sections. Excel
includes additional options to make your workbooks easier
to view and compare. For example, you can choose to
open a new window for your workbook or split a worksheet
into separate panes.

To open a new window for the current workbook

 Worksheet Organization

Excel allows you to open multiple windows for a single

workbook at the same time. In our example, we'll use
this feature to compare two different worksheets from
the same workbook.

1. Click the View tab on the Ribbon, then select the

New Window command.
2. A new window for the workbook will appear.

3. You can now compare different worksheets from the

same workbook across windows.

TIP: If you have several windows open at the same

time, you can use the Arrange All command to
rearrange them quickly.
Worksheet Organization
 Worksheet Organization

To split a worksheet

Sometimes you may want to compare different sections

of the same workbook without creating a new window.
The Split command allows you to divide the worksheet
into multiple panes that scroll separately.
1. Select the cell where you wish to split the worksheet.
2. Click the View tab on the Ribbon, then select the
Split command.
 Worksheet Organization

3. The workbook will be split into different panes. You

can scroll through each pane separately using the scroll
bars, allowing you to compare different sections of the
workbook.

✓ TIP: After creating a split, you can click and drag the
vertical and horizontal dividers to change the size of
each section.
FINALIZING AND PROTECTING WORKBOOKS

• Before sharing a workbook, you'll want to make sure it

doesn't include any spelling errors or information you
wish to keep private. Fortunately, Excel includes
several tools to help finalize and protect your
workbook, such as Spell Check and the Document
Inspector.

1.3.1. Spell Check

1. From the Review tab, click the Spelling command.

FINALIZING AND PROTECTING WORKBOOKS
FINALIZING AND PROTECTING WORKBOOKS

2. The Spelling dialog box will appear. For each spelling

error in your worksheet, Spell Check will try to offer
suggestions for the correct spelling. Choose a
suggestion, then click Change to correct the error.

✓TIP: If there are no appropriate suggestions, you can

also enter the correct spelling manually
FINALIZING AND PROTECTING WORKBOOKS
FINALIZING AND PROTECTING WORKBOOKS

3. A dialog box will appear after reviewing all spelling

errors. Click OK to close Spell Check.
FINALIZING AND PROTECTING WORKBOOKS
Ignoring spelling "errors"

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

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

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

✓ Add: This adds the word to the dictionary so it will never appear as an
error again. Make sure the word is spelled correctly before choosing this
option.
FINALIZING AND PROTECTING WORKBOOKS
1.3.2. Document Inspector Whenever you create or edit a workbook,
certain personal information may be added to the file automatically. You
can use the Document Inspector to remove this kind of information before
sharing a workbook with others.

Because some changes may be permanent, it's a good idea to save an

additional copy of your workbook before using the Document Inspector to
remove information.

1. Click the File tab to access Backstage view.

2. From the Info pane, click Check for Issues, then select Inspect
Document from the drop-down menu.
FINALIZING AND PROTECTING WORKBOOKS
 FINALIZING AND PROTECTING WORKBOOKS

3. The Document Inspector will appear. Check or

uncheck boxes, depending on the content you wish to
review, then click Inspect.
 DATA ANALYSIS

If your worksheet contains a lot of content, it can be

difficult to find information quickly. Filters can be used to
narrow down the data in your worksheet, allowing you
to view only the information you need.
 DATA ANALYSIS
AutoFilter and Advanced Filter

Filtering rows allows you to include or exclude rows

based upon a value. When a column is filtered a small
filter icon appears in the column header.

AutoFilter

The AutoFilter feature puts drop-down arrows (with

menus) in the titles of each column. The menus are
used to select criteria in the column so that only records
that meet the specified criteria are displayed.
 DATA ANALYSIS

1. In order for filtering to work correctly, your worksheet

should include a header row, which is used to
identify the name of each column.

See next slide…

DATA ANALYSIS
 DATA ANALYSIS

2. Select the Data tab, then click the Filter command.

 DATA ANALYSIS
3. A drop-down arrow will appear in the header cell for
each column.

4. Click the drop-down arrow for the column you wish to

filter.
 DATA ANALYSIS

• 5. The Filter menu will appear.

• 6. Uncheck the box next to Select All to quickly

deselect all data.

• 7. Check the boxes next to the data you wish to filter,

then click OK.

See next slide …..

DATA ANALYSIS
 DATA ANALYSIS

8. The data will be filtered, temporarily hiding any

content that doesn't match the criteria.
 DATA ANALYSIS

Advanced Filter

If you need to filter for something specific, basic filtering

may not give you enough options. Fortunately, Excel
includes many advanced filtering tools, including
search, text, date, and number filtering, which can
narrow your results to help find exactly what you need.
 DATA ANALYSIS
Advanced Filter
To filter with search
Excel allows you to search for data that contains an exact phrase, number,
date, and more.

1. Select the Data tab, then click the Filter command. A drop-down
arrow will appear in the header cell for each column. Note: If you've
already added filters to your worksheet, you can skip this step.
2. Click the drop-down arrow for the column you wish to filter.
3. The Filter menu will appear. Enter a search term into the search box.
Search results will appear automatically below the Text Filters field as
you type.
4. When you're done, click OK.
5. The worksheet will be filtered according to your search term.
 DATA ANALYSIS
Advanced Filter
To use advanced text filters

Advanced text filters can be used to display more specific

information, such as cells that contain a certain number of
characters, or data that excludes a specific word or number.

❑ And Criteria

To find data that meets one condition in two or more

columns, enter all the criteria in the same row of the criteria
range.
 DATA ANALYSIS
1. Enter the criteria shown below on the worksheet.
 DATA ANALYSIS
Advanced Filter
To use advanced text filters

2. Click any single cell inside the data set.

3. On the Data tab, in the Sort & Filter group, click

Advanced.
 DATA ANALYSIS
4. Click in the Criteria range box and select the range
C1:D2.

5. Click OK.
 DATA ANALYSIS
❑ Or Criteria

To find data that meets either a condition in one column

or a condition in another column, enter the criteria in
different rows of the criteria range.

1. Enter the criteria shown below on the worksheet.

2. On the Data tab, click Advanced, and adjust the
Criteria range to range C1:D3.
3. Click OK.
DATA ANALYSIS
 DATA ANALYSIS

❑ Formula as Criteria

1. Enter the criteria (+formula) shown below on the

worksheet.
2. On the Data tab, click Advanced, and adjust the
Criteria range to range B1:D3.
3. Click OK.
DATA ANALYSIS
 DATA ANALYSIS
To turn off the Advanced Filter click on Clear from the
Data tab in the Sort & Filter Group.

Creating and Using Outlines

Worksheets with a lot of content can sometimes feel

overwhelming and even become difficult to read.
Fortunately, Excel can organize data in groups,
allowing you to easily show and hide different sections
of your worksheet. You can also summarize different
groups using the Subtotal command and create an
outline for your worksheet.
 DATA ANALYSIS
Grouping Rows or Columns

1. Select the rows or columns you wish to group. In

this example, we'll select columns A, B, and C.
2. Select the Data tab on the Ribbon, then click the
Group command.
DATA ANALYSIS
 DATA ANALYSIS
3. The selected rows or columns will be grouped.
 DATA ANALYSIS
To ungroup data, select the grouped rows or columns,
then click the Ungroup command.
 DATA ANALYSIS
To hide and show groups
1. To hide a group, click the Hide Detail button.

2. The group will be hidden. To show a hidden group,

click the Show Detail button.
 DATA ANALYSIS
Creating subtotals

The Subtotal command allows you to automatically

create groups and use common functions like SUM,
COUNT, and AVERAGE to help summarize your data.
For example, the Subtotal command could help to
calculate the cost of office supplies by type from a large
inventory order. The Subtotal command will create a
hierarchy of groups, known as an outline, to help
organize your worksheet.
 DATA ANALYSIS
To create a subtotal

In our example, we will use the Subtotal command with a T-

shirt order form to determine how many T-shirts were
ordered in each size (Small, Medium, Large, and X-Large).
This will create an outline for our worksheet with a group for
each T-shirt size and then count the total number of shirts in
each group.

1. First, sort your worksheet by the data you wish to subtotal.

In this example, we will create a subtotal for each T-shirt
size, so our worksheet has been sorted by T-shirt size from
smallest to largest.
 DATA ANALYSIS
2. Select the Data tab, then click the Subtotal
command.
 DATA ANALYSIS

3. The Subtotal dialog box will appear. Click the drop-

down arrow for the At each change in: field to select
the column you wish to subtotal. In our example, we'll
select T-Shirt Size.
4. Click the drop-down arrow for the Use function: field
to select the function you wish to use. In our example,
we'll select COUNT to count the number of shirts
ordered in each size.
5. In the Add subtotal to: field, select the column
where you want the calculated subtotal to appear. In
our example, we'll select T-Shirt Size.
DATA ANALYSIS
 DATA ANALYSIS

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

7. The worksheet will be outlined into groups, and the

subtotal will be listed below each group. In our example,
the data is now grouped by T-shirt size, and the number
of shirts ordered in that size appears below each group.
DATA ANALYSIS
 DATA ANALYSIS
To view groups by level
When you create subtotals, your worksheet it is divided into
different levels. You can switch between these levels to quickly
control how much information is displayed in the worksheet by
clicking the Level buttons 1 2 3 to the left of the worksheet. In our
example, we'll switch between all three levels in our outline. While
this example contains only three levels, Excel can accommodate
up to eight.
1. Click the lowest level to display the least detail (level 1).

2. Click the next level to expand the detail. In our example, we'll
select level 2, which contains each subtotal row but hides all
other data from the worksheet.
DATA ANALYSIS
 DATA ANALYSIS
3. Click the highest level to view and expand all of your
worksheet data (level 2).

TIP: You can also use the Show and Hide Detail
buttons to show and hide the groups within the outline.
 DATA ANALYSIS
To remove subtotals
Sometimes you may not want to keep subtotals in your
worksheet, especially if you want to reorganize data in
different ways. If you no longer wish to use subtotaling,
you'll need remove it from your worksheet.
1. Select the Data tab, then click the Subtotal
command.
2. The Subtotal dialog box will appear. Click Remove
All.
DATA ANALYSIS
 DATA ANALYSIS
3. All worksheet data will be ungrouped, and the
subtotals will be removed.

TIP: To remove all groups without deleting the

subtotals, click the Ungroup command drop-down
arrow, then choose Clear Outline.
 DATA ANALYSIS
Conditional Formatting

Let's imagine you have a worksheet with thousands of

rows of data. It would be extremely difficult to see
patterns and trends just from examining the raw
information. Similar to charts and sparklines,
conditional formatting provides another way to
visualize data and make worksheets easier to
understand.
 DATA ANALYSIS
Understanding conditional formatting

Conditional formatting allows you to automatically apply

formatting—such as colors, icons, and data bars—to one or more
cells based on the cell value. To do this, you'll need to create a
conditional formatting rule. For example, a conditional formatting
rule might be: "If the value is less than $2000, color the cell
red." By applying this rule, you'd be able to quickly see which cells
contain values under $2000.

Note: When you create a conditional format, you can only

reference other cells on the same worksheet; you cannot reference
cells on other worksheets in the same workbook, or use external
references to another workbook.
 DATA ANALYSIS
To create a conditional formatting rule
In our example, we have a worksheet containing sales
data, and we'd like to see which salespeople are
meeting their monthly sales goals. The sales goal is
$4000 per month, so we'll create a conditional
formatting rule for any cells containing a value higher
than 4000.
1. Select the desired cells for the conditional
formatting rule.
2. From the Home tab, click the Conditional
Formatting command. A drop-down menu will
appear.
DATA ANALYSIS
 DATA ANALYSIS
3. Hover the mouse over the desired conditional formatting type,
then select the desired rule from the menu that appears. In our
example, we want to highlight cells that are greater than $4000.
 DATA ANALYSIS
4. A dialog box will appear. Enter the desired value(s) into
the blank field. In our example, we'll enter 4000 as our value.
5. Select a formatting style from the drop-down menu. In
our example, we'll choose Green Fill with Dark Green Text,
then click OK.
6. The conditional formatting will be applied to the selected
cells. In our example, it's easy to see which salespeople
reached the $4000 sales goal for each month.
TIP: You can apply multiple conditional formatting rules to
a cell range or worksheet, allowing you to visualize different
trends and patterns in your data.
 DATA ANALYSIS

To remove conditional formatting

1. Click the Conditional Formatting command. A

drop-down menu will appear.
2. Hover the mouse over Clear Rules, and choose
which rules you wish to clear. In our example, we'll
select Clear Rules from Entire Sheet to remove all
conditional formatting from the worksheet.
DATA ANALYSIS
 DATA ANALYSIS

3. The conditional formatting will be removed.

TIP: Click Manage Rules to edit or delete individual

rules. This is especially useful if you have applied
multiple rules to a worksheet.
DATA ANALYSIS