Chapter 4 - ICT Soft Tools : Spreadsheet

Chapter 4 - ICT Soft Tools : Spreadsheet

Q.1 Fill in the blanks:

1. Excel is software in the Microsoft Office Suite which is used principally for accounting.
2. Excel 2007 adds the extension .xlsx to every workbook filename.
3. Spreadsheets of an Excel workbook are called Worksheet.
4. In Excel, cells are formed by the intersection of rows and columns.
5. A selected cell in an Excel worksheet is called a/an active cell.
6. The name box in Excel displays the address of the active cell.
7. To add the values in a selected row, column or cell, F2 option is used in Excel.  OR    In Excel, the function key F2 is used to place the insertion point inside a selected cell.
8. In Excel, the result of the formula, = 5+2^3, is 13.
9. XY scatter chart in Excel is (usually) used for scientific data analysis.
10. In Excel, Form facility is used to complete one entry at a time.    OR
11. In Excel, a form is used for data entry, one field at a time.  

Q.2 State whether the following statements are true or false:

1. Simple cell is the one in which information is being entered.           True
2. We can minimize the Ribbon in a Microsoft Office Application to display only the names of the tabs by double clicking on the name of the active tab. True
3. There are keyboard shortcuts for every button/icon on the Ribbon. True
4. The cell at the first row and first column of an Excel worksheet is referenced as A1.         False   
5. The address of the active cell of a worksheet can be seen in the Formula bar. False
6. In Excel, to place the insertion point inside a selected cell, you double click inside the cell or press the function key F2.       True
7. In Excel, if the text is too long, it is displayed in the right side of the cell as if they are empty. True
8. When we select the Wrap Text option, all the text in a single cell is displayed on multiple lines.OR  When we select the Wrap Text option in the Alignment group of the Home tab, the cell height adjusts according to the text without any change in the cell width. True
9. By default, Excel 2007 enters time as AM.                                                    True
10. Excel 2007 considers a leading plus sign as a defining formula.                   False
11. The formula in Excel, =A5+D5, returns the sum of the numbers in the cells A5 and D5. True
12.  Suppose the cell B2 of a worksheet contains the formula, =A$1. If you copy The cell B2 to B3, the formula changes to =B$1. False
13. The formula in Excel, =MEAN (A5:D5), returns the arithmetic mean of the numbers in the range A5 to D5. False
14. In Excel, the formula =5+3*2 and = (5+3)*2 give the same result, viz., 16.  True

Q.3 Multiple choice questions ( Single Correct Answer):

1. Excel 2007 adds the extension .xlsx to every workbook filename.
2. When we open Excel 2007, the default number of worksheets present in a workbook are 3.
3. By default, number data type in a worksheet cell is displayed horizontally right aligned.
4. In Excel, a single click on any of the small arrows at the end of a scroll bar scrolls the active worksheet one cell at a time.
5. Right- clicking on any of the tab scrolling buttons in Excel 2007 displays the list of worksheets in the active workbook.
6. To select all cells in a worksheet, click the diagonal arrow at the intersection of row & column headers.
7. The Page Layout view in Excel 2007 is similar to the Print Layout view in Word 2007.
8. Find the odd one out from the following: 24/08/1971.
9. Find the valid time from the following: 02:26.
10. In Excel 2007, the mathematical expressions are referred as formulas.
11. The Union operator combines a range of cells on a worksheet for calculations.

Q.4 Multiple choice questions ( Two Correct Answer):

1. In Excel 2007, each worksheet can contain 10,48,576 rows, 16,384 columns. 
2. In Excel 2007, which of the following is true about a cell? It is formed by the intersection of a row and a column, It is identified by a unique address.
3. In Excel 2007, country specific special formats can be applied to which of the following data type?    Date, Time. 
4. Which of the following characters can we use while specifying a date in Excel?  Forward slash[ / ], Hyphen (- ).

1. In Excel 2007, functions are built-in, predefined formulas.

Q.5. Write short note on:

1. Navigation in Excel 

There are different ways to navigate through a worksheet. You can use the arrow keys, the scroll bars, the sheet bars tabs, the scroll buttons or the mouse.

• To move between cells on a work sheet click any cell or use the arrow keys.
• To move one row up or down. Press the scroll lock on the keyboard and use the up or down arrow keys.
• Roll the mouse wheel button forward or backward to go up and down.
• Press the page up or page down key to move one screen up or down.Changing row height and column width.

2.a Changing column width 

• Double click on the right border the column width automatically increases. 
• Hold the mouse pointer over the right border and drag it inside or outside to decrease or increase the column width.
• Right click on the column header and enter the value in points.

b.Changing row height : 

• Increase the font size or wrap text to the cell and the row height automatically increases.
• Hold the mouse pointer over the bottom border and drag it inside or outside to decrease or increase the row height.
• Right click on the row header and enter the value in points.

Q.6. Answer the following in brief:

1. List the various uses of Excel.

Excel is used to:

• To create marksheet for test papers.
• Create and analyse account statements of banks, business, establishments.
• Create and analyse payroll of employees
• Create and analyse scientific sales and marketing sales.
• Create crosswords.
• Applications of excel are used in all organisation for sales, marketing, accounting,  inventory, management, payrolls, hotel management, schools and colleges.

2. What is a formula in Excel? What are the different types of operators that can be used in a formula.

