Pivot table and Dashboard in microsoft excel

Data analysis and data vizualization using excel pivot table and pivot chart.
It enables to create summery reports and dashboards

1. Pivot Table & Dashboard using Microsoft Excel Feb. 2, 2018 Frehiwot Mulugeta [email protected]

2. Objectives To improve data visibility, data use and facilitate data driven decision making To understand and create Pivot Tables To create interactive dashboard

3. Part 1 – Pivot Table Definition Overview components of a pivot table Creating pivot table Introducing filters and slicers Formatting

4. Definition PivotTables summarize and analyze large amounts of data into summary reports.

5. Pivot Table Advantages Interactive : rearrange them by moving, adding, or deleting fields Easy to update : “refreshable” if the original worksheet data changes

6. The data must be normalized : Must have tabular layout Every column must have column h eadings in the first row .

7. Creating PivotTable Click in the Excel table or select the range of data for the PivotTable Click Insert tab > click the PivotTable button Click the Select a table or range option button and verify the reference in the Table/Range box Choose where you want to place the pivot table To place on new sheet Click the New Worksheet option button/ It directed to new sheet/ click the Existing worksheet option button and specify a cell Click the OK button

8. Creating PivotTable

9. Components of PivotTable

10. Adding a Report Filter to a PivotTable A report filter allows you to focus on a subset of items in that field

11. Analyze Tab To add slicer To group fields To change the data source change To show & hide pivot table tab To refresh the data To create pivot chart The ribbon for PivotTables

12. The ribbon for PivotTables Design Tab To change the table style To include/ exclude sub total & grand total To change the report layout

13. Adding data slicer in PivotTable Data slicers help you filter & visualize the filtered summery report It makes the pivot table/chart interactive click any cell in the pivot table > click Analyze > under Filter click insert slicer > select the field u want to filter from insert slicer > ok Adding data slicer steps

14. Slicer … Formatting data slicer steps Select the data slicer > click Option > under slicer styles > select the styles u want Creating report connection Select the data slicer > click Option > under slicer click report connection > select pivot table & chart to connect to the filter > Ok

15. Format PivotTable Good formatting will communicate your data better Pivot table Style Formatting Reporting layout Formatting Style Formatting steps click any cell in the pivot table > click design > Pivot table Styles choose a style Reporting layout Formatting click any cell in the pivot table > click design > Layout > choose a layout

16. Format Fields/ data Number format Right click the value on pivot table fields > click value field settings> Number format Button > do the formatting and click ok Or Select the data inside the pivot table > home > Number > do the formatting

17. Grouping PivotTable Items Automatically grouping dates in to months and years For easy understanding Reporting layout Formatting click date > click Analyze > Group selection > click month & Year > ok

18. Refreshing a PivotTable When you create a PivotTable, a copy of the data is stored in a pivot cache. Any changes to the data won’t show up in the report until you refresh the cache . To refresh the data: Right-click the pivot table and click Refresh Data. Or Go to the Analyze tab , in the Data group, click the Refresh button

19. Why Visualization Chart types and their use Creating dashboards in excel Part 2 – Visualization

20. For performance monitoring To see trends over time  Advantages of visualization It improves data use & data-driven decision

21. Chart type and their purpose BAR CHART They are best used to show change over time, to compare different categories HORIZONTAL BAR CHART Best used to compare individual categories COLUMN BAR CHART useful for showing data changes over a period of time or for illustrating comparisons among individual items Rules 1. Always start at zero y-axis

22. Chart type … 100% Stacked Bar Chart Stacked Bar Chart Best used to compare parts of a whole. show the relationship to the whole, comparing the contribution of each of individual items value to a total across categories % share of part from the whole the percentage that each value contributes to a total across categories

23. Chart type … Line Charts Line charts display continuous data over time and are good for showing trends in data. Areal Charts Depicts a time-series relationship, but they are different than line charts in that they can represent volume. start y-axis at a zero baseline Don’t plot more than 4 lines in one graph Label lines to help quickly identify lines

24. Chart type … This should be used to visualize a correlation  between two data sets. Pie Chart how categories represent part of a whole -- the composition of something Make sure All data adds up to 100%. Don’t compare more than 6 things in one pie chart Dual Axis/ combo Chart

25. Selecting the right Chart for your data Purpose Chart Type To compare one or many value sets column bar chart line chart Scatter Plot To show distribution column bar chart line chart To understand the relationship between value sets Scatter Plot Line To show trends over time line chart column bar chart To show composition: how individual parts make up the whole of something pie chart stacked bar chart Area

26. Creating a PivotChart A PivotChart is a graphical representation of the data in a PivotTable A PivotChart allows you to interactively add, remove, filter, and refresh data fields Click any cell in the PivotTable > Tools group > Pivot chart > All Chart > select appropriate chart

27. Sample Excel Dashboard