select predict(DREAM,1) from [LIFE]

MOS 2010 Excel Expert Beta Exam Information (77-888)

2010-12-24T00:07:00.006+08:00

The Excel Expert beta exam is accepting registration now.

The beta exam is free, but you will face more questions than that from actual exam.

The exam content objectives be found from this link:

Just taken the exam today, I think is about 80% tougher than the "specialist" one.
Besides formula building, more steps were needed for each questions to be done.
(In my opinion, this exam do have more advanced topics than that in 2000~2003 experts exam, e.g. array formula)

When preparing for this exam, make sure that you have some experience on those sub-objectives
(i.e. "This objective may include but is not limited to...")
You will meet MOST (almost all) of them in the exam.
Besides, just like all other MS exams, there should have some questions about 2010 new features.

Here are the objectives that "expanded" for a little bit
Sharing and Maintaining Workbooks

•Apply workbook settings, properties, and data options.
This objective may include but is not limited to: setting advanced properties, saving a workbook as a template, and mapping/importing/exporting XML data

•Apply protection and sharing properties to workbooks and worksheets.
◦This objective may include but is not limited to: protecting the current sheet, protecting the workbook structure, restricting permissions, and requiring a password to open a workbook

•Maintain shared workbooks.
◦This objective may include but is not limited to: merging workbooks and setting Track Changes options

Applying Formulas and Functions
•Audit formulas.
◦This objective may include but is not limited to: tracing formula precedents, dependents, and errors, locating invalid data or formulas, and correcting errors in formulas

•Manipulate formula options.◦This objective may include but is not limited to: setting iterative calculation options and enabling or disabling automatic workbook calculation

•Perform data summary tasks.◦This objective may include but is not limited to: using an array formula and using SUMIFS function (Remember the brothers of SUMIFS)

•Apply functions in formulas.◦This objective may include but is not limited to: finding and correcting errors in functions, applying arrays to functions, and using lookup, Statistical, Date and Time, Financial, Text, and Cube functions
(Common function from each of the above categories)

Presenting Data Visually
•Apply advanced chart features.◦This objective may include but is not limited to: using Trend lines, Dual axes, chart templates, and Sparklines, "Dynamic Chart" (i.e. chart with filtered data)

•Apply data analysis.◦This objective may include but is not limited to: using automated analysis tools and performing What-If analysis (i.e. Scenario/Goal Seek), Data Consolidation

•Apply and manipulate PivotTables.◦This objective may include but is not limited to: manipulating PivotTable data and using the slicer to filter and segment your PivotTable data in multiple layers

•Apply and manipulate PivotCharts.◦This objective may include but is not limited to: creating, manipulating, and analyzing PivotChart data

•Demonstrate how to use the slicer.◦This objective may include but is not limited to: choosing data sets from external data connections ("External" means pivot table that other than currently selected)

Working with Macros and Forms
•Create and manipulate macros.◦This objective may include but is not limited to: running a macro, running a macro when a workbook is opened, running a macro when a button is clicked, recording an action macro, assigning a macro to a command button, creating a custom macro button on the Quick Access Toolbar, and applying modifications to a macro

•Insert and manipulate form controls.◦This objective may include but is not limited to: inserting form controls and setting form controls properties

----
Last Updated at: 2010-12-24

Excel 2010: Where is the "Office button -> Prepare" in Excel 2010?

2010-06-12T21:21:00.000+08:00

The Prepare Tab in office button of Excel 2007 has been reorganized completely into Info Tab of File menu in Excel 2010.

If you are going to take MOS 2010 Exam, it is better to take a look at the new locations:

Properties: (The most "hidden" one) Now you can go to advanced properties with fewer click(s).

Inspect Documents

Encrypt Documents, Add a Digital Signature & Mark as Final

The Encrypt Document is the same as Tools->General Options->Password to Open in the Save As dialog box

Run Compatibility Checker:

Excel 2007/2010: "World Cup 2010" Excel Template: Dynamic Image

2010-06-06T18:01:00.005+08:00

A good template that demonstrated how to use a dynamic image.
World Cup 2010 Excel template
In the "Group Stage" sheet, the content of image on the right side is based "Group Standings" sheet.
There is a formula in the image to redirect content from specified cell location.

MOS 2007 logo changes

2010-06-02T23:46:00.004+08:00

Just a very short blog about MCAS (Microsoft Certified Application Specialists)

This month, MCAS title and logo has been changed back to Microsoft Office Specialists

