Capturing Formulas in Excel
Unfortunately, it is easy to delete a formula. Of course, there is always Undo but if the file has been closed getting the formula back is difficult unless…..
(more…)TRIMRANGE and OFFSET in Excel
I have seen a few posts recently suggesting that the new TRIMRANGE feature may reduce the use of the OFFSET function when creating flexible ranges. I thought that maybe the OFFSET function will be used more with the TRIMRANGE functionality.
(more…)TRIMRANGE Referencing in Excel
A recent update has introduced a new way to refer to ranges in Excel. This update makes it much easier to refer to flexible ranges in Excel.
(more…)Excel YTD Reporting Solution
Having Actuals, Budget and Variances for each month going across the page is a very common reporting construct. Here is an easy way to sum the correct YTD values as the year progresses.
(more…)Custom Calendar Function in Excel
A few years back I posted a series of three posts about developing a formula to create a month calendar in Excel. I thought I would revisit that and convert that long formula into a custom function using LAMBDA.
(more…)One Minute to Excel #30 – Extract Data Based on Sheet Name
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
The New LET Function in Excel
If you have the subscription version of Excel you may have the new LET function. This function lets you capture variables within a formula. Let’s see an example.
(more…)Easier Structures in Excel
What is the best layout when working with months/quarters/half years and full years? There are a few common structures. I prefer the one that lets you create single formulas that can be quickly copied across and down with as few copies as possible. (more…)
Some Observations on Structure in Excel
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…)