Excel Chapter 7 Prepare Case - Spa

Step

Instructions

Points Possible

1

Start Excel. Download and open the Excel file named Excel_CH07_Prepare_Spa.xlsx. Your name will automatically be added to the start of the file name. Save the file to a location where you are storing your data files.

0

2

Grouping worksheets allows you to perform certain tasks once and have those tasks affect the same cells for all worksheets in the group. You want to group the four therapists worksheets to add formatting and formulas which will make the workbook more useful. Group the Istas, Mault, and Niese worksheets. Change the tab color to Blue from the Standard Color row.

0.3

3

With grouped worksheets, you can enter text on all sheets within the grouping at one time. With the worksheets still grouped, in cell A25, type Total

0.3

4

With the worksheets grouped, you can also enter formulas and functions on all sheets within the grouping at one time. With the worksheets still grouped, in cell E7, type =C7*D7 Copy the formula through cell range E7:E24. In F7, type =E7 In F8, type =F7+E8 Copy the formula through cell range F8:F24. Ungroup the worksheets.

0.9

5

Fill Across Worksheets is a command that can be used to copy cell contents, formats, or both contents and formats to worksheets in a group. The source and destination worksheets must all be included in the group. On the Istas worksheet, in cells C25:E25, use the SUM function to total the cell range C7:E24. Group the Istas, Mault, and Neise sheets to copy the contents across worksheets to the Mault and Niese worksheets.

0.9

6

By grouping worksheets, you can apply cell formatting to multiple worksheets at once. If necessary, group the Istas through Niese worksheets. Apply the cell style Accent 5 to the cell range A6:F6. Apply the Total cell style to the cell range C25:E25. Ungroup the worksheets.

1.2

7

Filling formats across worksheets is like filling content across worksheets, but only the formatting is applied. On the Istas worksheet, in cells E7:F24, fill the formats across the Mault and Niese worksheets. Ungroup the worksheets.

0.6

8

Three-dimensional references, or 3-D references, allow formulas and functions to use data from cells and cell ranges across worksheets. You will delete the date entered in cell A4 of the three therapists’ worksheets. Then you will change the date, so it is entered on the Istas worksheet and the other two worksheets will have a 3-D reference to it. Delete the dates in cell A4 on the Mault and Niese worksheets. Change the dates so the Mault and Niese worksheets have a 3-D reference to cell A4 of the Istas worksheet.

0.8

9

A 3-D named range references the same cell or range of cells across multiple worksheets in a workbook. You will create a 3-D named range for the Spa workbook. Create a 3-D name for cell E25 on the Istas:Niese worksheets. Name the range TotalCharges3D

0

10

When you have data on multiple worksheets and want to consolidate that data into a summary worksheet, you can use a 3-D formula. A 3-D formula is a formula that references the same cell or range of cells across multiple worksheets in a workbook. In the Spa workbook, you will summarize the number of hours and total charges for all three therapists on a new summary worksheet. On the HoursSummary worksheet, in cell B7, create a 3-D formula that totals cell C7 of the Istas through Niese worksheets. Copy the formula through cell B24. On the HoursSummary worksheet, in cell C7, create a 3-D formula that totals cell E7 of the Istas through Niese worksheets. Copy the formula through cell C24.

1

11

Consolidate by position can be used to create a summary worksheet when the source worksheets all have an identical structure. You will consolidate data by position on the three therapist worksheets. On the ChargesSummary worksheet, in cell B7, consolidate data using the SUM function for the data in cell E7:F24 of the Istas, Mault, and Niese worksheets.

0.5

12

Consolidating by category is more flexible than consolidating by position. When consolidating by category, Excel examines row and/or column headings to determine which cells should contribute to a given calculation. Data does not need to be in the same relative position between and among worksheets. In the Spa workbook, you will consolidate therapists’ sales by service rather than by time. On the ServiceSummary worksheet, in cell A6, consolidate data for cells B6:E24 on the Istas, Mault, and Niese worksheets (in that order). Use the top row and left column as labels. In cell A6, type Service Autofit the column width of columns A:D. In cell D20, type =SUM(TotalCharges3D)

1.1

13

A data consolidation may contain links to source data that will include the cell references from other worksheets that contributed to the consolidated data result. The source cell reference details are placed into hidden rows that can be viewed if necessary. You will create a linked consolidation of sales by appointment time. On the ChargesSummaryLinked worksheet, in cell A6, consolidate data for cells A6:F24 on the Istas, Mault, and Niese worksheets (in that order) using top row and left column as labels and create links to source data. AutoFit Column Width of columns A:G. Format A10:A78 with the Time, 1:30 PM format.

1

14

When you need data from a different workbook, the advantage to referencing that data at its source rather than copying it into your workbook is that when the source data is changed, your workbook can also be changed to reflect the most current data. You will delete the PriceList worksheet from the Excel_CH07_Prepare_Spa workbook. Then you will link to the Excel_CH07_Prepare_SpaPrices workbook to use the pricing data from that workbook. By linking to the Excel_CH07_Prepare_SpaPrices workbook, you will correct the errors that occurred by deleting the PriceList worksheet in the Excel_CH07_Prepare_Spa workbook. In the Excel_CH07_Prepare_Spa workbook, delete the PriceList worksheet. Click the Mault worksheet and note the error in the formulas.

0.2

15

With the Excel_CH07_Prepare_Spa workbook still open, open the Excel_CH07_Prepare_SpaPrices.xlsx workbook. Save the workbook as Excel_CH07_Prepare_SpaPrices_LastFirst using your last and first name.

0

16

Arrange the Spa and SpaPrices workbooks side by side. On the Excel_CH07_Prepare_Spa workbook, group the Istas through Niese worksheets. In cell D7, edit the formula to remove the #REF! in the VLOOKUP function by selecting cell range A2:D26 from the Excel_CH07_Prepare_SpaPrices workbook. Copy the formula in D7 through cell D24. Ungroup the worksheets in the Excel_CH07_Prepare_Spa workbook. Select the Excel_CH07_Prepare_SpaPrices workbook. In cell C17, enter 120 Save and close the Excel_Ch07_Prepare_SpaPrices workbook.

1.2

17

Save and close the Excel_CH07_Prepare_Spa workbook. Exit Excel. Submit the Excel_CH07_Prepare_Spa.xlsx workbook as directed.

0

Total Points

10