Broaden Your Excel Skill Set. Speed Up Your Reports.

Build your confidence to write robust & dynamic formulas that reduce your turn-around time while creating better, reliable and dynamic management reports in Excel

Let’s face it friends!

  • Tired of spending unduly lots of time creating and updating reports in Excel–and deep down in you, you’re saying there must be a better way?
  • How about those end-month late nights in the office?

Recently as I was doing expectation gathering for one of my clients, one participant told me: William, I want my weekend back!

And yes…

That is probably true for you. Are reports stealing the only time you have for your family or just to have time for self?

That is where our courses come in –we’ll do anything to equip you with skills that cut by more than 50% the time you spend creating reports and reduce  Excel frustrations

These among other reporting hurdles can be a thing of the past, but only if you take action

Here is What You’ll Get

Get ready to say “Wow, I didn’t know I could do THAT!”

What did our past clients think?

financial dashboards in excel

“Your dashboard training has already saved me a lot of time! I have used some tips which to present my funding analysis by location and staff type. Every month I just paste the new data and my source table self-adjusts. Magic! I then click the refresh button and my management dashboard is ready, just like that!”


Yakub Mohamed, Finance Officer, WFP

Content

For over 7 years, our instructors have trained thousands of working professionals (PwC, Unilever, NIC Bank, Toyota Kenya, etc.)

We constantly ask them to describe the challenges and frustrations they face every day on the job. (You know, the things that make you want to pull your hair out because the boss is waiting for a report and it’s getting late in the night?)

We listened, helped them on actual work scenarios, and enriched our thoroughly researched content with similar examples (after anonymizing). You can be sure therefore that each of the coursers below is crafted to solve the exact (or similar) problems which you face at the office

This module is formula intensive and usually fits those who have never or barely interacted with VLOOKUP and Pivot Tables. You’ll also learn beyond basic concepts such as manipulating text and dates, customizing charts, working with inbuilt conditional formatting features among other concepts.

Pre-Requisite

A good foundation in Excel is essential for this course. Participants should not just be regular users of Excel but must be able to write basic formulas taught in the foundational level module.

Curriculum

  • Save loads of time dealing with blank cells in Excel –filling blanks
  • Identify and eliminate repeated records in Excel –learn more than 3 ways
  • Compare two lists of data -3 methodologies
  • Text extractions – combining and splitting columnar data using LEFT, RIGHT, MID, LEN, FIND, etc.
  • Nesting the IF function to segment data; combining the IF function with information functions such as ISBLANK, ISNUMBER, ISNONTEXT
  • Looking up data from a list using the VLOOKUP (and/or HLOOKUP) function; Create auxiliary columns needed for reporting and mapping/integrating different sources of data
  • Creating a drop down in Excel
  • Enhancing readability with named ranges; expandable named ranges with Excel tables
  • Using the SUMIF and COUNTIF functions for analysis
  • Intercepting and handling errors with IFERROR
  • The ins and outs of Pivot Tables (Slicers for interactive reports, grouping dates into quarters, show values as percentages, computing YoY% inside pivot reports, styling your pivot reports, working with pivot charts)
  • Various date scenarios
    • Counting working days with the NETWORKDAYS function
    • Determine a completion date or an invoice due date with the WORKDAY function
    • Calculating future and past dates using EOMONTH and EDATE
    • Compute the tenure of various employees using the YEARFRAC function
  • Dual axis charts with a secondary axis
  • Applications of the REPT function for in-cell charts
  • Special charts –mimicking a thermometer for Actual versus Budget analysis
  • Conditional formatting charts to display variances in different colours without manually colouring the bars
  • Applications of pre-built Conditional Formatting features –data bars for in-cell charts, evaluate performance (exceeded target, average, below target) given some thresholds using Icon Sets

In this module, you will find out the available advanced data analysis tools in Excel that will develop your skills to make yourself more marketable in the field of data analysis. You’ll also learn how to combine different features and nest various formulas for dynamic Excel reports

Pre-Requisite

The concepts taught here are advanced to the average Excel user.

You’re advised to get your act together with our Intermediate level concepts before enrolling in the Advanced module. This ensures you get the best value for the program you pay for.

Curriculum

  • Converting unusual date formats to proper dates in Excel -applying TEXT and DATE functions
  • Flexible lookup techniques –learn how to combine VLOOKUP with MATCH function for dynamic data analysis. No more manually typing which column in your function; Lookups involving multiple sheets
  • Nesting the MATCH function inside the INDEX function for flexible huge data analysis
  • Advanced logic in a business context (e.g. working out bonus payments linked to performance) – IF, AND, and OR functions
  • A primer on automating data cleaning and consolidation processes using Power Query
  • Practical applications of Pivot Tables in business
    • Grouping days invoice outstanding to segment them into ageing clusters
    • Interactive reports using Pivot Charts, slicers, and timelines; Connecting multiple reports to a single slicer
    • Creating custom-styled, professional slicers that match your brand
    • Using calculated fields to generate an income statement, compute variance analysis, calculate margins, etc.
    • Highlight pivot table results with conditional formatting
  • Conditional aggregation of data (make dynamic summary tables) –SUMIFS, COUNTIFS, SUMPRODUCT
  • Showing top/bottom performers using LARGE, SMALL, IF, RANK.EQ
  • What-if analysis –creating sensitivity tables
  • Creating combo charts
  • Making interactive charts
  • Highlighting the maximum or minimum points in a chart
  • Learn how to use conditional formatting to make key data stand out -apply custom formulas
  • Creating bullet charts for performance tracking
  • Special charts -Waterfall chart, KPI ring chart