New logo

Old logo

Excel: Wired sorting results in Pivot Table and solution

2010-05-16T00:26:00.000+08:00

Summary

You may see that the sorting on text column going strange like below.

The Left side is a normal data range. The right side is a pivot table that with source from A1:A7

Possible Cause

The problem has been caused by sorting with "custom list" under PivotTable.

The "Custom List" is used to instruct the Excel the sorting order of a list of items.

An example custom list: "JAN", "FEB", "MAR"

should be sorted as "JAN", "FEB", "MAR", but not alphabetically "FEB", "JAN", "MAR"

There are two types of "custom List", one is Excel built-in (including Months,Day of weeks...), another type is user defined.

The "Custom List" could be reviewed or adjusted with following location:

Excel 2010: File->Option->Advanced->Edit Custom Lists (General Section)

Excel 2007: Office button->Excel Options->Popular-> Edit Custom Lists (button)

Excel 2000/XP/2003: Tools->Option->Custom Lists (Tab)

Symptoms

Assume that we feeding a list of 3 alphabet into a pivottable to sort: "AAA","AAB"..."AAZ","ABA"...."ZZZ"

The "sorted" result from Pivottable would become this:

You could see the list is started with "Months" JAN but not AAA

Resolution and Workaround

Excel 2007/2010

A. To switch off the custom list sorting on pivot table

Right Click the PivotTable-> PivotTable Options
Totals & Filters-> remove the check box from "Use Custom Lists when sorting"

B. To switch off the individual column custom list ordering

Right Click the Field required -> Sort->More Sort Option
In the "Sort Options", Make sure "Ascending" or "Decending" selected.
Click on the "More Options" Button
Remove the checkbox: "Sort automatically every time the report is updated"
Select "No Calculation" in first key sort order
Press OK twice

Excel 2003

C. To switch off the individual column custom list ordering

Right click the Field required, go to Data Menu->Sort
Click on Options button, select "normal" in custom sort order
Press OK twice

Excel PivotTable Autofilter on data value

2010-04-16T00:30:00.002+08:00

When working with pivot table,
you may find the Autofilter feature for data value is disabled from Pivot table.

2010

2003

To take the autofilter for data value, first, select the cell near the header like below image.

After that, go to Data Ribbon(2007/2010)/Menu(2003/XP)->filter again,

you would see that the Autofilter feature is back

2010

2003

However, beware that the Total value are not affected by the autofilter function.

2010

2003

If showing total value for filter data required, it could be achieved by setting up some helper formulas and then add it to page filter.

For Programmers
If your existing application are accessing the old preview box.
you can still access the old preview mode. It's also not inside the 2010 object model change list:



ActiveWindow.SelectedSheets.PrintPreview



Formula Recipe:Random order list
2010-04-03T16:40:00.000+08:00
To transform a given list of item in random order, the following formula could be used:

RAND - for creating random number

ROW - used with RAND to generate random number

LARGE - To match out the list from ranking.

VLOOKUP - used with LARGE for locate exact position of item



Assume the list of Target is located in column B





Step1: add formula in A2:

=INT(RAND()*1000)+ROW()/10000

the "ROW()/10000" is used to prevent duplicated random number.Even the rand() result is 0, the default ranking would be row number. The 10000 is the possible number of items in the list.



Step 2: add formula in D2:

=LARGE(A$2:A$21,ROW()-1)



To generate sorted list of rank

Step 3: add formula in E2

=VLOOKUP(D2,A$1:B$21,2,FALSE)

Based on the sorted list of rank, look for the sorted list of targets.



Step 4: extend the formula in A2,D2 and E2 to number items by copy and paste



Finish!





Excel 2010: Print Preview Full Screen (Old Print Preview)
2010-04-02T21:19:00.005+08:00
For Users



From menu controls, the new print preview mode has completely replaced the old print preview.

The new print preview mode offer a streamlined "quick printing" in one screen:

Selecting printer, number of copies, paper orientation,... All in One



However, convenience control "margin manual tuning" (as below) is missing from the new mode.





Actually, the old print preview is still not removed.

You can add it to the quick access bar, or any ribbon (new ribbon customization feature in Office 2010)



The below steps demonstrate how to release the old print preview (Print Preview Full Screen) method to the quick access bar



Step 1:Click on the more 




































Step 2:


Finish, Click this button to launch the old print preview screen:




