Republic of the Philippines

MARINDUQUE STATE COLLEGE

SCHOOL OF INDUSTRIAL TECHNOLOGY
P Manguera Sr. Rd., Tanza, Boac, Marinduque 4900

Bachelor of Science in Industrial Technology (BSIT)

Level III Re-Accredited by the Accrediting Agency for Chartered Colleges Universities in the
Philippines (AACCUP), Inc.

Module 5 – Charts, Auto Fill and

Conditional Formatting

Revised 03/14/21
 Applied Computer: Spreadsheet

Module Overview

This module will continue to expand on your knowledge of functions and formulas from the prior modules
with the addition of Auto fill. Additionally, this module will focus on the many different types of Charts
available in Excel and Conditional Formatting

Contents
Module Overview .................................................................................................................................................1
1. Auto Fill ..........................................................................................................................................................2
1.1. Auto Fill.....................................................................................................................................................2
1.2. Auto fill and Formulas.................................................................................................................................2
1.3. Absolute Cell Reference .............................................................................................................................4
2. Charts .............................................................................................................................................................4
2.1. Parts of a Chart .........................................................................................................................................5
2.2. Types of Charts .........................................................................................................................................7
3. Condi tional Formatting! ......................................................................................... Error! Bookmark not defined.
3.1. Understanding conditional formatting ........................................................................................................ 12
3.2. Conditional formatting presets ................................................................................................................... 14
4. Exerci se s – Now, it’s your turn to shine! ............................................................... Error! Bookmark not defined.

Module 5
 Applied Computer: Spreadsheet

1. Auto Fill
As mentioned during prior lessons, the auto fill is a tool used to assist with copying and
pasting information (including formulas & functions) in Excel.

1.1. Auto Fill

Perhaps one of the best features found in Microsoft Excel is the “Automatic Fill” capability.
Auto Fill can save time by eliminating the manual task of entering commonly used text
labels and number series.
In the bottom right corner of a selected cell or range of cells there is a small black square
known as the fill handle. When the mouse pointer is directly on top of the fill handle the
mouse pointer will change to the thin plus sign. This is your visual clue that you can
AutoFill by dragging the fill handle down or to the right.

Fill Handle Used to copy cell contents to

“Auto Fill” adjacent cells.
To use, hover your mouse over
the bottom left corner of the
selected cell or cells.
Click and drag to fill or copy
contents into new cells.
Excel can auto fill numbers, dates, and even word patterns. We will try an example with
entering months.

Follow Create new worksheet auto fill Months

Me 1. Open a new worksheet in Excel
2. Type January into A2.
3. Left click once in A2 to select it again.
4. Hover over the bottom right corner of the cell, auto fill
handle, until the cursor changes to look like the auto fill.
5. Click and drag to cell A13.
6. Select B1
7. Type 2010
8. In C1, type 2011
9. Us the auto fill to complete the years through present.
Select both B1 and C1 to auto fill.
It is important to fill in two numbers in this case because
otherwise Excel will complete the pattern in unexpected ways such as fill in each
column with the same number.

1.2. Auto fill and Formulas

Module 5
 Applied Computer: Spreadsheet

In addition to completing numbers, dates, and some word patterns, the auto fill function
is most powerful when copying formulas.
Follow Add data and formulas
Me 1. Starting in B2, fill in a number between 0 and 100 for each month of 2010,
2011, 2012, and 2013 (If you like you can fill in all years)
2. Type Total into A14
3. In B14 enter the following formula
=B2+B3+B4+B5+B6+B7+B8+B9+B10+B11+B12+B13

4. Hit enter when finished, the answers may vary if alternative numbers are
used.
5. Reselect B14
6. Using the auto fill, copy the formula into C14, D14, and E14.
7. Save the file as Sales.

Notice that in the example above, the cell references in the formula change “relative” to
their new column letter. This means when you copy the formulas to the other cells, you
are still adding the 12 cells above the cell where the formula is located. This is called
relative cell references. This is the normal case; you don’t have to do anything special
to achieve this result. A Copy followed by Paste will produce the same kind of change
in cell reference; that is relative cell references in formulas will be preserved. Drag &
Drop (Move) or Cut and Paste assumes you simply want to relocate the formulas
without changing them. That is, the formulas will remain unchanged and the relative
cell references will not be preserved.

Module 5
 Applied Computer: Spreadsheet

1.3. Absolute Cell Reference Allow changes A1

In some cases, there may be parts of the formula
that you will not want to change when copying or Do not change the Row A$1
auto filling formulas. To do this you need to tell
Excel which part of the cell reference should not Do not change the $A1
change (the row or the column). To do this, put a $ Column
in front of the part you do not want to change.
Do not change either $A$1
In our example of Sales, let’s assume that every (always the same cell)
year, the cost of running the business is the same.

Follow Calculate Cost

Me
1. Type Annual Cost in B16
2. In C16 we will enter the annual cost of 25
3. Type Profit in A17
4. In B17 enter =B14-C16
Without an absolute reference the auto fill will change both cells to the next cell in
the same direction the cell is filled.
5. Using the auto fill, to copy the
formula in B17 over to C17, D17,
and E17.
C17, D17, and E17 are not being
calculated correctly if you select them
you will notice they are using the correct
annual total but they are not subtracting
the annual cost in C16.
In this particular case we do not want
the column in the cell reference C16 to
change, so we put a $ in front of the C.
6. Correct B17 to =B14-$C16
7. Use the auto fill to copy the
correct formula in B17 over to
C17, D17, and E17.
8. Save your work.

2. Charts
Charting the data in your worksheet is not only fun, but it provides another dimension in
analyzing data. If you were tracking daily temperatures in a worksheet, it would be
difficult to see the trend or the fluctuations using just the numbers. However, if you turn
those numbers into bars on a chart, the picture becomes much clearer. The value of a
visual portrayal increases as the amount of data increases.

