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.
Category Archives: Videos
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.
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.
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.
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.
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.
One Minute to Excel #8 – Incrementing a Long List of Dates
It is easy when you know how
In this short video I cover how to increment dates in long ranges.
It uses a little know dialog.
One Minute to Excel #7 – Copy Date down a Long Range
Quick and easy technique
In this short video I cover how to insert dates in long ranges.
Its simple and quick.
One Minute to Excel #6 – Add Subtotals Automatically
Built-in feature
In this short video I cover how to insert automated subtotals.
Its a built-in feature, that is easy to use.
One Minute To Excel #5 – Extracting Unique Entries
Three techniques
In previous videos I worked with duplicated entries.
In this video I demonstrate how to extract unique entries from a list.
Three different ways in 60 seconds.
Off we go!
Related Posts
One Minute to Excel #3 – Filling in the blanks
No blank looks
Imported data often has missing entries you need to populate.
You can use Power Query, but that duplicates the table.
This technique works on the existing table and is quick and easy to apply once mastered.
Start the clock!
One Minute to Excel #2 – Identify Duplicates
Short, sharp video tips
In the previous video I removed duplicates, in this video we identify duplicates using Conditional Formatting.
I identify the duplicates twice in a minute in this video.
The clock is ticking.
One Minute to Excel #1 – Remove Duplicates
Working against the clock
I have started a new series of short videos showcasing Excel’s features that work in less than a minute.
There is a countdown to see if I can do it.
In this first one I remove duplicates in less than a minute.
Free Webinar Recording – Excel Format as Table Feature
Feedback score 95% based on 58 responses
In March 2020 I presented a session on Excel’s Format as Table Feature. I covered it’s advantages and how to use it to improve your Excel files.
The detailed pdf manual and example file can be downloaded using the button below. Content is listed below 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.
In this session you will learn all about Excel’s formatted tables.
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)
As always I will share a few other tips.
Free Webinar Recording – Excel Budget Challenge Solution
Feedback score 92% based on 83 responses
In February 2020 I reviewed my solution to an Excel Budget Challenge from late 2018. This included some advanced Excel budget techniques.
The detailed pdf manual and example file can be downloaded using the button below. Content listed below 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.
This session includes a lot of useful content and it brings together a number of different techniques. The content is worthy of a paid session but since it was a public challenge I am presenting the session for free.
The materials includes the challenge documentation, the pdf manual and the solution file. I assume you have read the documentation. You also receive the blank Excel file if you want to create your own solution.
Techniques/topics covered include
- using INDEX-MATCH (better alternative to VLOOKUP)
- 3-D formulas to summarise all report sheets and techniques to make using them easy
- using a reporting template to speed up creation
- validations
- extracting sheet names
- automating reports
Free Webinar Recording – Date and Time Calculations
Feedback score 95% based on 61 responses
In January 2020 I looked at solving some of the frustrations date and time calculations can cause in Excel.
The detailed pdf manual and example file can be downloaded using the button below. Content listed below 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.
Discover the functions that handle different types of calculations.
See how to avoid some of the frustrations that dates and times can cause. Topics covered include:
- adding months to a date
- formulas for the first and last day of the month
- using today’s date in a formula
- handling days of the week calculations
- automating dates in headings
- how to avoid the traps in time calculations
- remove times from dates
- generating random dates (for testing)
As always there will be a few other tips and tricks shared during the session.
Free Webinar Recording – Excel Yourself 2019
Feedback score 93% based on 41 responses
In December 2019 I reviewed four of my articles from 2019 plus some new content.
The detailed pdf manual and example file can be downloaded using the button below. Content listed below 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.
This year’s webinar includes
- a new logic function IFS
- how to do budget allocations across months using the MOD function
- how to do complex monthly budget allocations
- using helper cells to achieve advanced conditional formats
As always there will be other tips and tricks shared along the way.
Free Webinar Recording – Excel Chart Tips and Tricks
Feedback score 94.5% based on 59 responses
In November 2019 I re-ran my Excel Chart Tip sand Tricks session.
The detailed pdf manual and example file can be downloaded using the button below. Content listed below 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.
This webinar is focused on showing you how to create and modify charts in Excel 2016 with a minimum of fuss.
The Chart interface changed in Excel 2013 and I take you through some of the changes.
You will see best practice design and formatting techniques demonstrated and explained.
See how to create dynamic charts that automatically change based on selections made or data added.
Learn about the feature added in Excel 2010 called Sparkline charts.
I also include some useful date-based functions.
Free Webinar Recording – Excel Formula and Function Tips
Feedback score 95.8% based on 66 responses
In October 2019 I re-ran my Excel Formula and Function Tips session.
The detailed pdf manual and example file can be downloaded by using the button below. Content listed below 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.
This session covers lots of tips, tricks and techniques to speed up the formula creation process. You will learn:
- how to quickly start a formula
- the benefits of the numeric keypad
- to use AutoComplete to save typing
- how to easily insert $ signs to fix references
- about the calculation sequence
- how to use the colours Excel displays when editing formulas
- the tricks on selecting large ranges
- using formulas and Format as Table together
- a helpful technique when working with ranges in other sheets
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.
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
Free Webinar Recording – Excel Formatting Tips
Feedback score 94% based on 70 responses
In October 2019 I re-ran my Formatting Tips session. The detailed pdf manual and example file can downloaded by using the button below. Content listed below 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.
This session covers:
- a format to avoid and the one to use in its place
- keyboard and mouse shortcuts
- how to use and create customer number formats
- about Styles and how they can make your formatting more consistent
- that colours can be used to filter, sort and even find things in your sheets
- how to stop zeroes displaying plus other general formatting tips
- a quick demo of Flash Fill
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.
Free Webinar Recording – Recorded Macros Tips Tricks and Traps
Getting started with recorded macros
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.
Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.
Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.
This is the first in a series of webinars dedicated to macros. Future paid sessions this month (August 2019) will expand on the techniques taught in this session. Click here to register.
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.
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.
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.
Related Posts
Twelve months of free Excel webinars
Free monthly Excel webinars
This month celebrates 12 consecutive free monthly Excel training webinars. One a month since August 2018.
(I have kept adding the new free sessions to the list)
You can view the recordings and download the materials using the table below. Sorted by Skill Level.
17 hours of free, practical CPD. Remember to keep your own records of recording viewing for CPD purposes.
Please share with the your network, enjoy!
Latest Free Excel Webinars
| Free Courses (One hour each) | Level | Recording Link | Materials |
|---|---|---|---|
| Excel Keyboard Shortcuts | Beg | Recording | Materials |
| Excel Mouse Shortcuts | Beg | Recording | Materials |
| Excel Formatting Tips | Beg | Recording | Materials |
| Excel 2016 Conditional Formats | Int | Recording | Materials |
| Excel Date and Time Calculations | Int | Recording | Materials |
| Excel Custom Number Formats | Int | Recording | Materials |
| Excel Financial Functions Part 1 | Int | Recording | Materials |
| Excel Financial Functions Part 2 | Int | Recording | Materials |
| Dashboard Guidelines & Techniques | Int | Recording | Materials |
| Format As Table Features | Int | Recording | Materials |
| Excel 2016 Chart Tips & Tricks | Int | Recording | Materials |
| Excel Yourself 2018 | Beg/Int/Adv | Recording | Materials |
| Excel Yourself 2019 | Int/Adv | Recording | Materials |
| Mastering Excel's Form Controls | Adv | Recording | Materials |
| Excel 2016 What-If Techniques | Adv | Recording | Materials |
| Excel Budget Challenge | Adv | Recording | Materials |
| Excel Recorded Macros | Adv | Recording | Materials |
| Interactive Excel Chart | Adv | Recording | Materials |
| Introduction to Power Query | Adv | Recording | Materials |
Level Beg = Beginner, Int = Intermediate, Adv = Advanced
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.
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.
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.
Multiple Entries in an Excel Filtered List
Ctrl key to the rescue
When a list is filtered you are only seeing the rows that match the filter. The other rows are still there, just hidden. If you want to make the same entry in a group of filtered cells you can’t use the fill handle to drag and copy as you will overwrite the hidden rows. There is an easy way to do it.
Free Excel Webinar Recording – Custom Number Formats
Feedback score 92%
In April 2019 I demonstrated many of Excel’s Custom Number Formats.
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.
In this brand new webinar we examine Custom Number Formats which hide away at the bottom of the Number Format tab. These custom made formats offer some useful techniques.
They can
- display negatives in red and with brackets
- format mobile phone numbers correctly
- display numbers and text together and still perform calculations
- hide zeroes
- display rounded numbers to thousands and millions
- display the day of the week
- create customised dates
- be saved and be available in new sheets and files using a Template
- be saved as a Style
See examples and demonstrations of many different custom number formats and learn how to create your own.
As always I will share a few other tips.
Moving Images in Excel
Make the Shift
When you are moving images, charts or other objects that float above the grid you can use the Shift key to make it easier.