For Programmers
If your existing application are accessing the old preview box.
you can still access the old preview mode. It's also not inside the 2010 object model change list:




ActiveWindow.SelectedSheets.PrintPreview






Microsoft Certified Application Specialist(MCAS) credential change
2010-03-12T23:02:00.000+08:00
According to wendyj, the Microsoft Certified Application Specialist (MCAS) will be rebranded to Microsoft Office Specialist (MOS) at June/2010. 



It seems that the new brand (MCAS) like MCITP doesn't heard by many employers. 

Many of the employer's still only knows about MCSE but not MCITP (We can find it out from the recruit).





Besides, there are far too more MOSs than MCASs in the world.



Anyway, there will have new logos for the MOS. 

About the old MCAS certificates or cards with MCAS logo, you may use them for lighting up the BBQ stove. :)





Office 2010 Code (VBA) Compatability Inspector
2010-02-21T17:43:00.000+08:00
To check whether existing VBA codes supported by Office 2010 or not,

instead of re-compiling the VBA codes, Microsoft now offering a new inspector for you.



Gray has written a short guide about on how to use the inspector.

If your macros are password protected, remember to unlock it before launching the inspector.



However, this tools is still in beta. I have encountered serveral false alarm about "Range" objects.



As the tools are in beta, you can send any feedback to the development team. 

There is also a sweeptakes on bug reporting.





MCAS Practice Test Excel (70-602) Suggested Solutions
2010-02-20T18:20:00.021+08:00
(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:

Highlight the range "A3:K159".
Data Ribbon > Data Tools Group > Remove Duplicates
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. 

Highlight the range "B4:B107".
Formulas Ribbon > Defined Name Group > Define name.
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".

Highlight the range "A3:G14" of "NY".
Home Ribbon > Clipboard Group > Format Painter
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.

Highlight the range "B4:B107".
Office Button > Prepare Group > Mark as final 
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.

View Ribbon > Show/Hide Group > uncheck this two boxes:  Gridlines / Formula bar
2. Enable share of the current workbook.

Review Ribbon > Changes Group > Share Workbook.
On Editing Tab, check the box "Allow change by more than one user at the same time...".
Press OK to save the workbook.
Question 4. 

1. Convert the range "A3:E15" to table with table style Table Style Light 1.

Select the range "A3:E15".
Home Ribbon > Styles Group > Format as Table 
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.  

Select the range "E4:E15" selected.
Data Ribbon > Data Tools Group > Data Validation
On Settings Tab, locate the "allow" drop down box and then select "Text length".
On the same Tab, locate the "data" drop down box and then select "less than or equal to".
On the same Tab, locate Maximum box, enter "2" in the box.
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.

Repeat step 1 and 2 of the last sub tasks.
On the 3rd tab "Error Alert", locate the "Error Message" Text box, and then enter the message "Up to 2 characters could be entered".
Press OK to complete the settings.
4. convert the table to normal range.

Select the range "A3:E15".
Table Tools (Design) Ribbon > Tools Group > Convert to Range
Click "Yes" to accept the convertion.
Question 5. 

1. Apply "blue data bars" conditional formatting to the cell range "F4:F17" (2004).

Select the range "F4:F17".
Home Ribbon > Styles Group > Conditional Formating > Data Bars > Blue Data Bars
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.

Locate this button on the top left hand conner.
Select All commands from "Choose commands from" drop down box. 

And then locate "Clear All".
Click on the "Add >>" button.
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.
Office Button > Prepare Group > Run Compatibility Checker
Click on the "Copy to New Sheet".
Question 6. 

1. Apply "Table style light 8" Table style to range "A3:G19".  

Select the range "A3:G19".
Home Ribbon > Styles Group > Format as Table 
Select the style "Table Style Light 8".
2. Save the workbook in the XML based and marco-enabled format 

Office Button > Save as Group > Excel Macro-Enabled workbook
Accept the current worksheet name and press "Save".
Question 7. 

1. Magnify the view of current worksheet to 125%.  

View Ribbon > View Group > Zoom
Type 125 on the "Custom" textbox and then press "OK"
2. Unhide all the hidden worksheets.

Home Ribbon > Cells Group > Format > Hide & Unhide > Unhide Sheet
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. 

Highlight the range "A5:A16".
Data Ribbon > Outline Group > Subtotal
In the "at each change in" dropdown box, select "Territory".
In the "use function" box, make sure "sum" is selected.
In the add subtotal check box list, make sure "Sales Amount" is checked on.
Checks the checkbox "Page Break Between groups" and then press "OK".
2. Inspect the workbook, remove all document information and personal information". 

Office Button > Prepare Group > Inspect Document
Click on yes to save the workbook if asked.
Uncheck all inspection options besides "document properties and personal information"
Click on "Inspect"
Click on the remove all button for "document properties and personal information"
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).

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. 

