Microsoft Excel

Microsoft Excel is the component of Ms-Office. Microsoft Excel is basically used for doing mathematical and accounting work. Microsoft Excel is a spreadsheet program which allows one to enter numerical values or data into the rows or columns of a spreadsheet, and to use these numerical entries for such things as calculations, graphs, and statistical analysis.

The extension of excel file is xl and the executable file of Excel is excel.

WorkBook: A Workbook is a group of worksheets or we can say that workbook is like a book which contain no. of sheets in it.  By default Excel provides us three worksheets in a workbook but we can increase or decrease the counting of these sheets . 

What is a spreadsheet?
At this moment there is someone in the world who is adding up a list of numbers. In China they invented the abacus, but it was not until the 1950s that mechanical calculators were developed to help total these lists of numbers. In the 1970s electronic calculators took over from the mechanical ones. These still had a major flaw in that once you had added the numbers you could not check that you had entered the numbers correctly. This was solved by developing calculators with printers, which allowed the operator to check the entered values. But unfortunately if an error was found then all the numbers had to be re-entered.

With the advent of the personal computers a spreadsheet program was developed. This allows the operator to enter numbers and equations into cells. The operator can visually check the numbers and change any that are incorrect. Totals are automatically recalculated.

When the uses of spreadsheets became known every accountant in the world wanted one, and from the records of sales nearly all them obtained one.

Excel 2000 is an industry standard spreadsheet. It does lots of fancy things, but you need to understand the basics of spreadsheets.

Example

The spreadsheet enters quantity and price calculating total and a grand total.

Rows : Rows run horizontally in an Excel worksheet. They are identified by a number in the row header. There are 65,536 rows in each Excel worksheet.

Columns : Columns  run vertically in an Excel worksheet. They are identified by alphabets  in the column  header. There are 256 columns in each Excel worksheet.

Cell : When rows and columns intersect each other then a cell is formed. Each cell has its unique address and its own identity . A cell can contain 32,767 characters in it.  A cell value can be edited by using F2 key from Keyboard and by double clicking of mouse. A cell in a sheet with rectangular border is known as Active Cell. When we select more then one cells then it is known as Range of Cells.

Running Excel

To start Excel, choose

Start-Programs-Microsoft Excel.

The Excel worksheet

The diagram below shows the standard components that make up the Excel worksheet. Take a minute now to locate these components on your worksheet.

Figure 1.3

Having worked through an Introduction to Windows and Word, you are now familiar with the four bars at the top of the screen.

A cell is the basic unit of the spreadsheet. The address of a cell is specified using a column letter and a row number, for example A1 as in figure 1.3. You will see to the left of the formula bar a display of the currently selected cell, called the active cell.

On the toolbar click on the downward arrow (to the right) of the 100%. This is the zoom control button. Choose 25%, then 200% and then return to 100%. This shows you that you can get an overview, or a detailed look of your spreadsheet. You will notice that all the cells are the same size. This gives us problems when we want to enter large values. You will be shown how to extend the size of the cells in.

To the right of the zoom button is a button with an arrow and question mark. This is the Office Assistant help button. If you click on the button, the Office Assistant will appear (if it was currently hidden) and prompt you with some help options.

Another great “help” tool you can use is the “What’s this?” pointer. To bring this tool up, choose Help….What’s This? from the menu bar.

This tool enables you to get help or information on an object or icon on the screen, simply by clicking on it. Try clicking on the scroll bar on the right hand side of the screen. The following information should appear.

With these two help tools you should be able to answer most of your own questions.

                       
When you enter something into a cell, Excel decides on the type of value by the first character keyed. Excel recognizes three types of data

Numbers
To enter a number, click on a cell and enter the numeric value. Numbers can be entered as either positive or negative and include fractional parts, for example 1, 2, -6, +7.767

Equations
To enter an equation, click on a cell and enter an equation. In Excel, equations always start with an equals sign, for example =2+3. Entering this equation would display the value 5 in the cell, but if you move the cursor over the cell, the equation will appear in the formula bar.

Text
To enter text, click on a cell and begin typing text (by default text does not start with a digit or the equal sign), for example Sales for 1997

Entering Numbers
In this exercise you will practise entering numbers into cells.

Step 2.1 Point at cell A1 to highlight the cell. Once cell A1 is selected, use the keyboard to enter a value, for example 15.

Figure 2.1

Notice that as you enter the value it is repeated in the formula bar. If the number is correct, just click on the Enter button (or press the Enter key). If the number is not correct, click on the Cancel button and re-enter the number.

Step 2.2 To clear the number, point at the cell and press Del (the delete key) on the keyboard.

Step 2.3 Enter the numbers as follows

Figure2.2

