#Exp22 Excel Ch09 HOE CircleCity
#Excel Chapter 9 Hands-On Exercise – Circle City Sporting Goods
You are the regional manager of Circle City Sporting Goods (CCSG), a retailer that has locations in Indianapolis, Bloomington, and South Bend. Each store manager gathers monthly data for every department and prepares a quarterly worksheet. The worksheets are identical to help you consolidate sales data for all three locations. You want to review sales data for the past fiscal year. Before consolidating data, you will format the worksheets, copy data to a summary sheet, and insert hyperlinks to the individual quarterly sheets. Later, you will consolidate each store’s data into a regional workbook. You will use auditing tools to identify errors and add validation to ensure accurate data entry. Finally, you will use tools to protect data, worksheets, and workbooks against unauthorized access, and then mark the workbook as final.
Start Excel. Download and open the file named Exp22_Excel_Ch09_HOE_CircleCity.xlsx. Grader has automatically added your last name to the beginning of the filename.
Note: When you open the file, Excel prompts you to fix a circular error. Click or press OK and continue.
The main title and row headings are displayed only in the Qtr1 worksheet. You want to fill in the title and row headings for the other three quarterly worksheets as well as the yearly worksheet.
Group the Qtr1, Qtr2, Qtr3, Qtr4, and Year worksheets. Select cell A1 in the the Qtr1 worksheet. Fill the formatting and content across the grouped worksheets. Fill the range A2:A10 across the grouped worksheets. Ungroup the worksheets.
You want to insert monthly and department totals for the quarterly worksheets.
Group the four quarterly worksheets. Select the range B3:E11 and use AutoSum.
Now you want to format values in the quarterly worksheets.
With the four quarterly worksheets grouped, apply Accounting Number Format to the ranges B3:E3 and B11:E11. Select the range B11:E11 and apply the Total cell style. Ungroup the worksheets.
The Year worksheet contains summary data. You will insert a hyperlink to each quarterly worksheet.
On the Year worksheet, in cell B2, insert a hyperlink to the range E2:E11 in the Qtr1 worksheet with the ScreenTip text Qtr 1 Totals.
In cell C2, insert a hyperlink to the range E2:E11 in the Qtr2 worksheet with the ScreenTip text Qtr 2 Totals.
In cell D2, insert a hyperlink to the range E2:E11 in the Qtr3 in the worksheet with the ScreenTip text Qtr 3 Totals.
In cell E2, insert a hyperlink to the range E2:E11 in the Qtr4 in the worksheet with the ScreenTip text Qtr 4 Totals. Test the hyperlinks to ensure the work correctly.
You decide to hide some worksheets. After hiding three worksheets, you decide to display two worksheets again to continue working on them.
Select and hide the Year, Consolidated, and Future worksheets. Then unhide the Year and Consolidated worksheets. (The Future worksheet should still be hidden.)
Now you are ready to insert a formula with a reference to the Qtr1 department totals.
Display the Year worksheet. In cell B3, insert a formula with a 3-D reference to cell E3 in the Qtr1 worksheet. Copy the formula to the range B4:B11.
Now you are ready to insert a formula with a reference to the Qtr2 department totals.
In cell C3 in the Year worksheet, insert a formula with a 3-D reference to cell E3 in the Qtr2 worksheet. Copy the formula to the range C4:C11.
Now you are ready to insert a formula with a reference to the Qtr3 and Qtr4 department totals.
In cell D3 in the Year worksheet, insert a formula with a 3-D reference to cell E3 in the Qtr3 worksheet. In cell E3, insert a formula with a 3-D reference to cell E3 in the Qtr4 worksheet. Copy the formulas in the range D3:E3 to the range D4:E11.
Column F in the Year worksheet is designed to display department totals.
In cell F3 in the Year worksheet, insert the SUM function with a 3-D reference to calculate the yearly total for the Athletic Apparel department using cell E3 in the four quarterly worksheets. Copy the formula to the range F4:F11.
You are ready to format the values in the Year worksheet.
Apply Accounting Number Format to the ranges B3:F3 and B11:F11. Apply Comma Style to the range B4:F10. Apply the Total cell style to the range B11:F11.
Although you used 3-D references to pull in quarterly sales for each department, you want to create a comprehensive worksheet displaying monthly sales for the entire year. You will use the Consolidate tool.
Display the Consolidated worksheet. In cell A1, use the Consolidate tool to select and add the range A2:E11 in the Qtr1 sheet, Qtr2, Qtr3, and Qtr4 worksheets. Use the top row and left column labels. Do not create links.
Now you are ready to format the consolidated data.
With the consolidated data selected, apply AutoFit Column Width. Select the range B1:N1 and apply bold and center horizontal alignment. Select the range B10:N10 and apply the Total cell style.
The CCSG Totals worksheet contains totals from stores in two cities. However, you need to link to data in another workbook to obtain the Bloomington values.
Open the Exp22_Excel_Ch09_HOE_Bloomington.xlsx workbook. Go back to the Exp22_Excel_Ch09_HOE_CircleCity.xlsx workbook. In cell C3 on the CCSG Totals worksheet, insert a link to the Athletic Apparel department total (cell B3). Change $B$3 to B3 in the formula. Use AutoFill to copy the formula from cell C3 to the range C4:C10 using Fill Without Formatting. Close the Bloomington workbook.
Your workbook contains FW2, a worksheet for the Qtr2 data for the Fort Wayne location. The worksheet contains a formula that is missing an adjacent cell. Now you want to find and correct it.
On the FW2 worksheet, check for errors and update cell E3 to include adjacent cells.
The FW2 worksheet also contains a circular error.
Use the error-detection tool to find the cell containing a circular reference. Then correct the formula.
On the FW3 worksheet, you want to insert the AVERAGEIF to calculate the average monthly revenue for departments that contain the word ball in the first column. However, the function will return #DIV/0! errors for rows that do not contain ball. Therefore, you will nest the AVERAGEIF function within the IFERROR function to avoid the error.
Display the FW3 worksheet. In cell F3, insert an IFERROR function with a nested AVERAGEIF function with a relative reference to cell A3, the criteria *ball, and the average range B3:D3. The second argument in the IFERROR function should be “-“. Copy the function to the range F4:F10.
You want to create a validation rule to prevent the user from entering too many seats sold. For now, you will create a validation for the Exercise Equipment values on the FW3 worksheet.
Select the range B4:D4 on the FW3 worksheet, create a validation rule to allow decimal values less than or equal to 500000. Enter the input message title Sales Data and the input message Enter the sales amount for the respective month. (including the period). Use the Warning alert with the error alert title Potentially Invalid Data and the error alert message The projected maximum is $500,000. However, if actual sales are higher, enter the actual value. (including the period). Test the data validation by attempting to enter 600000 in cell D4 and click or press Yes. Use the Data Validation arrow to circle invalid data.
You want to unlock data-entry cells so that the user can change the number of seats sold in the worksheets.
Group the Qtr1, Qtr2, Qtr3, and Qtr4 worksheets. Select the range B3:D10 and unlock these cells. Ungroup the worksheets.
Now that you unlocked data-entry cells, you are ready to protect the Qtr1 worksheet to prevent users from changing data in other cells.
Protect the Qtr1 worksheet using the default allowances with the password Expl0r!ng.
Mark the workbook as final.
Note: Mark as Final is not available in Excel for Mac. Instead, use Always Open Read-Only on the Review tab.
Close Exp22_Excel_Ch09_HOE_CircleCity.xlsx. Exit Excel. Submit the file as directed.