EXCEL MADE EASY

INTRODUCTION

A working spreadsheet (often called a worksheet or Document) is the electronic equivalent of a large sheet of squared paper divided into ROWS and COLUMNS. Each square on the worksheet grid is known as a CELL. Spreadsheet has 256 columns lettered A through IV, and 65,536 rows and has 16 million plus cells. Cell may contain text (label) or number (values). Unlike a sheet of paper, the cells within an electronic worksheet may also contain formula, which define the contents of many other cells. A particular cell, for example, might contain the result of adding up the contents of many other cells. Formula provide the ability to keep totals in a worksheet “up-to-date” automatically.

A spreadsheet is a problem-solving tool, which may be applied, in an infinite number of ways to solve real problems. Using a spreadsheet, you may represent a problem with a ‘model‘ with which you may ask ‘what if‘ question – seeing what happens to certain numbers if you change others – with answers being calculated immediately. Here, the term problem is used loosely and refers to any information that can be used within a worksheet.  This might, for example, include: analysing results from an experiment; a train time-table; exploring mathematical concepts; company or home finances; simulations; an invoice; or costing.

Microsoft Excel Screen

Part	Description
Title bar	The is the horizontal bar that contains the name of the window. Most title bars also contain buttons to close, minimize, and resize the window.
Menu bar	The displays a list of commands that are grouped into sections. Some of these commands have images next to them, so that you can quickly associate the command with the image. This image is also on the button that corresponds to that comman
Toolbars and formatting bars	The user interface of Excel contains the same bars and toolbars found in other Microsoft Office programs, such as Word. These bars and toolbars include the title bar, the menu bar, and the standard and formatting toolbars. Besides these, the most commonly used toolbars in Excel are formula bar and status bar.
Columns	The vertical divisions in a worksheet are called columns. Each column is identified by a letter. For example, the first column in a worksheet is A.
Rows	The horizontal divisions in a worksheet are called rows. Each row is identified by a number. For example, the first row in a worksheet is 1.
Cell	A cell is formed by the intersection of a row and a column. You can use a cell to store and display different types of data such as text, numbers, or formulas.
Cell address	Each cell in a worksheet is identified by a cell address. A cell address is made of the column letter and row number of the cell. For example, the cell formed by the first column and first row has the cell address A1. The cell address indicates the exact location of a cell in a worksheet.
Cell value	Cell value is the actual numeric value of the data in a cell. This value might not be the same as the data displayed in the cell. For example, when you enter a numeric data 9 in a cell, the cell value is 9. However, when you enter a formula =A1+A2 in a cell, the cell value is equal to the sum of the cell values of the cells A1 and A2. A blank cell has the cell value of zero.
Cell pointer	A cell pointer is the highlighted rectangular border formed around an active cell. An active cell is the cell in which you are working currently. There can be only one active cell at a time.
Label	A label is a text entry that is used to identify the type of information in a row or a column of a worksheet. For example, if you want to store the student details of your class in a worksheet, you can label the columns with meaningful titles such as Student Name, Roll Number, and Student Address. You can then enter relevant data in these columns. Labels do not have any cell value associated with them.
Cell range	A cell range is a block of cells selected to implement a formula in a worksheet. A range of cells can extend across a row, a column, or over a combination of rows and columns. All cells in the range must be adjacent, sharing at least one common border.
Workbook	A workbook is a file created in Excel. A workbook can contain one or more worksheets and related items. By default, an Excel workbook contains three worksheets. You can add or delete worksheets in a workbook as required.
Formula bar	The formula bar is used to enter or modify data in a worksheet. This bar is activated as soon as you start entering data in a cell. When you type data in a cell, the data is displayed simultaneously in the active cell and in the formula bar. However, when you enter a formula in a cell, the formula is displayed in the formula bar, whereas the numeric value of the formula is displayed in the corresponding cell.
Status bar	The status bar provides information about the present status of work in the worksheet. For example, if you are not working on the worksheet, the status bar displays the message Ready. When you click in a blank cell to enter data, the status bar displays the message Enter. Similarly, when you click in a cell that already contains some data, the status bar displays the message Edit, which means you are editing the present data.

The work area is the blank space within the program where you enter information.

Scroll bars are vertical and horizontal bars located at the side or at the bottom of a display area. You can use scroll bars to move to a specific location in the work area.

The status bar is a horizontal bar that is at the bottom of some productivity programs. This bar displays information about the current status of the program or document that you are viewing.

Task Pane window is used to quickly access your most commonly used tasks (such as creating a new workbook or opening an existing file). 

Towards the bottom of the worksheet is a small Tab that identifies each sheet within the workbook (file).  If there are multiple sheets, you can use the tabs to easily identify what data is stored on each sheet.  For example, the top sheet could be “Expenses” and the second sheet could be called “Income”.  When you begin a new workbook, the tabs default to being labeled Sheet1, Sheet2, etc

Worksheets

Microsoft Excel consists of worksheets. Each worksheet contains columns and rows. The columns are lettered A to IV; the rows are numbered 1 to 65536. The combination of column and row coordinates make up a cell address. For example, the cell located in the upper left corner of the worksheet is cell A1, meaning column A, row 1. Cell E10 is located under column E on row 10. You enter your data into the cells on the worksheet.

The Formula Bar

If the Formula bar is turned on, the cell address displays on the left side of the Formula bar. Cell entries display on the right side of the Formula bar. Before proceeding, make sure the Formula bar is turned on.

Although it is possible to operate Excel by use of the keyboard alone, it is assumed that a mouse is attached to your computer system, providing a much more convenient method of control.  Reference to keyboard and mouse operations is thus made throughout the text.

Keyboard controls    As you use Excel, you will find that a combination of mouse and keyboard controls leads to the most effective operation of the program.  Keyboard control requires the user to remember many keystrokes, whereas mouse control is more intuitive.  It is therefore recommended that a mouse (if available) is used for initial control of Excel.

                             While it is assumed in this Guide that a mouse is present, it is not essential that you have one. Equivalent keyboard controls are show in brackets throughout  the text and are summarised in Appendix A

                            

                             The following conventions are used to indicate Keyboard controls:

Function key       You may have 10 or 12 function keys, above or to the left of the main keyboard, marked F1 to F12.  These will be shown boxed throughout the text as  F1  to  F12.

Slash key            The  / key is used is used in association with other keys.  A sequence such as /FA  would indicate that you PRESS the \key, and then PRESS  F  key and then A key.

Backslash key    The \ key is used in association with other keys.  A sequence such as  \= would indicate that you PRESS the \ key and then PRESS the = key.

