Create a list from your data
Overview: Lists
In Excel 2004, the List Manager creates a frame around your
data that helps you distinguish the data from the rest of the
worksheet. You can use the List Manager to search, analyze,
and sort the data faster and more effectively.
The List tab in this workbook contains a Vehicle Repair
Record as an example of using the List Manager. This list
includes pop-up menus, conditional formatting, calculated
columns, and a total row.
To learn how we created the Vehicle Repair Record, click the
Instructions tab. You might want to print the instructions to
look at while you're examining the Vehicle Repair Record.
For more detailed information about how to use any of the
features, look in Help.
Vehicle Repair Record
Toyota Tacoma
Year Enter Make/Model Here to
Click Pop-up menu
options
see
AutoFilter include
This worksheet can help you track vehicle maintenance Vehicle ID No. (VIN)
options. conditional
costs. Enter values in the white boxes. Results will be
formatting.
shown in the yellow boxes. License No.
Phone Covered By
Date Description of Work Mileage Shop Name Labor Cost Parts Cost Total Cost
Number Warranty
06/12/99 Replace fan belt 3,294.5 Shop 1 555-555-1212 $0.00 $0.00 Yes $0.00
09/14/99 Oil & lube 6,759.8 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
12/20/99 Oil & lube 9,901.0 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
02/21/00 Oil & lube 13,024.0 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
04/30/00 Oil & lube 16,854.2 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
07/02/00 20K mile maintenance 20,581.3 Shop 2 555-555-6987 $65.00 $16.00 No $81.00
10/12/00 Oil & lube 25,234.1 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
Click a cell
01/15/01 Oil & lube 29,010.9 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
in this
04/01/01 Replace water pump 32,500.8 Shop 1 and
column 555-555-1212 $0.00 $0.00 Yes $0.00
07/04/01 Oil & lube 36,124.4 Shop 2 from
select 555-555-6987 $32.50 $16.00 No $48.50
11/14/01 40K mile maintenance 39,225.7 the pop-up
Shop 2 555-555-6987 $92.50 $16.00 No $108.50
02/01/02 Oil & lube 43,100.6 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
06/01/02 Replace windshield 47,322.0 Shop 3 555-555-1234 $195.00 $224.00 No $419.00
10/22/02 Oil & lube 51,002.4 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
01/31/03 Oil & lube 54,232.5 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
04/17/03 Oil & lube 58,004.0 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
08/20/03 60K mile maintenance 61,544.1 Shop 2 555-555-6987 $92.50 $16.00 No $108.50
11/01/03 Oil & lube 65,113.1 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
11/01/03 Oil & lube 65,113.1 Shop 2 555-555-6987 $32.50 $16.00 No $48.50
Total number of repairs: 19 Average Cost: $45.66 $25.26 Total: $1,347.50
List sample Page 3
List instructions
Use the Excel List Wizard to create a list. Using lists, you can insert or
delete rows of data without affecting adjacent data on your worksheet.
Once you specify a range of cells as a list, you can click the AutoFilter
arrow that appears next to each column to hide rows in the list.
In this sample, we created a list for a Vehicle Repair Record that an
organization might use to track vehicle maintenance labor and costs.
We included pop-up menus with conditional formatting and calculated
totals. We created a banner for the title and formatted the list to add
visual interest.
Create the list
1. On the Insert menu, click List to display the List Wizard.
2. In Step 1 of the wizard, click None to create a blank list.
Specify a new worksheet for the list and click Next.
3. Add columns to the list. For each column, type a name and
select a data type from the list. Then click Add. The data
type, such as text or currency, determines how cells in the
list are formatted and sorted.
We added the following columns:
4. Click Finish. The list appears on the worksheet, including
column headings. A heavy gray border outlines the list.
List sample Page 4
Create a pop-up menu
With pop-up menus, people recording information in a field can select
an entry from a list instead of typing the information. Create a pop-up
menu by using an Excel feature called data validation. With data
validation, you define the entry choices for a cell. In the Shop Name
column, the choices are Shop 1, Shop 2, or Shop 3. In the Covered By
Warranty column, the choices are Yes or No.
1. In the list, double-click the column heading in the column
where you want to use a pop-up menu.
2. For Data type, click List.
3. In the Source box, type the entries you want to appear in
the menu, separated by commas–for example, Shop 1, Shop
4. Click OK.
Now, when you click a cell in the Shop Name column, a pop-up menu
control appears beside it. When the control is clicked, a list of possible
values is displayed.
Tip: For more complex menus, you might want to store the pop-up
entries on a worksheet and name the range of cells for easy reference.
For more information, search for “defined names” in Help.
Apply conditional formatting
We applied conditional formatting to the cells in the Covered By
Warranty column so that when users select No from the pop-up menu,
the text is formatted in red.
• To apply conditional formatting, select the cells you want to
format. On the Format menu, click Conditional Formatting.
Set the condition, such as Cell Value Is then equal to then
=”No”. Click Format, and choose options on the Font and
Patterns tabs.
List sample Page 5
Sum numbers
In the Vehicle Repair Record, the Labor Cost is added to the Parts Cost
and placed in the Total Cost column. A total repair cost is included at
the bottom of the column.
• To insert a simple sum, as in the totals for each row, click
the cell under the column heading. In the formula bar, type
an equal sign (=) followed by the cell references containing
the amounts to be summed. For example, =G8+H8 would
add the amounts entered in G8 and H8. Then drag the fill
handle in the lower-right corner of the cell down to copy the
formula into all the cells in the column.
• To insert a sum involving a range of cells, as in the total
repair costs, use the SUM function. Click the cell under the
column heading. In the formula bar, type an equal sign (=)
followed by the function name and arguments. For example,
=SUM(J8:J27) would add the amounts entered in the cells G8
through H8. When you enter the function in the formula bar,
the correct syntax for entering the function appears in a
ScreenTip. You can click the function name in the ScreenTip
to read an explanation of the function.
Tip: If you filter the list to show, for example, only the oil and lube
entries, the totals will update automatically to show the totals for only
the items displayed.
Apply formatting
We created the banner that appears at the top of the page by placing
rounded and square shapes on top of one another, using different
borders and fill colors. The logo and snowflake are inserted from
picture files.
• On the Formatting Palette, under Add Objects, click
AutoShapes, and then click the shape you want. Click your
worksheet to create the shape in the size you want. For
example, we used a rounded rectangle to create the banner
outline.
(continued)
List sample Page 6
(Apply formatting, continued)
Once you’ve created a shape, you can do the following:
• To create an outline for a shape, such as the blue border
we’ve included for the rounded rectangle, click line options
under Colors, Weights, and Fills on the Formatting Palette.
To add color to the shape, such as the solid blue in the box
displaying the list title, click a fill color.
• To enter text in a shape, with the shape selected type the
text. Select the text and choose options under Font on the
Formatting Palette. For example, we used white text for the
list title to set it off from the blue background.
• To insert a picture, such as the Alpine logo, on the Insert
menu, point to Picture, and click From File. Once the
picture appears in the spreadsheet, you can drag selection
handles to size it and drag a border to move it.
After arranging the elements to create the effect you want, you might
want to group them so that you can move them as a single object.
• To group objects, select the objects (hold down the SHIFT
key to select multiple objects), and click Group under Size,
Rotation, and Ordering on the Formatting Palette.
We inserted the text you see between the banner and the list by
merging cells, and then typing and formatting the text.
• To merge cells across several columns, select the cells you
want to merge. On the Formatting Palette, under
Alignment and Spacing, select the Merge cells check box.
We also applied the following formatting to the Vehicle Repair Record:
• We added a color to the row containing the column headings
and to the Total Cost column by selecting the row or column
and then clicking Shading Color under Borders and
Shading on the Formatting Palette.
• We drew the line color of the column borders and the dotted
lines between the rows by hand. Under Borders and
Shading on the Formatting Palette, click Draw by hand,
and then select the line color and line style in the toolbar.