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

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