Transcription

IT Training CentreUniversity College CorkExcel 2016Pivot TablesEmail [email protected]

Excel 2016 – Pivot TablesTable of ContentsPivot Tables . 1Changing the Value Field Settings . 2Refreshing the Data . 3Refresh Data when opening a file . 3Grouping Data . 4Pivot Charts . 5Creating a Pivot Chart . 5Slicer . 6Create a slicer in an existing PivotTable . 6Timeline. 7Use a timeline to filter by time period. 7Customize a timeline . 8Calculate values in a PivotTable report . 9Add a calculated field . 9Add a calculated item to a field . 9Using Show Value As . 10Notes . 12UCC IT Training CentreEmail [email protected]

Excel 2016 – Pivot TablesPivot TablesBeing able to analyse all the data in your worksheet can help you make better businessdecisions. But sometimes it’s hard to know where to start, especially when you have a lot ofdata. Excel can help you by recommending and then automatically creating PivotTables,which are a great way to summarize, analyse, explore, and present your data.Make sure your data has column headings or table headers, and that there are no blankrows. Click any cell in the range of cells or table. Click on the Insert Tab and click onRecommended PivotTables. In the Recommended PivotTables dialog box, clickany PivotTable layout on the left to get a previewto the right, and then pick the one that shows thedata the way you want. Click OK.Excel places the PivotTable on a new worksheet and shows the Field List so you canfurther rearrange the PivotTable data as needed.Note: Using a recommended PivotTable is a quick way to get started on the right path.However, you can still create an empty PivotTable to add your own fields and layout. Justclick PivotTable on the Insert tab, instead of Recommended PivotTables.UCC IT Training Centre1Email [email protected]

Excel 2016 – Pivot Tables All pivot table options are available within the Pivot Table Tools under both theAnalyze and Design tabs. These are displayed once you click anywhere on the PivotTable. You can re-arrange the layout of your pivot table by dragging the headings from thefield list on the right hand side to the Report filter, column labels, row labels andvalues areas beneathYou don’t have to use all the fields and a little experimentation is required initially todecide what layout works best. The heading in the values field nearly always represents a numeric value. A valueyou would want to sum, average or count. The heading in the Report Filter is something you might want to filter an entireset of data on. In this example it might be useful to compare the different stores. Using the drop down arrows on the headings you can filter the data.Changing the Value Field SettingsBy default if you have a numeric value in the Values fieldit will be summed. If you want to get the average orcount the number of values, do the following Click on the Arrow beside the Sum of ValueHeading or right click somewhere within the Unitsarea of the Pivot Table From the menu that displays select Value FieldSettings and choose the required function e.g. Average, Max, Min, Count The number format can also be changed here e.g. if you required Currency with 2decimal places Click OKUCC IT Training Centre2Email [email protected]

Excel 2016 – Pivot TablesRefreshing the DataIf changes are made to the original data you must Refresh your pivot table to reflect thesechanges. The Refresh command is located under the Anyalyze Tab of Pivot Table Tools.Refresh Data when opening a fileThere is an option that can be turned on that will refresh the data when opening the file. From the PivotTableTools click Options Tab then within Pivot Table Group, Optionscommand Click on the Data Tab and tick Refresh thedata when opening the file Click on OKUCC IT Training Centre3Email [email protected]

Excel 2016 – Pivot TablesGrouping DataIn the row and column label areas of a PivotTable report, you can group the items in a fieldin a custom way. Grouping the data can help you to isolate a subset of data that satisfiesyour specific needs, and that cannot be easily grouped in other ways, such as sorting andfiltering. You may wish to group using one of the following: Group numbers in numeric fields Group dates or times Group selected itemsExample of grouping by date:1. In the PivotTable report, click any date or time in the date or timefield, and then on the Options tab, in the Group group, click GroupField.2. The following screen appears:3. Leave the auto information unless you wish to set your own start and end date.4. In the By box, click one or more date or time periods for the groups. Group byMonths is selected by default, but you can click it to deselect it.5. To group items by weeks, in the By box, make sure that Days is the only time periodselected, and then in the Number of days box, click 7. You can then click additionaltime periods to group by, such as Months.6. Click on OK.UCC IT Training Centre4Email [email protected]

