11

Microsoft Office Excel 2003

1. What do you mean by a spreadsheet? What are its features?

Ans.: A spreadsheet is an electronic counterpart of a paper ledger sheet that helps to maintain records and perform calculations. The records may be of different types such as related to accounting information, daily expenses, salary sheet, etc. Before the advent of spreadsheet, such records and calculations were implemented in notebooks and registers and it was very much time-consuming. However, with the advancement of computer into our daily lives, spreadsheets became the popular form of maintaining records by replacing the manual method.

It is used to organize information in a rectangular grid that consists of columns (designated by letters) and rows (designated by numbers). This enables users to organize and examine data in much efficient way. Apart form this, it has got many other features. Some of these features are as follows:

	Ease of use: The spreadsheet programs are easy to learn and use. To learn spreadsheet, formal training is not required.
	Time-saving: There are many inbuilt functions and formulas in spreadsheet that saves our time in performing calculations.
	Error-free: Most of the calculations are performed by the computer, so there is less possibility of error unless we enter wrong data.
	Help option: The spreadsheet programs come with the built-in help option that can be queried to find any functions or any other feature.
	Sorting: It provides the option of sorting data that enable users to sort data in both ascending and descending orders.
	Table and graphics: The spreadsheet programs support the use of tables and graphics that helps in better understanding of the text.
	Password protected: The work done in spreadsheet programs can be made password protected. This feature increases the security of the data.
	What-if analysis: This feature of spreadsheet allows automatic recalculation of operation when some data are modified.
	Easy formatting: This feature allows formatting the text by changing the font, font style, etc. that increases the visual clarity of the text.

2. What is MS Excel? Explain the components of Excel window. What are its various uses?

Ans.: Microsoft Excel (shortened as MS Excel) is a spreadsheet application that allows a user to perform various calculations, estimations, and formulations with data. It also provides various facilities like inserting charts, creating graphs, and analyzing situations, and helps in decision-making. It is one of the best management tools. It provides flexibility to the user to manipulate the data without worrying about the size of the data for general applications. Excel 2003 permits a wide selection of options to be used in the creation of worksheets and allows user to create an impressive spreadsheet presentation.

MS Excel Window

Whenever MS Excel is opened, the main screen of the application appears. This main window contains following parts (Figure 11.1):

	Title bar: It is located at the top of the application window, which displays the name of the application and the active workbook.
	Menu bar: Below the title bar is the menu bar which contains different menus that have all the options, functions, and commands for the entire MS Excel application. By default, Microsoft Office Excel 2003 contains nine menus, each of which has an associated pull-down menu. For example, the File menu contains commands to open, create, and print a workbook.
	Toolbar: It is positioned just below the menu bar. It contains buttons, menus, or combination of both that allows you to perform many tasks in a single-click. By default, Standard and Formatting toolbars are displayed in the Excel environment. Additional toolbars like Formula Auditing and Chart toolbars can be added by right-clicking on the menu bar and selecting the desired toolbar(s) from the pop-up menu that appears. Figure 11.1 MS Excel Window
	Name Box and Formula Bar: Generally, the Name Box and Formula Bar are located below the toolbars. The Name Box displays the name of the active cell or selected range, and can be used to name a range of cells. The drop-down menu next to the Name Box is used to locate previously named regions. The Formula Bar displays the contents of the selected cell in the worksheet. It includes text, numbers, formulas, and functions. One can also edit the contents of a cell from the formula bar.
	Worksheet tabs and scrolling buttons: Worksheet tabs appear at the bottom of the Excel window. Each tab represents a single sheet. They allow a user to move from one worksheet to another within the same workbook. The scrolling buttons that appear to the left of the worksheet tab allow a user to scroll more quickly and easily through the sheets. A user can rename a sheet by right-clicking on the worksheet and selecting Rename from the pop-up menu that appears.
	Status bar: It is located at the bottom of the Excel window and displays information about a selected command or an operation in progress. When a command is selected, the left side of the status bar briefly describes the command. It also indicates operations in progress, such as opening or saving a file, copying cells, or recording a macro. The right side of the status bar shows whether keys such as Caps Lock, Scroll Lock, or Num Lock are turned on.
	Worksheet and workbook: Worksheet is the area on a spreadsheet in which all the work is done. A worksheet is made up of horizontal rows and vertical columns. An Excel worksheet contains 65,536 rows and 256 columns and each intersection of a column and row forms a cell. Individual worksheets are linked to form a workbook. Fundamentally, a workbook can be related to a very sophisticated ledger. It allows the user to work and store various kinds of related data within a single Excel file. By default, each workbook contains three worksheets (Sheet1, Sheet2, and Sheet3) that can be accessed by sheet tabs. If user needs more than three worksheets, choose Worksheet from the Insert menu and to delete any worksheet, choose Delete Sheet from the Edit menu.
	Columns and rows: In MS Excel, a column is defined as the vertical space from top to the bottom of the window. There are 256 columns in a single worksheet that are labelled in alphabetical order (A, B, C, …, Z, AA, AB, AC, …, IV). The maximum number of characters, which can be inserted in a column, is 255. A row is defined as the horizontal space across the window. The rows are labelled in a numbered order (1, 2, 3, …, 65,536). The maximum limit of the height of a row is 409 points (547 pixels).
	Cells: The basic unit in a spreadsheet is a cell. A cell is formed by the intersection of a row and a column. This intersection gives the cell a unique address known as cell address through which a cell can be referenced through its column name followed by its row number. For example, the intersection of row 8 and column E gives cell E8. The cell that denotes the current position of the insertion point is known as active cell and has a dark border around it. Data written in a cell include text, decimal numbers, date and time, currency, percentage, and scientific notation.

