Excel Practice 5
Here is a video demonstrating the skills in this practice. Please note it does not exactly match the instructions:
Complete the following Practice Activity and submit your completed project.
For Excel Practice 5, we will use Excel to manage projected Revenue for Paradise Beach City, where you have just been hired as a Financial Analyst. Key skills in this practice are creating and editing pie charts and What If Analysis.
- Start Excel. Click Open, then Browse to where your data files are saved and open Excel Starter_ Excel_Practice5
- With the Excel Practice 5 open, Select File, Save As, Browse, and then navigate to your folder on your flash drive or other location where you save your files. Name the workbook as Yourlastname_Yourfirstname_Excel_Practice_5.
- To the left of column A, and above row 1, click in the square where the sideways triangle is to select the entire worksheet. AutoFit all cell contents and then click anywhere in the worksheet to deselect it. Ensure you can view all of the cell contents and ### are no longer visible.
- Select the range A1:C1, and merge and center. Apply cell style Title. Double-click on the line between the row headings 1 and 2 to Autofit the Row Height if it does not display all the way.
- Select the range A2:C2, and merge and center. Apply cell style Heading 4.
- Select the range A3:C3 and apply cell style 20% Accent1. Center the headings. Ignore and spelling errors, we will run this later on.
- In cell B12, clear the value in the cell if necessary. Use the AutoSum function to Sum the range B4:B11. You can use any of the methods previously learned to complete the AutoSum function. The formula in cell B12 should look like this =SUM(B4:B11)
- Using the Format Painter, apply the format in cell A3 to A12. To use the Format Painter, select cell A3 so that it is the Active Cell. Click the Format Painter one time to activate it. The Format Painter is located on the Home Tab, in the Clipboard group. With the Format Painter active, click in cell A12.
- In cell C4, type = click cell B4, type / and click cell B12. Make the cell reference B12 absolute in the formula. The formula in cell C4 should look like this = B4/$B$12. This formula will calculate the percentage of third quarter sales tax.
- Select cell C4, and format it as a percentage with two decimal places, and center the percentage. Use the fill handle to copy the formula through cell C11.
- Select the non-adjacent ranges A4:A11 and C4:C11. To select non-adjacent ranges, select the first range, next hold down the CTRL key, and then select the second range letting go of the left mouse button first and the the CTRL key.
- With the two ranges selected, insert a 3-D Pie Chart. This is located on the Insert Tab, Charts Group, then select the arrow next to Pie Charts and select 3-D Pie Chart.
- Move the chart to a new sheet named Projected Revenue. To create a chart sheet, ensure the chart is selected, on the Chart Tools Design Tab, in the Location Gr