Stories are memory aids, instruction manuals and moral compasses.
Aleks Krotoskm, broadcaster, journalist, and social psychologist
Category Archives: Excel Blog
Grouping Images for Effect in Excel
Using textboxes by themselves can be a good way to add extra content to a spreadsheet. Combining a text box, an icon and an arrow with some colour may make it even better.
Excel Formula Updating Technique [Video]
A recent project required editing many formulas to insert an IF function to display the NA error in certain circumstances. Here’s how I did it.
Excel YTD Reporting Solution
Having Actuals, Budget and Variances for each month going across the page is a very common reporting construct. Here is an easy way to sum the correct YTD values as the year progresses.
Creating Sentences in Excel
If you want to automate journal descriptions or create sentences the TEXTJOIN function is your friend. It can combine words and insert the spaces for you.
Excel Dynamic Arrays and Print Ranges
Print ranges can accept dynamic arrays. This means you can set up print ranges that automatically expand or contract based on dynamic array spill ranges.
Using a Prefix for Excel Table Names
I have been using the tbl prefix for my formatted table names in Excel for a long time. There are a few good reasons I use it.
Selecting a Formatted Table in a Formula
You can select a formatted table when you have a cell or range selected in the table by pressing Ctr + A. But that shortcut won’t work when creating a formula that refers to a formatted table.
To select the table in a formula you must click a cell in the table and press Ctrl + Shift + Spacebar.
Excel Macro for Yesterday
I frequently use the shortcut Ctr + ; to enter today’s date in a cell. I thought it would be useful to be able to enter yesterday’s date in a cell. I wrote a one-line macro to do it for me.
Excel Hyperlink Formula Solution
Hyperlinks in Excel are a great way to navigate around a file, but they can be easily broken. Try this solution using a formula to create a hyperlink that doesn’t break so easily.
In the image below there is a formula in cell C3 that creates a hyperlink to cell A1.
Here is the formula.
=HYPERLINK("#"&CELL("address",A1),"<link text>")Simply change the cell reference from A1 to whatever cell you want to link to. This works for cell in the current sheet.
Hyperlinks to other sheets
In the image below is an example of a link to another sheet.
The formula is.
=HYPERLINK("#"&CELL("address",Report!A1),"<link text>")Again, change the reference to create a hyperlink that doesn’t break if the sheet name changes.
Pro Tip
To return after following a hyperlink press in sequence, function key F5 and then press Enter. Don’t hold them down just press F5 then press Enter.
Creating Full Names In Excel
When joining parts of names together, you may want vary the characters you use to separate the name. Depending on the effect you may be able to use array syntax to control the characters and their location.
It is impossible to get better and look good at the same time. Give yourself permission to be a beginner.
Julia Cameron, the Artist’s Way
Excel Auto-Adjusting Drop-Down List
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.
“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
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.
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?
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.