SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
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.
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.
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 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.
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.
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.
The SUBTOTAL function in Excel is quite flexible and in this second post we build an automated summary report using SUBTOTAL.
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.
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.
Excel has new TEXTBEFORE and TEXTAFTER functions. It doesn’t have a TEXTBETWEEN function. Let’s make one.
Excel’s new TEXTBEFORE function simplifies extracting text from the left. In this example I share how to extract all the text before a number in a code.
A while ago I posted about creating an ordinal (1st, 2nd etc) for a date. With the inclusion of the LAMBDA function I thought I would create a custom function to simplify the process.
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.
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.
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.
The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.
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.
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.
The MIN and MAX functions can provide easy ways to capture current dates.
AutoSum’s cryptonite is a blank cell – it stops AutoSum in its tracks every time.
Here’s how you can avoid AutoSum’s blind spot.
The UNIQUE function has a bit of an issue with blank cells, formulas that return blank cells and zeroes.
If you need a logical test to determine if a list is unique you can use the MODE function with the ISNA function.
If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.
One reason I like the N function is because it is Excel’s shortest function name. But it has quite a few useful features as well.
A while back I posted a formula to find the row number of the last used cell in a column. I revisit the solution to provide the last used column number in a row.
I wrote a blog post a while back about outliers and Excel and I thought I would revisit it thanks to dynamic arrays.
If you need to convert between different measurement systems Excel has just the function for you, called CONVERT.
Let’s say you are transitioning to retirement (lucky you) and you only work four days a week. You have Wednesdays off to play golf. You may still do projects and you need to figure out completion dates based on a start date and working days. Excel can help you.
I was recently helping someone with a budget which they had built vertically, with the months going down the sheet. They then asked to display it horizontally, with the months going across the page. In the latest version of Excel this is straightforward.