View Ribbon > Window Group > New Window
View Ribbon > Window Group > Arrange All
Select "Horizontal" and then press OK
Click on "Inspect"
Click on the remove all button for "document properties and personal information"
Press on "close"
Question 10. 

1. Keep the first row fixed while scrolling through the rest of the worksheet.  



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).

Select the column F
View Ribbon > Window Group > Split
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".

Enter the following formula in cell G2 

=COUNTIF(H2:H25,">=2")
2. Set the sheet tab color as "Orange, Accent 6, Lighter 40%".

Home Ribbon > Cells Group > Format > Tab Color 
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.

Insert Ribbon > Illustration Group > Shapes
Line (2nd group) > Arrow (2nd shape from the left)
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
Home Ribbon > Cells Group > Format > Move or copy sheet 
On "To Book" drop down box, select "new book"
Switch on the "Create a copy" check box in lower position of the dialog.
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. 

Home Ribbon > Styles Group > Conditional Formatting
Highlight Cell Rules > Text that Contains
Enter "US" in the box on the left, Select Red Text on the downdown box.
Press OK
2. Clear all conditional formattings from current worksheet.

Home Ribbon > Styles Group > Conditional Formatting
Clear Rules > Clear Rules form the enire work sheet
3.Create a custom list contain the following words: "US","CA","DE","UK","AU".

Office Button > Excel Option (At the lower side of the menu)
On the current popular page, click on "Edit Custom List..." button
Enter "US,CA,DE,UK,AU" (excluding the double quote) on the "list entires" box
Click On "Add" and then click on OK
Question 14. 

1. On the current worksheet, apply the Opulent theme colors to the existing theme. 

Page Layout Ribbon > Theme Group > Colors
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.) 


Review Ribbon > Changes Group > Allow Users to Edit ranges
Press "New" on the right hand side.
Enter "=D4:D20" in the refers to cells.
Press OK to return to the "Allow Users to Edit Ranges" dialog.
Click on the "Protect Sheet..." button on the lower left hand conner.
On the password to unprotect sheet box, enter "mcas".
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).

Highlight the range "A3:E17".
Home Ribbon > Styles Group > Format as Table 
Select the style "Table Style Medium 4" on the 2nd group.
2. Apply conditional formatting 3 symbols (Circled) to the range changes (E4:E17). 

Select the range "E4:E17".
Home Ribbon > Styles Group > Conditional Formatting
Icon Sets > Three Symbols (Circled)
3.Filter the table(A3:E17) to display Changes(E4:E17) values with cross icon only.

Click on the "drop down arrow" of the header of table in E4
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.





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





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





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





Keep the table selected
Table Tools Ribbon &gt; Table Style Options Group
Switch on the "Total Row" selection
5.Modify the cell C12 in totals row to use the Average function.

Select cell C12, a drop down arrow would appears
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.





Enter the following formula in D4 

=IF(C4>B4,"YES","NO")
2. Display the formula in the current worksheet

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.





Insert the following formula in cell D6 

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





Select C6
Home Ribbon > Clipboard Group > Copy 

or Ctrl + C
Select D7:D12
Home Ribbon > Clipboard Group > Paste 

or Ctrl + V
3.Remove all the comments from the table A5:D12.

Select the range "A4:D12"
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.)





Office button > Save As Group > Excel 97-2003 Workbook
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.





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. 

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). 

Highlight range B4:E11
Insert Ribbon > Charts Group > Column > Clustered Cylinder Chart (Cylinder Group)
2. Move the chart to new worksheet (notes: accept all default settings).





Select the Chart created in the first task
Chart Tools (Design) Ribbon > Location Group (right hand most selection) > Move Chart
Select New sheet radio box and then click on OK
3. Format the chart on the current worksheet with the chart style 10.

Keep the chart created in first task selected
Chart Tools (Design) Ribbon > Chart Styles Group
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.   





Select the Chart in the current sheet.
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. 

Select the Chart in the current sheet.
Chart Tools (Layout) Ribbon > Labels Group > Axis Title > 

