How to Use Excel Custom Number Formatting

by Matthew Kuo on April 12, 2013

in Excel, Visual Design

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon

Because it’s buried deep in the cell formatting menu and has very little documentation, Excel’s custom number formatting is probably its most underutilized feature.  Understanding and mastering this feature can greatly enhance the Visual Design of your Excel workbooks.  The key benefit of custom number formatting is:

It allows you to change the appearance of your data without actually changing the data value

Please keep in mind that this is different than conditional formatting which, while allowing you to change the font, font size, text color, and highlighting, does not allow you to change the actual data that appears.

People who don’t understand custom number formatting will often go through the process of changing their data values rather than just the presentation of those values.

For example, consider a user who wants to show 1,000,000 as 1.0 M.  Instead of using formatting, the user decides to divide his value by 1,000,000 and concatenates an “M” to the end of it.  In doing so, not only does he add an additional step in his model, but such a step is also rarely documented, which can create confusion if the work needs to be handed off to another person.

An even worse case if is the user decides to remove the link between the display value and the actual value altogether.  He or she simply hardcodes the value by typing in 1.0 M.  At that point, any changes made to the original 1,000,000 value will not translate through to what is displayed.  Once you master the custom number formatting syntax, you can avoid both of these issues.  Custom formatting is like having your cake and eating it too; it allows you to improve the appearance of your outputs while still maintaining the integrity of your data inputs.

To get a sense of how custom number formatting works:

Select a cell and make it “Accounting” format using the home section of the Excel ribbon
Hit CTRL + 1 to open up the Format Cells menu
Now in the column of formatting options, Select – Custom


The Custom Number Formatting Syntax

The first thing you should notice is that all custom number formats follow a basic syntax:

<Positive Values> ; <Negative Values> ; <Zero Values> ; <Text Values>

Between each of the semicolons is where you define the formatting for each of the conditions specified.  Please note that in some special cases, these conditions are not relevant (ie. date and time formats) and that you don’t necessarily have to specify the condition for zeroes or text values.

Accounting Format Adjustments

In this view, you can also see all the custom formatting adjustments that go into creating the Accounting format.  (This is also a good exercise if you like the Accounting format, as I usually start with Accounting as a base before I make custom formatting adjustments.)

Now to explain what each of these adjustments do:

Format Description
_( Adds a left indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore
$ Special character that will show up in your output without the need to add quotation marks within the syntax
* (Please also note that the code indicated here is both an asterisk and a blank space) The asterisk tells Excel to repeat the subsequent character until the width of the cell is filled; this is why the accounting format has the dollar sign aligned to the left, the number aligned to the right, with as many spaces as necessary in between
#,##0 Displays commas in between every three digits, for all values exceeding three digits
0 Digit placeholder that displays insignificant digits (ie. digits that are zero)
0 The zeros trailing after the first zero indicate how many decimals places you want to show; even if the final digit is zero, it will be displayed in this format
_) Adds a right indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore
“-“ Displays a text value; anything between the quotation marks will be shown
? Digit placeholder similar to 0, but does not display insignificant digits; if the value is zero, it will just be a blank space; in accounting format, because it is in the Zero section of the syntax, it will always appear as a blank space
@ Placeholder for text


Theoretically, there are an infinite number of adjustments you can make the using custom number formatting.  Rather than trying to include all of them, I will focus on explaining the most common and most useful implementations of this feature.  For the most part, I will try to leave out scenarios that can either be typed in manually or created through one of the default number format categories.

Round by Thousands


The most common use of custom number formatting is to round large numbers greater than 1,000.  For example, assume we want to display 1,562,000 as 1.562.

Each comma rounds three decimal places; therefore “,” puts your number in thousands “,,” is in millions and “,,,” is in billions, etc.  In front of the comma, we indicate how many decimal places we want to show.  Note that you typically want to replicate any adjustments you make on the positive side with the negative one, in case your value switches signs.  Don’t forget to include negative notation for the negative number syntax – either a negative sign or a set of enclosed parentheses.


Making this adjustment is especially helpful for charts, where a long number can add unnecessary space to your vertical axis.


Add a Unit Value


In the example above, after you’ve rounded your value, you may also need to add a unit indicator to the end of your new value.  To do this, we simply add the text of the new value within quotation marks in the custom number formatting syntax.

Please note, that in this newly formatted cell, I can type whatever numeric value I want without typing “M” at the end of my entry.  It will still show up in the same format as 1.562 M.


Positive Numbers Green / Negative Numbers Red

[Green] ; [Red]

This format is useful when you have a dynamic model that will spit out both positive and negative output numbers.  In this situation, because we’re only changing the color of the text, not the values that show up, this change can also be made using Excel’s basic conditional formatting.


Add an Indent


The accounting format already adds indents on both sides of the cell for you.  Many people like to indent their cells so that their values don’t ride up right against the cell border.


Turn Zeroes into Dashes or Blanks


The accounting format already turns all zeros into dashes.


Making this adjustment is very helpful when you have a large table of data with both zero and non-zero numbers.  Turning the zeroes into dashes makes it much easier to see where your non-zero data points are.


Using blanks for zeros is also helpful when labeling dynamic graphs.  You could delete the values that are zero, but if the graph values were to change, you’d have to add those labels back every time.  Custom number formatting allows you to automate this process.


Add Parentheses to Negative Percentages


While Excel 2010 has a format for negative values with parentheses in both the default “Number” and “Currency” categories, it does not have one specifically for negative percentage values.  We can use custom number formatting to create our own.  Anytime you show negative values with parentheses, you should also add an indent to the positive value.  That way, positive and negative numbers line up when stacked on top of each other.


Show an Error Message


Since custom number formatting allows you to display pretty much whatever text you want, you can leverage it to display error messages when the situation applies.  For example, assume you have a model that should only output positive numbers.  Anytime a negative value appears, you can have it just display an error message rather than the negative number.


Show Leading Zeroes


Excel’s default formatting cuts off leading zeroes.  Leading zeros are usually necessary for any primary key codes that use them.


Display a Date or Time

When choosing a date format, you should always start by picking the standard date format that’s closest to what you want and then make adjustments in the custom formatting option.  Below are common formatting designations for dates and times.

Format Description
m month as a number with no leading zeroes
mm month as a number with leading zeroes
mmm three letter month abbreviation
mmmm full name of month
d day as number with no leading zeroes
dd day as number with leading zeroes
ddd three letter day of week abbreviation (Wed)
dddd unabbreviated day of week text (Wednesday)
yy two digit year
yyyy four digit year
h hour as number with no leading zeroes
hh hour as number with leading zeroes
m minutes as number with no leading zeroes
mm minutes as number with leading zeroes
s seconds as number with no leading zeroes
ss seconds as number with leading zeroes

A Note on Conditions

Custom number formatting also allows you to add conditions to your formatting.  However, you’re probably better off doing this using Excel’s conditional formatting feature, which is better designed to handle the task.

{ 77 comments… read them below or add one }

Henry September 14, 2013 at 11:31 am

Excellent article!

I’ve been searching for articles that explain the number formatting feature in a very easy to understand format (including examples too).

This should be high up in the google search whenever someone find about “number formatting”.


Antonio February 15, 2015 at 12:25 am

Great article!
Can i get a little help when it comes to this problem please? I have in a cell the fallowing formula =G3-G3*E3 that shows as 999.99 for example i want the number to be 31 font size and the . and the decimals to be 16 font size. I swear to god i searched all internet for this and cud not find a solution.. help please!
Tank you!


Tobias October 31, 2013 at 7:30 am


thanks for your great article to number formatting.
But I can’t accomplish what I want to do.
In germany the “,” is used as decimal separator and “.” used as separator for thousands.
I try to define a number format, which will display a “.” instead of the “,” for a number like “1,253”.
Thanks for your help.


Rebecca November 5, 2013 at 7:55 am

Thank you! You have finally demystified custom number formatting for me. I needed the typical accounting format, only in thousands with the letter K. It works now! This is a huge timesaver.


JOhn November 8, 2013 at 2:30 am

Many thanks, this is what I was looking for, and very helpful.


shy January 13, 2014 at 12:36 am

How do you enter a double quotation character (“) into a format? (Inches)


GeneB March 4, 2014 at 7:16 am

Thanks for the info.
is there a way to custom format a cell that will create a single bar charts with the % trailed by a letter or group of letters (different for each bar).

Example: 45ABC 37CDE 32CD


lisadh March 14, 2014 at 1:01 pm

Thank you! Very helpful – exactly what I needed. 🙂


Todd March 25, 2014 at 2:51 pm

Great article and very useful, however, I need to expand on this and format for Thousand (“K”), Million (“M”) and Billion (“B”). Is there a way for excel to do this for me?



victor lara March 2, 2015 at 12:33 pm

asume the numer is in A1

=IF(A1>=1000000000,TEXT(A1,”$ #.,,,”)&”B”,IF(A1>=1000000,TEXT(A1,”$ #.,,”)&”M”,IF(A1>=1000,TEXT(A1,”$ #.,”)&”K”,TEXT(A1,”$ ###”))))


YIM March 28, 2014 at 1:29 pm

Great article, thanks. I’ve been struggling with a formatting issue and I’m hoping you can help me. I get info in formats below (letters -2 to 4- and numbers). I need to format this so that there is always a space after the letters and the numbers are always in 000.0 format. Any ideas? Below an example of the data I need formatted. Thanks in advance for any suggestions!
LMR 652.0
OH 479.1
ARK 445.0
LMR 230.5
GIWW 485.1


Elliott April 3, 2014 at 10:32 pm

I have a line graph and need to multiply the value by 1000. For example I have label of 4.8. I need it to be 4800. I tried typing *1000 in the custom number box when that did not work I searched on the internet and found your page.

Is it possible to perform a calculation on data labels or will I need to type them all in manually?


carmen April 9, 2014 at 1:30 pm

Is there any way to format a number to have 4 digits between commas (instead of the default three digits)? For example: 1234,5678,9012

Also, is there a way to override the E on very long numbers? I need Excel to recognize a number having 11 sets of three digits… For example:
111,222,333,444,555,666,777,888,999,000,111 without turning it into 1.11E+32 or something like that.

Thank you in advance for your help


Rajeesh May 18, 2014 at 4:27 am

go to format cell, select custom then in type box put this


Alvin May 23, 2014 at 6:22 pm

Thanks for the very helpful tips!
For the number format, can it be set in 10,000 instead of 1,000 (in China number in 10k is more popular)?
For example, 1,234,400 presents as 123.4?


jemma May 28, 2014 at 7:54 am

Is there a way of making a word have a numerical value so that on a spreadsheet it is shown as a word, however in a sum it acts as a number.
on the spreadsheet it shows as “amber” but in a sum it shows as “0.20” if printed or viewed


Sam June 12, 2014 at 11:05 pm

Thanks for the article.
I encountered a Excel number format like the following


What’s the [$-10C09] for?


Uday October 14, 2014 at 7:23 pm

Hi all:

I am trying to delete ‘Custom’ number formats for cells in 1 specific excel workbook.
When I do so – the following error message shows up.

“You cannot use this command on a protected sheet. To use this command, you must first unprotect the worksheet (Review tab, changes groups, unprotect sheet button) You may be prompted for a password”.

This workbook isn’t protected to begin with.
When we navigate to the ‘Review’ tab within it – there is an option to ‘Protect’ the workbook, but not unprotect it as indicated by th error.
So, this workbook must already be unprotected.

Any clues about why this is happening and how I can fix this?


Zoe November 18, 2014 at 4:28 am

Hi Uday,
Sometimes when that happens to me, I find that the “Design Mode” button is pushed in. I have to take it out of Design Mode (under the Developer tab), then go to the Review tab and work with the Protect/Unprotect buttons.

I hope this helps!


Barbara February 16, 2015 at 5:58 pm

Hi…any suggestions how I format a cell for the Massachusetts learner’s permit number? The format is:


That’s a small “s” in front. I hope someone is out there to help! Many thanks.


Pete Versneij April 12, 2015 at 7:07 am

Thanks, wonderful info. Very clear en succinct explanation. Without tedious irrelevancy.

The highlight was the infi about how to suppress the thousands in the formatting.



Kadambi May 13, 2015 at 7:48 am

Great information. Thanks!

I just remembered that I once ran into the trouble of aligning a row of number range which were separated by an en-dash. For example: 2–32 in row 1; 0–12 in row 2; and so on. I wanted to aligned them in a way that they were all centered at the en-dash, i.e. “–”. I was never able to figure how to do that.

Any insights?


Tiago June 11, 2015 at 7:17 am

hello, i have a excel char

In my Y-axis i have range numbers 0,1 to 1,0 and i want to change the , to a .

how do i do that?


Arijit Mukhopadhyay July 10, 2015 at 2:03 am

@Tiago: You can go into the Excel Options to do this. Follow the steps mentioned below and you should be good:

File >> Options >> Advanced >> Editing Options >> Use System Separators

You can change them to whatever you like in there. Hope this helps.



Dan July 11, 2015 at 12:41 pm

Great information. Given that custom number formats are pretty hard to assign, I created a number format macro that allows a quick keyboard shortcut to assign your most common number formats. It also allows you to quickly create some of these custom number formats such as millions with “M”, negative percentages with parenthesis, and ppts or bps (percentage points or basis points). It’s the first macro in Dan’s Macros Productivity Suite. Hope you find it helpful!



Chris July 13, 2015 at 8:22 am

I frequently have to input 22-digit numeric strings into Excel. The string is a combination of 7 fields. I would like to separate the string into the 7 fields using dashes via a custom format. For example:
1001015951525100330000 to 10-010-15-9515-2510-0330-000

I run into the following problems:

1) If I make the cell format NUMBER and use a custom format of 00-000-00-0000-0000-0000-000 or ##-###-##-####-####-####-###, excel changes my input of 1001015951525100330000 to scientific notation (1.0010159515251E+21) which changes the value of any number after the 14th to 0 thus rendering the last 8 digits of my 22-digit string meaningless. Well…they have meaning…so, no good.

2) If I make the cell format TEXT to correct for the scientific notation problem, the custom format options in Excel for text are extremely limited. I haven’t been able to find how to add dashes to text using a custom format.

It seems like my scenario runs into two competing issues within Excel. First, Excel’s automatic conversion of long numbers to scientific notation (can’t turn off). Second, Excel’s limited custom formatting of text.

Any ideas?

Thanks in advance!


Andy October 30, 2015 at 7:53 am

Have a look at this KB entry and see if there’s a solution in later versions of Excel. Excel can handle custom formatting with numbers up to only 5 digits. Formatting as text will let you insert up to 1024 digits in a cell, but then custom formatting doesn’t apply, as you’ve discovered.

good luck.


Andy October 30, 2015 at 7:54 am

sorry, up to 15 digits.


Thuloane September 9, 2015 at 7:52 am

A very basic yet effective guide! I have enjoyed learning from this article… and just what I needed exactly.

Thank you.


Jay Sandeen October 23, 2015 at 11:34 am

I have a spreadsheet with columns that display latitude and longitude that I need to import into ArcMap. In order to do that I need them to have up to three whole numbers with eight decimal places. The numbers as they appear now are single ten digit numbers with no decimal places. Is there a way to format the existing numbers to a XX.XXXXXXXX format? I’ll also need to make all the longitude values negative.


sharath November 4, 2015 at 1:53 am


As per the above example shown requesting you to pl guide how to format the numbers by putting comma after 3 numbers as shown above since the same is not getting formatted in my excel sheet and shows as 11111000,000,000.00. Please guide

I m using Excel 2010


gs November 7, 2015 at 6:01 pm

how to 000,000.00 format


Tony November 10, 2015 at 11:08 am

When I use a formula, =CONCATENATE(DAY(MID(A33,5,LEN(A33)-4)),MONTH(MID(A33,5,LEN(A33)-4)),RIGHT(YEAR(NOW()),2))
the result, in my example, is 91115
I used 000000, as the special format, hoping to see 091115. It didn’t work
I had to copy the cell and paste it back into the same cell using the “value” paste option, and then press “Enter” for the special format to work.
Is there a better way to do this?


matej November 27, 2015 at 1:40 am

How Do I Format A Number To Cut Off The Thousands but not round?


Matthew Kuo November 29, 2015 at 3:23 pm

Hi Matej,

I assume when you say not round, you mean you don’t want it to round up? If that is the case, you can just use Excel’s ROUNDDOWN function to prevent Excel from rounding. I’m not sure you can do this in the formatting interface, so you may need to just divide your number by 1000 first, then use the ROUNDDOWN function over that value.



Rasheena December 13, 2015 at 8:31 pm

Is it possible to round off a number still keep the decimals using Custom Format. Like when i input numbers 51,595.20 or 88,626.89 it should automatically change to 51,595.00 or 88,627.00

Please help.


Rob Krauzig December 29, 2015 at 9:31 am

i need to make a custom format that shows an underscore character, when i use underscore it leaves a blank.


Matthew Kuo December 30, 2015 at 11:56 pm

Hi Rob,

To show the underscore character, you need to put quotes around it, like this


Let me know if that works.



Shrikant December 29, 2015 at 8:53 pm

I have following requirement:

15 should be formatted as 15, NOT 15.00
14.3453453 should be formatted as 14.35
12.1 should be formatted as 12.1
0 should be formatted as blank

please help and thanks in advance


brad bialy January 20, 2016 at 8:18 am

I was looking for some help on how to paste special from the formula =Today() to a correlating pivot table that is separated by days of the week (DDD) but when the =today() formula is recorded it messes up my pivot table. When I try and past just the values It pastes a strange number (42389) instead of the Wed it ? HELP ME PLEASE!


Matthew Kuo January 23, 2016 at 3:53 pm

Hi Brad,

Would need a little more detail to help you with this, but I believe you should be able to format values in a pivot table. Just click the 42389 and change the format of it to a date, after you’ve done the paste special values. It should flip to 1/20/2016 once you do that.

Let me know if that works.



George Bernabe February 3, 2016 at 9:11 am

I have a spreadsheet with data exported from SAP. In the same column, I have a bunch of time values either custom formatted in hours or custom formatted in minutes. So in the same column, I could have 7 H (7 hours) or 5 MIN (5 minutes). The problem is I want to add all these time values but need to convert either the hours to minutes or the minutes to hours but can’t do it because the value of the cell is just a number. Is there a way to incorporate the custom format for hours or mnutes in a formula so Excel knows a number is either an hours value or a minutes value?


Matthew Kuo February 6, 2016 at 1:33 pm

Hi George,

I’m pretty sure you can’t do this with formatting alone. I think your best bet is to create a separate cell, with IF logic that converts value into the lowest common denomination, which I think in your case is minutes. For example, if the rightmost value of the cell is an H, take the leftmost value and multiply it by 60.

Let me know if that helps.



Matt Regier February 16, 2016 at 7:46 am


Does anyone know of a way to make cell appear as a “-” a range of values. So if the number is between -0.499 and 0.499, the cell will appear as a “-“? Similar to the turning zeros in to dashes section above but for this range.



matt regier February 17, 2016 at 3:20 pm

Does anyone know of a way to format that would make any number that is between 0.499 and -0.499 appear as a “-“. Similar to the Turn Zeroes in to Dashes section above but for this specific range. I tried conditional formatting for the range but it still treats the number as a negative and thus appears as 2 dashes “–”

Thanks in advance!



dinesh March 11, 2016 at 3:59 am

Dear Matt you have to simply do conditional formatting by using a simple formula.
Suppose the value is entered into cell d2 then type the formula in “use a formula to determine which cells to format” in conditional formatting options
=AND(D2-0.499) and customs format the number as “-“


bilal March 12, 2016 at 8:40 pm

I dont want to round my number.
I have 5,123456789 but it seems 5,000000000
how can I do that?
please help me


12bet indo April 10, 2016 at 12:16 pm

Nice post. I learn something totally new and challenging on sites
I stumbleupon on a daily basis. It’s always exciting to read through content
from other writers and practice something from their websites.

Reply April 13, 2016 at 7:40 am

Thanks for finally writing about >How to Use
Excel Custom Number Formatting <Liked it!


asse da stiro June 2, 2016 at 7:04 pm

My brother suggested I might like this website.
He was totally right. This publish truly made my day.
You can not consider simply how so much time I had spent for this information! Thanks!


whitelabel API June 4, 2016 at 8:41 am

Hello, everything is going perfectly here and ofcourse every one
is sharing information, that’s actually fine, keep up writing.


unlocked cell phones June 7, 2016 at 8:06 pm

Haviong read this I thought it was rather enlightening.
I appreciate you spending some time and eneegy to put this article together.
I once again find myself spending a signnificant
amount oof time both readong and posting comments. But so what, it wass still worth it!


hair relaxer June 11, 2016 at 6:55 am

It’s awesome in favor of me to have a site, which is valuable for my know-how.

thanks admin


celll2get review June 14, 2016 at 12:17 pm

Thanks a lot for sharing this with all people
you actually knlw what you’re talking about! Bookmarked.

Please additionally talk over with my site =). We can have a hyperlink trade agreement between us


Robert June 19, 2016 at 8:26 pm

Thanks ffor finally talking about >How to Use Excel Custom Number Formatting <Loved it!


Naqash June 22, 2016 at 11:07 pm


for the above numbers , i want landing 0s 23, but last digit changes once i make formating
for example it is showing. 00000003112706314419380 (last digit become 0 insted of 7)

someone plz help me out


apa itu seo June 24, 2016 at 6:15 am

I am actually thankful to the owner of this web site who has
shared this wonderful post at at this time.


Lieng July 3, 2016 at 3:06 am

I want replace 0+000 are 0 in excell


efek gitar custom July 22, 2016 at 2:26 am

I just like the valuable info you provide on your articles.
I will bookmark your blog and test again here regularly.

I’m fairly sure I will be told a lot of new stuff right right here!
Good luck for the next!


LJ September 4, 2016 at 6:46 pm

Does any one know how to format a cell so that only a portion of the text is shown, eg only the first three letters? can I put this info in quotation marks or brackets and condition the formatting of the cell to only show the text between?


dia del fans paris September 5, 2016 at 2:40 pm

I like reading through an article that can make people think.
Also, thank you for allowing for me to comment!


Wayne Moores September 7, 2016 at 5:19 pm

I am using a drop down box
what i am trying to achieve is if the value 1 is selected specific text will appear if the vale 2 is entered specific text will appear and so on. so far i go to the drop down box select format cells custom
and use this formula [=1]”octagonal”;[=2]”round”;”not selected”;
this works so far but i can not add options for if 3,4,or 5 are entered
please please help or offer another solution.


Moe September 8, 2016 at 10:56 pm

What if vertical axis contains small values. e.g. 0.000002
how to show the numbers in micron or nano scale and then enter a label beside axis? something like “display unit” in axis option.
Many thanks


DON MORAN October 11, 2016 at 3:03 pm

Does Excel have an automatic number format setting as does “.numbers” on Macs? With that setting it shows decimal AS ENTERED, e.g. 1 shows as 1 & 1.0 in another cell shows as 1.0 .


George Varghese October 25, 2016 at 6:34 am

Hello my name is George. I am dealing with an issue regarding larger numbers in a Vlookup formula. Numbers with more than 12 digits are shown in scientific format and it won’t let me to use as a reference cell for the Vlookup formula. I know I can select ‘0’ in Custom format or use the Trim function so all digits will be displayed, but still it won’t let me to use as a reference cell in a Vlookup formula. So here is the question:

How to use numbers with more than 12 digits as reference cells in a Vlookup formula?

Any help regarding the above would be really appreciated. Thanks.


Roy April 12, 2019 at 8:46 pm


Wrap your reference value source in the TEXT() function apcifying the “0” format. For instance, A1 has your lookup value and TEXT(A1,”0″) is your lookup reference. Excel will treat it as a number when doing the lookup. If you somehow have a version that doesn’t (pretty sure even Excel 98 will do so), then wrap all that in the VALUE() function.


sukanya November 11, 2016 at 10:08 am

hi folks,
i just want that cell should lead with zero.


Badal November 13, 2016 at 5:17 am


For the above numbers , I want to put as same, but last digit changes once i.e. 1.92275E+17 . After custom changes it makes 192275315510021000 (last 3 digit become 000 insted of 043) , 192275315510031000 (last 3 digit become 000 insted of 817)

Please help .


Brakeman July 10, 2018 at 10:54 am

################0 works but the issue I found was that the number was converted upon paste. If I paste the number inside the formula bar, the problem goes away.


emergency plumber December 5, 2016 at 7:42 pm

Great post.


aleksandar December 18, 2016 at 9:40 am

Hi, thanks for all the help, great tips. I am hoping you might help me with the custom format, where i want the content of the cell to display some TXT code, depending on the value of the cell.

So, for example, I am using : [=1]”SI”;[=2]”SO”;” ”
But in case i want to add third criteria, so : [=1]”SI”;[=2]”SO”;[=3]”R”;” ” gives back and error… I can’t figure out what’s the reason, must be some restriction?



forex forum February 13, 2017 at 4:05 am

It’s actually a cool and helpful piece of information. I am happy that you just shared this useful info with us.

Please keep us informed like this. Thanks for sharing.


Kamal February 20, 2017 at 8:06 am

Ask a Question.

I wan to display numbers in an Excel cell in format 999,99,999.99

The middle 2-digit 99, is not a typo. This is how currency is written in India.

999,99,999.00 in words is 999 Crore, 99 Lakhs, 999 Thousand and zero paisa.


yky May 12, 2017 at 7:57 pm




Rani June 6, 2017 at 11:31 am

Hi, i have a numbers in column AU & AV with %, M & B visible in the cells but not is formula bar.Now i wants to change
% as ” pct”
B as ” bln”
M as ” mln”

can you please help me to do it. I tried replace, substitute, text, left, right functions but no use.


Tiago Correia August 20, 2018 at 8:09 am

Hi there,

This is my question (help!): when typed in a cell the text (ABC121234), it should format to (ABC-12-1234). If i put (123121234) it shows (123-12-1234), which is correct. but with the first 3 letters, the format doesn’t show correctly.

How come?

Keep the good work. Thanks


Prakash Hatole August 21, 2018 at 2:45 am


I need below solution –
If cell value is <=5 then icon should be DARK RED
If cell value is 3 then icon color should be RED
If cell value is 2 then icon color should be ORANGE
If cell value is 1 then icon color should be YELLOW
If cell value is <=1 then icon color should be GREEN


Leave a Comment

{ 3 trackbacks }

Previous post:

Next post: