This Excel Trick Lets You Sum a Number of Cells with a Variable Range. Note: The information in the article applies to Excel versions 2019, 2016, 2013, 2010, and Excel for Mac. You can embed the INDIRECT function as an argument inside of the SUM function to create a variable range of cell. Format only cells that contain - Select the type of cell that you want to format, then select other rules in the drop-down boxes that appear based on your choice. One practical use of conditional formatting is using it to identify cells containing negative numbers in a budget (or zeroes in a store inventory sheet.
, - 208 comments 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 separatorsnotation 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 US they would be phone number xxx-xxx-xxxx, ssn xxx-xx-xxxx, zipcodexxxxx, 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. 3 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:. 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: 341234 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. Hi Chandoo - Awesome suggestions. Big question for ya! 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! Hi chandoo, hope you can help me with this problem; it's something about number formatting and 'Creating dash boards using excel conditional formatting' @ 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?
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! 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. 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 (00 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?! @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 00, 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.
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. 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. 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.
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? 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 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.
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?
Is there a simple way to customise a cell with units that I have specified in another cell (or more)? 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'. 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. 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. 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. 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. 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!
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. 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 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.