Combining and Organizing Data

Advanced Training for Microsoft Excel 2002

Working with More Than One Set of Data

1	To begin working with more than one workbook at a time, on the Standard toolbar, click the Open button.
2	In the Open dialog box, click January.xls, press and hold down the CTRL key, and click February.xls and March.xls, and then click Open.
3	To arrange these three files in the Excel window, click the Window menu, then click Arrange.
4	In the Arrange Windows dialog box, click Cascade and click OK.
5	The windows of the open documents are cascaded within the Excel window, and January remains the active document. Click the File menu, then click Save As.
6	The Save As dialog box opens with January.xls already selected in the File name text box. To rename this file, type FirstQuarter, then click Save.
7	Notice the name on the title bar of the active document changes to FirstQuarter.xls.To copy the February worksheet into the FirstQuarter.xls workbook, first click the February.xls title bar to activate the document, then click the Edit menu and click Move or Copy Sheet.
8	In the Move or Copy dialog box, click the To book down arrow, then click FirstQuarter.xls.
9	In the Before sheet list, click (move to end), and then, at the bottom of the Move or Copy dialog box, click Create a copy to select it, and click OK.
10	The February worksheet now appears in FirstQuarter.xls. To copy the March worksheet to the FirstQuarter.xls workbook, first click the March.xls title bar to activate the document, then click the Edit menu, and click Move or Copy Sheet.
11	In the Move or Copy dialog box, click the To book down arrow, then click FirstQuarter.xls.
12	In the Before sheet list, click February, and then, at the bottom of the Move or Copy dialog box, click Create a copy to select it, and click OK.
13	To reorder the tabs, click the February tab and hold down the left mouse button, and then drag the blank page icon that appears to the left until the small black insert arrow appears to the left of the March tab, and release the mouse button.
14	The tabs and worksheets are reordered. To differentiate the tabs using tab colors, click the March tab to select it, click the Format menu, point to Sheet, then click Tab Color.
15	In the Format Tab Color dialog box, click the light orange square (third row from the bottom, second column from the left), then click OK.
16	Excel assigns the light orange color to the March tab. To add color to another tab, click the February tab, click the Format menu, point to Sheet, then click Tab Color.
17	In the Format Tab Color dialog box, click the light yellow square (third row from the bottom, third column from the left), then click OK.
18	To view the new tab colors, click the January tab.

Summarizing Multiple Sets of Data

1	To select the location where you would like to paste consolidated data from the FirstQuarter workbook, click the SalesByMonth workbook on the task bar, and click cell D5.
2	To begin defining the data you would like to consolidate, click the Data menu, and click Consolidate....
3	Notice that the Function: box is aleady set to calculate a sum. To locate the ranges that you will use to calculate sums, click the Window menu, then click FirstQuarter.
4	Click the right scroll arrow of the horizontal scroll bar three times, then click cell D6.
5	Notice that the Reference box of the Consolidate dialog box now contains the cell references you have just defined. To expand this reference to include cells D6 through O36, click below the scroll box in the vertical scroll bar once, hold down the SHIFT key, click cell O36, and release the SHIFT key.
6	To add the reference you have just defined to the list of ranges to be consolidated, click Add.
7	To define the same range in the February sheet, click the February sheet tab, and click Add.
8	To define the same range in the March sheet, click the March sheet tab, and click Add.
9	To return to the SalesByMonth workbook, click the Window menu, then click SalesByMonth.
10	To have Excel calculate the sums of the contents of cells in the three ranges you've defined and insert the results into the SalesByMonth workbook, in the Consolidate dialog box, click OK.

Grouping Multiple Data Lists

1	To open two files at once, on the Standard toolbar, click the Open button.
2	In the Open dialog box, click SalesbyMonth, press and hold down the CTRL key, click TotalByHour2003, release the CTRL key, and click Open.
3	To create a workspace that references the two open files, click the File menu, then click Save Workspace.
4	In the Save Workspace dialog box, in the File name box, type SalesbyMonthSummary, then click Save.
5	To close both of the active workbooks, at the top right corner of SalesbyMonth.xls, click the gray Close Window button, then on the second file, click the Close Window button again.
6	To open the SalesbyMonthSummary workspace, on the Standard toolbar, click the Open button.
7	In the Open dialog box, double-click the SalesbyMonthSummary icon.

