Module for COF 111 (Computer Fundamentals and Word Processing)

Module 7: Microsoft Excel (Part 3)

Objectives: Upon completion of this Module, the student should be able to:

 Create and format Worksheets and Workbooks

 Navigate through Worksheets and Workbooks
 Customize Options and Views for Worksheets and Workbooks
 Configure Worksheets and Workbooks to Print or Save
 Create Cells and Ranges
 Insert Data in Cells and Ranges
 Format Cells and Ranges
 Order and Group Cells and Ranges
 Create and modify a Table
 Filter and Sort a Table
 Apply Formulas and Functions
 Utilize Cell Ranges and References in Formulas and Functions
 Summarize Data with Functions
 Utilize Conditional Logic in Functions
 Format and Modify Text with Functions
 Create Charts and Objects
 Insert and Format Building Blocks
 Format a Chart
 Insert and Format an Object

Table

Introduction

Once you've entered information into a 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, but
they'll also help to 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.

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 A4:D10.
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
different options for customizing a table, 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 in your table, Excel allows you to modify the table size by
including additional rows and columns. There are two simple ways to change the table size:

 Begin typing new content after the last row or column in the table. The row or
column will be included in the table automatically.
  Click, hold, 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 style.

4. The selected table style will appear.

To modify the 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.

2. From the Design tab, 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 will automatically calculate the total value of the
cells in column D.
These options can affect your table style in various ways, depending on the type of content in
your table. You may need to experiment with a few different options to find the exact style you
want.

To remove a table:

Sometimes you may not want to use the additional features included with tables, such as the
Sort and Filter drop-down arrows. You can remove a table from the workbook while still
preserving the table's formatting elements, like font and cell color.

1. Select any cell in your table. The Design tab will appear.

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.
Practice!
1. Open an existing Excel workbook.
2. Format a range of cells as a table. If you are using the example, format the cell
range A2:E13.
3. Add a row or column to the table.
4. Choose a new table style.
5. Change the table style options. If you are using the example, add a total row.
6. Remove the table.

Charts

Introduction

It can often 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.

Understanding charts

Excel has several different types of charts, allowing you to choose the one that best fits your
data. In order 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.
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
different elements, or parts, that can help you interpret the 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 in the worksheet.

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

Chart layout and 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—such as 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 different 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 use the chart formatting shortcut buttons to quickly add chart elements, change
the chart style, and filter the 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 the 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, the Book Sales data are grouped by year, with columns for each genre. However, we
could switch the rows and columns so the chart will group the data by genre, with columns
for each year. 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 genre, with columns for each year.

To change the chart type:

If you find that your data isn't well suited to 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 the 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. Alternatively, you can 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.

Practice!
1. Open an existing Excel workbook.
2. Use worksheet data to create a chart. If you are using the example, use the cell
range A1:F6 as the source data for the chart.
3. Change the chart layout. If you are using the example, select Layout 8.
4. Apply a chart style.
5. Move the chart. If you are using the example, move the chart to
a new worksheet named Book Sales Data: 2008-2012.
 Sparklines

Introduction

Sometimes you may want to analyze and view trends in your data without creating an entire
chart. Sparklines are miniature charts that fit into a single cell. Because they're so compact, it's
easy to include several sparklines in a workbook.

Types of sparklines

There are three different types of sparklines: Line, Column,

and Win/Loss. Line and Column work the same as line and column charts. Win/Loss is similar
to Column, except it only shows whether each value is positive or negative instead of
how high or low the values are. All three types can display markers at important points, such as
the highest and lowest points, to make them easier to read.

Why use sparklines?

Sparklines have certain advantages over charts. For example, let's say you have 1,000 rows of
data. A traditional chart would have 1,000 data series to represent all of the rows, making
relevant data difficult to find. But if you placed a sparkline on each row, it will be right next to
its source data, making it easy to see relationships and trends for multiple data series at the
same time.
In the image below, the chart is extremely cluttered and difficult to follow, but the sparklines
allow you to clearly follow each salesperson's data.

Sparklines are ideal for situations when you need a clear overview of the data at a glance and
when you don't need all of the features of a full chart. On the other hand, charts are ideal for
situations when you want to represent the data in greater detail, and they are often better
for comparing different data series.

To create sparklines:

Generally, you will have one sparkline for each row, but you can create as many as you want in
any location. Just like formulas, it's usually easiest to create a single sparkline and then use
the fill handle to create sparklines for the adjacent rows. In our example, we'll create sparklines
to help visualize trends in sales over time for each salesperson.

1. Select the cells that will serve as the source data for the first sparkline. In our
example, we'll select the cell range B2:G2.

2. Select the Insert tab, then choose the desired Sparkline from

