Practice your Intermediate Excel skills by using autosum and functions to create simple and complex formulas, calculate sales tax, create your own home budget, and use tables to sort and total data.
Excel Formulas and Functions Exercises
Worksheets
1. Add 7 additional work sheets to your workbook
2. Label them according to the exercise, For example: Simple formulas: addition and subtraction
3. Give each Worksheet tab a different color. Any color.
Simple Formulas: Addition and Subtraction
1. Create an addition formula for cells B1 and B2. The formula should go in cell B3
2. Create a subtraction formula for cells C1 and C2. The formula should go in cell C3
Simple Formulas: Multiplication
1. Create a formula that will calculate the total pay based on the number of hours and the wage per hour. The
formula should go in cell B3.
Complex Formulas: Averaging Grades, Division, and AutoFill
1. Average student 1’s grades.
2. Challenge: Use AutoFill to complete the formulas for students 2-5
3. Challenge: use AutoSum to average the grades instead of typing the formula out manually
Tip: use the Number formatting commands to decrease decimals to two places for your totals
Complex Formulas: Averaging Grades, Division, and AutoFill
1. Average each student’s grades for the three tests.
2. Challenge: Use AutoFill to complete the formulas for James and Linda.
Complex Formulas: Adding Tax to a Price
1. Type the below data, applying formatting as shown (fill color, font color, resizing cells, number formatting for
currency)
2. Calculate the total price of items—formulas should be in cells D4 and D5
3. Calculate 5.5% sales tax on item totals (0.055 for 5.5% tax)—formula should go in cell D10
4. Calculate the total price of the order, including the two totals and the tax—formula should go in cell D11
Tips:
USE CELL ADDRESSES
Use AutoFill when appropriate
Use the order of operations for complex formulas
Functions
1. Enter the below data.
2. Use functions to answer the questions. The answers can go in the row labeled total
Tips: Functions used:
ISEVEN
ROUND
SQRT
POWER
Extra Practice: Creating a Home Budget
1. Data Entry: Enter in only the LABELS: no numbers yet
2. Formulas: Addition and Subtraction— there are three formulas in total
3. Make it pretty: Use formatting commands to apply fill color to your sections and borderlines
4. Enter the number values: use the above numbers or make up your own. The formulas will adjust to calculate
whatever value is entered in to the cells!
Supporting files:
Tip Type: