2010-02-20

MCAS Practice Test Excel (70-602) Suggested Solutions

(Last Updated 2010-02-28)
Belows are the suggested solution of the practice test.
The steps below is not the only answer.
There may have other ways or even shortcut leading to the same result.
Part I Q1-Q10 Part II Q11-Q20 Part III Q21-Q25

Question 1.
1. Remove the duplicated entry of Vendor Name(Column B) from the range "A3:K159".
Solution:
  1. Highlight the range "A3:K159".
  2. Data Ribbon > Data Tools Group > Remove Duplicates
  3. Make sure that ONLY the box of "Vendor Name" (or Column B) is checked. (Uncheck all others) and then click on OK.
2. Name the cell range "B4:B107": Vendors.
  1. Highlight the range "B4:B107".
  2. Formulas Ribbon > Defined Name Group > Define name.
  3. Enter "Vendor" in the Name box and then click on OK.

Question 2.
1. Copy the format of range "A3:G14" of sheet "NY" to the range "A3:G14" of sheet "CA".
  1. Highlight the range "A3:G14" of "NY".
  2. Home Ribbon > Clipboard Group > Format Painter
  3. Select worksheet "CA" and then Highlight the range "A3:G14".
(Exam Hints: To paste format to multiple targets, remember to double click the format painter when "copy" format. Reference )

2. Mark the workbook as final.
  1. Highlight the range "B4:B107".
  2. Office Button > Prepare Group > Mark as final
  3. Click on OK when you see the message: "This workbook will be marked as final and then saved." (Accept all other default settings if there are any)
Question 3.
1. In the current worksheet, hide the gridlines and the formula bar.
  1. View Ribbon > Show/Hide Group > uncheck this two boxes:  Gridlines / Formula bar
2. Enable share of the current workbook.
  1. Review Ribbon > Changes Group > Share Workbook.
  2. On Editing Tab, check the box "Allow change by more than one user at the same time...".
  3. Press OK to save the workbook.
Question 4.
1. Convert the range "A3:E15" to table with table style Table Style Light 1.
  1. Select the range "A3:E15".
  2. Home Ribbon > Styles Group > Format as Table 
  3. Select the style "Table Style Light 1" on the top left hand conner.
2. Restrict cells in range "E4:E15"("Region") to allow up to 2 characters could be entered. 
  1. Select the range "E4:E15" selected.
  2. Data Ribbon > Data Tools Group > Data Validation
  3. On Settings Tab, locate the "allow" drop down box and then select "Text length".
  4. On the same Tab, locate the "data" drop down box and then select "less than or equal to".
  5. On the same Tab, locate Maximum box, enter "2" in the box.
  6. Press OK to complete the settings.
3. Setup an alert. If the entry in "A3:E15" is more than 2 character, the following message will be shown immediately: Up to 2 characters could be entered.
  1. Repeat step 1 and 2 of the last sub tasks.
  2. On the 3rd tab "Error Alert", locate the "Error Message" Text box, and then enter the message "Up to 2 characters could be entered".
  3. Press OK to complete the settings.
4. convert the table to normal range.
  1. Select the range "A3:E15".
  2. Table Tools (Design) Ribbon > Tools Group > Convert to Range
  3. Click "Yes" to accept the convertion.
Question 5.
1. Apply "blue data bars" conditional formatting to the cell range "F4:F17" (2004).
  1. Select the range "F4:F17".
  2. Home Ribbon > Styles Group > Conditional Formating > Data Bars > Blue Data Bars
  3. Select the style "Table Style Light 1" on the top left hand conner.
(Exam Hints: As the time for exam is a little bit tight, it's better to take a look at the colors available. It's not necessary to memorise it, but go through all the color once could help prevent error. As an example, in the above question, selected "Light blue color" instead of "Blue color" will result in marks deduction.)

2. Add the "Clear all" button to the quick access toolbar.
  1. Locate this button on the top left hand conner.
  2. Select All commands from "Choose commands from" drop down box.
    And then locate "Clear All".
  3. Click on the "Add >>" button.
  4. Press OK to complete the settings.
3. Execute a compatibilty check, so that the Excel is compatible with earlier versions of Excel. Make sure incompatible functionlity checking results are copied to a new worksheet.
  1. Office Button > Prepare Group > Run Compatibility Checker
  2. Click on the "Copy to New Sheet".