Primary Horizontal Axis Title > None
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.

Select the Range A3:E13
Data Ribbon > Sort & Filter Group > Sort
Make sure "My data has headers" (Upper Right hand conner) has been switched on 
In the first row of the sort by, select "Hire Date", and then select "A to Z" for the Order
Press "add level" on the upper left hand conner, a new row of sort by appears
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".

Select the image placed over the range of F3
Picture (Format) Ribbon > Picture Styles Group > Bevel Group
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).

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.

Select the range A4:A19
Data Ribbon > Data Tools Group > Text to column
On "Text to column Dialog - Step 1 " Select "Delimited" and then press next
On "Text to column Dialog - Step 2 " Switch on the "Comma" in the Delimiters checkbox, remove all others. 
Press Finish when done 
2. Format the converted range into table with table style style 9.

Highlight the range "A3:C19"
Home Ribbon > Styles Group > Format as table
Select "style 9" from the style lists
3. On the current table, apply the first and last column table style.

Select the table created in task 2 ("A3:C19")
Table Tools (Design) Ribbon > Table Styles Options Group
Switch on the checkbox of "First Column" and "Last Column"


You are ready for the real Exam!!





MCAS Practice Test Excel (70-602) Q21~25 of 25
2010-02-13T15:07:00.008+08:00
(Last Questions Updated: 2010-02-28)



Here is the Third part of the MCAS Excel Practice Test series

For instructions, please refer to the Part I , Part II



Question 21



Complete the following THREE tasks:

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).

2. Move the chart to new worksheet (notes: accept all default settings).

3. Format the chart on the discounts worksheet with the chart style 10.







Question 22



Complete the following TWO tasks:

1.On the chart of current worksheet, insert a data point display. The data point Labels should position outside the end of data point.

2.Remove the primary horizontal axis title, and then display a rotated title for primary vertical axis title.



Question 23



Complete the following TWO tasks:

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.

2.Format the image on the right by Bevel picture effect "Divot".



Question 24



Complete the following tasks:

1. On the current wokrsheet, insert a function in cell B21 that counts the number of Promotions(A4:A19).



Question 25



Complete the following THREE tasks:

1. Convert the text in range A4:A19, which seperated by commas "," into 3 columns.

2. Format the converted range into table with table style style 9.

3. On the current table, apply the first and last column table style.





MCAS Practice Test Excel (70-602) Q11~20 of 25
2010-01-31T10:22:00.019+08:00
(Last Questions Updated: 2010-02-28)





Here is the second part of the MCAS Excel Practice Test series

For instructions, please refer to the Part I , next part: Part III



Question 11



Complete the following TWO tasks:

1.Insert a function in cell G2 that counts only those customers owned 2 or more cars "G5:G29".

2.Set the sheet tab color as "Orange, Accent 6, Lighter 40%".



Question 12

Please download the Excel first, the EditGrid can't display shapes.



Complete the following TWO tasks:

1.Connect the shape "Partial College" to shape "High School" with an arrow of 3PT width. 

2.Copy the "CustomerEdProfile" worksheet to a new workbook.



Question 13



Complete the following THREE tasks:



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.

2.Clear all conditional formattings from current worksheet.

3.Create a custom list contain the following words: "US","CA","DE","UK","AU".



Question 14



Complete the following TWO tasks:



1.On the current worksheet, apply the Opulent theme colors to the existing theme.

2.Protect the worksheet with password mcas, with allowing users to edit the content in range "D4:D20".



Question 15



Complete the following THREE tasks:

1.format the range A3:E17 on the current worksheet with table style medium 4 (Notes:accept all default settings).

2.Apply conditional formating 3 symbols (Circled) to the range changes (E4:E17).

3.Filter the table(A3:E17) to display Changes(E4:E17) values with cross icon only.



Question 16



Complete the following FIVE tasks:

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

2.Resize the chart, set width as 9cm and height as 10cm.

3.Format the range C3:C11 with table style style medium 1.

4.Enable totals row for the table in C3:C11.

5.Modify the cell C12 in totals row to use the Average function.



Question 17



Complete the following TWO tasks:

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.

2.Display the formula in the current worksheet.



Question 18



Complete the following FOUR tasks:

1.In the cell D6, insert a formula that multiply the value in C6 to absolute referenced value in exchange rate F2.

2.Copy the formula of cell D6 to range D7:D12.

3.Remove all the comments from the table A5:A12.

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.)