Organizing Data into Levels

1	To select rows 1 through 32, for which you'd like to calculate subtotals, click the gray row heading for row 1, click once below the scroll box in the vertical scroll bar, press and hold down the SHIFT key, click the gray row heading for row 32, and release the SHIFT key.
2	To add subtotals to the selected rows, click the Data menu, and click Subtotals.
3	To add a subtotal in the Sales column at each change in the Week column, and a grand total below the final row, click OK.
4	To create a level 4 grouping within Week 1 that consists of data for Monday through Friday only, click the row heading for row 2, press and hold down the SHIFT key, click the row heading for row 6, and release the SHIFT key.
5	Click the Data menu, point to Group and Outline, then click Group.
6	To hide rows 2 through 6, in the Outline section, next to row 7, click the Hide Detail button (the - symbol) .
7	To unhide rows 2 through 6, in the Outline section, next to row 7, click the Show Detail button (the + symbol).
8	To hide all rows except row 1 (with the column headings) and row 38 (with the Grand Total), in the Outline section, click the Level 1 button.
9	To view the weekly subtotals along with the Grand Total, in the Outline section, click the Level 2 button.
10	To view all rows except rows 2 through 6, in the outline section, click the Level 3 button.
11	To show all rows in all four levels, in the Outline section, click the Level 4 button.

Retrieving Data from a Database

1	To define a table in an Access database as a data source for this worksheet, click the Data menu, point to Import External Data, then click New Database Query.
2	In the Choose Data Source dialog box, on the Databases tab, note that New Data Source is already selected, so click OK.
3	To name the new data source, in the Create New Data Source dialog box, in the first box, type Product Information.
4	To select a driver for the type of database you want to access, click the down arrow in the second box, then click *Microsoft Access Driver (.mdb).**
5	To enter additional information requested by the driver, click Connect.
6	To select the database you want to access, in the ODBC Microsoft Access Setup dialog box, in the Database section, click Select.
7	In the Select Database dialog box, in the Database Name list, click Products.mdb, then click OK.
8	To accept the path shown for the database driver, click OK.
9	To assign the Products database as the default table for this data source, in the fourth box, click the down arrow, click Products, and click OK.
10	To start creating a query using the Query Wizard, in the Choose Data Source dialog box, click OK.
11	To include all three columns of data from the Products database in your query, in the Available tables and columns list, click ProductID then click the Add button, (>).
12	Click the Add button two more times, then click Next.
13	To specify a filter for the Price column, in the Column to filter list, click Price.
14	To limit the data brought into the workbook to products that cost $50 or more, in the first Comparison Operator box at right, click the down arrow, then click is greater than or equal to.
15	In the first Value box, type 50, then click Next.
16	To sort your data in ascending order of values in the ProductID column, in the Sort by box, click the down arrow, click ProductID, and click Next.
17	To begin saving your query, click Save Query.
18	In the Save As dialog box, in the File name box, type 50andOver, then click Save.
19	Notice that the Return Data to Microsoft Excel option is already selected, so, click Finish.
20	To accept cell A1 as the upperleft corner cell for the range where the query results will be pasted, in the Import Data dialog box, click OK.

Looking Up Information in a Data List

1	To create a named range of cells that includes all product IDs, product names and prices on this worksheet, click cell A4, press and hold down the SHIFT key, click cell C18, then release the SHIFT key.
2	In the upper left corner of the worksheet click the Name Box (which currently contains A4), type ToolList, then press ENTER.
3	To begin defining a VLOOKUP function that allows you to type in a product ID and return the corresponding product name, click cell E6 to select it, and type =VLOOKUP(.
4	To identify the cell where you will type the ID number you want to look up, type E4, (including the comma) and press the SPACEBAR.
5	To define the cell range that Excel will search, type ToolList, (including the comma) and press the SPACEBAR.
6	To have VLOOKUP return a value from the second column, type 2, (including the comma) and press the SPACEBAR.
7	To have VLOOKUP return a result only when it finds an exact match, type FALSE), and then press ENTER.
8	Since there is no ProductID in cell E4, an error code appears in cell E6. To give the VLOOKUP function something to look up, click cell E4 to select it, type TL0038, and press ENTER.
9	To look up another ProductID, click cell E4, and type TL3001, and press ENTER.