Uses of MS Excel

MS Excel is used for a variety of office tasks. Some of the popular uses of Excel are as follows:

	It allows users to create professional spreadsheets and charts.
	It performs numerous functions and formulas to assist in projects.
	It is useful in performing various mathematical calculations.
	It is used for transforming information into organized tables. This information is then analyzed and operated according to our needs.

3. Define cell range. How do you name a range of cells?

Ans.: A cell range (also called as block of cells) is a group of adjacent cells forming a rectangular area. A colon ‘:’ called range indicator is used for indicating range in Excel. For example, range from ‘D4’ to ‘L28’ is written as ‘D4:L28’ in the Excel program. Here, alphabets D and L represent columns, whereas numbers 4 and 8 represent rows.

A range of cells is formed by selecting a group of adjacent cells in a worksheet. Naming a cell or range of cells adds clarity and speeds up productivity especially when dealing with a very large spreadsheet. To name a range of cells, follow the steps given below:

1.	Select the cell or range of cells.
2.	Click inside the Name Box to highlight the existing name of the cell.
3.	Type in the name for the selected cell or range of cells and press the Enter key (Figure 11.2).

Figure 11.2 Naming a Range

4. Explain the types of data that can be entered into a cell of a spreadsheet.

Ans.: In MS Excel, there are three types of data that can be entered in a cell:

	Text: This type of data includes textual data and cannot be used to perform calculations. For example, ‘Monday’, ‘hello’, etc. Note that the textual data always appear on the left side of the cell.
	Values: This type of data is used for calculations and includes numbers and date and time. Numbers include the numerical characters 0–9 and special characters such as +, –, (,), /, E, e, $, and %. The numerical values always appear on the right side of the cell. Date and time can be displayed in various built-in formats, for example, 3/14/06, 21-May-2006, etc. Note that when dates are entered in a dot format (like 1.1.2006), Excel considers it (dates) as text rather than numbers. As a result, it is difficult to perform any calculation on the date.
	Formulas: This type of data is used to perform calculations on the values stored in a cell or a range of cells. A formula consists of an equal to sign (=), values, cell references, and operators (addition, subtraction, multiplication, etc.).

5. How do you select cells, rows, and columns in an Excel worksheet?

Ans.: To select an area, click on one cell, hold down the mouse button, and drag across the cells you want to include in the selection. Notice that as you move the cursor next to or onto a border, it changes from a white cross to an arrow-pointer. If you wish to select a large area of adjacent cells, use the Shift key to extend the selection. Click on the first cell of the range you want to select; then, while holding down the Shift key, click on the last cell in the range you want to select.

An entire row or column can be selected by clicking on the desired row or column. For example, to select row ‘5’ or column ‘D’, click on the row heading or the column heading.

Excel 2003 also allows for multiple selections and more than one range may be selected at the same time, that is, non-adjacent range of cells can also be selected. For this, select the first range of cells; then, while holding down the Ctrl key, select the second range of cells. If a user wants to select the entire worksheet, then this can be done by clicking on the grey square at the top left of the spreadsheet.

6. Write the steps to insert and delete cells/rows/columns in an Excel worksheet.

Ans.: While working with a worksheet, you may need to insert or delete cells, rows, or columns.

Inserting Cells/Rows/Columns

To insert cells/rows/columns in an Excel worksheet, first select a cell and then follow any of the steps given below:

	Select Cells from the Insert menu.
	Right-click on the cell and select Insert from the pop-up menu that appears.

When you perform any of the above mentioned action, MS Excel will display the Insert dialog box (Figure 11.3).

Figure 11.3 Insert Dialog Box

 

Choose any one option from the Insert dialog box and click OK. The various options present in this dialog box are listed in Table 11.1.

Table 11.1 Options of Insert Dialog Box

Options	Description
Shift cells right	It moves the active cell to the right and inserts a cell in its place.
Shift cells down	It moves the active cell down and inserts a cell in its place.
Entire row	It moves all cells of the row containing the active cell down and inserts a row.
Entire column	It moves all cells of the column containing the active cell right and inserts a column.

