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: