Look Who it Was Random video

Excel - Zones in Custom Number Formats - Episode 1064

399 views · Published 23 July 2009 · 2:44 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Excel Custom Number Formatting Zones Based on Conditions.

Welcome back to another episode of the MrExcel netcast! In this video, we're going to dive deeper into custom number formats and learn how to create our own zones for highlighting data in Excel. If you missed our previous video, we discussed the four default zones for positive, negative, zero, and text values. But what if you want to create your own zones based on specific criteria? Well, that's exactly what we're going to cover today.

Let's say you want to highlight all numbers above 90 as good and anything below 80 as bad. With custom number formatting, you can easily achieve this by defining your own zones using square brackets. For numbers greater than 90, we'll use the format [＞90] in green with a star symbol and the number. Similarly, for numbers less than 80, we'll use the format [＜80] in red with an exclamation mark and the number. And for any other numbers, we'll use the format [Blue] with just the number. Once you click OK, you'll see the high numbers highlighted in green, low numbers in red with exclamation points, and everything else in blue.

Now, this may seem like a primitive method compared to conditional formatting, but it's still a clever way to control the color and appearance of your data. Plus, the important thing to note is that the actual value of the cell remains unchanged. This means you can still use formulas to work with these numbers. So, whether you want to add them up or perform any other calculations, it's all possible with custom number formatting.

Thanks for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe to our channel for more Excel tips and tricks. We'll see you next time!"

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction to custom number formats
(00:17) Defining custom zones
(00:27) Creating custom zones for specific values
(00:37) Defining multiple conditions
(01:27) Applying custom number format
(01:43) Comparison to conditional formatting
(02:03) Importance of retaining numerical value
(02:21) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Conditional formatting
Controlling cell colors and adding extra words
Creating conditions in square brackets
Custom number formats
Defining your own zones
Formatting for high numbers in green
Formatting for low numbers in red
Formatting for other numbers in blue
Four zones (positive, negative, zero, text)
Highlighting values above 90 as good
Highlighting values below 80 as bad
Using formulas with custom number formatting

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152531/



In yesterday's netcast, I talked about using four zones in custom number formats for positive, negative, zero, and text. In Episode 1064, you will learn how to create your own zones for formatting.

This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

More from this channel