CBSE Notes for Class 9 Foundation of Information Technology – Spreadsheet Tools

CBSE Notes for Class 9 Foundation of Information Technology – Spreadsheet Tools

INTRODUCTION
Spreadsheet as the name suggests is one big table or chart with data spread all over the page. Before the advent of computer programs, paper spreadsheets were used to record data for financial analysis. The accountant had to spend several hours recording data in tiny rows and columns and calculating results using a calculator. All the work had to be done using pencil and could only be penned when one was sure that the data is not going to change. The introduction of spreadsheet on computers revolutionized the world of number juggling. It allowed user to quickly enter data electronically and edit it as required. Even complicated calculations could be performed and the data could be presented in the form of graphs and charts for easier comparison of two sets of data.
Microsoft Excel is one of the best-selling spreadsheet programs. An Excel sheet consists of rows, columns, mathematical functions, formatting tools, charts and graphs and many more features. Some of them can be listed as follows:

1. A huge volume of data can be organised without much effort.
2. Several built-in functions can be used for faster calculations.
3. Re-entry of a data automatically updates the results using that data.
4. Data can be represented pictorially in the form of graphs and charts.
5. Data can be sent to or received from other users via e-mail import/export option.
6. Above all, accuracy of results is awesome.

STARTING EXCEL
To start Excel, follow the steps given below:

• Click the Start button on task bar.
• Click All Programs to display program menu.
• Select Microsoft Office —> Microsoft Excel. The screen shown in Fig. 12.1 will be displayed on the computer.
• A X icon on the task bar or desktop when double clicked will also open a MS-Excel Worksheet.
  

Understanding the Work Area
A file in Excel is called a Workbook.
A Workbook is a collection of worksheets stored in a single file.
A Worksheet is a collection of rows and columns. Numbers and text are written in these rows and columns.
An intersection of a row and a column is called a cell. Data is stored in cells. A cell address has the column name and row number. For example, A1 refers to the cell in first row of column A.

Each row represents a record and each column represents a field. For example, for a student datasheet, on row per student will be there and fields will store the data of each student (Fig. 12.3).
Each row has a unique number called the row address and each column is represented by an alphabet A-Z. After Z the addresses start as AA, AB, AC,………., AZ, then BA, BB, BC ,……., BZ…….

There are 16,384 columns and 1,048,576 rows in a worksheet. By default three sheets are there in a workbook. Additional sheets can be created by right clicking the mouse on status bar at the bottom of the worksheet and choosing the option.

File Menu Options
On the Menu Bar, the file menu has the following options:

New	Opens a new workbook with a default name e.g., Bookl
Open	Opens an already created workbook
Close	Closes the current active workbook
Save	Saves the workbook with the name written on title bar
Save As	Saves the workbook with user defined name in the file/folder of user’s choice
Info	Displays general information about the active workbook
Exit	To quit MS Excel

Data Input in Worksheet
Data can be entered in a cell by placing the mouse pointer in the cell and left click. The boundary of the cell will become bold and thick. Now type the data in the cell. You can move to the cells up/down/ left/right of the current active cell by using arrow keys on keyboard or by moving the mouse.

Editing the Data
once the data has been entered, you can make minor changes in the data by the following steps:

1. Select the cell to be edited. Now type the new data so that this data overwrites the
   previous data.
2. To change a portion of data, select the cell. Position the mouse pointer or cursor at the point of change and double click. Now enter the data.
3. Data of the selected cell also appears on the formula bar. Editing of data can also be done on formula bar.

Selecting a Range of Cells

• A group of cells can be selected by dragging the mouse over them holding down the left button as shown in Fig. 12.6. Here cells A1 to A8 have been selected.
  
• Range of cells can also be selected using the keyboard.
  Place the cursor on the corner cell of the range. Press the shift key and holding this key move the cursor using arrow keys over the cells to select them.
• If the range is too large, we use EXT mode i.e., the extension mode to select the cells. Select the corner cell. Now press F8 key. You will find [EXT] written on status bar. Now move to the diagonally opposite corner cell of the range and left click the mouse. The whole range of cells will get selected. EXT mode can be switched off by pressing F8 again.
• To select all cells of a worksheet, click Select All button in the Edit menu.
• To select non-adjacent cells or cell ranges, select the first cell or range of cells, and then hold down CTRL and select the other cells or ranges (Fig. 12.7).
• To select an entire column, click the column heading (Fig. 12.8).
  
  
• To select an entire row, click the row heading (Fig. 12.9).
  

Modifying Data

