Appendix A: Data Analysis Quick Reference

This appendix lists the steps you follow for many of the data analysis tasks described elsewhere in this book. You can use this quick reference to get started on specific data analysis tasks. For brevity, the steps in this appendix commonly use command names and menu options from Microsoft Office 2002.

Microsoft Excel

Sort a List of Data Records

1. Select the cells you want to sort.

2. On the Data menu, click Sort.

3. Select the field or fields you want to sort by, the sort order, and other options in the Sort dialog box.

4. Click OK.

Filter a List of Data Records Using Simple Criteria

1. Click any cell in the list of records.

2. On the Data menu, point to Filter and then click AutoFilter.

3. Select an item from the AutoFilter list for one or more fields, or use the Top 10 or Custom items to enter criteria for the filter.

Filter a List of Data Records Using Advanced Criteria

1. Insert at least three blank rows above the list of data records.

2. In separate cells in the first blank row, type the name of each column by which you want to filter.

3. In the second and subsequent rows, type the advanced filter criteria.

4. Click any cell in the list of records you want to filter.

5. On the Data menu, point to Filter and then click Advanced Filter.

6. In the Advanced Filter dialog box, identify the group of cells you want to filter (List Range) and the cells that define the filter criteria (Criteria Range).

7. Click OK to apply the filter.

Run a Worksheet Function

1. Click the spreadsheet cell in which you want the function’s result to appear.

2. On the Insert menu, click Function.

3. Select the function name and click OK.

4. In the Function Arguments dialog box, enter the data values, cell addresses, or other arguments that the function requires.

5. Click OK to insert the function into the cell and see the results.

Format Cells Based on Certain Conditions

1. Select the cells to which you want to apply conditional formatting.

2. On the Format menu, click Conditional Formatting.

3. Enter the formatting conditions in the Condition 1 area. If you want to add more cell formatting options, click the Add button and provide formatting conditions in the Condition 2 area. Click the Add button again to add cell formatting options for the Condition 3 area.

4. Click OK to apply the conditional formatting.

Create a Chart

1. Select the cells containing the data values that you want to include in your chart.

2. On the Standard toolbar, click the Chart Wizard button.

3. Step through the Chart Wizard and select the chart type, the data range, chart formatting options, where to place the chart, and other options.

4. Click Finish to create the chart.

Import External Data into a Workbook

1. Click the cell in which you want the first item of the external data to appear.

2. On the Data menu, point to Import External Data and then click Import Data.

3. If you know that the data file or data source connection file already exists, locate and select the file, click Open, and then follow the directions Excel provides to finish importing the data. To create a new data source connection file, click the New Source button.

4. On the wizard’s first screen, select a data source and then click Next.

5. Depending on the type of data source you want to connect to, follow the steps to specify connection properties.

6. After you have specified connection properties, click Finish. The Select Data Source dialog box reappears.

7. Click the data source connection file, click Open, and then follow the instructions to finish importing the data.

Query Data in an External Data Source

1. On the Data menu, point to Import External Data and then click New Database Query.

2. If your data source is visible on the Databases tab, double-click the data source and go to step 9 in this procedure. Otherwise, select the Use The Query Wizard To Create/Edit Queries check box, click New Data Source on the Databases tab, and then click OK.

3. In the What Name Do You Want To Give Your Data Source box, type a name for the data source that’s descriptive yet easy for you to remember.

4. In the Select A Driver For The Type Of Database You Want To Access, select the type of database in which you want to query data.

5. Click Connect.

6. In the ODBC Setup dialog box, provide the information required to connect to the data source. (The information varies depending on the type of database you want to query.) Click OK.

7. In the Select A Default Table For Your Data Source list, select the table that contains the data you want to include in your query and then click OK.

8. Click OK to return to the Choose Data Source dialog box, and then click OK in the Choose Data Source dialog box to start the Query Wizard.

9. In the Available Tables And Columns list, double-click each column that you want to include in your query to move the column to the Columns In Your Query list.

10. Click Next.

11. Filter the data to be displayed by clicking a column in the Column To Filter list and specifying filter conditions in the Only Include Rows Where area. Click Next.

12. On the Sort Order page, specify columns to sort by and the sort order. Click Next.

13. Select an option to return the data to an Excel worksheet, view or edit the data in Microsoft Query, or create an OLAP cube from the data that the query returns. Click Finish.

Query Data in a Web-Based Data Source

1. Click the cell in the spreadsheet where you want to insert the first item of data.

2. On the Data menu, point to Import External Data and then click New Web Query.

3. In the Address box, type the address for the data and then click Go.

4. Click the arrows next to the data tables that you want to query.

5. Click the Import button.

6. Click OK. The results are inserted into the spreadsheet.

Subtotal a List of Data Records

1. Select the data for which you want to insert subtotals.

2. On the Data menu, click Subtotals.

3. Provide the subtotal settings in the Subtotal dialog box.

4. Click OK. The subtotals are inserted.

5. Use the outline buttons and the plus and minus buttons to show or hide data and subtotals.

Run an Analysis ToolPak Function

1. On the Tools menu, click Data Analysis.

2. Select the tool that provides the information you need.

3. In the dialog box for the ToolPak function, enter the information required; for example, the input range, the output range, and so on.

4. Click OK.

Run the Solver Add-In

1. On the Tools menu, click Solver.

2. In the Solver Parameters dialog box, enter information required for the target cell, the cells whose values can change, any constraints, and so on.

3. Click Solve.