All you have done so far is to enter four numbers into the spreadsheet. The value 10 has been placed into cell B2.

Question
What is the cell reference number that contains the value 40?

You will know Excel has recognised the values as numbers because it has placed them on the right hand side of the cell. If you made a mistake just click on the cell containing the wrong number, then re-enter the number.

Exercise one
Change cell C2(value 50) to a value of 20.

                   
In this exercise you will move and copy a number of cells from one location to another. To select a block of cells, follow the next step.

Step 2.4 Point at cell B2, keep your finger pressed on the left button and drag to enclose the required block.

Figure 2.3

To move a block of cells

Step 2.5 Point the cursor at one of the enclosing edges of the highlighted block of cells. The cursor turns to a white arrow. Drag the block to where you want to go. For this example move it up into the top left-hand corner.

Figure 2.4

To copy a block of cells

Step 2.6 Highlight the block of cells from A1 to B2 as shown in Figure 2.4. Point the cursor at one of the enclosing edges of the highlighted block of cells. The cursor turns to a white arrow. Keep the Ctrl button pressed (a small + will appear beside the white arrow). Drag the block, so that top left hand corner is D3.

Figure 2.5

To clear a block of cells

Step 2.7 To delete a block of cells, highlight the block and then press Del (the Delete key) on the keyboard. Clear the block of cells D3 to E4. Clear the block of cells A1 to B2.

Inserting numbers

A friend from America is staying at your house for the week. Each night he rings his partner back in the States. The toll call costs were $21.24 on Monday, $18.56 on Tuesday, $22.66 on Wednesday, $34.22 on Thursday and $27.56 on Friday.

Step 2.8 Enter them as follows

Figure 2.6

Whoops you have left out Wednesday’s toll call. We want to put it in B3 and shift the other 2 down.What we shall do is insert a complete new row 3. It would be easier just to re-key B3, B4 and B5, but we want to teach you how to insert a line.

Step 2.9 Click on the 3 of the rows. It should highlight the row as follows.

Figure 2.7

Step 2.10 Choose Insert from the menu bar, and then Rows.

Figure 2.8

The spreadsheet should look as follows

Figure 2.9

Step 2.11 Enter 22.66 into cell B3.

Figure 2.10

You can use the same method to insert a column.

If you want to delete a row, simply highlight and from the Edit menu choose Delete. Make sure you understand the difference between the Delete from the Edit menu and the Delete on the keyboard.

Step 2.12 Highlight cells B1 to B5 and clear their contents so you have an empty spreadsheet.

There is obviously not much point in setting up numbers if you are not going to do anything with them. What we shall do now is see how Excel enters equations.

Take for example the following spreadsheet

Figure 2.11

Step 2.13 Click on cell A1 and enter the value 25. Enter the value 14 into cell A2.

In cell A3 we want to put the sum of 25 and 14, but we do not write =25+14 (which is valid). What we really want to say is add the value stored in cell A1 to the value stored in cell A2 and store the result in A3.

Step 2.14 Click on cell A3 to make sure it is selected.

Step 2.15 Enter the following equation =A1+A2

This will total the contents of cells A1 and A2 and store the result in A3. Don’t forget the = sign which tells Excel that it is an equation.

Your spreadsheet should now look as follows

Figure 2.12

Note the equation is printed in the formula bar.

Step 2.16 Click on the Enter button.

Figure 2.13

Note how the equation appears in the formula bar, but the result appears in A3. To emphasise why we enter =A1+A2 rather than =25+14

Step 2.17 Change the value in A1 to 26. The spreadsheet should look as follows

Figure 2.14

See that A3 has changed as well, because the equation tells Excel to add the contents of A1 and A2 together, not 25 and 14.

Exercise two
Enter the values 25 and 15 into cells B2 and B3, and their sum in B4.

Exercise three
Enter values 18.2, 17.4, 5.2 into cells A2, B2 and C2 and their sum in D2. Did you forget the = sign at the beginning of equation?

Subtraction
To find the difference between the values in two cells write

=A1-A2

Multiplication
To find the product of the values in 2 cells write

=A1*A2

Notice the multiplication symbol used is the asterisk *.

Division
To find the ratio of the values in 2 cells write

=A1/A2

Exercise four
Work out what should be calculated for the following equations

Figure 2.15

Enter the spreadsheet to check your answers

Copying Equations
Consider the problem of working out the cost of buying 5kg of potatoes at 95 cents a kilo, 2kg of kumara at $1.85 a kilo, and 3kg of carrots at $1.05 a kilo.

Step 2.18 You could lay it out in a spreadsheet as follows

Figure 2.16

In A2 to A4 we have put descriptions of the product ordered. This is the third type of data Excel recognises, namely text.