1. Undo/Redo: Undo reverses the result of the last action. Redo repeats the last action.
2. Cut: It will place the selected range on the clipboard.
3. Copy: Select the range of cells. Click on Copy option. Now place the cursor/mouse at the desired location on worksheet and click on Paste. The selected range will get copied at the new location also. Cut, Copy and Paste options are also available in the menu which appear at the right click of mouse.
4. Paste: Whatever is on the clipboard gets pasted at the position of the cursor on the worksheet. (If a range of say 4 cells has to be pasted, then first select the same number of cells on worksheet range and then paste the cells in the range.)
5. Find: It helps in searching for a text/formula/number in the entire worksheet. You can use a question mark (?) to match any single character or an asterisk (*) to match any string of characters. For example,
   Cra?y will find “Crasy and “Crazy” whereas un* finds unusual, unhealthy etc.
6. Find and Replace: Click on the Find option and a dialog box will appear. Enter the object you want to find. In case you want to replace this object, click on Replace button and another dialog box will appear. It gives you option to replace all the occurrings of the data or a single occurring.
   
7. AutoFill: This facility in MS Excel allows us to fill a range of cells in a row/column with a series of values.
   
   (i) Type 1 in cell A2. Select it and drag the small black cross (fill handle) at the bottom right corner of the cell downwards till cell A15. You will see that all the cells from A3 to A15 will be filled with 1.
   (ii) Type 1 in cell A2 and 2 in A3. Select A2 and A3 and drag the cross till A18. You will find that the cells from A2 to A18 get filled with series 1, 2, 3, 4, ……….17. If you type 1 in A2 and 3 in A3 and then drag the fill handle (black cross) downwards, the selected range of cells will be filled with values 1, 3, 5, 7, 9 …….. So AutoFill automatically checks the relation between the first two entries given by the user and fills up the rest of the cells in range with appropriate values.
   (iii) AutoFill facility can also be used from the Edit menu. Fill option has a sub menu from where you can select the direction of fill i.e., up, down, right or left.
   (iv) Click on Series and you will get a dialog box (Fig. 12.12). Series can be filled either
   row-wise or column-wise.
   (a) Linear type refers to a series which follows a linear trend i.e., the values in the series jump according to the step value.
   (b) Growth type creates a growth series or geometric growth trend. If the trend box is cleared, a series is calculated by multiplying the value in the step value box by each cell value in turn. If the trend box is selected, the step value is ignored and geometric growth trend is calculated based on the selected value.
   (c) Date/Time type (Fig. 12.13) fills a series with dates. The type of date series that is incremented depends on the option selected in Date unit. Similarly, time series can also be filled.
   (d) AutoFill type produces the same result as dragging the fill handle to fill a series.
   
   
   Selecting the column series, fills the column and selecting the row series, fills the row.
   
   
   
   
8. Customised List: Sometimes we require the value of our choice to fill the series. This is called customised list. For example, class teacher needs to write the name of students of her class for various purposes like keeping record of marks, keeping record of fee payment, keeping record of discipline grade etc. We can create a customised list for her containing the names of all the students so that she can use AutoFill to create and print the list whenever required.

• Select File —> Options —> Advanced —> Edit Custom List.
  
• Click on Custom Lists in options dialog box.
  
• Enter the names in List Entries box and click on Add button. The list will appear in Custom Lists box.
• Press OK.
• On the worksheet, write first two names in two cells. Select the cells and drag the fill handle. The selected range of cells will be filled with the names you entered in the Custom List.

Insert Option
On the Home menu, under cells section we have options for Row/Column/Sheet insertion, deletion or formatting.

If we right click on any cell, we get a pull-down menu with Insert option. Click and you will get submenu of Insert.

1. Insert Cells: Click on Shift Cells Right (Fig. 12.22). A cell will be inserted so that the existing cells of the row shift towards right. The cell can also be inserted in a column so that the existing cells move down from the position of insertion (Fig. 12.23).
   
   
2. Insert Row: A row can be inserted using Entire row option from Insert menu bar. In the Fig. 12.24, the data has shifted from cell B2 to B3 because a row has been inserted.
   
3. Insert Column: A column can be inserted using the Entire column option from Insert menu bar. The data shifts a column towards right as shown in Fig. 12.25, shifts from cell B3 to C3.
   
4. Insert Worksheet: Normally a workbook shows only three worksheets—Sheetl, Sheet2 and Sheet3. Choosing Insert sheet option will insert an entire worksheet in the workbook (Fig. 12.26).
   

Insert Menu

