Excel Spreadsheet

Week 3 – hands-On 2

Part 4

Course Objectives:

· CO6: Perform advanced data analysis and consolidation, including 3-D references, workbook linking, formula checking, and multi-sheet operations, while leveraging Excel’s visualization and layout tools like pie charts and multi-workbook views

Estimated time to Complete:  20 minutes 

Description:

The student will learn about:

· Formatting a consolidated worksheet.

· Filling using a linear series.

· Using date, time and rounding functions.

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet to the Canvas module.

Setup:

Access to Microsoft Excel.

Lab Steps:

To Name and Print Sections of a Worksheet​

1. Create a new spreadsheet with the following data:

M&S Provisions
Consolidated Expense Worksheet
Expense Category	2020	2021 Projected % Change	2021	2022 Projected % Change	2022	Average % Changed
Payroll
Benefits
Fuel
Storage
Event Fees
Maintenance
Advertising
Administration

 

Create a Linear Series​

1. Enter the values 1 and 2 in the 2020 column under next to Payroll and Benefits.​

2. Select the two cells in the range.

3. Drag the AutoFill handle on the lower right of the cell to auto populate the numbers for the Expense Category.

Using the ROUND Function​

1. Enter the following values into the spreadsheet:

M&S Provisions
Consolidated Expense Worksheet
Expense Category	2020	2021 Projected % Change	2021	2022 Projected % Change	2022	Average  % Changed
Payroll	1	3.00%	1.03	4.00%
Benefits	2	6.10%	2.12	6.00%
Fuel	3	9.20%	3.28	8.00%
Storage	4	12.30%	4.49	10.00%
Event Fees	5	15.40%	5.77	12.00%
Maintenance	6	18.50%	7.11	14.00%
Advertising	7	21.60%	8.51	16.00%
Administration	8	24.70%	9.98	18.00%

 

2. Select the cell for Payroll Average % Changed. Type =ROUND formula and then click the Enter box in the formula bar to display the formula and the resulting value in the selected cell​.

3. Drag the fill handle on the lower right of the cell to calculate the Average % Changed for the remaining expense categories.

Create a Custom Format Code​

1. Select the Average % Changed range of cells and right-click any of the selected ranges to display a shortcut menu, and then click Format Cells to display the Format Cells dialog box​.

2. If necessary, click the Number tab and then click Custom in the Category list​.

3. Delete the text in the Type box, and then enter the format code ​“0.00%”.

4. Click OK to display the numbers using the custom format code.

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

Part 5

Course Objectives:

· CO6: Perform advanced data analysis and consolidation, including 3-D references, workbook  linking, formula checking, and multi-sheet operations, while leveraging Excel’s visualization and  layout tools like pie charts and multi-workbook views

Estimated time to Complete:  20 minutes

Description:

The student will learn to:

· Apply custom format code to cells.

· Create new cell styles.

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet to the Canvas module.

Setup:

Access to Microsoft Excel.

Lab Steps:

Create a New Cell Style​

1. On the Consolidated Expense Worksheet, click the Cell Styles button to display the Cell Styles gallery​.

2. Click “New Cell Style” in the Cell Styles gallery to display the Style dialog box​.

3. Type the name “Expense Category” for the new style​.

4. Click the Format button to display the Format Cells dialog box and select Bold font and Blue color formats​.

5. Click OK to close the Format Cells dialog box​.

6. Click OK to create the new style.

 

Apply a New Style​

1. Select the Expense Category cells and then click the Cell Styles button to display the Cell Styles gallery​.

2. Click the name “Expense Category” to assign the style to the selected cells.

Adding a Worksheet to a Workbook​

1. From the Consolidated Expense Worksheet, click the New sheet button at the bottom of the window to add a new worksheet to a workbook​.

Copy and Paste from One Worksheet to Another​

1. Click the sheet tab for the Consolidated Expense Worksheet.​