Module 5
 Applied Computer: Spreadsheet

Follow Create Chart for 2010 Sales

Me 1. Select the data for the chart by selecting A1 through B13.
2. Select a 2-D Line chart by clicking the Line drop down icon on the Insert tab
of the ribbon (Charts group).

2.1. Parts of a Chart

Each chart or graph will
have similar parts which
can be shown or hidden
and edited. The pictures
below show each of the
parts available for charts.
Chart Title – A name for
your chart, used to display
quick information on the
chart.
Data Series – A group of
related values, such as all

Module 5
 Applied Computer: Spreadsheet

the values in a single row or column in the spreadsheet.

Axis – A line that serves as a major reference for plotting data in a chart. In most charts
there will be two axes, the X-axis (horizontal) and Y-axis (Vertical). These should
always be labeled with both the name and unit being measured. Note: Pie charts do not
have axes.
Legend – A key that identifies additional information.
Note that once a chart has been created and each time a chart is selected, three new
tabs are shown on the ribbon under the heading “Chart Tools”. These new tabs –
Design, Layout, and Format – enable editing of the chart. If the chart is not selected,
the Chart Tools tabs do not show on the ribbon.
Each of the parts or elements of the chart can be added or changed from the ribbon
Layout tab. The most common items to include or change are labels and axes.

The ribbon Design tab can be used to reselect the data and to quickly change the layout
of the chart to another default. The color and effects can be changed through this tab
as well.

Follow Add Axis Title

Me 1. Select the Chart if
not already selected.
2. Once the chart is
selected new tabs
appear on the ribbon
under Chart Tools.
3. Select Layout 3 on
the ribbon Design tab
(Chart Layouts group)

Module 5
 Applied Computer: Spreadsheet

4. To add a vertical axis title, select Rotated Title from the drop down in the
ribbon layout tab (Labels group). See picture to the right for details.
5. Type Sales in USD hit enter to confirm.

2.2. Types of Charts

Choosing the correct chart for your data is critical to ensure the inserted chart adds value
and is not confusing or misleading. One good way to ensure you have chosen the correct
chart is to understand what question can be answered by the chosen chart.
2.2.1. Pie Charts
A pie chart looks like a circle (or a pie) cut up into segments. Pie
charts are used to show how the whole of something breaks down
into parts. Often when showing percentages a pie chart will be
used.
Some examples of questions that can be answered by pie charts
include:
-Which part was the greatest?
-Which part was the least?
In the example to the left we can quickly see that April and
February were similar and March was least. This can be important
if the expectation was that each month would be similar.

2.2.2. Line Graphs

Line graphs are typically used to show
trends or changes over time. Line
graphs show relationships between data.
Some examples of questions that can be
answered by line graphes include:
-Is this going up?
-Is this going down?
-Is there a high or low point?
Line graphs can be used to graph
different
things to see if a trend holds up. For example do ice cream sales go up when the
temperature goes up? This would mean graphing ice cream sales and
temperatures over time.
The graph to the right shows that sales are consistantly lower in January than
February. The graph also shows that March is has been a poor sales month.

Module 5
 Applied Computer: Spreadsheet

2.2.3. Bar or Column Graphs

Bar graphs allow you to compare amounts of things in
categories. Generally each category would be
independent of each other, so if one changed the
others would not change
Some examples of questions that can be answered by
a bar or column graph include:
-How much is something?
-Is this more or less than something?
-Is something a lot or a little more?
The graph to the left shows us that 2013 had the most sales while 2010, 2011, and
2012 were similar amounts.

2.2.4. Scatter Charts

Scatter charts, or scatter plots, can be
used when one variable is independent
and the other is either dependent or
independent. Scatter charts use
Cartesian coordinates to display values
for two variables.
Some examples of questions that can be
answered by a scatter chart include:
-How much was a given thing?
-Where any others similar?
The chart to the right shows us that in
February there was a large range between 2012 and 2013, the average fell evenly
between them. In May 2013 sales were up around $100 which was over double
the average and previous year.

3. Conditional Formatting
When working on a worksheet with thousands of rows of data. It would be extremely difficult
to see patterns and trends just from examining the raw information. Like charts and
sparklines, conditional formatting provides another way to visualize data and make
worksheets easier to understand.

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

Module 5
 Applied Computer: Spreadsheet

Follow Create conditional formatting rule:

Me 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 this 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.
Enter 4000 as value.
5. Select a formatting style from the drop-down menu. 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 sales
people reached the $4000 sales goal for each month.
Module 5
 Applied Computer: Spreadsheet

Note:
You can apply multiple conditional formatting rules to a cell range
or worksheet, allowing you to visualize different trends and patterns
in your data.

3.2. 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:

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

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

Module 5
 Applied Computer: Spreadsheet

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

Follow Using preset conditional formatting:

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

Module 5
 Applied Computer: Spreadsheet

Follow Removing conditional formatting:

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

Note:

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

especially useful if you've applied multiple rules to a worksheet.

4. Exercises – Now, it’s your turn to shine!

1.

Module 5
 Applied Computer: Spreadsheet

Based on the data in Sales, create two more charts that answer the questions below.
-Which month was the most profitable?
-Who has the most sales based on westbrook parker sales data?
Be sure to include titles in each of the new charts.

Save your worksheet, Sales_YourSurname.Name

2.
Open the practice workbook.
Click the Challenge worksheet tab in the bottom-left of the workbook.
Select cells B3:J17.
Assume that 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.
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.
Save your worksheet, ConditionalFormatting_YourSurname.Name

Module 5