A formula in excel is an equation beginning with an = sign, it helps to perform calculations. A formula contains constant, operator, cell reference and function. When the cell containing formula is active, the formula is displayed in the formula bar and the result is displayed in actual cell. There are 4 different types of calculations operators, arithmetic, comparison, text and references.  

3.  What are functions? How are function used in Excel

OMIT

4. Suppose the the cells B2 and B5 in a worksheet contains four numbers. How will you use the statistical functions to calculate the sum and average of the above integers.

• Click on the cell B6, click on the arrow below, click on the arrow below the autosum icon on the formula bar or select sum for the drop down list.
• Select range of cells (B2 to B5) and a marquee is seen around and press enter key. The total is displayed in B6.
• Similarly click on cell B7 and from the formula bar select average from the dropdown list.
• Select range of cells (B2:B5) a marquee is seen around, press enter key. The average is displayed in B7.

5. In Excel how will you find i) the number of days ii) the number of working days between the dates 01-07-2011 and 25-07-2012.

• Select a cell, in the formula bar type the formula =date(2012,7,25) – date (2011,7,1) and press enter key.
• Select a cell, in the formula bar type the formula =networkdays(date(2012,7,25) , date (2011,7,1)) and press enter key.

6. In Excel, how you will find i) the date after 10 days from today ii) the date after 10 days before 25-7-2011.

• In the formula bar, type the formula =today()+10 and press enter key.
• In the formula bar, type date (25-07-2011)-10 and press enter key.

Q.7. Answer the following:

Q.7. Answer the following:

1.      What are the advantages of electronic spreadsheet program?

• With the help of suitable formulae, all the calculations can be performed automatically and accurately every time the data is changed.
• There is no wastage of time whenever we need to redo the same calculations again and again just by changing values in one of the cells.
• The pictorial representation of the data in the form of various types of chards is possible with excel.
•  It helps to record, organize and analyse and even graphically represent data of number formats.

2.      Explain the feature of Excel or components of excel.

i.      Title bar: The title bar at the top of the window displays the name of the open document displays the name of the document and the program name.

ii.      Ribbon : It is a graphical user interface panel below the title bar. The commands and options for working with excel are placed here.

iii.      Office Button: It contains a dropdown list of the usual file related commands to open, save and print your file.

iv.      Quick Access toolbar: It is a customizable toolbar that contains a set of commands that re independent of the tab currently displayed.

v.      Worksheet area:  This is the spreadsheet area of the Excel window. It consist of grid of rows and columns where you type of data.

vi.      Formula Bar: Located above the worksheet area, It is used of entering or editing data and formula.

vii.      Name Box: displays the cell name of the active cell.

viii.      Status Bar: Displays the zoom controls, and sheet view.

3.      Explain the different formats available in Excel 2007. Explain how to change the number format. 

i. A constant number is made up of digits 0-9, it also includes arithmetic symbols, currency symbols, symbol for the power of 10, decimal point and a comma.
ii. The default number format is general.
iii. Some of the number formats are :

a.      Number: the no decimal places can be specified, you can specify thousand separator, decide how to display negative no.

b.      Currency: It displays the specified currency symbol with numbers.

c.       Accounting: The accounting format aligns the currency symbol and the decimal point.

d.      Percentage: The percentage format multiplies the cell by 100 and displays the result with a percent symbol.

e.      Scientific format: A scientific format: A scientific format displays a number in exponential notation with a E+.
iv.            The number format of a cell or a range of cells can be changed using the format cells dialog box.

4.      Write a note on cell editing

There are different ways to edit a cell: 

a)      To edit the contents of the cell: a) Place the insertion point in the cell and double click the cell. 

b) click anywhere in the formula bar and place the insertion point. C) Delete or insert a character and press enter key to make changes.

b)     To move or copy cells content: a) Entire cell and their contents can be moved or copied using the cut, copy and paste commands.

a.      To copy : Select a single cell or a range of cells, right click select copy or press ctrl + C to copy to the clipboard. Go to a new cell and  right click select paste or press ctrl +V and paste the contents of the cells.

b.      To move : Select a single cell or a range of cells, right click select cut or press ctrl + X to copy to the clipboard. Go to a new cell and right click select paste or press ctrl +V and paste the contents of the cells.

5.      Write a note on cell references and its types in Excel 2007.

a.       A cell reference is a formula in excel, is the address of a cell or address for the range of cells combined with appropriate reference of cells combined with appropriate reference operators. References to cells in other workbook are called links or external reference.

b.      Absolute references : An absolute reference in a formula referes to the sam cell in a specific location. If the position of the cell that contains the formula changes the absolute reference remains the same. Absolute reference to a cell is given by preceding both the column name and row number by the $ sign. Eg. $E$3

c.       Relative reference: Excel considers the cell address relative to the address of the cell containing the formula. If you copy the formula containing relative reference the reference automatically adjusts.

d.      Mixed reference: A mixed reference has either an absolute column and relative row reference or vice versa. If you copy a formula containing mixed reference, the relative reference automatically but the absolute reference remain the same.

6.     What is a graph or chart? How can different types of graphs or charts be prepared with Excel 2007?

A chart can transform the worksheet data to show comparison, patterns and trends.

To create a chart in Excel we have to follow the sequence of steps :

a.       Select the data we want to graph including the column titles and row labels.

b.      Click Insert Tab and then in the charts group select the type of chart we want.

c.       A variety of sample charts in the selectee category. We can select any one chart out of it.

d.      On the design tab under chart tools, in the type group click change chart type.