Category Archives: Videos

Posted on

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.

Related Posts

Posted on

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.

Related Posts

Posted on

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.

Related Posts

Posted on

Format As Table Webinar Recording 2023

In this session you will learn all about Excel’s formatted tables. Using Formatted Tables is an essential skill in Excel. Use the buttons below the video to download the materials and completed file.

Download Materials

Download Completed File

Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.

Formatted tables can allow you to create powerful reports like those in a relational databases.

Topics covered

  • advantages and limitations of formatted tables
  • keyboard shortcuts
  • using formatted tables with formulas
  • solutions to some of the limitations of formatted tables
  • using range names with formatted tables
  • using formatted tables with data validations
  • creating a running total
  • using PivotTables
  • Relationships (Data tab)
  • introduction to dynamic arrays

As always I shared a few other tips.

Related Posts

Posted on

One Minute to Excel #30 – Extract Data Based on Sheet Name

Shortcut to speed up name creation

Here’s a technique I use a lot to speed up report development.

Sheet names have to be unique, so they can’t be duplicated. This makes them great for department names or states.

This short video combines a few techniques to extract from a data set based on the sheet name.

All in less than a minute.

Related Posts

Posted on

Selecting a Column Range within a Merged Cell in Excel [video]

I am not a fan of the merged cell format. It causes more problems that it solves. One issue you will face is trying to select a single column range within a range that has a merged cell. Here is how you handle it.

This post is a video post as it easier to show the problem and the solution in a video.

Related Posts

Posted on

One Minute to Excel #29 – Nine Names in a Minute

Shortcut to speed up name creation

In this short video I demonstrate how to create range names quickly based on labels. Range names are a powerful formula feature. I also demonstrate their use.

Related Posts

Posted on

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.

Related Posts

Posted on

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.

Related Posts

Posted on

One Minute to Excel #26 – Fix AutoSum’s Weakness

Get the right range every time

AutoSum’s cryptonite is a blank cell – it stops AutoSum in its tracks every time.

Here’s how you can avoid AutoSum’s blind spot.

Related Posts

Posted on

Data Entry Formats in Excel [Video]

Format as you go

When you enter data into Excel you can format as you type. See how in this short video.

Related Posts

 

Posted on

One Minute to Excel #25 – Find the breakeven point

Goal Seek solution

If we have a simple Profit and Loss and we want to figure out a breakeven point, we can use Goal Seek to find it.

We can also use it to see sales required to meet a certain profit.

All in less than in minute.

Related Posts

Posted on

One Minute to Excel #24 – 1,000 random dates

A RANDARRAY solution

Let’s say we need to do some testing and we need 1,000 random dates in 2022.

We can use a new function to make this easy to create and easy to change.

RANDARRAY usually works with numbers but in Excel dates are numbers, so we get it to create random dates for us.

I set myself a challenge to do this in less than minute – see how I went in the video below.

Related Posts

Posted on

One Minute to Excel #23 – Text numbers to real number again

Another solution

One thing you learn quickly about Excel is that there are many ways to achieve the same outcome.

This is another example. In an earlier video I showed two separate ways to convert text numbers into real numbers.

Well, I have just learned another way. An Excel MVP Rick Rothstein shared a third way.  I tweaked it and share a keyboard shortcut to do it as well.

Hope you enjoy it.

Added Nov 27, 2021

If you use Text to Columns for other conversion in the same session, you may need to use Alt A E W F as the Delimiter defaults may interfere with the conversion.

 

Related Posts

Posted on

One Minute to Excel #22 – Normalise a budget

Power Query can be quick

The standard budget layout isn’t great for pivot tables. You can easily and quickly convert it in to data using Power Query

See now in this short video.

Related Posts

Posted on

One Minute to Excel #21 – Hiding Zeroes

Two ways

If you want to display a blank cell instead of a zero there are two ways to do it.

See both in this short video.

Related Posts

Posted on

One Minute to Excel #18 – Convert real numbers to text numbers

Two techniques

When you are working with text numbers in tables sometimes you need to convert real numbers into text numbers to do look ups.

There are at least two ways to do this.

Let’s see how to convert real numbers into test numbers, real fast.

Related Posts

Posted on

One Minute to Excel #13 – Create and Use a Check Box

Tick it to the next level

A check box is an easy interface to create and use.

See how to add one to a sheet and use it in a calculation.

Related Posts

Posted on

One Minute to Excel #12 – Hide Cell Entries

It is a special format

You can’t hide a cell, but you can stop the cell value from displaying on the sheet.

It involves a custom number format.

Related Posts

Posted on

One Minute to Excel #11 – Add % to a Pivot Table

It is not intuitive

Often people perform calculations off to the right of Pivot Tables to calculate percentages.

In this short video I show you those calculations can be done inside the Pivot Table itself.

The solution is not intuitive, but it is easy.

This example builds upon the previous One Minute to Excel post.

Related Posts

Posted on

One Minute to Excel #10 – Create a Pivot Table

It is easy

Note sure why, but Pivot Tables are often seen a “hard” or “advanced”.

In the short video we see how easy they are.

Oops – I go over my one minute time limit by a few seconds because I format the Pivot Table as well.

Related Posts

Posted on

One Minute to Excel #9 – Drop down list in a cell

Three techniques

This short video covers different ways to insert a drop down list into a cell.

I go over my one minute time limit by a couple of seconds, but I do cover three techniques.

Related Posts

Posted on

Excel’s Formatted Tables [Video]

Free webinar recording

Learn about Excel’s powerful Table feature that treats tables like little databases.

I ran this free live webinar in March 2021.

You can access the pdf manual and example file by clicking the button below the video.

Download Materials

Related Posts