Video 00151 Custom Formats, VBA, and Nerds!

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:

Quick Review

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.

BareValue VS With Custom Format

 

Let’s Get Started!

Here we see monthly sales data.

Monthly Sales Values

 

CHALLENGE: Employees want sales numbers formatted differently.

 

Helmut Schmidt

CEO Helmut Schmidt wants to read high level details in a compact dashboard.

 

 

 

 

Raj Singh

Data Analyst Raj Singh is working to create several reports. He only wants to display integers.

 

 

 

 

Lars Peterson

Database Administrator Lars Peterson wants to load the data without any formatting.

 

 

 

 

Over Eager Otis

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.

Custom Format Table

 

2. Select a name from the drop down list.

Select Character from Drop Down List

 

3.  Magically, Helmut’s Custom Format is Applied to the Sales Numbers!

Custom Formatted Sales Vales

 

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
Call AssignCustomNumberFormat  
End If
End Sub

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.

Sub AssignCustomNumberFormat()
Dim Getformat As String
Getformat = Range(“SelectedFormat”).Value
Range(“Table1[Sales]”).NumberFormat = Getformat
Range(“SampleFormat”).NumberFormat = Getformat
End Sub

I also used named ranges and formulas to make it easier on my rusty VBA skills.

 

How Do The Nerds Appear?

HowDidIGetHere

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

Download here or via my OneDrive (file 00151)

 

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).

 

About Me

KevinInMunich_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.

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.

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Leave a Reply

Your email address will not be published. Required fields are marked *