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.

Excel Power Query and Multiple Files 2023

In this recording of a live webinar I ran in late January 2023 you will learn how to import multiple files in a single Power Query. If you use data at all then Power Query is an essential skill to possess.

Use the buttons below the video to download the materials.

Download Materials

Building on the skills covered in the Introduction session, we will start working with multiple files. For example you may have 12 separate CSV files in a folder. All with the same layout, one for each month of the year. Power Query can import all 12 files as if they were a single file and create a table for the whole year

This session covers
• importing multiple CSV files from a folder
• a technique to capture the file name in a field (column)
• importing multiple Excel files
• merging data from multiple tables
• Extracting header information into a column from multiple CSV files

As always, I shared other tips and tricks along the way.

Introduction to Excel Power Query 2023

In this recording of a live session ran in late January 2023 you will learn how to automate data importation in Excel with Power Query. If you use data at all then Power Query is an essential skill to possess.

Use the buttons below the video to download the materials.

Download Materials

Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources.

Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.

This session covers

  • fixing dates so that Excel can recognise them
  • formatting columns as text – retaining leading zeroes in CSV files
  • deleting unwanted rows and columns from your data
  • removing leading and trailing spaces
  • populating blank values with zeroes
  • populating blanks with entries from above
  • correcting trailing minus signs
  • unpivot a report – converting a report layout into a table layout
  • converting a MYOB report into a data table
  • capture header information in a column

As always, I shared other tips and tricks along the way.

Excel Power Query Saving Issue

There was a recent discussion of this topic on LinkedIn. When you are in the Power Query window you can’t save the query. If you Close and Load and Excel crashes, you could lose your work. If you have spent some time working on a query this can be frustrating. Here’s how you can capture the query before using Close & Load.

Excel Power Query and Multiple Files 2022

Webinar recording from April 12, 2022

This is the recording of the second free Power Query webinar I ran in 2022.

You can watch the first one at this link.

In this session we see how to import multiple files in one Power Query. We look at importing CSV and Excel files.

You can download the materials, including a detailed pdf manual using the button below.

Download materials

Introduction to Excel Power Query

A free webinar recording

Earlier this month I ran a free webinar on Excel Power Query.

This is the recording of the session with no editing, no time limit and no sign up required.

Power Query is the best way to import data into Excel. It is also the data importation system used in Power BI, so everything you learn in Excel can be applied to Power BI.

You can download the materials, including a detailed pdf manual at the button below.

Please enjoy, learn and share.

Download webinar materials

 

Power Query Zoom

I used this today in a live webinar.

I zoomed into the Power Query window in Excel to make it easier to see.

Ctrl + Shift + + (plus)

Excel Power Query and Multiple Files [one hour video]

Includes detailed pdf manual

Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.

Power Query can automate the importation process. It is now a vital skill to possess.

This is a follow up session to the Introduction session which you can view here.

This session focuses on importing multiple files from a folder. The session uses CSV and Excel files plus Excel tables.

This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.

(Apologies I did have a coughing fit – I thought I had turned off the microphone but I hadn’t.)

Use the button below to download the pdf manual and example files so you can follow along.

We need to get the word out on this powerful Excel feature.

(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)

Download Materials

Introduction To Excel Power Query [one hour video]

Includes detailed pdf manual

Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.

Power Query can automate the importation process.

It is now a vital skill to possess.

This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.

Use the button below to download the pdf manual and example files so you can follow along.

We need to get the word out on this powerful Excel feature.

(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)

Download Materials

Power Query shortcut for Adults

The shortcut to display the Queries & Connections Task Pane is easy to remember. It is Adults Only!

To display or hide the Queries & Connections Task Pane you use. These keys are pressed in sequence, not held down.

So only adults allowed in Power Query.

 

Power Query feedback

Received this email after a recent Power Query session – some things may take a few goes to understand, that’s OK, that’s how we learn.

Free Webinar Recording – Introduction to Power Query

Feedback score 94.5% based on 91 responses

In October 2019 I ran my Introduction to Power Query webinar for free (previously it was a paid session). I want to get this information out to as many people as possible. please share this resource with colleagues and your network.

The detailed pdf manual and example file can be downloaded by using the button below. Content listed below the video.

Download Materials

Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources. Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.

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.

This session covers

  • fixing dates so that Excel can recognise them
  • formatting columns as text – retaining leading zeroes in CSV files
  • deleting unwanted rows and columns from your data
  • removing leading and trailing spaces
  • populating blank values with zeroes
  • populating blanks with entries from above
  • correcting trailing minus signs
  • unpivot a report – how to convert a report layout into a data table layout
  • converting a MYOB report into a data table