Alternate key      The Alt (Alternate) key is used in association with other keys.  A sequence such as Alt F would indicate that you PRESS and HOLD DOPWN the Alt key and then PRESS the F key.

Control key         The Ctrl  (Control key) is used in association with other keys.  A sequence such as Shift F7 would indicate that you PRESS and HOLD DOWN the shift key and then PRESS the F7 key.

Shift key              The Shift key is used in association with other other keys.  A sequence such as Shift F7 would indicate that you PRESS and HOLD DOWN the Shift key and then PRESS the F& key.

Enter key             The Enter (or Return) key, in the context of program control, is used to signify the completion of an operation.  Throughout the text, this key will be shown as Enter.

Backspace key   The Backspace (or delete) key, is used to delete characters to the left of the current cursor position.  This key is found at the top right-hand side of the main keyboard and is often marked with just a left-pointing arrow.

Cursor control keys

 ç                        Left cursor control key shown in the text as Left

è                         Right cursor control key shown in the text as Right

é                         Up cursor control key shown in the text as Up

ê                         DOWN cursor control key shown in text as Down

                            

                              

PgUp                   Page up key

                            

PgDp                   Page down key

                              

Ins                        Insert key

                            

Del                       Delete key (not to be confused with the Backspace key)  used to delete to the right of the cursor.

Esc                      Escape key

Using the mouse

The mouse actions used in Excel are exactly the same as the ones you used in Word for Windows and the ones you learnt in the mouse tutorial at the beginning of the course.

Basic mouse techniques

Below are some of terms used to describe mouse techniques and their meaning:

Term                             Meaning

Click                     To quickly press and release the mouse button.

Double-click                   To click the mouse button twice rapid succession.

Drag                    To press and hold down the mouse button while you move the mouse lightly on    the desk.

Point                    To move the mouse until the mouse pointer on the screen point to                                        the item of choice.

Short cut menu’s - the Right mouse button

Many things described in this manual can also be performed in another way.  One of the most useful features is the short-cut menu.  This provides quick access to some menu commands, and is especially useful for:

·        Formatting numbers, and text.

·        Copying & Pasting.

·        Inserting Rows & Columns.

·        Adding Patterns and Borders.

·        Showing Toolbars.

Using the short-cut menu

Click on the Right mouse button while the cursor is over a selected area or part of the screen.

 

The options on the short-cut menu will change depending on what you are doing at the  time.  So selecting a row or column will give you the option to change row height or column width.

Chapter 2: Basic Principle

This manual has been designed to take you through the features of Excel.  Each chapter has an exercise at the end of it.  Leading up to each exercise are some Excel features and the commands you will be using for that particular exercise.

Moving around the Worksheet

You can move around the worksheet by moving the cell pointer using the mouse or the keyboard.

 

Up one cell.

Down one cell

Right one cell.

Left one cell.

CTRL +                                   Last entry ( to right ) in the current series.                                            

CTRL +                                             Last entry (to left ) in  t he current series.

HOME                                                 Left - most cell ( in column A ) of the current row.

END + HOME                                  Move to bottom-right cell  of current worksheet.

CTRL + HOME                                Move to cell A1.

CTRL + END                                   Move to bottom-right cell of current  worksheet.

PgUp                                                Moves cursor up one screen.

PgDn                                                Moves cursor down one screen            

Basic Text and Number entry

Anything that you type will appear in the third line of the screen (the ‘Formula Bar’ at the top), as well as in the cell itself.  A tick and a cross also appear on the Entry line to indicate that you have changed this entry. Press the Return key when have finished entering for that cell.  (Or you can click on the Tick to confirm).  The tick and cross will then disappear

ADJUSTING COLUMN WIDTH & ROW HEIGHT

USAGE:                        Sometimes cell entries are too long to fit into the standard width columns.  Text will appear to "spill" over into adjacent cells as long as those cells are empty.  If the adjacent cells are not empty, Excel will truncate the text.

When entering large numbers, however, Excel will display the number in scientific notation if the column is not wide enough to display the entire number.  You may also see a large number (resulting from a formula) displayed as ##### - indicating that the column is not wide enough to display the answer contained within the cell.  However, if you apply formatting (such as dollar signs), Excel will automatically adjust the column to fit the largest entry so that the number remains visible.

Should a cell be too narrow for text or numbers that you have entered, you can widen the column in which the entry is located by following the steps outlined below:

       Place the mouse pointer in the column heading area to the right of the lettered column to adjust.  Make sure the mouse pointer is on the column margin line.  The pointer changes to a cross-hair  indicating you are on the margin line.

       Click and drag the column margin line either to the right (expanding it) or to the left (shrinking it). 

In the example above, column F is being stretched to the right.  Notice the "cross-hair".

NOTE:       To change the height of a row, place the mouse pointer on the bottom line of the row number (located on the left side of the worksheet).  A thin cross-hair will appear.  When you see the thin cross-hair, drag the mouse up/down to adjust the row’s height.

TIP:                      If you double-click the column or row margin line, Excel will automatically adjust the width of the column or row to fit the largest cell in the column/row.

NOTE:       You may also change the width of multiple columns or rows at once, by selecting the columns or rows (by clicking and dragging the mouse over each of the column letters or row numbers) before adjusting their width/height.

It is also possible to change the column width through the menu:

MENU:

Format

column   Ø

Select width... from the sub-menu.

The following dialog box will be displayed:

Enter a new column width and choose .

NOTE:       You can also choose AutoFit from the Format à column à autoFit Selection menu.  You must have the entire column or the widest entry selected for this option to work.

Saving a spreadsheet file

For the first time

1.   Click on the Save icon.

2.   Type the filename and specify the drive and directory you are saving on.

3.   Click on OK

You may be asked for Summary Information, depending on how your application was Set-up.  Enter the details you want and then click on OK.

Saving again

1.     Click on the Save icon.

Will automatically save under the same name and replace the old one.

Saving under a different name

1.     Click on the File menu.

2.     Click on Save As.

3.     Type in the Filename and specify the drive and directory name

4.     Click on OK.

Print Preview

Click on the Print Preview icon.

 

Zooming In & Out

To zoom in:                                                                            To zoom out::

1.   Move the mouse over the area to zoom in on.                       Click once more on page

2.   Notice your cursor is now in the shape of a magnifying glass.

3.   Click.

Printing

Click on the Print icon.

A dialogue box will appear informing you that the spreadsheet is printing.  When the box goes the spreadsheet will be printed.

OR

You can print from the File menu.

1.   Click on the File menu.

2.   Click on print.

3.   Click on OK.

Printing using the Print  icon will use whatever the previous print settings were.  You can change the number of copies, the page range and what you want to print only through the print option in the File menu.

Printing only part  of the spreadsheet