2. Revise column B to contain the following data:

Expense Category	2020
Payroll	22,688.40
Benefits	32,648.54
Fuel	9,778.08
Storage	5489.2
Event Fees	7899.65
Maintenance	13,057.40
Advertising	10,942.91
Administration	7,500.00

 

3. Click the Select All button to select the entire worksheet and then click the Copy button to copy the contents of the worksheet​.

4. Click the sheet tab for the sheet where the data is being copied to​.

5. Press ENTER to copy the data from the Office Clipboard to the selected sheet.

Reference Cells Across Worksheets​

1. Select the 2020 expenses and then click the AutoSum button to display the SUM function​.

2. Click the desired sheet tab to display the worksheet, and then click the same cell to select the first portion of the argument for the SUM function​.

3. SHIFT+click a new desired sheet tab to select the ending range of the argument for the SUM function​.

4. Click the Enter button in the formula bar to enter the SUM function with the 3-D references in the selected cell.

5. Capture a screen shot of the cell formula on the second spreadsheet and upload

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

Part 6

Course Objectives:

· CO6: Perform advanced data analysis and consolidation, including 3-D references, workbook  linking, formula checking, and multi-sheet operations, while leveraging Excel’s visualization and  layout tools like pie charts and multi-workbook views

Estimated time to Complete:  20 minutes

Description:

The student will learn to:

· Format 3-D pie charts with exploded slices and leader lines.

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet to the Canvas module.

Setup:

Access to Microsoft Excel.

Lab Steps:

Inserting a 3-D Pie Chart on a Worksheet​

1. Select the Expense Category and 2020 data range for the pie chart​.

2. Display the Insert tab, click the “Insert Pie or Doughnut Chart” button and then click 3-D in the Insert Pie or Doughnut Chart gallery to create the desired chart type​

3. Click the chart title, select the text, and then type “Expense Category” to change the chart title​.

4. Click the Chart Styles button to display the Chart styles gallery, and select a style.

Moving a Chart on the Same Worksheet​

1. Point to the border of the chart; the pointer will change to a four-headed arrow​.

2. Drag the chart to the right of the spreadsheet data.

Resize a Chart​

1. SHIFT+drag the lower-right resizing handle of the chart until the chart is the desired size​.

2. If necessary, zoom out until you can see the entire chart.

Exploding a Slice​

1. Click a pie slice twice (do not double-click)​.

2. Right-click the Payroll slice to display a shortcut menu​.

3. Click “Format Data Point” on the shortcut menu to open the Format Data Point pane​.

4. Drag the Point Explosion slider to the desired size to set how far the slice in the 3-D pie chart should be offset from the rest of the chart.

Rotating the 3-D Pie Chart​

1. Right-click the chart to display a shortcut menu, and then click “3-D Rotation” on the shortcut menu to open the Format Chart Area pane​.

2. Click the X Rotation up arrow (Format Chart Area pane) to the desired X rotation to rotate the chart

Formatting Data Labels​

1. Click the Chart Elements button to display the Chart Elements gallery​.

2. Point to Data Labels and then click the Data Labels arrow to display the Data Labels submenu​.

3. Click More Options to display the Format Data Labels pane​.

4. In the Labels Options area, click to display check marks on the Values and Show Leader Lines areas​.

5. Scroll down in the pane and click the Number arrow to display the Number settings, then click the Category button and choose Currency as the number style​.

6. Click Text Options in the Format Data Labels pane to display text options​.

7. Click the Text Fill arrow to display the text fill options, and then select Solid fill as the desired fill​.

8. Click the Color button arrow to display the text color options, and then select a color.

Adding a Chart Border​

1. Click Format on the ribbon to display the Format tab​.

2. Click the Shape Outline button arrow to display the Shape Outline gallery​.

3. Click on the desired theme on the Theme Color area to apply a chart border

Uploading completed worksheet

1. Save the workbook to your local drive.