Even if you’re not a teacher, be a teacher. Share your ideas. Don’t take for granted your education. Rejoice in what you learn and spray it.
Source: 9 Life lessons
Tim Minchin
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
Style—to renounce all but the essential, so that the essential may speak.
Freya Stark – travel writer
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.
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.