Question 19



Complete the following task:

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.



Question 20



Complete the following task:

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.





MCAS Practice Test Excel (70-602) Q1-10 of 25
2010-01-26T02:03:00.029+08:00
(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).





Wanna some game with Excel?
2010-01-26T00:05:00.000+08:00
Officelabs now offering a game over the actual Excel application.  



The "game" also has a little practice about the "main" new feature of Excel 2010, sparksline.



Your score could also be published to facebook newsfeed.





Step by Step:Excel Watermark (Excel 2010,2007)
2010-01-25T23:47:00.005+08:00
Word has a feature on creating warkmark text, such as "Confidential", "Draft",....

However, there no such direct feature in Excel.



The below steps "mimic" the watermark feature in Excel 2007/2010

1. Select the sheet you want to add the watermark

2. Locate Page setup properties



3. Go to the third tab "Header/footer" Click on the custom headaer





4. a)Enter the watermark text you want in the Center Section.



4. b)Highlight the wording and then Click on "A" button to adjust the font size & light color.

In this sample, font "Arial" of size 48 has been used for the word "CONFIDENTIAL"

Click OK two times to return to page setup (one time for "Font setting" and one time for "Header")





5. Go to the second tab "Margin".

"Push" the header to the centre of the paper, by adjusting the Header position. For A4 paper, it's 5" roughly.





6. Click OK to finish the setting.



Preview of printout: (Screen from Excel 2010)



This trick also applys for image watermark. For detail, please refer to this tutorial "Mimic a watermark in Excel" from Microsoft







Error when querying linked server from 64-bit SQL2005/SQL2008 to 32-bit SQL 2000
2009-12-20T21:03:00.001+08:00
When you got following message when accessing 32-Bit sql2000 linked server

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator. 

Msg 7311, Level 16, State 2, Line 1 

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "". The provider supports the interface, but returns a failure code when it is used. 



Try the method as mentioned in here:



1. Make sure the sql server is upgraded to sp3/sp4



2. Launch the following command from DOS:

osql -E -S \ -i \instcat.sql



Where location is the instance installation path

(Default:C:\Program Files\Microsoft SQL Server\MSSQL\Install)



...It will takes one or few minutes for the sql to finish...



3. When the command is finished, you can run the linked server query again without restarting the servers.





Excel 2010 Object Model Changes
2009-12-18T23:47:00.000+08:00
Seems the VBA changes on Excel 2010 has been fixed.

A official comparsion to 2007/2003 could be found here





Luckly, there are no critical changes like FileFind in 2007.

Most of them are related to conditional formatings.





Excel:1-minute Tricks Series Vol.1:Format Painter Special feature
2009-12-06T14:15:00.001+08:00
Without re-do formatting or re-entry data every time,

"Format Painter" providing a convenient ways on copying formats from one position to others.



Just like copy and paste, select the cells to copy first.

And then, click on the format painter 





Notice that the cursor will become brush style





Select the target location/range, done!





I think many of you already know the above... 

So what about the "special feature"?



You can also paste the format to multiple locations but without copying multiple times.



Double click the format painter when copy.

Even something has been pasted, the button will not be restored. 



To stop the format painter, press "Esc" on keyboard.



This feature also applied to Excel 97 and later on version.



That's all of the first trick!





Office 2010 Beta Activation issue
2009-11-29T22:18:00.011+08:00
When there is a problem on activiting the office beta:

"The setup controller has encountered a problem during install. Please review the log files for further information on the error."





Try the steps below:

1. Launch the setup program "setup.exe" under this location

C:\Program Files\Common Files\microsoft shared\OFFICE14\Office Setup Controller



2. Select "Enter a product key"





3. Click on continue



4. Wait the excel for re-deploy.... and click OK



5. Launch Excel again (or any Office program you like). This option is now back.





6. Wait again for the activiton. Click close when reaching this screen.





7. Restart the Excel. Finish!





Excel VBA Stub: Rearranging Sheets
2009-11-17T00:50:00.005+08:00
Codes for re-ordering worksheet
subSplitAndHideSheets: split and hide sheets that not appeared on list: strSheetsAll

------------------


Sub subLaunch
    dim strSheetsAll as string
    strSheetsAll = "Sheet1;Sheet3;Sheet2"
    call subSplitAndHideSheets(strSheetsAll)
end sub