Question 6.
1. Apply "Table style light 8" Table style to range "A3:G19". 
  1. Select the range "A3:G19".
  2. Home Ribbon > Styles Group > Format as Table 
  3. Select the style "Table Style Light 8".
2. Save the workbook in the XML based and marco-enabled format
  1. Office Button > Save as Group > Excel Macro-Enabled workbook
  2. Accept the current worksheet name and press "Save".
Question 7.
1. Magnify the view of current worksheet to 125%. 
  1. View Ribbon > View Group > Zoom
  2. Type 125 on the "Custom" textbox and then press "OK"
2. Unhide all the hidden worksheets.
  1. Home Ribbon > Cells Group > Format > Hide & Unhide > Unhide Sheet
  2. Double click the sheet name displayed.
Question 8.
1. insert subtotals to the range "A5:A16", find out the sum of "Sales Amount" (E5:E8) for each "Territory" (F5:F8). During setup of subtotals, create a page break between each group.
  1. Highlight the range "A5:A16".
  2. Data Ribbon > Outline Group > Subtotal
  3. In the "at each change in" dropdown box, select "Territory".
  4. In the "use function" box, make sure "sum" is selected.
  5. In the add subtotal check box list, make sure "Sales Amount" is checked on.
  6. Checks the checkbox "Page Break Between groups" and then press "OK".