1. Insert Picture/ClipArt/Shapes/Smart Art: As already seen in MS Word and MS PowerPoint, pictures can also be inserted in an excel worksheet. Apart from pictures which can be imported from either Clip Art or any other file, we can also insert auto shapes, organisation charts and word art. These topics have been dealt with in the previous chapters.
2. Insert Object: An object here refers to anything from bitmap image to a picture on a powerpoint slide or a video and so on. Any such object can be inserted in the worksheet by choosing Object option on insert menu bar in Text Section.
   
3. Deleting Cell: An individual cell, a row, a column or a complete sheet can be deleted by first selecting it and then using the Delete option on the menu bar or from the right click menu. If a cell is deleted, the remaining cells are moved up; if a row is deleted, the remaining rows are shifted up and if a column is deleted, the remaining columns on the right are shifted towards left. Use of DEL key will only clear the contents of the cell/row/column but the empty space will remain in the worksheet.
   

FORMATTING A WORKSHEET

1. Cells: There are a number of ways a cell can be formatted. Home menu shows groups for Number, Alignment, Font, etc. These formatting options do not affect the actual value of data. They just affect the appearance of data on the worksheet. Selecting a cell and clicking the right mouse button gives an option to Format Cells (Fig. 12.29).

Number group displays various categories like Number, Currency, Accounting, Date, Time, Percentage etc (Fig. 12.30)

Data	Formatting	Sample
34	General Number Currency Accounting	34 34.00(Decimalplacescanbeincreased/decreased) $34.00(Decimalplacescanbeincreased/decreased) $34.00(Decimalplacescanbeincreased/decreased)
12/3/1978	Date	Mar-78 March-78 March 12, 1978 3/12/78
1.30 pm	Time	13:30 1:30 PM 13:30:55 1:30:55 PM
67	Percentage	6700.00 Percentage Decimal can be adjusted

Currency, Percentage and Decimal places can be set from the formatting bar.

2. Cell Alignment: Data in a cell can be aligned vertically and horizontally.

• Horizontal alignment may be set as Left/Centre/Right/Fill/Justify/Centre Across selection.
• Vertical alignment may be set as Top/Bottom/Center/Justify.
• The text can be wrapped, shrunk to fit or cells can be merged.
• Orientation is the angle at which the text is displayed. It can be changed either by typing number in Degrees box or by rotating the needle in the orientation box [Fig. 12.34(a)]. Fig. 12.34(b) displays an orientation of -17°.
  

3. Font: Click on the arrow box on bottom right corner of Font group. It displays a dialog box (Fig. 12.35) having options for setting the font, font style, font size, effect, colour etc. Choose the required options and click on OK.

4. Border: The border tab is used to set the style, colour and position of border. Border can also be set from the format group.

5. Fill: Fill tab is used to set the colour of cell. Whichever colour you choose, your cell will be filled with that colour. Also you can choose a pattern for the colour fill.

6. Format a Row/Column:

• Row Height option is used to increase or decrease the height of the selected row(s).
• AutoFit Row Height changes the height of the row to default size.
• Hide option hides the selected row(s) from the screen.
• Unhide option again displays the hidden line.

7. Formatting a Column: The options are same as that for rows. Standard column width is 8.43 units. This can be changed using width option for selected columns only or standard width option for the entire workbook.
8. Sheet: Sheets can be renamed either from the format menu or by placing the pointer on the sheet number at the bottom of the Excel window and right click. Choose Rename option and type the new name which will appear in place of its old name i.e., sheet 1 or sheet 2.
A sheet can be hidden using Hide option.

TYPES OF CELL REFERENCES
Cell Reference
A reference identifies a cell or a range of cells on a worksheet and tells MS Excel where to look for value or data to be used in a formula. Using reference, we can use data present in different parts of a worksheet or on a different worksheet or another workbook. There are three types of references:

1. Relative Reference
2. Absolute Reference
3. Mixed Reference

1. Relative reference
These references to cells are relative to the position of the formula. Let us understand this using an example.
Suppose in cell B3 we write formula = C3. This means copy the contents of cell which is one position towards right in cell B3. Now if we copy the contents of B3 in D6, you will be surprised to find out that contents of B3 will not be copied. Rather the contents of E6 will be copied to D6. This is because E6 is one position towards right of D6.

Let us take another example.
If in cell C4, we enter = A4 : B4 and if we copy this formula in C6, the formula written there will be = A5 : B5. Here you see that the cell references in formula have changed automatically according to the current cell.
This is called relative referencing.

2. Absolute referencing
The absolute reference contains a formula which does not change when the formula is copied. To make an absolute reference, prefix $ is added to both row address and column address.