Deleting Cells/Rows/Columns

Deletion removes the entire row or column from the spreadsheet. This action is similar to removing a row or a column from a Word table. To delete cells/rows/columns, first select the cell and then follow any of the steps given below:

	Select Delete from the Edit menu.
	Right-click on the cell and select Delete from the pop-up menu that appears.

Figure 11.4 Delete Dialog Box

When you perform any of the above mentioned action, MS Excel will display the Delete dialog box (Figure 11.4).

Choose from the list of options available in the Delete dialog box and click OK. The various options present in this dialog box are listed in Table 11.2.

Table 11.2 Options of Delete Dialog Box

Options	Description
Shift cells left	It deletes the active cell and moves all the remaining cells to the left.
Shift cells up	It deletes the active cell and moves all the remaining cells up.
Entire row	It deletes the entire row containing the active cell.
Entire column	It deletes the entire column containing the active cell.

7. How do you edit and delete the contents of a cell?

Ans.: If you want to change the contents of the cell, then follow any of the steps given below:

	Single-click the cell to retype the contents of the cell.
	Press F2 or double-click the cell to modify the contents of the cell.
	Use the arrow keys to select the cell, and edit the contents of the cell.
	Select the cell to display the contents of the cell in the Formula Bar. Now, you can edit the contents of the cell directly in the Formula Bar.

When you perform any of the above mentioned actions, press Enter key, or click anywhere in the worksheet to accept the editing, or press Esc to cancel it.

If you want to delete the contents of a cell, select the cell and follow any of the steps given below:

	Select Clear from the Edit menu and choose Contents option from the pop-up menu.
	Press Delete key.

8. Explain formatting of cells.

Ans.: The formatting of data in Excel 2003 is similar to doing formatting in other MS Office applications. It is applied to the cells in order to change the appearance of the data stored in those cells. It is applied by altering the appearance of the cell by setting the alignment, typeface (font), size, style, and colour. It can be done by using the Formatting toolbar or by using the Format Cells dialog box.

Using Formatting Toolbar

To format a cell using the Formatting toolbar, follow the steps given below:

1.	Select the cell to be formatted.
2.	Use buttons on the Formatting toolbar to format the cell. Table 11.3 lists the buttons used in formatting cells.

Table 11.3 Formatting Cell

Using the Format Cells Dialog Box

Cells can also be formatted using the Format Cells dialog box. For this, follow the steps given below:

1.	Select the cell(s) to be formatted.
2.	Right-click on the cell and select Format Cells from the shortcut menu. This displays the Format Cells dialog box (Figure 11.5).

The Format Cells dialog box contains a number of tabs to format the cell. By default, the Number tab is active when the dialog box is opened. All the tabs are described as follows:

	Number tab: Excel permits numbers to be formatted in many ways without changing the value of the number in a cell. Number formats allow numbers to be represented so that they can be used in different kinds of scenarios. Different number formats are available under this tab. Some of the commonly used number formats are General, Number, Currency, Accounting, Percentage, and Fraction.
	Alignment tab: The options present in this tab allow you to change the position and alignment of the data within the cell. Data can be aligned with any or all four sides of a cell. Text can be aligned horizontally by selecting the various options such as Left (Indent), Center, Right (Indent), Fill, Justify, Center Across Selection, and Distributed (Indent). The selected cells can also be aligned vertically. This can be done by selecting the Top, Center, Bottom, Justify, or Distributed option in the Vertical drop-down box in the Alignment tab. Figure 11.5 Format Cells Dialog Box You can also change the orientation of the text in a cell (from top to bottom, bottom to top, left to right, etc.). This can be done by entering either the exact amount of rotation required into the Degrees text box, or drag the Text dial to give the desired level of rotation under the Orientation section.
	Font tab: The options present in this tab allow you to change font attributes such as font, size, style, etc. The Font tab can be used to set the font colour, underline text, and apply effects like subscript, superscript, etc.
	Border and Patterns tabs: The options present in border tab allow you to apply borders to the selected cells. There are different types of styles available to add border to selected cells. Click on the Outline button to add a basic border outside the selected cell range, or click on the Inside button to add borders between cells within the selected cell range. The options present in the Patterns tab allow you to apply shading to the selected cells.
	Protection tab: The options present in this tab allow you to lock the selected cell. However, it will have no effect unless the worksheet is protected. For this, select Protection from the Tools menu and choose Protect Sheet.

9. Describe the AutoFormat feature of MS Excel? Explain its usage.

Ans.: MS Excel provides a feature known as AutoFormat that enables you to apply pre-defined layouts to selected tables in the worksheet. These layouts do not alter the position of your data but apply colour backgrounds and rearrange borders with some attractive effects. To AutoFormat a table, select a range of cells and follow the steps given below:

1.	Select AutoFormat from the Format menu to display the AutoFormat dialog box (Figure 11.6).
2.	From this dialog box, choose the appropriate option and click OK to format the table accordingly.

