Excel Dashboards #1 Guidelines & Techniques

In this recording of a live webinar I ran in March 2023 you will learn about guidelines and technique for building dashboards in Excel.

Use the button below the video to download the materials.

Download Materials

This is the first session is a series of five webinars on Excel dashboards. The other four sessions are paid sessions.

In this free session we will focus on chart and dashboard guidelines plus some techniques used to create better dashboards. We will also look at creating a bullet chart which is a better alternative to a gauge or dial chart.

The session covers

  • general chart guidelines
  • questions to ask yourself to develop better charts
  • best charts to use
  • charts and formats to avoid
  • using text boxes on dashboards
  • how to create small charts
  • chart templates
  • lining up and re-sizing charts
  • useful number formats for dashboard charts
  • using the #N/A error with charts
  • how to create a bullet chart

As always I will share a few other tips.

One Minute to Excel #28 – Eight More Charts in a Minute

Fast mouse work to the rescue

OK in the last video I cheated and used Sparkline charts to create 8 charts in a minute.

This time I set myself a real challenge to create 8 real charts in a minute.

Its close – check out the video and learn a few useful techniques.

One Minute to Excel #27 – Eight Charts in a Minute

Sparklines to the rescue

Let’s see if I can create 8 charts in a minute – one for each state and territory in Australia

I cheat a little bit and use Sparkline charts. If you haven’t seen Sparklines before check out the video.

Missing Chart Data in Excel

Another macro to the rescue

The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.

Free Webinar Recording – Interactive Excel Chart

Feedback score 93.7% based on 58 responses

In September 2019 I re-ran my very first webinar that was based on an August 2012 INTHEBLACK feature article. I have provided a detailed pdf manual and example files which you can download at the link below.

This session covers

  • using controls with charts to make the user experience easier
  • tips for using controls on spreadsheets
  • flexible formula techniques to make reports more dynamic
  • a technique to stop figures plotting on charts
  • using range names with controls and reports
  • general charting tips

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Download Materials

Free Excel Webinar Recording – Financial Functions Part 2

Feedback score 87%

In July 2019 I explained and demonstrated a number of Excel’s  financial functions – see below for more details.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

In July 2019 following on from the response and feedback from my June webinar, I covered more financial functions. These are more related to comparing and analysing cash flows. I have also included a couple of requested schedules.

This session will cover the following functions

  • NPV – Net Present Value of regular cashflows (learn to trick to using it correctly)
  • XNPV – Net Present Value of irregular cashflows
  • IRR – Internal Rate of Return of regular periodic cashflows
  • XIRR – Internal Rate of Return of irregular periodic cashflows
  • Discounted Payback period schedule
  • Flexible Loan schedule – handles lump sum payments

As with all my sessions, I will throw in a few other shortcuts along the way.

Free Excel Webinar Recording – Financial Functions Part 1

Feedback score 93%

In June 2019 I explained and demonstrated a number of Excel’s  financial functions – see below for more details.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

These functions take three or more arguments, but there are a few things you need to know to use them correctly. Most involve loans or calculations associated with the time value of money.

This session covered the following six functions

  • PV – Present Value of future regular cash outflow
  • PMT – periodic loan repayment calculation
  • CUMIPMT – cumulative interest – great for loan schedules
  • RATE – interest rate
  • FV – future value of regular investment
  • NPER – number of periods

The session finishes with a loan model that calculates the “missing” value for a loan scenario based on two out of three inputs. The IFERROR function is also discussed.

Free Excel Webinar Recording – Dashboard #1 Guildelines & Techniques

Feedback score 92%

In May 2019 I examined and demonstrated some guidelines and techniques for creating charts for dashboards.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

In this session I focused on chart and dashboard guidelines plus some techniques used to create small charts for dashboards.

I also looked at creating a bullet chart, which is an alternative to gauge, speedo or dial chart.

The session covered

  • general chart guidelines
  • questions to ask yourself to develop better charts
  • best charts to use
  • charts and formats to avoid
  • using text boxes on dashboards
  • how to create small charts
  • chart templates
  • lining up and re-sizing charts
  • useful number formats for dashboard charts
  • using the #N/A error with charts
  • how to create a bullet chart

As always I shared a few other tips.

Free Excel Webinar Recording – Excel Yourself 2018

Feedback score 89%

In December 2018 I reviewed four of my articles from the INTHEBLACK magazine from 2018. Download the materials using the button below and watch the video.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

Includes extra content not included in the magazine.

This year’s smorgasbord webinar includes

  • how to handle validations
  • dynamically highlighting the current row in a table (uses a macro)
  • centralising logic – how and why to do it – alternatives to the IF function
  • creating a slope chart (includes a macro to speed up the process)

As always there will be a few more tips and tricks shared in the session.

Horizontal or Vertical Progress Bar in Excel

Conditional Format or Sparkline take your pick

In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.