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.
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 – how to convert a report layout into a data table layout
converting a MYOB report into a data table
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.
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
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.
When I am creating a file for my training or for my blog or other articles that I write I regularly use the FORMULATEXT function to display the formula in a cell on the right of the actual formula. To save time I created a macro to do the work for me.
I frequently copy an email address from Outlook to Excel and most times it looks like John Smith<jsmith@email.com>. To be used as an email I need to extract from between < and >. To do that in a single cell is tedious, so I wrote a macro to do it for me.
Over the years I have had regular requests for a technique to hide zero rows in reports. You can use macros but you can also use filters. Let’s see how you can implement a filter solution.
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.
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.
When you create an Excel file that handles inputs it is best practice to colour code the input cells. The colour you choose isn’t important but making sure you use it consistently is. You may need to unlock the input cells if you plan to add sheet protection to the file. Here’s a couple of ways to do that.
Promoting headers in Power Query means using the first row as column headers. In Power Query this is a useful and common option. In some cases it is even automated. There is one time though when you don’t want to use it.
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.
