Excel

Filtering Data

Introduction
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.

Optional: Download our practice workbook.

Watch the video below to learn more about filtering data in Excel.

Excel: Filtering Data

To filter data:
In our example, we'll apply a filter to an equipment log worksheet to display only the
laptops and projectors that are available for checkout.

1 In order for filtering to work correctly, your worksheet should include a

header row, which is used to identify the name of each column. In our
example, our worksheet is organized into different columns identified by
the header cells in row 1: ID#, Type, Equipment Detail, and so on.
2 Select the Data tab, then click the Filter command.

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

4 Click the drop-down arrow for the column you want to filter. In our
example, we will filter column B to view only certain types of equipment.

5 The Filter menu will appear.

6 Uncheck the box next to Select All to quickly deselect all data.
7 Check the boxes next to the data you want to filter, then click OK. In this
example, we will check Laptop and Projector to view only these types of
equipment.

8 The data will be filtered, temporarily hiding any content that doesn't match
the criteria. In our example, only laptops and projectors are visible.
 Filtering options can also be accessed from the Sort & Filter command on
the Home tab.

To apply multiple filters:

Filters are cumulative, which means you can apply multiple filters to help narrow
down your results. In this example, we've already filtered our worksheet to show laptops
and projectors, and we'd like to narrow it down further to only show laptops and
projectors that were checked out in August.

1 Click the drop-down arrow for the column you want to filter. In this
example, we will add a filter to column D to view information by date.

2 The Filter menu will appear.

 3 Check or uncheck the boxes depending on the data you want to filter, then
click OK. In our example, we'll uncheck everything except for August.

4 The new filter will be applied. In our example, the worksheet is now filtered
to show only laptops and projectors that were checked out in August.

To clear a filter:
After applying a filter, you may want to remove—or clear—it from your worksheet so
you'll be able to filter content in different ways.

1 Click the drop-down arrow for the filter you want to clear. In our example,
we'll clear the filter in column D.

2 The Filter menu will appear.

3 Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our
example, we'll select Clear Filter From "Checked Out".

4 The filter will be cleared from the column. The previously hidden data will
be displayed.

To remove all filters from your worksheet, click the Filter command on the
Data tab.

Advanced filtering
If you need a filter for something specific, basic filtering may not give you enough
options. Fortunately, Excel includes several advanced filtering tools, including search,
text, date, and number filtering, which can narrow your results to help find exactly
what you need.

To filter with search:

Excel allows you to search for data that contains an exact phrase, number, date, and
more. In our example, we'll use this feature to show only Saris brand products in our
equipment log.

1 Select the Data tab, then click the Filter command. A drop-down arrow
will appear in the header cell for each column. Note: If you've already
added filters to your worksheet, you can skip this step.

2 Click the drop-down arrow for the column you want to filter. In our
example, we'll filter column C.

3 The Filter menu will appear. Enter a search term into the search box.
Search results will appear automatically below the Text Filters field as you
type. In our example, we'll type saris to find all Saris brand equipment.
When you're done, click OK.
 4 The worksheet will be filtered according to your search term. In our
example, the worksheet is now filtered to show only Saris brand
equipment.

To use advanced text filters:

Advanced text filters can be used to display more specific information, like cells that
contain a certain number of characters or data that excludes a specific word or number.
In our example, we'd like to exclude any item containing the word laptop.

1 Select the Data tab, then click the Filter command. A drop-down arrow
will appear in the header cell for each column. Note: If you've already
added filters to your worksheet, you can skip this step.

2 Click the drop-down arrow for the column you want to filter. In our
example, we'll filter column C.

3 The Filter menu will appear. Hover the mouse over Text Filters, then select
the desired text filter from the drop-down menu. In our example, we'll
choose Does Not Contain... to view data that does not contain specific text.
 4 The Custom AutoFilter dialog box will appear. Enter the desired text to the
right of the filter, then click OK. In our example, we'll type laptop to exclude
any items containing this word.

5 The data will be filtered by the selected text filter. In our example, our
worksheet now displays items that do not contain the word laptop.

To use advanced number filters:

Advanced number filters allow you to manipulate numbered data in different ways. In
this example, we'll display only certain types of equipment based on the range of ID
numbers.

1 Select the Data tab on the Ribbon, then click the Filter command. A drop-
down arrow will appear in the header cell for each column. Note: If you've
already added filters to your worksheet, you can skip this step.

2 Click the drop-down arrow for the column you want to filter. In our
example, we'll filter column A to view only a certain range of ID numbers.

3 The Filter menu will appear. Hover the mouse over Number Filters, then
select the desired number filter from the drop-down menu. In our example,
we'll choose Between to view ID numbers between a specific number
range.

4 The Custom AutoFilter dialog box will appear. Enter the desired number(s)
to the right of each filter, then click OK. In our example, we want to filter for
ID numbers greater than or equal to 3000 but less than or equal to 6000,
which will display ID numbers in the 3000-6000 range.
 5 The data will be filtered by the selected number filter. In our example, only
items with an ID number between 3000 and 6000 are visible.

To use advanced date filters:

Advanced date filters can be used to view information from a certain time period, such
as last year, next quarter, or between two dates. In this example, we'll use advanced date
filters to view only equipment that has been checked out between July 15 and August
15.

1 Select the Data tab, then click the Filter command. A drop-down arrow
will appear in the header cell for each column. Note: If you've already
added filters to your worksheet, you can skip this step.

2 Click the drop-down arrow for the column you want to filter. In our
example, we'll filter column D to view only a certain range of dates.

