Participant Guide AWS
EXCEL Participant Guide
{EXCEL – PARTICIPANT GUIDE} Excel – Participant Guide Overview Contents
Topic Functions Charts Format Print Pivot Tables Pivot Table Slicers
See Page 3 7 17 21 25 34
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
1
{EXCEL – PARTICIPANT GUIDE} Functions Excel Functions
A function is a pre-defined formula that performs a particular type of calculation. Functions in Excel can help you save time.
SUM
Complete the following steps to use AutoSum in Excel: Step 1
Action Select the cell you wish to hold your formula.
2
Click the AutoSum in your Home tab ribbon.
4
Excel will guess which cells you want to sum, by putting Marching Ants around them. Select the cells to be totaled.
5
Excel will automatically put your selection into the formula for you. Press the Enter key on your keyboard to display the total.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
2
{EXCEL – PARTICIPANT GUIDE} Functions, Continued Activity: Use AutoSum 4
Make sure the Marching Ants appear around C69:C72 and hit the Enter key.
Click AutoSum.
3
Click cell C73 to activate it.
2
1 Select the Demographic worksheet.
Repeat the previous steps for cells D73 & E73.
5
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
3
{EXCEL – PARTICIPANT GUIDE} Functions, Continued AVERAGE
Complete the following steps to use the AVERAGE function: Step 1
Action Select the cell you wish to hold your formula.
2
Click the AutoSum dropdown menu and select Average.
3
Select the cells to be averaged.
4
Excel will automatically put your selection into the formula for you. Press the Enter key on your keyboard to display the total.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
4
{EXCEL – PARTICIPANT GUIDE} Functions, Continued Activity: Use the Average Function 3
Click the AutoSum dropdown menu.
Click Average.
4
2 Select cell C47. Select the Large Claims worksheet.
1
5 Select the cell range E46:P46 and hit the Enter key.
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
5
{EXCEL – PARTICIPANT GUIDE} Charts Overview
Charts are used to display series of numeric data in a graphical format to make it easier to understand large quantities of data and the relationship between different series of data. To create a chart in Excel, you start by entering the numeric data for the chart on a worksheet. Then you can plot that data into a chart by selecting the chart type that you want to use on the Insert tab, in the Charts group.
Create a Simple Chart
Complete the following steps to create a simple chart in Excel: Step 1
Action Select all of the data and all of the labels in the worksheet.
2
Click Insert tab and select a chart type from the ribbon.
3
A chart will appear in your worksheet. Notice the Chart Tools tabs that appear.
Use the Design tab to customize the chart style.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
6
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Activity: Create a Simple Chart Click the Insert tab.
3 Click the Column chart option.
Select all of the data and all of the labels in the worksheet.
2
Select Demographic worksheet.
5
4
1
Select the 2-D Clustered Column Chart from the drop down menu.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
7
{EXCEL – PARTICIPANT GUIDE} Charts, Continued
Select a style from the Chart Styles dropdown menu.
7
Click and drag the chart so that it appears under the data.
Change Chart Layout or Type
6
Complete the following steps to change the layout of a chart: Step 1 2 3
Action Click just inside the chart to activate it. Click the Design tab. Select either Chart Layouts to change the layout of the chart or select Change Chart Type to change the chart.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
8
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Activity: Change Chart Type and Layout Click the Design tab.
Click Change Chart Type.
2
3 2
Select the Line Chart with Markers.
Click just inside the chart to activate it.
Click OK.
1 2
4 2
5 2
Select the Chart Layout dropdown menu and select Layout 5. 6
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
9
{EXCEL – PARTICIPANT GUIDE} Charts, Continued
Select the Chart Style dropdown menu and select Style 2.
8
Double click the Chart Title to rename it.
7
Add Data to a Chart
Any alterations to the data in the cells used as source data for a chart will automatically reflect in the chart. But it you want to add new data, it must be added to the chart manually.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
10
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Add Data to a Chart, continued
Complete the following steps to add data to a chart: Step 1
Action Right click in the plot area of the chart and choose Select Data from the menu.
2
In the Select Data Source window, make sure the Chart date range field is active.
3
This will show you the data range the chart is pulling from. Click and drag your cursor over the entire range of cells you would like to include in your chart.
4
Select OK to save your changes.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
11
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Activity: Add Data to a Chart Before you start complete the following prep-steps (you’ve done these in previous activities): 1. Select cell F68 and type 2015 YTD Avg. 2. Enter the following values in the corresponding cells: a. F69 – 5472 b. F70 – 55741 c. F71 – 12150 d. F72 – 10520 3. Use AutoSum to total F69:F72 in cell F73. 4. Change the Chart Type to the Clustered Column Chart. Once you have completed these steps your chart should look like this:
Right click in the chart.
1 2
Click Select Data.
2
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
12
{EXCEL – PARTICIPANT GUIDE} Charts, Continued
4 Select range B68:F72.
Clear the data in the Chart data range field and leave it selected to activate it.
3
Click OK.
5
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
13
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Change a Chart’s Source Data
It is also possible to completely change the source data a chart is pulling from without having to create a new chart. Complete the following steps to change a chart’s source data: Step 1
2
3
4
Action Right click in the plot area of the chart and choose Select Data from the menu.
In the Select Data Source window, make sure the Chart date range field is active.
This will show you the data range the chart is pulling from. Click and drag your cursor over the new range of cells you would like to use for your chart.
This will automatically update your Select Data Source window. Select OK to save your changes.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
14
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Activity: Change a Chart’s Source Data
Right click in the chart.
1 2
Click Select Data.
2 2
Clear the data in the Chart data range field and leave it selected to activate it.
4 2
Select range B91:E95.
3 2
Click OK.
5 2
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
15
{EXCEL – PARTICIPANT GUIDE} Format Merge & Center Cells
Complete the following steps to merge and center cells: Step 1
2
Action Select the range of cells to merge.
Select Home tab>Merge and Center>Merge and Center.
The title moves to the top center of the cell.
Activity: Merge & Center Cells Click the Merge & Center dropdown menu> Merge & Center.
3
Select range A10:M10.
2
Click the Cover worksheet.
1 2
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
16
{EXCEL – PARTICIPANT GUIDE} Format, Continued Add Auto Header/Footer
Headers and footers are displayed at the top and the bottom of each printed page. If you are printing a long report it is very useful to add page numbers. Other items commonly added to page headers and footers include: A title The date/time that the report was printed The report author’s name The name of the Excel file that was used to generate the report The full path to the Excel file A company logo Copyright notices A distribution list or the security level of the document; i.e. Confidential Complete the following steps to add a header or footer to an Excel document: Step 1
2
Action Click the Page Layout view.
Click the Page Header area at the top of the screen or the Page Footer area at the bottom of the screen.
3
When you click in this area a new tab appears on the Ribbon. Click Header & Footer Tools> Design tab.
4
Click either Header or Footer, depending on what you are creating.
5
Select the desired option from the dropdown menu.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
17
{EXCEL – PARTICIPANT GUIDE} Format, Continued Add Custom Header/Footer
Custom headers allow you to combine your own text with report fields, such as page numbers. You are also able to add text to three different sections in the header and footer areas; left, right & center. Click on any of the three different sections in the header or footer section and use the Header & Footer Elements on the Design tab to create custom headers and footers.
Activity: Headers & Footers Click the Current Date option.
Click the Design tab.
5 2
Click the left header box and type Printed on:
Click the Demographic Worksheet.
1 2
4 2 3 2
Click the Page Layout view.
2 2
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
18
{EXCEL – PARTICIPANT GUIDE} Format, Continued Click the Current Time option.
7 2 Type at: after the &[Date] in the Header.
Click the Number of Pages option.
6 2
9 2
Click the left footer box and type Holmes Murphy Confidential
Click outside of the header box.
10
Click the middle footer box.
11
8 2
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
19
{EXCEL – PARTICIPANT GUIDE} Print Print Centered on Page
Complete the following steps to adjust page margins automatically: Step 1
Action Select the Page Layout tab>Margins>Custom Margins.
2
In the Page Setup window, under Center on page, select both the Horizontally and Vertically boxes.
3
Click OK.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
20
{EXCEL – PARTICIPANT GUIDE} Print, Continued Activity: Print Centered on Page
Select the Page Layout tab> Print Area> Set Print Area.
2
Select range A1:V95.
4 2
1 2
Click Margins> Custom Margins
Change to Page Layout view.
3 2
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
21
{EXCEL – PARTICIPANT GUIDE} Print, Continued
Check the boxes next to Horizontally and Vertically.
5 2
Click Ok.
6 2
Print Scaling
Complete the following steps to change the paper orientation when printing: Step 1 2 3 4
Action Select the Home tab. Select all worksheets. Return to the Print Preview page and select Landscape Orientation. Select the scaling drop down menu and select Fit Sheet on One Page.
You can now page through and see how the document will print. Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
22
{EXCEL – PARTICIPANT GUIDE} Print, Continued Activity: Print Scaling 1
Click the File tab.
2 2
Select Print Selection under Settings.
Click Print.
5 2
4 2
3 2
Notice that your print selection now appears on one page.
Select Fit Sheet on One Page under Settings.
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
23
{EXCEL – PARTICIPANT GUIDE} Pivot Tables Overview
A pivot table is a great way to sort and summarize a large amount of data into a usable format quickly. It is a very powerful tool to automatically sort, count, and total spreadsheet data without using formulas.
Why Use Pivot Tables?
Pivot tables allow you to change the way you look at your data without changing the original spreadsheet.
Pivot Table Requirements
Prior to creating a Pivot table the following must be true: The top row of data contains column headers. Each row of data is a record about a particular entity or transaction. Each column of data holds the same kind of information. There are no entirely blank rows in the data. There are no entirely blank columns in the data. If a column contains numbers, use a zero instead of a blank cell when you don’t have a value.
Create a Pivot Table
Complete the following steps to create a pivot table: Step 1 2
Action Click any single cell in the data. Click the Insert tab>PivotTable.
Excel will look at your spreadsheet and determine what row and columns you would like in your pivot table. It will look until it finds a blank row or column to create the pivot table. Note: By default, the Pivot Table will be created on a new worksheet that Excel inserts into the workbook. Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
24
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued Create a Pivot Table, continued
Step 3
Action Click OK, and leave the defaults in the “Create Pivot Table” dialog. Your Pivot able will look like the picture below:
4
Think of the information that you want to see from your data table and use the Field List to select and organize that data. Drag the desired fields to the drop zones to create the Pivot Table structure.
5
Note: If you turn on the check mark for a text field, the Pivot Table automatically adds that field to the “Row Labels” drop area. If you turn on a check mark for a numeric field, the Pivot Table automatically adds that field to the “Values” drop area. Continue to drag and drop the fields from above as needed. This will produce the answers for the first total above. Note: You need at least two fields in a Pivot Table report (a row or column field and a data field), but three or more fields (a row field, a column field, and a data field) are the usual. Continue to drag and drop the fields from above as needed. Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
25
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued
Activity: Create a Pivot Table Using the Demo Spreadsheet, create a Pivot table to get the following information: 1. The total cost of the LTD within each of the units. 1) The cost for male and female for each of the above. Click the Insert tab.
3
2 Click PivotTable.
1 Click anywhere in the data to activate.
Click OK.
6
4
Click and drag Plan and Unit from the Field List to the Row Labels drop zone. Note: Make sure Plan is above Unit.
Click and drag Sex from the Field List to the Column Labels drop zone.
5
Click and drag Ann Ben RT from the Field List to the Values drop zone.
7
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
26
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued
Activity: Adding More to a Pivot Table The more information you add to a pivot table the more your information will be split apart.
1
2 Doubleclick the Row Labels cell (A:4) and rename it Plans.
Click and drag Plan Typ from the Field List to the Row Labels drop zone. Note: Make sure Plan Typ is above Unit.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
27
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued Sort & Filter a Pivot Table
Once you have created a Pivot Table you can sort and filter the different elements of the table, as you would in a regular Excel worksheet. This will not change the integrity of the table and allows you to only see the piece you need. Complete the following steps to sort and filter columns: Step 1
Action Click on the arrow next to any of the column headings.
2
Click on the checkmark in front of Select All to take off all of the fields.
3
Click on the box in front of any of the values to see only those options.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
28
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued Sort & Filter a Pivot Table, continued
Step 4
Action Click OK. Your new pivot table will look like this.
Note: you can tell that your Pivot table information has been filtered by the Icon that is next to the column header, where the arrow was located.
Activity: Sort & Filter a Pivot Table
Click the arrow next to the Plans column header.
Click on the checkmark in front of Select All to take off all of the fields.
1
2
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
29
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued
Select LTD-ER & STDEX.
3
Click OK.
4 Activity: Change Date from Row to Column To change from row labels to column labels, drag the field to a different drop zone. This will change the look of your pivot table. It is giving you the same information just in a slightly different format.
Click the arrow next to the Plans column header.
2
Click on the checkmark in front of Select All select all of the fields.
Click OK.
4
3
1 Click and drag the Unit field from Row Labels to Column Labels. Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT
July 22, 2015
30
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued Change Value to a Percentage
Complete the following steps to see a percentage of the values within a pivot table: Step 1 2 3 4 5
Action Right click in Values area. Click on Summarize Data By Click More Options. Click the Show Values as tab in the Value Field Settings dialog box Click the arrow to scroll down and select % of total.
Note: To return the values to normal view, follow the same steps above, and then click Normal. This will turn off the custom calculations. Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
31
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued
Activity: Change Value to a Percentage 2
Click the Options tab. Click Summarize Values By>More Options
4
5
3
Click any cell in the Grand Total column.
Click the Show Values As tab.
1
Select % of Grand Total in the dropdown menu.
Click OK.
6
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
32
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers Overview
Slicers are easy-to-use filtering components that contain a set of buttons that enable you to quickly filter the data in a PivotTable report, without the need to open dropdown lists to find the items that you want to filter. Unlike report filter fields, slicers show which filters are in place when a multiple-item filter is applied.
Create a Pivot Table Slicer
Once you have created a Pivot Table, complete the following steps to create a slicer: Step 1 2
3
Action Click anywhere in your Pivot table. This will display your pivot table tools in your menu bar. On the options tab, in the sort & Filter group, click on Insert Slicer.
In the Insert Slicers dialog box, select the check box of the PivotTable fields for which you want to create a slicer.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
33
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers, Continued Create a Pivot Table Slicer, continued
Step 4
Action Click OK. A slicer is displayed for every field you selected.
5 6
Click and drag the slicers next to your pivot table. To adjust the look of a slicer, activate the slicer and select the Options tab. Adjust the number of columns and resize your slicers as desired.
5
In each slicer, click the items on which you want to filter. To select more than one item, hold down CTRL, and then click the items on which you want to filter.
6
To remove a slicer filter, click on the funnel in the corner with the x in the slicer box. This will remove any slicers you have set in this box.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
34
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers, Continued Activity: Add a Pivot Table Slicer 2
Click the Options tab. Click the Insert Slicer.
1
Check the boxes next to Plan Typ, Unit & Sex.
Click OK.
3
4 Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
35
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers, Continued
Activity: Format a Pivot Table Slicer Click the Options tab.
3 Click and drag each slicer to view.
Give the slicer 2 columns.
1
4
Select a slicer.
2
Select a Slicer Style.
5
6 Repeat for each slicer.
Click and drag each slicer to resize as needed.
7
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT
July 22, 2015
36
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers, Continued
8 Use your slicers to filter information containing: Females with LTD & NESTD plan types and AMSSV units. Note: Use the CTRL key to select multiple filters
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
37
{EXCEL – PARTICIPANT GUIDE} Excel – Participant Guide Overview Contents
Topic Functions Charts Format Print Pivot Tables Pivot Table Slicers
See Page 3 7 17 21 25 34
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
1
{EXCEL – PARTICIPANT GUIDE} Functions Excel Functions
A function is a pre-defined formula that performs a particular type of calculation. Functions in Excel can help you save time.
SUM
Complete the following steps to use AutoSum in Excel: Step 1
Action Select the cell you wish to hold your formula.
2
Click the AutoSum in your Home tab ribbon.
4
Excel will guess which cells you want to sum, by putting Marching Ants around them. Select the cells to be totaled.
5
Excel will automatically put your selection into the formula for you. Press the Enter key on your keyboard to display the total.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
2
{EXCEL – PARTICIPANT GUIDE} Functions, Continued Activity: Use AutoSum 4
Make sure the Marching Ants appear around C69:C72 and hit the Enter key.
Click AutoSum.
3
Click cell C73 to activate it.
2
1 Select the Demographic worksheet.
Repeat the previous steps for cells D73 & E73.
5
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
3
{EXCEL – PARTICIPANT GUIDE} Functions, Continued AVERAGE
Complete the following steps to use the AVERAGE function: Step 1
Action Select the cell you wish to hold your formula.
2
Click the AutoSum dropdown menu and select Average.
3
Select the cells to be averaged.
4
Excel will automatically put your selection into the formula for you. Press the Enter key on your keyboard to display the total.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
4
{EXCEL – PARTICIPANT GUIDE} Functions, Continued Activity: Use the Average Function 3
Click the AutoSum dropdown menu.
Click Average.
4
2 Select cell C47. Select the Large Claims worksheet.
1
5 Select the cell range E46:P46 and hit the Enter key.
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
5
{EXCEL – PARTICIPANT GUIDE} Charts Overview
Charts are used to display series of numeric data in a graphical format to make it easier to understand large quantities of data and the relationship between different series of data. To create a chart in Excel, you start by entering the numeric data for the chart on a worksheet. Then you can plot that data into a chart by selecting the chart type that you want to use on the Insert tab, in the Charts group.
Create a Simple Chart
Complete the following steps to create a simple chart in Excel: Step 1
Action Select all of the data and all of the labels in the worksheet.
2
Click Insert tab and select a chart type from the ribbon.
3
A chart will appear in your worksheet. Notice the Chart Tools tabs that appear.
Use the Design tab to customize the chart style.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
6
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Activity: Create a Simple Chart Click the Insert tab.
3 Click the Column chart option.
Select all of the data and all of the labels in the worksheet.
2
Select Demographic worksheet.
5
4
1
Select the 2-D Clustered Column Chart from the drop down menu.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
7
{EXCEL – PARTICIPANT GUIDE} Charts, Continued
Select a style from the Chart Styles dropdown menu.
7
Click and drag the chart so that it appears under the data.
Change Chart Layout or Type
6
Complete the following steps to change the layout of a chart: Step 1 2 3
Action Click just inside the chart to activate it. Click the Design tab. Select either Chart Layouts to change the layout of the chart or select Change Chart Type to change the chart.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
8
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Activity: Change Chart Type and Layout Click the Design tab.
Click Change Chart Type.
2
3 2
Select the Line Chart with Markers.
Click just inside the chart to activate it.
Click OK.
1 2
4 2
5 2
Select the Chart Layout dropdown menu and select Layout 5. 6
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
9
{EXCEL – PARTICIPANT GUIDE} Charts, Continued
Select the Chart Style dropdown menu and select Style 2.
8
Double click the Chart Title to rename it.
7
Add Data to a Chart
Any alterations to the data in the cells used as source data for a chart will automatically reflect in the chart. But it you want to add new data, it must be added to the chart manually.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
10
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Add Data to a Chart, continued
Complete the following steps to add data to a chart: Step 1
Action Right click in the plot area of the chart and choose Select Data from the menu.
2
In the Select Data Source window, make sure the Chart date range field is active.
3
This will show you the data range the chart is pulling from. Click and drag your cursor over the entire range of cells you would like to include in your chart.
4
Select OK to save your changes.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
11
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Activity: Add Data to a Chart Before you start complete the following prep-steps (you’ve done these in previous activities): 1. Select cell F68 and type 2015 YTD Avg. 2. Enter the following values in the corresponding cells: a. F69 – 5472 b. F70 – 55741 c. F71 – 12150 d. F72 – 10520 3. Use AutoSum to total F69:F72 in cell F73. 4. Change the Chart Type to the Clustered Column Chart. Once you have completed these steps your chart should look like this:
Right click in the chart.
1 2
Click Select Data.
2
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
12
{EXCEL – PARTICIPANT GUIDE} Charts, Continued
4 Select range B68:F72.
Clear the data in the Chart data range field and leave it selected to activate it.
3
Click OK.
5
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
13
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Change a Chart’s Source Data
It is also possible to completely change the source data a chart is pulling from without having to create a new chart. Complete the following steps to change a chart’s source data: Step 1
2
3
4
Action Right click in the plot area of the chart and choose Select Data from the menu.
In the Select Data Source window, make sure the Chart date range field is active.
This will show you the data range the chart is pulling from. Click and drag your cursor over the new range of cells you would like to use for your chart.
This will automatically update your Select Data Source window. Select OK to save your changes.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
14
{EXCEL – PARTICIPANT GUIDE} Charts, Continued Activity: Change a Chart’s Source Data
Right click in the chart.
1 2
Click Select Data.
2 2
Clear the data in the Chart data range field and leave it selected to activate it.
4 2
Select range B91:E95.
3 2
Click OK.
5 2
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
15
{EXCEL – PARTICIPANT GUIDE} Format Merge & Center Cells
Complete the following steps to merge and center cells: Step 1
2
Action Select the range of cells to merge.
Select Home tab>Merge and Center>Merge and Center.
The title moves to the top center of the cell.
Activity: Merge & Center Cells Click the Merge & Center dropdown menu> Merge & Center.
3
Select range A10:M10.
2
Click the Cover worksheet.
1 2
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
16
{EXCEL – PARTICIPANT GUIDE} Format, Continued Add Auto Header/Footer
Headers and footers are displayed at the top and the bottom of each printed page. If you are printing a long report it is very useful to add page numbers. Other items commonly added to page headers and footers include: A title The date/time that the report was printed The report author’s name The name of the Excel file that was used to generate the report The full path to the Excel file A company logo Copyright notices A distribution list or the security level of the document; i.e. Confidential Complete the following steps to add a header or footer to an Excel document: Step 1
2
Action Click the Page Layout view.
Click the Page Header area at the top of the screen or the Page Footer area at the bottom of the screen.
3
When you click in this area a new tab appears on the Ribbon. Click Header & Footer Tools> Design tab.
4
Click either Header or Footer, depending on what you are creating.
5
Select the desired option from the dropdown menu.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
17
{EXCEL – PARTICIPANT GUIDE} Format, Continued Add Custom Header/Footer
Custom headers allow you to combine your own text with report fields, such as page numbers. You are also able to add text to three different sections in the header and footer areas; left, right & center. Click on any of the three different sections in the header or footer section and use the Header & Footer Elements on the Design tab to create custom headers and footers.
Activity: Headers & Footers Click the Current Date option.
Click the Design tab.
5 2
Click the left header box and type Printed on:
Click the Demographic Worksheet.
1 2
4 2 3 2
Click the Page Layout view.
2 2
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
18
{EXCEL – PARTICIPANT GUIDE} Format, Continued Click the Current Time option.
7 2 Type at: after the &[Date] in the Header.
Click the Number of Pages option.
6 2
9 2
Click the left footer box and type Holmes Murphy Confidential
Click outside of the header box.
10
Click the middle footer box.
11
8 2
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
19
{EXCEL – PARTICIPANT GUIDE} Print Print Centered on Page
Complete the following steps to adjust page margins automatically: Step 1
Action Select the Page Layout tab>Margins>Custom Margins.
2
In the Page Setup window, under Center on page, select both the Horizontally and Vertically boxes.
3
Click OK.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
20
{EXCEL – PARTICIPANT GUIDE} Print, Continued Activity: Print Centered on Page
Select the Page Layout tab> Print Area> Set Print Area.
2
Select range A1:V95.
4 2
1 2
Click Margins> Custom Margins
Change to Page Layout view.
3 2
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
21
{EXCEL – PARTICIPANT GUIDE} Print, Continued
Check the boxes next to Horizontally and Vertically.
5 2
Click Ok.
6 2
Print Scaling
Complete the following steps to change the paper orientation when printing: Step 1 2 3 4
Action Select the Home tab. Select all worksheets. Return to the Print Preview page and select Landscape Orientation. Select the scaling drop down menu and select Fit Sheet on One Page.
You can now page through and see how the document will print. Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
22
{EXCEL – PARTICIPANT GUIDE} Print, Continued Activity: Print Scaling 1
Click the File tab.
2 2
Select Print Selection under Settings.
Click Print.
5 2
4 2
3 2
Notice that your print selection now appears on one page.
Select Fit Sheet on One Page under Settings.
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
23
{EXCEL – PARTICIPANT GUIDE} Pivot Tables Overview
A pivot table is a great way to sort and summarize a large amount of data into a usable format quickly. It is a very powerful tool to automatically sort, count, and total spreadsheet data without using formulas.
Why Use Pivot Tables?
Pivot tables allow you to change the way you look at your data without changing the original spreadsheet.
Pivot Table Requirements
Prior to creating a Pivot table the following must be true: The top row of data contains column headers. Each row of data is a record about a particular entity or transaction. Each column of data holds the same kind of information. There are no entirely blank rows in the data. There are no entirely blank columns in the data. If a column contains numbers, use a zero instead of a blank cell when you don’t have a value.
Create a Pivot Table
Complete the following steps to create a pivot table: Step 1 2
Action Click any single cell in the data. Click the Insert tab>PivotTable.
Excel will look at your spreadsheet and determine what row and columns you would like in your pivot table. It will look until it finds a blank row or column to create the pivot table. Note: By default, the Pivot Table will be created on a new worksheet that Excel inserts into the workbook. Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
24
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued Create a Pivot Table, continued
Step 3
Action Click OK, and leave the defaults in the “Create Pivot Table” dialog. Your Pivot able will look like the picture below:
4
Think of the information that you want to see from your data table and use the Field List to select and organize that data. Drag the desired fields to the drop zones to create the Pivot Table structure.
5
Note: If you turn on the check mark for a text field, the Pivot Table automatically adds that field to the “Row Labels” drop area. If you turn on a check mark for a numeric field, the Pivot Table automatically adds that field to the “Values” drop area. Continue to drag and drop the fields from above as needed. This will produce the answers for the first total above. Note: You need at least two fields in a Pivot Table report (a row or column field and a data field), but three or more fields (a row field, a column field, and a data field) are the usual. Continue to drag and drop the fields from above as needed. Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
25
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued
Activity: Create a Pivot Table Using the Demo Spreadsheet, create a Pivot table to get the following information: 1. The total cost of the LTD within each of the units. 1) The cost for male and female for each of the above. Click the Insert tab.
3
2 Click PivotTable.
1 Click anywhere in the data to activate.
Click OK.
6
4
Click and drag Plan and Unit from the Field List to the Row Labels drop zone. Note: Make sure Plan is above Unit.
Click and drag Sex from the Field List to the Column Labels drop zone.
5
Click and drag Ann Ben RT from the Field List to the Values drop zone.
7
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
26
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued
Activity: Adding More to a Pivot Table The more information you add to a pivot table the more your information will be split apart.
1
2 Doubleclick the Row Labels cell (A:4) and rename it Plans.
Click and drag Plan Typ from the Field List to the Row Labels drop zone. Note: Make sure Plan Typ is above Unit.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
27
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued Sort & Filter a Pivot Table
Once you have created a Pivot Table you can sort and filter the different elements of the table, as you would in a regular Excel worksheet. This will not change the integrity of the table and allows you to only see the piece you need. Complete the following steps to sort and filter columns: Step 1
Action Click on the arrow next to any of the column headings.
2
Click on the checkmark in front of Select All to take off all of the fields.
3
Click on the box in front of any of the values to see only those options.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
28
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued Sort & Filter a Pivot Table, continued
Step 4
Action Click OK. Your new pivot table will look like this.
Note: you can tell that your Pivot table information has been filtered by the Icon that is next to the column header, where the arrow was located.
Activity: Sort & Filter a Pivot Table
Click the arrow next to the Plans column header.
Click on the checkmark in front of Select All to take off all of the fields.
1
2
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
29
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued
Select LTD-ER & STDEX.
3
Click OK.
4 Activity: Change Date from Row to Column To change from row labels to column labels, drag the field to a different drop zone. This will change the look of your pivot table. It is giving you the same information just in a slightly different format.
Click the arrow next to the Plans column header.
2
Click on the checkmark in front of Select All select all of the fields.
Click OK.
4
3
1 Click and drag the Unit field from Row Labels to Column Labels. Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT
July 22, 2015
30
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued Change Value to a Percentage
Complete the following steps to see a percentage of the values within a pivot table: Step 1 2 3 4 5
Action Right click in Values area. Click on Summarize Data By Click More Options. Click the Show Values as tab in the Value Field Settings dialog box Click the arrow to scroll down and select % of total.
Note: To return the values to normal view, follow the same steps above, and then click Normal. This will turn off the custom calculations. Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
31
{EXCEL – PARTICIPANT GUIDE} Pivot Tables, Continued
Activity: Change Value to a Percentage 2
Click the Options tab. Click Summarize Values By>More Options
4
5
3
Click any cell in the Grand Total column.
Click the Show Values As tab.
1
Select % of Grand Total in the dropdown menu.
Click OK.
6
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
32
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers Overview
Slicers are easy-to-use filtering components that contain a set of buttons that enable you to quickly filter the data in a PivotTable report, without the need to open dropdown lists to find the items that you want to filter. Unlike report filter fields, slicers show which filters are in place when a multiple-item filter is applied.
Create a Pivot Table Slicer
Once you have created a Pivot Table, complete the following steps to create a slicer: Step 1 2
3
Action Click anywhere in your Pivot table. This will display your pivot table tools in your menu bar. On the options tab, in the sort & Filter group, click on Insert Slicer.
In the Insert Slicers dialog box, select the check box of the PivotTable fields for which you want to create a slicer.
Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
33
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers, Continued Create a Pivot Table Slicer, continued
Step 4
Action Click OK. A slicer is displayed for every field you selected.
5 6
Click and drag the slicers next to your pivot table. To adjust the look of a slicer, activate the slicer and select the Options tab. Adjust the number of columns and resize your slicers as desired.
5
In each slicer, click the items on which you want to filter. To select more than one item, hold down CTRL, and then click the items on which you want to filter.
6
To remove a slicer filter, click on the funnel in the corner with the x in the slicer box. This will remove any slicers you have set in this box.
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
34
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers, Continued Activity: Add a Pivot Table Slicer 2
Click the Options tab. Click the Insert Slicer.
1
Check the boxes next to Plan Typ, Unit & Sex.
Click OK.
3
4 Continued on next page
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
35
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers, Continued
Activity: Format a Pivot Table Slicer Click the Options tab.
3 Click and drag each slicer to view.
Give the slicer 2 columns.
1
4
Select a slicer.
2
Select a Slicer Style.
5
6 Repeat for each slicer.
Click and drag each slicer to resize as needed.
7
Continued on next page – Holmes Murphy ORGANIZATION DEVELOPMENT
July 22, 2015
36
{EXCEL – PARTICIPANT GUIDE} Pivot Table Slicers, Continued
8 Use your slicers to filter information containing: Females with LTD & NESTD plan types and AMSSV units. Note: Use the CTRL key to select multiple filters
– Holmes Murphy ORGANIZATION DEVELOPMENT July 22, 2015
37
