Excel Drop-downs and Wildcards
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…)XLOOKUP and the Last Entry
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 Format Tip: Add a Line Break in a Cell
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…)Ensure a Single Check Box Selection
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…)First and Last Dates in Excel
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…)Fill not working as expected
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…
Flexible Arrow Format in a Cell in Excel
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…)Extracting Dates with XLOOKUP
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…)Creating a Random Number in Excel
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…)Selecting a Locked Cell in Excel
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…)Alphabetic Hyperlink List in Excel
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…)The only time to use SUMIF
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…)Flexible SUMIFS Function
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…)Alphanumeric Sort in Excel
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…)Yet Another Blank Cell Solution
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…)Different Colours for Positive and Negatives in an Excel Column Chart
A recent webinar query requested different colours for the columns if they were positive or negative. Here is one technique to achieve that.
(more…)Unhiding Sheets is Now Easier in Excel
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…
Excel Two Column Lookup – Part 2
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…)Two Column Lookup in Excel – Part 1
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…)Excel Icon Right Click
Did you know you can right click the Excel icon on the task pane? When you do you can see a list of recent files. In the image below I have right clicked the Excel icon. You can then click one of the file names to open Excel and the file. If Excel is already open it still works. You can open a recent file quickly by right clicking the icon and clicking the name. This technique also works for…
Power Query Merge
Merging in Power Query is a useful feature that automates and replaces XLOOKUP functions. But please make sure the look up table doesn’t have duplicates.
(more…)Clear Data from an Excel Table
Using formatted tables in Excel is best practice. If you regularly overwrite data in a formatted table here is a quick way to clear the data before pasting in updated data.
(more…)Excel LET Function and Other Functions
In my previous post I created a formula that calculates the month number for a financial year. That formula used the MONTH function four times. I thought I would share a technique you can use to define functions as a variable in LET function.
(more…)Excel and Financial Years and Months
Excel is built to work with calendar years. Financial years pose a few issues for correctly identifying year and month numbering. I wanted to create some formulas that would work with all financial year ends. Here they are.
(more…)Completed Years in Excel
Sometimes you need to know accurate completed years based on a start date and today’s date. Here is a simple formula to calculate that.
(more…)Excel Pie in Pie Workaround
I did a post on the Pie in Pie chart a few weeks back, but it can be a bit clunky to use. I thought about using a few dynamic array functions to make it a bit more interactive and flexible. The source data is in a formatted table called Table1. In a separate sheet there are two input cells. One for the state to report on and the other to enter the number of “main” segments to use. The…
When to Avoid Using Colours in Excel
Like me, you have no doubt seen some colourful Excel files. One task that colour is used for is to mark rows so they can be reviewed. In general, please avoid applying colours manually when marking rows for review. Instead use a code in a separate column.
(more…)Another Blank Cell Issue in Excel
Excel has plenty of issues when it comes to correctly handling blank cells. Here is a solution to one of those issues.
(more…)Pie Chart Other Segment in Excel
Pie charts often get a bad rap in data visualisation circles (sorry) but here is a technique to avoid those multiple small segments.
(more…)Capturing Formulas in Excel
Unfortunately, it is easy to delete a formula. Of course, there is always Undo but if the file has been closed getting the formula back is difficult unless…..
(more…)