Skip to content

Review of Formulas and Visuals

Playback Speed:
Transcript

This video will review basic and advanced spreadsheet formulas for calculating expenditures.

To calculate multiple occurrences of the same cost, enter the calculation directly into the cell.

For example, if your hotel costs 95 dollars a night and you plan to stay for 4 nights, type “equals 95 asterisk 4.”

To add all the values in a column, use a Sum formula.

For example, to add the costs for your entire trip, select an empty cell.

Type “equals, SUM” and select the cells with costs.

Press Enter to calculate the result.

To calculate category totals, create a space in your spreadsheet for categories.

List Transportation, Lodging, Food, and Activities in separate rows.

Under the total for the first category name, type “equals SUMIF.”

Select all of the cells in the category column, or type the cell range if there are lots of cells.

Then, type a comma and select the cell with the criteria, or category name.

Add another comma and select the cells, or type the cell range, in which you listed the costs.

Make sure to separate the range, criteria, and sum range with commas.

Press Enter, and the function automatically adds all the items in this category.

Copy the formula to the other categories, using absolute cell references where needed to indicate values that do not change.

Change the cell format to “currency” to reflect dollars and cents wherever necessary.

When you finish calculating all your expenses, add a chart to help visualize your spending.

Select the table you created with the category names and totals.

Then, insert the chart.

If you are still confused about these formulas, try to figure out the problem yourself.

Return to previous activities in this unit.

Or, search the internet for more advice.

If you have time, make your spreadsheet more personal by adding images, links, fill colors, font styles, and other visuals.

If you can’t figure out how to add these features, use the “Help” tab, ask a neighbor, or search the internet for more instructions.

Now It’s Your Turn: Calculate your expenses as you research them.

Keep track of how much you are spending for each item in your spreadsheet.

Add a sum formula that adds all the values in the Costs column.

Calculate category totals for transportation, lodging, food and souvenirs.

Add other elements to your spreadsheet like data validation lists, heading colors, or images.

Choose an Extension
Transportation
Watch this video for tips on transportation options for your trip.
Lodging/Accommodations
Watch this video for tips on finding a place to stay on your trip.
Food
Watch this video for tips on finding places to eat on your trip.
Attractions and Souvenirs
Use this video to help budget for things to do and souvenirs to buy on your trip.
Review of Formulas and Visuals
Watch this video to review how to use formulas and add visuals to your budget spreadsheet.
See more extensions
Next