Spreadsheet Fundamentals: Building a budget
An introduction to the most powerful data handling tool for the masses
Start Here: Jump to a section
This module was engineered to be worked through sequentially: from top to bottom
wb_incandescentCore idea: Spreadsheets in Brief
check_boxLearning Objectives
bookModule resources & links
webSetup your workspace: Software Options
extensionCore Concept: Managing Tabs
extensionCore Concept: Entering data
extensionCore Concept: Formatting content
extensionCore Concept: Creating formulas
motorcycleExercise 1:Exploring interesting spreadsheets
motorcycleExercise 2: Creating a collection tracker
wb_incandescentSpreadsheets in brief (15 min overview)
Screen cast tutorial on completing this introductory section
Spreadsheets are a tool for organizing data and calculating figures on sets of numbers. All data is organized into boxes laid out in a grid. The vertical columns are named with letters. The rows are numbered.
This spreadsheet tracks the number of miles somebody drives over the course of a single week. A nicely formatted header row at the top tells the reader what each column contains. A bolded total row calculates a total of all miles driven during the week.
Jump in with this tutorial: Open a spreadsheet program of your choosing--perhaps the Microsoft Corporation's Excel program or Libre Office Sheets--and create this spreadsheet by typing the values you see on the screen.
Each box is called a cell and are named by the column letter then the row number. The highlighted cell in the above image is named "B4". Cells can hold one of two types of data:
- Literal (typed in) numbers or text
- Formulas which calculate a cell's value--usually based on the values in other cells. A calculated cell value ALWAYS starts with a magic =
Calculating values in a spreadsheet
We often want to do basic math on a bunch of numbers, like calculating the total value of miles driven. We can do that by typing in a special command into the desired cell called a formula. In the image below, note the following instruction typed into cell B9 which calculates the sum of all miles driven with this special formula:
=SUM(B2:B8)
This formula tells the spreadsheet to add up all values between cell B2 and B8. This is called referencing a range of cells and is written by separating the beginning and ending cell in the range with a colon :
The power of spreadsheets lie in the fact that any time a single value in column B changes, the sum will be automatically recalculated, saving the time and error-prone process of adding up all 7 days' miles by hand after the change.
Spreadsheets can grow!
A single spreadsheet can store data in hundreds of columns and up to a million or more rows! See how easy it is to add data about each day by throwing a new colummn into the sheet:
The new "Food Cost" column is formatted as a currency value, which makes interpreting different kinds of numbers simple. The spreadsheet automatically adds the $. Dates, times, distances, and weights can all be stored in a neatly formatted way.
Cool beans! Tinker for a bit and move on: add a fourth column recording how many clients this person visited each day. Try summing up the values with a formula. We'll save this spreadsheet when you setup your workspace below.
arrow_upward back up to contents
bookExternal Resources
- Spreadsheets on Wikipedia provides An overview of what a spreadsheet is all about, its history, and some of its core functions. Note that this is not a tutorial, but the history and development sections are golden.
- Libre Office: 100% Free and open source alternative to the Microsoft Corporation's Proprietary (and expensive!) Office suite. Link to the Libre Office download page. You can do ALL assignments for this course using this office suite. Your instructor uses it exclusively for all office tasks. These programs all are 90% compatible with Microsoft's Proprietary Office formats like *.docx, *.xlsx so can be shared with Microsoft Users without any hassle. (the 10% non-compatible components are for advanced operations like Macros and charts).
- Kevin O'Brien's Libre Office suite tutorials on his professional page: ahuka.com. . This tutorial section is designed for students choosing to use free and open source Libre Office.
- CCAC's Student software access page contains links to tools for using OneDrive and for downloading Microsoft Corporation's Office Suite and Windows tools. NOTE that any MS Office products downloaded will become inactive soon after your student status ends at CCAC--downloaders beware! You'll be forced to pay upwards of $150 to active a non-student license key to regain access to the Office suite of software.
- The University of Pittsburgh's Microsoft Excel Fundamentals guide is GOLD and is authorized for re-use by other institutions. Please review the table of contents of this document before digging into this lesson so you can refer back to this resource as needed. I'll reference page numbers in here as we go.
arrow_upward back up to contents
check_boxLearning Objectives
- Explain the core concept of a spreadsheet and the difference between a data cell and a calculated cell.
- Navigate a spreadsheet's core features: cell editing, formula creation, sheet formatting
- Create a personal or small business budget spreadsheet that is useful to the creator
arrow_upward back up to contents
webSet up your workspace
The basic features of a spreadsheet will be exactly the same in any major distribution of a spreadsheet: Microsoft Corporation's Excel, Free and open source Libre Office Sheets, Apple's Numbers, Google Sheets, and others.
For this exercise, you can use any spreadsheet program you want. You may need to do a quick internet search if you can't find a feature, but there is lots of documentation about all major spreadsheet applications "out there."
NOTE: Microsoft Corporation's Excel Online is cool, but lacks a fundamental feature of spreadsheets: cell highlighting during forumla editing. You should avoid this tool for this module only.
Setup Steps
Open your spreadsheet program of choice and create a new spreadsheet. Do this by navigating to File >> New or something in that ballpark.
Step 2: SAVE your new spreadsheet in your CIT100 directory using this name setup:
budget_fa18Online_[first name]_[special ID#].[file extension]
For example, Loretta created a file in Microsoft Corporation's Excel online using her OneDrive. She downloaded it and when she viewed it in file explorer, she saw that it's name has a .xlsx at the end. Her special id number is 1289 (the first two and last two digits of her student id squished together). Her file name would be:
budget_f18Online_loretta_1289.xlsx
Choose your format with mindfulness:
We have folks using Excel, Libre Office, Google Sheets, Apple's Numbers, etc. Each of these programs has their own special file format AND can export files into common formats for sharing with others. The best is called open document spreadsheets that creates files with extensions .ods. This is the default extension in Libre Office. If Loretta was using LibreOffice and not Microsoft's Excel, her file name would be:
budgetSpreadsheet_loretta_1289.ods
Saving a spreadsheet in with a .xlsx extension: MS Excel Format on Excel
Saving a spreadsheet in with a .xlsx extension: MS Excel Format from Libre Office Sheets
arrow_upward back up to contents
extensionCore Concept: Managing your sheets
Please view the tutorial video linked below for the core concept demonstration. Follow along in the spreadsheet you created under setting up your workspace above.
arrow_upward back up to contents
extensionCore Concept: Entering Data into spreadsheets
Please view the tutorial video linked below for the core concept demonstration. Follow along in the spreadsheet you created under setting up your workspace above.
arrow_upward back up to contents
extensionCore Concept: Formatting cells
Please view the tutorial video linked below for the core concept demonstration. Follow along in the spreadsheet you created under setting up your workspace above.
arrow_upward back up to contents
extensionCore Concept: Writing simple formulas
Please view the tutorial video linked below for the core concept demonstration. Follow along in the spreadsheet you created under setting up your workspace above.
arrow_upward back up to contents
motorcycleExercise 1: Exploring interesting spreadsheets
Exercise type: Exploration
Take 10 minutes to open and explore spreadsheets that past CIT-100 students have made and been willing to share (link below). The following link (also found on our homepage) jumps you into a Microsoft Sharpoint shared directory. You can click on any of the documents, and navigate into sub-directories, to find some cool examples of students' interests manifesting themselves in rows and columns.
arrow_upward back up to contents
motorcycleExercise 2: Creating a collection tracker
Exercise type: Expand on an example
You should have worked through your instructor in the core concepts videos. These little mini-lessons involved building a spreadsheet of collected items. For this exercise, create your own collection spreadsheet using Eric's as the model. Remember, be as creative as you'd like: you might collect digital things, too.
A sample collections tracker:
arrow_upward back up to contents
buildModule Mini-Project for submission and sharing
Exercise type: Culminating Activity
With the essentials under your belt, continue what we started in the overview video and have fun making your budget!
highlightProject Specification: Create a budget spreadsheet that tracks your budgeted and actual amounts earned and spent over the course of a month. Since you'll be uploading this to the public server, obviously you can enter whatever dollar values you'd like. But the more realistic, the more useful!
A sample Completed Budget:
Submission steps:
Located at the end of our weekly guide.
arrow_upwardback up to contents