Extracting initials in Excel can be challenging. That’s because the names can be separated by different characters and there can also be more than two names. Some new functions in Excel can simplify the extraction of initials.
Shapes can be frustrating to work with in Excel until you find out there are two types of selections with shapes.
Laptop keyboards tend to re-purpose function keys to handle other features. Often the default for the function keys is the laptop features. You then must press the Fn key to access the software function key options. Look out for a FnLock option.
Did you know you can use Emojis on the sheet tabs? The font on sheet tabs is small, so some emojis may not be that effective, but simple emojis can be.
I started my own weekly cash flow forecast file way back in 2010 and it just ticked over to column AAA!
I just learned a new trick for labelling subtotal rows in a PivotTable. Hat tip to Ken Puls MVP of Excel Guru for this tip.
When making entries in Excel there are a few keyboard shortcuts worth knowing. These can save you time and effort.
The SEARCH function has a couple of issues that make it difficult to use. This makes it a prime candidate for a custom function to fix its limitations.
If you want to hide sheets and then stop people unhiding them, here is yet another technique.
It is easy to hide a sheet, you right click the sheet tab and select Hide. Unfortunately, it is just as easy to unhide that sheet once hidden. If you want to hide sheets and make it harder to unhide them, then these macros may help.
Following on from last week’s post on a single adjustment formula this post will share a more robust solution for including or excluding adjustments.
One of the advantages with Excel is that you can usually handle exceptions. In this post I examine a way to handle exceptions or adjustments without using the IF function.
Excel Sparkline charts don’t have a horizontal axis. Here is a technique that creates one in the cell above or below the Sparkline. This works best for the Column Sparkline.
I use the thin all borders format a lot. But there are times when I need to use the thin outline (outside) borders. This border is not as straight forward to apply to a range.
SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
Sometimes with Excel formatting you just want to clear everything and start again from scratch. You can clear just the formats, and there is an icon you can add to the Quick Access Toolbar to make clearing all the formats earlier.
I recently saw a post about using a LAMBDA function for the dates for yesterday and tomorrow. You don’t need to use LAMBDA, you can just use range names.
Education is the ability to listen to almost anything without losing your temper or your self-confidence.
Currently spill ranges do not spill formats. Hopefully Microsoft will add this functionality soon. In the meantime, here is a macro that will copy the format from the top left cell of the spill range to the rest of the spill range.
Excel allows you to easily hide and unhide rows and columns using a feature called grouping. There are two keyboard shortcuts that allow you to apply and remove grouping. These shortcuts can also be used to amend existing groupings.
Excel’s Custom Lists are great, but you need to drag them to create them. If you have a list that you use frequently why not create a custom function to display it? This has the added advantage of creating a spill range that can then drive other dynamic array formulas.
A mind all logic is like a knife all blade. It makes the hand bleed that uses it.
About 10 years ago I did a short blog post on Slope charts. I was reviewing my 10 year old posts and remembered I had written a macro to simplify the process of creating a Slope Chart. So here it is.
When working with YTD percentages you must be careful with the calculation. Adding up values or amounts is easy. Working with YTD percentages require a bit more work.
Four Thousand Weeks (2020)
An alternative view of being productive and striving to do the most you can, in the time you have.
Being efficient and effective is messy and a one size fits all rule based system usually won’t last or work.
Has some good ideas on what to focus on and what not to focus on.
When I started learning Python, I saw it had a Reverse function and thought, “I could do that in Excel.”