The # symbol is used in Excel sheets for hyperlinks and dynamic arrays. It is also used in VBA for date definitions.
Category Archives: Excel Blog
Colour Constants Updated
Thanks to Rick Rothstein MVP for sharing a LinkedIn article he wrote a while back about colour constants. He gave me permission to share the contents in this post. Making your own colour constants.
VBA Color Constants
Simplify using basic colours
In Excel colours are colors. If you want to used basic colours you can use the built-in VBA color constants. This simplifies the code and makes it more descriptive.
Extracting Initials in Excel – Part 2
In the previous post we looked at extracting initials when we only had a first name and a last name in this post, we will look at handling more than two names.
Extracting Initials in Excel – Part 1
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.
Working with Shapes and Other Graphic Objects in Excel
Shapes can be frustrating to work with in Excel until you find out there are two types of selections with shapes.
Excel, Laptops and Function Keys
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.
Excel Sheet Names and Emojis
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.
AAA all the way
I started my own weekly cash flow forecast file way back in 2010 and it just ticked over to column AAA!
Excel PivotTable Subtotal Label Trick
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.
Useful Excel Keyboard Shortcuts for Data Entry
When making entries in Excel there are a few keyboard shortcuts worth knowing. These can save you time and effort.
Yet Another Post on Hiding Sheets in Excel
If you want to hide sheets and then stop people unhiding them, here is yet another technique.
More Hiding Sheets Macros
These ones work with the selected sheets
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.
More Adjusting in Excel
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.
Adjusting in Excel
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.
Sparkline Horizontal Axis Technique
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.
Outside Borders in Excel
How to apply to a range
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 Wildcard Limitation
SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
Clearing All Formats in Excel
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.
Yesterday and Tomorrow in Excel
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.
Macro to Format a Spill Range in Excel
VBA and spill ranges can work together
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.
Changing row and column grouping in Excel
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 Custom Function for a List
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.
Slope Chart Macro
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.
Calculating YTD percentages in Excel
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.
Custom Function to Reverse a String in Excel
When I started learning Python, I saw it had a Reverse function and thought, “I could do that in Excel.”