ECDL Excel

Using the Application

Save this workbook as Excel 97-2003 Workbook format to . Do not change the file name. If a question is shown when saving, click Yes. Suggested solution: Click the File tab, and then click Save As, and then Browse. In the Save As dialogue box, select Excel 97-2003 Workbook in the Save as type list. Click Save.

Where should you click to collapse the ribbon? Your Answer Correct Answer

The Quick Access Toolbar, right-click The Tab bar, right-click on tab name The Ribbon, right-click The Collapse the Ribbon button The Ribbon Display options button

Switch to the year workbook. Suggested solution: On the View tab, in the Window group, and then click Switch Windows. Click year.xlsx.

Create a new, blank workbook. Suggested solution: Click the File tab, and then click New. Double-click Blank Workbook.

Where should you click to open the help function? Your Answer Correct Answer

The Help icon

Change the Excel user name to Sue Davis. Suggested solution: Click the File tab, and then click Options. Under Personalize your copy of Office, type Sue Davis in the User name box. Click OK.

Save this workbook to \ using the file name data. Suggested solution: Click the File tab, and then click Save As, and then Browse. In the Save As dialogue box, change the file name to data. Click Save.

Zoom the Weather worksheet to 95%. Suggested solution: On the View tab, in the Zoom group, click Zoom. In the % field, type 95.

Cells

On the Weather worksheet, change the number in cell F12 to 104.3. Suggested solution: Select cell F12. Type 104.3. Press Enter.

On the Weather worksheet, enter the number 33.1 in cell C9. Suggested solution: Select cell C9. Type 33.1. Press Enter.

Where should you click to undo the latest action? Your Answer Correct Answer

The Undo button

On the Weather worksheet, move the content in cell C2 to cell G16. Suggested solution: Select cell C2. On the Home tab, in the Clipboard group, click the Cut button. Select cell G16. On the Home tab, in the Clipboard group, click the Paste button.

On the Weather worksheet, use the appropriate command to find all occurrences of the word Moscow. Enter the number of times it occurs in cell B26 (use a figure). Suggested solution: On the Home tab, in the Editing group, click Find & Select and then Find. Type Moscow in the Find what box. Click the Find all button. Type the number of times the word appears in cell B26 (example: type 100, not hundred).

On the Weather worksheet, copy the cell F12 to cell G7. Suggested solution: Select cell F12. On the Home tab, in the Clipboard group, click the Copy button. Select cell G7. On the Home tab, in the Clipboard group, click the Paste button.

What is considered good practice when creating lists? Your Answer Correct Answer

Ensure that cells bordering the list are blank.

Which of the four workbooks follows good practice for entering data into cells? Your Answer Correct Answer

Workbook A

On the Budget worksheet, use the appropriate command to replace all occurrences of Result with Difference. Suggested solution: On the Home tab, in the Editing group, click Find & Select. Click Replace. In the Find what box, type Result. In the Replace what box, type Difference. Click the Replace all button.

On the Weather worksheet, use the autofill tool to finish the incomplete list in cells B19:B23. Suggested solution: Select cells B19:B20. Click the fill handle in the lower-right corner of the selection. Drag the handle down to cell B23.

On the Weather worksheet, sort the cell range B18:C23 by Per year in descending order (largest to smallest). Suggested solution: Select the cell containing Per year (C18). On the Home tab, in the Editing group, click Sort & Filter, and then click Sort Z to A.

On the Weather worksheet, delete the content in cell H13. Suggested solution: Select cell H13. Press the Delete key once.

Managing Worksheets

On the Weather worksheet, insert a new column between the columns E and F. Suggested solution: Click the column heading F to select the column. On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Sheet Columns.

On the Budget worksheet, set row 1 so it is always visible when scrolling the worksheet. Suggested solution: On the View tab, in the Window group, click Freeze Panes. In the drop-down menu, click Freeze Top Row.

On the Weather worksheet, change the height value of row 1 to 30. Suggested solution: Click the row heading 1 to select the row. On the Home tab, in the Cells group, click Format. Under Cell Size, click Row Height. In the Row height box, type 30.

What is considered good practice when naming worksheets? Your Answer Correct Answer

Worksheet names should be meaningful for a user.

Move the Budget worksheet to the right of the Weather worksheet. Suggested solution: Click the Budget worksheet tab. Drag the Budget tab and place it to the right of the Weather tab.

