Microsoft Excel 2000
Introduction for new users
Excel spreadsheet layout Columns and rows Menus and toolbars Sheets and scroll bars A profit forecast worksheet Keyboard: Keys required Building the model Changing column widths Simplified data entry
Labels numbers and formulae Constructing a formula Typing method
Pointing method Mouse method
Tying "what if" questions Saving
Save and save as
Checking a file was saved Inserting rows
Deleting rows Defining ranges Formatting
Erasing cell contents Copying
Printing
Creating a graph Defining the plot area Using the Chart Wizard Exiting Excel
Throughout this guide you will find a mixture of mouse commands, text to type, explanation and hints.
A mouse command will always be prefixed by a mouse symbol e.g.
Click on New Page (Unless otherwise indicated always click with the left mouse button.)
Where text is to be typed, the text will be prefixed by a keyboard e.g.
Market Share Increasing
Hints will be prefixed by the word Hint: and appear in italicised text e.g.
Hint: Selecting Black and White from the View menu will show how your slide will print on a black and white printer
Excel spreadsheet layout
When Excel has loaded you will see something similar to below:
Columns and rows
The worksheet area is split into columns and rows. The columns are labelled with letters A-G in the example above (there are 256 columns in total labelled up to IV). The rows are numbered 1 - 12 in the example above (there are 65,536 rows in total). This allows an individual cell to be identified - use the column letter, followed by the row number e.g. A1 or AB54.
To move around the spreadsheet use the arrow keys. To move down a page quickly press PgDn (PgUp to move up).
The mouse can also be used to move around the sheet. You can click in a cell to move the cursor to a cell or you can use the mouse and the scroll bars at the side of the worksheet.
Menus and toolbars
Across the top of the screen are a number of menus. These can be selected by clicking with the mouse. If you prefer to use the keyboard, hold down the Alt key and press the underline letter in the menu option, e.g. Alt +O would give the Format menu.
Below the menus are the toolbars. These contain pictures which are shortcuts for the functions, commands and Marcos. To use them, click the mouse pointer on the button. For a button description, move the mouse over the button.
Example:
What you see on menus and toolbars
When you first start excel, the menus and toolbars display basic commands and buttons. As you work with excel, the commands and buttons that you use most often are stored as personalized settings and displayed on menus and toolbars.
Finding toolbar buttons
When several toolbars are docked on the same row, they might show only the buttons that you have used most recently. To look for a button that doesn't fit on a docked toolbar, click More Buttons at the end of the toolbar. When you use a button that is not displayed on the toolbar, excel moves that button to the toolbar, and then moves a button that has not been used recently to the More Buttons list. You can also resize a toolbar to display more buttons, or you can show all buttons on a toolbar.
Finding menu commands
To look for a menu command that doesn't appear, click the arrows at the bottom of the menu. The menu expands to show more commands. You can also expand the menu by double-clicking it. When you expand one menu, all of the menus are expanded until you choose a command or perform another action. Any command that you click on the expanded menu is added immediately to the personalized (short) version of the menu. If you stop using a command for a while, excel stops showing it on the short version of the menu. If you want, you can specify that excel always show the full set of commands for each menu.
The Ashridge software guides will give instructions using the menu options initially and will give instructions for both mouse and keyboard usage.
Sheets and scroll bars
Across the bottom of the screen are sheet labels. Excel is a 3-D worksheet and sheets are the third dimension. There are 255 sheets available.
Scroll bars along the bottom and down the side of that screen can be used to get to different parts of the worksheet quickly.
A profit forecast worksheet
The example in this handout takes you through producing a simple profit forecast and introduces you to the basic ideas of a worksheet.
Your first finished example will look like this:
Note that the upper group of three rows are simply numbers, while the lower group are formulae which can be calculated from the data above.
More Specifically the calculation rules are:
REVENUE = UNITS * PRICE
COST OF SALES = SALES UNITS * SALES COST GROSS PROFIT = REVENUE - COST OF SALES The * symbol indicates multiplication.
Keyboard: Keys required
The only essentials for building the model in this handout are:
The letter keys in the centre of the keyboard
The number keys along the top
The space bar at the bottom
The Enter key at the mid right of the keyboard
The shift keys for producing upper case letters.
The operator keys + - * ( / is used for division if required)either on the top of the keyboard or to the right hand side.
The arrow keys (left, right, up and down) for moving around the worksheet
The Esc key (escape), for escaping from at least some of your mistakes.
Building the model
The following instructions will take you through producing the Profit Forecast Model. (As the
procedure for the 1998-1999 calculations are similar to those for 1996-1997, the instructions are not repeated).
Move to cell A1 Profit Forecast Press Enter
Changing column widths
You will notice that the text runs over into the next column:
Column A needs to be made wider Click in A1.
Choose Column from the Format menu, then chose Width Type in 16 Click on OK.
Move to cell B3.
1996
If you press Enter, the cell pointer will move to the next cell (B4), so to move across to the right, Press right arrow key
Repeat for the other years:
1997 Press right 1998 Press right 1999 Press right Move to cell A5
Sales (units)
Press Enter (moves cell pointer down to A6).
Repeat for the other row headings.
Price/Unit Press Enter
Cost/Unit
Press Enter twice (leaving a blank Row).
Simplified data entry
When entering data which progresses down the sheet, it is easy to use the Enter and work
downwards, however, if you need to move across the sheet entering data both down and across, you would need to use a combination of the arrow keys.
It is possible to set up a data entry area to make input easier.
Move to cell B5.
Highlight the area from B5 to E7.
When the mouse pointer is over B5, hold down the left mouse button, keep hold and move the mouse pointer to E7 - Let go of the mouse button and the area will be highlighted.
The screen should look like below:
Don't press anything else and start typing as below:
2000 Press Enter
1.5 Press Enter
0.5 Press Enter
You will notice that the cell pointer (the white cell) has moved up to cell C5.
2104 Press Enter
Keep going and input all the data needed for this top area as seen as on page 3. Don't type in any other figures for Sales Revenue, Cost of Sales or Gross Profit yet.
Labels numbers and formulae
Up to now you have entered 'Labels' (i.e. text) or numbers - but the lower three rows of the model contain formulae i.e. there are calculations that you want the computer to work out for you. The next few instructions explain how to enter a formula.
There are two golden rules for entering formulae:
Start in the right place, and always input an equal sign (=)
Constructing a formula
The first calculation to enter is for the 1996 Sales Revenue, this is calculated by multiplying the sales units of 1996 (i.e. the contents of cell B5) with the price charged per unit in 1996 (i.e. the contents of cell B6).
Typing method
Click in cell B9
=B5*B6 and press Enter
The answer, 3000, should appear in cell B9. (Formulae always start with an '=' sign so that Excel doesn't confuse formulae with text.
Click in cell B10 (cursor should already be there).
=B5*B7 (i.e. Cost of sales = sales units *cost/unit), press Enter
Click in cell B11 (cursor should already be there) =B9-B10 and press Enter
Move to Sales Revenue for the next Year, cell C9. This time we will use another (better) method of entering formula.
The formula this time is =C5*C6, but don't type it in !
Pointing method
The method you are going to use is known as 'pointing'. You literally point to the elements in your calculation and Excel puts them together. Press the following keys carefully, noting what happens in the cell. You should see the formula being constructed as you point to the various cells.
=
Press up arrow Press Up arrow Press up arrow
Press up arrow (i.e. you should now be pointing to cell C5) The screen will look like this, with a dotted outline around cell C5
* (i.e. what you want to do to C5).
Press up arrow Press up arrow
Press up arrow (i.e. until you are pointing at C6).
The screen will look like this, with a dotted outline around cell C6
Press Enter
You should have built the correct formula in cell C9.
This pointing technique, once you are accustomed to it, is much easier than typing in the formulae, since you don't need to think about the column and row references but just about where the numbers are located on the worksheet.
Mouse method
However as Excel is a Windows spreadsheet, it would be easier to use the mouse.
Click in the cell C10 = This time use the mouse.
Click in cell C5
As in the Pointing method, the formula will start building up and there will be a dotted line around C5.
* (The next part of the calculation) Click in cell C7
Just above the column headings, is the edit line and while you are in edit mode a red cross and a green tick appear. Clicking on the cross whilst typing is the same as pressing Escape and clicking on the tick is the same as pressing Enter.
Click on the green tick.
Practice any of the three methods - typing, pointing or mouse and complete all of the model as shown on previously
Trying "what if" questions
Your model is now complete. Before you go on to the rest of the exercises try out your model by changing some of the data in rows 5,6 & 7 and noting the effect on your formula cells. The model allows you to ask such questions as:
"What is the effect on our profit if we increase price by .25 per unit?" without having to re-calculate the answer yourself.
Paper and pencil might have been quicker the first time but it depends on how often you change your data and also there are a number of short cuts that we omitted. The model can be saved on disk and used as often as you like: it can also be printed rapidly and turned into a graph.
The following section introduces you to some of the commands that can be used to manage and enhance your worksheet.
The following examples use the worksheet you have created. The commands in Excel are accessible via the menus at the top of the screen e.g.
File, Edit, View, Insert, Format, Tools, Data, Window, Help (To access the menu click on menu name with the mouse) You are going to use a selection of the most useful commands:
Saving
Inserting
Deleting
Formatting
Erasing
Copying
Printing
Saving
It is important to save your work regularly on disk, every half hour ideally, so you don't lose any work because of faults in the computer, the disk, the electricity supply or your fingers ! You will need a disk in drive A.
File names used to be restricted to 8 characters. This has changed in Windows 95. To make it easier to find your documents, you can use long, descriptive file names, up to 255 characters, but perhaps this is a little too long for day-to-day use.
File names cannot include any of the following characters:
Forward slash: (/) backslash: (\) greater-than: (>) Less-than: (<) asterisk: (*) question mark: (?) Quotation mark: (") pipe symbol: (|) Colon: (:)
Semicolon: (;)
It is wise to end a file name with a version number which you increase by one each time you save e.g.
PROFIT FORECAST 1, PROFIT FORECAST 2 etc.
Before you attempt the erasing and deleting commands it would be a good idea if you saved your file - just in case !
Click on the menu option File at the top of the screen.
Save and save as
You will notice that there are 2 menu options Save and Save As, the difference is that Save will save the worksheet under the existing name and Save As will ask for a new name, allowing you to keep different versions of the same worksheet. When saving a worksheet for the first time, both Save and Save As will ask for a file name.
From the drop down menu:
Click on Save
Click in the file name box (It may already have a file name in for you - if not type in the file name below.
Profit Forecast 1
Checking a file was saved
To check that the file was successfully saved:
Select Open from the File menu
You should see the name of your file in the list (if your file name is not listed, click on the Cancel and repeat the save procedure above).
Click on Cancel
Now that you are sure that your file was saved you can continue with the rest of the examples.
Inserting rows
Firstly you are going to insert some rows between your data and calculations.
This would be useful if you wanted to enter extra information such as fixed costs.
Click in cell A8, hold down the left button. Drag the mouse downwards until the highlighted area includes row 10 (in column A is enough - see below).
Select Rows from the Insert menu.
The data in rows 11 onwards will slide down the page leaving 3 new rows.
If you move the cursor to the gross profit cell for 1996 you will notice that Excel has adjusted the calculations for you from =B9-B10 to +B12-B13 so that it still gives the correct answer.
Deleting rows
You are now going to delete the three rows you have entered.
Highlight A9 to A11
Select Delete from the Edit menu.
Click in the Entire Row>/p>
option Click on OK.
The gap should close up and the calculations return to the original format.
Defining ranges
A range is any rectangular block of cells. It is defined by the two diagonally opposite cell references.
For instance, all the formulae in our example are contained in the range B9 to E11. Excel uses the notation B9:E11 with a colon, or if you prefer, E11:B9 or B11:E9 or E9:B11; it doesn't matter.
However it is much easier to define a range by dragging the mouse over the range as if you were throwing a fishing net, so we will use this method to change the format of the worksheet.
Hint: You have already used ranges when inserting and deleting rows although this was limited to a range in a singe column.
Formatting
Any cell or range of cells can be given one of a number of formats which affect the way the cell content looks and prints but don't change the actual contents. We will change the results of the Profit Forecast to show as monetary amounts with a £ symbol in front.
Note that the precision with which numbers are stored is not altered by the process - only the appearance changes.
Highlighting a range is achieved by moving the mouse pointer to the top-left cell of the range, holding the left mouse button and dragging the pointer to the bottom right cell.
Highlight the range B9:E11
Chose Cells from the Format menu.
Click on currency as in the example below.
Click on OK.
The figures on the worksheet will now be prefixed by a £ symbol and all be displaying 2 decimal places.
Erasing cell contents
Deleting a row or column, (as demonstrated previously) causes the surrounding rows or columns to close up and fill the gap. In order to blank out the contents of a cell or range without disturbing the rest of the sheet, the Delete key on the keyboard is used.
Highlight the range C9:E11 (i.e. leave the 1996 results) Press the delete key.
The contents of the cells have been erased. The formatting applied earlier will remain associated with the empty cells.
Hint: If you accidentally erase some data cells it is possible to undo the last action by clicking on the Undo button.
Copying
This next section will take you through one of the most useful commands of a spreadsheet package - the ability to repeat a number of calculations many times over with a couple of mouse clicks.
The formulae in columns C to E are almost the same as those in column B, so having typed them in once to column B they can be 'Copied' into the range C9:E11. There are a few different ways to do this, we will use the simplest method.
This is a two stage process:
1. Firstly you highlight the cells to be copied i.e. B9:B11 in this case.
2. You then indicate where you want to copy them to i.e. C9:C11.
Highlight the range B9:B11.
Select Copy from the Edit menu
The status bar at the bottom of the screen changes from Ready to Select destination and press Enter or choose Paste
Highlight the range C9:E11 Select Paste from the Edit menu.
The formulae for 1997-1999 should be generated.
Move to a cell containing one of the new formulae and take a look at it (in the editing line, top of the screen, just above the column C). Note that Excel has automatically changed all the D's in the formulae to E's, F's, and G's as appropriate. This automatic adjustment is very useful.
Printing
To print to the Networked Laser Printer follow the instructions below:
Select Print from the File menu.
select the printer you require.
Click on OK.
Finally, we will resave the worksheet.
Select save from the file menu.
This will resave the worksheet with the same name.
Hint: If there is a possibility that you may want to return to an earlier version of a worksheet, select Save As instead of Save and you can give your updated file a new name. This ensures that you do not risk losing your original.
Creating a graph
Creating a graph needs several steps. To produce the graph, Excel needs to know the location of the data to be plotted and what type of graph (Column, Bar etc) to plot. Other information such as titles and legends can be added to the graph but these are optional.
Excel however has a Chart Wizard, which takes you through the steps needed to create a graph.
The example we will use is the Profit Forecast and it is reproduced below with the associated graph for the Sales Revenue, Cost of Sales and Gross Profit:
Defining the plot area
As we are going to plot the Sales Revenue, Cost of Sales and Gross Profit for 1996-1999, all of this data needs to be highlighted for the Chart Wizard to create the graph.
Highlight A3:E3 Hold the Ctrl Key.
Highlight A9:E11
Let go of the Ctrl key and the 2 separate ranges should be highlighted.
It is important that the cell A3 is highlighted, even though it contains no data as Excel creates graphs using a pattern matching process - it will match 1 row by 5 columns against 3 rows by 5 columns.
Don't worry about this at the moment, just make sure the 2 ranges are highlighted as in the diagram above.
Using the Chart Wizard
Click on the Chart Wizard Button Step one of the Chat Wizard will be displayed:
Step one - chart type
This box allows you to chose the Chart Type.
Select the type indicated then click on Next.
Step two - data definition
Next is shown the data range selection box.
As you selected the data before starting, this should be correct. If so click on Next.
Step three - entering titles and labels
Next is the chart title and axes labels. Enter in the appropriate areas.
Profit Forecast; for the title.
Years for the X axes labels.
Pounds for the Y axes labels.
There are lots of other options in this section, which you can look at later, for now just click on Next.
Step four - chart location
Next is the option to insert the chart as a new sheet or as an object on the sheet.
Chose the option you want and then select Finish
Your graph will be inserted in the workbook either as a new sheet or as an object depending upon your selection.
You have now completed your graph.
Resave your worksheet
Exiting Excel
To exit Excel
Chose Exit from the File menu.
