Activating a Sheet When an Excel File is Opened
There are times when you would like to have the same sheet visible each time a file is opened. You can achieve that with a Workbook Event macro.
(more…)Changing the Close & Load settings in a Power Query
A recent attendee at a webinar posed the question, can you change the Close & Load setting on an existing query? Here is the answer. (more…)
Conditionally Selecting a Column in Excel
When creating a formula sometimes you need to conditionally choose the column to perform a calculation on. The IF function can be used, but there is a trick to shorten the formula. (more…)
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 – Excel Formula and Function Tips
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. Download Materials 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. https://vimeo.com/366912862 This session covers lots of tips, tricks and techniques to speed up the formula…
Free Webinar Recording – Introduction to Power Query
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…
Free Webinar Recording – Excel Formatting Tips
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. Download Materials 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. https://vimeo.com/366395923 This session covers: a format to avoid and the one to use in its place keyboard and mouse…
Excel Macro to Insert a Formula
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.
(more…)Sequential Numbers Formula
Getting a single formula to create a series of sequential numbers is most commonly done with the ROWS function. (more…)
Extract Email from Copied Outlook Address
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.
(more…)