When you open Excel, a blank "Spreadsheet" usually opens, ready for you to use with the cursor placed in cell A1 in the upper left corner. At the bottom left, you will notice Sheet 1, Sheet 2 and Sheet 3 tabs. These 3 tabs comprise a Excel "Workbook". Let's look at some of the things you may want to do to set the way it opens and the tools you have to use. (Note: What's Different? from Word)

1. Office Button: Save, Open, Print, close
2. Home Tab: Clipboard, Font, Alignment, Numbering, Styles Cells , Editing
3. Insert Tab: Tables, Illustrations, Charts, Links Text
4. Page Layout Tab: Themes, page Setup, Cell Heigth & Width scale, Gridlines and Headings
5. Formulas Tab: Foluma Library, Function Auditing, Calculation
6. Data Tab: Sort and Filter
7. Review Tab: proofing and Comments
8. View Tab: Views, Show/Hide, Zoom, Freeze

What's New? Size | Size 2

The first eight exercises should be submitted via the assignment tool in Blackboard as Excel 1-4 and Excel 5-8. Each file will consist of four exercises. Exercise 9 is a 3rd file due separately as: Excel 9; it will be attached to a message on the discussion board. Your work may not look exactly like the samples shown. The objective is to fulfill the spirit of the exercise.

General protocols - watch the video Cheryl Furbee has kindly shared.
Excel IntroMS07
Excel IntrMS03:

1. Create interactive spreadsheets that use cell referencing, formulas, and/or functions where possible rather than calculating the answers by hand and entering the data.
2. Enter your name in cell A1 using the AutoCorrect function on ALL pages of your workbook. Put Business 5 in cell A2 the same way.
3. Use the sheet tabs at the bottom of the worksheet to label the exercises; i.e Ex 1, Ex 2. To do this, double click on Sheet1, type in Ex 1,
4. You will be creating multiple worksheets in the same workbook. Follow the instructions and submit your files as stated.
5. While not required for the first exercises, it is suggested that you use graphics, fills, colors, etc. to make your worksheets more attractive, if you want.
6. Do not have empty columns. If you need more than one column width to accommodate the cell contents, widen the column. If you see ##### in a cell, widen the column so the numbers can be seen. Make your columns look good, many times this means to make them of equal width, learn how to do this. Also, change the height and alignment of the rows, where appropriate.
7. Unless stated otherwise, use Page Setup to allow the spreadsheet to be printed on ONE page. This may require you to change the orientation, change the margins, make some columns narrower, click on the "Fit to" box, change the font size, etc.
8. Center all headings across all the columns being used (merge cells). Change the font size, style, color, etc. to make the heading look like a heading, unless told to do otherwise.

The following arithmetic symbols and order of precedence are used by Excel and will be used in these instructions. The Order of Precedence means that Excel will perform the action within parentheses first. Use parentheses to change the order of operations when necessary. For example:

your formula is =2+4*10.

• If you want to add the 2 + 4 first and multiply the sum by 10, you would have to enter =(2+4)*10 to get 60;
• otherwise the program multiplies 4*10 and adds 2 which equals 42.

Math Symbols and Order of Precedence

( ) Parentheses

^ Exponentiation

* Multiplication and / Division

+ Addition and - Subtraction

Exercise 1 Watch the video Excel 1

This exercise uses the SUM, ROUND, and AVERAGE functions using relative cell referencing.

Data - Exercise 1

Instructions - Exercise 1 My Sample
Enter your name in cell A1 using the AutoCorrect function on ALL pages of your workbook. Put Business 5 in cell A2 the same way.

1. Enter the data as shown on the example for Exercise 1 and create the following formulas in the appropriate cells:
   1. Regular Pay = Regular Hours * Pay Rate.
   2. Overtime Pay = Overtime Hours * Pay Rate * 1.5
   3. Deductions = Regular Pay + Overtime Pay * .1; do you need parentheses?
   4. Net Pay = Regular Pay + Overtime Pay - Deductions
   5. Since we have a two-decimal monetary system, we should round all the above numbers to two decimals so they will add correctly. This is different from formatting to two decimals. Formatting changes the appearance, the round function changes the cell content; it actually truncates all decimals after the desired number. To use the round function, click your insertion point after the = sign in the formula bar, type in round( then go to the end of your formula [End] key and enter ,2). Your formula will look like this - =round(cell *cell,2). Might need double parentheses. Round each of the above formulas. You should be able to enter the four formulas in the first row and copy them down to the rest of the employees. The fastest way to copy is to highlight the four formula cells across the row and drag the small box in the lower right-hand corner of the range. This copies all the formulas to the remaining 14 employees; you only have to type in 4 formulas and drag one range box. Voila!!
