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. https://vimeo.com/692104077
Some weird stuff happening when using a range name in the same sheet. Check out the video. Never seen this in my over 25 years of using Excel. The file may have issues but definitely some weird stuff. Found the problem - or more accurately Jan Karel Pieterse Excel MVP did on LinkedIn. It was a setting in the Options => Advanced => Lotus compatibility settings. The Transition formula entry option was ticked - it needs to be unticked -…
When calculating variances between actuals and budget, you typically have a positive value representing a favourable (good) variance and a negative value for an unfavourable (bad) variance. When looking at revenue and expenses together this poses a problem for the variance calculation. The calculation needs to be different for revenue and costs. Here’s a way to use a single formula for both.
(more…)
If you need to convert between different measurement systems Excel has just the function for you, called CONVERT.
(more…)Earlier this month I ran a free webinar on Excel Power Query. This is the recording of the session with no editing, no time limit and no sign up required. Power Query is the best way to import data into Excel. It is also the data importation system used in Power BI, so everything you learn in Excel can be applied to Power BI. You can download the materials, including a detailed pdf manual at the button below. Please enjoy,…
Let’s say you are transitioning to retirement (lucky you) and you only work four days a week. You have Wednesdays off to play golf. You may still do projects and you need to figure out completion dates based on a start date and working days. Excel can help you.
(more…)
When you create a hyperlink manually in Excel it captures the cell reference. If you insert rows the cell reference in the hyperlink doesn’t update. If you need it to update, this is what you need to do.
(more…)
I was recently helping someone with a budget which they had built vertically, with the months going down the sheet. They then asked to display it horizontally, with the months going across the page. In the latest version of Excel this is straightforward.
(more…)