Microsoft Excel Introduction Exercise Booklet

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

Spreadsheets using Microsoft Excel

Description Through this course you will gain experience dealing with a spreadsheet. The use of the software, knowledge will be gained through formatting, inputting text and numerical data, creating basic formulae and functions. Another use of spreadsheets is utilising the facility of creating charts and graphs. You will also experience creating a variety of charts and graphs.

Learning Outcomes       

identify and use spreadsheet software correctly enter and edit data accurately insert, replicate and format arithmetical formulae and functions use common numerical formatting and alignment manage and spreadsheet documents produce pie charts, line graphs and column/column charts select and present single and comparative sets of data

M A SLATTER/Dec10/V4

2

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

Spreadsheet applications are designed for the manipulation of numerical information. The computer's monitor displays a sheet divided into rows and columns. Both figures and text can be inserted into the boxes on the sheet.

Spreadsheet Terminology A spreadsheet consists of a grid of vertical COLUMNS and horizontal ROWS. Where column and row intersect is a CELL. The cell is none as a REFERENCE that consists of two co-ordinates - the column letter followed by the row number (as in a street map) eg C4. Cells can contain TEXT (labels), NUMBERS (data values) or FORMULAE/FUNCTIONS. FORMULAE tell the spreadsheet to perform calculations, e.g. to use values (within the cells) for calculation purposes. FUNCTION is another way to work out calculations using words eg SUM, AVERAGE, COUNT using a group of cells. These calculations ensure that totals are automatically recalculated when the data values (within the cells) in the spreadsheet are changed. At the bottom of a worksheet you can display either the name of the sheet (in this case sheet1, or be renamed to represent another name eg Months, Years, etc. The icons at the left of the Worksheet(s) are navigation buttons and may be used to move to other sheets within the same book.

M A SLATTER/Dec10/V4

3

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

There are two Scroll columns, a vertical scroll column down the right-hand side of the worksheet for moving up and down, and a horizontal scroll column along the bottom of the worksheet for moving left and right. The Status column is at the bottom of the screen. It displays information about the current command; no command has been issued yet so it reads 'Ready'.

Ribbons MS Excel, consists of various Ribbons to enable you to work the application effectively, these are called:

MS Office 2007

Office Button

Home Insert Page Layout Formulas Data Review View

There is a "Quick Access Toolbar" this can be added to with the icons or removed depending on your requirements.

Within a Ribbon, there are various groups of "Actions" eg within the Home Ribbon, there is: Clipboard, Font, Alignment, Number, Styles, Cells and Editing. MS Office 2010/2013

File Drop Down

M A SLATTER/Dec10/V4

4

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

Outcomes        

Columns & Rows o height/width o Deleting/Inserting Worksheet Tabs names/colour/moving/copying/inserting/deleting (Ribbon/Mouse) Format Cells (alignment/font/border/patterns) o cell type number /Currency/Decimals Headers & Footers Basic Formulas / * + Formula replication Decimals/Integer Ranges explained

Exercise 1 1.

Open Exercise 1 and save as spex1

2.

Using the Insert Ribbon, insert a Header with your name in it. In another section of the Header use the Current Date Field.

3.

Rename sheet 1 to read Addition, delete sheets 2 and 3, colour the Tab, Orange.

4.

Using the Home Ribbon, embolden the main heading and the column headings, ensure you can see all the cell contents.

5.

Replicate the months down that start from January (abbreviation Jan), so that it shows the first six months until June (Jun).

6.

Right align the column headings NEWS, MAGS and TOTALS, so that they are inline with the figures .

7.

In (D4) the row below the TOTALS heading, enter the function sum, to calculate the values of NEWS and MAGS for the month of June eg =sum(B4:C4)

8.

Replicate this sum function down the remaining months.

9.

Save the spreadsheet and close the file.

M A SLATTER/Dec10/V4

5

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

Exercise 2 1.

Open Exercise 2 and save as spex2.

2.

Using the Insert Ribbon, Insert a header, with your name (in any part of the 3 sections).

3.

Using the Home Ribbon, change the Profit heading to a size 20.

4.

Align the column headings Sales, Costs and Profit to the right and make them bold, also make bold the Item heading. Ensure all the data can be seen

5.

Enter a formulae in the Profit column, in the Jazz Row (D4), to calculate the Costs figure from the Sales figure e.g. =B4-C4

6.

Replicate the formulae down the remaining column cells

7.

Rename the worksheet tab to read Sales

8.

Save and close the file.

Outcomes    

Sorting Decimals Merge and Centre Word Wrap

Exercise 3 1.

Open Exercise 3 and save as spex3

2.

Using the Insert Ribbon, enter your name in the Footer. click back into your data and locate back to cell A1

3.

Rename the worksheet tab to read Costings, delete the extra sheets 2 and 3.

4.

Using the Home Ribbon Check and correct the alignment of the column headings. Can all the data be seen? Embolden the title and the column headings. Widen column A and column B

5.

Merge and centre Video Purchase across all the column headings.

6.

Word wrap Total cost into one cell

7.

In D4, calculate the cost of the items by multiplying the QUANTITY ordered by the PRICE., and then replicate the formula down the remaining column cells. (Remember to use the

M A SLATTER/Dec10/V4

* operator for multiplication.) There may be a problem?? 6

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

8.

Change the Price and Total Cost columns data types, to a number (ensure two decimal places)

9.

Sort the spreadsheet in A to Z order of Item

10.

Save and close the file.

Exercise 4** 1. 2. 3.

4. 5. 6. 7. 8.

Open Exercise 4 and save as spex4 Using the Insert Ribbon, in the Header enter your name and today’s date. Using the Home Ribbon, can you see all the data labels and figures? If not, amend the column widths accordingly. Could some column headings be word wrapped? so it fits onto one page? Embolden the title and all column headings Set the format of all currency fields to display to 3 decimal places (no currency symbol). In the Takings column (F4), multiply No Sold by the Price Each for each of the different meals, replicate the formula down the column. Sort the spreadsheet in A to Z order of the Meals Save the spreadsheet

Outcomes   

Editing BODMAS introduction Centre and merge

Exercise 5** 1. 2.

3.

4.

5. 6.

Utilising the previous exercise (spex4). Using the Home Ribbon, or using the mouse menu (right click), insert a new column between COST and NO SOLD - Give this new column the heading title TOTAL COSTINGS (could this text be wrapped around the cell - giving it 2 lines?) Within the new column of TOTAL COSTINGS (D4) apply a formula to multiply the NO MADE with the COST, replicate this formula down the remainder of the cells for all the other meals. In the column PROFIT/LOSS (H4) enter a calculation. The calculation is the difference between the TOTAL COSTINGS and TAKINGS, eg Takings - Total Costings, and replicate the formula down the column. Only display the PROFIT/LOSS column with a currency symbol to 2 decimal places Amend to price of COST of CHICKEN MARYLAND to 2.40.

M A SLATTER/Dec10/V4

7

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

7. 8. 9.

Amend the PRICE EACH of GAMBAS to 1.75. Delete the row for LAMB DINNER. Using the Office Button use ‘Save As’, save the spreadsheet as spex5

Outcomes    

Functions (Averages/Minimum/Maximum and Count) Functions (Today/Now) Displaying formulas Sorting/Filtering

Exercise 6 1.

Open the file name spex1

2.

Using the Home Ribbon, Merge and Centre the Main Heading COSTS across the whole table

3.

Create a copy of the worksheet

4.

Sort the Addition(2) worksheet

5.

On the Addition(2) worksheet Insert a column between NEWS and MAGS.. Label the new column heading COMICS.

6.

Enter the following data under the Comics Column, against the months as shown below:

JAN FEB MAR APR MAY JUN 7.

10.25 8.75 13.00 11.66 10.95 9.45

Save the file as spex6

Exercise 7 1

Using the previous exercise spex6 - save as spex7

2

At the end of the data table, after the TOTALS column heading, add the following 3 column headings in F, G and H - AVERAGE, MINIMUM, MAXIMUM and COUNT - use the functions to calculate the new columns accordingly, work across the columns eg =average( and replicate them down the columns of data. (Do not include any calculated fields)

M A SLATTER/Dec10/V4

8

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

3

Place to 0 decimal places (eg integer format)

4

Insert today's date as a function in cell A11 eg =today()

5

Save and close the spreadsheet

Exercise 8 1

Continuing on with the same exercise as above (spex7) - save as spex8

2

At the end of the data table, after the Maximum column heading

3

Add a new column (in column J) to be named Projected

4

Put a calculation in this column that calculates the total by multiplying the total

Profit

0.03 eg =(E4*0.03)+E4 5

Put the calculation into 2 decimal places

6

Save and close the spreadsheet

Exercise 9 1.

Open Exercise 9 and save as Spex9

2.

Widen column A, so that you can see all the data within that column.

3.

Make bold all the column headings including the title.

4.

Calculate the Total Column (column E3) for all the magazines using a function eg =SUM(B3:D3) and replicate it down the remainder of the column, where there is data

5.

Calculate the Total Row (B8) to add up the column for Jan, using the SUM function =SUM(B3:B7) and replicate the formula across the remainder of the data cells

6.

In the Average column (F3), calculate the average for the months of January, February and March, using the AVERAGE function =AVERAGE(B3:D3) and replicate the formula down the remainder of the column where there is data

7.

Ensure both the Total and Average figures are in integer format (no decimal places)

8.

Save the spreadsheet

M A SLATTER/Dec10/V4

9

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

Exercise 10 1.

Using the previous spreadsheet (spex9), right align the column headings Jan, Feb, Mar, Total and Average.

2.

Next to the average column enter a new column heading in G - Count.

3.

In the Count column enter the function of Count, for the Semester Break, for the months of Jan, Feb and Mar. Replicate this formula down the remainder of the magazines.

4.

In B10 enter the function of NOW

5.

Sort the worksheet on the column heading Magazines.

6.

Save the spreadsheet and close

GRAPHS AND CHARTS Outcomes   

Pie Chart Altering styles Header & Footer

All Charts and Graph exercises are located in the spreadsheet file named: Charts and Graphs Exercise 10 – Pie Chart 1.

Open the tab sheet named Exercise 10

2.

Produce a pie chart based on the data values of the Station and Audience. -

3.

Move the location of the chart to a new sheet– give the new sheet the name pie 10.

4.

The Chart title is:

SHARE OF RADIO LISTENING, UNITED KINGDOM

M A SLATTER/Dec10/V4

10

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

5.

The title as above, should be a font size of 24

6.

The data labels/legend, should be a font size of 14

7.

Change the style colours - the pie segments (so that they are clearly different when printed -shades of grey), change the style manually eg you select the colour/pattern or shading and textures etc

Exercise 11 1

Open tab Exercise 11

2

Produce a pie chart based on the Holiday Town and the Number of Visitors

3

Move the chart to a to be a new sheet and name it pie11

4

The Chart title is: VISITOR PERCENTAGE - NORTH WEST REGIONS

5

The data labels should show label and value

6

Change the style

Outcomes 

Column Charts

Exercise 12 – Column Chart 1 Open tab exercise 12 2 Produce a single column chart based on the Monthly Sales 3 Move the chart to a new sheet and rename the chart to Column 12 4 Enter the following details in relation to the Titles Chart title Category (X) axis Value (Y) axis

M A SLATTER/Dec10/V4

: : :

SALE OF CARS, SWINDON BRANCH Months Value

11

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

5 In this instance, a legend is not required (only use a legend for comparison charts). 6 Change the style

Outcomes 

Altering the Y Axis

Exercise 13 1. Open tab exercise 13 2. Create a comparison Column chart (Column) between Population and Area 3.

Location to be floating in the spreadsheet and move the chart underneath the Spreadsheet Data Table Title - European Country Sizes and Populations X Axis - Country Y Axis - Size

4.

Change the Y Axis values from min - 0 to 20000 and max - 70000 to 60000

Exercise 14 1.

Open tab Exercise 14

2.

Create a comparison Column Chart (Column) based on the entries for 1992 and 1993

3.

Location to be floating within the spreadsheet, move the chart underneath the Spreadsheet Data Table. Title - CLAIT ENTRIES 1992-1993 X Axis - APPLICATION CODE Y Axis - ENTRIES

4. Change the Y Axis values from min - 0 to 500

M A SLATTER/Dec10/V4

12

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

Outcomes 

Line Graphs

Exercise 15 Line Graph Line charts with a difference Excel sees any numbers as figures eg Years 2011 this is recognised as the number 2,011 - Excel does not recognise these types of numbers as labels (text) Up to now you have been producing clear charts with text labels and numerical data. You can see from the line chart information in Exercise 13 the difference. 1

Open tab Exercise 15

2

Select only the data which is to be charted (not the years) eg Profit & Loss data values

3

Update the X Axis Labels (Horizontal) to read the years via the Select Data Group.

4

Move the location of the chart to another sheet and rename the tab to: line15

5

The titles are: Chart title Category (X) axis Value (Y) axis

: PROFIT AND LOSS FOR MULTILINGUAL MATTERS : Year : £000s

Exercise 16 1 Open tab exercise 16 2 Produce a Line Graph based on the Pupils Percentage 3 Update the Horizontal Axis Labels to show the Years via Select Data 4

Move the chart to a new sheet and rename the tab to: line16

5

The titles are: Chart title Category (X) axis Value (Y) axis

M A SLATTER/Dec10/V4

: PERCENTAGE OF STUDENTS PASSING EXAMS : Year : Percentage Ratio

13

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

BUILD UP EXERCISE 1 Scenario You are working as an Administrative Assistant for ‘Global Industries’. Your job is to produce sales reports for the Head of Department. Your Team Leader has asked you to produce a set of reports in graph format showing the number of sales in the different counties over the last six months. 1

Open the data file Sales, which contains data on the number of sales in the different counties over the last six months.

The first report will compare the number of sales in each county for the first month. 2

Create a pie chart to display the Jan data for each of the counties.

a)

Give the chart the heading: January Sales Data.

b)