3 The Filter menu will appear. Hover the mouse over Date Filters, then
select the desired date filter from the drop-down menu. In our example,
we'll select Between... to view equipment that has been checked out
between July 15 and August 15.
4 The Custom AutoFilter dialog box will appear. Enter the desired date(s) to
the right of each filter, then click OK. In our example, we want to filter for
dates after or equal to July 15, 2015, and before or equal to August 15,
2015, which will display a range between these dates.

5 The worksheet will be filtered by the selected date filter. In our example, we
can now see which items have been checked out between July 15 and
August 15.

Challenge!
 1 Open our practice workbook.

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

3 Apply a filter to show only Electronics and Instruments.

4 Use the Search feature to filter item descriptions that contain the word
Sansei. After you do this, you should have six entries showing.

5 Clear the Item Description filter.

6 Using a number filter, show loan amounts greater than or equal to $100.

7 Filter to show only items that have deadlines in 2016.

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

Lesson 21: Groups and Subtotals

Introduction
Worksheets with a lot of content can sometimes feel overwhelming and can even
become difficult to read. Fortunately, Excel can organize data into 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.

Optional: Download our practice workbook.

Watch the video below to learn more about groups and subtotals in Excel.
Excel: Groups and Subtotals

To group rows or columns:

1 Select the rows or columns you want to group. In this example, we'll select
columns B, C, and D.

2 Select the Data tab on the Ribbon, then click the Group command.

3 The selected rows or columns will be grouped. In our example, columns B,

C, and D are grouped.
 To ungroup data, select the grouped rows or columns, then click the
Ungroup command.

To hide and show groups:

1 To hide a group, click the minus sign, also known as the Hide Detail button.

2 The group will be hidden. To show a hidden group, click the plus sign, also
known as the Show Detail button.
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. It will create a hierarchy of groups, known as an outline, to help
organize your worksheet.

Your data must be correctly sorted before using the Subtotal command, so
you may want to review our lesson on Sorting Data to learn more.

To create a subtotal:
In our example, we'll use the Subtotal command with a T-shirt order form to determine
how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This
will create an outline for our worksheet with a group for each T-shirt size and then
count the total number of shirts in each group.

1 First, sort your worksheet by the data you want to subtotal. In this example,
we'll create a subtotal for each T-shirt size, so our worksheet has been
sorted by T-shirt size from smallest to largest.

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

3 The Subtotal dialog box will appear. Click the drop-down arrow for the At
each change in: field to select the column you want to subtotal. In our
example, we'll select T-Shirt Size.

4 Click the drop-down arrow for the Use function: field to select the function
you want to use. In our example, we'll select COUNT to count the number of
shirts ordered in each size.
 5 In the Add subtotal to: field, select the column where you want the
calculated subtotal to appear. In our example, we'll select T-Shirt Size.
When you're satisfied with your selections, click OK.

6 The worksheet will be outlined into groups, and the subtotal will be listed
below each group. In our example, the data is now grouped by T-shirt size,
and the number of shirts ordered in that size appears below each group.

To view groups by level:

When you create subtotals, your worksheet it is divided into different levels. You can
switch among these levels to quickly control how much information is displayed in the
worksheet by clicking the Level buttons to the left of the worksheet. In our example,
we'll switch among all three levels in our outline. While this example contains only three
levels, Excel can accommodate up to eight.

1 Click the lowest level to display the least detail. In our example, we'll select
level 1, which contains only the Grand Count, or total number of T-shirts
ordered.

2 Click the next level to expand the detail. In our example, we'll select level
2, which contains each subtotal row but hides all other data from the
worksheet.

3 Click the highest level to view and expand all of your worksheet data. In
our example, we'll select level 3.

You can also use the Show Detail and Hide Detail buttons to show and hide
the groups within the outline.
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 want to use subtotaling, you'll
need remove it from your worksheet.

1 Select the Data tab, then click the Subtotal command.

2 The Subtotal dialog box will appear. Click Remove All.

3 All worksheet data will be ungrouped, and the subtotals will be removed.

To remove all groups without deleting the subtotals, click the Ungroup
command drop-down arrow, then choose Clear Outline.

Challenge!
1 Open our practice workbook.

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

3 Sort the workbook by Grade from smallest to largest.

 4 Use the Subtotal command to group at each change in Grade. Use the SUM
function and add subtotals to Amount Raised.

5 Select level 2 so you only see the subtotals and grand total.

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

Lesson 22: Tables

Introduction
Once you've entered information into your worksheet, you may want to format your
data as a table. Just like regular formatting, tables can improve the look and feel of
your workbook, and they'll also help organize your content and make your data easier
to use. Excel includes several tools and predefined table styles, allowing you to create
tables quickly and easily.

Optional: Download our practice workbook.

Watch the video below to learn more about working with tables.
Excel: Tables

To format data as a table:

1 Select the cells you want to format as a table. In our example, we'll select
the cell range A2:D9.

2 From the Home tab, click the Format as Table command in the Styles
group.

3 Select a table style from the drop-down menu.

4 A dialog box will appear, confirming the selected cell range for the table.

5 If your table has headers, check the box next to My table has headers, then
click OK.

6 The cell range will be formatted in the selected table style.

 Tables include filtering by default. You can filter your data at any time using
the drop-down arrows in the header cells. To learn more, review our lesson
on Filtering Data.

Modifying tables
It's easy to modify the look and feel of any table after adding it to a worksheet. Excel
includes several options for customizing tables, including adding rows or columns and
changing the table style.

To add rows or columns to a table:

If you need to fit more content into your table, you can modify the table size by
including additional rows and columns. There are two simple ways to change the table
size:

▶︎ Enter new content into any adjacent row or column. The row or column
will be roped into the table automatically.

▶︎ Click and drag the bottom-right corner of the table to create additional
rows or columns.
To change the table style:
1 Select any cell in your table, then click the Design tab.