For example,
= $A $4 + $B $4

3. Mixed referencing
This referencing is a combination of both absolute and relative referencing. For example, – $C8 is written in cell B8. When this formula is copied to cell D2, only the reference to row is adjusted, column remains same. So value in D2 will be calculated according to $C2.

Example: Automatic Updation. Let us now take an example of the class result and design a worksheet for the same.


In Fig. 12.43, marks of 6 students in 5 subjects have been entered. Sum of marks of each student and percentage of marks has been calculated using absolute referencing and SUM and AVERAGE functions. Maximum and minimum marks in the class in each subject and total has been calculated using MIN and MAX functions. *•
Any change in the data in the table automatically changes the other results where the changed data has been used. For example, changing GEETA’s Hindi marks from 54 to 99 changed her total, percentage and maximum value in Hindi automatically (Fig. 12.44).
Example: Calculating subtotal and grand total. A departmental store wants to find out the sales record section-wise and department-wise and the grand total.

• Enter the headings as shown in Fig. 12.45.
• Enter the data of sales of section 1 i.e., 10, 20, 25 in cells C2, C3 and C4.
• Select cell C5 and click on AutoSum ∑ button. Press Enter. The sum of cells C2, C3 and C4 will appear in C5.
• Again enter sales of section 2 in cells C6 and C7. Select cell C8 and click AutoSum and press enter. You will be surprised to find out that sum of only C6 and C7 will appear in C8.
• To find total sales of dept. 1, select cell C9 and click on AutoSum. The sum of C5 and C8 will appear in C9.

USING FUNCTIONS AND OBTAINING CHARTS
Using Auto Functions
Some of the most frequently used functions are Sum, Average, Max, Min etc (Fig. 12.46). These features are present on the standard tool bar.
In Windows 2010 a separate tab for formulas is there under which all the options for functions are present.

AutoSum: This function finds the sum of selected cells. As shown in Fig. 12.47, we had found Bl + B2 + B3 + B4 + B5 + B6.
Instead of writing the cell reference of all the cells, we can select the cells to be added or click on E sign on standard tool bar to get the result in B7 (Fig. 12.47). The formula bar in this case will display = Sum(Bl: B6).
Right click on the horizontal bar (status bar) at the bottom of the screen and you will get a shortcut menu showing a number of functions of which sum is one.
The following functions can also be applied on the selected cells:

1. Avarage: This function as the name suggests,find the average values of selected cells.
   for example, Average=62.5 for cells B1 to B6 using formula=Average(B1:B6)
2. Min:It finds and displays the minimum value from the selected range of cells.
   For example, 43 from cell B1 to B6 using formula=Min(B1:B6).
3. Max: It finds and displays the maximum value from the selected range of cells. For example, 87 from cell B1 to B6 using formula =Max(B1:B6).
4. Count: This function counts the number of values written in the selected range of cells which is 6 for cells B1 to B6 using formula = Count (B1:B6).

Use this function	To summarize
Sum	The sum of the values in a list. This is the default function for numeric data. For example, = SUM(4, 5) gives a result 9 = SUM(A1, B4, C3) will give a result 8 if A1 has 2, B4 has 4 and C3 has 2 = SUM(A1: A4) will result in 10 if A1 is 1, A2 is 2, A3 is 3 and A4 is 4 = SUM(A1: A4, 2) will give a result 12 = SUM (TRUE, FALSE) will result in 1 as true is 1 and false is 0 = SUM(“3″, “5”) will result in 8
Average	The average of the values in a list.
Max	The largest value in a list.
Min	The smallest value in a list.
Product	The result of multiplying all the values in a list.
Count	The number of records or rows in a list that contains numeric data.

If you click on the formulas tab on menu bar, you will get a menu, displaying all the built-in functions ranging from financial to statistical to logical. You can choose and apply the function of your requirement.
IF Function: IF function returns one value if a specified condition is TRUE and another value if that condition evaluates to FALSE. For example,
= IF(a3>a5, “1”, “0”)
It will compare value in cell a3 with that in a5. If a3>a5, “1” will be printed else “0” will be printed.

Building a Formula
Formulas in Microsoft Excel follow a specific syntax that includes an equal sign (=) followed by the elements to be calculated (operands) which are separated by calculation operators. The operands can be constant values, a cell reference, a range reference, a label, a name or a worksheet function. The order of elements in the formula defines the final result of calculation. A formula can have maximum of 8192 characters.
Following components can be used in building up a formula:

1. Value: Numeric, e.g., 182.6, String, e.g., “Computer”.
2. Cell Address: A10, A6: B9, Sheet 3!B6.
3. Function: Sum ( ), Average ( ), Max ( ), Min ( ), Count ( ).
4. Operators: A, * and / , + and – (Mathematical operators), =, < >, >=, <=, >, < (Relational operators) AND, OR, NOT (Logical operators).
5. Parenthesis: To control the order of operator precedence, e.g., = (A5 + A10)/B6.
6. File Name: For example, Sample2.xls to link the formula to another workbook.
7. Use of Values: Values can be joined by using mathematical operators, e.g.,
   = 18.0 + 19.6 + 38.2 * 6.0.
   The cell will display the result of this expansion and formula will be displayed on the formula bar.
   You can also join text with result, e.g.,
   “Sum of numbers is” &A6
   & here is used to join a string with a string or numeric value. The text has to be enclosed in quotes and & sign is used to join text with numeric value. This process is called concatenation. For example, =”Computer” and “Science”. The result of this formula will be “Computer Science”.

Errors Encountered
When you enter a formula Excel might give one of the following errors:

1. #### = The column is not wide enough to accommodate the formula
2. # Div/0 = Division by zero
3. # N/A = Data is not available
4. # Name = The cell reference is not known to Excel
5. # NUM = The number being used in formula is not acceptable
6. # REF! = The cell being referred to is not valid or has been deleted
7. # Value! = Formula has an invalid operator

Writing Formula in a Worksheet
Let us take some simple examples to see how formula works in Excel. In Fig. 12.48, data has been entered in cell A1 to A6 and B1 to B6. Now typing the formula = A1 + B1 in cell Cl will display sum of 23 and 56 i.e., 79 in cell Cl. If we click on cell Cl, the formula used to get the value 79 will be displayed in formula bar. Similarly, 375 in cell B7 is a result of formula = B1 + B2 + B3 + B4 + B5 + B6.

Insert Chart
Excel has an important feature which helps us in representing the data in the form of a chart. Charts are visually appealing and make it easy for user to see comparisons, patterns and trends in data. For example, in order to compare the monthly sales of various
sections of a department, we will have to go through the figures in worksheet. Instead, if we draw a chart, it will be easy to judge the trend of sales of all sections over the year as shown in Fig. 12.49.
The same chart can be converted to various other forms like bar graph, pie chart, radar chart etc Several options are given in the chart wizard window.

Create a Chart
Step 1: (i) Select the data cells for which you want to draw the chart.
(ii) Click on right bottom arrow in Chart group of Insert menu.
(iii) A window will appear (Fig. 12.50) showing various chart types and chart subtypes.
(iv) Let us choose column (Clustered column) and press Next.

Step 2: A dialog box will show a preview of the chart with data range. Click on Next.
Step 3: In this step, you can add a title to the chart (Sales in first half of the year). Category (X) axis —> month and value (Y) axis —> sale in thousand rupees. These can be added by choosing the related option from labels group under chart layout menu.

Gridlines can be added for X and Y axis to show clarity of data alignment. Legend refers to the colour key being used for each section. It can be placed either on top, bottom, left, right or corner of the chart area.
You can change the chart type by right click of the mouse. From the shortcut menu, select Change Series Chart Type and a window will appear. Choose any other chart type and follow steps 1 to 3. For the same data Pie chart is shown in Fig. 12.52 and Cone graph is shown in Fig. 12.53.

AutoCorrect
This feature helps in checking the spellings entered in data and corrects them. In Excel 2010 choose Review —> Spelling —> AutoCorrect.


A dialog box will appear where you can enter the misspelled *word and you will be asked to fill in the correct spellings.

Insert Comment
Comments are actually notes that you can add to a cell. A cell showing a small red triangle on its upper right corner indicates that there is a comment attached to the cell which can be viewed by resting the pointer over the cell.

• To add a comment, select the cell and from Review menu click New Comment. A text box will appear near the cell.
  You can enter your comments in the box.
• To view the comment, click on the cell. To view all the comments on the worksheet. Show All Comments from Review menu comment group.
• Reviewing tool bar is used to view the comments one by one. It can also be used to view the previous or next comment.
  

PRINTING FROM EXCEL

• Click File on menu bar.
• Select Print.
• Choose Active sheet to print only the sheet selected, Entire Workbook to print all the
  worksheets.

Set Print Area: A specific area of the worksheet can be printed by selecting it using
mouse.

• Click File —> Print Area —> Select Print Area.
• Click File —> Print.

Print only Chart: Select the chart from document and repeat the above steps.

Foundation of IT