Ensure that each sector is shaded in such a way that the data can be clearly identified when printed.

c)

Each sector of the chart must be labelled clearly with the name of the county and the number or percentage of sales.

3

Save the file using the name SPP1

CONTINUE WORKING WITHIN THE SAME WORKSHEET Your Team Leader is impressed with the increase in Lancashire’s figures and would like to see a column chart showing Lancashire’s data for the first four months. 4

Produce a column chart showing the data for Lancashire from Jan to Apr.

a)

Display the months along the horizontal x-axis.

b)

Set the y-axis to display the range 0 to 100.

c) Lancashire.

Give the column chart the heading Performance to Date –

d)

Give the horizontal x-axis the title Month.

e)

Give the vertical y-axis the title Sales.

5

Save the file using the name SPP2

M A SLATTER/Dec10/V4

14

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

CONTINUE WORKING WITHIN THE SAME WORKSHEET Your Team Leader is worried about the performance of Essex and has asked you to produce a line graph comparing Essex’s data with Lancashire’s data. 6

Produce a line graph comparing the data for Lancashire and Essex for Jan to Jun.

a)

Display the months along the horizontal x-axis.

b)

Set the y-axis to display the range 40 to 95.

c)

Give the graph the heading: Comparison of Lancashire and Essex.

d)

