Spreadsheet Exercises

This is one of the assignment exercises, we have to carry out as somewhat a guide or refreshing our skill for using spreadsheet software. There are nine(9) exercises altogether. We are to reproduce  exercise 1 to 8 while the last exercise; exercise 9 is carried out by our own ideas and creativity.

Click the link below to upload the exercise done by me:

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Exercise 5

Exercise 6

Exercise 7

Exercise 8

Exercise 9

Below is the exercises questions:

EXERCISE 1

Reproduce the following spread sheet using the Microsoft Excel 2007 and save it as Exercise1.

Use Auto fill to fill the series for the Months and Dates.

 

Save your work as Exercise1 and exit the program.

EXERCISE 2

Type in the following spreadsheet, and format it to look like the sample below.

1.       Type in all text and numbers shown in the spreadsheet below.

2.       Format all numbers as a currency.

3.       Center the spreadsheet heading 'Sales for the Month' across the spreadsheet.

4.       Format all text as displayed in the sample below.

5.       Create formulas to display a total for each sales rep.

6.       Create formulas to display a total for each product.

7.       Create a formula to calculate the total sales for all sales rep's for the month.

 

EXERCISE 3

Type in the following spreadsheet, and format it to look like the sample below.
1) Type in all text and numbers shown in the spreadsheet below.
2) Format all numbers as a currency.
3) Center the spreadsheet heading 'Sales and Produce Department' across the spreadsheet.
4) Format all text as displayed in the sample below.
5) Create formulas to display the average sales.
6) Create formulas to display total sales for each salesperson.

EXERCISE 4

Type in the following spreadsheet, and format it to look like the sample below.

1      Type in all text and numbers shown in the spreadsheet below.

2      Format all numbers with appropriate formats.

3      Center the spreadsheet heading 'Total Sales for the Month of November' across the spreadsheet.

4      Format all text as displayed in the sample below, including the rotated text labels.

5      Create formulas to display a total quantity for each fruit.

6      Create formulas to calculate the total sales to each fruit.

7      Apply all borders and shading (color) shown in the sample below, feel free to experiment with your own color schemes.

 

EXERCISE 5

1. Open a new workbook and develop a spread sheet shown below.

Worksheet 1        

                                                                              Worksheet 2

 

2. Linkup the two worksheet using VLOOKUP function.

3. Follow the following steps:

                                       i.      In worksheet 2, define the grade table.

                                     ii.      Name your table as ‘Grade’.

                                    iii.      Now, go to Worksheet 1, and click cell C5

                                   iv.      Then Fill in the blank, please refer to the notes on how to do so.

                                     v.      Click OK

 

4.       The Cell C5 will be filling with the grade ‘P’, Use AUTOFILL to copy the formula to the other cells.

5. Save the spread sheet as exercise5 and exit the program.

EXERCISE 6

In this exercise, you create a worksheet that uses Excel’s Mathematical and statistical functions to generate a value for use in a sweepstakes contest.

1. Create a worksheet that looks like the one shown below:

 

2. Your first step is to populate the worksheet with a list of 10 random integer values between 0 and 100. To do so:

Select: cell A5

Type: =int(rand()*100)

Press: Enter

Copy this from A5 to A14

Random value will appear in each cell.

3. Then, calculate the average value in D6.

Each person who rents a video will have an opportunity to press F9 key to recalculate the worksheet. If his ‘Average random value’ is greater than the ‘Value to beat’ then he is a winner

4.       In cell D8, use the IF functions to informs the participant on how they’ve done. The winner must achieve an average random value that is greater than the specified value to beat. If he wins, cell D8 will displays “Winner!”, otherwise displays “Try Again!”. To do so:

Select: D8

Type: =IF(D6>60, “Winner!”, “Try Again!”)

Press: Enter

5. In cell range A5 to A14, use conditional formatting fill the cells with blue background if the random value is less than or equal to 60.
6. In cell D8, also use the Conditional Formatting to fill it with yellow background if the participant wins.
7. On your own, press the F9 key to test your luck.
8. Save the spread sheet as exercise 6 and exit the program.

EXERCISE 7

1.       Type in the data below into the worksheet

Stationaries Sales for Year 2010
Items	Quantities Sold
Pencils	560
Pens	350
Rulers	188
Blue Markers	65
Scissors	40
Notepad	230

2.       Highlight the cells containing the data

3.       Click the Insert Tab > Choose 2D Bar chart

4.       The Bar chart will now appear on the worksheet, edit the chart according to what is shown below.

 

5.       Save your work as Exercise 7 and exit the program.

EXERCISE 8

1        The following survey of favourite ice cream flavours will be used for the example.  

Chocolate - 6 students

Strawberry - 5 students

Vanilla - 4 students

Rocky Road - 3 students

Peanut Butter Ripple - 2 students

Butter Pecan - 2 students

Neapolitan - 1 student

Black Cherry - 1 student

a)      Enter “Ice Cream Survey” in cell A1

b)      Enter “Flavour” in cell A2.

c)       Enter “Number” in cell B2

d)      Enter the flavour names in cells A3 to A10.

e)      Enter the corresponding number of students in cells B3 to B10

f)       Enter “Total” in cell A11.

2        Now you need to find the total number of the students.

3        Create a pie chart with the title chart “ICE-CREAM SURVEY”

4        Save your work as Exercise 8.

Exercise 9

1        Now it’s your turn to create your own chart!  Conduct a short survey of your classmates about a topic of your choice.  Some suggestions are:

a)      Favourite colour

b)      Favourite sport

c)       Favourite car

d)      Favourite song

e)      Least favourite food

2        First create a worksheet then a graph for your survey.  You do not necessarily have to do a pie chart.  Play around with some options, be creative!

3        Save your work as Exercise 9