Excel Basics:
Exercise Excel 2-2

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

You need to use what you just learned, and maybe learn a little more. Complete all parts of the following exercises. Don't forget to backup your Class disk when you have completed the exercises or whenever you stop for the day and saved a document along the way.

This exercise does not use any files from the resource files.
full floppy disk How to handle a full disk

Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel Basics  
Getting StartedTo subtopics      
    ArrangeTo subtopics  
    AutoFillTo subtopics
    FinishTo subtopics
    Exercises arrow to open subtopic 
    Exercise" Ex. 1 Theater tickets
    Exercise" Ex. 2 Soccer budget
    Exercise" Ex. 3 Pings
    Exercise" Ex. 4 On Your Own

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


Exercise Excel 2-2: Soccer Budget

What you will do: create a spreadsheet -
     enter data
     create formulas
     format numbers

Start with: blank Excel document a blank worksheet

The City Soccer League has created a budget for their season. You will put their figures on a spreadsheet and create formulas for totals and to show whether the league went over the budget.

  1. Data Entry: Use the handwritten notes in the illustration above to create a spreadsheet. Fill in titles starting with row 1, labels, and data in the same order. Leave a blank row above Expenses, below Insurance, one above Income, and one below Sponsorships.
  2. Sort: Sort the Expenses rows in alphabetical order. (Don't just sort the words. Sort rows.)
  3. Resize columns: Widen the columns as necessary to show words in the categories.
  4. Formula: Create a formula in cell D7 (in the first data row- Balls- in the column Over/Under Budget) to subtract the Actual amount in column C from the Budget amount in column B. If the league spent more than planned, the answer will be a negative number. Copy this formula to the other cells in this column that are on rows with categories or totals. (Hint: Copy down the whole column and then delete unneeded values.)
  5. AutoSum: Use AutoSum to calculate Totals for Expenses and Income in both the Budget and Actual columns.
  6. Numbers: Format the numbers on the sheet as Currency: range B7:D14, range B16:D16, range B19:D20, range B22:D22. Decrease Decimals twice. Note that the negative numbers now are in parentheses.
  7. Edit: To explain the new parentheses for negative values, put parentheses around the word Over in the column label: (Over)/Under. Shorten the text in cell B6 to Budget.
  8. Header: Create a header with your name and the date on the left, the file name and sheet name in the center and Exercise Excel 2-2 on the right.
  9. Prepare to Print: Use Page Setup to set the table to print centered horizontally on the page but not vertically. No gridlines or headings.. Spell Check. Print Preview.
  10. Class disk Save the workbook as soccer budget2.xls in the excel project2 folder on the Class disk.
    full floppy diskHow to handle a full disk
  11. Print Print
  12. Select A1: D16 which is just the titles and Expenses rows.
  13. Print Print just the selection.
  14. Chart: Select ranges A6:A14 and C6:C14 at the same time. Use the Chart Wizard to create a pie chart of what was spent. Use the default settings except for Data labels Show percent and place the chart on Sheet 2. Title the chart "Actual". Drag the chart to the upper left of the sheet.
  15. Chart Header: Create a header for Sheet2 just like in step h.  
  16. Prepare to Print Sheet2: Use Print Preview to check the layout. If the chart takes up the whole page, it is selected. This is an easy way to get a maximized chart on paper. But it can take a lot of ink! Go back to Normal view and unselect the chart. 

    The chart size depends on the size of the window when you inserted it on the sheet. You may want to enlarge the chart by selected it and dragging one of the handles. You may need to change the Font Size for the legend to show all of the items.

  17. Class disk Save. [soccer budget2.xls]
  18. Print Print. Once the printing is completed correctly, close the workbook.

Exercise Excel 2-2 completed