Give the horizontal x-axis the title Month.

e)

Give the vertical y-axis the title Sales.

f)

Use a legend to identify each line. Make sure that the lines and/or data points are distinctive, and can be identified when printed.

7

Save the file using the name SPP3

8

Close the document and exit the software securely.

M A SLATTER/Dec10/V4

15

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

BUILD UP EXERCISE 2 Scenario You are working in the office of Blackton School. One of your jobs is to produce a table of examination reports. You have been asked to produce a spreadsheet showing the results of the Christmas examination for a group of Year 10 students including the total marks gained over the 5 separate examinations. 1

Open the spreadsheet called: CHRISTMAS EXAM RESULTS.

2

Enter the following data, leaving the TOTAL MARKS column blank.

NAME

ENGLISH

FRENCH

GEOGRAPHY

MATHS

SCIENCE

ANN ROBERT CALLUM DENNIS EMMA FIONA GARRY

87 69 43 58 67 91 83

75 55 39 49 79 88 69

78 64 57 66 59 83 76

69 67 59 63 60 78 84

71 69 61 58 47 72 89

TOTAL MARKS

3

Enter your name in the header

4

The TOTAL MARKS for each student is calculated by adding all the figures over the different examinations.

Insert a formula (function) to calculate the TOTAL MARKS for ANN.

