Transcript Class 07 Excel CA202.ppt

CA202
Spreadsheet Application
Reordering and
Summarizing Data
Lecture # 7
1
Objectives
✔ Sort a data list
✔ Calculate Subtotal
✔ Organize data into levels
2
Sorting a Data List
• In this lesson you’ll learn how to sort your
data using one or more criteria, calculate
subtotals, and organize your data into
levels.
3
Sorting a Data List
• The data you entered might not be the
best order to answer your questions, you
might need to sort the data
• Once you have sorted your data into the
desired order, Excel lets you find partial
totals, or subtotals, for groups of cells
within a given range
4
Sorting a Data List
• You rearrange the worksheet rows based on the
contents of cells in a particular column.
• First step is to identify the column
• Then clicking the Sort Descending
button. Clicking the Sort Descending button put
the row with the highest value at the top
• If you wanted to sort the rows in the opposite
order, from the lowest sales to the highest, use
Sort Ascending
5
Sorting a Data List
• The Sort Ascending and Sort Descending
toolbar buttons let you sort rows in a worksheet
quickly
• You can sort rows in a worksheet by the
contents of more than one column through the
Sort dialog box
• You can pick up to three columns to use as sort
criteria and choose whether to sort the rows in
ascending or descending order
6
Sorting a Data List
• Sorting a list of values in alphabetical order would yield
incorrect results e.g. sorting months of the year, April is
the first and September the last
• Excel recognizes a number of special lists, such as days
of the week and months of the year.
• You can create your own list of values using the tools on
the Custom Lists tab page of the Options dialog box,
and use them in the sorting
• Important: If you sort using a custom list, the custom list
must be the primary sorting criterion
7
Organizing Data into Levels
• Calculate Subtotals
– After you have sorted the
rows in an Excel worksheet
you can have Excel
calculate subtotals, or
totals for a portion of the
data
– You can sort the products
by category, select all of
the cells that contain data,
and then open the Subtotal
dialog box
– Note: Week, Day,
Weekday, & Sale are
columns
8
Organizing Data into Levels
• The outline section at the
left of your worksheet
holds controls you can
use to hide or display
groups of rows in your
worksheet
• Level 1: The grand total
• Level 2: Group subtotal
• Level 3: Individual rows
• Tip: If you want to
remove all subtotals from
a worksheet, open the
Subtotal dialog box and
click the Remove All
Hide
button.
Details
Level
buttons
Show
Details
9
Chapter 7 Key Points
1.
2.
3.
4.
5.
You can rearrange the data in a worksheet quickly by
clicking either the Sort Ascending or Sort Descending
toolbar button
Don’t forget that you can sort the rows in a worksheet
using orders other than alphabetical or numerical. For
example, you can sort a series of days based on their
order in the week.
If none of the existing custom sort orders (days,
weekdays, and so on) meet your needs, you can create
your own custom sort order.
You can divide the data in your worksheet into levels, and
find a subtotal for each level.
Creating subtotals lets you show or hide groups of data in
your worksheets.
10