microsoft EXCEL DASHBOARDS & REPORTS FOR DUMMIES

Title Page
Copyright Page
Table of Contents
Introduction
	About This Book
	Foolish Assumptions
	Icons Used in This Book
	Beyond the Book
	Where to Go from Here
Part 1 Getting Started with Excel Dashboards and Reports
	Chapter 1 Getting in the Dashboard State of Mind
		Defining Dashboards and Reports
			Defining reports
			Defining dashboards
		Preparing for Greatness
			Establish the audience for, and purpose of, the dashboard
			Delineate the measures for the dashboard
			Catalog the required data sources
			Define the dimensions and filters for the dashboard
			Determine the need for drill-down features
			Establish the refresh schedule
		A Quick Look at Dashboard Design Principles
			Rule number 1: Keep it simple
			Use layout and placement to draw focus
			Format numbers effectively
			Use titles and labels effectively
	Chapter 2 Building a Super Model
		Data Modeling Best Practices
			Separating data, analysis, and presentation
			Starting with appropriately structured data
			Avoiding turning your data model into a database
			Using tabs to document and organize your data model
			Testing your data model before building reporting components on top of it
		Excel Functions That Really Deliver
			The VLOOKUP function
			The HLOOKUP function
			The SUMPRODUCT function
			The CHOOSE function
		Using Smart Tables That Expand with Data
			Converting a range to an Excel table
			Converting an Excel table back to a range
		Introducing Dynamic Arrays
			Getting the basics of dynamic arrays
			Understanding spill ranges
			Referencing spill ranges
		Exploring Dynamic Array Functions
			The SORT function
			The SORTBY function
			The UNIQUE function
			The FILTER function
			The XLOOKUP function
	Chapter 3 The Pivotal Pivot Table
		An Introduction to the Pivot Table
		The Four Areas of a Pivot Table
			Values area
			Row area
			Column area
			Filter area
		Creating Your First Pivot Table
			Changing and rearranging your pivot table
			Adding a report filter
			Keeping your pivot table fresh
		Customizing Pivot Table Reports
			Changing the pivot table layout
			Customizing field names
			Applying numeric formats to data fields
			Changing summary calculations
			Suppressing subtotals
			Showing and hiding data items
			Hiding or showing items without data
			Sorting your pivot table
		Creating Useful Pivot-Driven Views
			Producing top and bottom views
			Creating views by month, quarter, and year
			Creating a percent distribution view
			Creating a month-over-month variance view
	Chapter 4 Using External Data for Your Dashboards and Reports
		Leveraging Power Query to Extract and Transform Data
			Reviewing Power Query basics
			Understanding query steps
		Importing Data from Files
			Getting data from Excel workbooks
			Getting data from CSV and text files
		Importing Data from Database Systems
			Importing data from Microsoft Access
			Managing data source settings
Part 2 Building Basic Dashboard Components
	Chapter 5 Dressing Up Your Data Tables
		Table Design Principles
			Use colors sparingly
			De-emphasize borders
			Use effective number formatting
			Subdue your labels and headers
		Getting Fancy with Custom Number Formatting
			Number formatting basics
			Formatting numbers in thousands and millions
			Hiding and suppressing zeroes
			Applying custom format colors
			Formatting dates and times
	Chapter 6 Sparking Inspiration with Sparklines
		Introducing Sparklines
		Understanding Sparklines
			Creating sparklines
			Understanding sparkline groups
		Customizing Sparklines
			Sizing and merging sparkline cells
			Handling hidden or missing data
			Changing the sparkline type
			Changing sparkline colors and line width
			Using color to emphasize key data points
			Adjusting sparkline axis scaling
			Faking a reference line
			Specifying a date axis
			Autoupdating sparkline ranges
	Chapter 7 Formatting Your Way to Visualizations
		Enhancing Reports with Conditional Formatting
			Applying basic conditional formatting
			Adding your own formatting rules manually
			Showing only one icon
			Showing Data Bars and icons outside of cells
			Representing trends with Icon Sets
		Using Symbols to Enhance Reporting
		Wielding the Magical Camera Tool
			Finding the Camera tool
			Using the Camera tool
			Enhancing a dashboard with the Camera tool
		Enhancing Excel Reports with Shapes
			Creating visually appealing containers with shapes
			Layering shapes to save space
			Constructing your own infographic widgets with shapes
