Format numbers in thousands or millions
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
You know, every time that I have a question in Photoshop or InDesign, had to turn it over to a graphic designer who, you know, just laughs at me and says “That was so simple, how could you not know how to do that?” I get to turn the tables today, I got this file from Texas, they're trying to create a chart from this data, and the chart is not coming out well.
And so, as I looked at the data, I could see that these are real numbers here, but then they switched over to the text to show these numbers in thousands, and then back to numbers and millions, and just so on.
OK, so let's talk about how to clean up this data.
First thing I want to do is, I want to change everything into real live numbers, and so, from the start of it, there's a few things that we can just actually grab as numbers.
This one I'll have to type it 705000, 1200000, 1400000, OK now, these I can actually put a little formula here, so I type the equal sign, click on the number, the 1.7*1000000, and that will convert those numbers to millions, that looks good.
Over here these are fine, this one is a formula of that number *1000, copy that one down.
And then here we're going to type that one, 1100000, and then finally this formula will work over here, alright, so that gets us all of our information.
And then, to figure out the net income, we’ll take Revenue - Expense.
So formula there, G4-H4, copy that down, OK.
Now I can already hear them saying “No-no-no-no, we don't want all those zeros, we want it to look cool, we want it in thousands,” and so on.
Alright so, let's just take those numbers, we'll pull them over here again, and I'm going to use some special formatting, you can see that I've already set up this formatting.
If we come here, I'm going to press Ctrl+1, and I'm not going to use any of the building formats, I'm going to come to Custom and type this format of 0,K. What does that do?
The comma after the zero says “Divide the number by a thousand.” the case says “Put a K abbreviation in.” So for all my numbers that are less than a million, that's the format I'm going to use.
Now the other numbers, the numbers that are greater than a million, so that's all of these, and even these numbers here, and these few down here, I want to show those as millions with one decimal point.
So again, Ctrl+1 to get to Format Cells, click on Custom, on the left hand side, and then we're going to type in a format of 0.0,, ! That says “Hey, show this in millions.” And then I want to put an M, M already mean something, it means Months or Minutes, so I've put that “M” in quotes to get it to appear, click OK, and there, we now have our formatting.
Now why go to all this hassle?
Well, the reason we get all this hassle is now it’s easy to create a chart, because those are all real numbers.
So kind of a hassle, I'm sure for all those things that I think are really insane that we have to do in Photoshop, this probably seems just as insane, but certainly a good way to go.
Now, it would have been possible to create a conditional format over here, that would look and see if the number was less than or greater than a million, but for right now, let's go with that.
I want to thank everyone for stopping by, see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
You know, every time that I have a question in Photoshop or InDesign, had to turn it over to a graphic designer who, you know, just laughs at me and says “That was so simple, how could you not know how to do that?” I get to turn the tables today, I got this file from Texas, they're trying to create a chart from this data, and the chart is not coming out well.
And so, as I looked at the data, I could see that these are real numbers here, but then they switched over to the text to show these numbers in thousands, and then back to numbers and millions, and just so on.
OK, so let's talk about how to clean up this data.
First thing I want to do is, I want to change everything into real live numbers, and so, from the start of it, there's a few things that we can just actually grab as numbers.
This one I'll have to type it 705000, 1200000, 1400000, OK now, these I can actually put a little formula here, so I type the equal sign, click on the number, the 1.7*1000000, and that will convert those numbers to millions, that looks good.
Over here these are fine, this one is a formula of that number *1000, copy that one down.
And then here we're going to type that one, 1100000, and then finally this formula will work over here, alright, so that gets us all of our information.
And then, to figure out the net income, we’ll take Revenue - Expense.
So formula there, G4-H4, copy that down, OK.
Now I can already hear them saying “No-no-no-no, we don't want all those zeros, we want it to look cool, we want it in thousands,” and so on.
Alright so, let's just take those numbers, we'll pull them over here again, and I'm going to use some special formatting, you can see that I've already set up this formatting.
If we come here, I'm going to press Ctrl+1, and I'm not going to use any of the building formats, I'm going to come to Custom and type this format of 0,K. What does that do?
The comma after the zero says “Divide the number by a thousand.” the case says “Put a K abbreviation in.” So for all my numbers that are less than a million, that's the format I'm going to use.
Now the other numbers, the numbers that are greater than a million, so that's all of these, and even these numbers here, and these few down here, I want to show those as millions with one decimal point.
So again, Ctrl+1 to get to Format Cells, click on Custom, on the left hand side, and then we're going to type in a format of 0.0,, ! That says “Hey, show this in millions.” And then I want to put an M, M already mean something, it means Months or Minutes, so I've put that “M” in quotes to get it to appear, click OK, and there, we now have our formatting.
Now why go to all this hassle?
Well, the reason we get all this hassle is now it’s easy to create a chart, because those are all real numbers.
So kind of a hassle, I'm sure for all those things that I think are really insane that we have to do in Photoshop, this probably seems just as insane, but certainly a good way to go.
Now, it would have been possible to create a conditional format over here, that would look and see if the number was less than or greater than a million, but for right now, let's go with that.
I want to thank everyone for stopping by, see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