Insert a new, blank worksheet. Suggested solution: Click the New sheet button at the bottom of the Excel window. Right-click the new tab, and then click Rename in the context menu. Type the new name. Click a cell on the worksheet to confirm.

Switch to the Budget worksheet. Suggested solution: Click the Budget worksheet tab. Select the cell D1. Press the Delete key.

Formulas and Functions

On the Budget worksheet, enter a function in cell E24 that rounds the number in cell E22 to 0 decimal places. Suggested solution: Select the cell E24. On the Formulas tab, in the Function Library group, click Insert Function. Locate and select the ROUND function. Click OK. In the Number list, type E22. In the Num_digits list, type 0. Press the Enter key.

On the Budget worksheet, enter a formula in cell C32, using the multiplication sign, that multiplies cell C14 by cell E28. Suggested solution: Select the cell C32. Type =C14*E28. Press the Enter key.

On the Budget worksheet, enter a formula in cell E9 that subtracts cell C9 from cell D9. Suggested solution: Select the cell E9. Type =D9-C9. Press the Enter key.

On the Budget worksheet, enter a formula in cell E28 that divides cell E27 by cell E25. Suggested solution: Select the cell E28. Type =E27/E25. Press the Enter key.

On the Budget worksheet, enter a function in cell C4 that counts the cells that contain numbers in the cell range C9:E12. Suggested solution: Select the cell C4. On the Formulas tab, in the Function Library group, click Insert Function. Locate and select the COUNT function. Click OK. In the Value1 list, type C9:E12. Press the Enter key.

On the Budget worksheet, enter a function in cell E26 that displays Yes if the number in E24 is greater than the number in E25, and otherwise displays No. Suggested solution: Select the cell E26. On the Formulas tab, in the Function Library group, click Insert Function. Locate and select the IF function. Click OK. In the Logical_test list, type E24>E25. In the Value_if _true list, type Yes. In the Value_if _false list, type No. Press the Enter key.

On the Weather worksheet, enter a function in cell D25 to calculate the average value of cell range D19:D23. Suggested solution: Select cell D25. Click the arrow next to the Sum button in the Editing group on the Home tab. Click Average and then select the cell range D19:D23. Press the Enter key.

What is considered good practice when creating formulas? Your Answer Correct Answer

References to cells rather than numbers should be used.

There is a formula in cell C4 that contains a reference to cell C2. What error message will be displayed in cell C4 if the selected cells (which includes the cell C2) are deleted by selecting Delete from the right click menu? Your Answer Correct Answer

#REF!

On the Budget worksheet, change the formula in cell C32 so that the reference to cell E28 does not change if the formula is copied. Suggested solution: Select the cell C32. In the Formula Bar, change E28 to E$28. Press the Enter key.

Formatting

On the Budget worksheet, apply a Bottom Double Border to the cell range C21:E21. Suggested solution: Select the cell range C21:E21. On the Home tab, in the Font group, click the arrow next to the Borders button. In the list, click Bottom Double Border.

On the Weather worksheet, apply the thousand separator format to the cell range C19:C23. Suggested solution: Select the cell range C19:C23. On the Home tab, in the Number group, click the Comma Style button.

On the Weather worksheet, change the font type of the text in cell A1 to Arial. Suggested solution: Select cell A1. On the Home tab, in the Font group, choose Arial in the Font box.

On the Budget worksheet, apply a currency format of your choice to cells C9:E12. Suggested solution: Select the cell range C9:E12. On the Home tab, in the Number group, click the Number Format box and select Currency.

On the Budget worksheet, apply the Percentage format to the cell E29. Suggested solution: Select the cell E29. On the Home tab, in the Number group, click the Percent Style button.

On the Budget worksheet, apply text wrapping to cell C31 . Suggested solution: Select the cell C31. On the Home tab, in the Alignment group, click Wrap Text.

On the Budget worksheet, apply a different background colour of your choice to the cell range D24:D29. Suggested solution: Select the cell range D24:D29. On the Home tab, in the Font group, click the arrow next to Fill Color and then click a colour on the palette. To apply the most recently selected colour, click Fill Color.

On the Budget worksheet, left align the content of cells D24 to D29. Suggested solution: Select the cell range D24:D29. On the Home tab, in the Alignment group, click the Align Left button.

On the Weather worksheet, apply italic formatting to the cell A2. Suggested solution: Select cell A2. On the Home tab, in the Font group, click the Italic button.

On the Budget worksheet, merge and centre the cell range A1:F1. Suggested solution: Select the cell range A1:F1. On the Home tab, in the Alignment group, click the Merge & Center button.

