Skip to Main Content

Getting started with Microsoft Office Excel: Week 3 - More Budgeting

Learn to work with basic spreadsheets, storing both text and numerical data.

Create a Budget (Review Last Week's Exercise)

Create a Budget (re-do last week’s exercise)

  1. Use last week’s handout and re-create the Budget document –OR– go to the course site and download and open the file Week 2 Budget Final.

Exercise: More Budget Calculations

Example on the handout shows the final result – do not enter the totals in the shaded cells! Use calculations to do it!

  1. Insert row above budget items, enter “Expenses” in cell A1, make it the row bold and shaded (highlight row/Font/bold button and the fill color button—it looks like a paint can)
  2. In Row 10 Enter label “Income”, make it bold and shaded (highlight row/Font/bold button and the fill button)
  3. Create a row for “Salary” and “Other income” respectively in cells A11 and A12.
    • Enter values for each month for both salary and other income (use example on handout)  TIP: you can copy values for one month and paste in for other months, or use the Fill function to copy along a row or column.
    • Format all cells as currency (Home/Cells/Format/Format Cells)
    • Enter calculations in row 13 (Monthly Totals and Average, as was done for Budget Items above)
    • Label the calculations row “Monthly totals” and make the row Bold (highlight row/Home/Font/Bold button)
  4. In row 15 create a row called “Income – Expenses”, make it bold and shaded (highlight row/Font/bold button and the fill tab)
  5. Resize column A to fit this long label (double-click the border between Column A and B – or manually move it)
  6. Enter calculations for Income minus expenses.  So you want to subtract the values in Row 8 from Row 13  (Home/Editing/Autosum/Sum) The function will look like this:     =SUM(B13-B8)
  7. Insert Header for the spreadsheet, use File Name (Insert/Header & Footer/Header & Footer elements/File name)
  8. Use Page Setup to change orientation to Landscape (Page Layout/Page Setup (button on bottom right)/Landscape Orientation)

Exercise: See Your Data

More fun with the budget

We now want to create charts and graphs to see a visual representation of our budget spreadsheet.

  1. Create a Pie Chart to see the amounts spent on each type if budget item. (highlight A2-B7 then  Insert/ Charts/Pie chart
  • To see the amounts, right click on the chart (the circle) and select “Add Data Labels”
  • Move the chart to below the spreadsheet (click and drag it)
  1. Create a Bar Graph to see the amounts spent on each type if budget item. (highlight A2-F7 then  Insert/ Charts/Column chart/2D)
  • Change the chart type to 2 other types of charts of your choice to familiarize yourself with the types of charts that Excel can generate. (right click and select “Change Chart type”)