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.