Outline a list of data in a worksheet

Outline a list of data in a worksheet
You have a list of data that you want to group and summarize, you can create an outline of up to eight levels, one for each group. Each inner level, represented by a higher number in outline symbol displays detail data for the preceding outer level, represented by a lower number in the outline symbols. Use an outline to quickly display summary rows or columns, or to reveal the detail data for each group. You can create an outline of rows (as shown in the example below), an outline of columns, or an outline of both rows and columns.

An outline row of cells data grouped by geographical regions and months with several summary and detail rows displayed.

1. To display rows for a level, click the appropriate outline symbols.
2. Level 1 contains the total cells for all detail rows.
3. Level 2 contains the total cells for each month in each region.
4. Level 3 contains the total rows (only detail rows 11 through 13 are currently visible).
5. To expand or collapse data in your outline, click the + and - outline symbols.


Create an outline of rows
1. Make sure that each column has a label in the first row, contains similar facts in each column, and that the range has no blank rows or columns
2. Select a cell in the range.
3. Sort the columns that from the groups.
For more information on sorting, see sort data in a range or table.
4. Insert summary rows.
To outline data by rows, you must have summary rows that that contain formulas that reference cells in each of the detail rows for that group. Do one of the following:
Insert summary rows by using the Subtotal command
· Use the Subtotal command, which inserts the SUBTOTAL function immediately below or above each group of detail rows and automatically creates the outline for you. For information, see insert subtotals in a list of data in a worksheet.

Insert your own summary rows

Insert your own summary rows
· Insert your own summary rows with formulas immediately below or above each group of detail rows.
5. Specify whether the location of the summary row is below or above the detail rows.
How to specify the summary row location.
1. On the Data tab, in the Outline group, click the outline Dialog Box Launcher.
2. To specify a summary row above the details row, clear the Summary rows below detail check box.
To specify a summary row below the details row, select the Summary rows below detail check box.
Outline the data. Do one of the following:
Outline the data automatically.
If necessary, select a cell in the range.
1. On the Data tab, in the Outline group, click the arrow next to Group, and then click Auto Outline.

Important If you ungroup an outline while the detail data is hidden, the detail rows may remain hidden. To display the data, drag across the visible row numbers adjacent to the hidden rows. On the Home tab, in the cells group, click Format, point to Hide & Unhide, and then click Unhide Rows.
Prevent invalid data entry in a worksheet
In many worksheets that you create, users will enter data to get the desired calculations and results. Ensuring valid data entry is an important task. You may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered. Providing immediate help to instruct users and clear messages when invalid data is entered is also essential to make the data entry experience go smoothly. Once you decide what validation you want to use on a worksheet, you can set up the validation by doing the following:
1. Select one or more cells to validate.
2. On the Data tab, in the Date Tools group, click Data Validation.










Tab Data Validation dialog box is displayed.
3. Click the Settings tab.
4. To specify the type of validation that you want to, do one of following:
1. In the Allow box, select List.
2. Click the Source box and then type the list values separated by the Microsoft Windows list separator character (commas by default).
For example,
· To limit entry to a question, such as "Do you have children?" to two choices, type Yes, No.
· To limit a vendor's quality regulation to three rating, type Low, Average, High.
You can also create a list of values from a range of cells, For more information, see Create a drop-down list from a range of cells.
NOTE: The width of the drop-down list is determined by the width of the cell that has the data validation. you may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.
3. Make sure that the In-call dropdown check box is selected.
Allow a whole number within limits.
4. In the Allow box, select Whole Number.
5. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
6. Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.
For example, to set a minimum limit of deductions to two times the number of children in cell F1, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.
7. In the Allow box, select Dec

1 Filtering list

Using AutoFilter to filter data is a quick and easy way to find and work with a subset of data in a range of cells or table column. Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed. After you filter data, you can copy,
find, edit, format, chart, and print the subset of filtered data without rearranging or moving it.

Filter Text
1. Select a range of cells containing alphanumeric data.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Click the arrow in the column header.
Do one of the following:
Select from a list of text values
· In the list of text values, select or clear one or more text values to filter by.
The list of text values can be up to 10,000. If the list is large, clear (Select All) at the top.
Create criteria
1. Point to Text Filters and then click one of the comparison operator (=Equal to, > Greater than,<>=Greater than or equal to, <= Less than or equal to, and <> Not equal to.)commands, or click Custom Filter. For example, to filter by text that begins with a specific characters, select Begins With, or to filter by text that has specific characters anywhere in the text, select Contains.
2. In the Custom AutoFilter dialog box, in the box on the right, enter text or select the text value from the list. For example, to filter by text that begins with the letter "J", enter J, or to filter by text that has "bell" anywhere in the text, enter bell.

To find

If you need to find text that shares some characters but not others, use a wildcard character.

Use
To find
? (question mark)
Any single character
For example,sm?th finds "smith" and "Smyth"
* (asterisk)
Any number of characters
For example,*east finds "Northeast" and "Southeast"

Filter for top or bottom numbers
1. Select a range of cells containing numeric data.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Click the arrow in the column header.
Point to Number Filters and then select Top 10.
In the Top 10 AutoFilter dialog box, do the following.
0. In the box on the left, click Top or Bottom.
1. In the box in the middle, enter a number.
2. In the box on the right, do one of the following:
§ To filter by number, click Items.
§ To Filter by percentage, click Percent.
Sub total
You can automatically calculate subtotals and grand total in a list for a column by using the Subtotal command in the Outline group on the Data tab.

Select from a list of text values

In the list of text values, select or clear one or more text values to filter by.
The list of text values can be up to 10,000. If the list is large, clear (Select All) at the top.
Create criteria
1. Point to Text Filters and then click one of the comparison operator (=Equal to, > Greater than,<>=Greater than or equal to, <= Less than or equal to, and <> Not equal to.)commands, or click Custom Filter. For example, to filter by text that begins with a specific characters, select Begins With, or to filter by text that has specific characters anywhere in the text, select Contains.
2. In the Custom AutoFilter dialog box, in the box on the right, enter text or select the text value from the list. For example, to filter by text that begins with the letter "J", enter J, or to filter by text that has "bell" anywhere in the text, enter bell.

Filtering list

Using AutoFilter to filter data is a quick and easy way to find and work with a subset of data in a range of cells or table column. Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed. After you filter data, you can copy,
find, edit, format, chart, and print the subset of filtered data without rearranging or moving it.

Filter Text
1. Select a range of cells containing alphanumeric data.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Click the arrow in the column header.
Do one of the following:

Sort text

1. Select a column of alphanumeric data in a range of cells, or make sure that the active cell is in a table column containing alphanumeric data.
2. On the Home tab, in the Editing group, and then click Sort & Filter.
3. Do one of Following:
· To sort in ascending alphanumeric order, click Sort A to Z.
· To sort in descending alphanumeric order, click Sort Z to A.
Sort dates or times
1. Select a column of dates or times in a range of cells, or make sure that the active cell is in a table column containing dates or times.
2. Select a column of dates or times in a range of cells or table.
3. On the Home tab, in the Editing group, click Sort & Filter, and then do one of the following:
· To sort from an earlier to a later date or time, click Sort Oldest to Newest.
· To sort from a later to a earlier date or time, click Sort Newest to Oldest.