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…)