Performing Calculations and Formatting Data

Advanced Training for Microsoft Excel 2002

Creating Formulas to Calculate Values

1	To enter a formula in cell D6 that calculates the sum of the values in cells D3 and D4, click cell D6, type =D3+D4, and press the ENTER key on your keyboard.
2	To calculate the average price of items in Order #1, click the Insert menu, and click Function.
3	In the Insert Function dialog box, in the Select a Function list, click AVERAGE and then click OK.
4	The cell range D3:D4 is named OrderItems1, so type OrderItems1 and then click OK.
5	To use the SUM function to calculate a subtotal for Order #2, click cell D16, and, to the left of the Formula bar, click the Insert Function button.
6	In the Insert Function dialog box, in the Select a function pane, click SUM and then click OK.
7	In the Function Arguments dialog box, click OK.
8	To copy the formula from cell D16 to cell D26 and have Excel adjust the cell references in the formula to calculate the subtotal for Order #3, on the Standard toolbar, click the Copy button.
9	In the vertical scroll bar, click the down scroll arrow four times.
10	Click cell D26, and then, on the Standard toolbar, click the Paste button.
11	Notice that Excel has adjusted the formula you see in the Formula bar, automatically replacing the cell range D10:D15 with the cell range D20:D25. To scroll back up the page, click once above the scroll box in the vertical scroll bar.
12	Orders in which the average price is over $50 should receive a 5% discount. To create a conditional formula that tells if an order qualifies for this discount, click cell E7, click the Insert menu, and click Function.
13	In the Insert Function dialog box, in the Select a function list, click IF and then click OK.
14	In the Function Arguments dialog box, in the Logical_test box, type D7>50.
15	Click in the Value_if_true box, and type "5% discount" (be sure to type the quotation marks).
16	Click in the Value_if_false box, type "No discount" (including quotation marks), and click OK.

Finding and Correcting Errors in Calculations

1	To identify the cell range precedents of the value in cell D14, click the Tools menu, point to Formula Auditing, then click Trace Precedents.
2	The blue arrow indicates that cells in the D8:D12 range are used to calculate the value in cell D12. To remove the blue arrow, click the Tools menu, point to Formula Auditing, then click Remove All Arrows.
3	To change the precedents for the formula in D14, in the formula bar, click to the immediate left of the cell reference D8:D12.
4	Point to the right border of the blue box surrounding cells D8:D12, and when the mouse pointer changes to a four-headed arrow, hold down the left mouse button, drag the box one column to the right, and release the mouse button.
5	To identify the source of the error in cell D24, click cell D24. Click the Tools menu, point to Formula Auditing, then click Trace Error.
6	To edit the formula used in cell D20, in the formula bar, double-click D5 in the formula =AVERAGE (D5,D13, D21), then type D6.
7	To replace the second cell reference in the formula, in the formula bar, double-click D13, and then on the worksheet, click cell D14.
8	Notice that when you clicked the new cell, the selected cell reference in the formula bar changed. To replace the final cell reference, in the formula bar, double-click D21, then on the worksheet, click cell D22.
9	To see the result of the revised formula and remove the blue tracer arrows, press the ENTER key on your keyboard.

Making Numbers Easier to Read