Replicate this formula to show the TOTAL MARKS for each student.

5

Save your spreadsheet report with the name SPP4. Make sure that all the data is displayed in full.

6

Insert a new column entitled AVERAGE between the columns SCIENCE and TOTAL MARKS.

7

Insert a formula (function) to calculate the AVERAGE for ANN.

Replicate this formula to show the AVERAGE for each member of the group.

M A SLATTER/Dec10/V4

16

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

The Class Tutor would like some changes made to the report. 8

CALLUM has left the group so his figures should not have been included in this report.

Delete the entire row of data for CALLUM. 9

Make the following amendments to the spreadsheet:

a)

the FRENCH mark for GARRY has increased to 80.

b)

the ENGLISH figure for ANN should be 78.

c)

the name ANN should be ANNE.

Make sure the TOTAL MARKS and AVERAGE figures have updated as a result of these changes. The tutor would like you to format the report. 10

Apply alignment as follows:

a)

The column heading NAME and all row labels (eg ANNE, ROBERT, etc) should be left-aligned.

b)

The other column headings (eg ENGLISH, FRENCH, GEOGRAPHY, etc) should be right-aligned.

c)

All numeric values should be right-aligned.

11

Format the data as follows:

a)

All examination results and TOTAL figures should be displayed in integer format (to zero decimal places).

