Unleash the Power of Custom Formatting ✨
How to ditch CPU draining Conditional Formatting in favor of super efficient Custom Formatting in your next Excel Project!
Want to get the most out of this newsletter? Download the sample data so that you can follow along!
You're working on a crucial report, and you need a clear and visually appealing way to highlight deviations or trends in your data. But you're tired of the same old conditional formatting options, and frankly they are slowing down your project. That's where custom formatting comes in!
In todays newsletter I'll be sharing some amazing tricks to help you master custom formatting in Excel. If you've ever found yourself frustrated with the limitations of conditional formatting, then get ready because we're about to take your spreadsheet game to a whole new level! 😎
So, why use custom formatting instead of conditional formatting?
Custom formatting offers more flexibility and control over how your data is displayed.
Custom formatting you to use a variety of symbols and colors, making your spreadsheets more visually appealing and easier to understand.
Custom formatting processes information much more efficiently - it won’t slow down your sheets like conditional formatting does.
Custom formatting is a MAJOR time-saver once you get the hang of it!
I know, it sounds like a lot to digest, but don't worry - I'll walk you through each step, making it super easy to follow and understand. 🤗
Breaking down the custom formatting menu.
The custom formatting menu can be accessed by using the CTRL + 1 shortcut.
Go to the tab: Number
Select Category: Custom
In order to use custom formatting, you need to understand it’s language. Under type you enter commands that impact each type of input. The general order is as follows:
Positive ; Negative ; Zero ; Text
Example
Lets Start very simple. Here is a basic chart showing FY 2022 Sales Revenue, Cost, and Net profits
We want our cells to return the following:
Positive number: Profit
Negative number: Loss
Zeros: Break Even
Text: Nothing
Step 1: Highlight the area where we want to apply the formatting
Step 2: We go to the custom formatting menu as described above.
Step 3: Under Type we enter the following syntax:
“Profit”;”Loss”;”Break Even”
Note: If you try and copy and paste this in it may result in an error. If this happens simply type this in native.
Boom! The results appear immediately.
Displaying Simple symbols instead of words
Another easy way to easily point out progress is with simple arrows.
Step 1: Select any cell off to the side.
Step 2: Go into the virtual keyboard. This can be found under the menu: Insert → Symbols → Symbol
Step 3: Select Font: Arial, Subset: Geometric Shapes. Then select the up arrow and hit insert. Do the same for the down arrow.
Step 4: Close the window and then select the cell they were copied into. Copy them from the formula bar.
Step 5: Highlight the cells you want to apply the custom formatting to.
Step 6: Go into the Custom Formatting menu like before and add the following syntax:
“▲”;”▼”;”-”
Note: You’ll need to paste the arrows into the circled area. This is why we copied them before.
After you hit enter your cells will look like this:
Adding back in numbers with currency designation
Now that we have our designated arrows in place, lets add back in the numbers. This will help us better understand the true value of each cell.
Step 1: Highlight the area and open up the Custom Formatting menu like before.
Step 2: We want to enter a “$#” after each of the arrows. Make sure to put the “$#” OUTSIDE of the quotes. Be sure to put a (-) symbol before the “#” in the Negative portion of the syntax. The syntax will look like this:
"▲"$#;"▼" -$#;"-”
Now you have arrows along with currency values!
Lets add some color to our chart! 🔴🟢 🎨
Next, let’s add some color into this. First, you need to be aware of Excel Color Codes. Here is a quick reference chart to help you:
We will use these codes to build off of the existing syntax that we already entered. The syntax used to call forward any of these is [colorxx].
For example if we want to use Color 50 in the positive we would type in [color50] in the positive section next to our symbol.
For this example we will use Color 50 in the positive, Color 3 in the Negative. We will leave the Zero as is.
Step 1: Go back into the Custom Formatting Menu
Step 2: Enter in each of the color designations. The syntax is now:
[color50]"▲"$#;[color3]"▼" -$#;"-”
And just like that - our project has some much needed color!
Setting up alignment so it is easier to read
Wow, this looks great! But the problem is that the numbers and symbols are a bit crunched together. Lets change that. I’ll show you two techniques
Add a space
Step 1: Go back into the Custom Formatting Menu
Step 2: Add two spaces after each of the arrows. Here is the updated syntax
[color50]"▲ "$#;[color3]"▼ " -$#;"-”
As you can see there is now clear space between the shape and the value!
Add a * plus a space outside of the quotes. This will left justify the arrows and right justify the values.
Step 1: Go into the Custom Formatting Menu
Step 2: Add a * and a space outside of the quote. Here is the syntax:
[Color50]"▲"* $#;[Color3]"▼"* -$#;"-”
And here is the difference!
Now you can adjust the columns as wide or narrow as you would like and the cells will still read clearly.
Once you start using these techniques, you'll wonder how you ever lived without them. So go ahead, try them out, and watch as your spreadsheets become more visually appealing and informative than ever before. I’ve also made this easier than ever - be sure to download the sample data sheet so you can practice while you learn!
I hope you enjoyed this newsletter. There are a few other ways I can help you master Excel:
Want to learn more Excel Shortcuts? I have created something special (and free) for you: The Best Damn Excel Shortcut Sheet
Do you have any burning questions about Excel that you want answered? Send your question to hello@learnexcel.live. I will address it in a future newsletter.
Do you or your organization need a little spreadsheet help? Book a 1 on 1 with me here.
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you like these tips & tricks please share this newsletter with a friend or two...