How To Pass Odesk Excel 2003 Test Part 1 - 2012

How to Pass Excel 2003 Odesk Test with Flying Colors 

It is always wise to revise for any oDesk test before trying it out. Try to understand the topics and even apply them in your daily office life. This gives you a hands-on experience of what you are dealing with and gives a better position to enable you answer questions even when they are twisted.

I wish you all the best.

How to copy a formula

When you copy a formula, the cell references may change based on the type of cell reference that you use.

Example:

Odesk Excel 2003 Absolute Referencing

• For a formula being copied, if the reference is $A$1, it is absolute column and absolute row. It changes to $A$1
• For a formula being copies, if the reference is A$1, it is relative column and absolute row. It changes to C$1
• For a formula being copies, if the reference is $A1, it is absolute column and relative row. It changes to $A3
• For a formula being copied, if the reference is A1, it is relative column and relative row. It changes toC3.

$A$1 creates an absolute reference to cell A1. By adding $ in front of both A and 1, then no matter where the formula is moved/copied to, it will reference A1

Displaying with more than 12 characters in Excel

When you enter a very large number into a cell it is displayed e.g. 77889867810070809 is displayed as 8E+16 no matter how large you may try to make the cell width. To display a number larger than 12 characters you need to first format the cell before you enter the number by:-

• Right clicking  the empty cell/cells to enter the number
• Click format cells
• In the number tab, select Number and click OK
• Then enter the value and it will not be formatted with scientific notation but will be formatted as a number   

Autofilter 

One of the common things that you do with data in Excel is to filter the data based on different field values. To make this functionality available to you when using lists, AutoFilter drop-down lists are automatically added in the header row of a list when the list is created.

Adding comment to a cell in excel 2003

Comment are notes that you enter for a cell and when added, it displays a red triangle in the cell.

• Click on the cell to comment on
• On the Insert menu, click Comment
• Type in the comment text
• Then click outside the comment  box

Compare side by side toolbar

This allows you to easily and quickly compare two workbooks and do a line-by-line comparison of two workbooks.

      The Process of comparing side by side

• Open worksheet2011
• Open worksheet 2012
• Form the Window menu, chose Compare Side-by-side with worksheet2011.xls
• Scroll through the worksheet2012.xls workbook. (the worksheet 2011.xls workbook will automatically scroll to the corresponding row)
• Click the Reset Windows Position button on the Side-by-Side toolbar when you want to reset the workbook windows to their original positions
• When you are through with the comparing , click the close Side-By-Side button in the toolbar

Information Rights Management 

IRM available in Office Outlook allows individuals to specify access permission to e-mail messages. It prevent sensitive information from being printed, forwarded, or copied by unauthorized people. IRM prevents restricted content from being copied by using the Print Screen feature. IRM gives the same level of restriction to email- attachments created by Microsoft Office programs.

However IRM can't prevent content from being erased, stolen, or captured and transmitted by malicious programs like trajons. IRM can't protect the content from being corrupted  by a computer virus. IRM can't prevent a recipient to take a digital photograph of the restricted content when it is displayed on the screen. IRM can't protect against copying of the restricted content by use of third-party-capture programs.

Message with restricted permission that you receive can be identified by an icon that appears next to the message list of your inbox as shown below.

Odesk Excel 2003 Information Rights Management

Smart Tags

Smart Tags help to recognize certain data and to give action options based on the type of data recognized. Actions are made available with a button that appears near the cell that contains the recognized data. The button appears when the cell is activated or when you move the mouse point over the cell

Smart tags remains in a cell when the formatting of the cell is changed, when the rows or columns are inserted or deleted around the cell, when the cell is moved, cut, or copied and then pasted, when the cell is auto-filtered, hidden or rearranged because of a sort operation.

Smart tags do not remain after the data in a cell is changed or deleted, after the data in a cell is pasted over and after new data is dragged into a cell.

Combining two cells text into one cell  when A1 John is and B1 is Smith

You use the ampersand(&) operator in a formula to combine text from multiple cells into one cell.
Enter the formula by typing = and then select the first cell that contains the text that you want to combine and type &","& or &"_"& or &"."& or &""& depending on what you want to display as the following examples show.
        Examples

        =A1&","&B1 - This will display John,Smith

        =A1&"_"&B1 - This will display John_Smith

        =A1&"."&B1 - This will display John.Smith

        =A1&""&B1 - This will display JohnSmith

Date and time functions

DATE: Returns the serial number of a particular date

DATEVALUE: Converts a date in the form of text to a serial number

DAY: Converts a serial number to a day of the month

DAY360: Calculates the number of days between two dates based on a 360-day year

EOMONTH : Returns the serial number of the last day of the month before or after a specified number of months

MONTH: Converts a number into month

NOW: Returns the serial number of the current date and time

TIME: Returns the serial number of a particular time

TIMEVALUE: Converts a time in the form of text to a serial number

TODAY: Returns the serial number of today's date

YEAR: Converts a serial number to a year

Shared Workspace

This is an area, hosted by a web server, where colleagues can share documents and information, keep each other up to date on ongoing projects etc.

