MCAS Practice Test Excel (70-602) Q1-10 of 25

(Last Questions Updated: 2010-02-21 Fixed 1st Q/4th Q)
Part II Q11-Q20 Part III Q21-Q25 Solutions

In the following weeks, I will keep posting practice questions/solutions about MCAS.
The questions are inspired from the practice test provided by Arashi (Website in Chinese). Most of the sample data are obtained from SQL Server Adventure works sample database.

---

Instructions
- Each questions consists of 1 to 5 sub-tasks.
- The "Excel format" below is built with editGrid. Those excel could be downloaded for practice. By clicking on the small triangle at the left hand conner of each table, and then click on "Download Excel" option.
- Practice File could also be downloaded with this link
- Column Name / Cell Range are in italic format
- Manual Entries are marked in blue color e.g. Vendors

*Important: the exercises belows by no means copying from actual exam questions, it's just for practice purpose. There is no guarantee on pass even if you finish all the questions below. However, if you know about all of the question, it will be too hard for you to get a low score.

**Welcome for Feedback! please feel free to leave a message at the link in the bottom of this blog.

---

Question 1

Complete the following TWO tasks:
1. Remove the duplicated entry of Vendor Name(Column B) from the range "A3:K159".
2. Name the cell range "B4:B107": Vendors.

---

Question 2

Complete the following TWO tasks:
1. Copy the format of range "A3:G14" of sheet "NY" to the range "A3:G14" of sheet "CA".
2. Mark the workbook as final.

---

Question 3
No sample Excel provided for this question,
Create a new a workbook for this task.
Complete the following TWO tasks:
1. In the current worksheet, hide the gridlines and the formula bar.
2. Enable share of the current workbook. (Notes: Accept all default settings)

---

Question 4

Complete the following FOUR tasks:
1. Convert the range "A3:E15" to table with table style "Table Style Light 1".
2. Restrict cells in range "E4:E15"("Region") to allow up to 2 characters could be entered.
3. Setup an alert. If the entry in "E4:E15" is more than 2 character, the following message will be shown immediately: Up to 2 characters could be entered.
4. convert the table to normal range.

---

Question 5

Complete the following THREE tasks
1.Apply "blue data bars" conditional formatting to the cell range "F4:F17" (2004).
2.Add the "Clear all" button to the quick access toolbar.
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.

---

Question 6

Complete the following TWO tasks:
1.Apply "Table style light 8" Table style to range "A3:G19".
2.Save the workbook in the XML based and marco-enabled format.

---

Question 7

Complete the following TWO tasks:
1.Magnify the view of current worksheet to 125%.
2.Unhide all the hidden worksheets.

---

Question 8

Complete the following TWO tasks
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.
2.Inspect the workbook, remove all document information and personal information". (Notes Accept all default settings)

---

Question 9

Complete the following TWO tasks.

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).
2.Open a new window containing a view of current workbook, and then arrange the windows horizontally.

---

Question 10

Complete the following TWO tasks:

1.Keep the first row fixed while scrolling through the rest of the worksheet.
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).