MS Excel Formulas & Functions

Exercise 1:Payroll
Introduction to MS Excel files, Workbooks, Worksheets, Columns and Rows.
 Formatting Worksheets.
 AutoFill, Numeric formats, previewing worksheets.

1. Open a new workbook and save the file with the name “Payroll”.
2. Enter the labels and values in the exact cells locations as desired.
3. Use AutoFill to put the Employee Numbers into cells A6:A8.
4. Set the columns width and rows height appropriately.
5. Set labels alignment appropriately.
6. Use warp text and merge cells as desired.
7. Apply borders, gridlines and shading to the table as desired.
8. Format cell B2 to Short Date format.
9. Format cells E4:G8 to include dollar sign with two decimal places.
10. Calculate the Gross Pay for employee; enter a formula in cell E4 to multiply Hourly Rate by
Hours Worked.
11. Calculate the Social Security Tax (S.S Tax), which is 6% of the Gross Pay; enter a formula in
cell F4 to multiply Gross Pay by 6%.
12. Calculate the Net Pay; enter a formula in cell G4 to subtract Social Security Tax from Gross
Pay.
13. Set the work sheet vertically and horizontally on the page.
14. Save file as Exercise 1
 Exercise 2

1. Open a new workbook and save the file with the name “Call Statistics”.
2. Delete Sheet 2 & 3, and rename Sheet 1 to (Call Statistics).
3. Enter the labels and values in the exact cells locations as desired.
4. Set the row height of rows 1 & 3 to size 30; and rows 4 until 10 to size 20.
5. Set labels alignment appropriately.
6. Use Warp Text, Orientation and merge cells as desired.
7. Apply border, gridlines and shading to the table as desired.
8. Format column E to include Pesos sign (20b1 + alt + x) with two decimal places.
9. Format cell B12 to include % sign with 0 Decimal places.
10. Calculate the Calls per Hour, enter a formula in cell D4 to divide numbers of calls by Hours
worked. Using AutoFill, copy the formula to the remaining cells.
11. Calculate the Bonus. Enter a formula in cell E4 to multiply ‘Calls per Hours’ by the fixed
Bonus Rate in cell B12. Using AutoFill, copy the formula to the remaining cells.
12. Calculate the ‘TOTAL’.
13. Set the worksheet vertically and horizontally on the page.
14. Create a header that includes your name in the left section, and your ID number in the right
section. Create the footer that includes the current Date in the center.
15. Save file as Exercise 2
 Exercise 3
Number, Commas and Decimal numeric formats.
 Working with Formulas (Maximum, Minimum, Average, Count and Sum).
 Percentage Numeric Formats.

1. Create the worksheet shown above.

2. Set the column widths as follows: Column A: 8, Column B: 14, Columns C & D: 15,
Columns E & F: 14.
3. Enter the formula to find COMMISSION for the first employee. The commission rate is 2%
of sales, COMMISSION = SALES * 2% Copy the formula to the remaining employees.
4. Enter the formula to find TOTAL SALARY for the first employee where:
TOTAL SALARY = SALARY + COMMISSION
Copy the formula to the remaining employees.
5. Enter formula to find TOTALS, AVERAGE, HIGHEST, LOWEST, and COUNT values.
Copy the formula to each column.
6. Format numeric data to include commas and two decimal places.
7. Align all column title labels horizontally and vertically at the center.
8. Create a Header that includes your name in the left section, page number in the center
section, and your ID number in the right section.
9. Create footer with DATE in the left section and TIME in the right section.
10. Save the file with name Exercise 3.
 Exercise 4

 Working with the IF Statement.

For the above table find the following:

1. TAX (If ITEM PRICE is less than 100, TAX is 50, otherwise it should be 100).
2. TOTAL PRICE BEFORE TAX =NO. OF ITEMS * ITEM PRICE.
3. TOTAL PRICE AFTER TAX = TOTAL PRICE BEFORE TAX + TAX.
4. RATE (If TOTAL PRICE AFTER TAX > 3500 then the rate is “HIGH”,
otherwise it is REASONABLE.
5. Find Count of Items, Average of Taxes, Min Item PRICE and Max Item PRICE.
6. Save file as Exercise 4.
 Exercise 5

Working with Sum IF and Count IF statements.

Inserting Charts.

1. Create the worksheet shown above.

2. Set the Text alignment, Columns width and high appropriately.
3. Use AutoFill to put the Series Numbers into cells A5:A7.
4. Format cells C3:G7, C8:E11, C13:E13 to include dollar sign with two decimal
places.
5. Find the Average Sales and Maximum Sales for each City.
6. Find the Total Sales for each Month.
7. Calculate the Profit for each month , where profit = Total Sales – Cost
8. Calculate the 10% Bonus, which is 10% of the Profit.
9. Find the Total Sales for each Month; only for sales greater than 30,000.
10. Find the No of Sales for each Month; only for sales greater than 30,000.
11. Create the following Charts:
12. Save file as Exercise 5.
 Exercise 6

• Working with Sum IF and Count IF statements.

• Inserting Charts.

1. Open a new workbook and create the above worksheet.

2. Make sure that your worksheet looks like the picture (Alignment, Shedding,
Borders, Wrap text, Orientation …).
3. Find the entire customer IDs.
4. Format Colum E & D to Currency with Peso sign and two decimal places.
5. Find the Total Annual Purchases for each City.
6. Find the Average Annual Purchases for each Education.
7. Find the total number of customers from each gender.
8. Find the total annual salary for each gender in each city.
9. Create the following Chart:
10. Save file as Exercise 5.
 Exercise 7

Create a Spreadsheet that calculates statistics regarding the number of students enrolled at
Armstrong Twp. High School during a 5 year time period.

1. Begin by opening up a new worksheet in Microsoft Excel.

2. Copy the following spreadsheet exactly as show below. Make sure that you copy the exact
statistics shown on the table. Use the same rows and columns as shown.

3. The Highlighted Cells are where you will need to enter your formulas.

4. For each school year, enter a formula that will ADD the total number of males and females
for each grade level.

5. Enter a formula that calculates the Average Total Number of students by grade level for
each of the 5 years.
6. Enter formulas that calculate the Total Number of Students enrolled at ATHS for each of the
5 years.

7. Use the following equation to create formulas that calculate the percentage of students in
each grade level, for each of the 5 years.

Total # of Students in a Grade Level / Total # of Students = % of Students in Grade Level

8. Format the Percentages with NO DECIMALS!

9. Create OUTSIDE BORDERS around all of your cells where you have entered formulas

10. Save file as Exercise 7.

 MS Excel Exercise 8: Space Weight
1. You will be constructing a spreadsheet that will calculate your current weight if you were
standing (and still alive!) on each of the planets (including Pluto).

2. Open a New Worksheet in Microsoft

Excel.

3. Type the spreadsheet shown to the right.

4. In the column titled, New Weight (lbs)

insert a formula that multiplies the
gravity factor by the weight entered in
the cell entitled, My Weight (lbs).
MAKE SURE
TO USE ABSOLUTE REFERENCING!!!

5. After you have entered your formulas

and calculated your weights for each
planet. Highlight the entire table of
planets, gravity factors and new weight,
and Sort the Data by Gravity Factor in Ascending order.

7. Test your weight calculator by typing in different weights in the cell next to the My
Weight (lbs) = . You should notice that the New Weight column adjusts automatically. If not,
check through your formulas...

8. Type your full name in row 1.

9. Save file as Exercise 8

 MS Excel Exercise 9: GPA Calculator
1. You will be constructing a spreadsheet that you can use to calculate your current Grade
Point Average. (GPA)

2. Open a New Worksheet in Microsoft Excel.

3. Copy the spreadsheet shown to the next page.

Under the heading Subject, list the classes that you currently are enrolled in.

4. In the column titled, Current Grade,

check the grades for your current
grades in each subject and enter them
in the appropriate cell.

5. Use an IF Function to create the

Grade Values. Use the NOTE:
Grade Values to help you.

Condition: IF Current Grade = “A”, then

4.0, else IF Current Grade = “B”, then 3.0,
else IF Current Grade = “C”, then 2.0, else
IF Current Grade = “D”, then 1.0, else IF
Current Grade = “F”, then 0.0, else 0.0

6. In the Total Values = cell, insert a

formula that calculates the Total of all
individual letter grade values found in
the Grade Values Column. (Add up
all the individual letter grade values).

7. In the MY GPA= cell, insert a

formula that calculates the AVERAGE of all the individual Grade Values. You can do
this
either by using the Function Button and selecting AVERAGE for the range of cells or by entering
a formula for calculating averages. (Total of all values / total # of individual values).

8. Use the Decrease Decimal button to round of the decimals to the nearest 100th. (Two
decimal places)

9. Under the cell that calculates MY GPA, Insert an IF Statement for the following condition:
If a student's GPA is greater than or equal to a 3.50, then have the cell display HONOR ROLL!
If the student's GPA is less than the 3.50, have the cell display, Keep Trying!
10. Try out your GPA Calculator. Type in a series of low grades (1's & 2's) and then high
grades (3's & 4's) and see if the IF statement changes to Keep Trying or Honor Roll!

11. TYPE YOUR FULL NAME IN ROW 1

12. Save file as Exercise 8