2 Locate the Table Styles group, then click the More drop-down arrow to see
all available table styles.

3 Select the desired table style.

 4 The table style will be applied.

To modify table style options:

You can turn various options on or off to change the appearance of any table. There are
several options: Header Row, Total Row, Banded Rows, First Column, Last Column,
Banded Columns, and Filter Button.

1 Select any cell in your table, then click the Design tab.

2 Check or uncheck the desired options in the Table Style Options group. In
our example, we'll check Total Row to automatically include a total for our
table.
 3 The table style will be modified. In our example, a new row has been added
to the table with a formula that automatically calculates the total value of
the cells in column D.

Depending on the type of content you have—and the table style you've
chosen—these options can affect your table's appearance in various ways.
You may need to experiment with a few options to find the exact style you
want.

To remove a table:
It's possible to remove a table from your workbook without losing any of your data.
However, this can cause issues with certain types of formatting, including colors, fonts,
and banded rows. Before using this option, be prepared to reformat your cells if
necessary.

1 Select any cell in your table, then click the Design tab.

2 Click the Convert to Range command in the Tools group.

3 A dialog box will appear. Click Yes.

4 The range will no longer be a table, but the cells will retain their data and
formatting.

To restart your formatting from scratch, click the Clear command on the
Home tab. Next, choose Clear Formats from the menu.

Challenge!
1 Open our practice workbook.
 2 Click the Challenge tab in the bottom-left of the workbook.

3 Select cells A2:D9 and format as a table. Choose one of the light styles.

4 Insert a row between rows 4 and 5. In the row you just created, type
Empanadas: Banana and Nutella, with a unit price of $3.25 and a quantity
of 12.

5 Change the table style to Table Style Medium 10.

6 In Table Style Options, uncheck banded rows and check banded

columns.

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

Lesson 23: Charts

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

Optional: Download our practice workbook.

Watch the video below to learn more about charts.

Excel: Charts

Understanding charts
Excel has several types of charts, allowing you to choose the one that best fits your
data. To use charts effectively, you'll need to understand how different charts are used.

Click the arrows in the slideshow below to learn more about the types of
charts in Excel.

arrow_back_ios arrow_back_ios

Excel has a variety of chart types, each with its own advantages. Click
the arrows to see some of the different types of charts available in Excel.

In addition to chart types, you'll need to understand how to read a chart. Charts contain
several elements, or parts, that can help you interpret data.
 Click the buttons in the interactive below to learn about the different parts
of a chart.

To insert a chart:
1 Select the cells you want to chart, including the column titles and row
labels. These cells will be the source data for the chart. In our example,
we'll select cells A1:F6.

2 From the Insert tab, click the desired Chart command. In our example,
we'll select Column.

3 Choose the desired chart type from the drop-down menu.

 4 The Selected chart will be inserted into the worksheet.

If you're not sure which type of chart to use, the Recommended Charts
command will suggest several charts based on the source data.

Chart and layout style

After inserting a chart, there are several things you may want to change about the way
your data is displayed. It's easy to edit a chart's layout and style from the Design tab.

▶︎ Excel allows you to add chart elements—including chart titles, legends,

and data labels—to make your chart easier to read. To add a chart element,
click the Add Chart Element command on the Design tab, then choose the
desired element from the drop-down menu.
▶︎ To edit a chart element, like a chart title, simply double-click the
placeholder and begin typing.

▶︎ If you don't want to add chart elements individually, you can use one of
Excel's predefined layouts. Simply click the Quick Layout command, then
choose the desired layout from the drop-down menu.

▶︎ Excel also includes several chart styles, which allow you to quickly modify
the look and feel of your chart. To change the chart style, select the desired
 style from the Chart styles group. You can also click the drop-down arrow
on the right to see more styles.

You can also use the chart formatting shortcut buttons to quickly add chart
elements, change the chart style, and filter chart data.

Other chart options

There are many other ways to customize and organize your charts. For example, Excel
allows you to rearrange a chart's data, change the chart type, and even move the chart
to a different location in a workbook.

To switch row and column data:

Sometimes you may want to change the way charts group your data. For example, in the
chart below Book Sales data is grouped by genre, with columns for each month.
However, we could switch the rows and columns so the chart will group the data by
month, with columns for each genre. In both cases, the chart contains the same data—
it's just organized differently.
 1 Select the chart you want to modify.

2 From the Design tab, select the Switch Row/Column command.

3 The rows and columns will be switched. In our example, the data is now
grouped by month, with columns for each genre.

To change the chart type:

If you find that your data isn't working well in a certain chart, it's easy to switch to a new
chart type. In our example, we'll change our chart from a column chart to a line chart.

1 From the Design tab, click the Change Chart Type command.
 2 The Change Chart Type dialog box will appear. Select a new chart type and
layout, then click OK. In our example, we'll choose a Line chart.

3 The selected chart type will appear. In our example, the line chart makes it
easier to see trends in sales data over time.

To move a chart:
Whenever you insert a new chart, it will appear as an object on the same worksheet that
contains its source data. You can easily move the chart to a new worksheet to help keep
your data organized.

1 Select the chart you want to move.

2 Click the Design tab, then select the Move Chart command.

3 The Move Chart dialog box will appear. Select the desired location for the
chart. In our example, we'll choose to move it to a New sheet, which will
create a new worksheet.

4 Click OK.

5 The chart will appear in the selected location. In our example, the chart
now appears on a new worksheet.

Keeping charts up to date

By default, when you add more data to your spreadsheet, the chart may not include the
new data. To fix this, you can adjust the data range. Simply click the chart, and it will
highlight the data range in your spreadsheet. You can then click and drag the handle in
the lower-right corner to change the data range.
 If you frequently add more data to your spreadsheet, it may become tedious to update