Part 3 Adding Charts to Your Dashboards
	Chapter 8 Charts That Show Trending
		Trending Dos and Don’ts
			Using chart types appropriate for trending
			Starting the vertical scale at zero
			Leveraging Excel’s logarithmic scale
			Applying creative label management
		Comparative Trending
			Creating side-by-side time comparisons
			Creating stacked time comparisons
			Trending with a secondary axis
		Emphasizing Periods of Time
			Formatting specific periods
			Using dividers to mark significant events
			Representing forecasts in your trending components
		Other Trending Techniques
			Avoiding overload with directional trending
			Smoothing data
	Chapter 9 Grouping and Bucketing Data
		Creating Top and Bottom Displays
			Incorporating top and bottom displays into dashboards
			Using pivot tables to get top and bottom views
		Top Values in Charts
		Using Histograms to Track Relationships and Frequency
			Using Excel’s Histogram statistical chart
			Creating a formula-driven histogram
			Adding a cumulative percent
			Using a pivot table to create a histogram
	Chapter 10 Displaying Performance against a Target
		Showing Performance with Variances
		Showing Performance against Organizational Trends
		Using a Thermometer-Style Chart
		Using a Bullet Graph
			Creating a bullet graph
			Adding data to your bullet graph
			Final thoughts on formatting bullet graphs
		Showing Performance against a Target Range
Part 4 Advanced Reporting Techniques
	Chapter 11 Giving Users an Interactive Interface
		Introducing Macros
			Why use a macro?
			Recording your first macro
			Running your macros
			Enabling and trusting macros
			Understanding macro-enabled file extensions
			Enabling macro content
			Setting up trusted locations
			Examining some macro examples
			Building navigation buttons
			Dynamically rearranging pivot table data
			Offering one-touch reporting options
		Getting Started with Form Controls
			Finding Form controls
			Adding a control to a worksheet
		Using the Button Control
		Using the Check Box Control
			Toggling a Chart Series On and Off
		Using the Option Button Control
			Showing Many Views through One Chart
		Using the Combo Box Control
			Changing Chart Data with a Drop-Down Selector
		Using the List Box Control
			Controlling Multiple Charts with One Selector
	Chapter 12 Adding Interactivity with Pivot Slicers
		Understanding Slicers
		Creating a Standard Slicer
		Getting Fancy with Slicer Customizations
			Size and placement
			Data item columns
			Other slicer settings
			Creating your own slicer style
		Controlling Multiple Pivot Tables with One Slicer
		Creating a Timeline Slicer
		Using Slicers as Form Controls
		Using Slicers on Excel Table Objects
	Chapter 13 Sharing Your Workbook with the Outside World
		Protecting Your Dashboards and Reports
			Securing access to the entire workbook
			Limiting access to specific worksheet ranges
			Protecting the workbook structure
		Linking Your Excel Dashboards to PowerPoint
			Creating a link between Excel and PowerPoint
			Manually updating links to capture updates
			Turning off automatic updating of links
		Distributing Your Dashboards via a PDF
		Distributing Your Dashboards to OneDrive
		Limitations When Publishing to the Web
Part 5 The Part of Tens
	Chapter 14 Ten Chart Design Principles
		Avoid Fancy Formatting
		Skip the Unnecessary Chart Junk
		Format Large Numbers Where Possible
		Use Data Tables Instead of Data Labels
		Make Effective Use of Chart Titles
		Sort Your Data before Charting
		Limit the Use of Pie Charts
		Don’t Be Afraid to Parse Data into Separate Charts
		Maintain Appropriate Aspect Ratios
		Don’t Be Afraid to Use Something Other Than a Chart
	Chapter 15 Ten Questions to Ask Before Distributing Your Dashboard
		Does My Dashboard Present the Right Information?
		Does Everything on My Dashboard Have a Purpose?
		Does My Dashboard Prominently Display the Key Message?
		Can I Maintain This Dashboard?
		Does My Dashboard Clearly Display Its Scope and Shelf Life?
		Is My Dashboard Well Documented?
		Is My Dashboard Overwhelmed with Formatting and Graphics?
		Does My Dashboard Overuse Charts When Tables Will Do?
		Is My Dashboard User-Friendly?
		Is My Dashboard Accurate?
Index
EULA