If you want to revert to the original appearance of the cells, select Undo AutoFormat from the Edit menu.

10. How do you copy or move the contents of one cell to another?

Ans.: When you are working in a worksheet, you may decide to move or copy the contents of one cell or a range of cells to some other part of the spreadsheet. In Excel, there are many ways to copy or move the cell contents.

Figure 11.6 AutoFormat Dialog Box

Using the Cut, Copy, and Paste

If text is to be moved within the same worksheet or to another worksheet, it should be first cut and then pasted. When you choose cut or copy, the cells are surrounded by a flashing dotted line, and their contents are not actually moved until you click in the cell where you want to paste. The contents of the selected cells do not disappear as they do in Word 2003. Copying the text is similar to the cut operation, except that copy retains the text at the original place but in case of cut, the text is removed from the original cell. You can use the cut, copy, and paste options in Excel in the same way as explained in the previous chapter.

Copying Cells Using Fill Handle

In Excel, the fill handle provides an easy method of copying contents of the selected cells to adjacent cells in a column or in a row. It appears on the worksheet when you move your mouse over the right bottom corner of the active cell. To copy a cell to adjacent cells using the fill handle, follow the steps given below:

1.	Select the cell or range of cells to be copied.
2.	Point at the fill handle. The mouse pointer changes to black plus sign
3.	Drag the fill handle in the direction of the copy until the faded rectangle surrounds all the cells to be filled.
4.	Release the mouse button to copy the contents.

Dragging Cells

Cell contents can also be moved to another cell by simply dragging the cell to point to the desired cell. To move a range of cells using drag and drop, follow the steps given below:

1.	Select the cell or range of cells.
2.	Point at the border around the selection to change mouse pointer to a pointing arrow.
3.	Drag the border to the new location and release the mouse button.

To drag selected cells to a different sheet, hold down the Alt key and drag the selected area onto the sheet label. Excel will then display that sheet and you can position your selected cells as normal.

11. Explain the AutoFill option available in MS Excel.

Ans.: It may sometimes take a lot of time to enter data in a worksheet. MS Excel has a feature named AutoFill that enables you to automatically fill data, which are in the form of a series. This feature is known as AutoFill. The AutoFill entries are done with the help the data fill handle. Firstly, we need to enter the first entry of the series and then select that cell. When you place the mouse pointer over the edge of the selected cell, the mouse pointer changes to the data fill handle. Now, if you drag the data fill handle over the adjacent cells, you can see that cells are automatically filled with the desired series. For example, if you enter the word ‘January’ in cell ‘A1’ and drag the data fill handle to the cell ‘A12’, Excel automatically inserts ‘February to December’ into cells ‘A2’ to ‘A12’.

12. What is a custom fill series? How can it be created and changed?

Ans.: MS Excel allows you to enter a series of your choice, for example, a list of your friend's names, sales region, etc. For this, you need to create a custom fill series. A custom fill series can be based on a list of existing items on the worksheet, or one can type the list from scratch. To create a custom fill series, follow the steps given below:

1.	Select the custom list if it already exists on the worksheet. Skip this step if you want to create a new custom list.
2.	Select Options from the Tools menu to display the Options dialog box. Click the Custom Lists tab (Figure 11.7) Figure 11.7 Custom Lists Tab in Options Dialog Box
3.	If you have selected a custom list on the worksheet, the corresponding cell range appears in the Import list from cells box. Click Import to add this custom list in the Custom lists box and then go to step 6. To type a new list, go to the next step.
4.	Select NEW LIST in the Custom lists box, and then type the entries in the List entries box. Press Enter key after each entry.
5.	After you have typed all the entries, click Add. The new custom list appears in the Custom lists box.
6.	Click OK to close the Options dialog box.

Now, whenever you type the first entry of the custom list in a cell, select it and then drag it over the adjacent cells using the data fill handle, the cells get automatically filled with other custom list entries.

To change a custom list, follow the steps given below:

1.	Select Options from the Tools menu to display the Options dialog box. Click the Custom Lists tab.
2.	In the Custom lists box, select the list you want to change.
3.	Make the desired changes in the List entries box and then click Add.
4.	Click OK to close the Options dialog box.

13. Explain some library functions of MS Excel.

Ans.: MS Excel contains many predefined or built-in formulas, which are known as functions. These can be used to perform simple or complex calculations. They perform calculations by using specific values, called arguments, in a specific order. In Excel, functions have three parts:

1.	‘=’ sign, which tells Excel that a formula or function follows.
2.	Function name, such as SUM for addition or AVERAGE for determining the average of a series of numbers.
3.	Arguments on which the particular function operate. The argument contains cell references to let the function know which data to calculate. In Excel, a function can accept a maximum of 30 arguments. It must also be enclosed by parentheses.

Some of the categories of library functions available in MS Excel are Math and Trig, Logical, Statistical, Text, and Date and Time functions.

