Section C.7. Data Menu

C.7. Data Menu

The Data menu includes features for managing lists of data, like sorting, filtering, and grouping. It also provides commands for creating pivot tables and importing data from external sources, like databases and Web pages.

C.7.1. Sort

The Sort dialog box (Data Sort) lets you choose the columns you want to use to sort a selected range of cells or a data list. When you click OK, Excel performs the sort, rearranging your cells. For more information, see Section 13.3 (Chapter 13).

C.7.2. Filter

This menu lets you filter a list of data so that you see only the rows that meet certain conditions. If you use Excel's data list feature (and create a list using the Data List Create List command), automatic filtering is available through the column headers. But if you want to use filtering with a range of cells that isn't explicitly configured as a data list, you can use the AutoFilter command from this menu. You can also use the Advanced Filter to create more sophisticated filter conditions that can take several criteria into consideration. For information about both types of filtering, see Section 13.3.3 (Chapter 13).

C.7.3. Form

The Data Form dialog box (Data Form) lets you view or edit a single row of data in a list. Each field in your list occupies a separate text box in the Data Form window. You can step from row to row using the window's scrollbar, and you can use a somewhat quirky built-in search feature. The Data Form dialog box is described in detail on Section 13.2 (Chapter 13).

C.7.4. Subtotals

Data Subtotals performs two operations on a range of selected cells: it automatically splits the rows into separate groups, and it inserts a new row with subtotal information after each group . It's up to you to choose which column Excel should use for grouping, and what information Excel should total in the summary row (from the Subtotal dialog box). For a step-by-step overview, see Section 14.1.3 (Chapter 14).

C.7.5. Validation

The Data Validation dialog box (Data Validation) lets you set rules that restrict the kind of content that youor anyonecan type into cells you specify. In the Settings tab, choose an option from the Allow list box to set the type of data that you want to permit ( numbers , dates, and so on). Depending on your selection, you may also be able to set maximum and minimum value limits. In the Input Message tab, you can make your validation criteria more obvious by creating a message that will appear whenever you move into the cell that has the validation rule. In the Error Message tab, you can control what Excel does if invalid data is entered in the cell . For more information about using input validation, see Section 15.4.1 (Chapter 15).

C.7.6. Table

Use the Data Table command to create a variable table. (A variable table takes a single calculation and shows the result of that calculation given different information. For example, you could create a variable table to show the possible return on an investment depending on the interest rate.) Variable tables are similar to Excel's scenario feature, but they show all the possible results in one place. For more information on how to create a variable table, see Section 12.3.1 (Chapter 12).

C.7.7. Text to Columns

The Data Text to Columns command lets you separate the text contained in one cell into multiple cells, split over several adjacent columns. For example, you could use this technique to convert a cell containing the name Julia Digweed into two columns, one with the first name Julia , and the other with the last name Digweed . You can also use Text to Columns on a whole range of cells at once. When you select Text to Columns, Excel shows the Convert Text to Columns Wizard, which is similar to the Text Import Wizard described on Section 22.3.3 (Chapter 22). Like the Text Import Wizard, you need to choose how you want to carve your text up into separate columns (either based on a fixed number of characters or by using a recognized delimiter character, like a space or a comma). The Convert Text to Columns Wizard shows a preview of how Excel will separate your data based on the options you specify.

C.7.8. Consolidate

This command opens the Consolidate dialog box. Using consolidation, you can take several similarly structured tables and combine the data by averaging, totaling , or performing another type of summary calculation with each cell. To use consolidation, you need to add each table you want to consolidate in the Consolidate dialog box (set the cell range in the Reference box and then click Add). You can then click OK to create the summary table in the current location. For a step-by-step overview of how this works, see Chapter 14.

C.7.9. Group and Outline

This menu provides commands for grouping, which lets you collapse multiple columns or rows so that they're temporarily hidden from view. Grouping is a useful tool when creating summary tables, and it's described in detail in Chapter 14. Use the Group command to bind together the currently selected columns or rows. Excel adds a +/- box in the margin on the worksheet grid, which you can click to collapse and expand the group. (You can also use the Hide Detail and Show Detail menu commands for the same purpose.) Use Ungroup to reverse your operation and remove the grouping in the selected rows or columns. Finally, use AutoOutline to automatically group tables of data, provided they have a structure that Excel can recognize. Page Section 14.2 (Chapter 14) shows you how to apply outlining and when it does and doesn't work.

C.7.10. PivotTable and PivotChart Report

This command launches the PivotTable and PivotChart Wizard. (Pivot tables are data tables that are extremely flexible, and whose contents you can rearrange with only a couple of mouse clicks.) The wizard walks you through three steps, which let you choose the data you want to use (external data or data from the current workbook), and the location where you want to place it (in the current worksheet or in an existing worksheet). A newly created pivot table or pivot chart shows up in Excel as a collection of empty boxes. To actually show some information, you need to define its structure by dragging fields from the PivotTable Fields task. Chapter 20 shows you how.

C.7.11. Import External Data

This menu provides commands for creating a new Web query (choose New Web Query) or a new database query (choose New Database Query). Web queries retrieve information from a Web page. They're detailed in Chapter 24. Database queries retrieve data from a database file like Microsoft Access or a database server like Microsoft SQL Server. They're described in Chapter 22.

C.7.12. List

This menu provides commands for creating and managing data lists. To begin, select the range of cells you want to convert into a list and choose Create from this menu. (For all the tasks you can perform with data lists, see Chapter 13.) Once you've created a list, you can use the Total Row command to add a summary item to the bottom of the list, or the Convert to Range command to change the list back to an ordinary set of cells. The other commands in this menu provide integration with Microsoft SharePoint. If you have a SharePoint server on your network, you can publish a list to that server, and other people can then retrieve the information from the list into their own workbooks. For more information about SharePoint, which isn't covered in this book, you can surf to www.microsoft.com/sharepoint or read a dedicated book like Microsoft SharePoint: Building Office 2003 Solutions by Scot P. Hillier (Apress, 2004).

C.7.13. XML

This menu provides commands for using Excel with XML documents. In order to use these features, you first need to map an XML document or XML schema to a worksheet. You can start this process by choosing XML Source, which shows the XML Source task on the right side of the Excel window. For a step-by-step explanation of how to map an XML document, see Chapter 23. Once the mapping is in place, you can use the Import command in this menu to extract the latest data from an XML file and place it into your worksheet, or the Export command to take the current worksheet data and write it to an XML file.

C.7.14. Refresh Data

This command works only inside a range of cells that shows the results of a Web query (Chapter 24), database query (Chapter 22), or XML query Chapter 23). When you use this command, Excel executes the query and replaces the existing data with the latest available information.