Hyperlinks in Excel are a great way to navigate around a file, but they can be easily broken if the sheet name changes. Try these solutions using a formula to create hyperlinks that don’t break so easily. In the image below there is a formula in cell C3 that creates a hyperlink to cell A1. Here is the formula. =HYPERLINK("#"&CELL("address",A1),"<link text>") Simply change the cell reference from A1 to whatever cell you want to link to. This works for a…
When joining parts of names together, you may want vary the characters you use to separate the name. Depending on the effect you may be able to use array syntax to control the characters and their location.
(more…)
If you need to have a drop-down list automatically adjust and remove items as they are selected, here’s one technique.
(more…)
I recently saw a post on LinkedIn about making a drop-down list for a hyperlink interface. It was using a range name and the ADDRESS function. I thought I could streamline it with the CELL and INDIRECT function.
(more…)
Excel has functions to count the number of rows and columns in a range. It doesn’t have a function to count the number of cells in a range. We can still perform the calculation with the COUNTA function.
(more…)
Often when you import data into Excel the dates may include a time. This can make summarising the data more difficult as time is included as a fraction of a date. You can use an old function to fix the problem. This solution requires the subscription version of Excel.
(more…)
Not sure when it happened, but a recent update to Excel has enabled the IFERROR function to handle spill errors.
(more…)
A client recently had a problem. He was chasing a formula that identified if a text string contained one of three words. He wanted to base an allocation on finding those three words. SUMIFS offers a solution.
(more…)
I was checking out an old Excel book Excel Outside The Box by long time Excel MVP Bob Umlas and noticed he used the N function in his SUMPRODUCT functions. I then realised why. It converts text to a zero. That gets around an issue with adding up ranges that contains text, thanks again Bob.
(more…)
In the previous post we looked at extracting initials when we only had a first name and a last name in this post, we will look at handling more than two names.
(more…)
Extracting initials in Excel can be challenging. That’s because the names can be separated by different characters and there can also be more than two names. Some new functions in Excel can simplify the extraction of initials.
(more…)
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…)
Following on from last week’s post on a single adjustment formula this post will share a more robust solution for including or excluding adjustments.
(more…)
One of the advantages with Excel is that you can usually handle exceptions. In this post I examine a way to handle exceptions or adjustments without using the IF function.
(more…)
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.
(more…)
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.
(more…)
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.
(more…)
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…)
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.
(more…)
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…)
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. It can also ignore hidden rows, something that not many Excel functions can do.
(more…)
When you have a list of dates in Excel it can be useful to know which of those dates are weekends. You can automate a format for weekends using a conditional format.
(more…)
Excel has three functions that can provide random numbers. But the random numbers created may not be unique random whole numbers. Here is one way to get a list of unique random whole numbers.
(more…)
When analysing data you may want to check for outliers. You can use MIN and MAX to get minimum and maximum values but you may want to average a certain number of top or bottom numbers. Here’s how you can do it.
(more…)
Excel has new TEXTBEFORE and TEXTAFTER functions. It doesn’t have a TEXTBETWEEN function. Let’s make one.
(more…)
As we get used to the new year we may want to perform some calculations based on the old year. A recent inquiry requested a formula that could calculate the percentage of a year that an employee had been employed. He suggested using an IF function. See the solution below, but it doesn’t involve the IF function.
(more…)
A client recently requested a formula to round to the nearest 9 cents. This avoids getting to a price point. This is a common requirement in retail businesses. The solution was simpler than I thought it would be.
(more…)
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…)
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…)