Skip to Main Content

Getting started with Microsoft Office Excel: Week 2 - Create a Budget

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

Review

Work with a basic spreadsheet (REVIEW of last week)

  1. From Week 1 download the document “Week 1 Names draft” and open in Excel
  2. Save As
    • Location: Desktop
    • Filename:  Names
  3. Left Align the column headers (Home/Alignment/Left align button)
  4. Format cells in Zip column to be “Text” so the leading zero can be stored (Home/Cells/Format/Format Cells)
  5. Add a row to the spreadsheet, add your name and address
  6. Sort columns by Last Name (Click in that column and Home/Editing/Sort&Filter/AtoZ)
  7. Insert a column after the first name and before the city called “Street Address” (Home/Cells/Insert)
    • Enter an address for the rows by using Auto-fill the same value for all rows (Home/Fill/Down), then change the street # to make them different

Question:  What cells store the Zip codes for all the people in your spreadsheet?   ___________

Exercise - Create a Budget

Create a Budget

You want to create a spreadsheet to track your monthly expenses. This will help you calculate how much you spend per month as well as totals and averages for the year.

  1. Open Excel and select a new Blank workbook
  2. Save As
    • Location: Desktop
    • Filename:  Budget
  3. Create 6 column headers: Budget Item, October, November, December, Total, Average
    • Make the header row Bold (Home/Font/Bold button)
  4. Add 4 row labels (in cells A2- A6) for Budget items: Rent, Phone, Heat, Cable, Food
  5. Insert 2 columns for January and February (Home/Cells/Insert/Insert Sheet Column. Repeat). Create header row labels.
  6. Enter amounts for each budget item in the January column (enter amounts on your example).  Do NOT enter the formatting for decimals and the dollar sign; we will do that in the next step.
  7. Format cells in all monthly columns to be “Currency” (Home/Cells/Format/Format Cells/Numbers tab/select currency)
  8. Copy the values from January and paste in for February (Home/Clipboard/Copy)
    • Change the values for February (change values to match your example)
  9. Enter calculations in the Totals column, tallying each row (Home/Editing/Autosum/Sum)
  10. Enter calculations in the Averages column, tallying each row (Home/Editing/Autosum/Average)
  11. Resize your columns.  You can manually re-size or use auto fit columns (All cells button/double click between first 2 columns)
  12. Enter calculations for totals for the January monthly column (Home/Editing/Autosum/Sum)
    • Label row “Monthly totals” and make the row Bold
    • Copy and paste the formula for the other month’s totals (Home/Clipboard/Copy highlight cells and Paste)
  13. Enter October, November and December totals that are on the sample (watch the Total and Average columns update)

Challenge: make this for more of the year, create new columns for March through May and fill in random amounts.  Double-check that your formulas are updated to include these new amounts.