Chapter
Chapter
10
1010
10
Analyzing
Excel Data
You can get more out of Excel by
performing data analysis, which is the
application of tools and techniques to
organize, study, and reach conclusions
about a specific collection of
information.
In this chapter you learn how to sort and
filter a range, apply data validation rules
to a range, convert a range to a table,
create a data table, and summarize data
using subtotals.
12_577639-ch10.indd 17012_577639-ch10.indd 170 3/15/10 2:46 PM3/15/10 2:46 PM
Chapter
10
1010
Sort a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Filter a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Set Data Validation Rules . . . . . . . . . . . . . . . . . . . . . . . 176
Convert a Range to a Table . . . . . . . . . . . . . . . . . . . . . 178
Create a Data Table . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Summarize Data with Subtotals . . . . . . . . . . . . . . . . . . . 182
12_577639-ch10.indd 17112_577639-ch10.indd 171 3/15/10 2:46 PM3/15/10 2:46 PM
172
44 55

numerically from 0 to 9; a descending sort
arranges the values alphabetically from Z to A,
or numerically from 9 to 0.
Sort a
Range
12_577639-ch10.indd 17212_577639-ch10.indd 172 3/15/10 2:46 PM3/15/10 2:46 PM
173
Analyzing Excel Data
CHAPTER
10
77 88
99
00
•
Excel adds another sort level.
7
Click the Then by and then
click the field you want to use
for the sort level.
8
Click the Order and then
click a sort order for the field.
9
Repeat Steps 6 to 8 to add
more sort levels as needed.
0
Click OK.
•
Excel sorts the range.
Is there a faster way to sort a range?

to use as the filter.
1
Click inside the table.
2
Click the Data tab.
3
Click Filter ( ).
Filter a Range
You can analyze table data much faster by
filtering the data. Filtering a table means that
you configure a field so that you only view the
table records that you want to work with. One
way to do this is to use the AutoFilter feature,
which presents you with a list of check boxes
for each unique value in a field. You filter the
data by activating the check boxes for the
records you want to see.
Filter a
Range
12_577639-ch10.indd 17412_577639-ch10.indd 174 3/15/10 2:46 PM3/15/10 2:46 PM