How To Pass Odesk Excel 2003 Test Part 2 - 2012

Hallo there,

I hope that you have revised on yesterday' topics, questions and answers on Excel 2003 Odesk Test. Today I am going to exhaust more on the same test so that you can be well equipped for the test. Ensure that you thoroughly revise on these topics. When you do I promise you that you will definitely pass the exam. If you like this blog, please be a follower to enable you to be getting the updates as they come through your email.

Research Task Pane

This feature helps to search certain local and remote data sources from within the office 2003. It  helps one to query reference books like encyclopedias or other online resources. It helps search reach sites such as Bing.

To access use Research Task Pane, go to Tools menu, then click Research, you can choose to search from All Reference Books, under which you will get Encarta and several thesaurus. You can also search All Research sites, under which you will get Bind, Factiva iworks and Highbean research. Available also is getting services of Office Marketplace. You can also configure your Research options as preferred.

Now Function

Now function returns the current system date and time. This function will refresh the date and time value whenever the worksheet recalculates. The syntax for the Now function is =now().

• You can change the display to show just the date by using =today() function
• You can change the display to show just the time only by:-
• right clicking the cell, 
• click on Format Cells
• Under Number tab, select Time in the Category windows
• Pick from the available formats in the Type Window

Using  slanted Text in Excel 2003

Sometimes the column headings are too large and look out of place in their cells and the data appears not presentable. You can display the heading as slanted text to save space and come up with presentable layout by following the procedure below:-

• To rotate the text within  a cell, select the cells that contains the data you want to rotate 
• On the Format menu, click Cells
• On the Alignment tab, under Orientation, click or drag the indicator to the angle that you want, or type an angle in the Degrees box

How to sum cells from a different workbook

To do this,  you have to construct a formula that makes reference to each cell and each sheet in the workbook..

Example
There is a workbook names Sales.xls which has a worksheet named Quarterly. The worksheet contains the quarterly sales figures for the company in cells A3 to A6. Now you want to create a formula in a different workbook that reads the quarterly sales figures and adds them up while the Sales workbook is open

Process

