Unit-2.pptUnit-2.pptUnit-2.pptUnit-2.ppt

fff

1. Data Validation Data Validation
Exercise 4: Create Drop-down lists to populate the below Exercise 4: Create Drop-down lists to populate the below
fieldsfields

2. Naming Cells Naming Cells
- D- Descriptions to represent cells, ranges of cells,
formulas, or constant values

3. Naming CellsNaming Cells
To name cells in our previous example:To name cells in our previous example:
1. Highlight cells F2 to F9 1. Highlight cells F2 to F9
2. In the name box type Student2. In the name box type Student
3. Press "Enter"3. Press "Enter"
4. Repeat steps for columns G and H (name column G 4. Repeat steps for columns G and H (name column G
as Subject and column H as Grade)as Subject and column H as Grade)

4. Naming Cells Naming Cells
Assigned name of highlighted
data in column F

5. Data ValidationData Validation
Exercise 4:Exercise 4:
1. Highlight the whole column A1. Highlight the whole column A

6. Data ValidationData Validation
Exercise 4:Exercise 4:
2. Click on 2. Click on DataData from the Ribbon bar from the Ribbon bar
3. From Data Tools tab, click 3. From Data Tools tab, click Data ValidationData Validation

7. Data ValidationData Validation
Exercise 4:Exercise 4:
4. 4. Make sure the Settings tab strip is selected
5. 5. Click the black down arrow just to the right of “Allow:
Any Value”
6. A drop down list appears

8. Data ValidationData Validation
Exercise 4:Exercise 4:
7. 7. Choose List

9. Data ValidationData Validation
Exercise 4:Exercise 4:
8. From the Source box, click the icon with a red arrow
9. Press F3 and choose Student, click ok, then click the
icon with a red arrow
10. Click ok

10. Data ValidationData Validation
Exercise 4:Exercise 4:
11. Column A will have drop down list

11. Data ValidationData Validation
Exercise 4:Exercise 4:The resulting summary should look like thisThe resulting summary should look like this

12. Summarizing and Grouping Multiples Sets of DataSummarizing and Grouping Multiples Sets of Data
The following rules enable the consolidation of Lists
using the Consolidate command:
1. Structure of the Lists must be identical
2. Headings of all rows and the leftmost columns in the
Lists must contain the same topic

13. Summarizing and Grouping Multiples Sets of DataSummarizing and Grouping Multiples Sets of Data
The following rules enable the consolidation of Lists
using the Consolidate command:
3. Number of columns and the number of rows do not
have to be identical; nor does the internal order of the
text
4. Lists must have a single row for labels, and a single
column for labels

14. Summarizing and Grouping Multiples Sets of DataSummarizing and Grouping Multiples Sets of Data
The following rules enable the consolidation of Lists
using the Consolidate command:
5. Cells in the Lists data range must contain only numeric
data

6. Excel consolidates data by identifying corresponding
text crossed between the header row and the leftmost
column

15. Summarizing and Grouping Multiples Sets of DataSummarizing and Grouping Multiples Sets of Data
Exercise 5:Exercise 5:Consolidate the following dataConsolidate the following data

16. Summarizing and Grouping Multiples Sets of Data Summarizing and Grouping Multiples Sets of Data
Exercise 5:Exercise 5:
1. 1. Select a cell within List - press Ctrl+A to select the List, and
then press Ctrl+F3 to define a Name to List1

17. Summarizing and Grouping Multiples Sets of Data Summarizing and Grouping Multiples Sets of Data
Exercise 5:Exercise 5:
2. 2. Repeat step 1 and define a Name for Lists 2 and 3
3. Select cell A1 in a different sheet of the workbook, and
select Data -> Consolidate (in Data Tools Group)
4. In the Reference box, press F3
5. In the Paste Name dialog box, select List1, click Ok, and
then click Add to add List1 to All references box

18. Summarizing and Grouping Multiples Sets of Data Summarizing and Grouping Multiples Sets of Data
Exercise 5:Exercise 5:
6. 6. Repeat steps 4 and 5, and add Lists 2 and 3 to "All
references" box
7. In Use Labels in, select the Top row and Left column
checkboxes, and the click Ok

19. Summarizing and Grouping Multiples Sets of Data Summarizing and Grouping Multiples Sets of Data
Exercise 5: Exercise 5: You should see this resultYou should see this result