Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Excel Data Filtering and Sorting: A Step-by-Step Guide - Prof. Peter D. Casey, Study notes of Computer Science

A detailed guide on how to use excel's data filtering and sorting features to analyze and manipulate data from a table. The instructions cover importing data from a web query, cleaning the table, applying filters using various criteria, calculating new fields, and sorting records. The document also includes exercises to practice these skills.

Typology: Study notes

Pre 2010

Uploaded on 08/19/2009

koofers-user-hiz-1
koofers-user-hiz-1 🇺🇸

10 documents

1 / 1

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Open Excel and save the workbook as Filter Fun.
Use the Data menu to do a Web Query. Enter the address: http://www.geohive.com/global/world.php
Select the first table you find on the page and import it into A1 on Sheet1. Rename Sheet1 to Data Table.
Delete the last few rows of the table that do not hold country data (world population and notes). Scan through
the data to check for other data errors.
Change the last field header to 2005 Population. Auto size the width of each column of data and set the
headers to Bold.
Sort the records by the population field descending. Note how the default sort assumes a header row with
field names and it does not include it in the sort.
With cell A1 the current cell, use the Data menu, Filter, Auto Filter option. Notice the small ‘filter’ arrows at
the top of each column. These arrows indicate you are ready to filter your data.
Try a filter on the country field to show only those countries that begin with the letter ‘A’. Click the filter
arrow, choose the “Custom” option, using the drop down menu, select the “Begins with” option, enter an ‘A’
in the text box and click OK. (15 countries?) Notice how the row numbers still show the original numbers;
the other rows are simply hidden.
How many country names start with X? How many start with U? How many countries have only four letters
in their name? (Note the use of * and ? in the “Custom filter dialog box.) How many countries have a name
that starts with A and ends with A? Can you determine how many start with A, end with A, and have exactly
seven letters? Can you determine how many contain “an” anywhere in the name and start with an F?
To reset the filter on a field simply select the option (All) from the field filter menu. Reset the country field to
all.
Add a new field name to the right of the Population field. Name the field Density. To calculate the data for
Density divide Population by Area. Put that formula in the first record Density field. (What happens if you
double click the fill handle?)
You must inform Excel of the new field. Use the Data menu, Filter option to turn off the Auto Filter. Now
use the same menu to turn Auto Filter back on. Did you get an arrow on your Density field? Sort your data by
the density field. What country has the highest density? (Ignore the error for Vatican City).
Filter your data to show only those countries with a population greater than or equal to 80,000,000 people. Is
your sort order on density still active?
Copy the data that is now showing and paste the data into A1 of Sheet2. Rename Sheet2 to Large Countries.
Auto size the column widths.
Go back to your Data Table sheet and turn off all filters you may have set. (Do you notice anything different
about any of your filter arrows before you turn off the filters you set?)
Filter your data to select only small countries, countries with 1,000 or fewer square kilometers. Sort your data
on country area, ascending. Now filter on countries with fewer than 100 square kilometers.
Copy and paste the data table to Sheet3 into cell A1. Rename the sheet Small Countries and auto size all the
columns. Delete the record in your data containing the Holy See.
Reset your Data Table by turning off the auto filter option and sorting the data by Country name ascending.
In Lab Assignment-Data Filter and Sort
Submit to Blackboard when complete.
Create a text box on the sheet for your answers.

Partial preview of the text

Download Excel Data Filtering and Sorting: A Step-by-Step Guide - Prof. Peter D. Casey and more Study notes Computer Science in PDF only on Docsity!

 Open Excel and save the workbook as Filter Fun.

 Use the Data menu to do a Web Query. Enter the address:^ http://www.geohive.com/global/world.php

 Select the first table you find on the page and import it into A1 on Sheet1. Rename Sheet1 to^ Data Table.

 Delete the last few rows of the table that do not hold country data (world population and notes). Scan through

the data to check for other data errors.

 Change the last field header to^ 2005 Population. Auto size the width of each column of data and set the

headers to Bold.

 Sort the records by the population field descending. Note how the default sort assumes a header row with

field names and it does not include it in the sort.

 With cell^ A1^ the current cell, use the Data menu, Filter, Auto Filter option. Notice the small ‘filter’ arrows at

the top of each column. These arrows indicate you are ready to filter your data.

 Try a filter on the country field to show only those countries that begin with the letter ‘A’. Click the filter

arrow, choose the “Custom” option, using the drop down menu, select the “Begins with” option, enter an ‘A’ in the text box and click OK. ( 15 countries? ) Notice how the row numbers still show the original numbers; the other rows are simply hidden.

 How many country names start with X? How many start with U? How many countries have only four letters

in their name? (Note the use of * and? in the “Custom filter dialog box.) How many countries have a name that starts with A and ends with A? Can you determine how many start with A, end with A, and have exactly seven letters? Can you determine how many contain “ an ” anywhere in the name and start with an F?

 To reset the filter on a field simply select the option (All) from the field filter menu. Reset the country field to

all.

 Add a new field name to the right of the Population field. Name the field^ Density. To calculate the data for

Density divide Population by Area. Put that formula in the first record Density field. (What happens if you double click the fill handle?)

 You must inform Excel of the new field. Use the Data menu, Filter option to turn off the Auto Filter. Now

use the same menu to turn Auto Filter back on. Did you get an arrow on your Density field? Sort your data by the density field. What country has the highest density? (Ignore the error for Vatican City).

 Filter your data to show only those countries with a population greater than or equal to 80,000,000 people. Is

your sort order on density still active?

 Copy the data that is now showing and paste the data into A1 of Sheet2. Rename Sheet2 to^ Large Countries.

Auto size the column widths.

 Go back to your Data Table sheet and turn off all filters you may have set. (Do you notice anything different

about any of your filter arrows before you turn off the filters you set?)

 Filter your data to select only small countries, countries with 1,000 or fewer square kilometers. Sort your data

on country area, ascending. Now filter on countries with fewer than 100 square kilometers.

 Copy and paste the data table to Sheet3 into cell A1. Rename the sheet^ Small Countries^ and auto size all the

columns. Delete the record in your data containing the Holy See.

 Reset your^ Data Table^ by turning off the auto filter option and sorting the data by Country name ascending.

In Lab Assignment-Data Filter and Sort

Submit to Blackboard when complete.

Create a text box on the sheet for your answers.