the data range. Luckily, there is an easier way. Simply format your source data as a table,
then create a chart based on that table. When you add more data below the table, it
will automatically be included in both the table and the chart, keeping everything
consistent and up to date.

Watch the video below to learn how to use tables to keep charts up to date.

Excel Quick Tip: How to Make Charts Auto Update

Challenge!
1 Open our practice workbook.

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

3 Select cells A1:E6 and insert a 2D Clustered Column chart.

4 Change the chart title to September to December Sales.

 5 Use the Switch Row/Column command. The columns should now be
grouped by month, with a different color for each salesperson.

6 Move the chart to a new sheet.

7 Change the chart type to line with markers.

8 Use the Quick Layout command to change the layout of the chart.

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

Lesson 24: Conditional Formatting

Introduction
Let's say you have a worksheet with thousands of rows of data. It would be extremely
difficult to see patterns and trends just from examining the raw information. Similar to
charts and sparklines, conditional formatting provides a way to visualize data and
make worksheets easier to understand.

Optional: Download our practice workbook.

Watch the video below to learn more about conditional formatting in Excel.
Excel: Conditional Formatting

Understanding conditional formatting

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

To create a conditional formatting rule:

In our example, we have a worksheet containing sales data, and we'd like to see which
salespeople are meeting their monthly sales goals. The sales goal is $4000 per month, so
we'll create a conditional formatting rule for any cells containing a value higher than
4000.

1 Select the desired cells for the conditional formatting rule.

2 From the Home tab, click the Conditional Formatting command. A drop-
down menu will appear.

3 Hover the mouse over the desired conditional formatting type, then select
the desired rule from the menu that appears. In our example, we want to
highlight cells that are greater than $4000.

4 A dialog box will appear. Enter the desired value(s) into the blank field. In
our example, we'll enter 4000 as our value.

5 Select a formatting style from the drop-down menu. In our example, we'll
choose Green Fill with Dark Green Text, then click OK.

6 The conditional formatting will be applied to the selected cells. In our

example, it's easy to see which salespeople reached the $4000 sales goal for
each month.
 You can apply multiple conditional formatting rules to a cell range or
worksheet, allowing you to visualize different trends and patterns in your
data.

Conditional formatting presets

Excel has several predefined styles—or presets—you can use to quickly apply
conditional formatting to your data. They are grouped into three categories:

▶︎ Data Bars are horizontal bars added to each cell, much like a bar graph.

▶︎ Color Scales change the color of each cell based on its value. Each color
scale uses a two- or three-color gradient. For example, in the Green-
 Yellow-Red color scale, the highest values are green, the average values
are yellow, and the lowest values are red.

▶︎ Icon Sets add a specific icon to each cell based on its value.

To use preset conditional formatting:

1 Select the desired cells for the conditional formatting rule.

2 Click the Conditional Formatting command. A drop-down menu will

appear.

3 Hover the mouse over the desired preset, then choose a preset style from
the menu that appears.
 4 The conditional formatting will be applied to the selected cells.

Removing conditional formatting

To remove conditional formatting:
1 Click the Conditional Formatting command. A drop-down menu will
appear.
2 Hover the mouse over Clear Rules, then choose which rules you want to
clear. In our example, we'll select Clear Rules from Entire Sheet to remove
all conditional formatting from the worksheet.

3 The conditional formatting will be removed.

Click Manage Rules to edit or delete individual rules. This is especially

useful if you've applied multiple rules to a worksheet.
Challenge!
1 Open our practice workbook.

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

3 Select cells B3:J17.

4 Let's say you're the teacher and want to easily see all of the grades that are
below passing. Apply Conditional Formatting so it Highlights Cells
containing values Less Than 70 with a light red fill.

5 Now you want to see how the grades compare to each other. Under the
Conditional Formatting tab, select the Icon Set called 3 Symbols
(Circled). Hint: The names of the icon sets will appear when you hover over
them.

6 Your spreadsheet should look like this:

7 Using the Manage Rules feature, remove the light red fill, but keep the icon
set.

Lesson 25: Comments and Co-authoring

Introduction
There may be times when you're working on a workbook and find that you need the
help of others. Excel offers two powerful features that allow you to work with others on
the same spreadsheet: comments and co-authoring.

Optional: Download our practice workbook.

Watch the video below to learn more about comments and co-authoring.
Excel: Comments and Co-authoring

The Track Changes feature can also be helpful to review changes before
making them permanent. It's still available in Office 365, but it's now
hidden by default. You can learn more about Track Changes in our Excel
2016 lesson here.

Sharing a workbook with others

In order for others to collaborate on a workbook, you'll need to share it with them first.

1 Click the Share button in the top-right corner.

2 Click the OneDrive option associated with your account in order to upload
the workbook.
3 The Share pane will appear on the right side of the screen. Type the email
address of the person you'd like to share the workbook with.

4 Select Can edit from the drop-down menu to allow this person to edit the
workbook.
 5 Type a message if you want to include one, then click Share.

6 Your collaborators will now be able to access the workbook.

Comments
One way to collaborate on a workbook is through commenting. Sometimes you may
want to provide feedback or ask a question without editing the contents of a cell. You
can do this by adding a comment.

To add a comment:
1 Select the cell where you want the comment to appear. In our example,
we'll select cell D17.

2 From the Review tab, click the New Comment command.

3 A comment box will appear. Type your comment, then click anywhere
outside the box to close the comment.

4 The comment will be added to the cell, represented by a red triangle in the
top-right corner.

5 Select the cell again to view the comment.

To edit a comment:
1 Select the cell containing the comment you want to edit.

