Transcript
Excel 2000 Expert
QUESTION 1:
Import the tab-delimited text file "Spare parts" from the C:\ Certkiller \MyDocuments.
Answer:
Step 1: Click Data->Get External Data->Import Text File. Navigate to C:\ Certkiller \MyDocuments Step 2: Select the Spare parts text file and click Import. Click Next twice.
Step 3: Click Finish.
Step 4: Select Existing Worksheet. Click OK
QUESTION 2:
Create a new database query that will import the "Prices by Manufacturer" query from the "Car tracker" database in the C:\ Certkiller \MyDocuments folder. Return the data to the current worksheet.
Answer:
Step 1: Choose Data->Get External Data->New Database Query. Step 2: On the Datbases tab, select MS Access Database. Click OK. Step 3: Select the CarTracker.mdb database. Click OK.
Step 4: In the Available tables and column list, select Prices by Manufacturer. Click the > button. Step 5:.Click Next three times.
Step 6: Select Return Data to Microsoft Excel. Click Finish. Select Existing worksheet and click OK.
QUESTION 3:
Create a new workbook based on the Books template
Answer:
Step 1: Click File>New.
Step 2: Click the General tab. Select Books.xlt.
Step 3: Click OK.
QUESTION 4:
Save the current workbook as a template in your Templates folder.
Answer:
Step 1: Click File>Save As.
Step 2: In the Save as type box, select Template.
Step 3: Click Save.
QUESTION 5:
Apply the Accounting format with two decimal places to G6:L20.
Answer:
Step 1: Select cells G6:L20.
Step 2: Click Format>Cells. Click the Number tab.
Step 3: Select Accounting in the Category list.
Step 4: If necessary, type 2 in the Decimal places box.
Step 5: Click OK.
QUESTION 6:
In cell B20, enter a VLOOKUP function that will reference the loan amount and interest rate in range A3:J12.
Answer:
Step 1: Select cell B20. Click Insert>Function.
Step 2: Select Lookup & Reference in the Function category list.
Step 3: Scroll down to select VLOOKUP in the Function name list. Click OK. Step 4: In the Lookup_value box, type A4.
Step 5: In the Table_array box, type A3.J12.
Step 6: In the Cool index num box, type 3 and click OK.
QUESTION 7:
Hide the Formatting toolbar. Display the Forms toolbar.
Answer:
Step 1: Click Views>Toolbars.
Step 2: Select Formatting.
Step 3: Click View>Toolbars.
Step 4: Select Forms.
QUESTION 8:
Record a clear in the current workbook named NoFormat that will clear the formats of the selected text. Use an already blank cell to record the clear.
Answer:
Step 1: Click Tools>Clear>Record New Clear.
Step 2: In the Clear name box, type NoFormat.
Step 3: In the Store clear in box, select This Woorkbook. Click OK.
Step 4: Click Edit>Clear>Formats.
Step 5: Click the Stop Recording button on the Clear toolbar.
QUESTION 9:
Select cells B10:E10 and run the Reformat sales.
Answer:
Step 1: Select cells B10:E10.
Step 2: Click Tool>Sales>Macros.
Step 3: Select Reformat in the Sales name list and click Run.
QUESTION 10:
Use the Trace Precedents button on the Auditing toolbar to find the cells that contribute to the formula in cell C7 on the Qualification worksheet.
Answer:
Step 1: Click the Qualification worksheet tab.
Step 2: Select cell C7. Click Tools>Auditing>Show Auditing Toolbar.
Step 3: Click the Trace Precedents button on the Auditing toolbar.
QUESTION 11:
Use the Trace Dependents button on the Auditing toolbar to find the cells that depend on the value in cell A12.
Answer:
Step 1: Select cell A12.
Step 2: Click Tools>Auditing>Show Auditing Toolbar.
Step 3: Click the Trace Dependents button on the Auditing toolbar.
QUESTION 12:
Use Auto Outline to automatically outline the worksheet.
Answer:
Step 1: Click Data>Group and Outline>Auto Outline.
QUESTION 13:
Create a new database query that will import the Used Cars table from the CarTracker Access database in the C:\DDCMyDocs folder. Return the data to the existing worksheet in cell A1.
Answer:
Step 1: Click Data>Get External Data>New Database Query.
Step 2: Click the Database tab. Select MS Access Database* and click OK.
Step 3: Navigate to C:\DDCMyDocs, if necessary, and select the CarTracker database. Click OK. Step 4: In the Available tables and columns list, select Used Cars and click the > button. Click Next. Step 5: Click Next twice. Select Return Data to Microsoft Excel and click Finish.
Step 6: Select Existing worksheet selected and click OK.
QUESTION 14:
Apply data validation to cells D13:F21 as follows: the values entered must be decimals greater than 1000. Add an input message to appear when the cell is selected. Set Band Income as the title and Must be at least $1000 as the message.
Answer:
Step 1: Select cells D13:F21. Click Data>Validation.
Step 2: Click the Settings tab. In the Allow box, select Decimal.
Step 3: In the Data box, select greater than. In the Minimum box, type 1000. Step 4: Click the Input Message tab, type Band Income in the Title box. Step 5: Type Must be at least $1000in the Input Message box.
Step 6: If necessary, check the Show input message when cell is selected check box. Click OK.
QUESTION 15:
Create a PivotChart on a new worksheet. Add Location Code as a Category Field, Manufactured as a Series Field, and Cost as a Data Item
Answer:
Step 1: Click Data>PivotTable and PivotChart Report.
Step 2: Select PivotChart (with Pivot Table) and click Next. Click Next again and click Finish. Step 3: Use the PivotTable toolbar to perform the next three steps.
Step 4: Drag Location Code to the Drop More Category Fields Here box at the bottom of the worksheet. Step 5: Drag Manufacturer to the Drop More Series Fields Here box on the right side of the worksheet. Step 6: Drag Cost to the Drop Data Items Here box in the center of the worksheet.
QUESTION 16:
Save the current worksheet as an interactive Web page with PivotTable functionality. Save the page as UsedCarsPivot in C:\DDCMyDocs.
Answer:
Step 1: Click File>Save as Web Page.
Step 2: In the File name box, type: UsedCarsPivot
Step 3: Select the Selection: Sheet option and check the Add interactively check box. Step 4: Click Publishing.
Step 5: In the Viewing Options section, select PivotTable functionality in the Add interactivity check box. Step 6: Click Publishing.
QUESTION 17:
Add the comment Up 15% from last month to cell F19. Then select cell B3.
Answer:
Step 1: Select cell F19.
Step 2: Click Insert>Comment.
Step 3: Type: Up 15% from last month
Step 4: Select cell B3.
QUESTION 18:
Protect the active sheet for all three categories, but do not add a password.
Answer:
Step 1: Click Tools>Protection Sheet.
Step 2: Leave all three categories checked and click OK.
QUESTION 19:
In the Properties dialog box for this workbook, set the Company to Mortgage World.
Answer:
Step 1: Click File>Properties.
Step 2: Click the Summary tab.
Step 3: In the Company box, type: Mortgage World
Step 4: Click OK.
QUESTION 20:
Save the active workbook with the file name Confidential in the C:\DDCMyDocs folder. Set 2583 as the password to open the workbook.
Answer:
Step 1: Click File>Save As. Navigate to C:\DDCMyDocs.
Step 2: At top of the Save As dialog box, click Tools>General Options.
Step 3: In the Password to open box, type 2583 and click OK.
Step 4: In the Reenter password to proceed box, type 2583 and click OK.
Step 5: In the File Name box, type Confidential and click Save.
QUESTION 21:
Turn on Track Changes for this workbook. If prompted to save, do so.
Answer:
Step 1: Click Tools>Track Changes>Highlight Changes.
Step 2: Check the Track changes while editing check box.
Step 3: Click OK.
Step 4: If prompted, click OK to save the workbook.
QUESTION 22:
Set this workbook to allow changes from multiple users at the same time. If prompted to save, do so.
Answer:
Step 1: Click Tools>Share Workbook.
Step 2: Check the Allow changes by more than one user at the same time check box. Click OK. Step 3: If prompted, click OK to save the workbook.
QUESTION 23:
Merge the active workbook, with the Music3 workbook in C:\DDCMyDocs. If prompted to save, do so.
Answer:
Step 1: Click Tools>Merge Workbooks.
Step 2: If prompted, click OK to save the workbook.
Step 3: Navigate to the C:\DDCMyDocs folder.
Step 4: Select the Music3 workbook and click OK.
QUESTION 24:
Ad a second conditional format to B8:M15 that will apply a Red font color to the cells with values less than $10,000.
Answer:
Step 1: Select Cells B8:M15. Click Format>Conditional Formatting. Click Add. Step 2: In the second box of the Condition 2 section, select less than.
Step 3: In the third box of the Condition section, type 10000.
Step 4: Click Format. In the Color box, select Red (3rd row, 1st column) and click OK. Step 5: Click OK,
QUESTION 25:
Use the Print button on the Standard toolbar to print the first two worksheets with one print command (Nothing will actually print)
Answer:
Step 1: Hold Ctrl and click the Region, East, West worksheet tab.
Step 2: Click the Print button on the Standard toolbar.
QUESTION 26:
Display the Auditing toolbar.
Answer:
Step 1: Click Tools>Auditing>Show Auditing Toolbar.
QUESTION 27:
Use the Trace Error button on the Auditing toolbar to find the source of the error in cell D23.
Answer:
Step 1: Select cell D23.
Step 2: Click Tools>Auditing>Show Auditing Toolbar.
Step 3: Click the Trace Error button on the Auditing toolbar.
QUESTION 28:
Sort cells A9:F14 first by Percentage Used in descending order, then by New Rate in ascending order.
Answer:
Step 1: Select cells A9:F14. Click Data>Sort.
Step 2: In the Sort by box, select Percentage Used.
Step 3: To the right of the Sort by box, select Descending.
Step 4: In the first Then by box, select New Rate.
QUESTION 29:
Use the Subtotal form to add each change in the Selling Price and Cost columns grouped by Manufacturer. Replace the current groups and add a summary below the data.
Answer:
Step 1: Click Data>Subtotal.
Step 2: In the At each change in box, select Manufacturer.
Step 3: In the Add subtotal to list, check the Selling Price and Cost check boxes. Step 4: Leave Replace current subtotals and Summary below data checked. Click OK.
QUESTION 30:
Apply a filter to the worksheet. The criteria used for the filter is listed in the worksheet. Which command should you use.
Answer:
Step 1: Click in the list.
Step 2: Click Data>Filter->Advanced Filter.
Note: Excel automatically detects the list.
Step 3: Select the Criteria range.
Step 4: Mark the criteria cells in the worksheet.
Step 5: Return to the Advanced filter dialog box.
Step 6: Click the OK button.