•  Open Sales.xls workbook which has the quarterly worksheet
• Then open a new workbook, from which to calculate the cells A3 and A6
• Enter =Sum( like in the figure below.

Excel 2003 Summing Cells From Different Workbook

          

• Then move your mouse pointer to the sales workbook, in the quarterly worksheet and select the cells A3 to A6 as shown below.

Excel 2003 Summing Cells From Different Workbooks

          

• When you press enter key,  what is below will be displayed; showing full formula for the operation, which is =SUM([sales.xls]quarterly!$A$3:$A$6) in the formula bar and  the Total of the cells which  is 180.

Excel 2002 Summing Cells From Different Workbooks .JPG

           

Format Painter 

This is a standard toolbar button with a  paintbrush icon that is used to apply text formatting and graphics formatting like borders and fills. You can copy formatting from a picture as long as the wrapping style of the picture is set to something else except In Line With Text (on the Drawing toolbar, click Draw, and then Text Wrapping)


Format Painter cannot copy the font and font size on WordArt text.

Text To Columns Function in Excel

If you copy data to Excel from other programs, excel may condense several columns to a single columns. Text to Columns on Data  menu is used to put each record in a separate column. You can only convert one column of data at a time. Te delimiter can be a comma, space, tab or semicolon. It depends what delimiter is separating your data. The Text To Columns function  is found under Data menu

Date Validation in Excel

Data validation is used to define restrictions on data on what should or should not be entered in a certain cell in order to prevent invalid entries by displaying an error message like the one below:-

Excel 2003 Data Validation Error Message

To do data validation, select the cell or cells to validate, then under Data menu, choose validation.


You can use data validation to do the following

1. Restrict data to predefined items in a list. For example, you can limit types of departments to Sales, Finance and IT in a cell(s).
2. Restrict numbers outside a specific range. For example, you can restrict the values that can be entered into a cell so that only whole numbers between 1 and 100 can be entered
3. Restrict dates outside a certain time frame. For example you can create a time frame between today's date and 3 days from today's date.
4. Restrict times outside a certain time frame
5. Limit number of text characters
6. Validate data based on formula or values in other cells

Autocomplete Feature

Excel completes text entries that you start to type in a column of data, if the first few letters that you type match an existing entry in that column. you can turn autocomplete off by click the Microsoft Office Button, and then click Excel Option.. Click Advanced, and then under Editing Options, select or clear the Enable AutoComplete For All Cell Values check box to turn this option on or off. Autocomplete does not work if the cells contains only numbers ,dates and time.

AutoFit Selection Feature

By default columns do not automatically accommodate to fit the information they contain, but using AutoFit Selection you can set on or more columns to automatically fit the contents.  You can changes the column width to fit the contents by doing the following: -

• Select the column or columns to modify
• Under Format menu, hover over Columns and then click on Autofit Selection

XLStart folder in Excel

To automatically open a specific workbook when you start Excel, you can place that workbook in the XLStart folder which is a startup folder in excel. The XLStart folder was created when Excel was initially installed. 

Compare Side by Side Toolbar

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

You can only compare two workbooks side by side in Excel 2003. 

Process

• Open the two workbooks you want to compare side by side
• On the Window menu, click Compare Side by Side With ( just after this, the other workbook to be compared with will be displayed)
• After  you are through with the scrolling of the two workbooks
• Click Close Side by Side to stop comparing workbooks.
  

On Compare Side by Side toolbar you can do the following:-

To scroll through the workbooks at the same time, by clicking  Synchronous Scrolling.

Excel 2003 Synchronous Scrolling

To reset workbook windows to the positions they were in when you first started comparing workbooks, by clicking Reset Window Position.

Excel 2003 Reset Window Position

Errors in Formulas

###### 

• Occurs when a column is not wide enough to display the content
• Occurs when dates and times are negative numbers

#VALUE! - Occurs when the wrong type of argument or operand is used, for example:-

• Entering text when the formula requires a number or a logical value, such as TRUE or FALSE
• Entering or editing an array formula, and then pressing ENTER
• Entering a cell reference, a formula, or a function as an array constant
• Supplying a range to an operator or a function that requires a single value, not a range
• Using a matrix that is not valid in one of the matrix worksheet functions
• Running a macro that enters a function that returns #VALUE!

#DIV/0! - Occurs when a number is divided by zero

•  Entering a formula that contains explicit division by zero e.g. =5/0
• Using a cell reference to a blank cell or a cell that contains zero as a divisor
• Running a macro that uses a function or a formula that returns #DIV/0!

#NAME? - Occurs when Excel does not recognize text in a formula

•  Using a name that doesn't exist, misspelling a name or misspelling the name of a function
• Using a label in a formula, without labels being allowed
• Omitting a colon(:) in a range reference
• Referencing another sheet not enclosed in a single quotation marks
• Using a function that is part of the Analysis Toolpak add-in, without the add-in being loaded

 #N/A - Occurs when a value is not available to a function or formula

• Missing data, and #N/A or NA() has been entered in its place
• giving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function
• Using the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table
• using an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula
• Omitting one or more required arguments from a built-in or custom worksheet function
• Using a custom worksheet function that is not available
• Running a macro that enters a function that returns #N/A

$REF! - Occurs when a cell reference is not valid

•  Deleting cells referred to by other formulas or pasting moved cells over cells referred to by other formulas
• Using a link to a program that is not running
• Linking to a Dynamic Data Exchange (DDE) topic such as "system" that is not available
• Running a macro that enters a function that returns #REF!

 #NUM#!- occurs with invalid numeric values in a formula or function

•  Using an unacceptable argument in a function that requires a numeric argument
• Using a worksheet function that iterates, such as IRR or RATE, and the function cannot find a result
• Entering a formula that produces a number that is too large or too small to be represented in Excel

 #NULL! - occurs when you specify an intersection of two areas that do not intersect. The intersection operator is a space between references

• Using an incorrect range operator
• Ranges do not intersect

Understanding Set Print Area and Printer Selection

Print Area- Use Set Print Area sub-command of the Print Area command in the File menu so that you will be able to print a block of data from an excel worksheet, or print a select range of pages within a worksheet. If you forget to clear Print Area command, and print,  the whole worksheet, you still get the previously selected print area as the printout

Printer Selection - If you do not want to clear the Print Area you had set and you want to print a different selection from the one you had set, you can select a range of cells, go the Print What section of the Print dialog box and check the Selection button .This will print the selected range of cells and not the Set Print Area

Document Workspaces

When using Document workspaces, you and your colleagues can work together on a Document Workspace site to develop a document in the following ways: - 

• You can work directly on the copy located on the Document Workspace site.
• You can work on a local copy and update the copy regularly on the Document Workspace site  

Putting Multiple Line of Writing in Each Cell

• Using Keyboard combination

• Double click in the cell to enter the multiple line. Type the first line and hold down the "Alt" key and hit the "Enter" (ALT+ENTER)and type the next line. Repeat these steps to enter additional line into the cell.

• Using Format Cell
• select the cells, Click Format menu, select Cells, and under Alignment tab, click Wrap Text in the Text Control section,  and then click OK

Vlookup (Vertical Lookup)

Vlookup searches for a value in the 1st column of a table array and returns a value in the same row from another column in the table array 

Table_array - Two or more columns of data. Use a reference to a range or a range name. The values in the 1st column of table_array are the values searched by lookup_value and they can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

Col_index _num - The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array, and col_index_num of 2 returns the value in the second column in table_array and so on. If col_index is less than 1,  VLOOKUP returns the #VALUE! error code. If col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value

Lookup_value - The value is search in the first column of the table_array. It can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value

Questions and Answers to Odesk Excel 2003 Test

Question 21

State whether True of False

With the help of the Research task pane you can conduct research on Topics using an encyclopedia, Web search and also by accessing their-party content

a. True

b. False

Question 22
What does the NOW( )function return?

a. Returns the serial number of the current data and time

b. Returns the serial number of the current date
c. Returns the serial number of the current time
d. None of the above

Question 23
You are making a sales performance report in Excel which you would like to present to the top management. Some of the column headings are too large and look out of place in their cells. You decide to display the headings as slanted text to save space. How can this be achieved?
a. By selecting Slanted Text on the Format->Cells->Font tab

b. By altering the Text Orientation on the Format->Cells->alignment tab

c. By selecting Superscript on the Format->Cells->Font tab
d. By choosing Center Across selection on the Format->Cells->Alignment tab

Question 24
There is a workbook names Sales.xls which has a worksheet named Quarterly. The worksheet contains the quarterly sales figures for the company in cells A3 to A6. Now you want to create a formula in a different workbook that reads the quarterly sales figures and adds them up while the Sales workbook is open. Which of the following is the correct formula to do this?
a. =SUM[Sales.xls]Quarterly!A3:A6
b. = Sales.xls!SUM(A3:A6)

c. =SUM([Sales.xls]Quarterly!A3:A6)

d. =Sales.xls[Quarterly]SUM(A3:A6)


Question 25
This question is based upon the figure shown below

Odesk Excel 2003 Standard Toolbar Paintbrush

The figure show a view of the Standard Toolbar. What function is performed by the bottom marked by the letter 'Z'?
a. It is used to add a background color to the selected cell
b. It is used to add a colored boundary around the selected cell

c. It copies the formatting properties from one cell to another

d. It deletes the values of all the selected cells


Question 26
This question is based upon the figure below

Odesk Excel 2003 Test To Columns Menu Option

The figure show the Text to Column menu option. What function does it perform?

a. It allows you to add comma separated text into various cells automatically

b. It converts the selected text to a table
c. It automatically resizes the cell size whenever a large text is added into the cell.

Question 27
You would like to restrict the values that can be entered into a cell so that only whole numbers between 1 and 100 can be entered . Which option in the menu allows you to accomplish this?

a. The Settings tab under the menu Data->Validation

b. The Settings tab under the menu Data->Filter->Advanced Filter
c. The Setting tab under the menu Format ->Cells
d. The Settings tab under the menu Format->Conditional Formatting


Question 28
How can you select all the blank cells in your worksheet?
a. This must be done manually as it is not automated through any built-in feature
b. Chose Edit->Select->blank under the main menu

c. Choose Blanks in the Edit ->Go TO->Special menu option

d. Choose Blanks in the View->Go To->Special menu option

Question 29
How does the AutoComplee feature in Excel help you save time?
a. It automatically completes abbreviated words
b. It completes text and numeric entries that match an existing entry in the same column
c. It completes text entries that match an existing entry in the same worksheet

d.It complete text entries that match an existing entry in the same column

Question 30
What is the quickest way to select all the columns on a worksheet?
a. Click on the column heading of the first column, press Shift, and then click on the column heading of the last column
b. Click on the column heading of the first column, press Ctrl, and then click on the column heading of the last column
c. Choose Edit-> Select all from the main menu

d. Click on the gray rectangle on the upper left corner of the worksheet where the column headings and row headings meet.

Question 31
This question is based upon the figure shown below

Odesk Excel 2003 Test To Columns Menu Option

The figure show the AutoFit Selection menu option. What function does it perform?
a. It automatically re-sizes the column width to the minimum necessary to fit the contents of the selected cell.
b. It automatically re-sizes the column height to the minimum necessary to fit the contents of the selected cell.
c. It automatically re-sizes the column height and width to the minimum necessary to fit the contents of the selected cell.
d. It automatically compacts the text to fit into the original column size.

Question 32
Is the following statement true of false?
If a workbook is placed in the XLSTART folder, then the same workbook will open each time Excel is launched.

a. True

b. False

Question 33
What function does the 'Compare Side by Side With' Command on the Window perform?

a. It allows you to compare two worksheets simultaneously in a single workbook

b. It allows you to scroll through two or more workbooks simultaneously

c. It allows you to scroll through only two workbooks simultaneously to identify the difference between them

d. It only allows you scroll through more than two worksheets in a single workbook simultaneously.

Question 34
You select the row heading 10, 11 and 12 and then choose the menu option Insert->Rows. What will happen?
a. 3 new rows will be inserted after row 12
b. 1 new row will be inserted after row 12

c. 3 new rows will be inserted after row 9

d. 1 new row will be inserted after row 9

Question 35
Which of the following function would return a value of 8?
a. roundup(8.4999,0)
b. rounddown(8.499,0)
c. round(8.4999,0)
d. a and c

e b and c

Question 36
 What should you add before a fraction to avoid entering it as a date, e.g. 1/3, in Excel?
a. F
b. //
c. zero
d. FR

e. zero space

Question 37
 This question is based upon the figure shown below.

Odesk Excel 2003 Smart Tag

 What does the purple triangle at the bottom right corner of the cell signify?

a. Formatted cell
b. Text

c. Smart tag

d. Hidden data

Question 38
Which of the following errors appears when an invalid argument is passed while converting a number system from another system?
a. #N/A
b. Blank cell appears
c. # ERROR

d. #NUM

Question 39
You define a print area in your worksheet, but later you select a smaller range of cells to print and then click Selection under the menu File->Print->Selection. What will happen?
a. The print area will be printed

b. The selected area will be printed

c. Excel will prompt you to de-select the selected area 

Question 40

When using Document Workspaces, you cannot work directly on the Document Workspace copy, but you can work on your own copy which you can update periodically with changes that have been saved to the copy on the Document Workspace site.

a. True

b. False

Question 41
You have entered text in a cell which is too big for the cell. You want the text to appear in multiple line inside the same cell. What will you do?
a. Start typing in the cell and press the Enter key when you want to start another line

b. Use the Wrap Text option in the format -> Cells -> Alignment menu

c. Use the Shrink to Fit option in the Format -> Cells - > Alignment menu

Question 42

When using the VLOOKUP function, the error #REF! will appear in a cell if _______________

a. The loolup_value is not found in the first column of the table_array

b. The col_index_num argument is greater than the number of columns in table_array

c. The table_array argument included empty columns on the left of the table_array

Question 43

What value is displayed if the formula =2+"$9.00" is entered into a cell?
a. $11

b. 11

c. 2+$9.00
d. $11.00

Your comments are very much needed. If you like this blog please be a follower so that you can be getting updates as soon as they come through you email.

Thanks.

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.