FILTERING
Sometimes you want to examine only particular records from a large collection of
data. For that, you can use Excel’s Filter tool. On the toolbar, go to the “Data" tab,
then click "Filter”. Small buttons will appear at the top of each column:
Suppose we wish to see only the records from the Territory of Lazio. Click on the
button on the Territery column, uncheck the "Select All" box, and then choose Lazio
from the list, like this:

This is the result:
Notice that you now are seeing only rows 36, 44, 78, 80 and 104. The rest are still
there, but hidden.
More complicated filters are possible. For instance, suppose you wish to see only
records in which “Burglaries” is greater than or equal to 5,000 AND car thefts is less
than 2,000. You start by filtering Burglaries like this:
then this...

Do the same for Car Thefts, and you get this:
FUNCTIONS
Excel has many built-in functions useful for performing math calculations and
working with dates and text. For instance, assume that we wish to calculate the total
number of murders in all the provinces. To do this, we would go to the bottom of
Column D, skip a row, and then enter this formula in Cell D106: =SUM(D2:D104).
The equals sign (=) is necessary for all functions. The colon (:) means “all the
numbers from Cell D2 to Cell D104”. After you hit Enter, the result is this:
(The reason for skipping a row is to separate the sum from the main table so that the
table can be sorted without pulling the sum into the table during the sorting
operation. This way the sum will stay at the bottom of the column.

Often you will want to do a calculation on each row of your data table. For instance,
you might want to calculate the auto theft rate (the number of cars stolen per
100,000 population), which would let you compare the auto theft problem in cities
of different sizes.
To do this, we would create a new variable called “Car Theft Rate per 100k” in
Column J, the first empty column. Then, in Cell J2, we would enter this formula:
=(G2/C2)*100000.
This divides the stolen cars by the population, then multiplies
the result by 100,000.
(Notice that there are no spaces and no thousands separators
used in the formula.) Here is the result:
You can format your numbers using various choices in this box under the "Home"
tab:
It would be very tedious to repeat writing that calculation in each of 103 rows of
data. Happily, Excel has a way to rapidly copy a formula down a column of cells. To
do that, you careful move the cursor (normally a big fat white cross) to the dot on
the bottom right corner of the cell containing the formula. When it is in the right
spot, the cursor will change to a small black cross. At that point, you can double-click
and the formula will copy down the column until it reaches a blank cell in the
column to the left.

This would be the result:

#### You've reached the end of your free preview.

Want to read all 14 pages?

- Spring '18
- Dr. Moez