Step 2.19 In D2 we would write 5 times 0.95, but as an equation. Enter =B2*C2 into D2.

Figure 2.17

For kumara you would want =B3*C3 and carrots =B4*C4

But wait…… Excel offers you a short cut to do this. In the highlighted cell the bottom right hand corner is called the fill handle.

Step 2.20 Place the cursor over the fill handle (it changes to :). Drag that down through D3 and D4 and it will copy the equations.

Figure 2.18

Check the equations in D3 and D4. To complete the spreadsheet we would like the total cost in D5. The equation would be =D2+D3+D4.

Step 2.21 A simpler way is to define D2 to D4 (if not already defined) and press the AutoSum button in the toolbar.

Figure 2.19

This generates =SUM(D2:D4). SUM is a function and simply means add up the range specified.

So =SUM(A1:A5) is the same as =A1+A2+A3+A4+A5
and =SUM(B2:D2) is the same as =B2+C2+D2.

Press the function button to see other functions.

Recapping copying across

Fig 2.20

Copying A3 to B3 and C3 will give

Fig 2.21

Recapping copying down

Fig 2.22

Copying C1 to C2 and C3

Figure 2.23

This technique is called relative addressing. Note how the cell addresses have changed to reflect the new row that the formula is in.    

A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, to other workbooks, and to data in other programs. References to cells in other workbooks are called external references. References to data in other programs are called remote references.

The A1 vs. the R1C1 reference style

The A1 reference style   By default, Excel uses the A1 reference style, which refers to columns with letters (A through IV, for a total of 256 columns) and refers to rows with numbers (1 through 65536). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50. To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (:), and then the reference to the cell in the lower-right corner of the range. The following are examples of references.

The R1C1 reference style   You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

Relative references   When you create a formula, references to cells or ranges are usually based on their position relative to the cell that contains the formula. In the following example, cell B6 contains the formula =A5; Microsoft Excel finds the value one cell above and one cell to the left of B6. This is known as a relative reference.                                                    ((    Back...    ))

When you copy a formula that uses relative references, Excel automatically adjusts the references in the pasted formula to refer to different cells relative to the position of the formula. In the following example, the formula in cell B6, =A5, which is one cell above and to the left of B6, has been copied to cell B7. Excel has adjusted the formula in cell B7 to =A6, which refers to the cell that is one cell above and to the left of cell B7.

Absolute references   If you don't want Excel to adjust references when you copy a formula to a different cell, use an absolute reference. For example, if your formula multiplies cell A5 with cell C1 (=A5*C1) and you copy the formula to another cell, Excel will adjust both references. You can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1, for example, add dollar signs to the formula as follows:

=A5*$C$1                                                                                                        ((    Back...    ))

3-D references   If you want to analyze data in the same cell or range of cells on multiple worksheets within the workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13.

Refer to the same cell or range on multiple sheets by using a 3-D reference

The workbook must contain more than one worksheet.

Click the cell where you want to enter the function.

1. Type = (an equal sign), enter the name of the function, and then type an opening parenthesis.

2. Click the tab for the first worksheet to be referenced.

3. Hold down SHIFT and click the tab for the last worksheet to be referenced.

4. Select the cell or range of cells to be referenced.

5. Complete the formula.                                                                            

Keys for moving and scrolling in a worksheet or workbook

Keys for moving in a worksheet with SCROLL LOCK on

Tip   When you use scrolling keys (such as PAGE UP and PAGE DOWN) with SCROLL LOCK turned off, your selection moves the distance you scroll. If you want to preserve your selection while you scroll through the worksheet, turn on SCROLL LOCK first

Keys for working in

Keys for

Keys for

Keys for moving within a selection                                                        (( Back... ))

Keys for selecting cells, columns, or rows

Tip   When you use the scrolling keys (such as PAGE UP and PAGE DOWN) with SCROLL LOCK turned off, your selection moves the distance you scroll. If you want to keep the same selection as you scroll, turn on SCROLL LOCK first.

Keys for extending the selection with End mode on

Keys for selecting cells that have special characteristics

Tip   You can select any menu command on the menu bar or on a visible toolbar with the keyboard. To select the menu bar, press ALT. (Then to select a toolbar, press CTRL+TAB repeatedly until you select the toolbar you want.) Press the underlined letter in the menu name that contains the command you want. In the menu that appears, press the underlined letter in the command name that you want.

1. Press ALT+U to select the AutoShapes menu on the Drawing toolbar.

2. Use the arrow keys to move to the category of AutoShapes you want, and then press the RIGHT ARROW key.

3. Use the arrow keys to select the AutoShape you want.

4. Press CTRL+ENTER.

Tips Of Excel                                                    ((  Top  ))

Worksheet and workbook specifications

Calculation specifications