b)

The AVERAGE figures should be displayed to 1 decimal place.

12

Save your spreadsheet using the name SPP5. Make sure all data is displayed in full.

13

Close the spreadsheet and exit the software securely.

M A SLATTER/Dec10/V4

17

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

BUILD UP EXERCISE 3 Scenario You are working as an Administrative Assistant for ‘Global Industries’. Your job is to produce video rentals reports for the Head of Department. Your Team Leader has asked you to produce a set of reports in graph format showing the number of different video rentals in six of their shops. 1.

Open the data file Rentals, which contains data on the number of video rentals in the six video shops.

The first report will compare the number of video rentals in the first shop. 2.

Create a pie chart to display the Shop A data for each of the video rentals.

a)

Give the chart the heading: Shop A Rentals.

b)

Ensure that each sector is shaded in such a way that the data can be clearly identified when printed.

c)

Each sector of the chart must be labelled clearly with the name of the shop and the number or percentage of sales.

3.

Save the file using the name SPP6

CONTINUE WORKING WITHIN THE SAME WORKSHEET Your Team Leader is impressed with the increase in Comedy Rentals and would like to see a column chart showing Comedy Rentals data for Shop A to Shop D. 4.

Produce a column chart showing the data for Comedy Rentals from Shop A to Shop D.

a)

Display the Shops along the horizontal x-axis.

b)

Set the y-axis to display the range 0 to 100.

c)

Give the column chart the heading Performance to Date – Comedy Rentals.

d)

Give the horizontal x-axis the title Shops.

e)

Give the vertical y-axis the title Rentals.

5.

Save the file using the name SPP7

M A SLATTER/Dec10/V4

18

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

CONTINUE WORKING WITHIN THE SAME WORKSHEET Your Team Leader is worried about the number of Horror movies that are rented and has asked you to produce a line graph comparing Comedy data with Horror data. 6.

Produce a line graph comparing the data for Comedy and Horror for Shop A to Shop F.

a)

Display the shops along the horizontal x-axis.

b)

Set the y-axis to display the range 70 to 95.

c)

Give the graph the heading: Comparison of Comedy and Horror.

d)

Give the horizontal x-axis the title Shops.

