Used Data Tools

Task: Used Data Tools		No	C5
No	Steps	Yes	No
C5.1 Data Validation List
01	Open spreadsheet package
02	Create or open exiting Workbook
03	Select cell range
04	Data àdata validation
05	Click setting tab
06	select list in allow dropdown menu
07	Type list in the source area
08	Click Ok
C5.2 Data Validation
01	Open spreadsheet package
02	Create or open exiting Workbook
03	Select cell range
04	Data àdata validation
05	Click setting tab
06	select whole number in allow dropdown menu
07	Select condition in data dropdown menu
08	Type value
09	Click input message tab
10	Type title and input message
11	Click error alert tab
12	Select style (Stop/Warning/information)
13	Type title and error messagea
C5.3 Consolidate data
01	Open spreadsheet package
02	Create or open exiting Workbook
03	Select the cell in the destination sheet where the consolidate data should be appeared
04	Type formula
05	Ok
C5.4 Subtotal
01	Open spreadsheet package
02	Create or open exiting Workbook
03	Select data range
04	Sort suitable column
05	Apply subtotal command and give output

Grouping Cells Using the Subtotal Command

Grouping is a really useful Excel feature that gives you control over how the information is displayed. You must sort before you can group. In this section we will learn how to create groups using the Subtotal command.

To Create Groups with Subtotals:

1.       Select any cell with information in it.

2.       Click the Subtotal command. The information in your spreadsheet is automatically selected and the Subtotal dialog box appears.

3.       Decide how you want things grouped. In this example, we will organize by Category.

4.       Select a function. In this example, we will leave the SUM function selected.

5.       Select the column you want the Subtotal to appear. In this example, Total Cost is selected by default.

6.       Click OK.

The selected cells are organized into groups with subtotals.

To Collapse or Display the Group:

1.       Click the black minus sign, which is the hide detail icon, to collapse the group.

2.       Click the black plus sign, which is the show detail icon, to expand the group.

3.       Use the Show Details and Hide Details commands in the Outline group to collapse and display the group, as well.

To Ungroup Select Cells:

1.       Select the cells you want to remove from the group.

2.       Click the Ungroup command.

3.       Select Ungroup from the list. A dialog box will appear.

4.       Click OK.

To Ungroup the Entire Worksheet:

1.       Select all the cells with grouping.

2.       Click Clear Outline from the menu.