Excel has a couple of functions that will generate a random number. But the problem is they are volatile. The number changes when Excel calculates. Here’s a technique to generate a random number that doesn’t change.
(more…)
Excel has Paste List option to list the range names in the file. Here’s a macro that does it in one click.
(more…)
Excel and Chart legend and MVP Jon Peltier has blogged about how you can convert a line chart into a step chart. I have used the new TRIMRANGE functionality and range names to make a dynamic solution.
(more…)
The new TRIMRANGE range reference structure can be applied to range names to create flexible and dynamic range names.
(more…)
You can name parts of a spreadsheet and then use the name in formulas and other Excel features. Using a naming convention make things much easier to follow and adapt in the long run. I will share some suggestions for naming ranges.
(more…)
I recently saw a post about using a LAMBDA function for the dates for yesterday and tomorrow. You don’t need to use LAMBDA, you can just use range names.
(more…)
The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.
(more…)In this short video I demonstrate how to create range names quickly based on labels. Range names are a powerful formula feature. I also demonstrate their use. https://vimeo.com/739173957
Let’s say you have a validation check in your file and you want to display a message based on the validation status. Here is one way to do that.
(more…)
It is common to have a Factor in a cell or cells in a budget to allow you to easily tweak the numbers by a percentage. If you want to add a Factor to an existing budget model here is how you can do it.
(more…)
In some cases you may have to make manual inputs across multiple cells that are spread across a sheet. Before making the entries you need to clear the existing entries. Creating a range name can make that process much quicker. (more…)
When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.
(more…)
When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.
(more…)
My consulting work recently highlighted a stark contrast in different Excel models and the effort it takes to create or change them. I make some recommendations to make things easier for yourself at the end of this post. (more…)
Keyboard shortcuts can really speed up your work in Excel. Here are some of my favourites that use the Ctrl key. I’ll share some more in later blog posts. (more…)
Here is an example of a simple macro that solves a problem in Excel 2003 and earlier versions.
Range names can be corrupted if a cell that they refer to gets deleted. This doesn’t mean that the cell value gets deleted, but the cell itself is removed from the sheet.
(more…)