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
 

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

custom_number_formatting0

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

0.000,,

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.

custom_number_formatting1

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

custom_number_formatting2

Add a Unit Value

 “M”

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.

custom_number_formatting3

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.

custom_number_formatting4

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.

custom_number_formatting5

Turn Zeroes into Dashes or Blanks

“-“

The accounting format already turns all zeros into dashes.

custom_number_formatting6

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.

custom_number_formatting7

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.

custom_number_formatting8

Add Parentheses to Negative Percentages

[Red](0.0%)

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.

custom_number_formatting9

Show an Error Message

“ERROR”

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.

custom_number_formatting10

Show Leading Zeroes

000000

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

custom_number_formatting11

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.

{ 71 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”.

Reply

Antonio February 15, 2015 at 12:25 am

Greetings!
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!

Reply

Tobias October 31, 2013 at 7:30 am

Hey,

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

Reply

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.

Reply

JOhn November 8, 2013 at 2:30 am

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

Reply

shy January 13, 2014 at 12:36 am

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

Reply

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

Example: 45ABC 37CDE 32CD

Reply

lisadh March 14, 2014 at 1:01 pm

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

Reply

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?

Thanks,

Reply

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,”$ ###”))))

Reply

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!
OH976.0
LMR 652.0
LMR652
OH 479.1
ARK 445.0
AR448
CW80
LMR 230.5
LMR113
CW277
GIWW 485.1

Reply

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?

Reply

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

Reply

Rajeesh May 18, 2014 at 4:27 am

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

Reply

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?
Thanks
Alvin

Reply

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.
eg:
on the spreadsheet it shows as “amber” but in a sum it shows as “0.20” if printed or viewed

Reply

Sam June 12, 2014 at 11:05 pm

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

[$-10C09]#,##0,;(#,##0,);-;

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

Reply

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?

Reply

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!

Reply

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:

s55-55-5555

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

Reply

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.

Pete

Reply

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?

Reply

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?

Reply

July 10, 2015 at 2:03 am

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

Cheers
Arijit

Reply

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!

Dan

Reply

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:
00330000 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 00330000 to scientific notation (1E+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!

Reply

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.

Reply

Andy October 30, 2015 at 7:54 am

sorry, up to 15 digits.

Reply

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.

Reply

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.

Reply

November 4, 2015 at 1:53 am

11,111,000,000,000.00

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

Reply

gs November 7, 2015 at 6:01 pm

how to 000,000.00 format
help

Reply

November 10, 2015 at 11:08 am

Hi,
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?

Reply

matej November 27, 2015 at 1:40 am

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

Reply

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.

Best,
Matthew

Reply

Rasheena December 13, 2015 at 8:31 pm

Hi,
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.

Reply

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.

Reply

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.

Best,
Matthew

Reply

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

Reply

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!

Reply

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.

Best,
Matthew

Reply

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?

Reply

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.

Best,
Matthew

Reply

Matt Regier February 16, 2016 at 7:46 am

Hi,

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.

Thanks!
Matt

Reply

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!

Matt

Reply

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 “-“

Reply

bilal March 12, 2016 at 8:40 pm

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

Reply

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!

Reply

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!

Reply

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.

Reply

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!

Reply

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

Reply

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

Reply

June 19, 2016 at 8:26 pm

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

Reply

Naqash June 22, 2016 at 11:07 pm

9387
3478
9385
7331

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

someone plz help me out

Reply

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.

Reply

Lieng July 3, 2016 at 3:06 am

I want replace 0+000 are 0 in excell

Reply

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!

Reply

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?

Reply

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!

Reply

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.

Reply

Moe September 8, 2016 at 10:56 pm

Hi
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

Reply

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 .

Reply

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.

Reply

sukanya November 11, 2016 at 10:08 am

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

Reply

Badal November 13, 2016 at 5:17 am

1043
1817

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 1000 (last 3 digit become 000 insted of 043), 1000 (last 3 digit become 000 insted of 817)

Please help .

Reply

December 5, 2016 at 7:42 pm

Great post.

Reply

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?

thanks

Reply

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.

Reply

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.

Reply

Leave a Comment

{ 3 trackbacks }

Previous post:

Next post:


Related pages


how to check for duplicates in excelucla salary databaseexcel randexcel formula todelete a row in excel shortcutexcel remove rows containingucla mba gmat scorehistogram excel examplecountif in excelr side by side barplotremove duplicates in excelduplicates in excel columnexcel vlookup table arrayduplicate cells in excelvlookup function helpwhat are the most used websitesoffset match functionnested if function excelformula for hlookupvba remove duplicatestutorial excel formulasexcel 2010 stacked column chartexcel extract duplicatessheet formula excelexcel formula does not equalvlookup in tablehistogram analysis excelquestions to ask during an mba interviewremove duplicate data in excelexcel formula criteriaformula sheet exceldoing formulas in excelthe goal goldratt chapter summarytick charexcel custom cell formatmyuclaexcel spreadsheet inventorybell curve template excelvlookup lock table arrayadd axis label excelbell curve graph excelmuuclainsert macro in excelucla parking ticketsdownload microsoft powerpoint 2010 free trialexcel digitthe goal goldratt chapter summaryirs 2015 withholding calculatorucla rosenfeld librarynaming arrays in excelcreate histogram on excelexcel nested formulasms excel vlookup and hlookupdata table excel sensitivity analysisexcel consolidate duplicatesexcel vlookup match two columnsisna formula in excelexcell countifexcel weekly calendar templateexcel 2010 identify duplicatesexcel tickms excel 2007 vlookupto find duplicates in excelwhat does too many different cell formats mean in exceldocument tracking template excelhow to do formulas on exceltornado chart excelwhat is vlookup formulawriting excel formulasexcel graph barformula for bell curve in excelirs allowance calculator
\n