Informatics
Spreadsheet calculator - II
Petr Suchánek
Informatics

Outline of the lecture
•Filters
Øautofilter
Øadvanced filter
•Groups
•Subtotals
•Pivot table
•Sensitivity analysis

Filtering data
•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.*
•In order for filtering to work correctly, your worksheet should include a header row, which is
used to identify the name of each column.*
•
*https://edu.gcfglobal.org/en/excel2016/filtering-data/1/

Filtering data
•Select the Data tab, then click the Filter command.*
•A drop-down arrow will appear in the header cell for each column.*
•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.*
•
*https://edu.gcfglobal.org/en/excel2016/filtering-data/1/

Filtering data
•Uncheck the box next to Select All to quickly deselect all data.*
•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.*
•
*https://edu.gcfglobal.org/en/excel2016/filtering-data/1/

Filtering data
•Filters are cumulative, which means you can apply multiple filters to help narrow down your
results.*
•
*https://edu.gcfglobal.org/en/excel2016/filtering-data/1/

Filtering data – advanced filter
•Compared to the basic AutoFilter tool, Advanced Filter works differently in a couple of important
ways.*
•Excel AutoFilter is a built-in capability that is applied in a single button click. Just hit the
Filter button on the ribbon, and your Excel filter is ready to go.*
•Advanced Filter cannot be applied automatically since it has no pre-defined setup, it requires
configuring the list range and criteria range manually.*
•
*https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/

Filtering data – advanced filter
•AutoFilter allows filtering data with a maximum of 2 criteria, and those conditions are specified
directly in the Custom AutoFilter dialog box.*
•Using Advanced Filter, you can find rows that meet multiple criteria in multiple columns, and the
advanced criteria need to be entered in a separate range on your worksheet.*
•Below you will find the detailed guidance on how to use Advanced Filter in Excel as well as some
useful examples of advanced filters for text and numeric values.*
•
*https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/

Filtering data – advanced filter
•In addition to manual data filtering Excel enables fully automated filtering based on data from
the specified range of cells. Before you can use the advanced filtering feature, you must set up a
criteria range. A criteria range is a designated range on a worksheet that conforms to certain
requirements.*
•The criteria range holds the information that Excel uses to filter the list. It must conform to
the following specifications:*
ØIt consists of at least two rows, and the first row must contain some or all field names from the
list. An exception to this is when you use computed criteria. Computed criteria can use an empty
header row.*
ØThe other rows consist of your filtering criteria.*
•
*https://www.officetooltips.com/excel_2016/tips/using_advanced_filtering.html

Filtering data – advanced filter
*https://www.excel-easy.com/examples/advanced-filter.html
•Create a Criteria range (blue border below for illustration only) above your data set. Use the
same column headers. Be sure there's at least one blank row between your Criteria range and data
set.*
•To display the sales in the USA and in Qtr 4, execute the following steps.*
•1. Enter the criteria shown below on the worksheet.*
•

Filtering data – advanced filter
*https://www.excel-easy.com/examples/advanced-filter.html
•2. Click any single cell inside the data set.*
•3. On the Data tab, in the Sort & Filter group, click Advanced.*
•4. Click in the Criteria range box and select the range A1:D2 (blue).*
•5. Click OK.*
•

Filtering data – advanced filter
*https://www.excel-easy.com/examples/advanced-filter.html
•To display the sales in the USA in Qtr 4 or in the UK in Qtr 1.*

Filtering data
•
•
•https://edu.gcfglobal.org/en/excel2016/filtering-data/1/
•https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/
•https://www.excel-easy.com/data-analysis/filter.html
•https://spreadsheeto.com/filters/
•https://www.youtube.com/watch?v=dD0QQsdgSr4
•https://www.officetooltips.com/excel_2016/tips/using_advanced_filtering.html
•
•

Filtering data – groups
•Worksheets with a lot of content can sometimes feel overwhelming and 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.*
•
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/1/

Filtering data – groups
•Select the rows or columns you want to group. In this example, we'll select columns B, C, and D.*
•Select the Data tab on the Ribbon, then click the Group command.*
•The selected rows or columns will be grouped. In our example, columns B, C, and D are grouped.*
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/1/

Filtering data – 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.*
•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.*
•
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/1/

Filtering data – subtotals
•
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/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.*
•Select the Data tab, then click the Subtotal command.*
•