On the Budget worksheet, copy only the formatting from cell C31 and apply it to cell range B32:B37. Suggested solution: Select the cell C31. On the Home tab, in the Clipboard group, click the Format Painter button. The mouse pointer changes to a paintbrush icon. Select the cell range B32:B37 by using the mouse.

Charts

On the Budget worksheet, apply Value data labels to all bars in the chart. The position of the data labels is not important. Suggested solution: Click once on a chart bar to select all bars. Under Chart Tools, on the Design tab, in the Chart Layouts group, click Add Chart Element. Select Data Labels in the list and then click a display option.

On the Budget worksheet, change the chart area background colour to a solid colour of your choice (without changing chart style). Suggested solution: Click anywhere in the chart. Under Chart Tools, on the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box. Then select Chart Area. On the Format tab, in the Shape Styles group, click Shape Fill and choose a different solid fill color.

On the Budget worksheet, create a 2-D Clustered Bar Chart using cell range B31:C37. Suggested solution: Select the cell range B31:C37. On the Insert tab, in the Charts group, click Insert Bar Chart. Below 2-D Bar, click Clustered Bar. Select the chart and drag it below row 37 to the left-hand side of the worksheet.

On the Budget worksheet, change the chart title to Reductions. Suggested solution: Click the chart title to activate it, and then click it again to place the cursor in the text. Drag to select the text. Type Reductions.

On the Budget worksheet, change the colour of the chart columns to a solid colour of your choice. Suggested solution: Click anywhere in the chart. Under Chart Tools, on the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box. Then select Series “Future expenses”. On the Format tab, in the Shape Styles group, click Shape Fill and choose a different solid fill color.

On the Budget worksheet, change the font size of the chart title to 12 point. Suggested solution: Click the chart title Reductions to activate it, and then click it again to place the cursor in the text. Click and drag the mouse to select the title. On the Home tab, in the Font group, click 12 in the Font Size list.

On the Budget worksheet, change the width of the chart to 18 cm. Suggested solution: Click the chart area. Click a sizing handle in a corner of the chart. When the pointer changes to a double-headed arrow, drag the sizing handle until the chart is not wider than columns A to F.

On the Budget worksheet, change the chart to a 2-D Clustered Column Chart. Suggested solution: Click the chart area. On the Design tab, in the Type group, click Change Chart Type. In the Change Chart Type dialog box, click Column, and then the subtype Clustered Column.

Prepare Outputs

On the Budget worksheet, apply the appropriate setting so row 1 will be shown on top of all pages when printed (but do not print). Suggested solution: On the Page Layout tab, in the Page Setup group, click Print Titles. In the Page Setup dialogue box, click the Sheet tab. Click the Collapse Dialog button to the right of Rows to repeat at top:. Click anywhere in row 1. Click the Collapse Dialog button again to return to the Page Setup dialogue box Click OK.

On the Budget worksheet, change the right margin to 2.3 cm. Suggested solution: On the Page Layout tab, in the Page Setup group, click Margins and then Custom Margins. In the Right: field, enter 2.3. Click OK.

Where should you click to choose that 5 copies of the worksheet should be printed? Your Answer Correct Answer

The Number of copies box The Number of copies box, up arrow

Where should you click to display how the worksheet will appear when printed? Your Answer Correct Answer

The Print option

On the Budget worksheet, use the spell check tool and make the necessary changes. Suggested solution: On the Review tab, in the Proofing group, click Spelling. Click the Change button until all misspelled words are corrected.

On the Budget worksheet, change the page orientation to Landscape. Suggested solution: On the Page Layout tab, in the Page Setup group, click Orientation and then Landscape.

On the Budget worksheet, apply the appropriate setting to fit the worksheet on one page wide and two pages tall when printed (but do not print). Suggested solution: Click the Page Layout tab and go to the Scale to fit group. Enter 1 page in the Width: list. Enter 2 pages in the Heigth: list.

On the Budget worksheet, insert a Page Number field in the centre of the worksheet’s footer. Suggested solution: On the Insert tab, in the Text group, click Header & Footer. Under Header & Footer Tools,on the Design tab, click Go to Footer. Click the centre section of the footer. On the Design tab, click Page Number.

On the Budget worksheet, enter the word draft to the left section of the header. Suggested solution: On the Insert tab, in the Text group, click Header & Footer. The header is activated. Click the left section of the header. Type draft.