Odesk Excel 2003 Shared Workspace Status Tab Tab.JPG

The status tab , lists important information about the current document. The following information is usually listed:-

• Whether the document is up to date
• whether the document is in conflict with another member's copy
• Whether the document is checked out
• It also lists your Information rights Management (IRM) permissions

Odesk Excel 2003 Shared Workspace Status Tab Tab.JPG

 The document information tab,  display properties associated with the document. the following information is usually listed:-

• When the document was last modified
• If the document library where the document is stored defines custom properties for documents (those custom properties are also displayed in the document information tab)

Dealing with Confidential data in Excel

If your worksheet contains sensitive information that you don't want people in the office to see as you work on you computer, you can hide the sensitive cells on a the worksheet so that these values, although present in the worksheet, should remain hidden and as a result the cells containing the confidential data appear blank, but a value appears in the formula bar when you click one of the cells. Use the the following procedure: -

• Select the cells to hide
• On Format menu, chose cells
• Under Number tab, in the Category box, click Custom
• Type ;;; (three  semicolons)
• Click OK

Running and Stopping a Macro

Running a macro
You can write or record a macro so that it run automatically when Microsoft excel starts.

•  You can record a macro and save it using the name Auto_open
• Open the workbook to create a macro for
• On Tools menu, point to Macro,  and then click Record New Macro
• In the Macro Name box, type Auto_Open
• In the Store Macro In box, specify where to save the macro
• To save the macro in the current workbook, choose This Workbook (remember to move the file to XLStart folder)
  
• To Create a new workbook to save the macro in , choose New Workbook (remember to move the file to XLStart folder)
  
• To save the macro in a hidden workbook that is automatically loaded when you start Excel, choose Personal Macro Workbook, and click OK
  
• Click Stop Recording button
  
• You can write the macro in a Microsoft Visual Basic of Applications (VBA) produce for the OPEN event of a workbook
• Open only the workbook to add the macro
• On Tool menu, point to Macro, and then click Visual Basic Editor
• In the Project Explorer window, right click the This Workbook object and then click View Code on the shortcut menu
• In the object list above the Code Window, select Workbook which creates an empty Open event procedure, where you only add few codes, so that it look like this (added material in in blue).

                       Private Sub Workbook_Open( )
                                     MsgBox Now

                                     Worksheets("Sheet1").Range("A1").Value=Now

                                      Private Sub Workbook_Open

                       End sub

• Switch to Excel and save the workbook. Close and reopen the workbook. Excel runs the workbook_open procedure, which displays current date and time in the message box (remember to save this workbook in XLStart folder)
  

Stopping a Macro

You can stop a currently running macro by pressing ESC, and then click End in the Microsoft Visual Basic dialog box. to prevent automatically running a macro when Microsoft excel start, hold down SHIFT during startup

Mode Function

Mode function is used to calculate the most frequent value in a series to enable one to report on the most common value present in a particular series.

• If a series contains two or more sets of values represented equally, the value represented first in the series would be the result.
  
• If no value is represented more than once in a series, excel display " #N/A" in the cell.. You can change this result by using
  =IF(ISNA(MODE(series)),desired_value,MODE(series)) formula
• Replace 'series' with the data series to calculate and replace 'desired_value' with the value to display in the cell if a series does not contain any duplicate elements.
  