2 From the Review tab, click the Edit Comment command.

3 The comment box will appear. Edit the comment as desired, then click
anywhere outside the box to close the comment.

To show or hide comments:

1 From the Review tab, click the Show All Comments command to view
every comment in your worksheet at the same time.
 2 All comments in the worksheet will appear. Click the Show All Comments
command again to hide them.

You can also choose to show and hide individual comments by selecting the
desired cell and clicking the Show/Hide Comment command.

To delete a comment:
1 Select the cell containing the comment you want to delete. In our example,
we'll select cell E13.
 2 From the Review tab, click the Delete command in the Comments group.

3 The comment will be deleted.

Co-authoring
Another collaboration tool is co-authoring, which allows others to view and edit your
workbook in real time. This makes it easier and quicker to collaborate on a workbook
with your team. After sharing a workbook with others, they'll be able to co-author it.

Real-time co-authoring requires an Office 365 subscription.

When you co-author a workbook, you can see others at work because each will have
their own unique color. If you want to see who's currently editing a workbook, you can
hover over the activity to see their name.
Restoring a previous version
When you or anyone else makes changes to a workbook, the changes are saved
automatically. However, if you're unhappy with the changes, you can always restore a
previous version.

1 Click the clock icon next to the Share button.

2 A Version History pane will appear on the right side of the screen. Double-
click the version you'd like to restore.
3 Once you've decided this is the version you want, click Restore.

4 The previous version will be restored.

Challenge!
1 Open our practice workbook.

2 Add four comments to the worksheet.

3 Delete one of the comments.

4 Make all of the comments visible, using Show All Comments.

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

6 Optional: Share the document with someone you know, and experiment
with some of the various co-authoring features.
 Lesson 26: Inspecting and Protecting Workbooks

Introduction
Before sharing a workbook, you'll want to make sure it doesn't include any spelling
errors or information you want to keep private. Fortunately, Excel includes several tools
to help finalize and protect your workbook, including the Document Inspector and
Protect Workbook feature.

Optional: Download our practice workbook.

Watch the video below to learn more about inspecting and protecting
workbooks.

Excel: Inspecting and Protecting Workbooks

The Document Inspector

Whenever you create or edit a workbook, certain personal information may be added
to the file automatically. You can use the Document Inspector to remove this
information before sharing a workbook with others.

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

additional copy of your workbook before using the Document Inspector to
remove information.
To use the Document Inspector:
1 Click the File tab to access Backstage view.

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

3 You may be prompted to save your file before running the Document
Inspector.

4 Document Inspector will appear. Check or uncheck boxes, depending on

the content you want to review, then click Inspect. In our example, we'll
leave everything selected.
5 The inspection results will appear. In our example, we can see that our
workbook contains comments and some personal information, so we'll
click Remove All on both items to remove this information from the
workbook.

6 When you're done, click Close.

Protecting your workbook
By default, anyone with access to your workbook will be able to open, copy, and edit its
content unless you protect it. There are several ways to protect a workbook, depending
on your needs.

To protect your workbook:

1 Click the File tab to access Backstage view.

2 From the Info pane, click the Protect Workbook command.

3 In the drop-down menu, choose the option that best suits your needs. In
our example, we'll select Mark as Final. Marking your workbook as final is a
good way to discourage others from editing the workbook, while the other
options give you even more control if needed.
4 A dialog box will appear, prompting you to save. Click OK.

5 Another dialog box will appear. Click OK.

6 The workbook will be marked as final.

Marking a workbook as final will not prevent others from editing it. If you
want to prevent people from editing it, you can use the Restrict Access
option instead.
Challenge!
1 Open our practice workbook.

2 Use the Document Inspector to check the workbook and remove anything
it finds.

3 Protect the workbook by Marking As Final.

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

Lesson 27: Intro to PivotTables

Introduction
When you have a lot of data, it can sometimes be difficult to analyze all of the
information in your worksheet. PivotTables can help make your worksheets more
manageable by summarizing data and allowing you to manipulate it in different ways.

Optional: Download our practice workbook.

Watch the video below to learn more about PivotTables.

Excel: Intro to PivotTables

Using PivotTables to answer questions

Consider the example below. Let's say we wanted to answer the question What is the
amount sold by each salesperson? Answering it could be time consuming and difficult;
each salesperson appears on multiple rows, and we would need to total all of their
different orders individually. We could use the Subtotal command to help find the total
for each salesperson, but we would still have a lot of data to work with.

Fortunately, a PivotTable can instantly calculate and summarize the data in a way that
will make it much easier to read. When we're done, the PivotTable will look something
like this:
Once you've created a PivotTable, you can use it to answer different questions by
rearranging—or pivoting—the data. For example, let's say we wanted to answer What is
the total amount sold in each month? We could modify our PivotTable to look like this:

To create a PivotTable:
1 Select the table or cells (including column headers) you want to include in
your PivotTable.

2 From the Insert tab, click the PivotTable command.

3 The Create PivotTable dialog box will appear. Choose your settings, then
click OK. In our example, we'll use Table1 as our source data and place the
PivotTable in a new worksheet.
4 A blank PivotTable and Field List will appear in a new worksheet.

5 Once you create a PivotTable, you'll need to decide which fields to add.
Each field is simply a column header from the source data. In the
PivotTable Fields list, check the box for each field you want to add. In our
example, we want to know the total amount sold by each salesperson, so
we'll check the Salesperson and Order Amount fields.
6 The selected fields will be added to one of the four areas below. In our
example, the Salesperson field has been added to the Rows area, while
Order Amount has been added to Values. You can also drag and drop
fields directly into the desired area.

7 The PivotTable will calculate and summarize the selected fields. In our
example, the PivotTable shows the amount sold by each salesperson.