1.   Select the area you want to print by clicking on the left mouse button and dragging it over the area.

2.   Click on the File menu.

3.   Click on print. 

4.   You will now be taken into the print dialogue box and you must specify here what you want to print.

5.   Click on  “Selection” in the print  what box

Exiting Excel

1.     Click on the File menu.

2.     Click on Exit.

If you haven’t saved any of your worksheets you will be asked if you want to save before you are allowed to Exit.

Exercise 2

Enter the following data into your worksheet.

COMPUTER STUDIES LTD
Department	January	February	March	April	Total
Accounts	10500	8550	14500	12200
Admin	12500	12000	13000	14900
Marketing	8000	7000	9500	9000
Sales	14000	12500	16000	16200
Research	3500	3750	3600	3800
Total / Month

Save the spreadsheet as EXC 1.

1.   Practice moving around your worksheet.  Notice how the Cell Address in Line 3 changes as the cursor box moves.

2.   Column A is not wide enough to fit the headings in it so they seem to carry on into the next column.  Change the width of this column so that it is wide enough.

3.   Save the spreadsheet to your floppy disk as EXC 2.

4.   Preview your spreadsheet, and then print a copy of the entire spreadsheet.

5.   Exit Microsoft Excel and return to the Program Manager screen.

 Chapter 3:    Working on your spreadsheet

Opening one of the last 4 worksheets you worked on

1.   Click on the File menu.

2.   The last four files you used will be listed at the bottom of the File menu.

3.     Click on the desired worksheet.

Opening from a floppy disk

    i.     Click on the File open icon.

  ii.     Click on the arrow underneath Drives.

 iii.     Click on 3^1/2 floppy A:

iv.     Click on the desired worksheet.

  v.     Click OK

Replacing the contents of a cell

1.   Select the cell.

2.   Type in the new contents and press Return (or click on the tick).

Correcting the contents of a cell

    i.     Double-click on the cell.

  ii.     ( Or press F2 ).

 iii.     Make the changes you want.

iv.     Use the arrow keys, DEL and Backspace to alter cell contents.

  v.     Press Return.

Erasing cells

You can erase ( blank ) the contents of any cell or cells as follows:

    i.     Select Cell  (s).  Click on the cell to be blanked.

  ii.     Press Delete on the keyboard. 

Undo

If you make a mistake and change a cell you didn’t mean to - or even accidentally delete a whole range of cells - your cell - you can use the Undo command.

Click on the Undo button.  ( Or Edit menu and then select Undo ).

Deleting Rows and Columns

You can use this option to delete row or columns.  TAKE CARE not to delete cell with  information you need.

1.   Highlight the range of cell you want to delete.

2.   By clicking on the first cell and dragging your mouse over the rest of them to highlight them.

3.   Click on the  Edit menu.

4.   Click on Delete.

5.   The Delete dialogue box will appear.

6.   Select Entire Row or Entire Column and click on OK.

You row / column has now been deleted and everything else shifts to replace it.

Remember you can also use the right mouse button, after you have highlighted the cells, to  bring up the short-cut menu.

Insert Row and Column

Excel allows you insert one or more columns / rows in your worksheet.  You have select the same number of rows as you are inserting to the right or below where you want them.

Inserting a row

§  Select the row below the row you want to insert a row.

§  Click on the Insert menu.

§  Click on Rows.

Inserting a Column

§  Select any cell to the right of the place you want to insert a column. Click on the Insert menu.

§  Click on Columns.

Inserting several Rows or Columns

§  Click and drag over number of columns or rows to be inserted.

§  Click on the Insert menu.

§  Click on Rows or Columns

Exercise 3

1.   Open your spreadsheet file EXC2. XLS from your floppy disk.

2.   The March figure for Accounts is incorrect.  Delete this figure and replace it with 15000.

Also the Research department has been renamed Development. 

3.   The Sales department  no longer exists.  Delete this row completely from the spreadsheet.

4.   Three new departments have been added.  Insert three blank rows after Development.

5.   Type in the three new department names:

Personnel

Training

Promotions

6.   Insert two blank columns after the column for April.  These will be columns for May and June.  Enter the following figures for May and June:

                                                May                                         June 

Accounts                                 12500                                              13500

Admin                                      11500                                              12500

Marketing                                10000                                                9700

Development                             4000                                                 3600

Personnel                                 12000                                                12500

Training                                   16000                                                14000

Promotions                              12500                                                11000        

                    

7.   Save your spreadsheet with the filename EXC3

8.   Exit Excel and return to Program Manager.

Creating a Series Using Auto fill

Let’s start by entering the months of the year. You will use the Series function, which allows you to enter the first value in the series and have the computer enter the rest. 

1.   Move the cursor to cell A1.

2.   Type January.

3.   Click on the checkmark located on the Formula bar. This will enter January into the cell. Clicking on the checkmark is similar to pressing Enter.

4.   Drag the Fill Handle (the small square located in the lower right corner of the cell) to cell A12. Point to the Fill Handle and hold down the left mouse button while you move the cursor down to cell A12. The months February through December should fill the cells.

Chapter 4: Numeric Operators and formulae

These comprise:                                                                      

·        Addition

·        Subtraction

·        Multiplication

/         Division

Numeric formulae

Numeric data and the operations to be performed on them are termed formulae.  The power of spreadsheets comes from the ability to set up formulas  in cells, and to re-calculate when figures

change.

NOTE:    You should always start a formulae in Excel with an equals sign ( = ) so that

               Excel  knows it is a formulae and not just text to be displayed.

Cell Addresses

In spreadsheets, formulae often include Cell addresses of numeric data.  For example, if you wanted to sum the figures in columns A,  B, C, and D:

	A	B	C	D	E
1	15	20	12	15

Then your formula would use the cell addresses.  So in cell E1 you would enter the formula:  =A1+B1+C1+D1.  Excel will calculate this for you and putt the answer  in cell E1.

It is important to always use cell addresses for formulae,  For example, if you changed the figure in cell B1 above, the total would change as well because the formulae contained the address or location of that cell and it doesn’t actually matter what value was in there.

If you had not used the cell address and used actual number in the formula d 20 in it, when you change that figure the total would not change.  You would have to go and change the formula.

Entering a formula

1.   Select the cell where the result is to appear.

2.   Type = and then the formula.

For example, type in =B4+B5+B6

3.   Press Return or click on the tick.

Entering a formula by pointing

You don’t need to write in the cell references.  Simply point to them instead.

1.   Select the cell where the result is to appear.

2.   Typing =

3.   Click on the first cell.

4.   Type +

5.   Click on the next cell.

6.   Repeat this until you have entered all the cell addresses.  Press Return or click on the tick.