2. Calculate totals (using the Formula tab > AutoSUM function icon) of the following columns:
   1. Use the SUM function to add the total Regular Hours
   2. Copy this by dragging the small rectangle to the right to copy the SUM function from Reg Hours across to Net Pay. Delete the SUM function in the Pay Rate column. Again, one entry copied to all the columns.
3. Calculate the Average Pay Rate using the AVERAGE function. (using the SUM function icon, drop down arrow)
4. Calculate the Total Pay = Total Regular Pay + Total Overtime Pay
5. Format: Home Tab > Number, Alignment, Font Groups
   1. Use one decimal position for the regular hours and overtime hours columns.
   2. Use a comma style with two decimals positions for all other values.
   3. Center the heading Weekly Payroll across all the columns. Bold and enlarge the font of the heading. Change the font color to one of your choice.
   4. Center the column headings except Name. Make the column headings Bold and use a hard wrap to display the headings on two lines, as shown (with cursor in front of the H in Hours, hold down the Alt key and press Enter).
   5. Align the column headings Name and Deductions on the bottom of the cell (Format, Cells, Alignment, Vertical, Bottom).
   6. Make sure the columns are wide enough to accommodate the cell contents.
   7. Use borders to enter a thick line under the column headings and above Totals.
   8. Use borders to double underline the totals.
6. Save the spreadsheet.
7. Change the label of the tab that defaults as Sheet1 to Ex 1.
8. Print Preview and examine the page count to make sure the document will print on one page. If the page count exceeds one page, change to Landscape orientation.
9. Change the spreadsheet to display formulas (Ctrl ~).
10. Change back to regular display.
11. Save the spreadsheet.
    

Exercise 2 Watch the video Excel 2

Open your file from Exercise 1, if necessary. Label Sheet2 as Ex 2. This exercise uses the SUM function and has both relative and absolute cell referencing.

Data - Exercise 2

Instructions - Exercise 2 My Sample
Enter your name in cell A1 using the AutoCorrect function on ALL pages of your workbook. Put Business 5 in cell A2 the same way.

1. Notice the same names and pay rates are used for Exercises 1 and 2. Link the names and pay rates on Ex 2 to Ex 1. To do this, let's assume Name is in A4 on Ex 1. Have the active cell in Ex 2 be A4 (or the cell where you want Name to be). Type in the = sign in A4 on Ex 2, click on the Ex 1 tab, select A4 (or cell containing Name) and press [Enter]. You are now back on Ex 2 and the cell contains ='Ex 1'!A4. (Note: You do not have to be in A4 to link to A4). Return to Ex 1, change Nina to Nick. Notice the change is also made on Ex 2. Press Undo so Nina is restored.
2. Calculations:
   1. Current Weekly Pay = Current Hourly Pay Rate * Hours Per Week (Make this an absolute reference because all employees use this amount for their hours per week: $C$24; make the C24 cell entry red or put a border around it so you can recognize it as a variable you might want to change later and seehow the calculations recalculate.)
   2. Proposed Hourly Pay Rate = Current Hourly Pay Rate * Percent of Increase + Current Hourly Pay Rate OR Current Hourly Pay Rate * (1 + Percent of Increase). Make the Percent of Increase an absolute cell reference for the same reason Hours Per Week is an absolute cell reference. Use the F4 key
   3. Proposed Weekly Pay = Hours Per Week * Proposed Hourly Pay Rate
   4. Round the above formulas to 2 decimals as well as formatting to 2 decimals
   5. Total Current Pay = Sum of the Current Weekly Pay column
   6. Total Proposed Pay = Sum of the Proposed Weekly Pay column
   7. Weekly Increase = Total Proposed Weekly Pay - Total Current Weekly Pay
   8. Annual Increase = Amount of Weekly Increase * 52
3. Format:
   1. Use a comma style with two decimal places for all dollar values.
   2. Use 0 decimal positions for the hour value.
   3. Use the percent style with 0 decimal positions for the percent of increase value.
   4. Center the Table Name and Column Headings except Name which is vertically aligned at the bottom of the cell.
   5. Center all column contents, except the names. Notice the default alignment for text and numbers.
   6. Enter a thick border below the Column Headings and after the last employee name.
   7. Use colors, in bold for the percent increase and hours per week
   8. Be sure you have columns wide enough to accommodate the cell contents - do not have empty columns. Home tab > in Cells Group choose Format