Excel 2016 – Pivot TablesPivot ChartsA PivotChart provides a graphical representation of the data in a PivotTable report, in thiscase is called the associated PivotTable report. Like a PivotTable, a PivotChart is interactive.When you create a PivotChart, PivotChart filters are displayed in the chart area so that youcan sort and filter the underlying data of the PivotChart report. Changes that you make tothe field layout and data in the associated PivotTable are immediately reflected in thePivotChart.A PivotChart displays data series, categories, data markers, and axes just as standard chartsdo. You can also change the chart type and other options such as the titles, the legendplacement, the data labels, and the chart location.Creating a Pivot Chart Click any cell in the range of cells. Click on the Insert Tab and click on Pivot Chart and select Pivot Chart. Click on OK Create the Pivot Chart by dragging the fields as required to the FILTERS, LEGEND(SERIES), AXIS (CATEGORY) AND VALUES area at the bottom right of the screen. To avail of all the Pivot Chart Tools options click anywhere on the chart created andclick on the Analyze, Design or Format TabsUCC IT Training Centre5Email [email protected]

Excel 2016 – Pivot TablesSlicerSlicers are easy-to-use filtering components that contain a set of buttons that enable you toquickly filter the data in a PivotTable report, without the need to open drop-down lists tofind the items that you want to filter.When you use a regular PivotTable report filter to filter on multiple items, the filter indicatesonly that multiple items are filtered, and you have to open a drop-down list to find thefiltering details. However, a slicer clearly labels the filter that is applied and provides detailsso that you can easily understand the data that is displayed in the filtered PivotTable report.Slicers are typically associated with the PivotTable in which they are created. However, youcan also create stand-alone slicers. A slicer typically displays the following elements:1. A slicer header indicates the category of theitems in the slicer.2. A filtering button that is not selected indicatesthat the item is not included in the filter.3. A filtering button that is selected indicates thatthe item is included in the filter.4. A Clear Filter button removes the filter byselecting all items in the slicer.5. A scroll bar enables scrolling when there aremore items than are currently visible in the slicer.6. Border moving and resizing controls allow you tochange the size and positionCreate a slicer in an existing PivotTable Click anywhere in the PivotTable report for which you want to create a slicer.This displays the PivotTable Tools, adding anAnalyze and a Design tab. On the Analyze tab, in the Filter group, clickInsert Slicer. In the Insert Slicers dialog box, select thecheck box of the PivotTable fields for whichyou want to create a slicer. Click OK. A slicer is displayed for every field that you selected. In each slicer, click the items onwhich you want to filter. To select more than one item, hold down CTRL, and thenclick the items on which you want to filter.UCC IT Training Centre6Email [email protected]

Excel 2016 – Pivot TablesTimelineInstead of playing around with filters to show dates, you can now use a PivotTable timeline.It’s a box you can add to your PivotTable that lets you filter by time, and zoom in on theperiod you want.Much like a slicer you create to filter data, you can insert a timeline once and keep it withyour PivotTable Click anywhere in a PivotTable to show the PivotTableTools.Click Analyze and click on Insert Timeline.In the Insert Timelines dialog box, check the boxes of thedate fields you want and click OK.Use a timeline to filter by time periodWith your timeline in place, you’re ready to filter by a time period in one of four time levels(years, quarters, months, or days). Click the arrow next to the time level shown, and pick the one you want. Drag the timeline scroll bar to the time period you want to analyse.In the timespan control, click a period tile and drag to include additional tiles toselect the date range you want. Use the timespan handles to adjust the date rangeon either side.To clear a timeline, click the Clear Filter button. Tip If you want to combine slicers with a timeline to filter the same date field, you can dothat by checking the Allow multiple filters per field box in the PivotTable Options dialog box(PivotTable Tools Analyze Options Totals & Filters tab).UCC IT Training Centre7Email [email protected]

Excel 2016 – Pivot TablesCustomize a timelineWhen a timeline covers your PivotTable data, you can move it to a better location andchange its size. You can also change the timeline style, which may be useful if you havemore than one timeline. To move the timeline, simply drag it to the location you want. To change the size of the timeline, click it, and then drag the sizing handles to thesize you want. To change the style of the timeline, click it to display the Timeline Tools, and thenpick the style you want on the Options tab.UCC IT Training Centre8Email [email protected]