Pointing can be used on all formulas, instead of typing them.

Order of precedence

Where data has several operations to be performed on it, rules of precedence determine the order in which the operations are carried out.

he order of precedence is:

The  BDMAS  rule.

( )      Brackets

/*       Division, Multiplication

+-      Addition, Subtraction

It is important to follow this rule, because this order of precedence can effect your answer.

For example:  20 - 3 * 5

          Using the order of precedence rule then you would first multiply 3* 5 and then subtract the answer     from 20.  So your answer would be 5.

          However,  if you had  not used the rule and subtracted 20 - 3 and then multiplied by 5,  you would          have got an incorrect answer of 85.

Example exercise:

Try these examples now:-

1.   (5 - 3) * 5                                              2.   5 - 3 * 5

3.   3 + 7 + 4 *  2                                        4.   (5 - 3) * (1 + 4) / 4

5.   (5 -2) * 3 + (8 - 6 / 2)                                      6.   9 + 6 / 3 - 2 * 4

Aligning Text /Numbers within a cell

You can align your text or numbers within a cell so that they are on the left, in the centre, or on the right.

	Left                        Centre                                                                                                 Right

 

1.     Here’s how you do it

2.   Select the cells you want to change.

3.   Click on the appropriate icon:-

Take a look at the picture of the Formatting toolbar.  There are 3 icons in the middle used for aligning text.  These are:  Left Align, Right  Align and Centre.

Centering Text / Numbers across columns

This is useful to centre a heading across a table for instance. 

1.     Select the cell to centre and the range to centre across.

2.     Click on the Centre Across Columns icon.

This is next to the Right Align icon.

NOTE:                 When you centre across columns the text looks as if it ‘s in a new cell.  It’s not, it’s just displayed that way.  To edit it you must select the original cell ( You can check if you have correct one by seeing if the text appears on the entry line ).

Closing a worksheet

1     Click on the File menu.

2       Click Close.

Starting a New blank worksheet

Click on the New File icon.

Exercise 4

1     Open the file EXC3.XLS

2     Enter a formula in cell H5,  in the total column, to add up all the figures from January to June:

=B5+C5+D5+E5+F5+G5

3     Now enter formulas for all the other departments.  Remember:  formula use cell addresses.

Enter formulas for the Total / Month row to add up each column’s figures.  For example, the formula for the Total / Month for January is:

=B5+B6+B7+B8+B9+B10+B11

NOTE: Even though the last three cells for some of the columns are blank, include their cell addresses in the formula you enter for the Total / Month.  If you were then to enter some figures into these cells another time, then the formula will  automatically add them to the total and update the Total / Month figure.

§  Align all the month names so that each one is Centered in the cell it is in.

§  Centre the heading COMPUTER STUDIES LTD  so that it is Centred across the columns.

§  Save your worksheet with the name EXC4.

§  Preview and print your document.

§  Print only the column showing the total for each department.

§  Close your file and then open a New blank worksheet.

Chapter 5 Functions and formulae

In the previous chapter we used formulas to add up a series of numbers next to each other.  We wrote the formulas in the format, =B2+B3+B4+B5+B6.  This can be very time consuming, especially if there are a lot of numbers to add up.  The Sum function allows us to give the Range of cells that that we want to add up, so there is no need to specify each cell individual.  You need only specify the first and last one in the range.

The Sum Function

The Sum function adds a range of numbers.

Summing a row or column

§  Select the cell where the result is to appear.

§  Type =SUM(

§  Type first cell reference.  For example, B4

§  Type a Column :

§  Type or select second cell reference.  For example, B11

§  Press Return or click on the tick.

NOTE:                 You don’t have to type the final bracket in the =SUM(B4:B11) function, Excel will do it for you when press Return.

Summing by Pointing

§  Select the cell where the result is to appear.

§  Type =SUM(

§  Click and drag over the figures to add.

§  Press Return.

The Golden Rule for the SUM Function

When adding a row or column of figures using =SUM(), you should follow this rule.

Always have a blank row between the last item and the SUM

Formula, and make sure it is included in the SUM range even

though there are no figures in that cell.

This is because if you insert extra rows, and add some more figures, the formula will automatically adjust, provided you had followed this rule.

Using AutoSum for quick SUM

When you use AutoSum, Excel will make a guess at what you wanted to add up and put the SUM() calculation on the entry line.  The figures to be added up will be surrounded by a flashing dotted line.

§  Select the cell where you want the result to appear.

§  Click on the AutoSum  icon.

§  Shown by this symbol å  on the Standard toolbar.

§  Check to see that Excel has guessed correctly by looking at the flashing dotted line.

§  Press Return.

§  The result will appear in the cell.

The AutoSum icon on the Standard toolbar automatically creates a SUM function. The following illustrates using the SUM function to total the Region 1 sales: 

1.   Press F5.

2.   Type B16.

3.   Press Enter.

4.   Click on the AutoSum button, which is located on the Standard toolbar. 

B4 to B15 should now be highlighted. 

5.     Press Enter.

What if Excel guess’s wrong

If the numbers highlighted are not the ones you want to add up, then use the mouse to highlight the correct numbers before you press return.

Some Tips for AUTOSUM

The AUTOSUM feature can speed up things for you a lot, if used properly.

·        If you select the cells you want to add and the blank cell where you want the total to be, then click on the Autosum icon, Excel will not need to guess what you want to add up and will create the correct formula first time.

·        If your data is in a table type layout, you can highlight all the figures, together with the blank cells that will contain the SUM formulas and click on the AutoSum icon.  Excel will create all the SUM formulas in one go.

·        If you select a range that includes some totals already calculated, and the blank cell where you want the grand total to be, then click on AutoSum, then Excel will create a SUM formula that adds up the totals and ignores other figures.

Copying Data / Formulas

The Copy command is used to copy Text, numbers, formulae, functions and their formats from one or more cells to the corresponding number of cells.  This can greatly speed up your work.

NOTE:  The cells to be copied to must be empty, otherwise the data in them will be replaced.

Copying data

Copying can be done with the Copy and Paste icons.

1? Select the cells you want to copy.

2?   Click on the Copy icon on the Standard toolbar.

3? Go to the top left of the range you want to copy to.

4?   Press Return or click on the Paste icon.

                 Copy and Paste Icons

Copying formulas

When you copy formulas like =SUM(B3.E3) to other places on your worksheet, Excel automatically adjusts them so that they add up the correct figures. This is because any cell references in formulas, they are copied. For example

	A	B	C	D
9
10	Total	=SUM(B3.B9)

If you copied the formulas in cell B10 into cells C10 and D10, then the cell references will be adjusted and the formulas will become:

=SUM(C3.C9)              For cell C10

=SUM(D3.D9)              For cell D10

Setting Column Width / Row height for the whole spreadsheet

1     Select the whole spreadsheet.

This can be done by clicking on the square located above the row number 1, and to the left of the column letter A.  The whole spreadsheet is now highlighted.

2     You can now change the column width or row height by simply moving your cursor to the edge of the column or row until it changes into a cross arrow.  Then click and drag to adjust the column or row.

3     By adjusting just one column or row, the whole spreadsheet is changed.

Exercise 5

Enter the following data into a blank worksheet.

1?   Set column width to 13.00 for the whole worksheet before you begin.

NOTE:      When you are setting the column width, look at the box above column A. While you are using the cross arrow to set the width, the width is shown in that box.

Enter Computer P/c
	JANUARY	FEBRURAY	MARCH	APRIL	TOTAL
INCOME:
Cash Sales	2250	2000	3000	2500
Credit Sales	500	1000	500	500
OVERHEADS:
Rent	450	450	450	450
Rates	325	325	325	325
L,H, & Power	350	365	300	300
Telecomm	375	295	200	220
Stationery	50	50	50	50
Wages	600	350	450	530
Total Expenditure
Profit /Loss

1.   Save the spreadsheet as EXC5

2.   Continue working on your spreadsheet.  We will now space it out a bit more as follows:

a? Insert one empty row after the heading “Enterprise Computer P/c”.

Remember to place your cursor  in the row below the on you want to insert.

3.   Insert one blank row after Income, Overheads, Wages and then one after Total Exp.

3.   Centre heading “Enterprise Computer P/c” across the columns in the first row

4.   Align the month headings so that they are centred.

5.   In cell F5, insert a formula to sum up all the figures for Cash Sales.  Use the =Sum()

function.  Copy this formula down to Credit Sales.

6.   In cell F9, use the Autosum icon to sum the totals for Rent.  Copy this formula down to all the                       other Overheads - until Wages.

7.   Use the Sum function to find the Total Expenditure for January.

8.   Copy this formula to the other months.

9.   Insert a formula in column B, to calculate Profit / Loss for January.

10.       Profit / Loss =Cash Sales + Credit Sales - Total Expenditure.

11.       ** Remember **  Use Cell addressing and not the actual figures.

12.       Save the spreadsheet again, replacing the old one.  (Use the save icon to do it automatically).

13.       Print the whole spreadsheet.

14.       Exit the spreadsheet program.

Chapter 6:  Other functions, Formatting numeric data

There are over 150 other functions that can be applied in Excel.  Some of the useful ones include:

=AVERAGE ( ‘range’ )          Average of numbers in a range of cells. (Sum of numbers in list                                                    divided by number of numbers in that list)

=COUNT ( ‘range’ )               Count of number of values in a range of cells.

=MAX ( ‘range’ )           Maximum value in a range of cells.

=MIN ( ‘range’ )                      Minimum value in a range of cells.

These functions are used in the same way as the =SUM() function.

Decimal Places

An Integer is a whole number without a fractional part. For example, 1 2 3 4 5 6 7 8 9 10.

A Real number is a number with one or more decimal places.  For example, 1.2,  2.5  8.0.

Decimal places are used to indicate fractional part. For example, ½ is written as 0.5.

In spreadsheets the number of decimal place is fixed.  The cell display (what you can see in the cell on the worksheet) shows the number of decimal places set, regardless of the number of decimal places entered (cell content).

For example, if the format is fixed to 2 decimal places,

2.19387 is displayed as 2.19  and 7.9773 is displayed as 7.98.

Rounding up and rounding errors

With 7.9773, the number is rounded up to 2 decimal places.  If the third number is 5 or more then you increase the number before it by one.

For example, when rounding up 2.8467 to 2 decimal places, the number becomes 2.85.

This technique is always used but does lead to rounding errors in the displayed numbers.

To find the rounding error, you would subtract the smaller number from the larger one.

So, the rounding error for the above example is, 2.8467 = 0.0033.

Example Exercise

1.     Write down the number of decimal places in the following figures:

	No. of decimal places
23.0
56.0978
567.084
7.12
3.0083

2. Rewrite the following numbers to 2 decimal places.  Calculate their rounding errors.

	To 2 decimal places	Rounding error
23.5694

Change the format of a range of numeric data

Numeric data is displayed in a certain format in spreadsheets, depending on how it is set.

You can change the format of the way that numbers are displayed.  For example, you can specify

the number of decimal places, whether the number is currency, percent, etc.  Any changes made to the presentation of data is called  Formatting.

1.   Select the cell (s) to format.

2.   Click on the Format menu.

Click on Cells.

3.   Select Number from the Category list on the left.

4.   Select the Format Type you want.

For example, 0.00.

5.   Click on OK.

Formatting using the icons

You can also use the Formatting toolbar icons.  Take a look at the picture of the formatting toolbar .  Notice there are icons for Currency, Percentage, Increase Decimal, and Decrease Decimal.

You can change the format of any number (s) simply by clicking on any of these.

1.   Select cell (s)

2.     Click the required format icon.

Percentages

Percentages are used in 2 ways:

1.   To calculate the percentage of a number.

For example, 70% 0f 200 is : 70/100 * 200 = 140

2.   To express a number as a percentage.

In Excel, the percentage symbol can be used in the formula.  For example:

Multiplying by 100 gives a number as a percentage.  For example, 0.784 written as a percentage would be: 0.78.4%.

Remember that  ‘Percent’ means Per Hundred. Thus, 20% is 20 per hundred or 20/100.

In spreadsheets, Excel automatically multiplies a number by 100 when you format the display to percentage.

Formatting numbers as percentage

1.   Select the cell (s).

2.   Click on the Percent icon on the Formatting toolbar.

Exercise 6

1     Set the worksheet column width to 12.00.

2     Change  the width of column  A only to 14.0

3     Enter the following data into your blank worksheet.

INCOME STATEMENT
	1st quarter	2nd Quarter	3rd Quarter	4th Quarter	YEARLY
Sales	25000	20000	35000	25000
Costs
Profit
Expenses:
General & Admin
Consultant fees
Total Expenses
Net before Tax
Income Tax
Net  Income
Average Cost in year

Right align all the column headings in row 2.

4.   Costs = 60% of Sales.  Calculate the Costs figure for quarter.  Remember to use cell    addressing for the Sales figure - the formula for Costs for the 1^st Quarter would be:                                                                                                                                                                                                                                                                                                 =B5 * 60 / 100.  Enter this formula and copy it to the other cells.

5.   Save the file as EXC6.

6.   Calculate the Profit for each quarter.  Profit = Sales - Costs.  Remember to use cell                  addressing again.

7.   Calculate the General & Admin Expenses.  (= 15%  of Sales)

8.   Calculate the Consultant fees. (=12% of Sales).

9.   Calculate the Total Expenses.

12.    Calculate Income Tax.  (=20% of Net before Tax).

13.    Calculate Net Income =(Net before tax - Income tax).

14.    Use the =SUM() function to calculate the YEARLY figures.

15.    Use the =AVERAGE() function to find the Average Cost in year in cell B21.  (This is       the average of the four cost figures)

16.    Format all the numbers to Currency with 0 decimal places.

17.    Save the spreadsheet again.

18.    Print the whole spreadsheet.

19.       Exit Excel and return to the Program Managers.

 

Chapter 7: Chart/Graphs

Chart Wizard
The Chart Wizard brings you through the process of creating a chart by displaying a series of dialog boxes.

§  Enter the data into the worksheet and highlight all the cells that will be included in the chart including headers.

§  Click the Chart Wizard button on the standard toolbar to view the first Chart Wizard dialog box.

§  Chart Type - Choose the Chart type and the Chart subtype if necessary. Click Next.
Chart Source Data - Select the data range (if different from the area

§  highlighted in step 1) and click Next.

• 
  

§  Chart Options - Enter the name of the chart and titles for the X- and Y-axes. Clicking on the tabs can change other options for the axes, grid lines, legend, data labels, and data table. Press Next to move to the next set  of options.

§  Chart Location - Click As new sheet if the chart should be placed on a new, blank worksheet or select As object in if the chart should be embedded in an existing sheet and select the worksheet from the drop-own menu.

§  Click Finish to create the chart.

Resizing the Chart

To resize the chart, click on its border and drag any of the nine black handles to change the size. Handles on the corners will resize the chart proportionally while handles along the lines will stretch the chart.

Moving the Chart

Select the border of the chart, hold down the left mouse button, and drag the chart to a new location. Elements within the chart such as the title and labels may also be moved within the chart. Click on the element to activate it, and use the mouse to drag the element to move it.

Chart Formatting Toolbar

Chart Objects List - To select an object on the chart to format, click the object on the chart or select the object from the Chart Objects List and click the Format button. A window containing the properties of that object will then appear to make formatting changes.

Chart Type - Click the arrowhead on the chart type button to select a different type of chart.

Legend Toggle - Show or hide the chart legend by clicking this toggle button.

Data Table view - Display the data table instead of the chart by clicking the Data Table toggle button.

Display Data by Column or Row - Charts the data by columns or rows according to the data sheet.

Angle Text - Select the category or value axis and click the Angle Downward or Angle Upward button to angle the the selected by +/- 45 degrees.

Copying the Chart to Microsoft Word

A finished chart can be copied into a Microsoft Word document. Select the chart and click Copy. Open the destination document in Word and click Paste.

Practice

Using Exercise 6, to plot a chart  for sales and cost figures)

 Chapter 8:  Making your spreadsheet look good

Making it bold

1.   Select the cells to make Bold

2.   Click on the Bold icon.

Making it Italic

1.  Select the cell (s) to make italic.

2.  Click on the italic icon.

Underlining

1.   Select the cell (s) to underline.

2.   Click on the Underline icon.

Changing the Font

1.   Select the cells to change.

2.   Click on the downward arrow next to the font list.

3.   Select the font from the list that drops down.

Changing the size

1.   Select the cell (s) to change.

2.   Click on the downward arrow next to the font size list.

3.   Select a font size from the list that drops down (the bigger the number, the bigger the size).

Date and Time Formats

In Excel you don’t have to use a special format.

The NOW() function can be used to ‘date stamp’ a worksheet.  Not only will it insert the date, but it will update every time you use or print the worksheet.

1.   Select the cell.

2.   Type =NOW ()

3.   Press Return.

The date and time now be placed in the cell.  To change the look of this:

1.   Click on format.

2.   Click on Number.

3.   Click on ‘Date’ (or ‘Time’) in the Category column.

4.   Click on the date format required.

5.   Click on OK.

Page numbering         

Excel 5.0 insert page numbers automatically.  You can use the Header/Footer option to change their position, size, etc.

Page Setup

The Page Setup option in the file menu can be used to change margins, paper size and orientation. This can also be used to set header and footer and also sheet options.

Use Landscape or portrait orientation

                        

Portrait                                Landscapes        

 

To change to Landscape / Portrait

1.   Click on the File menu.

2.   Click on the Page Setup

3.   Click on the Page Tab

4.   Select Landscape or Portrait.

Page Size

You can manually change the size of the page in the Page Setup dialogue box.  When you change to Landscape or Portrait, the page size automatically adjusts itself.

Scale the output on the page

1.   Click on the File menu.

2.   Click on Page Setup.

3.   Click on the Page Tab

Change the Percentage figure to increase or decrease the size.         OR

Select Fit to 1 page by 1 page tall to REDUCE the size of your output to fit a page.

NOTE:             Select the “Fit To” option will only reduce size of the output, not increase it.  To                         increase the size use the “Adjust to” option.  You can’t use both options, it’s one                          or the other

Margins

The spaces around the text on the left, right, top bottom are the margins. The margins are preset in Excel as 1” from the top, bottom, left and right hand-side of the page.  These can be changed to any other settings through the page setup option in the file menu.

To change the margins

1.   Click on the File menu.

2.   Click on Page Setup.

3.   Click on the Margins tab (if not already selected).

4.   Click  in the margins setting you want to change and either type in the new number or use the up/down arrows to change the setting.

Centre it on the page

 

Horizontally centered:                                        Vertically centered:

The lines the same distance                                      The lines are the same distance

from the left the and right margins.                            from the top and bottom margins.

To centre horizontally / vertically

1.     Click on the File menu.

2.     Click on Page Setup.

3.     Click on the Margins Tab.

4.     Select Centre Horizontally and Vertically.

Headers & Footers

This lets set headers and footers that will print on every page of your worksheet.  When you start Excel it is automatically set to print the file name at the top of every page and the page number at the bottom.

Custom headers and footers

The headers and footers box’s are accessed from the page Setup box.  To access that box:

1.   Click on the File menu.

2.   Click on Page Setup.

3.   Click on the Header / Footer tab.

4.   Click on Custom Header or Custom Footer.

5.   Type or use the icons to enter your header/footer in the desired section.

6.   Click on OK.

The icons at the top can be used.  For example, there is an icon to insert the date, one to insert page numbers, etc.  The same thing applies to footer.

If your name is not listed in the header and footers

1.   Click into User Name section put your own name in there.

2.   Click on OK.

 

Exercise 7

1.   Open the file EXC6.

2.   We will now improve on it’s layout.

Change the font of the heading “INCOME STATEMENT” to one of your own choice.  Make the font size 15.  Bold and Underline it.

3.   Insert the date and time in the top right-hand side of the page as a Header.

4.   Insert your name and page number in the bottom left-hand side of the page as a Footer.

5.   Make the heading “Net Income” bold and italic.

6.   Resave the spreadsheet using the name EXC7.

7.   Print out the worksheet setting the following features before printing:

a)   Centre the table vertically and horizontally on the page.

b)   Use Landscape orientation.

c)   Adjust the size of the spreadsheet so that it fits most of the page when in landscape mode.

8.   Change the margin setting to 2” on the left and the right.

9.   Print out a copy.

10.  Change back to the margin default settings.  Save your file.

11.   Close your file and exit Excel.

Chapter 9: Cell Addressing:  Absolute, Relative, Mixed

There are three types of cell addressing:  relative, absolute and mix

Relative cell addressing

Normally when cell reference are copied, they are adjusted relative to the cell they are being copied.  That is, the cell addresses change to reflect the cells at the location of the formula.  This is known as relative cell addressing.

For example:

	A	B	C
11	=SUM(A2.A10)

Supposing the formula in cell A11 was copies to cells B11 and C11.  Because the cell addresses in the formula are relative cell addresses, when you copy them they will adjust so that they correspond to the  location they are in.

The formula would therefore become:

=SUM(B2.B10) for cell B11 and

=SUM(C2.C10) for cell C11.

Relative Cell addressing changes when copied to another location.

Absolute cell addressing

An absolute cell address will not change when copied to another location.  It allows you to maintain cell references to a specific cell when copying.  Sometimes you will want a cell reference to stay fixed, and change when you copy it.  You would then have to use absolute cell addressing on it otherwise it would automatically change when copied.

To keep a cell reference fixed, insert a $ sign before it in the formula.

For example:  If interest rate is stored in cell B1 below, we can then use it in all the formulas.  We use absolute cell addressing so that when we copy it from one location to the next, it will not change.

NOTE:       Never use the actual number value in the formula.

When you write a formula to calculate PROFIT using the interest figure, you should use the cell address for the interest figure and not it’s actual value.  This is because if you were to then go and change this value all the formulas in the spreadsheet will adjust  themselves automatically. Whereas this would not be the case if you had used the number value in that cell.

	A	B	C
1
2	Interest rate	8%
3		INCOME	PROFIT
4
5	M Hassan	12,000	=B5* $B $1
6	N Adams	13,000	=B6* $B $1
7	M Mohammed	11,000	=B7 * $B $1
8	Y Smith	16,000	=B8 * $ B $ 1

If you entered the first formula in cell C5 and then copied down to other cells, the cell address for the income figure would adjust itself because it is relative.  However, as you can see the cell address for the interest figure would stay the same because it is absolute.

Creating a formula with absolute cell references

1.   Select the cell where the answer is going to go.

2.   Type =.

3.   Select or type in the first cell reference.

4.   Press the F4 key to put the dollars in.

5.   Type the maths bit such as + or *.

6.   Select or type in next cell reference.

7.   Press the F4 key to put the dollars in if needed.

8.   And so on until you have created your formula.

9.   Press Return.

Mixed cell addressing

A mixed cell address contains both relative and absolute cell references in which either the column or row  is preceded by a $ sign but not both.

For example, in $D6 the column is fixed but the row changes.  In D$6,  the column changes and the row is fixed.

Mixed cell addresses are useful when you need a formula that always refers to the values in a specific column, but the values of the rows must change, and vice versa.

Exercise 8

1.   Set the width of the all columns to 13.00

2.   Create the spreadsheet shownd overleaf:

3.   Make the heading “Marks for ….”  Bold and change the font to Times New Roman, size 14.

4.   Centre this heading across the columns.

5.   Right align and bold and italic each students name.

6.   Underline, bold and each of the section headings, for example: “Maximum number…”.

7.   Type in all the  students details and marks.

8.   Calculate the percentage obtained by each student for each exam.  For example, the percentage obtained by Ahmed in Maths is:

Mark obtained / Maximum mark.

Remember to use absolute cell addressing for the maximum marks.  So the formula for Ahmed / maths would be: =B13/$B$5

Format all of them to percentage when  finish entering all the formulas.

9.   Repeat this procedure for each student to find their percentage mark.  You can copy the formulas for Ahmed’s Percentage mark in maths to the other columns.  But remember you have put in another formula for each row because the exam is different.

10.       Format all the figures now to percent.  Remember to select (highlight) all of them first  before pressing the Percentage icon.

11.       In cell B25, find out Ahmed’s average percentage.  Repeat this for all the other students.

12.       Copy the column containing Grant’s percentage for each exam over to column H without changing the individual rows.

NOTE: Because the column is changing, you need to fix the column address (absolute) so that it always refers to the original column containing Grant’s percentage.  The rows will not change so use relative addressing.  Thus, you should go back and edit Grant’s percentage formulas to get mixed cell addressing.

For example, the formula in cell D20 should be to: =$D5/$B$5

When you copy formula it will not change.  Repeat this for each of Grant’s other percentages.

13.   Copy the contents of Maria’s percentages over to column I.

14.   Save the spreadsheet with the name EXC8.

15.   Insert a header containing a date code.

16.   Insert footer containing your name and centre name.

17.  Print out the spreadsheet in Landscape mode.  Scale the output on the page necessary.  Use the Print Preview option to view your worksheet to make sure it is correct and laid out properly before you print.  This avoids wasting paper.

18.   Print out only columns H and I.

19.   Exit the spreadsheet program.

	A	B	C	D	E	F
1
2
3	MARKS FOR STUDENTS IN YEAR 5: 1994-1995
4	Maximum  Marks /Exams Subject
5	Maths	20
6	English	50
7	History	10
8	Art	30
9
10	Marks obtained by each student
11
12		Ahmed	Carmen	Grant	Maria	Dave
13	Maths	20	15	12	16	14
14	English	29	23	45	22	50
15	History	8	9	5	6	4
16	Art	22	26	6	15	19
17
18	Percentage obtained
19
20	Maths
21	English
22	History
23	Art
24
25	Average %

Chapter 9: Relational Operators and Logical Data Type

Relational Operators

Relational operators are used to compare two values.  Relational operators are:

=       Equal to

·        Greater than

>=     Greater than or equal to

<       Less than

<=     Less than or equal to

<>     Not equal to

Logical data type

Logical date type is used to represent either one of two values - TRUE or FALSE.  For example:

3 = 4 is False.

3 = 4 is True.

In spreadsheets, logical data types are only used as part of a formula containing relational operators.

Example exercise

Specify whether  the following are TRUE or FALSE:

1.   6 = 7                        3.   8 <> 5

2.   10 = 10                             4.   16  > 9

Logical functions and IF statements

The most commonly used logical function is =IF function.  With it you can give your spreadsheet some “intelligence”.  You can use it to choose between two alternatives.

The =IF function takes the following format:

=IF(this condition is TRUE, put this in the cell, otherwise if FALSE put this in cell).

For example, a student will either pass or fail, depending on whether his/her result is greater or less than a preset pass mark.  An IF statement would serve this situation:

If final mark >= pass mark then PASS else FAIL

You would write it like this in Excel:

=IF(FINAL MARK>=PASSMARK,” PASS”, “FAIL”)

The PASS and FAIL options are written in quotes because they are character strings which will be typed out exactly as they are.  The must be written in quotes, otherwise Excel will try to evaluate them, and will not be able to.

When writing out the condition part of the statement (final mark>pass mark), you must use cell addresses.

The IF function works like any other formula.  You type it into the cell you want your answer to go in, and the answer is displayed.

Further logical operators

Sometimes the condition part of the =IF statement is a combination of two conditions.  In this case can use the following operators to combine them:

AND, OR, NOT

Sorting

By sorting, you can arrange rows in a list according to the contents of particular columns.  For example, you can sort by order of name, or by date, or number.

To sort your entire list, just select a single cell in the list and choose the Sort command from the Date menu.  Microsoft Excel automatically selects the whole list for you.  Microsoft Excel detects your column labels, even when they occupy double rows, and excludes them from the sort.  You can use the column labels to identify the columns you want to sort by.

Microsoft Excel uses the following guidelines when sorting data:

·        If you sort by one column, rows with duplicate in that column remain in their original order.

·        Rows with blank cells in the sort column are placed last in the sorted list.

·        Hidden rows are not moved, unless they part of an outline.

·        The sort options - selected column, order (ascending or descending), and orientation (top to bottom or left to right) - are saved from the last time you sorted until you change them or change the selection or column labels.

·        If you sort by more than one column, rows with duplicate items in the primary column are sorted according to the second column you specify.  Rows with duplicate items in the second column are sorted according to the third column you specify.

Sorting Ascending button

If you are sorting a list, this icon sorts the current list from the lowest value to the highest value, using the column that contains the active cell.  For example, A - Z or 1 - 10.  Any sorting options used in a previous sort operation are also applied.

Sort descending button

If you are sorting a list, this button sorts the current list from the highest value to the lowest value, using the column that contains the active cell.  Any sorting options used in a previous sort operation are also applied.

Sorting a list from left to right OR top to bottom

1.   Select a cell in the list you want to sort.

2.   From the Data menu, choose Sort.

3.   Choose the Options button.

4.   Under Orientation, select the  Sort Left to Right option button OR,

5.   Top to bottom option button.

6.   Choose the OK button.

7.   In the Sort By box:  Select the row you want to sort by OR,

Select the primary column you want to sort by.

Then select the Ascending or Descending option button to specify a Sort order for the values in  that row / column.

8.   To sort by more than one row / column, select the row / columns you want to sort by in one or both of the Then By boxes.  Select the Ascending or Descending option button for each.

9.   Click on the OK button.

If you don’t like the way the data is sorted, undo immediately choosing the Undo Sort command from the Edit menu.

 Exercise 9

1.   Set the width of all the columns to 13.00 and create the following spreadsheet.

2.   Centre the heading ”XYZ...” across the columns, bold and enlarge it.

3.   Right align all the column headings on row 3 and make them bold and italic.

	A	B	C	D	E	F
1.	XYZ FINANCE LTD
2.	Interest Rate =
3.
4.	Customer	Amount	Interest	Total Payable	Actual Paid	Profit / Loss
5.
6.	Smith	1000			1000
7.	Jones	2000			2280
8.	O’Connor	2500			2700
9.	Mitchell	3000			3420
10.	Broderick	3500			3500
11.	Ahmed	4000			4560
12.	Lola	4500			4860

4.   Type in the details shown above.

5.   Enter the interest in cell B2 as 14/100 and then format it to Percent and 0 decimal       places.

6.   Calculate the amount of interest paid by each borrower.

Interest = 14% of amount borrowed.

7.  Format the figures for amount, interest, total payable, and actual paid to Currency and 0 decimal places.

8.  Interest an =IF statement in cell F6 to find out whether a Profit or Loss has been made.

It would be written like this, but remember to use relative cell addressing!!.

IF actual paid >= total payable then PROFIT else LOSS

9.  Copy this formula to the other cells in column F to see whether each one is a profit or a loss.

10.  Sort the customer names in ascending order.  Save the spreadsheet as EXC9.

11.  Print the entire spreadsheet.  Centre it on the page.

12.       Exit Excel.

Scenario

A scenario is a set of values that Microsoft Excel saves and can substitute automatically in your worksheet. You can use scenarios to forecast the outcome of a worksheet model. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.

Creating scenarios   For example, if you want to create a budget but are uncertain of your revenue, you can define different values for the revenue and then switch between the scenarios to perform what-if analyses.

	A	B
1	Gross Revenue	¢50,000
2	Cost of Goods Sold	¢13,200
3	Gross Profit	¢36,800

In the example above, you could name the scenario Worst Case, set the value in cell B1 to $50,000, and set the value in cell B2 to $13,200.

	A	B
1	Gross Revenue	¢150,000
2	Cost of Goods Sold	¢26,000
3	Gross Profit	¢124,000

You could name the second scenario Best Case and change the values in B1 to $150,000 and B2 to $26,000.

Scenario summary reports   To compare several scenarios, you can create a report that summarizes them on the same page. The report can list the scenarios side by side or summarize them in a PivotTable report.

Create a scenario

1.   On the Tools menu, click Scenarios.

1.   Click Add.

2.   In the Scenario name box, type a name for the scenario.

3.   In the Changing cells box, enter the references for the cells that you want to change.

4.   Under Protection, select the options you want.

5.   Click OK.

6.   In the Scenario Values dialog box, type the values you want for the changing cells.

7.   To create the scenario, click OK.

To create additional scenarios, click Add, and then repeat steps 3-7. When you finish creating scenario, click OK, and then click Close in the Scenario Manager dialog box.

Tip   To preserve the original values for the changing cells, create a scenario that uses the original cell values before you create scenarios that change the values

More information go to  help

Create a scenario

Display a scenario

Create a scenario summary report