Just like with normal spreadsheets, you can sort the data in a PivotTable
using the Sort & Filter command on the Home tab. You can also apply any
 type of number formatting you want. For example, you may want to
change the number format to Currency. However, be aware that some
types of formatting may disappear when you modify the PivotTable.

If you change any of the data in your source worksheet, the PivotTable will
not update automatically. To manually update it, select the PivotTable
and then go to Analyze > Refresh.

Pivoting data
One of the best things about PivotTables is that they can quickly pivot—or reorganize—
your data, allowing you to examine your worksheet in several ways. Pivoting data can
help you answer different questions and even experiment with your data to discover
new trends and patterns.

To add columns:
So far, our PivotTable has only shown one column of data at a time. To show multiple
columns, you'll need to add a field to the Columns area.

1 Drag a field from the Field List into the Columns area. In our example, we'll
use the Month field.
 2 The PivotTable will include multiple columns. In our example, there is now
a column for each person's monthly sales, in addition to the grand total.

To change a row or column:

Changing a row or column can give you a completely different perspective on your data.
All you have to do is remove the field in question, then replace it with another.

1 Drag the field you want to remove out of its current area. You can also
uncheck the appropriate box in the Field List. In this example, we've
removed the Month and Salesperson fields.
2 Drag a new field into the desired area. In our example, we'll place the
Region field under Rows.

3 The PivotTable will adjust—or pivot—to show the new data. In our example,
it now shows the amount sold by each region.
To learn more:
Once you're comfortable with PivotTables, review our Doing More with PivotTables
lesson for additional ways to customize and manipulate data.

Challenge!
1 Open our practice workbook.

2 Create a PivotTable in a separate sheet.

3 We want to answer the question What is the total amount sold in each
region? To do this, select Region and Order Amount. When you're finished,
your workbook should look like this:

4 In the Rows area, remove Region and replace it with Salesperson.

5 Add Month to the Columns area.

 6 Change the number format of cells B5:E13 to Currency. Note: You might
have to make columns C and D wider to see the values.

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

Lesson 28: Doing More with PivotTables

Introduction
As you learned in our previous lesson, PivotTables can be used to summarize and
analyze almost any type of data. To manipulate your PivotTable—and gain even more
insight into your data—Excel offers three additional tools: filters, slicers, and
PivotCharts.

Optional: Download our practice workbook.

Watch the video below to learn more about enhancing PivotTables.

Excel: Doing More with PivotTables

Filters
Sometimes you may want focus on a certain section of your data. Filters can be used to
narrow down the data in your PivotTable so you can view only the information you
need.

To add a filter:
In the example below, we'll filter out certain salespeople to determine how their
individual sales are impacting each region.

1 Drag a field from the Field List to the Filters area. In this example, we'll use
the Salesperson field.
2 The filter will appear above the PivotTable. Click the drop-down arrow,
then check the box next to Select Multiple Items.

3 Uncheck the box next to any item you don't want to include in the
PivotTable. In our example, we'll uncheck the boxes for a few salespeople,
then click OK.
 4 The PivotTable will adjust to reflect the changes.

Slicers
Slicers make filtering data in PivotTables even easier. Slicers are basically just filters but
are easier and faster to use, allowing you to instantly pivot your data. If you frequently
filter your PivotTables, you may want to consider using slicers instead of filters.

To add a slicer:
1 Select any cell in the PivotTable.

2 From the Analyze tab, click the Insert Slicer command.

3 A dialog box will appear. Check the box next to the desired field. In our
example, we'll select Salesperson, then click OK.
4 The slicer will appear next to the PivotTable. Each selected item will be
highlighted in blue. In the example below, the slicer contains all eight
salespeople, but only five of them are currently selected.

5 Just like filters, only selected items are used in the PivotTable. When you
select or deselect an item, the PivotTable will instantly reflect the change.
Try selecting different items to see how they affect the PivotTable. Press
and hold the Ctrl key on your keyboard to select multiple items at once.

You can also click the Filter icon in the top-right corner of the slicer to
select all items at once.
PivotCharts
PivotCharts are like regular charts, except they display data from a PivotTable. Just like
regular charts, you'll be able to select a chart type, layout, and style that will best
represent the data.

To create a PivotChart:
In the example below, our PivotTable is showing a portion of each region's sales figures.
We'll use a PivotChart so we can see the information more clearly.

1 Select any cell in your PivotTable.

2 From the Insert tab, click the PivotChart command.

3 The Insert Chart dialog box will appear. Select the desired chart type and
layout, then click OK.

4 The PivotChart will appear.

 Try using filters or slicers to narrow down the data in your PivotChart. To
view different subsets of information, change the columns or rows in your
PivotTable. In the example below, we've changed the PivotTable to view the
monthly sales for each salesperson.

Challenge!
1 Open our practice workbook.

2 In the Rows area, remove Region and replace it with Salesperson.

 3 Insert a PivotChart and choose the type Line with Markers.

4 Insert a slicer for Regions.

5 Use the slicer to only show the South and East regions.

6 Change the PivotChart type to Stacked Column.

7 In the PivotChart Fields pane to the right, add Month to the Legend
(Series) area. Note: You can also click the PivotTable and add Month to the
Columns area for the same results.

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

Lesson 29: What-if Analysis

Introduction
Excel includes powerful tools to perform complex mathematical calculations, including
what-if analysis. This feature can help you experiment and answer questions with
your data, even when the data is incomplete. In this lesson, you will learn how to use a
what-if analysis tool called Goal Seek.

Optional: Download our practice workbook.

Watch the video below to learn more about what-if analysis and Goal Seek.
Excel: What-if Analysis