the Sparklines group. In our example, we'll choose Line.
3. The Create Sparklines dialog box will appear. Use the mouse to select the cell
where the sparkline will appear, then click OK. In our example, we'll select
cell H2, and the cell reference will appear in the Location Range: field.

4. The sparkline will appear in the specified cell.

5. Click, hold, and drag the fill handle to create sparklines in adjacent cells.
 6. Sparklines will be created for the selected cells. In our example, the sparklines
show clear trends in sales over time for each salesperson in our worksheet.

Modifying sparklines

It's easy to change the way sparklines appear in your worksheet. Excel allows you to customize
a sparkline's markers, style, type, and more.

To display markers:

Certain points on a sparkline can be emphasized with markers, or dots, making the sparkline
more readable. For example, in a line with a lot of ups and downs, it might be difficult to tell
which values are the highest and lowest points. Showing the high point and low point will make
them easier to identify.

1. Select the sparkline(s) you want to change. If they are grouped in adjacent cells,

you'll only need to click on one sparkline to select them all.

2. From the Design tab, select the desired option(s) from the Show group. In our

example, we'll select High Point and Low Point.

3. The sparkline(s) will update to show the selected markers.

To change the sparkline style:

1. Select the sparkline(s) you want to change.

2. From the Design tab, click the More drop-down arrow.

3. Choose the desired style from the drop-down menu.

4. The sparkline(s) will update to show the selected style.

To change the sparkline type:

1. Select the sparkline(s) you want to change.

2. From the Design tab, select the desired Sparkline type. In our example, we'll
select Column.
 3. The sparkline(s) will update to reflect the new type.

Some sparkline types will be better suited for certain types of data. For example, Win/Loss is
best suited for data where there could be positive and negative values (such as net earnings).

Changing the display range

By default, each sparkline is scaled to fit the maximum and minimum values of its own data
source: The maximum value will go to the top of the cell, while the minimum will go to the
bottom. However, this doesn't show how high or low the values are when compared to the
other sparklines. Excel allows you to modify the sparkline display range, which makes it easier
to compare sparklines.

To change the display range:

1. Select the sparklines you want to change.

2. From the Design tab, click the Axis command. A drop-down menu will appear.

 3. Below Vertical Axis Minimum Value Options and Vertical Axis Maximum Value
Options, select Same for All Sparklines.

4. The sparklines will update to reflect the new display range. In our example, we
can now use the sparklines to compare trends for each salesperson.

Practice!
1. Open an existing Excel workbook.
2. Create a sparkline on the first row of data. If you are using the example, create a
sparkline for the first salesperson on row 3.
3. Use the fill handle to create sparklines for the remaining rows.
 4. Create markers for the High Point and Low Point.
5. Change the sparkline type.
6. Change the display range to make the sparklines easier to compare.

Track Changes and Comments

Introduction

Let's say someone asked you to proofread or collaborate on a workbook. If you had a printed
copy, you might use a red pen to edit cell data, mark spelling errors, or add comments in the
margins. Excel allows you to do all of these things electronically using the Track
Changes and Comments features

Understanding Track Changes

When you turn on the Track Changes feature, every cell you edit will be highlighted with a
unique border and indicator. Selecting a marked cell will show the details of the change. This
allows you and other reviewers to see what's been changed before accepting the revisions
permanently.

There are some changes Excel cannot track. Before using this feature, you may want to review
Microsoft's list of changes that Excel does not track or highlight.

You cannot use Track Changes if your workbook includes tables. To remove a table, select it,
click the Design tab, then click Convert to Range.
To turn on Track Changes:

1. From the Review tab, click the Track Changes command, then select Highlight

Changes... from the drop-down menu.

2. The Highlight Changes dialog box will appear. Check the box next to Track
changes while editing. Verify the box is checked for Highlight changes on
screen, then click OK.

3. If prompted, click OK to allow Excel to save your workbook.

4. Track Changes will be turned on. A triangle and border color will appear in any

cell you edit. If there are multiple reviewers, each person will be assigned a
different color.
5. Select the edited cell to see a summary of the tracked changes. In our example
below, we've changed the content of cell E11 from ? to Tyler.
When you turn on Track Changes, your workbook will be shared automatically. Shared
workbooks are designed to be stored where other users can access and edit the workbook at
the same time, such as a network. However, you can also track changes in a local or personal
copy, as seen throughout this lesson.

To list changes on a separate worksheet:

You can also view changes on a new worksheet, sometimes called the Tracked Changes history.
The history lists everything in your worksheet that has been changed, including the old
value (previous cell content) and the new value (current cell content).

1. Save your workbook.
2. From the Review tab, click the Track Changes command, then
select Highlight Changes... from the drop-down menu.

