Excel sheets can be large, and you may have multiple tables on a sheet. When a filter is applied it can affect copying and pasting in the rest of the sheet. To know if a filter is in place check the Data tab.
(more…)Excel can now use regex patterns in formulas. You might think that the REGEXEXTRACT function would simplify extracting numbers and letters, but it is actually the REGEXREPLACE function that simplifies the process. If you haven't used regex patterns below is a link to is an introductory article I wrote for the CPA Australia INTHEBLACK magazine – it includes a video. https://intheblack.cpaaustralia.com.au/technical-skills/excel-tips-regex-arrives-in-excel Extracting Numbers If you want to extract all the numbers in a code the REGEXREPLACE function provides a solution…
SUMIFS is great for summing values based on multiple conditions. But when one of those conditions needs to accept multiple options, such as two or more states, combining SUM with SUMIFS can provide a flexible solution. In the image below we have a simple data table on the left and two yellow input cells on the right and a SUMIFS function in cell G2. Notice that the SUMIFS result spills across to the right, returning one result for each state…
When you are trying to figure out if a value is between a lower and an upper value you typically use the AND function. There is a simpler solution.
(more…)If you are in Outlook and the Calendar and you are looking ahead and you then need to return to today press Ctrl + T.
If you need to perform case-sensitive look ups then you need to learn about the EXACT function.
(more…)
Using the From Folder option in Power Query allows you to import all the files from the selected folder and all its subfolders. What if you didn’t want the subfolders? There is a solution.
(more…)
I was reviewing a blog post from last year that listed duplicates in a range. I thought I could shorten the formula using the LET function.
(more…)
One thing you learn in Excel is that there are so many ways to display the same data. When working with Actual / Budget / Variance one structure can be a challenge to work with. See how the FILTER function can simplify it.
(more…)
I have written a lot of words about Excel in the last 25 years. Most of them written in MS Word. Hundreds of articles, hundreds of blog posts plus a 90,000 word book. I just found out you can turn off those stupid Word quotation marks.
(more…)
Way back in 2018 I wrote a post about using a conditional format to highlight formulas. Since then Dynamic Arrays were released and I thought I would create a macro to apply the conditional format easily.
(more…)
I just found out that Excel’s in-cell drop-downs via Data Validation allow wildcards characters that match an entry in the list. Wildcards combinations that don’t match are rejected.
(more…)
One major advantage XLOOKUP has over VLOOKUP is that it can search from right to left. That makes it useful for finance models where you need to identify the last month with a payment, balance, or forecast value.
(more…)
Custom Number Formats allow you to add text to numbers and still use those numbers in calculations. There is a workaround if you need to display the number and the text on separate lines within the cell.
(more…)
Check boxes are supposed to be used to allow multiple selections. But since they are now so easy to insert, you might want to use them to allow a single selection. Here’s one way to do that.
(more…)
The MIN and MAX functions are useful as for returning the first and last dates in a range. They have conditional versions as well MINIFS and MAXIFS which allow you to find the earliest and latest dates based on a condition.
(more…)I was working on another project and tried something that I thought always worked and it didn't. When you drag a cell with a number with the Fill Handle it copies. When you drag a cell with a number but hold the Ctrl key down it increments. Well the Ctrl key didn't work when I had a filter in place in a separate part of the sheet. Has that always been the case? I have never seen the Ctrl key…
Sometimes you like to point to a cell from the cell on the left using something like ===>. Here is an easy way to automate the length of the arrow based on the width of the cell.
(more…)
One issue you may find with dates when extracting from a look up table is that an empty date field generates a zero. You can avoid that with a simple formula adjustment.
(more…)
Excel has a couple of functions that will generate a random number. But the problem is they are volatile. The number changes when Excel calculates. Here’s a technique to generate a random number that doesn’t change.
(more…)
When you protect a sheet in Excel there is a setting to stop users from selecting locked cells. This means when the sheet is protected, they can’t use the keyboard or mouse to select a locked cell. But you can still select an locked cell in a protected sheet – see how.
(more…)
It is common to have codes that start with letters. In a long, sorted list scrolling can be time consuming. A link to the first code starting with a specific letter can save time. Here’s a couple of ways to implement that in Excel.
(more…)
I no longer teach the SUMIF function. I teach the SUMIFS function as it provides more solutions because it handles multiple criteria. The two functions differ in their argument sequence which can be confusing when switching between them. Rather than learning both, it is easier to learn the SUMIFS function. There is however one time when the SUMIF function is shorter and easier to use than SUMIFS.
(more…)
The SUMIFS function is a conditional sum function that allows you to handle multiple conditions. Here is a technique to handle the situation where a condition maybe blank.
(more…)
If you have ever sorted codes like A1 and A100 you have found that Excel’s standard sort doesn’t usually provide the required sort sequence. Here’s a solution.
(more…)
When you link to an empty cell Excel it displays as zero. The same happens when you filter a list using the FILTER function. Blank cells display as zeroes. Here’s how to stop that.
(more…)
A recent webinar query requested different colours for the columns if they were positive or negative. Here is one technique to achieve that.
(more…)Not sure when this change happened but it is now much easier to unhide multiple sheets in Excel. In the past you had to use tick boxes to select sheets unhide. On click per sheet. Now you can just use the Ctrl or Shift keys to select multiple sheets to unhide. In the image below you can see that the tick boxes have been removed and just the sheet names are listed. You can hold the Ctrl key down to…
In the previous post I showed how to use XLOOKUP to do a two-column lookup. See the link below for the previous post. This time we will see how to do the two column lookup using Power Query.
(more…)
It is common to perform a lookup based on one column. Performing a lookup based on two columns is more unusual. This is a formula technique you can use to perform a two-column look up.
(more…)