A spreadsheet is defined as a large sheet which contains data and information arranged in rows and columns.
❉ Spreadsheets, also known as worksheets, allow us to perform detailed analysis on numerical data. Data is entered in a cell, which represents the intersection of a row and a column.
❉ The most powerful feature of a spreadsheet is that it automatically recalculates the result of mathematical formulas if the source data changes.
❉ A spreadsheet can help us quickly record and manipulate a large amount of numerical information and share it with others in a wide variety of forms.
❉ Since MS-Excel, an integral component of MS-Office, is one of the programs which has all these features and many more we have taken it as a spreadsheet program.
Steps to start MS Excel
1. Click on start button on the taskbar
2. Click on program option in the pop up window
3. Click on Microsoft excel
Elements of Spreadsheet
1. Workbook and Worksheet:
❉ Each spreadsheet file is known as a workbook and is stored with a default extension of .xls.
❉ Each workbook can contain many sheets, so various kinds of related information can be organised in a single file.
❉ Each workbook can contain up to 255 worksheets.
❉ Worksheet is the area where the data is stored and work is performed.
❉ Extra worksheets can be added as and when required.
2. Rows, Columns:
❉ The rows in a worksheet are numbered from top to bottom along the left column of the worksheet.
❉ The columns are labelled from left to right with letters.
❉ The total number of rows in Excel are 65536.
❉ The rows are numbered from 1 to 65536.
❉ The total number of columns are 256.
3. Cells:
❉ A cell is the intersection of a row and a column.
❉ A cell is identified by an address that consists of the column name followed by the row number.
For example, the first cell is referred to as A1, which indicates that it lies at the intersection of the column A and row 1. This is the active cell.
❉ The active cell is ready for accepting any action or input. A small group of contiguous cells is defined as a range.
❉ The range is referred to by writing the starting address of the cell in the range: Ending address of the cell in a range or vice versa.
Example A1 : A10 (can also be referred as A10 : A1).
How To navigate a Worksheet?
The cursor keys, mouse and the scroll bar can be used to navigate through the worksheet. However, navigating through the 65,536 rows and 256 columns using these techniques is very inefficient.
❉ To move to any cell directly without scrolling through the entire worksheet, the user is required to:
1. Type the cell address in the Name Box
2. Press ENTER to reach the desired cell.
For example, to move to cell E5, enter E5 in the Name Box and press ENTER. The cursor is positioned on the cell in the E column and 5th row.
How to Save a Workbook?
1. Choose the Save As option from the File menu or Click on the Save button on the Standard Toolbar or Close the workbook by clicking on the Close button. The Save As dialog box is displayed.
2. Select the directory in which the file is to be saved.
3. Type the name of the file in the File Name text box.
4. Click Save.
How to Open a workbook?
To open a workbook
1. Choose Open option from the File menu or Click on the Open button on the Standard Toolbar.
2. Select directory in which the file has been saved.
3. Type the name of the file in the File Name field or select the name by clicking on it.
4. Click Open.
Using Formulas and Functionss
Formulas are entries containing an equation that calculates the value to be displayed.
❉ When working with formulas, do not type in the numbers but type in the equation.
❉ This equation will be automatically updated upon the change or entry of any data that is referenced in the equation.
Entering Formulas:
❉ Cells in a worksheet can also contain formulas that are helpful in performing calculations. Formulas are mathematical equations.
❉ They are useful for establishing the relationship between two or more cells.
❉ They contain the coordinates of the cells that are used in the formula, operators and functions.
❉ When a formula is entered, the cell displays the result of the formula. Formulas must begin with an = sign, otherwise it is treated as a text entry.
Arithmetic Operators:
❉ When a number is entered into a cell, it is possible to perform mathematical calculations using them.
❉ Spreadsheets have many mathematical functions built into them. The most basic operations used widely are addition, subtraction, multiplication and division.
Auto Sum:
❉ The addition of numbers is one of the most frequently used actions. Thus, a toolbar button,
AutoSum, has been provided to accomplish this task.
❉ The AutoSum button on the Standard toolbar automatically adds the values above the destination cell or to the left of the destination cell. The steps to illustrate this are as follows:
1. Click on the destination cell i.e. the cell in which the result is to be displayed.
2. Click on the AutoSum button, which is located on the Standard toolbar. The cells containing the numbers to be added automatically should now be highlighted.
3. Press Enter to see the result in the destination cell.
Functions:
A set of prewritten formulas called functions.
❉ Functions are special programs that accept data and return a value after processing the data.
❉ Functions differ from regular formulas because they accept values and not the operators, such as +, -, *, or /.
❉ SUM function can be used to add numbers in place of the ‘+’ operator. When using a function, do the following :
a. Use an equal to sign (=) to begin a formula.
b. Specify the function name
c. Enclose arguments (data accepted by a function) within parentheses.
d. Use a comma to separate arguments.
Copying and Pasting Formulas and Functions
Sometimes when working with formulas, the need arises to repeat the same formula for many different cells. The formulas can be copied using various methods:
I. Using the Edit Menu:
❉ Click on the cell that contains the formula.
❉ Choose the Copy option from the Edit menu.
❉ Click on the cell where the formula is to be copied.
❉ Choose the Paste option from the Edit menu.
Note the change in the cell reference.
❉ Press Esc to exit the Copy mode.
II. Using the formatting Toolbar:
❉ Click on the cell that contains the formula.
❉ Click on the Copy icon, located on the Formatting toolbar or press Ctrl + C keys.
❉ Click on the cell where the formula is to be copied.
❉ Click on the Paste icon, located on the Formatting toolbar or press Ctrl + V keys.
❉ Press Esc to exit the Copy mode.
❉ If a formula needs to be copied to multiple cells, use the AutoFill feature.
CELL REFERENCING
When we copy and paste formulas, they are pasted relative to the position they are copied from. This is because of the way the formula treats the cell references. The cell coordinates in the formula are known as cell references. Two commonly used cell references are – Absolute and Relative.
1. Absolute referencing:
+ Absolute referencing implies that the coordinates of a cell are not changed when a formula is copied from one cell to another.
+ To make a cell address an absolute cell address place a dollar sign in front of both the row and column identifiers.
For example, $A$1 implies that both row and column have been fixed or made absolute. In other words this means that while copying this formula to another cell, neither the column name nor the row number will change.
2. Relative Referencing:
With relative cell referencing, when we copy a formula from one area of the worksheet to another, it records the position of the cell relative to the cell that originally contained the formula. This is the default mode of referencing in a spreadsheet.
+ The F4 key is used to toggle between the absolute and relative modes of referencing cells.
MICROSOFT OFFICE
Checking Spelling and Grammer: Checking spelling and grammar in a document after we finish typing and editing the text is very useful.
❉ We can check for possible mistakes and then confirm each correction. In Microsoft Word, we can start the spell checker by clicking on Spelling and Grammar option under Tools menu.
❉ We can use one of the words suggested by the word processor and click Change.
❉ We may want to change the word ourselves.
Click the word on the document (not in the Spelling and Grammar dialog box) and edit it. After fixing the error, click on Resume.
❉ The indicated word may actually be the correct one, but not available in the Dictionary; for example, the name of the town Hyderabad. To include such word into the word processor’s dictionary, click Add to dictionary.
If, we would like to ignore it, click Ignore once. If we want to ignore all instances of such cases in the document then click Ignore All.
❉ After we resolve each misspelt word, the word processor flags the next misspelt word so that we can decide what we want to do.
❉ After the program finishes flagging the spelling mistakes, it begins showing us the grammatical mistakes. We can proceed in a similar fashion to correct those also.
❉ Similarly, the grammar mistakes are flagged by a wavy green underline – we can correct these just as we corrected the spellings.
However, don’t blindly accept the corrections suggested by the word processor. The word processor is not always accurate in detecting spelling and grammar errors!
