Login
Excel Yourself/Excel Yourself Webinar Series (13 hours)
Get access for free

Excel Yourself Webinar Series

Each year I review my published articles and present a few of them as a live webinar. I re-write the articles into a pdf manual and usually add in a few extra tips. The topics covered in each session are diverse - its a smorgasbord of Excel features, functions and techniques.

Many of the companion files have all the examples from the year's articles even though the topics aren't covered.

This series has all the webinars I have run starting with the most recent in December 2022.

2022 marked 20 years of Excel articles.

I cover all the years back to 2012. Includes 13 hours of video.
Enroll now

  • Free

Excel Yourself Webinar Series (13 hours)

See my magazine articles come to life with extra and updated content. From 2012 to 2022 - each module is a smorgasbord of Excel topics and skill levels. Lots of tips, tricks and traps explained in each module. Level: Beginner to Advanced Duration: 13 hours Price: Free
Buy now

Contents

Excel Yourself 2022

Each year I run a webinar to review a few of my magazine articles from the year and go into a bit more depth.

2022 marked 20 years of my Excel magazine articles.

In this session we will cover
  • Days and dates
  • Creating a vertical line on a line chart
  • Using Goal Seek and how to automate it
I will also share a few bonus tips and tricks.

Excel Yourself 2022 PDF manual
  • 988 KB
Excel Yourself 2022 webinar example file
  • 180 KB
EY2022 Video recording
  • 59 mins
  • 220 MB
Excel Yourself 2022 webinar example file completed
  • 202 KB

Excel Yourself 2021 - Two Parts

Excel Yourself 2021 Part 1 - All Versions

 Each year I run a webinar to review a few of my magazine articles from the year and go into a bit more depth. These days with the different versions of Excel I am running two webinars. One for all versions and one for the subscription version.

 In this session we will cover

- Creating a dynamic chart that updates automatically
- How and why to use IS functions
- Conditional formats using formulas
- Creating an Index/Contents sheet

Excel Yourself 2021 Part 2 - Subscription Version

In this session we will cover

- LET function using variables in formulas
- MINIFS calculation over the years
- Dependent drop-down lists
- Advanced LET Function solving a sorting issue  
Excel Yourself 2021 Part 1 PDF Manual
  • 662 KB
Excel Yourself 2021 Part 1 Example File
  • 219 KB
EY2021 Part 1 Recording
  • (1h 01m 27s)
  • 247 MB
Excel Yourself 2021 Part 1 Example File - Completed
  • 241 KB
Excel Yourself 2021 Part 2 PDF Manual
  • 555 KB
Excel Yourself 2021 Part 2 Example File
  • 219 KB
EY2021 Part 2 Recording
  • (1h 03m 04s)
  • 231 MB
Excel Yourself 2021 Part 2 Example File - Completed
  • 221 KB

Excel Yourself 2020 - Two Parts

I usually finish the year with a webinar based on my Excel Yourself articles from the CPA Australia INTHEBLACK magazine. This year I have decided to do TWO webinars based on my 2020 articles. 
 
The first session covers topics applicable to all Excel versions. 
 
The second session is applicable to just the subscription version. I will cover dynamic arrays, including the six new functions plus XLOOKUP. 

Excel Yourself Part 1

In this session which applies to all Excel versions we will cover
  • imitating a Sparkline chart
  • performing a flexible conditional count
  • using Textboxes and a Linked Picture
  • using Power Query with an input table 
 

Excel Yourself Part 2 

In this session which is for the subscription version of Excel only, we will cover
  • an introduction to dynamic arrays
  • two dynamic array functions UNIQUE and FILTER
  • four more dynamic array functions SORT, SORTBY, SEQUENCE and RANDARRAY
  • the XLOOKUP function

Excel Yourself 2020 Part 1 PDF Manual
  • 618 KB
Excel Yourself 2020 Part 1 Example file
  • 164 KB
Excel Yourself 2020 Part 1 completed example file
  • 198 KB
Excel Yourself 2020 Part 1 Video
  • (1h 07m 03s)
  • 233 MB
Excel Yourself 2020 Part 2.pdf
  • 638 KB
Excel Yourself 2020 Part 2.xlsb
  • 160 KB
Excel Yourself 2020 Part 2 20201217 completed.xlsb
  • 163 KB
Excel Yourself 2020 Part 2 Video
  • (1h 10m 00s)
  • 232 MB

Excel Yourself 2019 Materials And Video

In December 2019 I reviewed four of my articles from 2019 plus some new content. 
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.
Excel Yourself 2019.pdf
  • 444 KB
Excel_Yourself_2019.xlsb
  • 130 KB
Excel_Yourself_2019_completed_20191217.xlsb
  • 130 KB
Excel Yourself 2019.mp4
  • (1h 06m 38s)
  • 244 MB

Excel Yourself 2018 Materials and Video

