I doubt that you have ever seen custom formats presented like this before! Selecting a name from a drop down list changes the custom format and also displays a nerd!
But First A Quick Review
Custom formats don’t change the value in the cell. Custom formats change the appearance of the value. You decide how to display positive numbers, negative numbers, zeros and text. Here is a sample:
Below we see the bare values on the left with a General format and the same values on the right using the custom format defined above.
Let’s Get Started!
Here we see monthly sales data.
CHALLENGE: Employees want sales numbers formatted differently.
CEO Helmut Schmidt wants to read high level details in a compact dashboard.
Data Analyst Raj Singh is working to create several reports. He only wants to display integers.
Database Administrator Lars Peterson wants to load the data without any formatting.
Marketing Manager Over Eager Otis gets a bonus if December sales are greater than 1 million (hide other months).
How Can We Easily Change Formats?
1. Let’s create a table to define each person’s desired custom format.
2. Select a name from the drop down list.
3. Magically, Helmut’s Custom Format is Applied to the Sales Numbers!
It’s Not Magic. It’s VBA!
VBA event code watches for a change in the drop down list (where we selected ‘Helmut Schmidt’).
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$I$2” Then
Each time we change the cell’s value a second macro runs that picks up the selected person’s desired custom format and applies it to all the sales numbers.
Dim Getformat As String
Getformat = Range(“SelectedFormat”).Value
Range(“Table1[Sales]”).NumberFormat = Getformat
Range(“SampleFormat”).NumberFormat = Getformat
I also used named ranges and formulas to make it easier on my rusty VBA skills.
How Do The Nerds Appear?
Selecting a name from the drop down also displays each character’s picture. Select a different character and the picture changes accordingly! You can also change the text that goes into the blue text bubbles as seen above. Download my Excel file and watch my video to see how it works. Endless hours can we wasted playing around with this!
Download My Excel file
Watch My YouTube Video
Why not use Excel’s TEXT function?
We could use Excel’s TEXT function but remember that it works differently compared to custom formats. TEXT function would require a helper formula cell and unlike the four options in a custom format, the TEXT function only allows one format. Lastly, the value produced is a text value. If you’re using it for presentation purposes then that’s fine but you can’t directly do math on text values (so use the value that your TEXT function refers to for calculations).
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.
Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy playing with my dog Cali (she doesn’t like Excel), learning Spanish, playing Chess and drawing nerds.