1	To begin applying a date format to the selected cell, click the Format menu, then click Cells.
2	To view formatting choices for dates, in the Format Cells dialog box, in the Category list, click Date.
3	To accept 3/14/01, which is highlighted in the Type* list, as the date format, click OK.
4	To assign the format of cell B4 to cells B5 through B23, on the Standard toolbar, click the Format Painter button.
5	Click cell B5, hold down the mouse button, drag the mouse pointer to cell B23 and release the mouse button.
6	To test the formatting you have just selected, click cell B4, type January 25, 2003, and press ENTER.
7	Notice that the date has been changed to 1/25/2003, matching the format you just set. To scroll to the right side of the worksheet, click to the right of the scroll box in the horizontal scrollbar, and then click cell J4.
8	To select multiple cells for formatting all at once, hold down the SHIFT key, click cell J23, then release the SHIFT key.
9	Click the Format menu, then click Cells.
10	To view a list of special formats, in the Format Cells dialog box, in the Category list, click Special.
11	In the Type list, click Phone Number, then click OK.
12	To test the formatting you have just selected, click cell J4, type 4255550102, and press ENTER.
13	Notice that the number has been correctly reformatted as a 10-digit telephone number. To specify a custom number format for cells in the Limit column, click cell K4, click the Format menu, then click Cells.
14	In the Format Cells dialog box, in the Category list, click Custom.
15	In the Type list, click #,##0.00.
16	To further modify this format, in the Type box, click to the left of the existing format and type $.
17	Click to the right of the format, press the SPACEBAR, type "total" (including the quotation marks), and press ENTER.
18	To copy the formatting from K4 to additional cells in this column, on the Standard toolbar, click the Format Painter button.
19	Press and hold down the SHIFT key, click cell K23, and then release the SHIFT key.
20	To test the custom formatting in column K, click cell K4, type 2400, and press ENTER.

Formatting Data Based on Its Value

1	To begin defining a conditional format for the selected cell, click the Format menu, then click Conditional Formatting.
2	To define the conditions of this conditional format, click in the First Argument box (third field from left), then type 1000.
3	Click in the Second Argument box (at far right), then type 2000.
4	To define the format, click the Format button.
5	In the Font tab of the Format Cells dialog box, click the Color arrow, then in the color palette that appears, click the Blue square (second row, sixth box from left).
6	Notice the blue color applied to text in the Preview pane, and click OK.
7	To add a second condition, click the Add button.
8	In the Condition 2 section of the dialog box, click in the First Argument box (third from left), and type 2000.
9	Click in the Second Argument box (at far right), type 2500, and then click the Format button.
10	In the Format Cells dialog box, on the Font tab, click the Color arrow, then from the color palette that appears, click the Green square, (second row, fourth square from left).
11	In the Format Cells dialog box, click OK.
12	To apply the new conditional format, in the Conditional Formatting dialog box, click OK.
13	To copy the conditional format to the cells below K4, position the mouse pointer over the Fill Handle at the bottom right of cell K4, hold down the mouse button, drag the Fill Handle to cell K6, and release the mouse button.
14	To revert the contents of cells K5 and K6 to their previous values but apply the conditional formats to the selected cells, click the Auto Fill Options button, and then click Fill Formatting Only.

Creating Rules to Validate Data

1	To create a validation rule to limit the value allowed in cell K4, click on the Data menu, then click Validation.
2	In the Data Validation dialog box, on the Settings tab, click the Allow arrow, and then click Decimal.
3	To limit data to a maximum value, click the Data arrow, then click less than or equal to.
4	Click the Maximum box and type 2500.
5	To have Excel treat a blank cell as a value less than or equal to 2500, click the Ignore Blank check box to clear it.
6	To create an input message to tell users what rules they must follow, click the Input Message tab.
7	Click the Title box, then type Enter Limit.
8	To create an error message that will appear when invalid data is entered, click the Error Alert tab.
9	Click the Style arrow, and then click Warning.
10	To name the error message, click in the Title box, and type Error.
11	To use a default error message, leave the Error message box blank, and click OK.
12	To test the validation rule, type 2501, and press ENTER.
13	To accept the value 2501 in cell K4, in the Error warning box, click Yes.
14	To test the validation rule with a lower number, click cell K4, type 2500, and press ENTER.
15	To disallow decimal values from being entered, click cell K4, click the Data menu, and then click Validation.
16	In the Data Validation dialog box, click the Settings tab.
17	Click the Allow arrow, then click Whole number, and click OK.
18	To test the revised rule, type 2499.95, and press ENTER.
19	In the Error warning box, click No.
20	Type 2500, and press ENTER.

Limiting the Data That Appears on the Screen

