Official Business
STEM Teach
Utilizing Technology in
Teaching Mathematics
Module 2: Utilizing Excel in
Mathematics Classrooms
Official Business
Lesson 3: Advanced Excel Skills: Data
Analysis Tools and Using Excel for
Calculations
In this lesson, some advanced Excel features will be
discussed. This lesson will guide you on how to sort and
filter data, as well as create pivot tables. Creating
charts and customizing those charts will also be
explained. You will also be presented with statistical
functions like MEDIAN, MODE, and STDEV. You will also
utilize Analysis ToolPak in Excel.
Sorting, Filtering, and Creating Pivot Tables
Advanced Excel skills include powerful tools such
as Sort, Filter, and Pivot Tables for sophisticated data
analysis.
Sorting allows users to organize data in ascending
or descending order based on specific criteria,
enhancing data readability and analysis.
To sort the data, highlight the cells that you want to
sort. Under the Home ribbon, click Sort & Filter. You
Official Business
can sort from A to Z or Z to A. You can also use the
custom sort.
Filtering provides a way to display only the data
that meets certain conditions, enabling users to focus
on relevant information and perform detailed data
assessments.
To filter the data by column, highlight the column
header of the data set. Under the Home ribbon, click
Sort & Filter. Click Filter.
The cell should look something like this:
Official Business
If you click the lower left corner of this cell, more
options will pop up. You can select the option that you
need.
Pivot Tables are dynamic tables that summarize
and analyze large datasets by grouping and aggregating
data, offering insightful views and trends with the ability
to quickly reorganize and drill down into details.
Creating Charts
Creating charts in Excel is a powerful way to
convert raw data into their visual representation,
making it easier to detect patterns and trends.
Depending on the nature of the data, users can select
from a variety of chart types, such as bar, line, pie, and
Official Business
scatter plots. Once a chart is created, it can be
customized using the Chart Tools for design and
formatting, allowing for the adjustment of titles, colors,
and data labels. This visual representation not only
enhances the presentation of data but also aids in more
effective analysis and decision-making.
To create a chart, highlight the data set. On the
Insert ribbon, click the type of chart that is the most
applicable to the nature of your data.
Statistical Functions
Excel’s advanced statistical functions such as
MEDIAN, MODE, and STDEV provide deeper insights
into data distributions.
The MEDIAN function determines the middle value
of a dataset, offering a measure of central tendency
Official Business
that is less affected by outliers compared to the mean.
To use this function, input =MEDIAN([number 1],…).
The MODE function identifies the most frequently
occurring value, highlighting common trends within the
data. To use this function, input =MODE([number 1],…).
The STDEV function calculates the standard
deviation, which quantifies the amount of variation or
dispersion in the dataset, helping to understand the
spread and consistency of the data. These functions
are essential for detailed statistical analysis and data
interpretation in Excel.
To use this function for data gathered from the
entire population, input =STDEV.P([number 1],…).
To use this function for data gathered from the
sample only, input =STDEV.S([number 1],…).
Official Business
To do more advanced statistical analysis, you may
load the Analysis ToolPak in Excel. Here are the step-
by-step procedures on how to load it on Excel.
1. On the Excel Search Bar, type “Add-ins”
2. Click Add-ins. Click Analysis ToolPak.
Official Business
3. In the Add-Ins box, check the Analysis
ToolPak check box, and then click OK.
▪ If Analysis ToolPak is not listed in
the Add-Ins available box,
click Browse to locate it.
▪ If you are prompted that the Analysis
ToolPak is not currently installed on your
computer, click Yes to install it.
Official Business
If you successfully loaded the Analysis ToolPak, you
should see this on the Data ribbon.
You can utilize any of these statistical tools once
you click the Data Analysis.
You may watch the video uploaded in this folder to
see the demonstration on how these functions can be
used.