1、 Excel Dashboards i About the Tutorial Dashboards are popular visual displays of data, mostly comprising of charts / graphs with striking attention seeking components. There are various tools available in the market to create dashboards. If you are a Microsoft Office user with reasonably good master
2、y on Excel, then creating dashboards in Excel is a wise decision. This is because Microsoft has introduced several powerful features in Excel, making your job of handling large datasets from various data sources simple and less tiresome. In this tutorial, you will learn how to use Excel features eff
3、ectively in dashboards. They include features that can make a dashboard dynamic and interactive. Audience This tutorial has been designed for all those readers who depend heavily on MS-Excel to prepare charts, tables, and professional reports that involve complex data. It will help all those readers
4、 who use MS-Excel regularly to analyze data. Once you get an understanding of the several Excel features that come handy in creating Excel dashboards, creating dashboards will become a trivial task for you. Prerequisites Before proceeding with this tutorial, the reader should have a preliminary unde
5、rstanding of Excel workbooks, Excel charts, Excel PivotTables, Excel Data Model, Excel Power PivotTables and Power PivotCharts and Excel Power View reports. All these topics are available as full-fledged tutorials in our tutorials library. Copyright & Disclaimer Copyright 2016 by Tutorials Point (I)
6、 Pvt. Ltd. All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publ
7、isher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents
8、 including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at Excel Dashboards ii Table of Contents About the Tutorial . i Audience . i Prerequisites . i Copyright & Disclaimer . i Table of Contents . ii 1. Dashboards Introduction . 1 Dashboard Definit
9、ion . 2 Key Metrics for Dashboard . 2 Dashboard Benefits . 3 Types of Dashboards . 3 Dashboard Data and Formats . 6 Live Data on Dashboards . 6 2. Dashboards Excel Features to Create Dashboards . 7 Excel Tables . 7 Sparklines . 7 Conditional Formatting . 8 Excel Charts . 9 Excel Camera . 15 Excel Pi
10、votTables . 15 Dynamic Dashboard Elements with Interactive Controls . 17 Excel Power PivotTables and Power PivotCharts . 18 Excel Power View Reports . 19 Key Performance Indicators (KPIs) . 20 3. Dashboards Conditional Formatting . 21 Highlighting Cells . 21 Top / Bottom Rules . 23 Data Bars . 25 Co
11、lor Scales . 27 Icon Sets . 30 Using Custom Rules . 31 Managing Conditional Formatting Rules . 33 4. Dashboards Excel Charts . 35 Types of Charts . 35 Selecting the Appropriate Chart Type . 42 Showing Trends with Sparklines in Tables . 43 Using Combo Charts for Comparisons . 45 Fine Tuning Charts Qu
12、ickly . 45 Using Aesthetic Data Labels . 49 Using Trendlines in Charts . 53 Using Shapes in Charts . 54 Using Cylinders, Cones, and Pyramids . 55 Using Pictures in Charts . 57 Excel Dashboards iii 5. Dashboards Interactive Controls . 58 Scroll Bars in Dashboards . 59 Creating a Scrollbar . 61 Creati
13、ng a Dynamic and Interactive Target Line . 66 Excel Option (Radio) Buttons . 73 Excel Checkboxes . 81 6. Dashboards Advanced Excel Charts . 86 Types of Advanced Excel Charts . 86 Displaying Quarterly Performance with Bullet Charts . 91 Displaying Profit % Region-Wise with Waffle Charts . 91 7. Dashb
14、oards Excel PivotTables . 93 Creating a PivotTable . 93 Filtering Data in PivotTable . 97 Using Slicers in PivotTable . 100 8. Dashboards Power PivotTables & Power PivotCharts . 103 Uses of Power Pivot . 103 Differences between PivotTable and Power PivotTable . 103 Creating a Power PivotTable . 103
15、Creating a Power PivotChart . 107 Table and Chart Combinations . 112 Hierarchies in Power Pivot . 113 Calculations Using Hierarchy in Power PivotTables . 117 Drilling Up and Drilling Down a Hierarchy . 119 Using a Common Slicer . 121 Aesthetic Reports for Dashboards . 125 9. Dashboards Power View Re
16、ports . 127 Power View Visualizations . 127 Combination of Power View Visualizations . 131 Interactive Nature of Charts in Power View Visualizations . 132 Slicers in Power View . 132 Tiles in Power View. 133 Power View Reports . 137 10. Dashboards Key Performance Indicators . 138 Components of a KPI . 138 Base Value . 138 Target Value . 139 Status Thresholds and Status . 139 Defining KPIs in Excel . 139 Visualizing KPIs with Bullet Charts . 139 Visualizing KPIs with Power View . 140 11. Dashboards Build a Dashboard .