3. The Highlight Changes dialog box will appear. Check the box next to List changes
on a new sheet, then click OK.
 4. The tracked changes will be listed on their own worksheet, called History.

To remove the History worksheet from your workbook, you can either save your workbook
again or uncheck the box next to List changes on a new sheet in the Highlight Changes dialog
box.

Reviewing changes

Tracked changes are really just suggested changes. To become permanent, the changes must
be accepted. On the other hand, the original author may disagree with some of the tracked
changes and choose to reject them.

To review tracked changes:

1. From the Review tab, click Track Changes, then select Accept/Reject

Changes from the drop-down menu.
2. If prompted, click OK to save your workbook.
3. A dialog box will appear. Make sure the box next to the When: field is checked
and set to Not yet reviewed, then click OK.

4. A dialog box will appear. Click Accept or Reject for each change in the workbook.

Excel will move through each change automatically until you have reviewed
them all.

5. Even after accepting or rejecting changes, the tracked changes will still appear in
your workbook. To remove them completely, you'll need to turn off Track
Changes. From the Review tab, click Track Changes, then select Highlight
Changes from the drop-down menu.
 6. A dialog box will appear. Uncheck the box next to Track changes while editing,
then click OK.

7. Click Yes to confirm that you want to turn off Track Changes and stop sharing
your workbook.

To accept or reject all the changes at once, click Accept All or Reject All in the Accept or Reject
Changes dialog box.

Turning off Track Changes will remove any tracked changes in your workbook. You will not be
able to view, accept, or reject changes; instead, all changes will all be accepted automatically.
Always review the changes in your worksheet before turning off Track Changes.

Comments

Sometimes you may want to add a comment to provide feedback instead of editing the
contents of a cell. While often used in combination with Track Changes, you don't necessarily
need to have Track Changes turned on to use comments.

To add a comment:

1. Select the cell where you want the comment to appear. In our example, we'll
select cell E8.
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 the 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
then 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 E8.

2. From the Review tab, click the Delete command in the Comments group.

 3. The comment will be deleted.

Practice!
1. Open an existing Excel workbook
3
2. Turn on Track Changes.
3. Delete, add, or edit the text in several cells. Notice how the edited cells
are highlighted.
4. Accept all of the tracked changes, then turn off Track Changes.
5. Add a few comments to different cells in your worksheet.
6. Show all of the comments, then hide them.

Finalizing 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 Spell Check and the Document Inspector

To use Spell Check:

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

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

3. A dialog box will appear after reviewing all spelling errors. Click OK to close Spell
Check.

If there are no appropriate suggestions, you can also enter the correct spelling manually.

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.

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.

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. The 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.
4. The inspection results will appear. In our example, we can see that our
workbook contains some personal information, so we'll click Remove All to
remove that information from the workbook.
 5. 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 many different 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 someone from editing it. If you want to prevent
people from editing it, you can use the Restrict Access option instead.

Practice!
1. Open an existing Excel workbook.
2. Run the Spell Check to correct any spelling errors in the workbook.
3. Use the Document Inspector to check the workbook. If you are using the
example, remove all personal information from the workbook.
4. Protect the workbook by marking it as final.

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 another way to visualize data and make worksheets
easier to understand
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.

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 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 have

applied multiple rules to a worksheet.
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.

Practice!
1. Open an existing Excel workbook.
2. Apply conditional formatting to a range of cells with numerical values. If you are
using the example, apply a rule for the sales data (cells B3:G23) that will fill cells
with green if their values are more than $9000.
3. Apply a second conditional formatting rule to the same set of cells. If you are
using the example, apply a preset conditional formatting rule.
4. Clear all conditional formatting rules from the worksheet.

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.

Using PivotTables to answer questions

Let's say we wanted to answer the question: What is the amount sold by each salesperson? for
the sales data in the example below. Answering this question 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's both

easy to read and manipulate. 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, if we wanted to answer the question: 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) containing the data you

want to use.

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 on a new worksheet.
4. A blank PivotTable and Field List will appear on 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 Field
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 the Field List. In
our example, the Salesperson field has been added to the Rows area, while
the Order Amount has been added to the Values area. Alternatively, you can
click, hold, and drag a field to 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 spreadsheet data, you can sort the data in a PivotTable using the Sort &
Filter command in 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—data,
allowing you to look at your worksheet data in different ways. Pivoting data can help you
answer different questions and even experiment with the data to discover new trends and
patterns.
In our example, we used the PivotTable to answer the question: What is the total amount sold
by each salesperson? But now we'd like to answer a new question: What is the total amount
sold in each month? We can do this by simply changing the field in the Rows area.

To change the row:

1. Click, hold, and drag any existing fields out of the Rows area. The field will
disappear.

2. Drag a new field from the Field List into the Rows area. In our example, we'll use
the Month field.
 3. The PivotTable will adjust—or pivot—to show the new data. In our example, it