Goal Seek
Whenever you create a formula or function in Excel, you put various parts together to
calculate a result. Goal Seek works in the opposite way: It lets you start with the
desired result, and it calculates the input value that will give you that result. We'll use a
few examples to show how to use Goal Seek.

To use Goal Seek (example 1):

Let's say you're enrolled in a class. You currently have a grade of 65, and you need at
least a 70 to pass the class. Luckily, you have one final assignment that might be able to
raise your average. You can use Goal Seek to find out what grade you need on the final
assignment to pass the class.

In the image below, you can see that the grades on the first four assignments are 58, 70,
72, and 60. Even though we don't know what the fifth grade will be, we can write a
formula—or function—that calculates the final grade. In this case, each assignment is
weighted equally, so all we have to do is average all five grades by typing
=AVERAGE(B2:B6). Once we use Goal Seek, cell B6 will show us the minimum grade
we'll need to make on that assignment.
1 Select the cell with the value you want to change. Whenever you use Goal
Seek, you'll need to select a cell that already contains a formula or
function. In our example, we'll select cell B7 because it contains the
formula =AVERAGE(B2:B6).

2 From the Data tab, click the What-If Analysis command, then select Goal
Seek from the drop-down menu.

3 A dialog box will appear with three fields. The first field, Set cell:, will
contain the desired result. In our example, cell B7 is already selected.

The second field, To value:, is the desired result. In our example, we'll enter
70 because we need to earn at least that to pass the class.

The third field, By changing cell:, is the cell where Goal Seek will place its
 answer. In our example, we'll select cell B6 because we want to determine
the grade we need to earn on the final assignment.

4 When you're done, click OK.

5 The dialog box will tell you if Goal Seek was able to find a solution. Click OK.

6 The result will appear in the specified cell. In our example, Goal Seek
calculated that we will need to score at least a 90 on the final assignment to
earn a passing grade.

To use Goal Seek (example 2):

Let's say you're planning an event and want to invite as many people as you can without
exceeding a budget of $500. We can use Goal Seek to figure out how many people to
invite. In our example below, cell B5 contains the formula =B2+B3*B4 to calculate the
total cost of a room reservation, plus the cost per person.

1 Select the cell with the value you want to change. In our example, we'll
select cell B5.

2 From the Data tab, click the What-If Analysis command, then select Goal
Seek from the drop-down menu.

3 A dialog box will appear with three fields. The first field, Set cell:, will
contain the desired result. In our example, cell B5 is already selected.

The second field, To value:, is the desired result. In our example, we'll enter
500 because we only want to spend $500.

The third field, By changing cell:, is the cell where Goal Seek will place its
answer. In our example, we'll select cell B4 because we want to know how
many guests we can invite without spending more than $500.

4 When you're done, click OK.

 5 The dialog box will tell you if Goal Seek was able to find a solution. Click OK.

6 The result will appear in the specified cell. In our example, Goal Seek
calculated the answer to be approximately 18.62. In this case, our final
answer needs to be a whole number, so we'll need to round the answer up
or down. Because rounding up would cause us to exceed our budget, we'll
round down to 18 guests.

As you can see in the example above, some situations will require the
answer to be a whole number. If Goal Seek gives you a decimal, you'll need
to round up or down, depending on the situation.

Other types of what-if analysis

For more advanced projects, you may want to consider the other types of what-if
analysis: scenarios and data tables. Instead of starting from the desired result and
working backward, like with Goal Seek, these options allow you to test multiple values
and see how the results change.
 ▶︎ Scenarios let you substitute values for multiple cells (up to 32) at the same
time. You can create as many scenarios as you want and then compare
them without changing the values manually. In the example below, we're
using scenarios to compare different venues for an upcoming event.

For more information on scenarios, read this article from Microsoft.

▶︎ Data tables allow you to take one or two variables in a formula and replace
them with as many different values as you want, then view the results in a
table. This option is especially powerful because it shows multiple results
at the same time, unlike scenarios or Goal Seek. In the example below, we
can view 24 possible results for a car loan.

For more information on data tables, read this article from Microsoft.

Challenge!
1 Open our practice workbook.

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

3 In cell B8, create a function that calculates the average of the sales in
B2:B7.
 4 The workbook shows Dave's monthly sales amounts for the first half of the
year. If he reaches a $200,000 mid-year average, he will receive a 5% bonus.
Use Goal Seek to find how much he needs to sell in June in order to make
the $200,000 average.

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

Lesson 30: What is Office 365?

What is Office 365?

Office 365 is a subscription-based version of the Microsoft Office Suite, and you have a
few options when purchasing an account. One is Office 365 Personal, which gives a
single user full access to every Office application. Another is Office 365 Home, which is
designed for families where multiple people will be using Office.

Watch the video below to see more of what Office 365 has to offer.

What is Office 365?

Exclusive features
There are a lot of similarities between Office 365 programs and the traditional Microsoft
Office Suite, so the overall experience should feel familiar if you've used Office before.

However, Office 365 offers several advantages that aren’t available with the Microsoft
Office Suite. For instance, a subscription to Office 365 grants you access to more
features, including the Translator, a Resume Assistant, and Smart Lookup. You can also
collaborate with others in Excel via the co-authoring feature, which lets others edit your
workbook in real time.

Office mobile apps also come with more features when you subscribe. For example, you
can do things like insert page breaks, use more colors, or make PivotTables with the
Excel mobile app. Free editions of the mobile apps, however, only let you perform basic
tasks, like creating a file and entering text.

Office 365 includes other benefits as well, like more file storage in OneDrive and
technical support.

SharePoint and software updates

