Do you spend hours just to update your Excel reports weekly or monthly?

Then, you are doing it wrong!

All you need are a few key rules for setting up your workbooks and in most cases you should be able to simply click the refresh button or import your new data and the report will update. Job done!

Are you frustrated with your lack of skills in creating dashboards that are effective, insightful and actionable?

Then, this is the course made just for you!

Data visualization is a critical skill in today’s data-driven world.

As data continues to grow at an exponential rate, data visualization helps the stakeholders in a company who aren’t data savvy to digest and understand big data.

Excel is an excellent tool to make powerful dashboards that can provide analysis, insights and alert managers in a timely manner.

Who should attend?

This workshop is crucial for analysts, managers, reporting professionals, business owners, executives, and anyone responsible for preparing Excel-based dashboards, scorecards or KPI reports. This training is highly recommended for:

• Directors
• General Managers
• Chief Financial Officers
• Finance Managers
• Accountants
• Financial Planning & Analysis
• Marketing Managers
• Sales Managers
• HR Managers
• Business Owners
• Anyone who deals with business, management and financial reporting

• What is a dashboard?
• Purpose and benefits of dashboards
• Understanding dashboard design principles
• Layout, color and display
• Common mistakes when building dashboards
• Choosing the right data visualization to communicate information effectively
• Displaying trends with charts

• The anatomy of an Excel chart
• When to use a secondary axis
• Combining two chart types
• Formatting tricks
• Adding icons and images to dashboards

• In-cell charts
• Step charts
• Frequency distribution
• Speedometers
• Bullet graphs
• Waterfall chart
• Pyramid chart and funnel charts

• Actual vs budget (target) charts – Floating Markers
• Band chart – show performance against target range
• Conditional colors in column chart
• Panel charts

• Creating sparklines – line, column and win/loss
• Sparkline formatting and options
• Sparkline tips and tricks
• Applying conditional formatting
• Data bars, colour scales and icon sets
• Conditional formatting options
• Using symbols to enhance reporting

• Understanding your data
• Organizing data
• Tools and tricks to clean data before using it to build a report or dashboard

• Naming cells and ranges
• Creating Excel tables
• Applying table names
• Using structured references in formula

• Learn to nest functions together to create robust formulas
• Use IF, Nested IFs and IFS (Excel 2016) for logical test with single or multiple conditions
• Embed AND or OR function in IF for robust logical tests
• Trap and handle errors produced by other formulas or functions with IFERROR
• Use CHOOSE to return a value from an array based on index number
• Aggregate data with single criterion using SUMIF, COUNTIF, AVERAGEIF
• Tabulating information using multiple criteria with SUMIFS, COUNTIFS, AVERAGEIFS
• Lookup and retrieve data from a specifi c column in table using VLOOKUP
• Use MATCH to find the relative numeric position of an item in a range
• Use INDEX to extract a value from a table (or range)
• INDEX and MATCH – powerful combo that has more flexibility and speed
• Why INDEX MATCH is better than VLOOKUP
• Using ROW(S) and COLUMN(S) for indexing
• Rank and sort your data using RANK, LARGE and SMALL
• Use ‘database functions’ DSUM, DCOUNT and DAVERAGE to filter information in a list or database based on set criteria
• Using TEXT and CONCATENATE to string numbers and text together to show in a dashboard or chart.