e)

Give the vertical y-axis the title Rentals.

f)

Use a legend to identify each line. Make sure that the lines and/or data points are distinctive, and can be identified when printed.

7.

Save the file using the name SPP8

8.

Close the document and exit the software securely.

M A SLATTER/Dec10/V4

19

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

BUILD UP EXERCISE 4 Scenario You are working in the sales office of ‘Ocean Cash Registers’. Your job is to produce routine financial reports. You have been asked to produce a spreadsheet showing the quarterly sales figures, commission paid to each representative and the overall earnings of the Sales Team. 1

Create a new spreadsheet and enter the title REPRESENTATIVES’ SALARIES.

2

Enter the following data, leaving the TOTAL column blank. NAME ANDY BROOKLYN CATHRIN DENNIS EDWARD FRANCES GEORGINA

BASIC PAY 2700 1700 2300 1590 1900 2100 1650

RATE OF COMMISSION 0.12 0.17 0.14 0.21 0.16 0.14 0.20

JAN

FEB

MAR

65 43 71 29 36 58 49

51 48 37 45 51 45 39

48 41 55 45 48 56 44

TOTAL

3

Enter your name in the footer

4

The TOTAL for each representative is calculated by adding the figures for JAN, FEB and MAR.

Insert a formula (function) to calculate the TOTAL for ANDY. Replicate this formula to show the TOTAL for each representative. 5

Save your spreadsheet report with the name SPP9 Make sure that all the data is displayed in full.

6

Insert a new column entitled PAY between the columns RATE OF COMMISSION and JAN.

7

PAY is calculated by multiplying the TOTAL figure by the COMMISSION RATE and then adding the BASIC PAY.

Insert a formula to calculate the PAY for ANDY. Replicate this formula to show the PAY for each member of the Sales Team.

M A SLATTER/Dec10/V4

20

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

Your Line Manager would like some changes made to the report. 8

CATHRIN has left the company and her figures should not have been included in this report.

Delete the entire row of data for CATHRIN. 9

Make the following amendments to the spreadsheet:

a)

the BASIC PAY for DENNIS has increased to 1900

b)

the COMMISSION RATE for BROOKLYN is now 0.19

c)

the MAR figure for FRANCES should be 64

d)

the name GEORGINA should be GEORGIA

Make sure the TOTAL and PAY figures have updated as a result of these changes. Your Line Manager would like you to format the report. 10

Apply alignment as follows:

a)

The column heading NAME and all row labels (eg ANDY, BROOKLYN, etc) should be left-aligned.

b)

The other column headings (eg BASIC PAY, PAY, JAN) should be rightaligned.

c)

All numeric values should be right-aligned.

11

Format the data as follows:

a)

The sales figures for JAN, FEB, MAR and TOTAL should be displayed in integer format (to zero decimal places).

b)

The figures for BASIC PAY and COMMISSION RATE should be displayed to 2 decimal places.

c)

The PAY data only should be displayed with a £ sign and to 2 decimal places.

12

Save your spreadsheet using the name SPP10. Make sure all data is displayed in full.

13

Close the spreadsheet and exit the software securely.

M A SLATTER/Dec10/V4

21

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

Build Up Exercise 5 Scenario You are working as an Administrative Assistant for ‘Global Industries’. Your job is to produce weather reports for the Head of Department. Your Team Leader has asked you to produce a set of reports in graph format showing the hours of sunshine for six cities in the UK. 2

Open the data file Weather, which contains data on the hours of sunshine for six cities in the UK.

The first report will compare the hours of sunshine for Blackpool. 3

Create a pie chart to display Blackpool’s data for each month.

a)

Give the chart the heading: Blackpool’s Data.

b)

Ensure that each sector is shaded in such a way that the data can be clearly identified when printed.

c)

Each sector of the chart must be labelled clearly with the name of the county and the number or percentage of sales.

4

Save the file using the name SPP11

CONTINUE WORKING WITHIN THE SAME WORKSHEET 5

Your Team Leader would like to see a column chart showing hours of sunshine the Cities of Blackpool to Cardiff in January.

