Lab: Excel 2
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 1 of 12
This assignment will expand your knowledge of spreadsheet operations by introducing you to some common spreadsheet tasks. Specifically, this assignment will require you to:
With this assignment, you will: Use the functions Sum, Min, Max, and Average. Perform text, cell, and image formatting. Learn absolute and relative cell addressing. Create and format a chart that visually depicts a worksheet data. Add worksheet protection to prevent changes to the worksheet
What & How to Submit:
Attach the following file to the Canvas assignment: a. YourLastName YourFirstName SalesReport. exe the extension may not be visible on your
computer
Your score will be based on the (25 points maximum): 1. Quality and completeness of your completed assignment
a. YourLastName YourFirstName SalesReport.exe Protected worksheet 2. Spelling and grammar (potential deductions) 3. Following directions (potential deductions)
Instructions:
This lab requires you to download the file Excel P2 SalesReport.xlsx before you continue.
There is a screen print of the final result on the last page of this document
1. If you are not doing this assignment at Mt. SAC, make sure you are using Excel 2016. Although you can use previous versions of Excel to complete this assignment, some of the assignment instructions will not match your older versions—which may make it difficult to complete the assignment.
You cannot use versions of Excel before 2007 to do this assignment.
If you are doing this assignment in the lab, remember that you need your student ID to be assigned a computer.
2. To access Microsoft Excel:
Open Excel 2016
Click the Start button on the bottom left corner of the Taskbar.
In the black section of the Start menu you should see capital letters of the alphabet. Click one of the letters e.g. A or C. Then scroll down to the E and click Excel 2016.
If you do not see the capital letters, click All Apps then continue with the instructions in the bullet above.
3. Let’s retrieve the Excel P2 SalesReport.xlsx file from storage. You needed to have already saved the Excel P2 SalesReport.xlsx file to a flash drive or to your computer from Canvas.
Open the Excel starting file Excel P2 SalesReport.xlsx you downloaded from Canvas.
In the Green Navigation pane on the left, click Open Other Workbooks at the bottom
Double‐click This PC under the large‐font Open just to the right of the green navigation panel.
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 2 of 12
(1)
(2)
OR Click the Browse button under the large‐font Open just to the right of the green navigation panel
Use the Open Dialog box to navigate to the location you saved the SalesReport Workbook
After you locate your downloaded copy of the Excel P2 SalesReport.xlsx select it (click on the file name).
Click the Open button on the bottom right side of the window to open the workbook. The workbook should now be brought into the Excel window.
4. Rename the Workbooks
Save the workbook with the name YourLastName YourFirstName SalesReport.
Click on the File tab, and then click Save As.
Double‐click This PC under the large‐font Save As just to the right of the green navigation panel. OR Click the Browse button under the large‐font Save As just to the right of the green navigation panel
Use the Save As Dialog box to navigate to the location you saved the renamed Excel file
At the bottom of the Save As dialog box are textboxes that display the name and format of the file you are saving.
In the File name: textbox, type YourLastName YourFirstName SalesReport (replacing the words YourLastName YourFirstName with your own name).
Click the Save button
5. About the SalesReport worksheet.
The SalesReport worksheet reports quarterly data about sales representatives for Mountie Enterprises. The basic structure of the worksheet has already been created. Your job is to complete the worksheet as instructed in the following pages.
6. Formatting the header.
Right‐align the contents of Cell B2 which has already been merged to span from B2 through D2.
Click on cell B2 to select it. To format an area of a worksheet you must first select the area to be formatted.
Right align the text by clicking on the Align Text Right button (1) ‐ located in the Alignment group on the Home tab).
Notice how far right the text moved.
Cell B2:D2 was already formatted as Merged & Centered range of cells. B2:D2 represents a range of cells including B2, C2 & D2.
The Merged & Centered button (2) is located in the Alignment group of the Home tab.
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 3 of 12
Increase the font size to 14 for the contents of Cell B2
With the Cell B2 still selected, click on the Font Size down arrow button (located in the Font group on the Home tab), and then clicking 14 from the drop‐down list box.
Apply bold and Italics to the contents of Cell B2
With the Cell B2 still selected, click the Bold button which displays and Italic buttons (also within the Font group).
7. Formatting cell width.
Increase the width of column A using AutoFit (or BestFit)
To increase the cell width of column A so that the full name of each sales rep is displayed, move the cursor to the gray line separating cells A and B at the very top of the worksheet.
When you are positioned on the vertical gray line, the cursor should change to a dark bold cross sign.
When the cross appears, double‐click. Column A expands just enough to fit the widest field in the column. (See Excel Part 1 for a review of this concept.)
8. Calculating Gross Profit. Gross profit is the difference between the revenue received from selling a product and the
costs incurred to either purchase or manufacture it. Cell B17 indicates that the profit margin for items
In cell C6, type a formula to calculate Gross profit of Sales
Click C6. Calculate the gross profit of George Bush’s sales of by typing the following formula into C6: = B6*B17 (Gross Profit * Profit Margin).
Press Enter and then move the cursor back to cell C6. You will see the value of 15,400 in C6 and the formula displayed in the formula bar.
9. Relative versus Absolute cell addresses also known Absolute cell reference.
When Excel copies a formula to another cell, it uses Relative Addressing. Relative Addressing, also known as Absolute referencing, is a feature in which Excel automatically changes the column and row addresses in a formula to correspond to the new location of the formula. It is the most common type of addressing.
For example, the grade worksheet pictured on the following page contains students’ grades for the first two exams. The formula in D2 adds the first two exam grades. If Excel did not utilize relative addressing, then if the formula in D2 was copied to D3, D3 would still contain the formula = B2+C2, and Sue Briggs would incorrectly be given the same total score as Bob Smith. Excel prevents this mistake from happening by automatically changing cell addresses in formulas to correspond to the formula’s new location. In this example, if the formula in D2 was copied to D3, Excel would recognize that the new location of the formula is one row below the original location, and change the formula to = B3+C3.
A B C D
1 Name Exam 1 Exam 2 Total
2 Bob Smith 86 94 = B2+C2
3 Sue Briggs 70 66
4 Jim North 98 94
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 4 of 12
However, there are situations when you do not want Excel to use a relative reference. For example, within the SalesReport worksheet,
Copy the formula in C6 to C7 this will produce an error; continue on
Copy the formula in C6 to C7 by clicking C6 and then dragging the small green square in the bottom right corner of C6 down to C7. (See Excel Part 1 for a review of this concept.)
Click C7. Notice the value of 10,800 is incorrect.
Click C7. The value of 10,800 is incorrect. Look at the formula bar.
Due to relative addressing, the new formula multiplies Bill Clinton’s sales by the commission percentage of 10%. Since the copied formula’s cell location is one row below the original formula’s cell location, Excel changed the row address of both operands in the original formula to one row below their original address. Although it was correct for Excel to change the address of B6 to B7, we do not want to change the address of B17 to B18. Rather, we want to multiply each sales figure in B6 through B9 by the same Profit Margin value in B17. To do so, we have to change the original formula to indicate that B17 is an Absolute address.
An Absolute address does not change when a formula is copied to another cell. An Absolute address is indicated by placing a $ sign to the left of the column and row address ($B$17).
There are a couple of ways to enter an Absolute cell reference. You can type a $ before the column letter designation and the row number designation or you can position your cursor in the cell B17 and press F4.
Change the cell reference B17 in cell C6 to absolute value e.g. = B6*$B$17.
Click C6. Move the cursor before the B and type a $. Move the cursor before the 1 and type a $. You should now see $B$17 rather than B17. OR Click C6 and position the cursor on B17. Press the F4 function key. This will change B17 to $B$17.
Copy the formula to cells C7 through C9.
Refer to step 9 above for directions on how to copy cells
Click each of the cells from C6 through C9 to observe that Excel used relative addresses for the first cell operand and absolute addresses for the second cell operand.
10. Calculate Net Profits.
Net profit is the difference between gross profit and expenses. In cell C6, type a formula to calculate Net Profits.
To calculate George Bush’s net profit for this quarter, click E6 and enter the following formula: = C6‐D6 and press Enter
Note that we want to use relative addresses for both of the formula’s operands in this situation.
Copy the formula to cells E7 through E9.
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 5 of 12
11. Formatting cell color.
Since net profit is a very important category for Mountie Enterprises, Michelle Obama, the regional sales manager, wants to highlight the Net Profits column by giving it a colored background.
Fill cell range E6:E9 with a color of your choice
Click cells E6 through E9.
Click the Fill Color button (located within the Font group). It looks like a bucket spilling paint.
Click the down arrow to the right of the Fill Color button and select the color of your choice.
12. Calculate Commission.
Mountie Enterprises pays its sales reps a quarterly commission of 10% of the sales reps’ net profits.
In cell C6, type a formula to calculate Commission. The percent commission must be an absolute cell reference.
To calculate George Bush’s quarterly commission, click F6 and enter the following formula: = E6*$B$18. (The value should be $1,396)
Note that we must use an absolute address for B18 so that the operand will not change when the formula is copied to other cells.
Copy the formula in F6 to cells F7 through F9. The result is the quarterly commission for each sales rep.
13. Calculate column totals.
In cell B11, create a formula using a function to calculate Total Sales.
A Function is a formula built into Excel. All Functions begin similar to =SUM( Click B11 and then click the AutoSum button ( ∑ ) on the Ribbon (within the Editing group of
the Home tab). Excel automatically enters the function to sum the values of cells B6 through B10. (B10 is empty so we will not remove it from the range.)
Press Enter. The result is that B11 contains the total sales of all four sales reps.
Copy the formula in cell B11 to the range C11:F11
This will provide totals for gross profits, expenses, net profits, and commissions of the sales reps.
Mrs. Obama wants the Total Sales row to stand out. Format all of the cells in row 11 with boldface font.
Click Row 11 (far left of the window) to select the row
Clicking on the Bold button in the ribbon
Click on the Save button ( ) located on the Quick Access Toolbar to save your work
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 6 of 12
You can perform many functions with keyboard shortcuts. Keyboard shortcuts are keys pressed in combination – press and hold the Ctrl key and the S key to save. The following are a few common keyboard shortcuts:
Ctrl+S Save Ctrl+X Cut Ctrl+B Bold
Ctrl+A Select All Ctrl+C Copy Ctrl+I Italic
Ctrl+F Find Ctrl+V Paste Ctrl+U Underline
Ctrl+H Replace
14. Mrs. Obama wants to know the average sales of the four sales reps.
In cell B13, enter a Function to calculate the Average Sales (range B6:B9)
Click cell B13, then click the Formulas tab located at the top of the Ribbon.
Click the Insert Function button located towards the left of the Formula Ribbon.
In the Select a Function: textbox, click AVERAGE, then click OK.
If Average is not visible in the Select a function: textbox, click the down arrow to the right of the Or select a category textbox and click Statistical.
In the Number1 textbox: Edit the formula so that the range is B6:B9. To do this ‐ Click to the right of the 12 then backspace two times. Type 9. The value should read B6:B9. Click OK
Within the Function Arguments dialog box you will see the cell range B6:B12 displayed in the Number 1 textbox. We do not want to average this range, as it would erroneously include cell B11—which contains the total of all four sales reps.
Before you click OK in the next step, notice the brief explanation of the function appears at the bottom of the Insert Function dialog box.
Then click on the OK button. The average sales of the four sales reps is now displayed in B13.
To move the dialog box (or any window that is not maximized), click and drag the title bar to a new location.
Copy the formula in B13 to cells C13 through F13 to calculate the average Gross Profits, expenses, net profits and commissions of the sales reps by.
15. Mrs. Obama also wants the worksheet to provide the highest and lowest values in each category. The Excel built‐in functions Max and Min provide the highest and lowest values, respectively, for a specified range of cells.
In cell B14, enter a Function to calculate the Minimum Sales (range B6:B9)
Click cell B14, and then click the Formulas tab located at the top of the Ribbon.
Click the Insert Function button located at the far left of the Formula Ribbon.
Click the down arrow to the right of the Or select a category textbox and click Statistical
Scroll down until you see MIN. Click MIN, then click OK.
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 7 of 12
In the Number1 textbox, edit the contents so that the range B6:B9 is displayed. To do this ‐ Click to the right of the 13 then backspace two times. Type 6:B9. The value should read B6:B9. Click on the OK button.
Another way to enter the correct range is to click and drag through the range. To do this ‐ Click & Drag the title bar of the Functions Arguments dialog box so is not covering your data. Click in B6, hold the mouse button down and drag through B9. Release the mouse button.
Copy the Function in cell B14 through cell F14.
Use the same steps given above to place into cells B15 through F15 the highest values for each of the five categories.
Hint: You should use the same approach as you did to determine the lowest values, except you will use the MAX function rather than the MIN function.
16. Mrs. Obama wants the cells with currency data to be formatted with a leading dollar sign. (See Excel Part 1, step 23 for a review of this concept.)
Format cells B6 through F15 to the Accounting Number format ($) with NO decimal places
Click the Home tab.
Click the Accounting Number Format ($) button (located within the Number group of the Home tab)
Eliminate the decimal points by clicking the Decrease Decimal (.000) button two times (in the Number group).
All of the numbers should now appear as whole numbers with dollar signs. Enter your first and last name into cell A20.
Save your work.
17. Delete a row
Right‐click on the number 5 at the far left end of row 5.
Then click the Delete option in the shortcut box.
Because the Excel charting feature assumes continuous data, we must delete the row 5 line separator.
18. Create a pie chart to visually depict the amount of net profit each sales rep contributed to the company’s quarterly total.
The labels and the data are not next to each other so we must select non‐ consecutive ranges
Select cells A4 through A8.
Next, hold down on the Control (Ctrl) key and select cells E4 through E8.
This is how you select non‐consecutive (not next to each other) cells or cell ranges. Release the mouse and Ctrl key
Click the Insert tab and then click the Insert Pie or Doughnut Chart button. The icon is a mostly blue circle in the Charts group.
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 8 of 12
Under the 2‐D section, select the 1st Pie chart button (Pie tool tip) ‐ top‐left corner or the chart type gallery.
This creates a pie chart depicting the net profits of each sales rep. Notice that Excel automatically created a chart title indicating the type of data being graphed and a legend indicating the sales reps being represented by each piece of the pie.
Add the data values to the chart by right‐clicking within the pie chart (the colored wedges) and clicking the Add Data Labels option.
By default, the chart is placed next to the data it represents. Bold the Data Labels
Click one of the Data Labels. They will all be selected as shown by a rectangle around each of them.
Click Bold. Deselect (click in a white area of the chart). Move the chart so that the top of the chart window to the top of row 21 and the left side of the
chart is at the far left of the worksheet (column A).
To move the chart, click in the white area outside the pie area (but still within the chart window) and, with the left mouse button still pressed down, drag it to the desired location. (click and drag)
To make a chart wider (or thinner or taller or shorter), position your mouse over the middle of the right edge (designated by four dots) until the cursor is a white horizontal double‐arrowed then click and drag to the right.
Deselect the chart area (click in a blank cell to the right).
Save your worksheet.
19. Sometimes it is helpful to place textboxes on a worksheet to comment upon or explain specific data. Mrs. Obama wants to note that George Bush’s relatively low sales total is due to his being out of work for six weeks during the quarter.
Insert a Textbox in range H4:J7
Click the Insert tab. Click the Shapes button within the Illustrations group.
Click the TextBox button (near the right side of the Ribbon for the lnsert tab).
Move the cursor to the top left corner of cell H4.
Create a textbox by clicking and holding the left mouse button down and dragging the cursor to the bottom right corner of J7.
Release the mouse button. The textbox should now cover the range H4 through J7.
Click inside the top left corner inside the textbox and type the following text: George missed six weeks this quarter due to a back injury.
Textbox
Arrow
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 9 of 12
Insert an arrow starting at the textbox and pointing to the value $1,396
Click the Insert tab.
Click the Shapes button (near the left side of the Ribbon in the Illustrations group).
Within the Lines section OR the Recently Used Shapes section, click the second line style (Arrow tool tip) from the left. It has an arrow pointing downward. (see figure on previous page)
Move the cursor to the far right end of G5 (so that it is touching the far left side of the textbox).
Click and hold the left mouse button down and dragging the cursor across cell G5 until it touches the far left side of cell G5, then release the mouse button. There should now be a line with an arrow pointing from the textbox to cell F5. The arrow should be next to the value $1,396.
Save your worksheet.
20. One of the primary advantages of a spreadsheet is the ability to conduct what‐if analysis. If a cell’s value is determined by a formula (such as Gross Profit), then whenever the data of a cell within that formula is modified (such as profit margin). Excel automatically recalculates the new value of the formula and places the new value in the cell containing the formula.
For example, Mrs. Obama wants to know the amount that total gross profits, net profits, and commissions for her territory would increase if the profit margin was increased to 37%.
Note the current worksheet values for cells C10, E10 and F10 as stated below: Gross Profits: $110,775 Net Profits: $106,285 Commissions: $10,629
Also note that the data values on the pie chart match the data values in the Net Profits column.
Now, change the Profit Margin in B16 from 35% to 37%
Click in B16. Type 37 and press Enter.
Note the new values in cells C10, E10 and F10 are: Gross Profits: $117,105 Net Profits: $112,615 Commissions: $11,262
Because all three of the sales categories listed above are calculated with formulas that relate, in some way, to profit margin and sales, Excel will recalculate each of these values whenever a profit margin or sales value is changed. In this case, increasing the profit margin by 2% would increase gross profits and net profits by approximately $6000 each and commissions by about $600 (assuming Sales stayed the same).
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 10 of 12
Also note that the values within the pie chart also changed to reflect the new values in the spreadsheet.
Save your worksheet.
21. Add Protection to the worksheet to prevent accidental or deliberate changes to the data.
Anyone who opens your workbook file can easily change the information that it contains. Sometimes you may even make accidental changes if you bump the keys on the keyboard. Applying worksheet protection prevents the worksheet contents from being altered.
It does not prevent users from viewing information or formulas. It should not be considered as a method for securing confidential information.
Protect the Worksheet.
To protect your worksheet, click the Review tab
Click the Protect Sheet button in the Changes group.
Do NOT enter a password and do not make any changes to the default settings.
Click OK ‐ We will use all of the default settings at this time.
The Protect Sheet dialog box is shown to the right. Only use the password feature if you want to prevent others from unprotecting your sheet. Change the checkbox settings when you want to allow users to make specific types of changes to your workbook. The default settings allow users to select locked and unlocked cells. This means that they can click in a cell, but they cannot change the contents.
Try typing in cell E2. You should receive the message above
Notice that the Review Ribbon now shows an Unprotect Sheet button instead of a Protect Sheet button
UNProtect the Worksheet.
Click the Unprotect Sheet button in the Changes group on the Review tab.
22. Unlock a cell to allow the user to type in a protected worksheet.
When you protect a worksheet all cells in a worksheet are locked by default. When worksheet protection is applied, you can’t type anywhere! Fortunately, Excel allows you to unlock some of the cells before you add worksheet protection. You will unlock cell B16 so that users can change the profit margin.
Unprotect cell B16 (The worksheet MUST be unprotected first, see the instructions about 2 inches above.)
Click in cell B16. Click on Allow Users to Edit Ranges. It is found on the Review tab in the Changes group.
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 11 of 12
(1) (2)
If this option is grayed out and not available, make sure that you unprotected your worksheet.
Click on the New button.
Verify that Refers to cells: =$B$16 and then press the OK button.
Verify that Title – Range1 and Refers to cells ‐ $B$16
Click the Protect Sheet… button on the Allow Users to Edit Ranges dialog box.
At the Protect Sheet Dialog box, click the OK button. (Do not enter a password.)
Test cell B16 to see if it allows changes. Test the other cells.
Save your worksheet.
Ensure the value for Profit Margin is 37% (cell B16) before you save
23. Preview your work and change Page Orientation
Before printing, verify that cell B16 displays 37%.
First, let’s view what the worksheet would look like if you printed it using Print Preview. We will add Print Preview and Print button to the Quick Access Toolbar to make it easy to use this feature at any time.
Add Print Preview to the Quick Access Toolbar
Click the Customize Quick Access Toolbar (1) ‐ down arrow to the right of the Quick Access Toolbar located above the Page Layout tab (see figure below)
Click Print Preview and Print command in the menu. This will add the Print Preview and Print button (2) to the Quick Access Toolbar
Close the Customize Quick Access Toolbar menu.
Review your worksheet with Print Preview
Click the Print Preview and Print button (2) (See the figure on the previous page).
Backstage will open (you can also open Backstage by clicking the File tab)
You will see a small image of what your spreadsheet will look like if printed.
Mt. San Antonio College CISB11 LAB: Excel Spreadsheet Assignment Part 2
Page 12 of 12
Notice that, in the default portrait mode, the textbox explaining George Bush’s low numbers is cut off. The portion not displayed is placed on a second page. Look at the bottom of the Excel window to see the page controls: 1 of 2 is shown. In order to view the entire worksheet, we need to print it in landscape mode.
By default, the spreadsheet is Portrait Orientation.
Change the Orientation to Landscape
Click the down arrow to the right of the Portrait Orientation area (see below image).
Change to Landscape Orientation.
The entire worksheet will now appear on the printout. (Note: The bottom of the chart frame may not be visible.)
Save
24. Wrap up
Save your work
Click File tab, then click Close (to close the document). At the dialog box, click Save to save the changes you made to display the formulas.
Click Exit (to close Excel).
If you used a flash drive for this assignment, make sure you remove your flash drive before leaving.
Completed Worksheet