Math and Trig Functions

A user can perform simple calculations such as rounding a number or calculating the total value for a range of cells with the help of mathematical and trigonometric functions. Various mathematical functions are listed in Table 11.4.

Table 11.4 Mathematical Functions

Formula	Description	Example
SUM(number1, number2, …)	It adds all the numbers in a range of cells.	SUM(10, 20, 30) Output: 60
ROUND(number, num_digits)	It rounds off a number to specified places of digits.	ROUND(3.628, 2) Output: 3.63
SQRT(number)	It returns a positive square root of a given number. If number is negative, SQRT returns the #NUM! error value.	SQRT(25) Output: 5
ABS(number)	It returns the absolute value of a number.	ABS(–100) Output: 100
TRUNC(number, num_digits)	It truncates a number to an integer by removing the fractional part of the number.	TRUNC(8.99,1) Output: 8.9
COUNT(value 1, value 2, …)	It counts the number of cells that contain numbers and also numbers within the list of arguments.	COUNT(A4:A6) Output: 2

Logical Functions

Excel has a number of functions that allow the user to evaluate values and make decisions based on the result of the evaluation. These functions are known as logical functions. It returns either true or false value depending on the condition. Various logical functions are listed in Table 11.5.

Table 11.5 Logical Functions

Statistical Functions

In addition to mathematical functions, Excel provides a great deal of assistance to compute statistical data. All the functions are simple and take only a set of observations as arguments. Various statistical functions are listed in Table 11.6.

Table 11.6 Statistical Functions

Text Functions

In Excel, text functions are used not only to convert a value to text but also to join several text strings into one text string. Many functions are available that enable you to manipulate text strings, convert numeric entries into text strings, and convert numeric text entries into numbers. Various text functions are listed in Table 11.7.

Table 11.7 Text Functions

Date and Time Functions

These functions are used for working with date and time. Excel uses serial numbers to store date, giving each day of each year a unique number. Two of the date and time functions are listed in Table 11.8.

Table 11.8 Date and Time Functions

14. Write the steps to insert a function in an Excel worksheet.

Ans.: To insert a function in an Excel worksheet, follow the steps given below:

1.	Click the cell where the function is to be inserted.
2.	Select Function from Insert menu, or click Insert Function button on the Formula Bar to display the Insert Function dialog box (Figure 11.8). Figure 11.8 Insert Function Dialog Box
3.	Select the desired function category from the select a category drop-down box and choose the function name from the Select a function box that contains a list of available functions in the selected category. For example, we have selected the Statistical category and the AVERAGE function.
4.	Click OK. Excel displays a Function Arguments dialog box to help user to create the function (Figure 11.9). In this dialog box, first click on the collapse button (labelled with a red arrow) to the right of the box labelled Number1 or Value1 (this depends on the function chosen). Figure 11.9 Function Arguments Dialog Box
5.	Drag the mouse to select the range of cells to be included as the first argument of the function and press Enter key.
6.	To specify additional arguments into the function, repeat steps 4 and 5.
7.	Click OK to insert the function. Figure 11.10 shows the AVERAGE function inserted in the worksheet.

Figure 11.10 Inserting Function in Worksheet

15. List the steps to use AutoSum in Excel.

Ans.: The SUM function is used more often than any other function. To make this function more accessible, Excel includes an AutoSum button on the Standard toolbar, which inserts the SUM function into a cell. It is a great tool to use when you want to quickly add contents of a range of cells. To use AutoSum, follow the steps given below:

1. Select the cell where you want the sum to appear.
2. Click the AutoSum button on the Standard toolbar. Clicking on the AutoSum, the cells get surrounded by a flashing dotted line. This dotted line is called a marquee. Excel puts this around the range of cells you want to add up and inserts the range reference in the formula.
3. If this is the correct range, then press the Enter key. If not, type or highlight the correct range and press the Enter key to apply the formula.

16. What is the use of COUNTIF() function? Give syntax and explain with the help of a suitable example.

Ans.: The COUNTIF() is a statistical function that counts the number of cells within a range that meets the given criteria. The syntax of this function is as follows:

COUNTIF(range, criteria)

Figure 11.11 Sample Worksheet

where range defines the range of cells from which you want to count the cells, and criteria defines the cells that are to be counted. You can use number, expression, or text as the criteria. For example, you can specify the criteria as ‘32’, ‘>32’, or ‘apples’.

To exemplify the use of COUNTIF() function, consider the worksheet shown in Figure 11.11. Suppose we have to count the number of cells having quantity greater than 70.

Now, follow the steps given below to use the COUNTIF() function:

1. Click the cell in the sample worksheet where you want to use the function. In our case, the cell is B6.
2. Select Function from the Insert menu to display the Insert Function dialog box.
3. Select Statistical category in select a category drop-down box and click COUNTIF in Select a function box as shown in Figure 11.12.

   

   Figure 11.12 Choosing the COUNTIF() Function

