Summer 2002 The Discovery Museums Tufts School of Engineering Lucent Technologies Foundation
By the end of this workshop, you will be able to:
use simple commands and perform calculations in Microsoft Excel
create a budget sheet to keep track of your spendings during the program
Activity: making a Budget Sheet
For each step, take your time and we will come around to help you and to answer any questions.
Before we begin, let’s draw some objects to see where we all stand in drawing techniques.
Step 1:Open Excel to a blank page. Go to Start Programs Excel, and click.
This is what your starting screen would look like:
The spreadsheet consist of “cells,” and each cell has a corresponding “address.” For example, the address of the cell highlighted above is D8, meaning that the cells is at the intersection of Row 8 and Column D.
Step 2:In cell 1A (the upper left corner), type in a TITLE for the budget sheet. An example is shown below.
Step 3:
Using the cells in row 3, type in the headings to include in the budget sheet. Examples are: Date*, Item Number*, Item Description, Quantity*, Cost Each*, Total Cost*, Vendor Name, etc ( * - these are required headers). An example is shown below.
Enter one mock entry in your budget sheet. Type in date, item, quantity, and unit cost (but not the total cost – we’ll get to this next).
Step 4:
You can make Excel do all the math for you.
If you get X number of item at Y dollars each, then the total cost is (X)×(Y).
Looking at the first entry of your budget sheet, locate the cell address for “Quantity,” “Cost Each,” and “Total Cost.” In the example, these are D4, E4, and F4.
In the cell corresponding to Total Cost (F4), type in a “=” followed by the formula for [Cost Each×Total Cost] – (“=D4*F4” in the example). The total cost should appear in the cell.
To make Excel do the same calculation for the rest of this column, click on the cell containing the formula, grab the small square at the lower right corner with your mouse and drag it down until the end of your data. This automatically “fills” the cells with the formula that you had put in in the first cell.
When performing calculations in Excel, keep in mind that Multiply is “*” and Divide is “/” and you need an equal sign “=” before each formula. All normal rules with parenthesis and precedence (order of calculation) apply.
Step 5:The spreadsheet can be formatted to make it look the way you want it. For example, we can make the date to look like “7/10/02.” Right-click on one of the dates that you have entered, and choose “Format Cells.”
Choose one of the formats, and click on “OK.”
To apply the same format to multiple cells, highlight all cells that apply, right-click on the highlighted area, and choose “Format Cells.” Alternatively, you can click on the formatted cell, then grab and drag the small square at the lower right corner to fill the format to other cells.
Repeat this process to change the format of “Cost Each” and “Total Cost” to Currency format with two decimals.
Step 6:
Add more entries. The new entries should be automatically formatted in the way you have set it, and the Total Cost for each item should be calculated as soon as you enter Quantity and Cost Each. The Total Cost will be updated if you change any of the values.
Step 7:
The total cost of the budget sheet should be calculated to help you keep track of how much money you have left for your project during the summer.
Choose a cell that is below your entries in the Total Cost column. Input in that cell the following formula: “=SUM(F4:F9)” -- F4 and F9 are the first and last entry that you have, and defines the range to perform the sum over. Change this to match your budget sheet.
Alternatively, you can type “=SUM”, then use your mouse to highlight the cells that you want to add up.
Step 8:
The spreadsheet can be formatted with colors and borders to make it easier to read. For example, you can add gridlines by highlighting the area you want covered, then click on Borders button on the toolbar, and select the type of gridlines you want.
1