Table Of Contents



Create and use a simple computer spreadsheet to solve a problem

Spreadsheet Theory

For your assessment you need to know all the following terms. Your answers do not need to be in these exact words, however simple one-word answers are not acceptable.

Terms

1. Cell

a) Is located by a column Letter and row Number.

b) A cell holds a discrete item of data which can be a:

i) Label

• Left aligned by default

• Combination of any keyboard characters

ii) Formula

• Must start with equals =

• Mathematical expression

BEDMAS (Brackets, Exponents, Division/Multiplication, Addition/Subtraction)

• Can include spreadsheet functions. Eg Average(range)

iii) Number

• Is right aligned by default

• Special characters only can be added eg. %, $

2. Cell Address

This is the Column letter and Row number used to locate a cell eg A1

3. Work Area

This is the area of the work sheet that is actually used.

4. Worksheet

A grid of cells comprising the whole worksheet.

5. Workbook

A workbook is a collection of separate worksheets saved to a single file. Worksheets can be added, deleted, renamed and moved within the workbook.

6. Range

An area marked by the top left and bottom right coordinates. This can be a single column or row or a number of columns or rows. eg. A3:A26, B3:E25, A3:B26 . Ranges are used in spreadsheet functions such as =Average(A3:A26)

Uses and advantages

Below are some uses and advantages of spreadsheets, however you will be able to think of many more.

Uses

1. Industry

a) Financial accounts

b) Forecasting - predicting

c) Recording and comparing

d) Costing

2. At home

a) Budgeting and finances

b) Bank account reconciliation.

3. School

a) Recording and processing marks

b) Surveys

4. Clubs

a) Sponsorships and fundraising

b) Recording and analysing results. eg Wins, scores, averages etc.

Advantages over manual methods

A manual method could be using a pen sheet of paper and perhaps a calculator.

1. The sheet can be altered

a) Errors are easily corrected

b) Data can be presented in many different ways.

2. Charts can be produced from data

3. Data can be transferred to other applications.

4. Transposing errors are reduced, as data needs only to be keyed in once.

5. Presentation is improved. Trends can be highlighted.

6. “What if” predictions can be made by changing values such as interest rate, profit etc.

7. Checks can be made to make sure data is valid.

Spreadsheet Application Software

1. Microsoft Excel

2. Lotus 123

3. Corel Quattro Pro

Spreadsheet Workbook

A spreadsheet Workbook can consist of several sheets. When you first open a new Workbook you will find that it has sheet tabs named Sheet1, Sheet2, and Sheet3.

By clicking with your mouse pointer over one of these tabs you display another Worksheet. See the diagram below:

Renaming Sheets

Sheet1, Sheet2 and Sheet3 are the default names. These can be named by double clicking the sheet tab to blacken it, then typing in a new name. See the diagram below:

Inserting a new Work Sheet

Right click with the mouse pointer over one of the sheet tabs, then click Insert…, click Work Sheet, and then click OK. See the diagram below:

Deleting a Work sheet

Right click the tab of the Work Sheet you want to delete and click Delete

Changing the order of the Work Sheets

Click on the tab of the Work Sheet you want to move and drag it horizontally to the position you wish to place it.

Spreadsheet – Overview

A spreadsheet is similar to a giant table with the borders extending practically to infinity. The spreadsheet that appears on your screen will be similar to that below.

Moving around

1. Open up a new spreadsheet and click your cursor in the cell B2, as seen in the following picture.

2. Note that the cell reference, (B2) is seen in the Left-hand corner.

3. Type 1 into the cell and press the Enter key

You can move around the spreadsheet by either clicking on the cell with your mouse cursor, or, by using the arrow keys on the keyboard.

In the cells shown type in the information below

Note that the text is on the left-hand side of the cell and the numbers are on the right hand side of the cell.

The Drag handle

Dragging to copy cells

In the bottom right hand corner of a highlighted cell is a small square. By clicking and holding down the left mouse button you can drag the contents of the cell across other cells and copy them into the new cells.

