Impressive Excel DashboardCourse Duration: 2 Days
- Have you ever been swarmed by a table of data of an excel business report?
- Does the spreadsheet able to assist you to make effective and quick business decision?
- Does the function of Pivot Table provide enough assistance to you to dissect the information you need?
If you are still doubtful with the answers in your mind, Impressive Excel Dashboard is the course just for you!
Dashboard give you an unprecedented visibility into your work. You can get a quick, visual status update on your project or drill down into important details, all in one place, easily. Dashboard can boost you and your organization's speed by allowing you and your team to see more, manage more, communicate more and do more. Dashboard tracks KPIs, metrics and other data point in one visual, central place, giving you a high level view of work, helping you to make effective decisions. It also simplifies complex data and provide an at one glance view of current status or performance in real time.
AudienceThis course is best suited to anyone in Business Intelligence, data managers, data analysts or project managers. This course would also suit anyone looking to extend their knowledge of Excel to understand some of the more advanced features and how they can be used to work together.
PrerequisitesBasic knowledge of Windows is essential with the following pre-requisites:
- Create basic formulas - Addition, Subraction, Multiplication and Division
- Use basic functions - AutoSum, Count, Max, Min and Average functions
- Print a spreadsheet with headers and footers added
- Create a basic chart
- Create a spreadsheet with simple formatting
- Clean duplicate records
- Use data validation command
- Use basic conditional formatting
- Create basic PivotTable and PivotCharts
- Must possess formulas knowledge - basic COUNTIF, SUMIF, IF and VLOOKUP
Course ObjectivesUpon completion of this program, participants should be able to know:
- Creating an impressive dashboard
- PivotTable and PivotCharts
- useful formulas
- Conditional Formatting
- Form Controls
- Working with Charts
- Working with Sparklines
- Design and Layout of Dashboard
Module 1: What is a Dashboard?
- Why Use a Dashboard?
- Common Features of a Dashboard
- Why use Excel?
- Preparing to Build a Dashboard
Module 2: PivotTables and PivotCharts
- What is a PivotTable?
- Creating a PivotTable
- Formatting a PivotTable
- Refreshing a PivotTable
- Using Slicers to Manipulate PivotTables
- Using Timelines to Filter Dates (Excel 2013 Only)
- Creating PivotCharts
- PivotTables, PivotCharts and Slicers in Dashboard
Module 3: Useful Functions
- Using NESTED IF Statements
- Use COUNTIF & SUMIFS
- Using EDATE Function
- Use the INDEX function to retrieve information by location
- The INDE Array form
- Use the MATCH Function to Locate Data Position
Module 4: Conditional Formatting
- Conditional Formatting
- Conditional Formatting on PivotTables
- Conditional Formatting with Dashboards
Module 5: Forms Controls
- Adding Controls
- Understanding Controls and How They Work
- Using Form Controls on Dashboard
Module 6: Working with Charts
- Creating Charts
- Formatting Charts
- Secondary Axes
- Combination Charts
- Creating Chart Templates
Module 7: Working with Sparklines
- What are Sparklines
- Creating a Sparklines
- Modifying a Sparklines
- Customizing a Sparklines
Module 8: Design & Layout
- Gridlines & Outlines
- Lining Up Excel Objects
- Theming a Dashboard