Spreadsheets

[removed]

Week 5

Hands On 02 – Instructions

To Create a 2-D Line Chart​

1. Select the 0 to 5 years range of cells.

2. Click the Insert on the ribbon to display the Insert tab​.

3. Click the “Insert Line or Area Chart” button to display the Insert Line and Area Chart gallery​.

4. Click “Line with Markers” in the 2-D Line area to insert a 2-D line chart with data markers​.

5. Click the Move Chart button to display the Move Chart dialog box​.

6. Click New sheet to select the option button​.

7. If necessary, double-click the default text in the New sheet text box to select the text, and then type “0 to 5 Years”

8. Click OK to move the chart to a new sheet.

9. Change the Chart Title to “Total Revenue”.

Adding a Trendline to a Chart​

1. Click the “Add Chart Element” button to display the Add Chart Element menu​.

2. Point to the Trendline to display the Trendline gallery​.

3. Click “More Trendline Options” to display the Format Trendline pane​.

4. If necessary, click the Trendline Options button​.

5. Click the Series1 trendline type​.

6. Click the desired options in the Format Trendline pane​.

7. Click the Close button to close the pane.

Changing the Format of a Data Point​

1. Click the desired data point twice to select the single point. Do not double-click. ​

2. Right-click the selected data point to display the shortcut menu​.

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

4. Click the “Fill & Line” button, then click Solid line option​.

5. Close the Format Data Point pane and click the Save button to save the workbook.

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

Part 5

Course Objectives:

· CO10: Conduct data analysis with trendlines, PivotTables, and PivotCharts, including formatting, filtering, calculated fields, and slicers for dynamic data visualization and reporting.

Estimated time to Complete:  20 minutes

Description:

Students will learn about:

· Using trendlines to analyze data in a worksheet.

Required Resources:

· Access to Microsoft Excel.

Deliverable:

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

Setup:

Creating a Blank PivotTable​

1. Click the desired cell containing the data for the PivotTable​.

2. Click the PivotTable button on the Insert menu to display the Create PivotTable dialog box​.

3. Click OK to create a blank PivotTable report on a new worksheet and display the Field List​.

Adding Data to the PivotTable​

1. Drag the desired field from the “Choose fields to add to report” area to the desired locations (rows or columns)​.

2. Drag the Employee Experience and City to the Rows.

3. Drag the Sum of 2020 Revenue to the Sum of Values.

4. Drag the Sum of 2021 Revenue to the Sum of Values.

Changing the Layout of a PivotTable​

1. Click the Report Layout button on the Design tab to display the Report Layout menu​.

2. Click Show in Tabular Form.

Filtering a PivotTable Report Using a Report Filter​

1. In the PivotTable Fields menu, drag the City field from the “Choose fields to add to report” area to the Filters area in the PivotTable​.

2. Click the filter button to display the filter menu​.

3. Click to select the Duarte criterion​.

4. Click OK to update the display.

5. Capture a screen shot of the spreadsheet and upload it to Canvas.

Removing a Report Filter from a PivotTable Report​

1. Click the filter button and then click the (All) check box to include all criteria in the PivotTable report​.

2. Click OK​

3. Drag the desired button out Filters area to remove the field from the report.

Filtering a PivotTable Report Using the Row Label Filter​

1. Drag the City field to the Rows area.

2. Click the filter button to display the filter menu for the desired field​.

3. Select “More than 10 Years” check boxes on the filter menu​.

4. Click OK to update the display.

5. Capture a screen shot of the spreadsheet and upload it to Canvas.

Expanding and Collapse Categories​

1. Click the Collapse button to collapse the “0 to 5 Years” selection​.

2. Right-click the desired cell to display the shortcut menu and then point to Expand/Collapse to display the Expand/Collapse submenu​.

3. Click the desired settings​.

Updating a PivotTable​

1. Modify the contents of the desired cell on the desired sheet tab​.

2. Click the desired PivotTable sheet tab to make it active​.

3. Click any cell in the PivotTable report to make it active​.

4. Click the Refresh button in the PivotTable Analyze menu to update the PivotTable report.

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

Part 6

Course Objectives:

· CO10: Conduct data analysis with trendlines, PivotTables, and PivotCharts, including formatting,  filtering, calculated fields, and slicers for dynamic data visualization and reporting.

Estimated time to Complete:  20 minutes 

Description:

Students will learn about:

· Analyzing worksheet data using PivotTable and PivotChart reports.

· Creating calculated fields.

· Creating slicers to filter PivotTable and PivotChart reports.

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:

Creating a PivotChart Report from an Existing PivotTable Report​

1. Select a cell in the PivotTable report​.

2. Click the Field List button to display the Field List​.

3. Click the PivotChart button to display the Insert Chart dialog box​.

4. Click the Pie chart type​.

5. Click OK to add the chart to the worksheet.

Changing the PivotChart Type and Reset Chart Elements​

1. Click the chart to select the data series​.

2. Right-click to display the shortcut menu​.

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

4. Click the Chart Title chart type​.

5. Click the Chart Elements arrow to display the Chart Elements menu​.

6. Select the option to have the Chart Title at the top. Change the title to “Revenue”.

7. Click the Data Label chart type​.

8. Select the option to have the Data Labels inside the pie chart slices.

9. Close the Format Wall pane.

To Create a Calculated Filed to a PivotTable Report​

1. If necessary, click the PivotTable to make it active​.

2. Click the “Fields, Items, & Sets” button in the PivotTable Analyze menu to display the Fields, Items, & Sets menu​.

3. Click Calculated Field to display the Insert Calculated Field dialog box.​

4. Enter the name “Total Revenue”, and formula “2020 Revenue + 2021 Revenue” and then click the Add button to add the calculated field to the Fields list​.

5. Click OK to close the dialog box.

Uploading completed worksheet

1. Save the workbook to your local drive.