SPREADSHEET Filtering & Charts
FILTERING
WHAT IS FILTERING?
This involves extracting data from a
section of a spreadsheet using specific
criteria.
TYPES OF FILTERING IN
A SPREADSHEET
Simple
Advanced
SIMPLE FILTER
The simple filter
uses menus and
preset options
located in the
spreadsheet to
make extractions.
ADVANCED FILTER
The advanced
filter allows
the user to
specify the
criteria for
extraction.
WHEN DOING ADVANCED
FILTER THE FOLLOWING
SHOULD BE CONSIDERED:
List Range
Criteria Range
Filtered List
LIST RANGE
The list range is table which has the
data you want to extract.
When highlighting the list range you must block the
headings as well.
CRITERIA RANGE
The criteria range has the headings from the
list range and the criteria for extraction under
the appropriate heading.
FILTERED LIST
The filtered list is the list with all the
data you wish to extract.
STEPS USED TO CREATE
THE CRITERIA RANGE:
Block the headings in the list range and click
copy
Paste the headings in an unoccupied area of
the spreadsheet, preferably below the list
range
Place the criterion or criteria for extraction
under the appropriate column heading
ADVANCED FILTERING
STEPS:
Block the list range
Click on the data ribbon and select advanced
Click in the criteria range row on the dialog
box displayed and block the criteria range
Select copy to another location and click in
the copy to row on the dialog box displayed.
Click in an unoccupied area in the
spreadsheet below the criteria range and click
ok.
CHARTS
DEFINITION
A chart is a graphical representation of
data that can be used to:
a) Show trends
b) Compare data
c) Emphasize mathematical or statistical figure
TYPES OF CHARTS
I. Column
II. Pie
III. Bar
IV. Line
V. Doughnut chart
There are others, you can research and look at
their suitability.
HOW TO GENERATE A
CHART?
1. Select the data for the chart
2. Click on the insert tab
3. Select the chart of your
choice from the chart section
HOW TO GENERATE A
CHART?
1. Charts can be created using adjacent or non-
adjacent cells.
2. When selecting non adjacent cells
a) Block the first set of cells,
b) Hold on to the ctrl key and select the next set of cells
PRACTICE ACTIVITY
Instructions: Enter the data below in your spreadsheet, start entering the data from A1
Students Results for Academic Year 2011-2012
Subject Term 1
Mathematics 67
English Language 78
Principles of Business 66
Principles of Accounts 89
Information Technology 57
Spanish 78
Office Administration 15
Generate a column chart to display the languages results for Term 1
STEPS
Block from a2 to b9.
Click on insert ribbon then select column, located in
the chart group.
Select the first chart option under the 2 or 3D
column headings.
Click in the title text box and type an appropriate
title.
CHART TOOLS
Chart tools are used to adjust the
appearance of the chart.
These adjustments include but are not
limited to:
a) Chart layout
b) Chart style
c) Etc.
PRACTICE ACTIVITY
Instructions: Enter the data below in your spreadsheet, start entering data from A1
Students Results for Academic Year 2019-2020
Yearly
Subject Term 1 Term 2 Average
Mathematics 67 45 56
English Language 78 77 77.5
Principles of Business 66 76 71
Principles of Accounts 89 58 73.5
Information
Technology 57 89 73
Spanish 78 67 72.5
Office Administration 15 44 29.5
1. Generate a column chart to display the results for Term 1 and Term 2. Generate a column chart to
display the yearly average for all subjects
STEPS
In order to block non-adjacent cells.
Click on the first set of cells, hold on
the ctrl key and block the other cells
before.
For this question:
Block from first cell to the last cell in the range (include
the headings).
Hold on to the CTRL key on your keyboard and second
range of cells before releasing.
Follow the steps required to generate a column chart.