(

Exercise 3A - Dragging

1. First click on the cell B2 where your 1 is, to place the box around the cell.

2. Click and hold your Left mouse button down on the drag handle in the bottom right of the cell and drag across to H2, 1’s will be copied across the page.

3. Highlight the new cells that have been added (C2 to H2) and press the Delete key on the keyboard to delete them out again. We should be back to the 1,2,3 again from the first lesson.

Dragging

To do this the computer needs to know by how much you want the numbers to increase. For example do you want them to go: 1,2,3, or 1,3,5 or 1,5,9, (increasing by 4).

A Quick Method – Right Drag

This method involves right dragging the handle across or down the work sheet.

Quick Method – Enter the first two values in the series

Not only are numbers copied across and down cells but some words can be copied as well.

Type the words below and then pull them down the sheet using the drag handle.

Try also using Qtr 1, and 1st Group

Personal Expenses

1. Open a new spreadsheet.

2. Rename the Sheet1 tab to Personal Expenses

3. Type the information below into the cell references shown.

4. For the words under Month type January into A4 and drag down to fill down.

5. For the numbers under Income type 2120 into B4 and drag down to copy.

6. For the numbers under Expenditure type 1900 into C4 and drag down to copy. Then with the cells still marked go: Edit and Fill and Series, and set the Step Value: to 5.

7. Mark the cells A3 to D3. Click on the Bold, and Centre buttons.

8. Click on A10, and then click the Bold and Right Buttons

9. Mark the cells A4 to A9 and click the Right Button

10. Read the section below to set the cell widths

11. Then save the spreadsheet by going File, and Save as.

In the File Name field type “your name” Personal Expenses. Eg: John Personal Expenses. We will use this exercise in later lessons.

Changing Cell widths

You will have noticed that some cells in the exercise above are wider than other cells. The width of the cells can be set in a number of ways.

Changing cell widths by dragging

Exercise 5 – Cell Widths

1. Move your cursor between A and B on the Column row as shown left.

2. Click and hold down your left mouse button and drag the cell A out to make more room in the cell.

3. What happens if you move the cell boundary in too far and the words cannot fit into the cell? (Try and then move the cells back again).

4. What happens when you move the cells with numbers in too close?

Changing cell widths automatically

The cell widths can be set automatically by double clicking on the lines between the cells, in the same place as above. The columns then will increase or decrease to fit the biggest entry.

Formulas

Formulas perform calculations on spreadsheets. Formulas range in complexity from the easy ones below to extremely complicated formulas.

Use the following buttons to for mathematical operators in the spreadsheet:

+ to add

- to subtract

/ to divide

* to multiply (to make this hold down the Shift key and press the 8 or * on the numeric key pad)

% to make a percentage (Shift and 5)

= to make equals

Formulas with Numbers

Exercise 6A – Formulas

1. In a new spreadsheet type in the formulas on the left into the formula bar remembering to always include an = sign before the formula.

2. Note how the differences in the answers depend on the order of the symbols

Formulas with Cell references

Up until now we have used numbers in our formulas.

Now however we will practice using cell references instead.

Exercise 6B – Cell references

Type in the numbers on the right (they are in row 2)

(

1. Click on cell F2 to give it the focus.

2. Click into the formula bar and type in the formula right. (You don’t need to have capital letters in the cell references)

3. Click on the Tick, or press Enter. This will place the answer into cell F2.

4. Using the above steps type the formula =C2+D2/E2 into the cell F3

5. Using the above steps type the formula =C2+(D2/E2) into the cell F4

Inserting Cell References Automatically

Instead of typing in C2 each time you want it in the formula try the following:

(

Exercise 7 – Auto Cell references

1. Click in cell F5

2. In the formula bar type =

3. Now point to cell C2 on the spreadsheet and click it once, notice that the cell reference C2 will appear in the formula bar.

4. Click back in the formula bar and type +

5. Point to, and click on cell D2

6. Click back on the formula bar and type *

7. Point to, and click on the cell E2. You should have the formula

=C2+D2*E2

8. Click on the Tick to finish the formula,

Exercise 8 – Personal Expenses

(

1. Open the Spreadsheet “your name” Personal Expenses

2. Click in the cell D4, under the title Savings.

3. Type the following formula into the Formula bar: =B4-C4

4. Click the tick to see the answer in the cell.

5. Click on the Drag handle in the bottom right of the D4 cell and drag the formula down to D9.

You should now have a table similar to the following.

Functions

A function is a pre–set formula used by the spreadsheet to perform calculations. The most common function used is the SUM function.

The Sum Function

In earlier lessons we learned that we could add cells by writing the formula =C2+D2+E2 adding up the cells C2 to E2 or (C1:E2).

However the computer can accomplish this automatically. By typing = to start the formula then SUM and type in the range of the cells you want added together inside of brackets. The Sum function will add this selection automatically. So the formula we write will look like this: =Sum(C2:E2) The Colon : means To in the formula giving the range from the beginning cell to the end cell.

(

Exercise 9 – Functions

Open the Personal Expenses exercise

1. Click on the cell B10

2. In the formula bar type the following formula

3. Click the Tick to see the answer

4. All the cells from B4 to B9 will be automatically added together. Note that B4:B9 is the Range of the cells, from the top left to the bottom right.

\

The AutoSum button

Instead of typing in the formula it can be automatically generated by using the AutoSum button from the toolbar.

Exercise 10 – Formulas

1. Click in cell C10

2. Click on the AutoSum button on the toolbar

3. Note the shading covers the cells that will be added together, and that the formula appears in the cell box C10 as well as on the formula bar

4. Click on the Tick (on the formula bar and the cells will be automatically added up.

5. Repeat the above to add up the Savings column D10.

(

Personal Expenses 2 Exercise

1. In a new spreadsheet type in the following information below, including June, using the dragging to copy skills we have covered earlier to make the table quickly. Read the notes below on creating each area first.

2. To make the Total Finance fields, use the AutoSum in B10 first. Click on B10 then click on AutoSum, the formula will be inserted automatically. Then click on the drag button and drag across to copy the cell to G10.

3. Click on each cell with the formula and note in the formula bar that the formulas change to match the columns they are in. These are called Relative formulas, because the change relative to the cells they are in.

4. Use Autosum for the Total Column by clicking in G6 and clicking the AutoSum button to add the Credit card Row.

5. Drag the answer down to G10 to copy the formula

6. Click on each cell with the Total formula and note in the formula bar that the formulas have changed to match the rows they are in

7. Don’t forget to create May, as shown on the spreadsheet.

8. Save the exercise with your name Personal Expenses 2

Accessing other built-in Functions

There are a number of methods that can be used to insert functions into your spreadsheet. In this section we will examine some of the more common methods whilst practicing the new functions.

To select a function click on the Function button on the toolbar[pic]to open the Function window, and then click on the function you want to use.

The functions we will investigate should all be found under Most Recently Used, however there are many more specialist functions available to use in the other sections.

Average Function

(

Imagine that you want to know the average amount of money you pay each month for your total finance expenses.

The Average function can answer your question for you.

Exercise 12 – Average function

1. Using the exercise Personal Expenses 2, in Cell A12 type the heading Average Expenses per month

2. Click In Cell A13 and click on the Function button to open the Function window, and click on Average and OK.

3. The next screen asks for the range of the cells that you want to average. Highlight the Total Finance row, without including the total in F11.

4. Click OK and note that the formula will be entered into A13 as you have already given this cell the focus.

5. Click Finish. Note that the decimal points are not set. Format the answer as Currency by clicking the $ Button on the tool bar

Max and Min Function

(

Imagine that you also want to know the Maximum and Minimum amounts of money you spend on expenses each month for your total finance expenses

Exercise 13 – Max function

1. Using the exercise Personal Expenses 2, in Cell A14 type the Heading Maximum Expenses per month

2. Click In Cell A15 and using the Function window choose Max and click OK.

To find the minimum amount spent each month

Exercise 14 – Min function

1. In Cell A16 type the Heading Minimum Expenses per month

(

2. Click In Cell A17 and using the Function window choose Min and click OK.

If/Then Function.

Using this function you can set a one cell to show different values, such as words or numbers, if another cell is true or false. The function uses three parts, If something happens, Then do something, Otherwise do something else.

For example if the Total finance spent for six months is greater than 5351, you can make a warning appear that reads Warning! Over Budget otherwise it would say Under Budget.

This would be written as:

IF(G10>5351, “Warning! Over Budget”, “OK, Under Budget”)

In English, the above function says if G10 is greater than 5351, then put Warning! Over Budget, otherwise put OK, Under Budget. This function can be great fun and is worth learning to use. So gather your courage and do the following:

Exercise 15 – IF function

1. Using the exercise Personal Expenses 2, click in A18 and using the Function window choose IF and click OK

2. Type in the 3 fields as shown below, remembering to include the “ ” marks around the words.

3. Click OK

Note that quote marks are only needed with text, if you replace the words with numbers, there is no need to use the quote marks.

Change some cells to see if it works. Try the If statement using other words, numbers and fields.

Note: the G10>5351 will in fact show FALSE not TRUE on your sheet, the pic is mistaken

The Count Functions

These simple functions count the number of cells highlighted.

Using the Function Window, find and use the Count function to count how many number cells (cells containing numbers) are used in the Personal Expenses spreadsheet.

Count Blank, and Count If, are two other functions that are also very useful. Count Blank, will count the empty cells, and Count If will count cells that match a set criteria.

Groceries Exercise

Using the formulas we have covered above create and complete the Groceries Bill work sheet shown on the left.

The formulas you will use are:

Sum, Max, Min, and Average and CountA

Save with “your name” and Groceries eg

"Fred Groceries"

Note:

Unit cost and Number of items purchased refers to column C. Total cost refers to column D.

Your formulae are to be entered in the shaded cells.

International Academy Exercise

Using the formulas we have covered above create and complete the International Academy spreadsheet shown below.

The formulas we will use are:

Sum, Max, Min, and Average, Count, CountIf,

1. Enter formulae into the cells F3, G3, H3, I3, J3 then highlight and fill down using the drag handle

2. Enter the formulae into cells B18, B19, B20, B21, B22 then highlight and fill across using the drag handle.

3. Save with “your name” and Academy.

Note: The numbers in the TOTAL column are incorrect!

They should be the total of the first 4 tests only.

Moving Data on the Spreadsheet

One of the necessary tools in using a spreadsheet is being able to move data around your spreadsheet. The most obvious method is to delete out the data you don’t want in one place and type the new data into the place you want it. However that method is long and tedious.

Here are some more simpler and easier techniques for moving data

(

Cut and copy

Exercise 17 – Cut and copy

1. If you don’t already have it open, then open Academy

2. Mark the Name column including all the names, from A2 to A16.

3. Click on the Cut button

4. Move to J2 and click in the cell, to give it the focus.

5. Click the Paste button to paste the selection

Click and Drag.

Exercise 18A – Dragging

1. Mark the Name column from J2 to J16 again.

2. Move your cursor to the edge, or boundary of the highlighted area.

3. Click and hold down the left mouse button and drag the cells back into their original place.

Dragging Exercise

On a new spreadsheet, create the table below anywhere. Using Cut and Paste, and Drag, rearrange all the years in consecutive order from 1995 to 1999.

Exercise 18B – Dragging

Inserting Rows and Columns.

As well as being able to move cells around the spreadsheet it can be important to learn how to add extra cells into an existing table or spreadsheet.

For this exercise we are going to create a place to move the May column into the Personal Expenses 2 table.

Inserting a new Column

1. Open the Personal Expenses 2 exercise. Click in the Column F marker to highlight the entire F column.

2. Right click your mouse button and choose Insert.

3. A new column will be inserted, moving the Total column over one .

4. Highlight and drag the May Column into the space created in the new F column.

5. Note that the formulas for the Total column have automatically added in the new information.

Inserting a new Row

1. Open the Personal Expenses 2 exercise.

2. Insert a row by clicking on the Row 9 marker

3. Right click your mouse button and choose Insert.

4. Add in the following information and complete the table.

5. Hire Purchase with payments of $25 every month.

Deleting a row or Column.

1. Click the marker for the Row or column to highlight the entire row or column

2. Right click your mouse button and choose Delete.

Absolute and Relative Cell references

In some situations you will not want the cell reference to change when copying formulas across cells.

Below is an example where multiplying the Gross wages by the Tax, gives the Tax Paid. Here if you drag the formula from C7 to H7 the equation will not operate, as the tax cell B4 will also change.

To prevent the formula changing the cell B4 must be ‘locked’ into place – to prevent it changing when the formula is dragged.

1. Type in the Spreadsheet below onto a new page and save it as ‘Your name’ Personal Income

2. Click on C7 and in the formula bar type the following: =C6*B4

3. Click the Tick and drag the C7 cell to H7 to copy the formula across.

Note that the formula operates only in C7 but not in any other cell. In the other cells the B4 in the formula has changed to C4, or D4, or E4 etc all of which are empty cells.

These are Relative Cell references, as they change relative to the cell.

To lock the cell reference B4 into the formula type a $ before the cell reference, so in the C7 formula we will replace B4 with $B$4.

This creates an Absolute Cell Reference, a cell reference that will not change.

1. In C7 type in the $ before the B and 4 and drag over the old formula to copy to H7.

2. Use Auto Sum to total the Tax Paid range.

Personal Income Exercise

Our client makes candles as extra part time income, and as well as tax he pays GST on the candles.

Using the Personal Income spreadsheet above modify the spreadsheet to add the following below.

You will need to write a simple formula to calculate the Candles Net Income. This formula will subtract the Tax and GST from the Gross wages. How will you do it? Another formula is needed to achieve the total Net income in row 15.

Use Auto Sum for the Total Column.

Some cells in the I column in particular may change to look like #####. This means that the numbers are too big for the cell, and they cannot be viewed in the cell.

To fix this click and hold your left mouse button down on the line between the Column letters and drag the line to the right to widen the cell, or double click on the cell border line.

Berry Pickers Exercise

1. Create the spreadsheet below. Both are on the same work sheet.

2. Use an appropriate formula to calculate the hours worked by each employee in one week and the hours worked by all workers in one day.

3. Using an Absolute cell reference create a formula in cell B20 (Fred) that multiplies the Hourly pay rate by the Hours he worked on Monday (B6).

4. Copy the formula right across the page to G20.

5. Copy the range from B20 to G20 down the page to fill the spreadsheet.

6. Use a formula to calculate the Pay received by each person.

7. Check your answer is the same as below and save as “Your Name” Berry Pickers

8. Suppose the hourly rate changes from $8.50 to $8.54, now how much will now be paid out in one week? (Cell H30)

Formatting Cells

There are many presentation techniques that can be used on your spreadsheet to make it easy on the eye.

Some of these are the following, be sure to experiment with the techniques used and investigate any others we have not covered in this section.

Type in the following spreadsheet outlining our clients car expenses and save as “Your Name” Car Expenses.

Centring across Columns

Instead of having the heading Auto Expenses on the left of the Spreadsheet it can be centred across the spreadsheet..

Exercise 26 – Centering

(

1. Mark the cells B4 to H4, so that they are highlighted.

2. Click on the Merge and Centre button. .

3. Click the Bold button to make bold.

4. Note that although the heading is in the middle of the selection, the words are still in the cell B4, not in any other cell in the heading line. Click on C4 and D4, to see what I mean.

Word colour and Cell colours.

It is easy to change the colours of the words or the cells.

Exercise 27 –Colours

1. First highlight the cell you want to change.

2. Then either click on the colours shown on the buttons, to put that colour into the cell, or choose another colour by clicking on the small down arrow[pic] and choosing a new colour.

Vertical Adjustment within rows

We can also stretch out rows and set the text within the row to the Top, Centre or bottom, of the Row.

1. Move your mouse arrow over the line under row 4, as shown left, and click and drag down to extend the height of the row.

2. Click in row marker 4 to highlight the row, and click your Right mouse button. From the menu click on Format cells.

3. Click on the Alignment Tab and then under the Vertical section choose Centre.

4. Click again on the Alignment tab, and then click Centre from the selection under Vertical and then OK.

Note that the heading is now centred within the row, and centred across the selection of the columns.

Using the Font window in the same Format cells screen, make the font size of the heading 14, and the font Times New Roman.

Wrapping text within a cell.

Words that go over the edge of the cell border will still be seen in the table if the next cell is empty, as in the Unexpected Expenses heading in our example above. But if there is something in the next cell the information will seem to disappear. We can however make the words ‘wrap’ within the cell, that is start another line to fit into the cell boundaries. It’s very useful to know, and here’s how we do it.

1. Click on C5 and in the formula bar add the words Vic and click the tick. Note that Vic is not shown on the Spreadsheet.

2. Open the Format Cells Window and again click on the Alignment tab. Click on Wrap Text, and click OK.

3. Using Wrap Text, wrap the words Unexpected Expenses so they are in the same cell.

4. Use the Centre button (left) from the toolbar to centre the text within the cell.

Number Formats

The numbers themselves can be formatted as either, general, currency, percentage etc, using the same Format Cells window.

1. Mark the cells C6 to G6 containing the numbers. Open the Format Cells window, click on the Number tab and then click Currency.

2. Click in the Negative numbers in Red field to show deficits in red. Note that you can set the decimal places if wished (however they are set correctly for currency already)

3. Investigate the other formats available, Currency, Percentage and General are the most common ones, and we will use others later in the manual.

Deleting Rows and Columns

To delete an entire row or column click on the Row number or the Column letter to highlight the entire row or column. Then with your mouse arrow in the shaded area, click your right mouse button to show the menu and click Delete.

Using the Car Expenses exercise delete out Column A by the following:

Exercise 31 – Deleting rows and columns

1. Click on the A Column Letter.

2. Put your arrow into the shaded area and click your right mouse button.

3. Choose Delete from the menu.

Car Expenses Exercise

Combining all we have learnt so far create the spreadsheet below using the Car expenses spreadsheet as the basis for your new sheet. This spreadsheet breaks down the yearly expenses into monthly and weekly totals so our client can set aside a budget for the car. All the new fields can be dragged to save time.

In B7 create a formula to divide the Registration by the 12 months of the year.

In B8 divide B6 by 52 weeks. Drag both across to the G column.

□ Set the cells black and the letters white where shown.

□ Mark the entire table and use the Border options in the Format Cells window to set the double line around the table.

Sorting

Sorting data on a sheet is very easy to do. Click on the first cell of the column you want sorted and click on the Sort Ascending, or Sort Descending, buttons on the toolbar.

Multiple columns can be sorted at once. Highlight the columns you want to sort and then go: Data, from the Menu, and click on Sort. Choose the fields you want sorted.

Garage Sale Exercise

1. Create the Garage Sale spreadsheet

2. Widen the column to best fit the items

3. Format the numbers to Currency using Format Cells.

4. Using formulas do the following:

5. Calculate the total

6. Calculate the cheapest

7. Calculate the most expensive

8. Calculate the number of items.

9. Click on cell A2 containing Ladder. Click on the Sort Ascending button [pic] to sort.

10. Sort the money in ascending order.

11. Save as “Your Name” Garage sale

Interrelated Sheets

In the first lesson you learnt about Sheets. In this exercise we are going to use the sheets to create a multi-sheet expenses book.

Exercise 34 – Interrelated sheets

1. We need a total of 4 worksheets in our workbook, so to add another sheet to the Workbook

2. Rename Sheet1, Vehicle Expenses, Sheet2 to Van 1, Sheet 3 to Van 2

3. Insert and additional sheet and name it Car 1.

4. Reorder the sheets so Vehicle Expenses is first by Clicking on the Vehicle Expenses tab and holding down the Left Mouse button. Drag the Vehicle Expenses tab to the left to move the tab.

5. On Car 1 sheet, create the spreadsheet below. Use a formula to calculate Total, and Cost per km.

6. Copy the data to Van 1 and Van 2 sheets and change the numbers as shown.

7. On the Vehicle Expenses sheet below, create the spreadsheet shown.

8. Click in the cell B9 to make the formula. Type = in the formula bar then click on Car 1 sheet and click on D4 and click the tick to complete the formula. The formula will look like the one below and the cell will have 100,000 in it.

9. Complete the fields copying from the Van and Car sheets back to the main sheet.

10. Complete the Total fields with formulas.

11. Using this system individual records can be maintained for each vehicle. Change some of the numbers for the vehicles on their individual sheets and note the changes to the main sheet.

Charting

One of the most important and enjoyable tools in Excel is the charting tool. There are a myriad variety of charts available, we will practice making a simple chart then use more advanced methods later.

Exercise 35 – Charting

1. Open the File Charting in:

2. Spread Sheets ( Exercises Folder in T: Drive.The table below will be found under Exercise 1 (If you don’t have access to the table type it in.)

3. Highlight the Arrivals and Departures columns (B3 toC14) including both headings

4. [pic] Click on the Chart button then click on Line, and click on Next.

5. When you reach Step Two, click on the Series Tab.

6. Then click your cursor in the Category (X) axis labels window as at left.

7. Go back to your table and then highlight the Years column, from A4 to A14, without the Year heading.

8. Click Next for Step 3. Complete the Chart Title, Category, and Value fields as shown at the left.

9. In Step 4 have the chart on a new sheet.

10. Double click in the middle of your new chart to open the Format Plot Area Window. Click on None for area, to remove the background color.

11. [pic] Click on the Drawing button to open the drawing toolbar.

12. [pic]Use a text box to make a place in the bottom right of your chart with your name in it.

Charting exercise Completed ( Signed ____________________ (Student)

George’s Chocolate Sales Exercise

Exercise 36 – Georges chocolate sales

Create a spread sheet using the chocolate sales figures in the table below.

Refer to earlier sections of this manual if you are unsure what to do.

1. Wrap text in the following cells – A2, H2, A9.

2. Format so that Vertical Adjustment within rows is set to top – for cells B2 to G2

3. Format all money values to currency format. (Number Formats section in Manual)

4. Calculate the monthly total by entering a formula into B9 to sum column B. Drag this formula to G9.

5. The commission paid to George is 15% of the Monthly total. Using Absolute and Relative Cell references create a formula to show George’s Income each month in cells B13 to H13.

6. George’s Average daily Gross Income is the amount of income he receives divided by the number of days in each month. The days in each month are shown below.

|January |February |March |April |May |June |

|31 |28 |31 |30 |31 |30 |

7. In the table below write what Georges average daily income would be each month if his commission was raised from 15% to 15.5%.

|January |February |March |April |May |June |

| | | | | | |

-----------------------

Microsoft Excel 2000

[pic]

Student Workbook

Unit 2784

Version 4

Level 2

Credit 3

Cell (A1)

[pic]

sheet tabs

Double click to blacken

Type the new name

[pic]

Words will appear on the Left-hand side of the cell

Numbers will appear on the Right hand side of the cell

This is a Highlighted cell

This is the Drag Handle

(

Hold Left button down to copy

Right click on the handle (right corner of active cell)

Drag down or across the work sheet and release the mouse button

Click Fill Series

The series will increase by 1

Type in the first two numbers in the series

Highlight both numbers and click on the handle at the bottom right of the highlighted cells.

Drag the handle downwards and release.

Click here and drag down

Double click here

The order of operations is:

BEDMAS

1. Brackets ( )

2. Exponents ^

3. Multiplication and Division * /

4. Addition and Subtraction + -

5.

| |B |

|1 |=(3+4)/5 |

|2 |=3+4/5 |

|3 |=3+(4/5) |

|4 |=(3+4)*5 |

|5 |=3+4*5 |

|6 |=3+(4*5) |

|7 |=(3-4)/5 |

|8 |=3-4/5 |

Type in here

The AutoSum Button

Click here to adjust cell width

Alter text size to 14pts Bold

If G10 is greater than 5351

Then show ”Warning! Over Budget”

Otherwise show “OK, Under Budget”

(

Click here

Use AutoSum

Type 25% using the keyboard % (shift 5)

Use 14pt and bold

[pic]

Van 2

Van 1

Car 1

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download