4. After completing the exercise, change the percent of increase to 10% (.1). Every value in the entire proposed hourly pay rate and the proposed weekly pay column should change. Then change the hours to 30 and every value in the current weekly pay and the proposed weekly pay column should change. If this does not happen, you have done the exercise wrong; you have not used absolute cell referencing. Make these changes to check if you have done the exercise correctly before you submit the file. Change back to 5% (.05) and 40 hours before submitting.
5. Save the spreadsheet again, now both Exercise 1 and Exercise 2 are on one workbook.

Exercise 3 Watch the video Excel 3 & 4

Open your file from Exercises 1 & 2, if necessary. Label Sheet3 as Ex 3. This exercise uses both relative and absolute cell referencing and the ROUNDUP function.
My Sample

Data - Exercise 3

Instructions - Exercise 3

1. Use AutoFill to enter the quantities
   Establish the pattern in 2 consecutive cells, highlight both and drag
2. Make the columns all the same width
3. Calculations:
   1. Total Cost = Fixed Costs + Variable Cost Per Unit * Quantity. Do you have to use ( ) for Order of Preference?
   2. Revenue = Price * Quantity
   3. Profit/Loss Per Unit = Total Revenue - Total Cost / Quantity. Do you need ( )? Where?
   4. Break-Even = Fixed Cost/(Price - Variable Cost Per Unit). Do you understand the need of the ( )?
   5. If done correctly your break even point will contain some decimals. However, we cannot produce a fraction of a unit so to truly break-even we must round up to the nearest whole number of units. Therefore, use the ROUNDUP function - =ROUNDUP(formula in calculation #4,0). The 0 is to have only whole units and no decimals. Watch your ( ).
   6. Format any negative numbers to show in red with ( ) rather than a minus sign.
4. Set it up to print the gridlines. Page Layout tab > Page Setup Group options > Sheet Tab > check "Print Gridlines" box
5. Do a Print Preview and make any changes necessary so the entire spreadsheet fits on one page.
6. Center the data horizontally on the page. Page Layout tab > Page Setup Group options > Margins Tab > check Center on Page: Horizontally
7. Save the workbook - you now have three exercises on one workbook.
   

Exercise 4

This exercise uses the AVERAGE, MIN, MAX, COUNT, and COUNTA functions and has only relative cell referencing.

Data- Exercise 4

Instructions - Exercise 4 My Sample

1. Using the same workbook, insert a fourth worksheet. Change the tab to Ex 4. Arrange the tabs in numberical order, if necessary. Link the names to Ex 1.
2. Make the data columns the same width. Home tab > in Cells Group choose Format > Column Width
3. Fill in the appropriate functions (MIN, MAX, AVERAGE, COUNT, and COUNTA) (using the SUM function icon, drop down arrow) for the rows and columns as needed. Notice the difference between Count and CountA. The Average Grade, High Grade, and Low Grade should be fore EACH exam. The Avg of all Tests and Number of Tests should be for ALL 60 tests. Use Help (F1 key and search for Statistical Functions then scroll to find "List of worksheet functions (by category)")
4. Format with 0 decimals for the entire worksheet.
   Use the "Decrease Decimal icon in the toolbar and the Format Painter
5. Make the following changes:
   First look at and review Formulas Tab > Calculation Options on far right
   1. Ambrosini - delete the score for test number 4
   2. Booth - change the score for test number 2 from 67 to 0
   3. Alderson - delete the scores for tests 3 and 4
   4. Insert your name between the names of Batazatto and Booth with the following test scores: 95-98-97-100
   5. Sort the spreadsheet based on the name column in ascending order
      High Light the spreadsheet data > Data Tab > and in the Sort & Filter group choose > Ascending by Column A
   6. Sort the spreadsheet based on the average of the four tests in descending order
      High Light the spreadsheet data > Data Tab > and in the Sort & Filter group choose > Descending by Column H
6. Add appropriate borders. Add colors and fills.
7. Center horizontally on the page. Remember Page Setup?
8. Save the workbook
9. Submit the workbook via Blackboard. Note: You will have all four exercises on ONE file. This ONE workbook contains 4 worksheets.