www.makeuseof.com
Open in
urlscan Pro
54.157.137.27
Public Scan
URL:
https://www.makeuseof.com/use-custom-formatting-excel/
Submission: On February 07 via manual from IN — Scanned from DE
Submission: On February 07 via manual from IN — Scanned from DE
Form analysis 2 forms found in the DOM
GET /search/?q=
<form method="get" class="search-form js-searchBox icon i-search" id="searchform2" action="/search/?q=">
<input id="js-search-input" type="text" name="q" class="input-text search-input" autocomplete="off" placeholder="Search MUO" autofocus="">
</form>Name: articleNewsletterForm — POST
<form class="user-static-form user-content" enctype="multipart/form-data" id="articleNewsletterForm" method="post" name="articleNewsletterForm">
<h2 class="newsletter-widget-title">Upgrade Your Tech IQ With Our Free Newsletters</h2>
<p class="f-errors" id="f-validation"></p>
<div class="form-group">
<input maxlength="4000" id="newsletter-form-email" name="newsletter-form-email" placeholder="Email Address" required="" type="email">
</div>
<div class="form-group">
<input class="main-cta primary-cta brand-cta" id="SubmitButton" name="SubmitButton" type="submit" value="Subscribe">
</div>
<div class="form-group">
<p class="f-errors" id="newsletter-form-validation" style="margin-top: 1em; font-size: small; color: red"></p>
<div class="g-recaptcha-container newsletter-form-captcha" hidden="">
<div id="site_captcha_5">
<div style="width: 304px; height: 78px;">
<div><iframe title="reCAPTCHA" width="304" height="78" role="presentation" name="a-as56iouqrhb2" frameborder="0" scrolling="no"
sandbox="allow-forms allow-popups allow-same-origin allow-scripts allow-top-navigation allow-modals allow-popups-to-escape-sandbox allow-storage-access-by-user-activation"
src="https://www.google.com/recaptcha/api2/anchor?ar=1&k=6LeXhCsUAAAAAPI3u-Fknyv2O01JI1GEfnRWV4it&co=aHR0cHM6Ly93d3cubWFrZXVzZW9mLmNvbTo0NDM.&hl=de&v=MHBiAvbtvk5Wb2eTZHoP1dUd&size=normal&cb=crygc1kehjwa"></iframe>
</div><textarea id="g-recaptcha-response" name="g-recaptcha-response" class="g-recaptcha-response"
style="width: 250px; height: 40px; border: 1px solid rgb(193, 193, 193); margin: 10px 25px; padding: 0px; resize: none; display: none;"></textarea>
</div><iframe style="display: none;"></iframe>
</div>
</div>
</div>
<p class="content-note bottom-note">By subscribing, you agree to our <a href="/page/privacy-policy/">Privacy Policy</a> and may receive occasional deal communications; you can unsubscribe anytime.</p>
</form>Text Content
MAKEUSEOF
Newsletter
Log in
Trending iPhone Tips ChatGPT TikTok PS5 Scams Windows 11 Emojis Explained
* * PC & Mobile
* Windows
* Mac
* Linux
* Android
* iOS
* Internet
* Social Media
* Security
* Programming
* Productivity
* Work & Career
* Creative
* DIY
* Lifestyle
* Smart Home
* Gaming
* Entertainment
* Tech Explained
* Artificial Intelligence
* Crypto
* Tech Jargon
* Networking
* Electric Vehicles
* Reviews
* Buying Guides
* * More
* Free Cheat Sheets
* Deals
* Podcasts
* Join Our Team
* Newsletter
* Advertise with us
* Log in
*
*
*
*
*
*
*
*
* Join Our Content Team
* Home
* Contact Us
* Terms
* Privacy
* Copyright
* About Us
* Fact Checking Policy
* Corrections Policy
* Ethics Policy
* Ownership Policy
* Partnership Disclaimer
* Owned & Operated by Valnet Inc.
Copyright © 2024 www.makeuseof.com
* Home
* Productivity
HOW TO USE CUSTOM FORMATTING IN EXCEL
By Amir M. Bohlooli
Published Nov 30, 2022
Learn how to use custom formatting in Excel to make your spreadsheet more
professional and readable.
Readers like you help support MUO. When you make a purchase using links on our
site, we may earn an affiliate commission. Read More.
Number formatting is a core part of Excel for anyone who uses it often. Through
these built-in formats, you can automatically add symbols to your cells, have
different formats for positive or negative values, and much more.
MUO VIDEO OF THE DAY
Close
Powered By
00:00/10:55
10
MSI Raider GE78 HX 13V Review: The $2500 Beast With Desktop-Level Performance
Share
Next
Stay
SCROLL TO CONTINUE WITH CONTENT
Although the built-in Excel formats are useful, in some cases, they might not
include the particular format that you're after. In that case, you can use
custom formatting in Excel to create exactly the format you want for your cells.
USING CUSTOM FORMATTING IN EXCEL
The built-in number formats in Excel are many and useful, but what if the
specific format that you want isn't included in the built-in formats?
For that, you'll have to create your own custom format in Excel. As a general
note, you can customize nearly everything in Excel. You can even create a button
with a custom function in Excel.
Creating custom number formats in Excel is not easy right-off the bat, but once
you get the hang of the symbols and what they do, it'll be a breeze. To get
started, check out the table below. This table is a summary of some important
symbols in custom formatting.
Symbol
Function
#
Optional number. If the custom format is #, then the cell will display any
number in it.
.0
Decimal point. The decimal points are determined by the number of zeroes you put
in after the period. If the custom format is #.00 and the cell value is 1.5, the
cell will display 1.50.
,
Thousands separator. If the custom format is #,### and the cell value is 5000,
the cell will display 5,000.
\
Displays the character following it. If the custom format is #\M and the cell
value is 500, the cell will display 500M.
@
Text placeholder. If the custom format is @[Red] and the cell value is MUO, the
cell will display MUO in red.
" "
Displays custom text. If the custom format is # "Years" and the cell value is 5,
the cell will display 5 Years.
%
Multiplies the number by 100 and shows it as a percentage. If the custom format
is #% and the cell value is 0.05, then the cell will display 5%
[ ]
Creates a condition. If the custom format is [>10]#;; and the cell value is less
than 10, the cell will display blank.
Another important factor to consider when creating a custom format is the order.
You can include different formats for positive numbers, negative numbers, and
zero in your custom format by putting a semicolon (;) between them. Consider the
format below:
"Positive"; "Negative"; "Zero"
With this formatting applied, if the cell value is positive, the cell will
display the string Positive. For negative values it will display Negative, and
for zero it will display Zero. The custom format doesn't have to include all
three of these conditions. If you put in only the first one, it will be used for
all three.
What we talked about here isn't all that there is to custom formatting in Excel,
but it's a good starting point. Now let's turn this knowledge into skill with
some examples.
1. EXCEL CUSTOM FORMATTING EXAMPLE: TEXT SUFFIXES
To give you an understanding of custom formats in Excel, we're going to start
with a simple example. Let's say that you want to input the duration of some
operations into a range of cells.
By creating a custom format that adds an Hours suffix to the cell, you can
simply just type in the numeral value of the years and skip having to type the
text. Here's how you can do that:
1. In the Home tab, go to the Number section and click on General.
2. From the menu, select More Number Formats. This will open a new window.
3. In the new window, under Category, select Custom.
4. Select any of the formats.
5. Enter the line below under Type:
# "Hours"
6. Press OK.
Now your custom format is ready. Select the cells that you want to apply this
custom formatting on, then repeat the steps above and select the custom format
that you just created. Notice how the cell values are unchanged despite the
Hours suffix being displayed in the cells.
The hashtag (#) in the code represents any optional number, but since we didn't
include any decimals in the format, the decimals aren't displayed in the cells.
In fact, the numbers are all rounded up.
2. EXCEL CUSTOM FORMATTING EXAMPLE: DECIMALS AND THOUSANDS SEPARATOR
It's time we got an example of custom formatting for different values in Excel.
As mentioned before, you can create different formats for positive, negative,
and zero values in a single custom cell format.
Let's build on the previous example. Here we have the same spreadsheet, but want
to change the displayed values for negatives and zeros. To improve the accuracy
and the readability of the displayed numbers, we're also going to add a
thousands separator and a decimal point.
1. In the Home tab, go to the Number section and click on General.
2. From the menu, select More Number Formats. This will open a new window.
3. In the new window, under Category, select Custom.
4. Select the format you created in the previous section.
5. Enter the line below under Type:
###,###.0 "Hours"; "Value is negative!"; "-"
6. Click OK.
Now if you apply this format to the cells, you'll see that it adds the Hours
suffix to the positive values, displays Value is negative! where the values are
negative, and displays—for zeroes. Adding a decimal point to this custom format
inhibited Excel from rounding the numbers, and the thousands separator has made
the numbers easier to read.
Custom formats with text strings allow you to create new things in Excel. For
instance, you can use custom formatting to create a bulleted list in Excel.
3. EXCEL CUSTOM FORMATTING EXAMPLE: COLORS AND CONDITIONS
Now, let's try something new with custom formatting. In this example, we have
the grades of some students. The grades are out of 20, and if a student has got
10 or above, they have passed the exam. If not, then they have unfortunately
failed.
The goal here is to create a custom format that will display Pass in green if
the cell value is 10 or above, and display Fail in red if the cell value is
below 10.
As mentioned before, you can create custom conditions inside brackets ([ ]) and
use brackets to specify font color. Let's get down to it. With that in mind,
let's create a custom format with conditions.
1. In the new window, under Category, select Custom.
2. Select any of the formats.
3. Enter the line below under Type:
[>=10][Green]"Pass";[<10][Red]"Fail"
4. Click OK.
Once you apply the format to the cells, the cells below 10 will display Fail in
red, and the rest will display Pass in green. Again, note that the cell values
haven't changed from the original numbers.
Using custom formatting, you can keep the cell values intact and display text
based on the cell values. If you're interested in using conditions for your
custom formats, you should definitely check out conditional formatting in Excel.
GET WHAT YOU WANT FROM EXCEL
Excel is a tool designed to take the burden off your shoulders when it comes to
dealing with data and numbers. Cell formatting is one Excel feature that
provides help in this aspect, but if the formatting you want isn't listed in the
Excel built-in formats, you don't have to compromise.
With custom formatting in Excel, you can create a unique cell format that
exactly suits your needs. Now that you know enough to get started with custom
cell formatting in Excel, it's time to experiment and improve your spreadsheets.
UPGRADE YOUR TECH IQ WITH OUR FREE NEWSLETTERS
By subscribing, you agree to our Privacy Policy and may receive occasional deal
communications; you can unsubscribe anytime.
Comments
Share Share Share Share Share
Copy
Email
Share
Share Share Share Share Share
Copy
Email
Link copied to clipboard
Related Topics
* Productivity
* Microsoft Excel
* Spreadsheet
About The Author
Amir M. Bohlooli • Senior Writer for Productivity (257 Articles Published)
Amir is a senior writer at MUO. As a PharmD student, spreadsheet apps have
become an inseparable part of his life due to the need to collect and analyze
lab data.
Having always had a love for writing, Amir started writing guides in 2018,
always finding ways to simplify complicated topics.
When he's not crunching numbers or crafting words, you can find him cruising
around town, jamming to his favorite tunes, or gaming like a pro.
Close
RECOMMENDED ARTICLES
Microsoft Excel
HOW TO USE CONDITIONAL FORMATTING IN EXCEL TO FORMAT DATA
Excel's conditional formatting feature lets you format individual cells in an
Excel spreadsheet based on their value. We show you how to use this for various
everyday tasks.
Microsoft Excel
HOW TO CREATE A BUDGET IN EXCEL AND UNDERSTAND YOUR SPENDING
New to budgeting? Here's how to make a budget in Excel the right way.
Gmail
HOW TO CREATE GMAIL FILTERS TO EASILY ORGANIZE YOUR INBOX
Struggling with email overload? Here are some simple steps to create Gmail
filters and organize your inbox effortlessly.
ChatGPT
HOW TO USE "GPT MENTIONS" TO CALL OUT CUSTOM GPTS IN ANY CHATGPT CONVERSATION
Want to use two custom GPTs at once? ChatGPT's got you covered.
Bluetooth
THE BEST BLUETOOTH ADAPTERS OF 2024
Tired of tangles on your desk? Then pick out one of these Bluetooth adapters and
enjoy the wireless lifestyle.
Artificial Intelligence
HOW TO GENERATE AI IMAGES WITH GOOGLE BARD
You can now generate AI images using Google Bard, and it's completely free to do
so.
* ARTIFICIAL INTELLIGENCE
* SAMSUNG GALAXY
WHAT IS CODEGPT AND CAN IT REALLY WRITE CODE?
12 hours ago
WHAT ARE CHATGPT'S DEFAULT PLUGINS, AND WHAT CAN I USE THEM FOR?
4 days ago
THE BEST ANDROID PHONES OF 2024
2 days ago
See More
THE BEST SAMSUNG GALAXY S24 ACCESSORIES OF 2024
5 days ago
THE BEST SAMSUNG GALAXY S24 ULTRA CASES OF 2024
6 days ago
HOW TO CLEAN YOUR APPLE AIRPODS SAFELY
10 hours ago
See More
TRENDING NOW
HOW HULU IS CRACKING DOWN ON PASSWORD SHARING
IS ALEXA ALWAYS LISTENING? HOW TO STOP IT
* Join Our Content Team
* Home
* Contact Us
* Terms
* Privacy
* Copyright
* About Us
* Fact Checking Policy
* Corrections Policy
* Ethics Policy
* Ownership Policy
* Partnership Disclaimer
* Owned & Operated by Valnet Inc.
Copyright © 2024 www.makeuseof.com
✕
Über Google anmelden
user-signalWir schätzen Ihre Privatsphäre
Alles akzeptieren
ZusammenfassungZwecke1522 Partner
Stimmen Sie diesen Maßnahmen zur Datenverarbeitung durch uns und unsere Partner
zu?
Store and/or access information on a device
Precise geolocation data, and identification through device scanning
Personalised advertising and content, advertising and content measurement,
audience research and services development
Einige Partner berufen sich möglicherweise auf ihr berechtigtes Interesse. Sie
können hier Einspruch gegen das berechtigte Interesse des Verkäufers einlegen.
Ihre Auswahl auf dieser Website wird für diese Website angewendet. Sie können
Ihre Einstellungen jederzeit ändern, indem Sie Ihre Einwilligung widerrufen,
indem Sie auf das Symbol in der unteren rechten oder linken Ecke klicken.
Cookies, IDs, Browserdaten oder ähnliche Online-Identifikatoren dürfen für einen
oder mehrere der hier genannten Zwecke verarbeitet werden.
Powered By