In December 2018 I reviewed four of my articles from the INTHEBLACK magazine from 2018. Use the buttons below to download the materials and watch the video.

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.
Excel Yourself 2018.pdf
  • 803 KB
Excel_Yourself_2018.xlsb
  • 216 KB
Excel_Yourself_2018_20181213_complete.xlsb
  • 227 KB
Excel Yourself 2018.mp4
  • 59 mins
  • 232 MB

Excel Yourself 2017 Materials And Video

In March 2018 I revisited four of my magazine articles from 2017 and added some extra content.  The Excel file includes the examples from ten of my articles from 2017.

This session covers four of my articles from 2017.
  • XNPV and XIRR functions
  • Moving annual totals
  • Advanced Filter automation
  • TEXTJOIN and CONCAT functions (new to some versions of Excel 2016)
Content applies to Excel 2007 and later versions, except the two new Excel 2016 functions. As always a few extra tips and tricks are shared along the way.
Excel Yourself 2017.pdf
  • 828 KB
Excel Yourself 2017.xlsm
  • 718 KB
Excel Yourself 201720180321.xlsm
  • 733 KB
Excel Yourself 2017.mp4
  • 58 mins
  • 176 MB

Excel Yourself 2016 Materials And Video

This is my annual smorgasbord session from December 2016. I revisit five of my magazine articles from the year and add some extra content. We cover a couple of functions; a filtering solution; how to reduce file size plus a feature added in Excel 2013 that can read your mind (sort of).

There are two dates to choose from – content is the same on both days. If you can’t attend you can still enroll and you will receive the recording link after the event.

This session covers five of my articles from the year. Topics are:
  • the new Flash Fill feature (Excel 2013) – why, how and when to use it
  • how to quickly adjust multiple calculations using a factor
  • the binary file file type – advantages and disadvantages
  • a reporting solution using the INDIRECT function
  • Slicers – the easy way to filter data
Excel Yourself 2016.pdf
  • 946 KB
Excel Yourself 2016.xlsm
  • 2.28 MB
Excel Yourself 2016.mp4
  • 58 mins
  • 166 MB

Excel Yourself 2015 Materials and Video

The January 2016 free session covered five of my INTHEBLACK magazine articles from 2015.

Topics covered:
  • Displaying numbers as text, but still performing calculations with them
  • Identifying possible duplicates when data is missing
  • Converting a single budget model to handle multiple scenarios
  • Using Styles
  • Reporting on multiple tables in Excel 2013 without VLOOKUP (requires Excel 2013 or later)
Excel Yourself 2015.pdf
  • 966 KB
Excel Yourself 2015.xlsm
  • 1.34 MB
Excel Yourself 2015 - complete.xlsm
  • 1.36 MB
Excel Yourself 2015.mp4
  • 57 mins
  • 111 MB

Excel Yourself 2014 Materials and Video

In December 2014 I reviewed 5 of my 11 articles that appeared in the CPA Australia INTHEBLACK magazine during the year. The webinar’s companion Excel file contains 10 out of the 11 Excel examples from the 2014 articles.

The session covered the following smorgasbord of topics
  • a macro to unhide an individual column on the left of the active column but on the right of a large group of hidden columns
  • amending the RANK function to provide a sequential ranking – no ties
  • creating unique codes from duplicate codes so each can be accessed individually
  • how to create a bullet chart (better and smaller than a speedo or gauge chart)
  • how to use formulas to create a data table from badly laid out data 
Excel Yourself 2014.pdf
  • 1010 KB
Excel Yourself 2014.xlsm
  • 375 KB
Excel Yourself 2014.mp4
  • 60 mins
  • 117 MB

Excel Yourself 2013 Materials and Video

The December 2013 webinar featured six of my eleven Excel Yourself topics from the CPA Australia INTHEBLACK magazine for 2013.

See the articles come alive. The companion Excel file contains ALL the examples from ALL the year’s articles – including the two macros. Extra content was also included.

Topics covered include
  • creating a MAXIF function
  • summing the top 5 values in a range
  • creating a Pareto chart
  • performing workday calculations
  • deleting zero rows
Excel Yourself 2013.pdf
  • 797 KB
Excel Yourself 2013.xlsm
  • 240 KB
Excel Yourself 2013.mp4
  • (1h 08m 24s)
  • 136 MB

Excel Yourself 2012 Materials and Video

In April 2014 I went back another year and reviewed 6 of my articles from the CPA Australia INTHEBLACK magazine during 2012. This is a veritable smorgasboard of topics including the SUBTOTAL function; formulas for sheet names; conditional formatting using formulas; a macro that links the print footer to a cell; how to use the TEXT function and fixing a problem with the RANK function. 
Excel Yourself 2012.pdf
  • 968 KB
Excel Yourself 2012.xlsm
  • 224 KB
Excel Yourself 2012.mp4
  • 60 mins
  • 107 MB