Advanced Excel Data Import and Filtering
Translated by
ScribdTranslationsAdvanced Excel Data Import and Filtering
Translated by
ScribdTranslationsAdvanced Excel Exercise
Introduction
In this exercise, you will learn to import data from a text file into a sheet.
it's useful to use Excel functions for processing large amounts of data.
information:
● simple filters
● complex filters
● subtotal calculation
● pivot table
The proposed exercises are based on a table of over 2500 rows, whose values are
stored in the text file called "dati_da_importare.txt".
The table contains information related to the students enrolled in a degree course over the years.
between 1988 and 1995.
The table consists of the following columns:
● academic year
● sesso (0 se di sesso maschile, 1 se di sesso femminile)
● this
● weight (espresso in Kg)
● height (expressed in cm)
● (1 if the student has obtained the scientific high school diploma, 0 otherwise)
● (1 if the student has obtained the classical diploma, 0 otherwise)
● diptec (1 if the student has graduated from a technical institute, 0 otherwise)
● dipalt (if the student has another type of diploma)
● compon (number of family unit members)
● occhiali (1 se lo studente porta gli occhiali, 0 altrimenti)
● smoke (1 if the student is a smoker, 0 otherwise)
Data import
Purpose: to import the data from the file dati_da_importare.txt
In teaching exercises, small tables are often used.
dimensions, containing only a few lines and whose data is entered by hand.
In real use, spreadsheets are used to handle large volumes of
data, which is imported from external sources (files, databases, ...). External sources
diverse can have different formats, which are not natively supported by
Excel. However, the vast majority of applications allow for
exporting and importing data in a standard format called CSV. CSV stands for
Comma Separated Values (comma-separated values) is a text file.
simple (only ASCII characters, without any particular formatting) containing values (both
text that numbers) separated by commas.
In this exercise, you will create a medium-sized table based on the data.
stored in CSV format in the file "dati_da_importare.txt".
● open a new (blank) spreadsheet
● selectData
● select the file dati_da_importare.txt (the exact location of the file in your
the filesystem depends on where you unzipped the archive)
● choose "delimited" and import all the data contained in the file
data_to_import.txt (including the first line), press next
● choose the comma as delimiter
● Select the sheet and the cell where to import, press ok
● result: the current spreadsheet shows the table with all the data that
they will be used for the subsequent exercises. The obtained table must have the
12 columns described in the introduction and 2760 rows (including the row
of the header)
Simple filters
Purpose: to select only male students enrolled in 1988
weighing 62 kg
The table obtained from the previous exercise contains a lot of data, difficult to
analyze. In particular, it is complicated to search within the table for only the few
fields that correspond to specific search criteria.
In this exercise, you will apply simple filters to the table, so that you can
easily carry out simple searches.
● select a cell within the data table
● selectDate -> Filter -> AutoFilter
● use the dropdown menus that appear in the first row of the table to
filter the data based on the various fields
○ in the dropdown menu of the year column select 1988
○ in the dropdown menu of the gender column select 0
○ in the dropdown menu of the weight column select 62
● result: only some of the rows contained in the table are displayed
those that satisfy all three constraints selected in the previous point,
corresponding to the following expression in Boolean logic:
((ANNO = 1988) AND (SESSO = 0) AND (PESO = 62))
The resulting table is as follows:
YEAR
1988 0 20.8 62 180 0 0 1 0 5 0 1
1988 0 20.6 62 176 0 0 1 0 5 0 0
● check the effects of other filter rules by changing the values in the menus
column header of the table
● remove all filter rules set before proceeding to the next
exercise selecting Data -> Filter -> Show All
Complex filters
Purpose: to select all males with a height of less than 160 cm and all the
females taller than 177 cm
Boolean logic function:
(( (gender = 0) AND (height < 160)) OR ((gender=1) AND (height > 180)))
Through the dropdown menus provided by the simple filters, it is not possible
to express logical filtering functions as described in the purpose of this
exercise. To solve this exercise, it is necessary to use complex filters.
In a complex filter, the filter rules are read from a table, where they are
insert all filtering criteria. All conditions entered on the same line of the
tables are considered in logical AND, while the conditions represented by
Different rows are considered in OR.
The boolean function of this exercise can be written as
(line_1 OR line_2)
where at line_1 we need to replace
((gender = 0) AND (height < 160))
and at line 2 we have to replace
(gender = 1) AND (height > 180)
● build a small table with filtering criteria ON TOP OF THE TABLE WITH THE DATA
(if you do not have enough space above the table, create it by inserting some)
empty lines
SEX HEIGHT
0 < 160
1 180
● you can check that this table describes the desired filter rules
the first line expresses the conditions of row_1, the second line expresses the
conditions of row_2, the two rows are in logical OR with each other
● select the first cell of the data table
● selectDate -> Filter -> Advanced Filter
● compare the mask that requires the entry of the necessary data to the
creation of the filter
● check that the List Range field contains the area of the table with the data
● in the Criteria Range field, enter the area of the table containing the criteria
filtering
● press OK
● result: only some of the rows contained in the table are displayed
those that satisfy the Boolean expression represented by
filtering criteria table.
The resulting table is as follows:
year
1989 0 41.1 61 155 0 0 1 0 5 1 0
1992 0 19.3 50 158 1 0 0 0 4 0 0
1994 1 25.1 73 182 0 1 0 0 4 0 1
● remove all filter rules set before proceeding to the next
exercise selecting Data -> Filter -> Show All
Subtotals
Purpose: to create the following table in a new spreadsheet called
"average heights", and calculate the missing data (marked by the dot)
interrogative
Year average height average height of males average height of females
1988 ? ? ?
1991 ? ? ?
1995 ? ? ?
To apply functions (in this example, the AVERAGE() function) only to some
rows of a table, you need to use the SUBTOTAL() function. This function
takes two parameters: an integer and a range of cells. The integer
represents the function to be used (9 indicates SUM, 3 indicates COUNTA, 1
indicates MEDIA, ...). The list of all available functions can be consulted using the
Excel guide. The second parameter represents the range of cells to which to apply.
the function.
The interesting feature of SUBTOTAL() is that the function indicated by the first
the parameter is applied exclusively to the cells that are visible, while all
the cells that are hidden (for example, due to not matching with the expressions
Set filter settings are ignored.
What exercise can be solved using the SUBTOTAL() function and
setting filter rules to display only the relevant cells (to
example: the average height of males in 1995 can be calculated with
SUBTOTAL() applying function 3 (AVERAGE) on all cells of the column of
heights and using simple filters to exclusively select males
(SESSION=0) of 1995).
● selectInsert ->Worksheet
● rename the new worksheet to "average heights"
● create the 'skeleton' of the table (manually write the first row)
first column
● return to the spreadsheet 'data_to_import'
● select a cell ABOVE THE TABLE WITH THE DATA
● write the formula =SUBTOTAL(1,F11:F2769) [replace the second
parameter the range of cells containing the height values of your
data table
● selectDate -> Filter -> AutoFilter
● the result of the formula is calculated only based on the displayed cells
○ To calculate the average height of females in 1988, select only
the rows with year 1988 and sex 1
● Acting on the filters, calculate all the necessary values and report the calculated data.
in the table of the sheet "average heights"
● result:
year average height average height males average female height
1988 168.739 175.759 163.868
1991 169 176.144 164.185
1995 170.301 177.343 164.656
Purpose: to create the following table in a spreadsheet called
number of enrolled
year number of registered members number of males number of females
1988 ? ? ?
1991 ? ? ?
1995 ? ? ?
● proceed in a similar manner to the previous exercise
● Hint: in the subtotal() function, use the counta function (3) instead.
of the sum function (9)
● result
year number of members number of males number of females
1988 476 281 195
1991 327 132 195
1995 236 105 131
pivot table
Goal: to create a pivot table in a new spreadsheet that summarizes
the table with the data and that allows easy visualization of how many
students held a classical and scientific diploma
dividing them by sex and by year of enrollment
Pivot tables are an extremely useful tool for aggregating large amounts
of data, and allow for a comprehensive view of information that
they would be difficult to interpret.
In general, a pivot table consists of three main parts: the rows area,
the area of the columns and the area of the data. In the area of the rows and the columns can
to set the fields to be used to divide the data, while in the area
The data fields to be aggregated must be entered.
● select the first cell of the table with the data
● selectData -> PivotTable and Pivot Chart Reports
● select the voices 'Microsoft Office Excel list or database' and 'Pivot table'
press Next
● check that the Range field contains the area of the table with the data,
press Next
● selectLayout (button at the bottom left)
● drag YEAR into the rows area
● drag SEX into the columns area
● drag DIPSCI and DIPCLA into the data area (DATA)
● press Next
● select the "NewWorksheet" option and press Finish
● result: a pivot table similar to the one shown in the following image
● you can modify the appearance of the table by using the drop-down menus and you can
modify the structure of the pivot table using the PivotTable window
Field List
○ add the DIPTEC data to the data area of the pivot table for
also display the data related to the students graduated from the technical institute