4. Click OK to close the Insert Function dialog box and display the Function Arguments dialog box.
5. Specify the range of cells from which you want to count the number of non-blank cells in the Range box. For example, we have specified the range B2:B5 (Figure 11.13).

   

   Figure 11.13 Specifying Range and Criteria

6. Specify the criteria based on which the function is to be evaluated in the Criteria box. For example, we have specified >70 to count the number of cells in the specified range having value greater than 70.
7. Click OK. As you click, the result of the function appears in the Function Arguments dialog box (e.g., in our case, 3) and the Function Arguments dialog box disappears gradually. Along with this, the result of the applied function also appears in the specified cell. For example, in our case, the value 3 will appear in cell B6.

17. How to use a function as an argument to another function?

Ans.: In MS Excel, a function can be used as an argument to another function. In such cases, the functions that are used as arguments are nested inside the main function. The syntax for nested function is as follows:

Function A (Function B)

where Function B (nested function) is passed as an argument to Function A (main function).

For example, consider the following function:

IF (MAX(B2:B5) > AVERAGE(C2:C5), 1, 0)

Here, the functions MAX and AVERAGE are nested within IF function. The MAX function computes the maximum of values stored in the cell range B2 to B5 and checks whether it is greater than the average of the values stored in the cell range C2 to C5. If it is, the IF function returns 1 else it returns 0.

While using a function as an argument to another function, some points need to be kept in mind:

	Valid returns: When a nested function is used as an argument, it must return the same type of value that the main function accepts as argument. For example, if the main function accepts TRUE or FALSE as argument then the nested function must return a TRUE or FALSE value. If it does not, MS Excel displays a #VALUE! error.
	Nesting level limits: Nesting of functions can be done up to seven levels. When Function B is used as an argument in Function A, Function B is a second-level function. A function nested within the Function B would be a third-level function, and so on.

18. How will you enter a formula in MS Excel? Explain with example.

Ans.: Formulas are mathematical expressions built in Excel that instruct the computer to carry out calculations on specified sets of numbers in the rows and columns. A formula always begins with an equal sign (=) followed by some combination of numbers, text, cell references, and operators. If a formula is entered incorrectly, an ERROR IN FORMULA message will appear. If the user forgets to enter the initial (=) sign, Excel will treat the expression like a text string and the values will not be calculated. It is to be noted that Excel evaluates a formula in a specific order: from left to right following the order of operations.

Suppose, cell A1 contains 25 and cell A2 contains 90 and you want to add values in cell A1 and A2 in A3. For this, follow the steps given below:

1. Select the cell where formula is to be inserted. In our case, the cell is A3.
2. Type ‘=’ followed by the operation (say, SUM) to be performed.
3. Type the first and second cell names, separated by a colon (A1:A2).
4. Press the Enter key or click Enter button () on the Formula Bar. Now, the formula appears in the Formula Bar while the cell (A3) contains the result of the formula as shown in Figure 11.14.

Figure 11.14 Using Formulas

19. Write the formula to compute the grades according to the following conditions:

Marks >= 80, Grade = A+

60 <= Marks < 80, Grade = A

50 <= Marks < 60, Grade = B

Marks < 50, Grade = F

Ans.: Suppose the marks are entered in column A of the worksheet and the grades are to be computed in the column B. Now, to compute the grade corresponding to marks stored in cell A2, you need to apply the following formula in cell B2:

IF(A2 >= 80, “A+”, IF(A2 >= 60, “A”, IF(A2 >= 50, “B”, “F”)))

As you enter this formula (preceding with ‘=’ sign) in cell B2 and press the Enter key, the result is displayed in the cell. Similarly, the grades for the marks entered in other cells can be calculated by changing only the row number in the formula.

20. Write in brief with the help of examples on relative, absolute, and mixed referencing.

Ans.: Referring to cells by their column and row labels (such as ‘A1’) is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if a simple addition formula in cell C2 =SUM(A2:B2) is copied to cell C3, the formula would change to ‘=SUM(A3:B3)’ to reflect the new row (Figure 11.5 a).

To prevent this change, cells must be called by absolute referencing and this is accomplished by placing dollar signs ‘$’ within the cell addresses in the formula. For example, if the formula in cell C2 would read ‘=SUM($A$2:$B$2)’ in which both the column and row of both cells are absolute, the formula will not change when copied (Figure 11.5 b).

Mixed referencing can also be used where only the row or column is fixed. For example, in the formula ‘=SUM(A$2:$B2)’, the cell reference A$2 indicates relative column and absolute row and the cell reference $B2 indicates absolute column and relative row (Figure 11.5 c).

Figure 11.15 Relative, Absolute, and Mixed Referencing

21. Tabulate various types of errors along with their description that are seen in Excel.