1	To create a filter so that of rows 6 to 36, only rows containing the five largest values in cells P5 to P36 will display, click the Data menu, point to Filter, and then click AutoFilter.
2	In cell P5, click the down arrow and, from the list that appears, click (Top 10...).
3	Press the DELETE key, type 5, and click OK.
4	To remove the filter and display all rows again, click the Data menu, point to Filter, and then click AutoFilter.
5	To scroll left in the worksheet, click once to the left of the scroll box in the horizontal scroll bar.
6	To create a filter so that of rows 6 through 36, only the rows with Mon in column B will display, click cell B5, click the Data menu, point to Filter, and click AutoFilter.
7	In cell B5, click the down arrow and, from the list of unique column values that appears, click Mon.
8	To display all rows again, click the Data menu, point to Filter, and then click AutoFilter.
9	To scroll right again, click once to the right of the scroll box in the horizontal scroll bar.
10	To create a filter so that of rows 6 through 36, only the rows with totals in column P of at least 3000 will be displayed, click cell P5, click the Data menu, point to Filter, and click AutoFilter.
11	In cell P5, click the down arrow and then, from the list that appears, click (Custom?).
12	In the upper-left box, click the down arrow and, from the list that appears, click is greater than or equal to.
13	Click in the upper-right box, type 3000, and then click OK.
14	To change the filter so that of rows 6 through 36, only the row containing 2236 in column P will be displayed, click the down arrow in cell P5, and from the list of unique column values that appears, click 2236.
15	To remove the filter and view all rows again, click the Data menu again, point to Filter, and click AutoFilter.
16	To ask Excel to pick rows at random from rows 5 through 36 in order to perform an audit on data from 16% of the days listed, click cell Q5, type Audit, and press ENTER.
17	In cell Q6, type =RAND()<17% and then press ENTER.
18	Click cell Q6, point to the Fill handle at the lower-right corner of the cell, hold down the left mouse button, drag the Fill handle down to cell Q36, and release the mouse button.
19	To scroll back to the upper-left corner of the worksheet, while holding down the CTRL key,press the HOME key on your keyboard.
20	To create a filter that allows only the rows containing the first occurrence of each value in cells B5 to B36 to display, point to cell B5, hold down the left mouse button, drag to cell B36, and release the mouse button.
21	Click the Data menu, point to Filter, and then click Advanced Filter.
22	At the bottom of the Advanced Filter dialog box, click the Unique records only check box and then click OK.
23	To view all rows again, click the Data menu, point to Filter, and then click Show All.

Creating a Template from an Existing Workbook

1	To modify this file and save it as a template, click the Sheet2 tab in the lower left area of the workbook window, press and hold down the CTRL key, and click the Sheet3 tab.
2	Sheet2 and Sheet3 are now selected. To delete these worksheets, click the Edit menu, and then click Delete Sheet.
3	To save this version of the file as a template, click the File menu, and click Save As.
4	At the bottom of the Save As dialog box, click the Save as type down arrow, and click *Template (.xlt).**
5	The default folder, or active directory, changes to the Microsoft Office XP templates folder. To rename the file, double-click SalesTrack in the File name box to select it, type MonthlySales, then click Save.
6	Excel saves your document as a template.To close this file and create a new workbook based on this template, click the File menu, and click Close.
7	MonthlySales.xlt closes. Click the View menu, then click Task Pane.
8	The New Workbook task pane appears.In the New from template section of the task pane, click General Templates.
9	In the Templates dialog box, double-click MonthlySales.xlt.
10	A new workbook opens named MonthlySales1.xls. On the Standard toolbar, click the Save button.
11	The Save As dialog box appears with the Templates folder as the active directory. On the left side of the dialog box, click My Documents, then double-click the sub-folder Garden Company.
12	To rename the file, double-click MonthSales1 in the File name box to select it, type DecemberSales, then click Save.
13	Excel saves your new workbook as DecemberSales. To rename the Sheet1 tab, double-click the Sheet1 tab to select it, type Tools, and then press ENTER.
14	To insert a new worksheet, click the Insert menu, then click Worksheet.
15	To rename this new tab, double-click the Sheet1 tab to select it, type Supplies, and then press ENTER.
16	To return to the Tools worksheet, click the Tools sheet tab.