Utilizing Microsoft Excel Dashboards for Reporting
Target Audience: Anyone who has a good knowledge of the workings within Excel and need to make formal presentations of their raw data by using interactive charts to highlight their company’s KPIs.
About the Course:
An Excel dashboard is a very powerful tool that can be designed fairly easily to create an impact on the visualization of data presented. Dashboards enable a reader to quickly make sense of the raw number by presenting them in visually colorful charts and tables. It also gives valuable insights into the key performance indicators (KPIs) of the business. The most important aspect of dashboards is it is highly interactive where the user can filter and change its views.
You will learn the following:
Learn how to bring data into your spreadsheet.
Where will the data come from?
Manage the data and link it to the dashboard objects, like charts and tables.
How often will data be updated?
Design the dashboard report.
Learn to apply Excel functions like:
SUMPRODUCT; SUMIF; SUMIFS; AVERAGEIF; AVERAGEIFS; COUNTIF; COUNTIFS; COUNT; COUNTA; COUNTBLANK; RANK; SMALL; LARGE; DSUM; DCOUNT; DAVERAGE; DMAX; DMIN; VLOOKUP; HLOOKUP; INDEX; MATCH; IFERROR; OFFSET; INDIRECT; CHOOSE
In summary, an Excel Dashboard is simply a summary as follows:
Usually fits on one page
Displays key trends, comparisons and data graphically or in small tables
Provides the reader with conclusions to their objective
Is often interactive allowing the user to filter data and switch views themselves
Employs best practices that enable the report to be updated quickly and easily (often at the click of just one button)
Training / Course Outline:
1. BUILDING THE LOGIC REQUIRED FOR USE IN DASHBOARDS
Working with relational operators within the logical functions like IF, AND, and OR statements.
Summarizing data using functions like MAX, MIN, RANK, SMALL, LARGE, COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS.
Integrating data using INDEX and MATCH
Looking up specific results using VLOOKUP and HLOOKUP
Formatting raw data using Text functions like LEFT, RIGHT, MID, SUBSTITUTE, REPLACE, LEN, TRIM, and EXACT
Dealing with dates and time using YEAR, MONTH, DAY, NETWORKDAYS, TODAY, and NOW
2. CONTROLLING DASHBOARD ENTRIES USING DATA VALIDATION
Why use Data Validation?
Creating a list using Data Validation
Applying formulas using Data Validation
3. USING CONDITIONAL FORMATTING TO COLOR EXCEL DASHBOARD CELLS
Changing the look is worksheet cells using Conditional Formatting
Setting up logical formulas using Conditional Formatting
Managing Conditional Formatting rules
Key Conditional Formatting rules that will help with Excel dashboards
4. UNDERSTANDING VARIOUS CHART TYPES
Conveying a Message with a Chart
Choosing the right Chart Type
Analyzing various types of Charts
Importance of looking good
Changing Chart Elements
Fine tuning the Chart
5. COMPONENTS SHOWING PERFORMANCE AGAINST A TARGET
Showing performance with variances
Showing performance against organizational trends
Using a Thermometer chart
Using a Bullet chart
6. ADDING INTERACTIVE CONTROLS TO THE DASHBOARD
Understanding Form Controls
Adding a control to a worksheet
Using the various Form Control buttons (Combo Box; List Box; Spin Button) by incorporating Excel functions
Controlling multiple charts with one selector
7. PIVOT TABLE DRIVEN DASHBOARDS
Customizing the Pivot Table
Filtering your data
Various views within the Pivot Table
8. ADDING INTERACTIVITY WITH SLICERS
Creating and formatting a slicer
Using Slicers as Form Control