Ans.: An error may occur in Excel while working with formulas when the intended task is not carried out in a proper way. For example, if you intend to add cells (A1:A4) and one of the cells say, A3 contains text, then the correct output will not be shown in the selected cell and an error state is said to have occurred. The errors may also occur due to some other reasons. Some of the common errors are listed in Table 11.9.

Table 11.9 Error Values

Type of Error	Description
######	This is not exactly a kind of error; it just specifies that the result is too long to fit in the selected cell. This error can be corrected by making the column wider.
#DIV/0!	This type of error occurs when a number is divided by zero. The error can be corrected by making sre that the divisor is not zero.
#NAME?	This type of error occurs when there is a name in the formula that Excel cannot recognize. This error can be avoided by selecting a name from the Name Box instead of typing it. If user types a function, check the spelling and verify that the function exists or if user performs operations on text, enclose it in double quotation marks.
#REF!	This type of error occurs when cell reference is invalid. For example, when user deletes cells referred to in a formula or cells are pasted and moved elsewhere. This error can be corrected by entering the formula again.
#VALUE!	This type of error occurs when wrong operands or arguments are used within a formula. This error can be corrected by checking the passed arguments again and if wrong then passing them again.

22. What is the use of chart in MS Excel? List the different charts available in MS Excel.

Ans.: In Excel, numerical data can be easily converted into a chart for graphical presentation of the data. Charts provide more visual clarity than tables of data and, therefore, have more impact. MS Excel allows a chart to be placed in either of two ways: an embedded chart or a chart sheet. Embedded chart appears as an object on the worksheet and can be resized and positioned to appear along side tabular information or other charts and objects, whereas chart sheet exists as a separate sheet in a workbook. Chart sheet is, perhaps, a little easier to manipulate but when printed, it appears alone on the page. It is, therefore, possible to adjust the page orientation, headers, footers, and other page attributes for the chart without affecting other worksheets.

Excel allows users to create chart of different types with each type having a number of variations. Whatever the type, all charts are linked to the worksheet data. This means that data values can be amended even after the chart has been created and the chart will automatically be updated. Some of the commonly used chart types are as follows:

	Area This is used when a user wants to emphasize change over time.
	Surface In this chart, temperature and time are plotted together to show the tensile strength they produce.
	Bar This chart compares values with the different values given.
	Radar In this chart, each category of information has its own line radiating out from the centre.
	Column This chart is very similar to a bar chart, except that the bars are aligned vertically instead of horizontally.
	Bubble This chart shows three sets of variables, represented by the two axes and the size of the bubble.
	Line This chart is useful for comparing trends.
	XY (Scatter) This is useful for comparing a set of values with the average or predicted values.
	Pie This chart is used to compare a set of figures.
	Doughnut This chart is very similar to a pie chart, except that it can show more than one set of figures. Each ring of the doughnut represents a set of figures.

23. Explain step-by-step procedure for creating a chart by taking a suitable example.

Ans.: One of the simplest and easiest methods to create a chart is by using the Chart Wizard. This wizard helps in creating a chart by displaying a series of dialog boxes. The dialog boxes included are selecting a chart type, selecting a format for the chart, specifying how the data are arranged, and adding a legend, axis titles, and a chart title. To insert a chart, follow the steps given below:

1. Enter the data into the worksheet, which is to be converted into chart as shown in Figure 11.16.

   

   Figure 11.16 Worksheet Data

2. Select Chart from the Insert menu or click the Chart Wizard button on the Standard toolbar to view the first Chart Wizard dialog box.
3. Choose the Chart type and Chart sub-type from the available list. Then click Next (Figure 11.17).

   

   Figure 11.17 Chart Type Dialog Box

4. Select the data range (if different from the area highlighted in step 1) and click Next (Figure 11.18).

   

   Figure 11.18 Chart Source Data Dialog Box

5. Now the Chart Options dialog box appears (Figure 11.19). This dialog box allows you to enter the name of the chart and titles for the X and Y axes. By clicking on the tabs, one can change the axes, gridlines, legend, data labels, and data table. Click Next.

   

   Figure 11.19 Chart Options Dialog Box

6. Chart Location dialog box is the last step in Chart Wizard. This dialog box prompts you to specify the location of the chart. Click As new sheet if the chart is to be placed on a new, blank worksheet or select As object in to embed the chart in an existing worksheet (Figure 11.20).

   

   Figure 11.20 Chart Location Dialog Box

7. Click Finish. The chart appears on the worksheet.

24. Give steps to sort a given range of cells.

Ans.: Sorting is the process of arranging data into meaningful order so that one can analyze it more effectively. It can be performed for different data types as follows:

	Sort text data into alphabetical order.
	Sort numeric data into numerical order
	Sort group of data to many levels, for example, you can sort on City within Country

Excel's sort feature is a quick and easy way to sort data in a spreadsheet. There are two options for sorting data: sort in ascending order (A to Z alphabetically or smallest to largest for number data) and sort in descending order (Z to A alphabetically or largest to smallest for number data). To perform sorting in Excel, follow the steps given below:

1. Click in the column by which you want to sort the data or select a specific range in a column that is to be sorted.
2. Select Sort from Data menu to display the Sort dialog box (Figure 11.21).

   

   Figure 11.21 Sort Dialog Box

3. By default, Excel sorts all the data in ascending order. If a user wants to sort in descending order, select Descending. It is to be noted that one can sort the data up to three fields at a time.
4. Click OK to sort data and close the Sort dialog box.

25. What are filters in Excel?

Ans.: MS Excel provides the option for working with a subset of data from an available range. When data are used in such a way, the data are said to be filtered and the whole process is known as filtering. While using Filter option, only the data that meet the criteria specified by the user is displayed. Filtering does not sort any data but it hides the data that a user does not want to display.

26. Explain Page tab, Margin tab, Sheet tab, and Header/Footer tab in the Page Setup dialog box.

Ans.: The Page Setup dialog box is used to set the layout of a page. It appears when you select Page Setup from the File menu. As shown in Figure 11.22, this dialog box has four tabs: Page, Margins, Header/Footer, and Sheet.

Page Tab

Click on the Page tab (by default, active tab) to make changes to the page layout. This tab allows a user to make changes to any of the following:

	Orientation: Select Portrait or Landscape in the Orientation section.
	Paper size: Click the Paper size drop-down box to select the required paper size.
	Scaling: This is used to reduce or enlarge the print. Figure 11.22 Page Setup Dialog Box
	Print quality: Choose the quality needed. This depends upon the printer installed on the computer.
	Page numbering: To begin page numbering, select the First page number text box and enter the number that is needed.

Margins Tab

Margins are the empty space between the text and the left, right, top, and bottom edges of a printed page. By default, margins are ‘1’ inch at the top and bottom and ‘0.75’ inch from left and right as shown in Figure 11.23. You can make changes to the top, bottom, left, and right margins of the document in order to create more space for the data present on the page or to add some extra space when binding a document.

Figure 11.23 Setting Margins

Header/Footer Tab

Headers and footers can be easily created in Excel. There are some standard as well as customized options available for creating headers and footers. To create standard header (or footer), click on the down arrow to the right of the Header (or Footer) list box to display a list of available headers (or footers). Select on the appropriate header (or footer) needed. If you want to create a different header (or footer), you can do so by clicking on the Custom Header (or Custom Footer) button (Figure 11.24).

Figure 11.24 Adding Headers and Footers

Sheet Tab

You can change the print range and elements to be printed such as headings, gridlines, and comments, and also the order of pages to be printed in this tab. This tab allows a user to specify any of the following options given below (Figure 11.25):

	Columns or rows to repeat: Click on the icon to the right of the Rows to repeat at top text box in the Print titles area and drag over the rows that user wants to repeat at the top of the page. Click on the icon in the right of the Columns to repeat at left text box and drag over the columns that is to be repeated at the left of the page.
	Elements to print: Select the elements to print, that is, Gridlines, Comments, Draft quality, Black and white, and Row and column headings.
	Order of pages to print: Select Down, then over, or Over, then down.
	Print range: In the Print area text box, enter the worksheet range to be printed.

Figure 11.25 Sheet Tab

Multiple-choice Questions

1.	_____________ appear at the bottom of the Excel window. (a) Sheet tabs (b) Name Box (c) Formula Bar (d) Title bar
2.	_____________ is the area on a spreadsheet in which all the work is done. (a) Work area (b) Worksheet (c) Task pane (d) None of these
3.	Workbook is a collection of _____________. (a) Page Setup (b) Buttons (c) Worksheets (d) Charts
4.	A cell range is represented as _____________. (a) (A6.A8) (b) (A6-A8) (c) (A6:A8) (d) (A6,A8)
5.	Borders tab in Format Cell dialog box is used to apply _____________. (a) Borders (b) Shading (c) Background colours (d) All of these
6.	Which of these is not a font style? (a) Bold (b) Underline (c) Italic (d) Footer
7.	Excel provides a feature known as _____________ that enables you to apply predefined layouts to selected tables in a worksheet. (a) AutoFormat (b) Header and Footers (c) Undo and Redo (d) None of these
8.	MS Excel contains many predefined or built-in formulas, which are known as _____________. (a) Formatting cells (b) Functions (c) Relative referencing (d) AutoSum
9.	An error may occur in MS Excel while working with _____________ when the intended task is not carried out in a proper way. (a) Formulas (b) Tables (c) Headers and Footers (d) None of these
10.	One of the distinguishing features of Excel is that it makes use of _____________ to dynamically calculate results from data present in worksheets. (a) Tables (b) Goto (c) Formulas and functions (d) All of these

Answers

1.	(a)
2.	(b)
3.	(c)
4.	(c)
5.	(a)
6.	(d)
7.	(a)
8.	(b)
9.	(a)
10.	(c)