Excel 2016 – Pivot TablesCalculate values in a PivotTable reportAdd a calculated field Click the PivotTable report. This displays thePivotTable Tools, adding the Analyze and Designtabs. On the Analyze tab, in the Calculationsgroup, click Fields, Items, & Sets, and then clickCalculated Field. In the Name box, type a name for the field In the Formula box, enter theformula for the field.To use the data from anotherfield in the formula, click thefield in the Fields box, andthen click Insert Field. Forexample, to calculate a 15%discount on each value in theUnit Cost field, you couldenter Unit Cost * 15%.Click Add.Then click on OK.Add a calculated item to a field Click the PivotTable report. This displays the PivotTable Tools, adding the Analyze andDesign tabs. Click the field where you want to add the calculated item. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then clickCalculated Item. In the Name box, type a name for the calculated item. In the Formula box, enter the formula for the item.To use the data from an item in the formula, click the item in the Items list, andthen click Insert Item (the item must be from the same field as the calculated item). Click Add.UCC IT Training Centre9Email [email protected]

Excel 2016 – Pivot TablesUsing Show Value AsInstead of writing your own formulas in calculated fields, you can use Show Values As toquickly present values in different ways. It provides several calculation options, such as % ofParent Total or % Running Total In.TipYou can use this feature to try different calculations in a value field. However,because you can add the same value fields to a PivotTable more than once, you canalso use this feature to show the actual value and other calculations, such as arunning total calculation, side by side. To add two or more of the same value fields to the PivotTable so that you can displaydifferent calculations in addition to the actual value of a specific field, do thefollowing: In the Field List, drag the value field that you want to add to the Values areathat already contains that value field, and then place it right below that field.Note The value field is added to the PivotTable and a version number isappended to its field name. You can edit the field name as needed. Repeat the above step until you have displayed all the value fields you wantto calculate by using Show Values As. Right click on the field andchoose Value Field SettingsClick on the Show Value As tab.This is currently set to NoCalculation.Select the required calculationand click on OK UCC IT Training Centre10Email [email protected]

Excel 2016 – Pivot TablesThe following calculation options are available in value fields:Calculation optionNo Calculation% of Grand Total% of Column Total% of Row Total% Of% of Parent Row Total% of Parent Column Total% of Parent TotalDifference From% Difference FromRunning Total in% Running Total inRank Smallest to LargestRank Largest to SmallestIndexUCC IT Training CentreResultDisplays the value that is entered in the field.Displays values as a percentage of the grand total of all thevalues or data points in the report.Displays all the values in each column or series as a percentageof the total for the column or series.Displays the value in each row or category as a percentage ofthe total for the row or category.Displays values as a percentage of the value of the Base item inthe Base field.Calculates values as follows:(value for the item) / (value for the parent item on rows)Calculates values as follows:(value for the item) / (value for the parent item on columns)Calculates values as follows:(value for the item) / (value for the parent item of the selectedBase field)Displays values as the difference from the value of the Baseitem in the Base field.Displays values as the percentage difference from the value ofthe Base item in the Base field.Displays the value for successive items in the Base field as arunning total.Calculates the value as a percentage for successive items in theBase field that are displayed as a running total.Displays the rank of selected values in a specific field, listing thesmallest item in the field as 1, and each larger value with ahigher rank value.Displays the rank of selected values in a specific field, listing thelargest item in the field as 1, and each smaller value with ahigher rank value.Calculates values as follows:((value in cell) x (Grand Total of Grand Totals)) / ((Grand RowTotal) x (Grand Column Total))11Email [email protected]

Excel 2016 – Pivot TablesNotesUCC IT Training Centre12Email [email protected]

Excel 2016 - Pivot Tables UCC IT Training Centre 2 Email [email protected] All pivot table options are available within the Pivot Table Tools under both the Analyze and Design tabs. These are displayed once you click anywhere on the Pivot Table. You can re-arrange the layout of your pivot table by dragging the headings from the field list on the right hand side to the Report filter .