• 0- is  the result displayed if  IF(ISNA(MODE(B1:B4)),0,MODE(B1:B4) is applied to B1:27, B2:22, B3:28 and B4:21

 
Now that you have revised along these lines, give these questions a trial.

Odesk Excel 2003 Questions and Answers

Question 1

Which of the following statements is true if the cell B1 contains the formula =$A$1

a.The value of cell B1contains the same number of characters as the value of cell B2

b.There is an absolute reference to cell A1 so that the formula =$A$1 can be copied to other cells without changing the reference to A1

c. There is a relative reference to cell A1 so the formula will change each time it is copied to other cells

d. The value of cell B1 is copies only once, further changes in value of A1 will not effect the value of B1

Question 2

You have entered 4/6 as data in a cell without applying any formats to it. By default, Excel will treat this data entry as a:

a.fraction

b.Date

c.Number

d.Text

Question 3

Which of the following options would be used if you need to insert a 77889867810070809 number in a cell?
a. place  the character T before the number e.g. T77889867810070809

b. Place the characters TX before the number e.g. TX 77889867810070809

c.Enclose the number is brackets e.g. (77889867810070809)

d. apply the Test Format to empty cells, and then type the numbers 

Question 4

 This question is based upon the figure shown below

Odesk Excel 2003 Standard Toolbar Hyperlink Button

The figure show a view of the Standard Toolbar. What function is performed by the button marked by the letter 'A'?

a. It is used to publish the worksheet on a web browser for viewing over the internet

b. It is used to make the worksheet universally shareable

c. It is used to add a hyperlink

Question 5

AutoFilter drop-downs are automatically added in the header row of a list when the list is created.

a. True

b. False

Question 6 

This question is based upon the figure shown below

Odesk Excel 2003 Comment Red Triangle

The figure shows an Excel worksheet. What does the red triangle in cell C2 signify?

a. The cell is locked

b. The data entered in the cell will be validated to see if it matches the criteria set

c. The cell has been marked so that it can be referred to in case of need

d. There is a comment associated with the cell

Question 7

This question is based upon the figure shown below

Odesk Excel 2003 Compare Side by Side Toolbar

The given figure shows the 'Compare Side by Side ' toolbar. What function is performed by the button marked A?

a.It allows you to open a new workbook for comparison

b. It allows synchronous scrolling of the workbooks

c. It allows you to stop comparing workbooks

d. It allows you to reset the workbook windows to the positions they were in when you first start comparing workbooks

Question 8

Which of the following statement is true if the author has created the content with restricted permissions using IRM

a. Prevent an unauthorized recipient from accessing e-mail content, including attachments.

b. Prevent restricted e-mail content from being copied by using the Print Screen feature in Microsoft Windows 

c. Restricted e-mail content from being copied by using third-party screen-capture programs

d. None of the above

Question 9

A smart tag will be removed from a cell when ___________________________.

a. The cell is hidden

b. the cell is moved

c. The data in the cell is changed or deleted

d. The formatting of the cell is changed

Question 10

This question is based upon the figure shown below

Odesk Excel 2003 Drawing Toolbar

The figure shows a view of the Drawing Toolbar. What function is performed by the button marked by letter' X'?

a. It inserts a Diagram or Organization chart in the worksheet

b. It is used to rotate the picture in the worksheet to the desired angle

c. It inserts clip-art in the worksheet

d. It applies 3-D style to the picture in the worksheet

Question 11

 suppose the value in cell A1 is John and B1 is Smith then which of the following function you will use to get the John_Smith in C1 cell?

a. =A1&","&B1

b. =A1&"_"&B1

c. =A11&"."&B1

d. =A1&""&B1

Question 12

Which of the following Date and Time function you will use to return the serial number of the last day of the month before or after a specified number of months?

a. DATEVALUE

b. EOMONTH

d. TIMEVALUE

d. EDATE

Question 13
This question is based upon the figure shown below

Odesk Excel 2003 Shared Workspace Status

 As shown in the figure what information will be enlisted by the tab marked as "B" about the current document?
a. It displays custom properties of the current document

b. It displays whether the current document is up to date or not

c. It displays when the current document was last modified
d. It displays whether the current document is in conflict with another member's copy
e. It displays your Information Rights Management permissions

Question 14

You have created a worksheet which consists of confidential data. You want that these values, although present in the worksheet, should remain hidden and as a result the cells containing this data should appear blank in the worksheet. Which method will help you do this?
a. Select the cells, the data of which you want to hide. Click Cells on the Format menu and choose the Hide option
b. Select the cells, open the Window menu and click on Hide
c. Select the cells, open the Data menu and click on Filter

d. select the cells, the data of which you want to hide. Click cells on the Format menu and then the Number tab. In the category list click Custom, type three semicolons(;;;) in the Type box.

 Question 15
 You start Microsoft Excel and you do not want it to automatically run a a macro upon starting. What will you do?

a. Hold down the SHIFT key during startup

b. Hold down the CTRL key during startup
c.Hold down the ESC key during startup
d.Hold down the ESC + CTRL key during startup

Question 16

This question is based upon the figure shown below

Odesk Excel 2003 Freeze Row & Column

The figure shows an Excel worksheet. If you want to freeze the row showing the months (row 1) and the column showing the products (column A), what should you do?
a. Select the cells A2 to A6 and cells B1 to F1 and click on freeze Panes under the menu Windows->Freeze panes

b. Select the cell B2 and click on Freeze Panes under the menu Window->Freeze panes

c. Select the cell A1 and click on Freeze panes under the menu windows->Freeze panes

d. Select the cells F1 and F
6 and click on Freeze Panes under the menu Window->Freeze Panes
e. None of the above

Question 17
Which of the following shortcuts can be used to insert a new line in the same cell?
a. Ctrl+Enter
b. Enter

c. Alt+Enter

d. Ctrl+L

Question 18
You have recorded a macro. By mistake you recorded an action in it which you do not want. How will you remove the unwanted action without having to  record the whole macro again?
a. By using the Find and Replace option in the Edit menu
b. By clicking on the Refresh button in the Macro toolbar
c. It cannot be edited, the macro will have to record again without the unwanted actions

d. by opening the macro in the Visual Basic Editor and then removing the actions which are not required

Question 19
This question is based upon the figure shown below

Odesk Excel 2003 Forms Toolbar

Which toolbar is shown in the figure?

a. Forms toolbar

b. Standard toolbar
c. Formatting toolbar
d. Chart toolbar
e. WordArt toolbar

Question 20
What will be the output of the function IF(ISNA(MODE(B1:B4)),0,MODE(B1:B4)) when it is applied to the data series given below?

B1:27
B2:22
B3:28
B4:21

a. #N/A
b. 27

c. 0

d. Error

Please send in you comments about this post. Watch out for How to Pass Odesk Excel 2003 Part 2.