Business managers need timely access to accurate data – presented in a clear and understandable way.

DCA’s Excel Dashboards course equips you with the skills needed to build informative, professional, visual-rich dashboards that will allow managers to step back from the details and see the key trends and relationships that drive their companies …with responsive features to switch views

Pre-Requisites

We believe in effective training and tangible results, hence, if you are a beginner, or even a regular user of Excel but are not familiar with VLOOKUP, basic charts and Pivot tables, this course is not for you. Completion of our Intermediate Excel course or its equivalent is important.

We like being honest -dashboard courses labelled for “all levels” end up being (as you already know) a frustration as you will end up just covering the basics!

Course Outline

  • Retrieving external data
  • Data cleansing procedures –fill blanks, cleaning duplicates, creating auxiliary columns to segment data
  • How to deal with dates in your Excel dashboard using the TEXT, YEAR, MONTH, TODAY
  • Working with linked shapes for stylish dashboards
  • Pasting picture links and use of the Camera tool
  • Special charts –Gauge, Waterfall, Bullet charts
  • The REPT function for in-cell charts
  • Creating miniature charts suitable for dashboards
  • Showing trends using Sparklines
  • Make important data to stand out using conditional formatting –traffic lights, positive and negative indicators
  • Using the camera tool and/or linked pictures
  • Create scrolling charts to present a long list of information
  • Decision-making functions: IF, CHOOSE
  • Conditional aggregation functions: SUMIFS, COUNTIFS
  • Showing top/bottom performers using LARGE, SMALL, IF, RANK.EQ
  • Advanced pivot charts techniques -the applications
  • The proximity principle
  • User requirement analysis
  • Dashboard final touches, e.g. making slicers static, disabling resize on charts and other shapes, freezing to disable scrolling
  • Transferring your analytics into your dashboard
  • Drop down lists
  • Applications of Form controls (option buttons, combo boxes) and linking these to charts or key metrics
  • Leveraging on slicers –connect to multiple reports and customize their look to brand them
  • Self-adjusting named ranges

Booking Info

Don’t miss out on these upcoming sessions

Save over 20% in our March 2019 sessions

Fee (VAT Incl.): KES 18,900 14,900

08mar(mar 8)8:30 am09(mar 9)4:00 pmIntermediate Excel TrainingFormula intensive class to ground you to achieve more for less time8:30 am - 4:00 pm (9) The Heron Portico Hotel, Nairobi

Fee (VAT Incl.): KES 24,500 19,600

22mar(mar 22)8:30 am23(mar 23)4:00 pmAdvanced Excel TrainingGo beyond VLOOKUP and learn to nest formulas for scalable analysis8:30 am - 4:00 pm (23) The Heron Portico Hotel, Nairobi

Fee (VAT Incl.): KES 41,900 34,900

13mar(mar 13)8:30 am15(mar 15)4:00 pmExecutive Excel DashboardsExcel Tools to Build Interactive Excel Dashboards Ground Up8:30 am - 4:00 pm (15) The Heron Portico Hotel, Nairobi

  • The discount applies only for classes within March 2019 and for full payments received at least 1 week before event date

  • The fee paid covers: Meals and refreshments, certificate, writing materials

  • IMPORTANT: Enrollment for any of the programs above STRICTLY closes 1 week to the course date

  • Carry your laptop along, installed with MS Office 2010, 2013, or (preferably) 2016

+254 726 11 5853

Fill out this form and start saving time!

Your name

Your email

Your phone number

Select your program of interest

Specific training needs

Key Trainer

excel dashboards trainer

HI, I’M WILLIAM.

I am a man on a mission –to help individuals and companies to use Excel better, improve their reporting processes and cut down on time spent generating reports.

When I started out back in 2012, I didn’t know much about Excel. Like most of you, I didn’t even know it was a major reporting tool in the workplace (never taught in college).

For some reason, my former boss, believed in me that I could deliver as an Excel Trainer. I took the challenge and I believe I didn’t disappoint (check recommendation)

Since then, I’ve trained hundreds of staff (PwC, World Vision, Unilever Tea, Gulf Energy, Toyota Kenya, NIC Bank, etc.) and developed numerous programs covering Excel, Power Query, Power BI and Power Pivot.

I hold a degree in Applied Statistics with Computing (BSc.) from Moi University, and a certificate on Analyzing and Visualizing Data with Power BI from Microsoft. Am a CPA Part 1. I’m also the author of this Excel Dashboards book