6 Produce a column chart showing the data for January from Blackpool to Cardiff. a)

Display the Cities along the horizontal x-axis.

b)

Set the y-axis to display the range 0 to 4.

c)

Give the column chart the heading Hours Of Sunshine In January.

d)

Give the horizontal x-axis the title Cities.

e)

Give the vertical y-axis the title Hours.

7

Save the file using the name SPP12

M A SLATTER/Dec10/V4

22

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

CONTINUE WORKING WITHIN THE SAME WORKSHEET Your Team Leader has asked you to produce a line graph comparing the hours of sunshine in January and February for all six cities. 8

Produce a line graph comparing the data for January and February for Blackpool to Belfast.

a)

Display the cities along the horizontal x-axis.

b)

Set the y-axis to display the range 0.5 to 5.

c)

Give the graph the heading: Comparison of January and February.

d)

Give the horizontal x-axis the title Cities.

e)

Give the vertical y-axis the title Hours.

f)

Use a legend to identify each line. Make sure that the lines and/or data points are distinctive, and can be identified when printed.

9

Save the file using the name SPP13.

10

Close the document and exit the software securely.

M A SLATTER/Dec10/V4

23

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

BUILD UP EXERCISE 6 Scenario You are working as an Administrative Assistant for ‘Dive for Pleasure Ltd’. Your job is to produce routine financial reports. Your Team Leader has asked you to produce a spreadsheet report showing the quarterly web sales, discount and sales figures for the accessories of the diving equipment. 1.

Create a new spreadsheet

2.

Enter the title Accessories for Web Sales

3.

Below the title enter the following data, leaving the SALES column blank

DESCRIPTION ATX50 Sub ATX20 Sub Uwatec Dive Timer Gauge Clip Reg Bag Compass Tank Clipper

PRICE 365 78 79 1.25 19.95 38 2.99

DISCOUNT 66 10 11 0 9.95 5 0.99

JAN 25 35 65 41 25 32 5

FEB 54 24 13 32 42 24 17

MAR 24 23 15 14 26 28 14

SALES

4

Enter your name in the header.

5

The SALES for each quarter, is calculated by adding the figures for JAN, FEB and MAR.

Insert a formula to calculate the SALES for the ATX50 Sub Replicate this formula to show the SALES for each product. 6

Save your spreadsheet report with the name SPP14. Make sure that all the data is displayed in full.

7

Insert a new column entitled WEB between the columns DISCOUNT and JAN.

8

WEB is calculated by subtracting the DISCOUNT figure from the PRICE figure, then multiplying by the SALES figure

Insert a formula to calculate the WEB for the ATX50 Sub. Replicate this formula to show the WEB for each product. Your Team Leader would like some changes made to the report. M A SLATTER/Dec10/V4

24

Skills, Learning and Development Service Microsoft Excel Introduction - Exercise Booklet

9

Some products have been discontinued and should not have been included in the report.

Delete the entire row for Gauge Clip. 10

Make the following amendments to the spreadsheet

a: b: c: d:

The price for the ATX20 Sub should be 120 The FEB sales figure for the Reg Bag should be14 The Discount figure for the Tank Clipper should be 0.50 The description for the Uwatec Dive Timer should read Uwatec Depth Timer

Make sure the WEB and SALES figures have updated as a result of these changes. Your Team Leader would like you to format the report. 11

Apply alignment as follows:

a:

c:

The column heading DESCRIPTION and all row labels (eg ATX50 Sub etc) should be left aligned. The other column headings (eg PRICE, DISCOUNT, WEB etc) should be right aligned. All numeric values should be right aligned.

12

Format the data as follows:

a:

The sales figures for JAN, FEB and MAR should be displayed in integer format (to zero decimal places) The figures for PRICE, DISCOUNT and WEB should be displayed to 1 decimal places. The WEB data only should be displayed with a £ sign and to 2 decimal places.

b:

b: c:

13

Save your spreadsheet using the name SPP15. Make sure that all data is displayed in full.

14

Close the spreadsheet and exit the software securely.

M A SLATTER/Dec10/V4

25