If you need to have a drop-down list automatically adjust and remove items as they are selected, here’s one technique.
Array Syntax Custom Function
Array syntax is a powerful feature in Excel. Array syntax allows you to create a list within your formulas which makes them self-contained. Typing array syntax is difficult as you need to use a combination of quotation marks, commas or semi-colons plus braces (curly brackets).
“Retreating with enthusiasm is a sign of wisdom and long-term thinking.”
Seth Godin
Hyperlink Drop Down List
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 just the CELL function.
“We first make our habits, and then our habits make us.”
John Dryden
Counting the Number of Cells in an Excel Range
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.
Some nice feedback
It was a nice start to the day to receive this email this morning.
“Doth thou love life? Then do not squander time, for that is the stuff life is made of.”
Benjamin Franklin
Excel’s Magic Percentage Symbol
I recently saw a post of LinkedIn (from Patryk Samborski) that used the percentage symbol with the SEQUENCE function to produce a list of decimals and I thought I would have a play with that idea.
“It is not how much we have, but how much we enjoy, that makes happiness.”
Charles Spurgeon
Working Backwards in Excel
I recently received a request to help with a salary packaging calculation. I thought I would share the solution and explain the technique to solve it. This is a case where we have a value we need to equal but don’t know the components that make it up. We are in effect working backwards to find the missing value.
“Logic will get you from A to B. Imagination will take you everywhere.”
Albert Einstein
Olympic Average in Excel
Averages are affected by outliers. If Bill Gates walks into a room the average net worth per person jumps substantially. In the Olympics some sports deduct the top and bottom scores before calculating the average score. Here’s a formula to do that in Excel. You need the subscription version of Excel for this solution.
“We know what we are but know not what we may be.”
William Shakespeare
Double Click the Excel Icon
You can close Excel down (with multiple files open) by double clicking the Excel icon – top left of screen.
This works for the other Office apps too.
If you haven’t saved a file Excel will ask if you want to.
To close a single file down use the X on the top right of screen.
Using Emojis in Excel Formulas
You can use conditional formatting to insert symbols in cells. You can also use formulas with emojis. using range names makes it even easier.
To insert an emoji icon in a cell you can use press the Windows key and the full stop.
This opens the Emojis dialog box.
In this example we are going to insert three separate symbols in formulas.
I have named each cell that has an emoji. A1 = Tick, A2 = Cross and A3 = Dash.
You can use these names in formulas throughout the file.
The formula in cell F2 (Sales) is.
=IF(D2>E2,Tick,IF(D2<E2,Cross,Dash))
The formula in cell F3 (Costs) is.
=IF(D3<E3,Tick,IF(D3>E3,Cross,Dash))
The advantages with using formulas instead of conditional formatting is that you can format the cells. Plus using formulas in cells is easier than using formulas in conditional formats.
Naming your emojis makes then easier to use. You can use these emoji icons names in your formulas throughout the file.
A wonderful discussion about creativity and how we can all be creative.
Makes you think.
“Play is the highest form of research.”
Albert Einstein
Excel and Range References
The typical range reference looks something like A1:A10. You always refer to the top left cell followed by the colon followed by the bottom right cell of the range. Did you know Excel can handle you entering the last cell followed by the first and it corrects it for you?
Clear all formats
To clear all the formats for a cell or a range select the cell/range and press in sequence Alt H E F don’t hold the keys down.
All the formats will be removed.
If you need to see the underlying number for a date this is an easy way to find it.
Solving a Date – Time Issue in Excel
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.
IFERROR Function Now Handles SPILL Errors
Not sure when it happened, but a recent update to Excel has enabled the IFERROR function to handle spill errors.
Creating A Text String of Lowercase And Uppercase Letters
As a follow on from last week’s post you may want to create a text string of all of the lowercase and uppercase letters. Again we can combine some Excel functions to achieve this.
Listing Alphabetic Letters in Excel
If you need a listing of the letters of the alphabet you can combine a couple of functions to provide the list.
Excel Drop Down Improvement
In previous versions of Excel when you had a drop-down list that contained duplicates those duplicates would show up in the drop-down list. This has been fixed in the latest versions of Excel. The duplicates are now removed.
You have to finish things — that’s what you learn from, you learn by finishing things.
Neil Gaiman – author
Updating the Excel Header with a Cell Value
A common request over the years for Microsoft has been to have the ability to link the print header or footer to a cell. As at the time of writing this functionality doesn’t exist, but we can add a one-line macro to fix that.
SUMIFS Magic Solution
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.
If you knew how much effort went into it you would not call it genius.
Michaelangelo
Solving a Conditional Summing Text Problem in Excel
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.