now shows the total order amount for each month.

To add columns:

So far, our PivotTable has only shown one column of data at a time. In order 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 Region field.
 2. The PivotTable will include multiple columns. In our example, there is now a
column for each region.

Filters

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

To add a filter:

In our example, we'll filter out certain salespeople to determine how they affect the total sales.

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 for any items you don't want to include in the PivotTable. In
our example, we'll uncheck the boxes for a few different 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 they're
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. Select 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 a list of all
salespeople, and six of them are currently selected.

5. Just like filters, only selected items are used in the PivotTable. When

you select or deselect items, the PivotTable will instantly reflect the changes.
Try selecting different items to see how they affect the PivotTable. Press and
hold the Ctrl key on your keyboard to select multiple items from a slicer.
You can also click the Filter icon in the top-right corner to select all items from the slicer 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 this example, our PivotTable is showing each person's total sales per month. 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 slicers or filters to change the data that is displayed. The PivotChart will automatically
adjust to show the new data.
Practice!
1. Open an existing Excel workbook.
2. Create a PivotTable using the data in the workbook.
3. Experiment by placing different fields in the rows and columns areas.
4. Filter the report with a slicer.
5. Create a PivotChart.
6. If you are using the example, use the PivotTable to answer the question, Which
salesperson sold the lowest amount in January? Hint: First decide
which fields you need in order to answer the question.

What-If Analysis

Introduction

Excel includes many 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.
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 whose 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:

o Set cell: This is the cell that will contain the desired result. In our
example, cell B7 is already selected.
o To value: This is the desired result. In our example, we'll
enter 70 because we need to earn at least that to pass the class.
o By changing cell: This 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 B4 contains the formula =B1+B2*B3 to calculate the total cost of a
room reservation, plus the cost per person.

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

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:

o Set cell: This is the cell that will contain the desired result. In our
example, cell B4 is already selected.
o To value: This is the desired result. In our example, we'll
enter 500 because we only want to spend $500.
o By changing cell: This is the cell where Goal Seek will place its answer.
In our example, we'll select cell B3 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 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, check out 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, check out this article from Microsoft.

Practice!
1. Open an existing Excel workbook
2. Use Goal Seek to determine an unknown value. If you're using the example, go
to the History Class worksheet and use Goal Seek to determine what grade you
would need on Test 3 to earn a final grade average of 90.

Customizing the Ribbon

To customize the Ribbon in Excel 2013:

You can customize the Ribbon by creating your own tabs with whichever commands you want.
Commands are always housed within a group, and you can create as many groups as you want
in order to keep your tab organized. If you want, you can even add commands to any of the
default tabs, as long as you create a custom group in the tab.

1. Right-click the Ribbon and select Customize the Ribbon... from the drop-down

menu.
2. The Excel Options dialog box will appear. Locate and select New Tab.

3. Make sure the New Group is selected, select a command, then click Add. You
can also drag commands directly into a group.
4. When you are done adding commands, click OK. The commands will be added to
the Ribbon.
If you don't see the command you want, click the Choose commands from: drop-down box and
select All Commands.

Enabling Touch Mode

To view the Ribbon in Touch Mode in Excel 2013:

If you're working on a touch-screen device, you can enable Touch Mode to create more open
space on the Ribbon, making commands easier to tap with your fingers.

1. Click the drop-down arrow to the right of the Quick Access toolbar, then

select Touch/Mouse Mode from the drop-down menu.
2. The Touch/Mouse Mode command will appear on the Quick Access toolbar.
3. Click the Touch/Mouse Mode command, then select Touch from the drop-down
menu.

4. The Ribbon will switch to Touch Mode.

To turn off Touch Mode, click the Touch/Mouse Mode command, then select Mouse from the
drop-down menu.
Exercise 1:

Create a Pie Graph by Selecting a Data Table

1. Open a new worksheet in Excel and type in the data in Table G1-1. Title the columns as in the table.
Agreement: Please watch the video.
https://www.youtube.com/watch?v=e2SXvq99S5E
https://www.youtube.com/watch?v=RdTozKPY_OQ

References:

 Vermaat. 1/e, c2019.Discovering Computers 2018: Digital Technology, Data, and Devices
 John Ward. Introduction to Computer Applications and Concepts
https://courses.lumenlearning.com/zeliite115/ , retrieved October 6, 2020
 Goodwill Community Foundation. A program of Goodwill Community Foundation® and
Goodwill Industries of Eastern North Carolina Inc.® (GIENC®), all GCFLearnFree.org®
educational content is designed and produced in the GCF Global headquarters in
Durham, N.C. https://edu.gcfglobal.org/en/word , retrieved December 2, 2020