The other day I had to make an excel sheet for tracking all errors across one of the applications we are doing for our customer. The format was something like this,
We wanted to use a consistent message id format [4 digits: 0001, 0002, … , 1000 etc.]. Now I do not want to type “0001” in excel, instead I wanted to type 1 and I want excel to convert that to 0001 for me. So I started looking for a custom cell format and dug a little deeper to understand those. I thought it would be nice to share them to you all.
First take a look at how the cell formatting dialog box – number tab looks like:
Now apart from the built in types General (leave excel to guess the data format), number, currency, accounting (uses the separators, () notation etc.), date, time, percentage, fraction, scientific, text there are 2 interesting types of formating.
Special: Used for phone number, zipcode, social security number formats depending on the locale you select. For eg. for US they would be phone number [xxx-xxx-xxxx], ssn [xxx-xx-xxxx], zipcode[xxxxx, xxxxx-xxxx].
Custom: Used for creating your own cell formatting structure. This is a bit like regular expressions but in entire microsoftish way. Any cell custom format code will be divided in to 4 parts : positive numbers ; negative numbers ; zeros ; text. If your formatting codes have less number of parts (say 1 or 2 or 3) excel will use some common sense to find out which ones are for what.
Ok, without further confusion, this is probably how you can use the custom cell formatting feature in Microsoft excel.
Some explanation that you can skip if you already get it
- For formatting a number [eg. 1] to fixed number of digits [eg. 0001] you have to use 0000 as the custom formatting code
- For formatting a phone number [eg. 18003333333] to a standard phone number format [eg. 1 800-333-3333] you have to use 0 000-000-0000 as the custom formatting code
- To fill rest of the cell with a character of your choice [eg. *] you have to use @**(this applies for text inputs)
What are your favorite data formatting tricks? [Also read : Creating cool dashboards in excel using conditional cell formatting]
212 Responses to “Custom Cell Formatting in Excel – Few Tips & Tricks”
[...] Krunker | Technology Around the World wrote an interesting post today onHere’s a quick excerpt The other day I had to make an excel sheet for tracking all errors across one of the applications we are doing for our customer. The format was something like this, We wanted to use a consistent message id format [4 digits: 0001, 0002, … , 1000 etc.]. Now I do not want to type “0001? in excel, instead I wanted to type 1 and I want excel to convert that to 0001 for me. So I started looking for a custom cell format and dug a little deeper to understand those. I thought it would be nice to shar [...]
Many Thanks. This is really informative. I want to know how a program can read"contents" of a Custom cell. WRT the eg. above, if the Value After is 0001, when a program reads the value, it reads it as 1 instead of 0001.
@Swati - you are welcome. Yeah, you are right, the cell format changes the value at UI level alone, when you access the value from formulas / vba / scripts the value is still what you have entered (ie 1 in your case)
Welcome to PHD, enjoy 🙂
Thanks. So in case, I want my program to pick the value the way it appears on the UI, how can that be done?
@Swati ... wow, thats a tricky one.
You can use the following function:
Function fetchValue(incell As Range) As String
fetchValue = incell.Text
End Function
Just add it to your workbook's VBA code in a module and start accessing it from code. I havent tested it extensively, but it seem to be working fine.
Just curious, why would you need the value of the cell as it is formatted against its actual value in code? Chalo, let me know if this is working for you 🙂
I HAVE USED CUSTOM FORMATTING AND ADDED PREFIX AND SUFFIX TO A NUMBER IN WHOLE ROW. BUT NOW WHEN I COPY IT, IT SHOWS THE ORIGINAL NOT THE FORMATTED NUMBER. KINDLY HELP.
I found your blog via Google while searching for free personal finance tips and your post regarding use custom cell formats in Microsoft Excel - tips | Pointy Haired Dilbert - Chandoo.org looks very interesting to me. I always enjoy coming to this site because you offer great tips and advice for people like me who can always use a few good pointers. I will be getting my friends to pop around fairly soon.
[...] read Using Custom Cell Formats in Excel - Tips & Tricks to findout how to format dates, currencies, special formats etc. Tags: excel, formatting, howto, [...]
Dear Sir,
I also have some problem in excel, can you solve my problem. I gave you my email ID .
Thanking you
I want to sort/filter data by their different custom format is it possible to do so. ex.
5.660/Pcs
0.258/Sqm
5.213/Rmts
5/nos
these kind of custom formatted details are downloaded from Tally ERP -9 I want to sort data by Sqm/Pcs/Rmts and other UOM . all data are in custom number format
@Jitender ... surething, you are welcome to write a comment explaining the problem, that way, even if I dont know the solution, there will be one of our excellent readers who would know how to solve it for you, and they may come forward and post it in replies. Otherwise, feel free to write me an email... 🙂
I've been browsing this thread for awhile and there is one format I can't seem to create a custom format for. If I want to go from 1234-5678 to 12345678 how can I do that?
Thanks!
@ Kc... thanks,
I dont know any format that can 12345678 from 1234-5678, you can use substitute("-","",1234-5678) though... let me know if you findout how to do this.. I will sponsor you a donut 2.0 😀
[...] thus making the 01001 as 1001. Worry not, you can use rept() the extra needed zeros. You can also custom format cell contents to display zip codes, phone numbers, ssn etc. Example: Use zipcode & REPT("0",5-LEN(zipcode)) to convert zipcode 1001 to [...]
[...] Try these other date formats as well. Learn more about custom cell formatting. [...]
tour
[...] How to use custom cell formats in Microsoft Excel - tips | Pointy Haired Dilbert - Chandoo.org Custom Cell Formatting in Excel - Few Tips & Tricks [...]
i want to calculate formula for example: 500PCS X 12 = 6000. but when i mention PCS or doz or set after any digit as above, formula says #VALUE. It can not give result.
suppose 500PCS IN A CELL x 12 IS IN B CELL FORMULA IS =SUM(A1*B1). kindly help me abt.
@Asghar: Welcome to Chandoo.org.
You can correct the formula in the following ways:
(Simple) do not enter PCS, Doz etc in the cell A1, but use another cell like A2 maintain the units.
(slightly complex) use custom formats to display "PCS" or "Doz" next to the value so that formulas still work while you can maintain the desired look. You can do this by defining a custom formatting code like 0 "PCS" or 0 "doz"
See this image.
Let us know if you still have any trouble. 🙂
Asghar, I have another way to fix your solution
Select the cells you want to put the number, Go to Number formatting/ Customer and type ####"PCS"
This will add PCS to the cells you choose, you just only type 500 or any number you want. Then do your calculations, hope that helps. Now choose the easiest way you want
Asghar,
You can try using the left function. Using your example:
=(LEFT(A1,3)*B1) ought to give you 6000.
Thus said, you need to customize this formula in case the number of units run into thousands. 🙂
Image URL:
http://i287.photobucket.com/albums/ll133/pointy-haired-dilbert/excel-formula-help-asghar.png
Also, you dont need to write sum(a1*b1), you can directly write =a1*b1 and it works.
Btw, Somnath: good suggestion 🙂
Chandoo,
I am enjoying my first visit to your site. Looking at the thread posted by KC, you are absolutely right. Substitute function is the way to go about it.
Considering 1234-5678 is in cell A1, the substitute function will be:
=SUBSTITUTE(A1,"-","",1)
Lots of good stuff in here!!! Makes me feel nostalgic about my good old college days. 🙂
@Somnath: thank you, I am very happy you liked the site and actively contributing to it. I have learned so much from readers like you and I really enjoy interacting with new people through this site. Keep visiting and share your knowledge with all of us. 🙂
HOW ABOUT THIS ...12345-12 => 12345-13 ( THE LAST DIGIT )
12345-19=> 12345-20 ( PO WITH RUNNING NO.)
Any tips.
@Din: Assuming the value 12345-12 is in cell A1, using the formula =LEFT(A1,6)&RIGHT(A1,2)+1 will give you the desired result. A more refined formula is =LEFT(A1,SEARCH("-",A1,1))&RIGHT(A1,LEN(A1)-SEARCH("-",A1,1))+1 which will work will any number of digits.
assuming the value to be changed is in cell A1, '=LEFT(A1,SEARCH("-",A1,1))&RIGHT(A1,LEN(A1)-SEARCH("-",A1,1))+1' should work:-)
2581-14 #NAME? A1, ‘=LEFT(A1,SEARCH(“-”,A1,1))&RIGHT(A1,LEN(A1)-SEARCH(“-”,A1,1))+1
I TRY RESULT SHOWN . CAN YOU HELP !
I have a problem when formatting a cell that has 16 or more digits, using the Custom format. We are working with Credit Card numbers and want to be able to enter them as a 16 digit number like this: 1234123412341234 but want them to look like this: 1234 1234 1234 1234
The problem we’re having is that the last digit always turns into a 0 (zero) and the number ends up looking like this: 1234 1234 1234 1230
The custom format type we created is: 0000 0000 0000 0000 but we’ve also tried: #### #### #### #### with the same results.
This only happens when using 16 or more digits. If we use 15 or less digits, the Custom format works fine. Unfortunately, credit cards have 16 digits so we’re stuck with the problem.
We’ve tried this on Excel 2007 with service pack 1 and on Excel 2000 with service pack 3 with the same results. The Operating Systems we’ve tried it on are Windows XP with Service Pack 3 (and all other Microsoft Updates installed) and on Windows 2000 with Service Pack 4.
Any help would be greatly appreciated.
@James,
checkout the post: http://chandoo.org/wp/2009/04/07/using-credit-card-numbers-in-excel/ to understand how this can be solved
[...] asks in Custom Cell Formatting in Excel Post, I have a problem when formatting a cell that has 16 or more digits … We are working [...]
Hi Chandoo --
Awesome suggestions.... big question for ya! We have P.O. numbers that come from a system with the standard format of XX-XXXXX. The first 2 XX are the year - the very last X could be there or not be there depending on whether we are at the beginning of the year or the end of the year. When we dump data from this system into excel, 95% of the PO numbers format correctly in the column: 09-1234 or 09-12345 for example.
The other 5% show as a date in the field: Sep-67. If you place your cursor in the formula bar it says 9/1/8267. The REAL PO number for that one is 09-8267. I've tried a ton of different ways to try and make a custom format for the user and am coming up blank every time.
Any help would be of tremendous help! Thanks!
SReid
@SReid: welcome to PHD. I will try this and get back to you. But I guess the custom code 00"-"00000 should work?
Hi Chandoo - Sorry no luck on that format. The correct PO number is 08-8979. When dumped into Excel, and reviewing it the cell - it changed to a date format of Aug-79. If looking at it in the formula bar, it is 8/1/8979. If I apply the custom format you suggested, it changes the PO number in the cell to 25-85766 and in the formula bar to 2585766 (without the dash). Crazy I tell you! Crazy!
Thanks! SReid
@SReid: when you dump data, excel assumes the formats of the data it is pasting. so even before pasting the data, you need to set the cell formats. Also, I see that your data comes with hyphen (-), in which case, you need not use any format codes, but just set the cell formats to TEXT from GENERAL. If you paste a lot of data, select the entire sheet and press ctrl+1 and set the formats to text.
Let me know if you have some problems
hi chandoo,
hope you can help me with this problem; it's something about number formatting and "Creating dash boards using excel conditional formatting" @ http://snipurl.com/g2rwb
consider these data:
set 1: 2851/3.00 = 0.001052262...... = 0.11% (% with 2 decimal)
set 2: 2851/3.01 = 0.00105576..... = 0.11% (% with 2 decimal)
when i do a IF comparison for both 0.11%, it's NOT EQUAL. This creates a little problem with the dashboard formula whereby it will show icon ? instead of ?.
The only way to 'solve' this is to force format each cell with this: =text(2851/3.00,"0.00%") and =text(2851/3.01,"0.00%"). Only then that bot sets are EQUAL.
As there a simpler way to TRULY HARD FIX the decimal numbers in a percentage format? 0.11% == 0.11% 🙂
Thanks!
rewrite:
This creates a little problem with the dashboard formula whereby it will show icon "^" instead of "o".
cybpsych,
use ROUND(2851/3.00,4) and ROUND(2851/3.01,4) instead and the two values will be equal.
Hi CHandoo....
I am dumping the data from SQL. It is a varchar field. I have tried setting the column to TEXT. This does not work. SQL has the field (correctly) as 08-8979. When dumped to Excel, it changes it to: Aug-79. If I try to change the format to TEXT, the data then CHANGES to: 25-85766.
The crazy part of this is that it only happens on about 5% of the data in that column. All of the rest of the data comes over in the correct XX-XXXXX format.
Any other ideas?
Thanks!
@SReid: Hmm... do you have some automated program that is dumping the data from SQL? What do you mean when you say you have set the column to text? Is it in excel or sql. You should set it in excel. Another thing that comes to my mind is to use text import wizard and select the column type as text in the wizard. I know this could take more time, but if you import once in a blue moon it might be worth the effort.
In the worst case, you may need to get some vba that would take the sql data and properly dump it.
Also, when I try to paste the value 08-8979 in to excel, even though cell format is "TEXT" it is still displayed as Aug-79, but when I select the little paste icon beneath the pasted cell and say "match destination format" it is correctly displayed as 08-8979. May be excel is forcing itself to use source formatting (weird how it decided that source format is in date...?).
I know this was posted 3 years ago but maybe someone else will have a similar issue so here is the answer: I'm not sure why exactly but if you use 00"-"0000 it works. When you put in the 5th 0 it changes it to 00-88979? Strange! But it worked. 🙂
hi chandoo, i'm curious on the following:
how to custom format this number: 883509615 --> 883.509615 Million or 0.883509615 Billion?
don't bother on the decimal numbers; I can round them up as per Robert's suggestion above 😉
thanks!
@Cybpsych: hmm, I am not aware of custom format codes that can do this. I can suggest a formula version though, but I guess it should be obvious. Let me know if you come across any custom codes that can do this. btw, if you use the numbers in charts, excel has a facility to show them in millions, billions etc. but when they are used in cells... I am not sure which option would convert them to million etc. at format level while keeping the value intact...
cybpsych,
use the thousand operator in custom number format:
Assumed you have the cell value 12345678.
Custom format #,###.00, will display 12,345.68
Custom format #,###.00,, will display 12.35
Custom format #,###.00,,, will display 0.01
this format is very good but my problem is not resolved i am entering cell s value is 1,10,000 this value covet to 1.10 Plz help and send me the anwer to my id
thanks robert for your help 😉
Hey Chandoo,
I've tried relentlessly to figure this one out, but I've given up at this point. I have a sheet full of millions of entries that I input onto a worksheet, which are then pulled into a more intricate report using vlookup. Within one of the columns is a UPC code that i'm trying to transfer, and it should contain 12 digits (000000000000 custom number format). I have changed the columns format to suit that, so that some of the columns that had 11 digits will now have 12. However, even though I have changed the format, the 11 digit numbers remain unchanged until i open the formula cell and click into another cell. I obviously can not do this to the 100's of entries every week....do you have any suggestions?! thanks!
@Robert: Very cool tip, I didnt know we can do that...
@JohnM: Welcome to PHD. I have tried to replicate your problem in excel by entering dummy values in 10000 odd rows. The values had a mix of 11 digits and 12 digits among them. But when format them using the custom format code 000000000000, they are instantly changed. So I assume it should work the same way for you. May be because there are a million values, excel is choking under the pressure and not running the formats until someone navigates to the cell. But I would imagine the formats working perfectly if you ever need to print them.
hi chandoo/john,
i believe this is happening because the data is imported from other sources?
if so, Excel doesn't treat the "numbers" as numbers. You will need to convert them into proper numbers in Excel.
If AutoCorrect is on, select the range and hover aroudn the GREEN corner arrow. Select the "Covert to Number".
[...] to the data label. It is one of my favorite tricks in excel and I have written about it in introduction to custom cell formatting in excel, number formatting in excel using custom codes, showing decimal values only when needed [...]
[...] when you do this, you need to enable wrap-text feature for that cell from cell formatting dailog (ctrl+1) to ensure proper [...]
[...] lines in a cell | Learn Excel | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on Custom Cell Formatting in Excel - Few Tips & TricksHow to Get Colors in Excel Chart Data Lables - Formatting Trick | Charts & Graphs | Pointy [...]
[...] use the custom cell formatting (more here) code [...]
If I wanted all cells within column A to only have 7 digit numbers how can this be done? If a user wanted to enter 12, that 12 would be converted to: 0000012. I basically only want values: 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9 and locations 1, 2, 3, 4, 5, 6, and 7 of the 7 digit string. I've been fooling with some time with no lock. I first converted the entire column A to text and gave it a format of 0000000 so that it got preceding zeros when values that are less than 7 digits are given. Then I started fooling with the Data Validaion putting: =LEN($A$1)=7
but it doesn't seem to work quite right.
Hello !! I am facing a problem in displaying value in this format "1/1" (Without inverted commas).
Currently I have information in this form 1 / 1. When I remove spaces from the cell, the value is automatically converted to Date Format as 1-Jan. Can anyone of you help me getting the required format after removing spaces.
Many Thanks in advance !
Making the problem more clear !
Current Value of the cell: 1 / 1
After removal of space from the cell
New Value of the cell: 1-Jan
Required Format: 1/1 (without space)
An urgent response will be highly appreciated !! Thanks !!
@Messam there's a method that comes from Lotus 1-2-3. When you're typing the value, start with a ' (apostrophe). So, in your example, typing '1/1 should work properly.
Hope that helps.
Many Thanks !!
I got a solution myself as well.
The same can be done using a function in excel called "SUBSTITUTE".
Aires: I could not understand the solution you provided. Can you please explain?!?!
@Messam.. you can also force excel to consider a cell's contents as text by setting the formatting type to "text" from "general" this will leave 1/1 as 1/1 instead of making it jan-1
[...] and “n”, user should type “1″ and “0″. Then we can use custom number formatting to conditionally display the tick mark [...]
re: John M
I am having the exact same problem as you! Did anyone ever find a solution??
I had tried everything I could think of for the problem John described that I was also having.
I had this issue with phone numbers and zip codes.
I decided to use text to columns to split up my zip+4 column and accidentally clicked finish on the first step (with delimited selected) and magically my entire column of data updated to display the correct format. I repeated my "mistake" on the phone number column, which was also not updating to the correct format, and it worked there too!
It's a silly way to have to do it, but hey, whatever works...!
I would love to know what is causing this issue though.
@Elisha... welcome to PHD and thanks for your comments.
Did you try using the TEXT formula. It forces the formats by changing the values to text. For eg. =text(a1,"00000-0000") would force the value A1 to appear as zip+4 format.
I have a spreadsheet of X's and I want to add the number of Xs in each column. I'd like to custom fomat X so that it reads as 1 and then I can add all the 1s. How would I do that?
Hi chandoo
i want to convert to figures to text . how can do it
it means 5000/- :- Five Thousand Only.
@Jay.. I am not aware of any custom formatting code that would do this. But some UDFs can do it. See here: http://support.microsoft.com/kb/213360 and http://www.ozgrid.com/VBA/ValueToWords.htm
Hi chandoo
Custom Cell Formatting in Excel-
Select column - RAM CHANDRA
Value before- RAM CHANDRA
Costume Format- "MR." @
Value After- MR. RAM CHANDRA
Hi
chandu
I want to no. tricks but in below this format
****12,345.00 this format .
please send me some tricks for this format
Jay, try
"****"#,###.00
[...] custom number format [...]
dear sir.
i have two problems that are :
1) in a work sheet, list of names, automatically added the email domain [eg: snarif @honda.com,munni@honda.com; tazman@honda.com]
2) few numbers are available, i want those numbers in words [5000 will show Five thousand, 100 will show One Hundrate]
hi chandoo
make a telephone directory in excel using visual basic and create the derectory
please give me idia's
Dear Chandoo,
I want to display codes of 5 digits, but sometimes it has 3 decimal places after those 5 digits. I don't want to show the decimal unless it is followed by the 3 decimal places. If I use ?????.??? or #####.### it always shows the decimal after the 5 digit numbers without any decimals following them. For example 12345 would show up as 12345., and 12345.678 would show as that. The latter is fine, but the first example needs to drop the decimal point. Any ideas will be appreciated. Thank you.
Dear Chandoo,
I used the following custom format (found in a forum) to display a hyphen instead of zero... But the problem is that after changing to this, I can not align the content to center or left.
_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_). Could you please check and advise. Thanks
@Ensav
try
_(* #,##0_);_(* (#,##0);_( -_);_(@_)
No need for the quotes around the - unless you want them
I tried. It works only with the cells of 'zero' values and not with the remaining cells having other values. Normally the format applies to all sheets / area. Any other way.... Thanks
Hi Chandu,
I need to format the values as follows and have no idea how to do it.
00:17:42 --> 00d 17h 42m
Please help.
@Fortunate
Use the Custon Number Format
Ctrl 1, Custom
d"d" [h]"h" mm"m"
Make sure all the "'s are ", retype if necessary
Hi Hui...,
Thanks for your quick reply..
It is not working 🙁 It is giving the same output 00:17:42
I think we need something to remove the colons. Please help!
Someone asked me about this today...
Enter 1-2 and have it convert to 1'-02". 1-10 goes to 1'-10", etc. Actually, it might be more important to have 1-6 convert to 1.5, 1-2 convert to 2.1666667, etc because what it really needs to do is multiply/add correctly. So maybe the solution is not so much in the formatting, as much as it is in the formula? I guess the user could use TEXT for input so it would stay as 1-10, then have the formula parse the "-", and convert it to 1 + xx/12? Or any better ideas?
send me excel notes
Thanks for this great tip, was really helpful
Hi Chandoo,
I have always been a frequent visitor and reader of your posts. Inspired by the same, I have done a project performance dashboard for my organization.
Currently am revisiting the dashboard to be more dynamic. In the process I am facing a slight problem. I am trying extract data from the database to a single cell using vlookup. Now the data is of different types like dates, percentages, numbers and text. But I am unable to make the cell receiving these data to have multiple formats. Can you please suggest on how to make a cell recieve different data types.
This will be of real great help.
Thank you in advance
Raj Kiran
Hi chandu,
this is my first visit to your site. Can you tell me that if I have the data with comma as seperators and decimal 2 places , how to convert it to general format.
Hello all and please help!
One unit is six minutes which I need to display as 00:06. Ten units is one hour which needs to be displayed as 01:00 and so on.
Then there is an hourly rate, say £60 per hour. The final column needs to show the total amount.
How do I show the time in minutes but multiply by the number of units??
TIA
Lisa
@Lisa
If cell F1 has your units
In G1 put =F1*(6/(24*60))
Apply Custom Format as hh:mm
in H1 =F1*60
Apply £ format to H1
Is there a way to have a cell value always show a "+" if it is a positive number, and an "-" if the value is negative?
Example: 0.6% would show as +0.6%.
-0.8% would show as is.
Brian,
Ctrl+1
Choose Custom
Type +0:-0
That will do the trick
Hi..
I was trying to format numbers in the Indian format of Lakhs & crores..
I went to custom and typed the following 00,00,000
But excel keeps showing the default of millions & billions 000,000,000
Pls help
Dear Naveen (#65),
you can try this shortcut Ctrl+Shift+~
@Sam
Have a read of http://chandoo.org/wp/2010/07/26/indian-currency-format-excel/
On the -12 and 12 example, where did the Rs. came from?
Hi,
I am trying to custom format a particular cell in excel to display the figure in lakhs
Original number is 11111111. This should convert to 111 lakhs and the cell value should be shown as 111
Can you please let me know if it is possible in custom format. I tried
0"."0,, which returns in crores (1.1) and 0"."0, which returns 1111
@Roopa
It is only possible to divide numbers by 10^3, 10^6, 10^9 etc
This is done by adding a comma to the custom format code
so
123456 displayed as "#,.000" will display 123.456
123456789 displayed as "#,,.000" will display 123.457
.
The only other small modification you could use is to use a helper cell with
=TEXT(10*B4,"0,,")
so that 11111111
will display as 111, except that internally it will hold 111111110
Hi Hui,
Thanks for your quick reply although I do have to admit that the solution cannot be adopted since this particular cell is only a format template cell and does not accept any links or formulas. Also the internal value of the cell cannot change since the same base has to be used for all the conversions( thousands, millions, crores etc)
I needed some expert confirmation before calling quits 🙂
Thanks a lot
Roopa
Roopa
I am not familiar with Lakhs
But have a read of the following post as it may have some useful info on how to use your Local/International settings which are part of Windows to assist you with the problem.
http://chandoo.org/wp/2010/07/26/indian-currency-format-excel/
@Roopa
Thanx to Kyle below, You can use a Custom Format of
#,,," Lakhs" %%To display what you want
Use the above Custom Format and insert a Ctrl J before the first %
Then set your cell to word wrap and top/centre alignment
Hello,
I'm wondering if there is a way to have Excel display a value 1000 times greater than its actual value using custom number formats.
For example, if I give a cell the value 0.00943 how can I get it to display as 9.43 mA, but still have the value 0.00943 V so I can perform other operations on it. I realize that I can simply multiply the cell value by 1000, but then if I want to use that value in another calculation I need to divide by 1000 to get it back to its original value
Hi A.Mac,
Select the cells you want to format and enter this in the custom format:
.# "mA"%%
before you press enter, put the cursor between the " and the % and press ctrl+j which will insert a carriage return.
And then on the Alignment tab of the Format Cells dialog, select word wrap. .000943 becomes 94.3 mA.
The rows have to be normal height, if they are larger you will see the two %% symbols. Change the number of # after the decimal if you want to increase/decrease the number of digits after the decimal
Kyle
sorry just realized that mine was 10,000 and gave 94.3 and you wanted 9.43, so same deal except
.##, "mA"%%% - this is multiplying by 1,000,000 with the three % and also dividing by 1,000 with the comma
plus the ctrl+j between " and % and word wrap will give 9.43
Neat trick Kyle
But to get from 0.00943 to 9.43 mA you need the following format
0.#, "mA"%%%The , divides it my 1000 and the 3 %'s multiply by 100 each
Also the cell needs to be Top Aligned as well as have word wrap
Thanks Hui. I noticed that, as you can probably see now. Are you sure about the top alignment? I just tried it with all vertical and horizontal alignment combinations and the %% symbols stayed out of sight. Row height is the 15 (20 pixels).
Thanks for your help gentlemen.
It's too bad there wasn't a more elegant solution, but that did the trick.
How to use my own text wording in a cell from the results from another cell in excel 2010. Example When H17 is a negative dollar value I would like the word in E17 to read Disqualified in the colour red. When H17 is a positive dollar value I would like to have the word in E17 to read QUALIFIED in the colour blue.
Thank you
[...] thanks to a tip I picked up from Kyle who responded to a post here at Chandoo.org they are all [...]
Hi Chandoo,
I have a scenraio here, where the cell needs to hold leading zero's for example Cell A1 will have the 0123456, cells A2 can have the data as 0012345, Cell A3 can have the data as 1234567.
I am unable to fix this issue with custome format. Can this issue be fixed with custom format.
Thank You.
Hi Veena,
In Custom format type zero seven times i.e. 0000000
It worked for me
[...] in the new box, and towards the bottom are some more time formats to choose from. There are some tips here on how to use the custom [...]
Hi,
I too am stuck with a peculiar issue.
I have a sheet with seconds (from 0 to 10,000) against each person stating how much they used the phone on each day, I have converted the seconds to time by using <>, where c2 is the cell (column) for seconds. I wanted to display this as <>. So I tried using the custom formatting of the cell by putting <>. Worked fine till the usage goes over 30 days. If the person uses the phone for (lets say) 35 days, 2 hours and 31 minutes, it displays <>. Is there ant setting to get the absolute number of days stated?
Thanks.
Chandoo,
I am trying to custom format a cell in Microsoft Excel. I want to set up a custom format that will add a specified % to any number I input into a cell. What type of format/formula must be included for this to work?............
Thanks
@Scott
any code just add an % to the end
eg:
#.00%
0.0%
etc
I did not explain myself well. Sorry.
I want to add 2% to any number I input into the cell by using a custom format.
For example in the cell: when I input 3, I want the cell to multiply it by 2%, giving me the output of 3.06.
I am not sure this is even possible.
Thank you.
Scott
@Scott
As you suspected, Excel can't do that with Custom Formatting
You can do it to a cell using VBA if required.
Thank you for your help!! 🙂
Can you format cells to always have a negative number no matter what you type in. I need like 15 cells in one column and 35 cells in another column?
Thank you!
Debbie
@Debbie
Just put a negative sign in front of your favorite Custom Format style
eg:
-#,##0;-#,##0
can someone tell me how to write number 1234567 as 1,234,567.00
Thanks
can someone tell me how to write number 1234567 as 1,234,567.00
in Excel 2010
Thanks
Hi,
Can anyone help me with the below issue,
I have a report in excel in which all the amounts are in custom format. I wanted to paste the same in Text format. (For e.g. amount in custom format is shown as 100 Dr however when we formula bar we could see only 100; I want 100 Dr to be shownn on formula bar) Could you help me on this.
Thanks,
Prasad
@Prasad
Custom Formats only change the displayed format of the cells contents in the Cell itself
In your case the cell contains the number 100
but the cell displays it as 100 Dr
The Formula Bar always displays the cells contents wether it is Text, a Value or a Formula
Need some help I have a group of numbers that have a letter in it and I am trying to get the format to match but coming up with nothing.
Example: 24A102
This is what I am wanting to get: 24-A1-02
@Kevin
Using a helper cell:
=LEFT(A2,2)&"-"&MID(A2,3,2)&"-"&RIGHT(A2,2)[...] http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/ [...]
hello can any one tell me how to use formula for this particular format in custom
Format - first 5 character, then 4 digit, then one character
eg. AAAA12345A
if user doeas not follow above formate error should display.
I just want how to put formula
Hi Chandoo,
I am trying to format the following number in Excel -10 can you tell me how can i do it. Example is below, when i import the data it comes with 1,25M and so on, how can i convert them with 1,250,000 etc.
1,25M = 1,250,000
5,00M = 5,000,000
@HP
Try:
=LEFT(SUBSTITUTE(A1,",",".",1),LEN(A1)-1)*1000000Then use a Custom number format as #,###,###
dear sir
how to split date month ,year time in one word example :20120621121201
Actual :2012/06/21 12:12:01
You can use this formula to convert that number to a real date/time value...
=1*TEXT(A2,"0000-00-00 00\:00\:00")
and then format the cell to display it the way you want. I am guessing you would want this Custom Format type pattern...
yyyy/mm/dd hh:mm:ss
Dear sir
yyyy/mm/dd hh:mm:ss how to apply in excel sheet
@Ravi
Select the cells
Ctrl 1 or Right Click, Format Cells
On the Number Tab, select Custom
In the Type: Box type:
yyyy/mm/dd hh:mm:ssOk
Dear sir
Thank you very much for our instant support we happy with solution.
happy week end
Regards
Ravi
Hey, i have been having a problem formatting a cell. Since everyone here seems like experts compared to me, i hope you can help me solve this.
In a cell the data is input as "abc00120120101001" where I need it to be formatted into "abc001-20120101-001" and then in a different cell I need "abc001".
Any suggestions would be wonderful.
[...] http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/ [...]
Chandoo,
Kindly assist how to format/customize the Arrow, the arrow pointing up is color Red while the arrow pointing down is Color Green.
Tnx alot in advance
mxm
Hi!
Is there a simple way to customise a cell with units that I have specified in another cell (or more)?
e.g.
I have two cells:
A1 - where I specify a unit.
A2 - where I enter a value.
I would like cell A2 to have as units the one specified in A1.
So if A1 contains "m" and I enter "25" in A2,
A2 should display "25 m".
(I do not want to use an 'if' formulation here because I would like to allow for A1 to take any possible value ... It could be 'Apples', in which case A2 should display "25 Apples" ... etc).
Thanks,
LS.
And if possible, I would like to avoid having to define a name for A1 values.
Thanks,
LS.
Hi LS.. are you still looking for help on this.
Since you just want 25 m or 25 apples for display (but in background the value remains 25, why not readjust the columns so that you enter value first, then pick the unit in next cell. Then you can remove grid lines, adjust alignment so that it looks natural.
I need to format a cell that would take health card number, sometimes there is a alpha verison code (not always).
Example> 1234 567 890
Example> 1234 567 890 nm or 1234 567 890 ab
I have tried several different ways to custom format a cell but I was unsuccessful.
The formatting codes work very well for numbers, not so much for text values like above. So I suggest this:
Select the cell(s) and format them as TEXT.
Now you can enter whatever you want.
When displaying you can easily add a space after 4th and 7th characters like this:
=LEFT(A1,4)&" "&MID(A1,5,3)&" "&MID(A1,8,3)
& IF(LEN(A1)>10," "&RIGHT(A1,2),"")
Hello, I received a file with prices in txt format and after opening with Excel 2010 some cells have 'general' format and show prices e.g. 114.00 but others have 'custom' format and when I click on them, they actually show date in the formula bar, e.g. when I click on price 2.23 it shows 1.2.2023 in the formula bar....I need 2.23 to be the contents of that cell and in a number format, please help!
Hi ,
thanks a lot for your excel formating content. i have a phone field some time it will have 10 digit value (e.g.7877988334) some time it may have phone field with the country code ( e.g. (+91) 7877988334 ). how to create the format to support both of them.
waiting for your reply,
- Solomon
[...] [...]
Hi,
I want to know that how can i force a user to enter this particular number in the sheet. 000-000-000 is the format and number will vary. In addition if there are any alphabets in the entry a typo maybe then they should highlight with some color. Please let me know it its possible.
Thanks in advance.
Sunny
@Sunny: Assuming that the value is in cell A1.
Go to data - data validation - data validation - allow (custom) and formula should be:
AND(CODE(MID(A1,{1,2,3,4,5,6,7,8,9},1))>=48,CODE(MID(A1,{1,2,3,4,5,6,7,8,9},1))<=57,LEN(A1)=9)
the above formula should be without an "=" sign at the start.
Now under format cells, keep the formatting as 000-000-000. Doing the above will restrict the user from typing anything other than numbers and that too of 9 digits. Now simply copy+paste cell A1 to all the cells wherein this criteria is to be followed. You may delete the data in the cells but the data validation will remain.
Hope this solves it. Let me know.
Regards,
Suril
hey
I've a prblm,
tarunkumar
anilsingh
here are different range names, I want to separate them,
i couldn't find the way to separate them(text to column is not working in this because it doesn't have any space,commas,etc.)guys do u have some for this....
@Tarun, if I understand your question right, you want to tarunkumar to appear as tarun kumar. If so, then there cant be any solution to this! If I got the question wrong then do let me know..
PO WITH RUNNING NO.
EG 12345- 01( CELL A1 ) . I WANT TO CHANGE B1 TO 12345-02.
ANY FORMULA WITHOUT USING DRAG FILL.
MANY THANKS
How to set Default character against input character
eg:
I input Vijay but cell shows Mr. Vijay
How to prefix against text
@vijay you can use "Mr. "@ format under the format cells option.
Hi everyone. Please help with this. I have been tried to find this entirely on Net. Bt i did not fine that.
Actually i have a spreadsheet attched with other sheet.. and i enter value in 1st sheet and its bring the result in 2nd sheet. bt something results are negative number. in cell let say in Cell a1 has some formula and some time result will be change in negative numbers. Once the results automatic change. i want pop-up message instantly. please help me wtih that. Mr. chandoo. please..
Thank you
@YOGESHWAR
Lets say value you enter is in A1 (Sheet1) and result in A1 (Sheet2). Goto A1 (Sheet1), data - data validation - settings - allow custom and formula should be Sheet2!(A1)<0; goto error message, style - warning and write your warning. Done!
Suril. Thank you very much to reply me back.
actually i have formula in Cell A1 has formula and after entering anything A1 result automatic goes up and down and sometime its in Negative numbers. So once Forumla cells goes in Negative i want to have pop-up error message. instantly.please help me with that.
Thanks again
@ Suril. Dear. i tried with that.. its not working.. its now alowing me to enter any value.on there. please help me with that..
can u let me know what error is coming? If you have changed the style to 'warning' then only a warming message should be displayed. If you still are unable to enter any value then you may mail me the sheet on suril88@gmail.com.
Dear Suril. Please accept my invitation that. i just send you on Gtalk.
Thank you very much for you kind help .
Is there anyway to force a cell to take a certain format and give an error otherwise? I know it can be done in Access, but I can't figure it out for excell. I have an ID that should be in the format 'A1234.' Any ideas?
Assuming that the value is in cell A1, you can use the below formula:
=IFERROR(AND(LEN(A1)=5,CODE(MID(A1,{2,3,4,5},2))=0)),"")
You can use the above formula for different formats by modifying it appropriately. To ensure adherance to the format, "validation" should be used.
This is truly an awesome site. I have used your thought process(es) to come up with many Excel solutions. I'm stumped on this one, even though it seems simple enough.
I have many rows and columns of data, but for this example, I'll talk about the first three rows and first two columns. In Column A I have numbers, and in Column B there may or may not be a letter. I am trying to use Custom Format to put the letter (if any) from Column B into Column A (after the number), but have had no success. I realize that since I'm referencing the cell next to it, I'll probably need to use offset, but perhaps VBA is the only answer. I also have data in Column D/E, F/G, etc. with each series of two columns having a number and then the following column that may or may not have a letter for the formatting.
The reason for this is that I want to still be able to add/calculate Column A for charts and graphs, so if I just concatenate it, I lose that ability.
I've tried code that includes (where fullRng is Col B):
For Each curCell In fullRng.Cells
If curCell.Value = "" Then
cfStr = "#%"
Else
cfStr = "#%""/" & curCell.Value & """"
End If
curCell.Offset(0, -1).NumberFormat = cfStr
Next curCell
I'm stuck. 🙁
@Presto
It looks like your trying to setup Custom number formats
But there is no need to include the Cell's value in a Number Format
Can you tell us what your trying to achieve ?
Thank you so much for responding. Trying to get the letter in the cell to the right (Col B) to show up at the end of the number in Col A without losing the ability to calculate Col A. So if I have 20% in Col A and 'C' in Col B, I want Col A to show 20%C but still be able to calc and chart the 20%. Bearing in mind I have hundreds of rows and about 20 columns of data with odd colums (A,C,E,etc) housing percentage and even columns (B,D,F,etc) either blank or housing a letter that I want to show in the same cell as the number. I currently do it manually, cell by cell, but it takes forever, and i keep getting this request from other departments.
@Presto
Can you post a sample file with your data and example of what the outcome should be?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Of course. Thank you.
https://docs.google.com/file/d/0B0o-dyb6UCKkRXh4d0RpTG1JdlU/edit?usp=sharing
Notice that even though the letter appears in the cell when I manually set custom formatting, I can still do calculations on the cell.
My thought would be that in the actual document, the cells with the letters on them would be hidden, and the cells with the % would just use the hidden cells as part of their custom format. I've done this before with conditional formatting (colors, etc.), but it has a place to reference other cells. I'm struggling with how to reference a cells contents in Custom Number Format if it is possible. I'll use VBA if needed...either way, if I can get it to work, it'll save me from having to do it manually for every letter.
@Presto
Try this code:
You may need to retype the " marks as WordPress seems to scramble them
Dim fullRng As Range
Dim curCell As Range
Set fullRng = Range("B2:G4")
For Each curCell In fullRng
If curCell.Value = "" Then
cfStr = "#%"
ElseIf curCell.Value >= 0 And curCell.Value <= 1 Then cfStr = "#%" & curCell.Offset(0, 1) Else cfStr = "General" End If curCell.NumberFormat = cfStr Next
Thank you very much! Closer to the goal. I'm getting an error that says I'm unable to set the NumberFormat property of the range class.
After some testing, it rolls through the sequence, but when it hits an empty cell it throws the error. I'm investigating how to have it ignore that...probably just more if...then. If I get final code that works through the error, I will post it, or if you have an idea (and time to share it), I'm open.
Either way, you've given me some great code and a direction...thank you!
Aha!
Getting rid of the "\" seemed to do the trick. I updated the code to check the cell's format rather than content, but kudos to you in putting me on the trail of the solution!!
Code that worked...
'Dim fullRng As Range
'Dim curCell As Range
'Dim acf As String
'Set fullRng = Range("B2:G4")
'For Each curCell In fullRng
'acf = curCell.NumberFormat
'If acf = "0%" Then
' If curCell.Offset(0, 1).Value = "" Then
' cfStr = "#%"
' Else
' cfStr = "#%" & Chr(34) & curCell.Offset(0, 1) & Chr(34)
' End If
'Else
'cfStr = "General"
'End If
'If curCell.Value = "" Then
' cfStr = "General"
'End If
'curCell.NumberFormat = cfStr
'Next
Once you have entered a Custom choice in the Formatting, is there a way to save it as a permanent choice for any Excel spreads I do in the future?
http://office.microsoft.com/en-us/excel-help/create-a-custom-number-format-HP010342372.aspx
Excerpt:
Note A custom number format is stored in the workbook in which it was created and will not be available in any other workbooks. To use a custom format in a new workbook, you can save the current workbook as an Excel template that you can use as the basis for the new workbook.
It is rare to find somone who really knows their stuff. Good job.
my problem is that ,i have to keep cell as text but the format shoud be DD/MM/YYYY. Means i need custom format for the cell.
please help.
Regards
Alok
@Alok
Dates are always numbers, but they can be displayed as Text
You can use a Custom Number format of dd/mm/yyyy
What do you want to do with it as text ?
Hi Chandoo,
I want to remove below character. I have around 50k rows data which belong some of one character is there. I want remove those.
> ? / {} [] \ | # @ $ % ^ & * ( ) _- = + and so on.
So I used
=TRIM(A2)
=TRIM(SUBSTITUTE(B2,CHAR(160),CHAR(32)))
but after using these function also all characters are not removing.
Please suggest. (? & *) these are the critical character to remove.
can a number 1234567 or12345678 always be displayed as either 12-34567 or 12-345678 so that there are always 2 digits to the left of the "-" and the remaining digits to the right of the "-" regardless of how long the number is by using custom formatting only without the use of a helper cell?
Thanks anyway, I got it.
When using excel and performing calculations, when the calculation results in -, 0, #DIV0 is there a way to format the cell to not show that? I want it to show nothing.
@Don Jones
Put the formula in an
=Iferror(Existing formula, Value if error)
function
can you help me for this?
i have value (text) in cell :
190050300000
i want that value be :
1.90.05.03.0.0.000
what i must be do?
If it is text, I don't think there is much you can do
If it is a Number you can use a Custom Number format of
#"."##"."##"."##"."#"."#"."###
I'm curious if it is possible for a single cell to show a dollar amount (ie. $5,000) or a percentage amount (ie. 5%). I'm using "if" formulas in this cell as well. Thanks in advance for your help!
@Robin
You can use a Custom Number Format
Like [<1.1]0.00%;$ #,###.00
Which will work for numbers under 110%
Adjust to suit
I would like to format a column to not put hyphens or dashes in. For example, if I want to type say a phone number 123-456-7890, I want the result to be 1234567890 or a social security number 123-45-6789 to become 123456789. I know how to do Find & replace, or write formula to remove/substitute. What I'm looking for is a way to format that column so that when user enter a number with dash or hyphen, the end result is still in the same column but without the dash or hyphen.
@Que
Try a custom number format
Select the cells
Ctrl 1
Number, Custom
In the Type use: 0","000","000
Apply
You may have to re-type the " manually as the Blog software sometimes stuffs them up
Dear Chandoo,
I am creating an excel invoicing worksheet. The total amount cell i want format in Lakh/crore format (ex.1,23,45.678.90) Also i want that cell should be blank if calculated value is 0. right now i am having 0.00 value in that cell. I have applied the following string in custom format:-
[>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00
Kindly help me which string i need to add after that so i can have blank cell in case of zero value.
Regards,
aknsms
Dear Chandoo,
I have a cell formatted as DATE and whenever i type 31/12/2012 it becomes 12/31/2012. I have selected English (UK) format and changed it to required format.
But
What i want is if i input 31-12, it should aouto complete to 31/12/13 or 31/12/2013
Can it be possible?
Regards
I would like to format .256482454864 to show 256482454. Basically the number converts to nanomolar.
Thanks.
@Donna
Have a read of:
http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10/
Use a Custom number format of
0^%%%%%%
where ^=Ctrl J
Make sure to put Word Wrap on in the cell where the number is
Remember that this is how the number is displayed, it is still 0.256482454864 internally
Hey guys ,
i need to convert the following :
Example: 4P9874 to 4P-9874
Thank your in advance
hi, i want to format the number with two digits and 100 separator only once
e.g.
1) 1234,56.00
2) 123,45.00
3) 12,34.00
4) 1,23.00
5) 12.00
I am using following custom format "##","##.00;(##","##.00);-"
The problem i m facing is in case of 5th example above where I get displayed number as ",12.00". i.e. comma is displayed before number which i dont want. Please help. Its urgent.
[…] Custom Cell Formatting in Excel – Tips, more tips, even more […]
Hi , I have a positive numbers which are formatted as ""0.00" Cr".
So i want excel to convert all the numbers with this formatting to Negative numbers so when i add a Debit and credit number it should give me a Net Value. Please help
Regards,
Chintan Oza
@Chintan
Try a format like:
0.00 "Cr";-0.00 "Dr"
@ Hui,
I am using some thing like this ""-0.00" Cr",
and numbers in the cell is correctly displayed as -3200.00 Cr
But the base value still remains same that is when i double click on that cell it still shows positive 3200 and hence this number stays positive.
I want base number which was inputted to be converted in negative number.
hope i am making some sense.
@Chintan
Correct, Excel still reads the underlying value of the cell
Do you have Cr or Dr in a seperate column ?
If so you can use that to assist
Can you please past some sample data or email me directly
Thank you MR Hui for your help, basically this data is exported from Tally and i have taken help from tally architech and i get this numbers in Negatives and positives..
Also i experimented with a below VBA code and its solving my problem-
'Convert positive Cr numbers to negative
Sub cr()
Dim cell As Range
For Each cell In Selection
If ActiveSheet.cell.NumberFormat = ""0.00" Cr" Then
cell.Value = "0" - cell
End If
Next
End Sub
Hi,
I am trying to achieve this
100000 as 1.0
1000000 as 10.0
10000000 as 100.0
100000000 as 1,000.0
and so forth..
So far have created a custom format 0"."00, that make the last value looks as 1000.00
Still to add comma and remove the last decimal
Any help..
Plz help me with a require custom format......
@VSM
Try using: #,,Ctrl+J%%
Select the cells
Ctrl 1
Number, Custom
in the Type box use #,,,Ctrl+J%%
Apply
Now with the cells selected
Ctrl 1
Alignment
Tick Wrap text
Adjust the column Width to suit
Note: Remember that the cell still has the base value for use in other calculations,it is simp0ly displayed as being divided by 100,000
You may want to refer to this page:
http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10/
Thanks HUi...
It was helpful, Thanks 🙂
I have created a custom format 0.0,,, ctrl+J%% which is giving me this :
Type display
10000 0.1
100000 1.0
1000000 10.0
10000000 100.0
100000000 1000.0
However i want to insert Indian style comma seprater also... like if i type 100000000 should dislay as 1,000.0
By Indian style comma seprater i meant first comma after three decimal then after every two decimal from left like
1,000
10,000
1,00,000
10,00,000
1,00,00,000 so forth
Plese advice..
I have also tried #,#.0,,,ctrl+J%% but it is not giving the desire result
Thanks
sir
i want input pan no in excel so i like a formula jo first 5 text & mid 4 number and last text. plz elp me tell costum formula
Hi sir
I wan to ask if I want maintain first letter and the rest of letter should be like 'xxxxx'
For example
Michael Jackson and I would like to create like
Mxxxxxx Jxxxxxx
How is it?
Thank you
Regard
Jacky
@Jacky
Unfortunatly there is no way to do that with a Custom Number Format
You can use a Formula win another cell like:
=LEFT(A1,1)&REPT("x",FIND(" ",A1)-2)&" "&MID(A1,FIND(" ",A1)+1,1)&REPT("x",LEN(A1)-FIND(" ",A1)-1)
If you copy/paste this re-type all the " characters as they are not what they look like
or use some VBA to change the cell it as soon as you enter in it
If you describe the usage It will be a big help
Once I have custom changed the cell to hide the text (with ;;;) how can I get the Excel filter to recognize it? OR how would I get the filter to list all the rows associated with a merged cell?
Can you help by send reply to my email?
How can I reset MS Excel
[…] http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/ […]
Dear Sir,
I want Hide Formulas and display values in formula bar
Awesome post.
Hi, I have been a regular reader of your blog. You are so simple & genuine and that’s what makes me connect with your writing. I had problems with Excel. But now no.
I am preparing one data file. I have employees PAN details with me but in XX format and at other side i have entire employee master as well. Dont understand how to vlookup and get the correct PAN details.
Please help
Hi All,
I am looking for a custom format in which my data should show in Number format or Percentage format depending upon the choice through slicer.
I mean to say can someone help me with the custom format where excel cell will change number to Percentage depending upon the value.
Thanks guys.
when I try to enter a number on an excel cell say 12345, it comes as 123.45
Looks like you have the automatic decimal point setting on. Go to File > Options > Advanced > Editing options and uncheck decimal points option (should be the second one...)