Another distinct advantage to using Office 365, especially for businesses, is access to
SharePoint Online. This is a service included in some versions of Office 365 that allows
you to share and collaborate with others, whether they’re colleagues or customers.
Because the documents live in the cloud, security permissions can be set up to allow
anyone in the organization, regardless of their location, to view a document.
Office 365 subscribers also get more frequent software updates than those who have
purchased Office without a subscription. This means Office 365 subscribers have access
to the latest features, security updates, and bug fixes.

Lesson 31: New Features in Office 2019

New features in Office 2019

Office 2019 was released in September 2018. If you've used Office 2016 or earlier
versions, you'll probably find Office 2019 familiar. The interface is similar, and most of
the features still work the same. However, there are several improvements designed to
make Office 2019 more powerful and easier to use.

Office 2019 is only available for computers running Windows 10 or one of

the three most recent versions of macOS.
 Watch the video below to learn more about Office 2019's new
improvements and features.

What's New in Office 2019

Visual updates
Office 2019 comes with several new features that customize the visuals of your projects.
There's a new library of graphics called Icons that you can use and customize however
you want. You can also turn your drawings into standard shapes using the Ink to Shape
function and insert interactive 3D models into your projects.
Word
Word has a new feature called Learning Tools that can help make text easier to read
without making permanent changes to your document. You can change the text spacing,
page color, or even have Word read your text aloud.

PowerPoint
PowerPoint includes a new Morph transition that allows you to animate objects
between slides in a short amount of time. If you like saving your presentations as video
files, PowerPoint now also gives you the ability to export your presentation at 4K
resolution.
Excel
There are a couple of new chart types in Excel: map charts and funnel charts. There's
also a feature called precision selection, which lets you deselect individual cells after
you've highlighted them.

Office 2019 vs. Office 365

It's important to note that Office 2019 doesn't have as many new features as Office
365. If you're interested in more dynamic updates, you may want to look into the
subscription-based Office 365 instead.

Many of the changes and improvements in Office 2019 are small, but they can help to
increase your productivity and ease of use in certain situations.

Lesson 32: What are Reference Styles?

What are reference styles?

Every Excel spreadsheet contains rows and columns. Most of the time, columns are
identified by letters (A, B, C), and rows are identified by numbers (1, 2, 3). In Excel, this
is known as the A1 reference style. However, some prefer to use a different method in
which columns are also identified by numbers. This is known as the R1C1 reference
style.

In the example below, the image on the left has a number over each column, which
means it is using the R1C1 reference style. The image on the right is using the A1
reference style.
While the R1C1 reference style is helpful in certain situations, you'll probably want to use
the A1 reference style most of the time. This tutorial will use the A1 reference style. If
you're currently using the R1C1 reference style, you'll need to turn it off.

To turn off the R1C1 reference style:

1 Click the File tab to access Backstage view.

2 Click Options.

3 The Excel Options dialog box will appear. Click Formulas on the left side of
the dialog box.
 4 Uncheck the box next to R1C1 reference style, then click OK. Excel will now
use the A1 reference style.

Lesson 33: Office Intelligent Services

Office Intelligent Services

Microsoft Office contains many useful features, including a language translator and
PowerPoint Designer. But when you try to use these features, a prompt may ask you to
activate Intelligent Services first. While it may seem like an easy decision, you should
consider what you're agreeing to when you decide to turn on Intelligent Services.

What are Intelligent Services?

Intelligent Services power several cloud-enhanced features throughout Office. In order
for these features to work, however, Microsoft must collect and analyze the contents of
your document. In addition, it will collect data on how you use Office.

Should you use Intelligent Services?

Before deciding whether to enable Intelligent Services, you should first know what
Microsoft does with the data it collects. According to its privacy statement, it uses
collected data for things like product development, research, and targeted advertising.

Before activating Intelligent Services, ask yourself if you're comfortable with Microsoft
having access to your work. If you're concerned about the company collecting your data
or seeing confidential documents, you may not want to use any Intelligent Services-
based features.

Activating Intelligent Services

By default, Intelligent Services is turned off, and you have two ways to activate it.

The quickest method is to select an Intelligent Services-based feature, such as Translate.

A prompt will appear, asking you to turn on Intelligent Services. Simply click Turn on to
enable it.

The other method involves a few more steps.

1 Click the File tab in Word, Excel, PowerPoint, or Outlook.

2 Click Options.

3 Click the checkbox labeled Enable services, then click OK.

Notable features
PowerPoint Designer can analyze the images, lists, and text in your presentation to
generate professional-looking slides, and it uses Intelligent Services to give you more
personalized suggestions.

To use it, select the Design tab, then click Design Ideas on the right. As you add
elements to your slide, the feature will update with new ideas. It’ll even give you
suggestions if you have a completely blank presentation.

Keep in mind, however, that PowerPoint Designer is exclusive to Office 365 subscribers.

Smart Lookup allows you to conduct an online search of a word or phrase within Word,
Excel, or PowerPoint. To use it, right-click a term, then select Smart Lookup. A window
will appear on the right, giving you the most relevant search results and definitions.

To use Translate, simply right-click on a word or phrase, then select Translate. You can
then translate your selection into dozens of languages.
Keep in mind that this feature isn’t perfect, so don’t be surprised if the results aren’t
totally accurate.

Office 365
It’s worth noting that some Intelligent Service-based features, like the PowerPoint
Designer, only work with an Office 365 subscription. Microsoft also plans to add more
Intelligent Service-based features to Office 365 in the future. Upcoming features include
Ideas in Excel, which suggests different ways to display your data; and the LinkedIn
Resume Assistant, which uses the entire LinkedIn database to help make your resume
stand out.

From presentation ideas to Smart Lookup, Intelligent Services can provide a number of
useful features if you’re comfortable with Microsoft collecting your data.