Sub subSplitAndHideSheets(wbCurr As Workbook, strSheetsAll as string)

    Dim strSheets() As String
    Dim strSheetsSD
    Dim intSheetsCounter As Integer
    Dim intSheetsCount As Integer
    Dim intCurrSheet As Integer


    If strSheetsAll = "" Then
        Exit Sub 'Exit sub if not given
    End If

    Set strSheetSD = CreateObject("Scripting.Dictionary")

    strSheets = Split(strSheetsAll, ";")
    intCurrSheet = 1
    intSheetsCount = UBound(strSheets)
    For intSheetsCounter = 0 To UBound(strSheets)
        If funcChkSheetExist(wbCurr, strSheets(intSheetsCounter)) Then
            strSheetSD.Add (UCase(strSheets(intSheetsCounter))), Nothing
            wbCurr.Sheets(strSheets(intSheetsCounter)).Move before:=Sheets(intCurrSheet)
            intCurrSheet = intCurrSheet + 1
        End If
    Next

    intSheetsCount = wbCurr.Worksheets.Count
    If intSheetsCount <= 1 Then
        Exit Sub
    End If
    For intSheetsCounter = 1 To wbCurr.Worksheets.Count
        If wbCurr.Sheets(intSheetsCounter).Visible = xlSheetVisible Then
        'set as hidden: if Sheet not exist in given string array and name is  not parameters
            If Not strSheetSD.exists(UCase(wbCurr.Sheets(intSheetsCounter).Name)) And _
                UCase(wbCurr.Sheets(intSheetsCounter).Name) <> STRSHEETNAME_PARAMETERS Then
                wbCurr.Sheets(intSheetsCounter).Visible = xlSheetHidden
            End If
        End If
    Next
End Sub

Function funcChkSheetExist(wbCurr As Workbook, strSheet As String)
On Error GoTo errHandling
    Dim tmpVal
    tmpVal = wbCurr.Sheets(strSheet).Range("A1")
    funcChkSheetExist = True
    Exit Function
errHandling:
    funcChkSheetExist = False
End Function






Excel: 此"列"不同彼"列"
2009-11-13T01:21:00.008+08:00
 



神把人的語言分拆了,但人們只懂使其更複雜...

下面的是真人真事.



與香港客戶對話:



我:就是這個"列"有問題吧?

客:什麼列? 這兩"行"有問題...

我:ok,我幫你檢查一下這"行".





與大陸同事對話:



我:可以幫忙刪去這個值(欄)嗎?

同事:刪去這"列"對吧?

我:不是這"列",是這"欄".

同事:刪去這"列"就行了嗎?

我:不是這"列",是這"欄"!!!!

同事:...就是這"列"吧,不知你"欄"是什麼意.

...







...投降啦.我以後還是用英語啦



相信這個是比較特別的例子

其他的會是功能上,但做成亂的機會比較少:

樞紐表vs透視表,宏 vs 宏集 vs 巨集.



(2010-01-26 後記: 日本語版本跟簡體版一樣,也是"行"+"列")





MCAS CM in Japan
2009-09-10T23:43:00.004+08:00
睇下人地幾dum本.細田都以經算係1線演員. 
香港果間代理,學下野啦.











SQL Azure CTP SSMS connection
2009-09-05T16:01:00.005+08:00
To connect to Azure with SSMS:
1. Launch the SSMS 2008
2. When you are prompted for connection, click CANCEL
Otherwise you would go the following message

----
Cannot connect to SERVERNAME.ctp.database.windows.net
----
ADDITIONAL INFORMATION: 
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
-->Invalid object name ’sys.configurations’. (Microsoft SQL Server, Error: 208) 




3. Click on NEW QUERY
https://sql.azure.com/


4. Login to the database with detail provided during activation at 



a. Server name: SERVERNAME.ctp.database.windows.net
b. Authentication: SQL Server Authentication
c. login: LOGIN@SERVERNAME
d. password
e. DB: if you created a DB, select it under option ->connection properties first. 
Since the USE DB_NAME doesn't works...

5. you may just ignore this warning



6. Finish!






Excel 2010 SSAS Cube Sets.
2009-08-14T00:31:00.004+08:00
Following up with SSAS features in Excel 2010

Besides creating sets contain only dimension members or calculated members,

you can them both in the same set:







The magic of the sets could be respected to the new "calculatedmember" property in the connection object:



ActiveWorkbook.connections("...").OLEDBConnection.CalculatedMembers.Add Name:="[Set1]", Formula:= "{....}"