2. Inspect the workbook, remove all document information and personal information".
  1. Office Button > Prepare Group > Inspect Document
  2. Click on yes to save the workbook if asked.
  3. Uncheck all inspection options besides "document properties and personal information"
  4. Click on "Inspect"
  5. Click on the remove all button for "document properties and personal information"
  6. Press on "close"
    Question 9.
    1. On the "2010" worksheet, insert a function in cell "B15" that averages the salary rate from sheet "2009" (B5:B17) and sheet "2010" (B5:B13).
    1. Enter the following formula In the cell B15 of worksheet "2010"
      =AVERAGE('2009'!B5:B17,'2010'!B5:B13).
    2. Open a new window containing a view of current workbook, and then arrange the windows horizontally.
    1. View Ribbon > Window Group > New Window
    2. View Ribbon > Window Group > Arrange All
    3. Select "Horizontal" and then press OK
    4. Click on "Inspect"
    5. Click on the remove all button for "document properties and personal information"
    6. Press on "close"
    Question 10.
    1. Keep the first row fixed while scrolling through the rest of the worksheet. 

    1. View Ribbon > Window Group > Freeze Panes > Freeze Top Row

    2. Vertically split between Phone (Column E) and EmailAddress (Column F). Keeping Phone Column appears on left side. Displaying City (Column J) on the right of Phone (Column E).
    1. Select the column F
    2. View Ribbon > Window Group > Split
    3. Make use of scroll bar in lower side, slide it to the right side until the column J put next to column E
    Question 11.
    1. Insert a function in cell G2 that counts only those customers owned 2 or more cars "G5:G29".
    1. Enter the following formula in cell G2
      =COUNTIF(H2:H25,">=2")
    2. Set the sheet tab color as "Orange, Accent 6, Lighter 40%".
    1. Home Ribbon > Cells Group > Format > Tab Color 
    2. Select "Orange, Accent 6, Lighter 40%" (4th Color in the most right hand column)
    Question 12.
    1.Connect the shape "Partial College" to shape "High School" with an arrow of 3PT width.
    1. Insert Ribbon > Illustration Group > Shapes
    2. Line (2nd group) > Arrow (2nd shape from the left)
    3. Connect the line to the edge of box one by one.
      Connect the end of line to the middle of each edges.
      (On selection of line, the end point will become red in color as show in image below)
    2. Copy the "CustomerEdProfile" worksheet to a new workbook
    1. Home Ribbon > Cells Group > Format > Move or copy sheet 
    2. On "To Book" drop down box, select "new book"
    3. Switch on the "Create a copy" check box in lower position of the dialog.
    4. Press OK.

    Question 13.
    1. In Range country code "D4:D184", change the conditional formatting (Red Color) so that it is applied where the cell value is equal to US.
    1. Home Ribbon > Styles Group > Conditional Formatting
    2. Highlight Cell Rules > Text that Contains
    3. Enter "US" in the box on the left, Select Red Text on the downdown box.
    4. Press OK
    2. Clear all conditional formattings from current worksheet.
    1. Home Ribbon > Styles Group > Conditional Formatting
    2. Clear Rules > Clear Rules form the enire work sheet
    3.Create a custom list contain the following words: "US","CA","DE","UK","AU".
    1. Office Button > Excel Option (At the lower side of the menu)
    2. On the current popular page, click on "Edit Custom List..." button
    3. Enter "US,CA,DE,UK,AU" (excluding the double quote) on the "list entires" box
    4. Click On "Add" and then click on OK
    Question 14.
    1. On the current worksheet, apply the Opulent theme colors to the existing theme.
    1. Page Layout Ribbon > Theme Group > Colors
    2. Select Opulent from the list (The list is in alphabetical order).
    (Exam Hints: Remember to select Colors but not Themes/Fonts, 2. Protect the worksheet with password mcas, with allowing users to edit the content in range "D4:D20".
    all of them has list of selections in similar names.)

    1. Review Ribbon > Changes Group > Allow Users to Edit ranges
    2. Press "New" on the right hand side.
    3. Enter "=D4:D20" in the refers to cells.
    4. Press OK to return to the "Allow Users to Edit Ranges" dialog.
    5. Click on the "Protect Sheet..." button on the lower left hand conner.
    6. On the password to unprotect sheet box, enter "mcas".
    7. Press OK, and then enter the same password again.
    (Exam Hints: Please be noticed that password is CASE SENSITIVE)
    Question 15.
    1. format the range A3:E17 on the current worksheet with table style medium 4 (Notes:accept all default settings).
    1. Highlight the range "A3:E17".
    2. Home Ribbon > Styles Group > Format as Table
    3. Select the style "Table Style Medium 4" on the 2nd group.
    2. Apply conditional formatting 3 symbols (Circled) to the range changes (E4:E17).
    1. Select the range "E4:E17".
    2. Home Ribbon > Styles Group > Conditional Formatting
    3. Icon Sets > Three Symbols (Circled)
    3.Filter the table(A3:E17) to display Changes(E4:E17) values with cross icon only.
    1. Click on the "drop down arrow" of the header of table in E4
    2. Select "Filter by color" > Select cross icon from the group "Filter by cell Icon"
    (Exam Hints: Please be noticed that password is CASE SENSITIVE)

    Question 16.
    1. Format the chart on the current worksheet with the chart style style 41.


    1. Highlight the Chart in column "E:J".
    2. Chart Tools (Design) Ribbon > Chart Styles Group
    3. Look for the style 41 and then click on it.
    2.Resize the chart, set width as 9cm and height as 10cm.


    1. Keep the chart selected
    2. Chart Tools (Format) Ribbon > Size Group
    3. Set the height and width accordingly
    3.Format the range C3:C11 with table style style medium 1.


    1. Highlight Range "C3:C11"
    2. Home Ribbon > Styles Group > Format as Table
    3. Select "Style Medium 1"
    4.Enable totals row for the table in C4:C11.


    1. Keep the table selected
    2. Table Tools Ribbon > Table Style Options Group
    3. Switch on the "Total Row" selection
    5.Modify the cell C12 in totals row to use the Average function.
    1. Select cell C12, a drop down arrow would appears
    2. Select "average" from the drop down selections
    Question 17.

    1. On the USDExRate worksheet, insert a formula in cell D4 that display the text yes if the average exchange rate of ARS in 2003 C4 is higher than that in 2003 B4 otherwise the text no should be displayed.


    1. Enter the following formula in D4
      =IF(C4>B4,"YES","NO")
    2. Display the formula in the current worksheet
    1. Formulas Ribbon > Formula Auditing Group > Show formula
      (Exam Hints: shortcut key to display formula: Ctrl+` (ctrl + shift + ~) )
    Question 18.
    1. In the cell D6, insert a formula that multiply the value in C6 to absolute referenced value in exchange rate F2.


    1. Insert the following formula in cell D6
      =C6*$F$2
    2.Copy the formula of cell D6 to range D7:D12.


    1. Select C6
    2. Home Ribbon > Clipboard Group > Copy
      or Ctrl + C
    3. Select D7:D12
    4. Home Ribbon > Clipboard Group > Paste
      or Ctrl + V
    3.Remove all the comments from the table A5:D12.
    1. Select the range "A4:D12"
    2. Home Ribbon > Editing Group > Clear > Clear Comment
    4.Save the workbook with the filename TirePriceEuro in the default working folder so that it is compatible with earlier versions of Excel. (Note: Accept all other default settings.)


    1. Office button > Save As Group > Excel 97-2003 Workbook
    2. Enter "TirePriceEuro" in the file name entry, and then click on save
    Question 19.

    1. on the current worksheet, insert a function in cell E2 that sum the number of cars. owned (E6:E55) for only those customer with an occupation (B6:B55) of Clerical.


    1. Insert the following formula on cell E2
      =SUMIF(B6:B55,"Clerical",E6:E55)
    Question 20.  
    1. On the current worksheet, insert a function in cell G3 that references the value in cell G2 and returns the associated state name (C4:C380) from the cell range B4:C380 based on an exact match. 
    1. Insert the following formula on cell G3=VLOOKUP(G2,B4:C380,2,FALSE)
    Question 21.  
    1. On the current worksheet, insert a chart that compare the products (Column B) with Total Cost Total (Column C), Sales Amount (Column D) and Profit/Loss (Column E) as a clustered cylinder chart (notes: accept all default settings). 
    1. Highlight range B4:E11
    2. Insert Ribbon > Charts Group > Column > Clustered Cylinder Chart (Cylinder Group)
    2. Move the chart to new worksheet (notes: accept all default settings).


    1. Select the Chart created in the first task
    2. Chart Tools (Design) Ribbon > Location Group (right hand most selection) > Move Chart
    3. Select New sheet radio box and then click on OK
    3. Format the chart on the current worksheet with the chart style 10.
    1. Keep the chart created in first task selected
    2. Chart Tools (Design) Ribbon > Chart Styles Group
    3. Click on the "style 10" form the style lists
    Question 22.  
    1.On the chart of current worksheet, insert a data point display. The data point Labels should and position outside the end of data point.Question 23.  


    1. Select the Chart in the current sheet.
    2. Chart Tools (Layout) Ribbon > Labels Group > Data Labels > Outside End
    2.Remove the primary horizontal axis title, and then display a rotated title for primary vertical axis title.
    1. Select the Chart in the current sheet.
    2. Chart Tools (Layout) Ribbon > Labels Group > Axis Title >
      Primary Horizontal Axis Title > None
    3. Chart Tools (Layout) Ribbon > Labels Group > Axis Title >
      Primary Vertical Axis Title > Rotated Title


    1. On the current worksheet, sort the table(A3:E13) by the hire date(Column C) in ascending order and then by employee name (Column A) in ascending.
    1. Select the Range A3:E13
    2. Data Ribbon > Sort & Filter Group > Sort
    3. Make sure "My data has headers" (Upper Right hand conner) has been switched on
    4. In the first row of the sort by, select "Hire Date", and then select "A to Z" for the Order
    5. Press "add level" on the upper left hand conner, a new row of sort by appears
    6. select "employee name" in the new row of sort by, and then select "Z to A" on Order for this "employee name" row.
    2. Format the image on the right by Bevel picture effect "Divot".
    1. Select the image placed over the range of F3
    2. Picture (Format) Ribbon > Picture Styles Group > Bevel Group
    3. Select "Divot" from the styles

    Question 24.  
    1. On the current wokrsheet, insert a function in cell B21 that counts the number of Promotions(A4:A19).
    1. Enter the following formula in B21
      =COUNTA(A4:A19)
    Question 25.  
    1. Convert the text in range A4:A19, which seperated by commas "," into 3 columns.
    1. Select the range A4:A19
    2. Data Ribbon > Data Tools Group > Text to column
    3. On "Text to column Dialog - Step 1 " Select "Delimited" and then press next
    4. On "Text to column Dialog - Step 2 " Switch on the "Comma" in the Delimiters checkbox, remove all others.
    5. Press Finish when done 
    2. Format the converted range into table with table style style 9.
    1. Highlight the range "A3:C19"
    2. Home Ribbon > Styles Group > Format as table
    3. Select "style 9" from the style lists
    3. On the current table, apply the first and last column table style.
    1. Select the table created in task 2 ("A3:C19")
    2. Table Tools (Design) Ribbon > Table Styles Options Group
    3. Switch on the checkbox of "First Column" and "Last Column"

    You are ready for the real Exam!!
    Posted by at
    Labels: ,

    2 comments:

    1. AnonymousMarch 21, 2011 at 4:41 PM

      It's very kind of you to post all practice Q & A. This is the best site about MOS Excel exam. Thanks a million!

      ReplyDelete
    2. AnonymousJune 21, 2015 at 2:13 PM

      Sorry but i am unable 2 understand the Q about subtotals... plz help me out……

      ReplyDelete

    Subscribe to: Post Comments (Atom)