Filtering data – subtotals
•
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/1/
•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.*
•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.*
•

Filtering data – subtotals
•
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/1/
•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.*
•

Filtering data – subtotals
•
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/1/
•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.*
•

Filtering data – subtotals
•
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/1/
•When you create subtotals, your worksheet it is divided into different levels.*
•You can switch between 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
between all three levels in our outline.*
•While this example contains only three levels, Excel can accommodate up to eight.*
•

Filtering data – subtotals
•
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/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.*
•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.*
•

Filtering data – subtotals
•
*https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/1/

Filtering data – groups and subtotals
•
•https://edu.gcfglobal.org/en/excel2016/groups-and-subtotals/1/
•https://www.officetooltips.com/excel_2016/tips/creating_subtotals.html
•https://support.office.com/en-us/article/why-can-t-i-add-subtotals-in-an-excel-table-excel-for-mac
-1df3b78d-8508-486b-95d8-d970dff1bb05
•https://support.office.com/en-us/article/insert-subtotals-in-a-list-of-data-in-a-worksheet-7881d25
6-b4fa-4f81-b71e-b0a3d4a52b3a
•https://www.ablebits.com/office-addins-log/2011/11/17/multiple-excel-subtotals/

Filtering data – pivot table
•
*https://www.techonthenet.com/excel/pivottbls/create2016.php
•Before we get started, we first want to show you the data for the pivot table. In this example,
the data is found on Sheet1.*
•

Filtering data – pivot table
•
*https://www.techonthenet.com/excel/pivottbls/create2016.php
•Highlight the cell where you'd like to create the pivot table. In this example, we've selected
cell A1 on Sheet2.*
•

Filtering data – pivot table
•
*https://www.techonthenet.com/excel/pivottbls/create2016.php
•Next, select the Insert tab from the toolbar at the top of the screen. In the Tables group, click
on the Tables button and select PivotTable from the popup menu.*
•A Create PivotTable window should appear. Select the range of data for the pivot table and click
on the OK button. In this example, we've chosen cells A1 to F16 in Sheet1 as indicated by
Sheet1!$A$1:$F$16.*
•

Filtering data – pivot table
•
*https://www.techonthenet.com/excel/pivottbls/create2016.php

Filtering data – pivot table
•
*https://www.techonthenet.com/excel/pivottbls/create2016.php
•Next, choose the fields to add to the report. In this example, we've selected the checkboxes next
to the Order ID and Quantity fields.*
•

Filtering data – pivot table
•
*https://www.techonthenet.com/excel/pivottbls/create2016.php
•Next in the Values section, click on the "Sum of Order ID" and drag it to the Rows section.*
•

Filtering data – pivot table
•
*https://www.techonthenet.com/excel/pivottbls/create2016.php
•Finally, we want the title in cell A1 to show as "Order ID" instead of "Row Labels". To do this,
select cell A1 and type Order ID.*
•Your pivot table should now display the total quantity for each Order ID as follows:*
•

Filtering data – pivot table
•
•https://www.techonthenet.com/excel/pivottbls/create2016.php
•https://support.office.com/en-us/article/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bf
e9-40a9-a8e9-f99134456576
•https://edu.gcfglobal.org/en/excel2016/intro-to-pivottables/1/
•https://www.groovypost.com/howto/create-pivot-tables-microsoft-excel-2016/
•https://www.excel-easy.com/data-analysis/pivot-tables.html
•

Filtering data – sensitivity analysis
•
*https://www.wallstreetmojo.com/sensitivity-analysis-in-excel/
•Sensitivity analysis in excel helps us study the uncertainty in the output of the model with the
changes in the input variables.*
•It primarily does stress testing of our modeled assumptions and leads to value-added insights.*
•In the context of DCF valuation, Sensitivity Analysis in excel is especially useful in finance for
modeling share price or valuation sensitivity to assumptions like growth rates or cost of capital.*
•

Filtering data – sensitivity analysis
•
•https://www.youtube.com/watch?v=N924D6tGOG8
•https://www.dummies.com/software/microsoft-office/excel/how-to-create-a-two-variable-data-table-in
-excel-2016/
•https://www.wallstreetprep.com/knowledge/financial-modeling-techniques-sensitivity-what-if-analysi
s-2/
•https://www.wallstreetmojo.com/sensitivity-analysis-in-excel/
•

The end