Embedded Files
Using Spreadsheets
Using Spreadsheets
Welcome to this spreadsheets tutorial.
Spreadsheets are a staple in any business. The can be used for simple computations to complex databases. These practical tutorials will help you learn some of the more useful tools to be found in in Excel and Google Sheets.
3. In the TAKINGS column use a formula to calculate the takings for SWEATERS (Total for all days x Item Price), you may need to use brackets. Replicate the formula to all other items of clothing
4. Calculate total TAKINGS, calculate TOTAL sales of all items for each day.
5. Insert a new column for SAT after the FRI column. Enter the following amounts for each of the items starting with SWEATERS: 27, 23, 19, 25, 31, 26.
6. Insert a new column for WED after the TUE column. Enter the following amounts for each of the items starting with SWEATERS: 10, 17, 19, 16, 15, 17.
7. Insert a new row TROUSERS after the row for SCARVES. The figures for each day starting with MON are: 25, 19, 14, 13, 26, 19. Item Price 7.99.
8. Check that all formulae in the table still work correctly.
9. Using a formula calculate the Average sales of Sweaters over the 6 day period. Replicate the formula for all other items of clothing. No decimal places.
10. Using a formula calculate the highest daily sale of SWEATERS over the 6 day period. Replicate for all other items of clothing. No decimal places.
11. Using a formula calculate the lowest daily sale of SWEATERS over the 6 day period. Replicate for all other items of clothing. No decimal places.
12. Format all figures in the TAKINGS, column to 2 decimal places with a £ sign.
13. Select the figures in the TAKINGS column and change the font colour to red.
14. Set the row height for the rows from SWEATERS down to TOTAL to 24px.
15. Select the cells A1 to L1 and A2 to L2 and apply a light yellow shading effect.
Task 2) Car sales data and graph
Task 2) Car sales data and graph
1. You are going to create a comparative bar chart to show the sales of cars in the UK regions in 1993 and 1994
2. On a new spreadsheet import data from “Car Sales Raw Data.txt”
3. Create a comparative bar chart using the main title CAR SALES. Give the X axis the title COUNTY and the Y axis the title CARS. Include a legend.
Task 3) IF(AND) logical formula
Task 3) IF(AND) logical formula
Open and copy the IF AND Functions worksheet into your own Google Drive:
1. Create a formula that shows “Pass” if the learner has achieved 3 or more units, and show “Fail” otherwise.
2. Create a formula that will show “Bonus” if the learner has completed 4 or more units and attended 3 or more days a week, or show “No Bonus” otherwise.
3. Create a formula that will show “Invite” to a graduation party if the learner has passed their course and is NOT a “Distance Learning” student.
Task 5) Sorting exercise
Task 5) Sorting exercise
Open and copy the League data worksheet into your own Google drive.
1. Move the title from the first row, to a Header. Delete the first two rows to tidy up the formatting.
2. Format all data to Tahoma font size 11 but italic. Ensure all data is visible.
3. Freeze panes on the first rows including the titles.
4. Total the Games Played and the Point columns on row 12 or below
5. Use a function to find the average Goals For and Against
6. Sort the teams into alphabetic order of name
7. Sort the teams into descending order of points. If the points are equal then sort on the goal difference, again in descending order. Then sort on the number of goals-for (descending).
8. Which team is bottom, which team is top?
9. Apply borders to all the of the data and a light yellow fill colour
10. Select titles row and change the text orientation to 45 degrees.
Task 6) Filtering exercise
Task 6) Filtering exercise
Open and copy the workbook called Pivot Data into your own Google drive.
1. Select the data and apply filters
2. Filter on Month 2 – how many records are there?
3. Filter the records for the Cheshire region. On top of this apply a second filter to find those in Cheshire who have enrolments greater than 30 – who and what number of enrolments? Clear filters.
4. Apply a filter to find the records of all colleges that begin with the letter B. Clear filters.
5. Apply filters to find the records of those who have above average enrolments – how many? And where the funding is above 200 – how many now? Clear filters.
6. Sort your data by Month ascending
7. Click anywhere in your data and apply subtotals giving the sum of funding for each change of Month, what are the three monthly funding totals?
8. Remove subtotals. Now sort your data by Region ascending.
9. Now apply subtotals to show the largest single amount of funding per month for each region. What region has the largest and what is the amount?
Task 7) Conditional formatting exercise
Task 7) Conditional formatting exercise
Open and copy the spreadsheet called Course Results into your own Google drive.
1. Starting in B11 work out the average mark for each learner
2. Now select all the average figures and apply conditional formatting (Colour scales – More Rules) so that the following applies:
a. 50 and below - colour red
b. 51 to 60 - colour yellow
c. 61 to 70 - colour purple
d. 71 to 80 - colour blue
e. 81 and above - colour green
3. In column J, for each subject insert column sparklines.
4. Freeze panes so that the top row containing names and the first column containing subjects remain static when scrolling.
5. The student performance target is given in cell B13. In cell B15 insert a function that would return the word Bonus if the students overall average is equal to or greater than B13 and if the English mark is greater than 60 or if not return the words No Bonus, replicate this for all students.
6. Create a 3D column chart using the learner names and overall average figures. Insert suitable chart and axis titles and delete the legend.
7. Fill the chart area with a ClipArt picture of a student formatted with 75% transparency. Ensure the Plot Area has no fill.
8. Copy the chart and paste the copy into a Word document using Paste Special and ensuring that the copy is linked to the original spreadsheet.
9. Return to the spreadsheet. Amend Colin’s Law result to 70 and Mod Languages result to 85. Check that the chart in the Word document has subsequently changed to reflect the new overall average value for Colin.
10. Close the Word document without saving.
11. Save the spreadsheet with the new file name Course Results Vs2 and insert a password of your choice to open the file and a password of your choice to modify the file. Close the file.
12. Reopen the file and ensure your passwords work. Close.
Task 8) Data validation drop down lists
Task 8) Data validation drop down lists
Open a new spreadsheet. Create two sheets; label the first sheet "Table" and the second sheet "Raw Data".
Click on the "Raw Data" sheet and create a heading in the first cell called "Categories".
In the three cells below write in three category names of your choice.
In the first sheet called "Table", create a heading in the first cell called "Category", then select the 10 cells below this heading.
Click on the "Data" menu tab, then click on "Data Validation" in the "Data Tools" section of the menu.
In the "Settings" tab, select the option from the drop down menu to Allow: List
Next click on the "Source" box, then click back on the "Raw Data" sheet and select all of the categories you have listed. This will convert the cells you have selected into drop down options.
Page updated
Google Sites
Report abuse