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.
Category Archives: Excel Blog
Macro to Format a Spill Range in Excel
VBA and spill ranges can work together
Currently spill ranges do not spill formats. Hopefully Microsoft will add this functionality soon. In the meantime, here is a macro that will copy the format from the top left cell of the spill range to the rest of the spill range.
Changing row and column grouping in Excel
Excel allows you to easily hide and unhide rows and columns using a feature called grouping. There are two keyboard shortcuts that allow you to apply and remove grouping. These shortcuts can also be used to amend existing groupings.
Excel Custom Function for a List
Excel’s Custom Lists are great, but you need to drag them to create them. If you have a list that you use frequently why not create a custom function to display it? This has the added advantage of creating a spill range that can then drive other dynamic array formulas.
Slope Chart Macro
About 10 years ago I did a short blog post on Slope charts. I was reviewing my 10 year old posts and remembered I had written a macro to simplify the process of creating a Slope Chart. So here it is.
Calculating YTD percentages in Excel
When working with YTD percentages you must be careful with the calculation. Adding up values or amounts is easy. Working with YTD percentages require a bit more work.
Custom Function to Reverse a String in Excel
When I started learning Python, I saw it had a Reverse function and thought, “I could do that in Excel.”
Jean-Georges Vongerichten showed me the value in taking away, taking things off of a plate. … The more you put on the plate, the easier it is to hide. The more you take away, there’s nowhere to hide—it has to be good.
Chef Wylie Dufresne
A Conversation with Wylie Dufresne
Formats Affect Excel’s Fill Handle
Applying a date format before you drag with the Fill Handle may save you some time. See why.
Using the Custom Month List Function
In the previous post I created a custom function that creates a horizontal list of months based on three inputs. In this post I share a few ways that you can use this list.
Excel Month List Custom Function
It is common in reporting files, budgeting and forecasting files to create a list of months going across the page. The custom function in this post can automate the process.
Stop Excel Control being Clicked
Textbox hack
In a protected worksheet users can still click on checkbox and option button controls. A warning message will pop up if the control’s linked cells are locked. There is a technique you can use to stop users clicking on these controls. This involves a macro that you can run just before you protect the sheet.
Excel and Ordinals
A recent update to Excel included ordinals as an option when you drag with the fill handle. A few months back I made a post about a Custom function for ordinals in dates. Dates are a bit easier because they max out at 31. I thought I would extend the function so that it handled all numbers.
Fixing the UNIQUE Function
The UNIQUE function in Excel has a problem when it comes to handling blank cells. Blank cells are treated as zero and if you have a blank cell and a zero in a range then UNIQUE will return two zeros. Also, if a cell has a function that returns a blank cell, then that is treated differently than a blank cell. Let’s create a custom function to fix UNIQUE’s blank cell blind spot.
Convert Day Text into Day Number in Excel
Excel uses a number from 1 to 7 to refer to the days of the week. The WEEKDAY function returns the number based on a date. Excel doesn’t a have a function to return the day number based on a text day like Monday. Time for a custom function.
Custom Function to Count Specific Characters in Excel
I have previously covered a formula that counts specific characters. Since it used multiple functions, I thought I would simplify it by creating a custom function.
Education isn’t something you can finish
Isaac Asimov (1920-1992)
Hiding Multiple Sheets in Excel Using xlVeryHidden
Flexible solution
It is easy to hide multiple sheets in Excel. Unfortunately, it now just as easy to unhide those sheets. You can hide sheets and make it harder to unhide them. You can use a setting called xlVeryHidden (no kidding) that won’t display the sheet name if you right click a sheet tab and choose unhide sheets.
The myth is that there isn’t enough time. There is plenty of time. There isn’t enough focus with the time you have. You win by directing your attention toward better things.
James Clear
If you can’t make a mistake, you can’t make anything.
Marva Collins
Excel Column Letter Custom Function
Sometimes Excel refers to columns using numbers. If you need to identify the column letter you can use a few functions in combination. Thanks to Marcus Small for the formula which is shorter and more elegant than my original one. You can also create a custom function.
Never give up your right to be wrong, because then you will have lost the ability to learn new things and move forward with your life.
David Burns
Percentage Movement in Excel
It is common in Excel to calculate the percentage movement or difference between two values. This may be between this year and last year or between actual and budget. There are two common issues you will face when doing this calculation. The first is handling zeros and the second is handling negatives.
Mistakes are painful when they happen, but years later a collection of mistakes is what is called experience.
Denis Waitley
FILTER Function Technique
An application I use recently updated it’s filtering options to allow you to filter by any filters or all filters. This was a useful addition to the software and I thought that I could apply the same idea to Excel’s FILTER function.
It’s good to remember that there’s much to learn from negative experiences. In science, mistakes always precede discoveries.
John C. Maxwell
Benford’s Law in Excel – Part Two
Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In my previous post I created a report to analyse a data set based on Benford’s Law. In this post we will create a single formula to create the report and then convert that into a custom function.
Benford’s Law in Excel – Part One
Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In actual data sets when reviewing values the 1st digit of the values tends to follow a predetermined frequency. For example, roughly 30% of the values should start with a 1.
If you’re willing to consider failure as a blessing in disguise and bounce back, you’ve got the potential of harnessing one of the most powerful success forces.
Joseph Sugarman
Comparing Averages in Excel
It is common in Excel to use averages to summarise large data sets. It is also common to compare the averages across different segments. Here’s a technique you might find useful when comparing a segment against all other segments.