I just found out you can split the VBA code window. See images below. Use the small icon above the right side scroll bar. This can be useful if you have a long block of code and need to look at separate parts together. Thanks to Bob Umlas the Excel MVP for sharing. You can double click the split bar to remove it.
Excel’s EOMONTH function is great. Here is a tweak using a custom function to calculate the number of days in a month.
(more…)
If you have the subscription version of Excel then you have access to the Navigation Pane. This allows you to navigate between sheets and see the structure of the sheets in your file.
(more…)Here's a technique I use a lot to speed up report development. Sheet names have to be unique, so they can't be duplicated. This makes them great for department names or states. This short video combines a few techniques to extract from a data set based on the sheet name. All in less than a minute. https://vimeo.com/780315328
Excel has three functions that can rank values. Unfortunately none of them provide a way to create a sequential list of ranking numbers when there are duplicates. Luckily we can adapt the RANK function to handle duplicates and create a sequential list of ranking numbers.
(more…)
In Excel you can use the Go To Special dialog to find constants. These are cells that won’t change. Constants are things like labels, entered text, numbers, or dates. But there are cells that won’t change that Go To Special won’t identify as a constant.
(more…)Wow - if you right click the Taskbar (bottom of the Windows screen) you can access the Task Manager. No more Ctrl + Alt + Del - woohoo!
I wrote a post in 2016 about using INDEX and MATCH to create a hyperlink based on a search result. I thought I would update the technique using XLOOKUP.
(more…)
There was a recent discussion of this topic on LinkedIn. When you are in the Power Query window you can’t save the query. If you Close and Load and Excel crashes, you could lose your work. If you have spent some time working on a query this can be frustrating. Here’s how you can capture the query before using Close & Load.
(more…)
The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.
(more…)In a browser you can open a link in a new tab by middle clicking - (click the mouse wheel) - the link. Woohoo!
Text alignment in Excel is versatile. If the column isn’t wide enough to display the text, it will display over the next cell. Date and number alignments are not so forgiving. If the column isn’t wide enough the cell with display the ### symbols or the scientific format. Here is a function technique to get around the limitation.
(more…)
When you link to a cell that has a number format you may be surprised to learn that as well as the value that is linked, the format also is updated based on the cell you linked to.
(more…)
When you copy from another system and paste into Excel the result may not be what you expect. One way around that is to paste in to Excel and match the format of the destination cell. Here’s how you do that.
(more…)My book was published in 2014 and I bought the Kindle version from Amazon. That doesn't stop them trying to sell me the paperback version of my own book. Algorithms never give up hope.
Sometimes Excel is not very helpful when it tells you that there is a circular reference in your file. If that is the case here is a technique to help you find where the circular reference is.
(more…)
In an earlier post I shared some VBA code to sort sheets by their colour. In this post I have tweaked the code to sort the sheets by their name.
(more…)
Sometimes when you are testing or training in Excel you need to create random entries. That is easy to do with the RANDBETWEEN or RANDARRAY functions. What if you wanted to emphasise some entries more than others in the random list created? There is a way.
(more…)
With dynamic arrays making array calculations more accessible and easier to use here is a hack for using array syntax in Excel formulas.
(more…)
Sometimes when you are building a text string in VBA you may need to insert quotations marks. That poses a problem because quotation marks are used to surround the text you want to join and using them within the text is problematic. There is a solution.
(more…)
If you have the subscription version of Excel you can create your own functions. One that you may want to create avoids the #DIV/0! error. (more…)
A Sparkline chart is a single data series chart (graph) that exists in a cell. Usually, you only need to select one range to create a Sparkline. Sometimes you may need to handle missing dates.
(more…)The standard colour for a formatted table is blue. If you use Ctrl + T to create a table, that's the colour Excel uses. You can change the default. On the Home ribbon tab, click the Format as Table drop-down and right click the colour scheme you want to set as the default. Select Set As Default from the menu - image below. Done!
I am not a fan of the merged cell format. It causes more problems that it solves. One issue you will face is trying to select a single column range within a range that has a merged cell. Here is how you handle it. This post is a video post as it easier to show the problem and the solution in a video. https://vimeo.com/739153569 This post is a video post as it easier to show the problem and the solution…
I am not a fan of Merged Cells but there may be a case to use them when creating a larger Sparkline chart.
(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
Using colours on your sheets can help you navigate and organise your file. If you want to sort your sheets by colour you can use a macro to speed up the process.
(more…)
When working with files in VBA code, you may need to know if a file is already open. A function can return TRUE if a file is currently open and FALSE if it isn’t.
(more…)
If you need to find the movement from the previous month a PivotTable can be your friend and do most of the work for you.
The General format is Excel's default format. It doesn't really do much. It can be handy to use though if Excel applies a format you don't want, like a date or a percentage. The keyboard shortcut to apply the General format is Ctrl + Shift + ~ Be careful if you forget the Shift key as it will display formulas. If that happens, press it again (without the Shift key) and it will revert back to normal.