Better MROUND Function for Excel
The MROUND function gives inconsistent results. Here is a replacement custom function that provides consistent results.
(more…)Comparing Lists in Excel
I have been going through Mike Girvin’s recent book Microsoft 365 Excel: The Only App That Matters, and found an elegant technique of comparing lists using FILTER and XMATCH. It inspired me to create a custom function to show missing or matching entries.
(more…)Excel Day Type Custom Function
Let’s say you need to classify a series of dates as either Weekday, Weekend or Holiday. Here’s a formula and a custom function you can try.
(more…)Custom Function to Increment Dates
Excel can easily add days to a date but months, quarters and years require a slightly different calculation. Here’s a single function that does all three.
(more…)Excel Custom Function for Common Dates
Excel has an EOMONTH function that returns the end of month. You can hack this function to return the start of the month as well. I thought I would make it easy to return the first, last or middle of the month in one simple function.
(more…)Sequential Rank Custom Function for Excel
Excel does not have a rank function that returns sequential numbers. All Excel’s ranking functions can return duplicated ranking numbers. Here is a solution to creating a sequential ranking system with no duplicates.
(more…)Formatted Table Running Total Custom Function
I have shared a running total formula for a formatted table many years ago. Here is a better solution that I have converted into a custom function.
(more…)Identifying Locked Cells in Excel
The default setting in Excel is for all cells to be locked. If you want users to input in a cell you must unlock that cell before you apply sheet protection. You can use a formula to identify locked and unlocked cells.
(more…)Array Syntax Custom Function
Array syntax is a powerful feature in Excel. Array syntax allows you to create a list within your formulas which makes them self-contained. Typing array syntax is difficult as you need to use a combination of quotation marks, commas or semi-colons plus braces (curly brackets).
(more…)Does an Excel range contain sequential numbers?
I have created a custom function to check if a range has sequential numbers. The range doesn’t have to be sorted.
(more…)Excel Sheet Names and Emojis
Did you know you can use Emojis on the sheet tabs? The font on sheet tabs is small, so some emojis may not be that effective, but simple emojis can be.
(more…)A Better SEARCH Function for Excel
The SEARCH function has a couple of issues that make it difficult to use. This makes it a prime candidate for a custom function to fix its limitations.
(more…)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.
(more…)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.
(more…)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.
(more…)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.
(more…)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.
(more…)Improvement to Excel’s HYPERLINK Function
Excel’s HYPERLINK function is not easy to use. You need to know a few of its secrets to get it to do what you want. Unfortunately, you can’t replicate the HYPERLINK function in a custom function, so we can’t improve it. But the next best thing is to simplify creating the reference you need as the first argument in the HYPERLINK function.
(more…)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.
(more…)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.
(more…)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.
(more…)SUBTOTAL and Dynamic Arrays in Excel – Part 4
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. In this post we will amend the custom function we have created to add an extra column plus headings.
(more…)SUBTOTAL and Dynamic Arrays in Excel – Part 3
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. In this post we will create a custom function to summarise a data set.
(more…)Excel Custom Function to Return Unique Random Whole Numbers
In last week’s blog post I covered a complex formula to return unique random whole numbers. In this weeks’ post we will look at how we can convert that complex formula into a custom function.
(more…)TEXTBETWEEN function in Excel
Excel has new TEXTBEFORE and TEXTAFTER functions. It doesn’t have a TEXTBETWEEN function. Let’s make one.
(more…)Custom Function for Age in Excel
Calculating age with years months and days takes several functions to create. Whenever you need multiple functions to calculate an entry you may have a good use case for a custom function.
(more…)Last Weekday of the Month in Excel
Excel has a function to find the last day of the month. To find the last weekday of the month you can combine a couple of functions. Here is a custom function that also works.
(more…)Custom Function for Days in the Month
Excel’s EOMONTH function is great. Here is a tweak using a custom function to calculate the number of days in a month.
(more…)A Cell Reference Custom Function
The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.
(more…)