Formatting Zones - 1063 - Learn Excel from MrExcel Podcast Video

Formatting Zones - 1063 - Learn Excel from MrExcel Podcast

Hey, welcome back to the Mr. Excel netcast. I’m Bill Jelen. Face it you, you start out with a massive amounts of data. Say, how are you going to analyze this, well, let’s fire up activity table and see if we can solve this problem. Hey, welcome back to Mr. Excel netcast. I’m Bill Jelen. Well today’s tip is sent in by Crystal. Now Crystal is a Microsoft MBP in Access, and Crystal is running some videos at Youtube. Just search for learn access by Crystal, all one word, that’s her user ID, and you’ll be able to learn Access. You go. Crystal said, hey, why don’t you talk about the different formatting codes in Excel? Let’s just take a quick look. I’m going to choose this. Press control one. And, you know, we’ll set up some sort of a format code, maybe currency with two decimal places. Click OK. And what happens there, if I go back in and click on custom. You’ll see that we’ve created a custom number format with a single zone. It’s possible to have custom number formats that have two zones. So I’m going to choose these, control one, and we’ll go into custom. And I’m going to put two zones in. Each zone is separated by a semi colon. So let’s put number, comma, and number, number zero and then a semi colon, and second thing. This will be for negative numbers, number, comma, number, number zero. Now the way that this works is if you only have two zones, the first zone is use for positive and zero numbers. The second zone is used for negative numbers. So we click OK, and you see that our negative numbers now have a minus sign in front of them. But we can change that, if you wanted your minus to appear afterwards, we can put the minus sign after the fact. Click OK. And now you’ll see we get the minus sign as a trailing minus sign. You can also do any kind of thing that you want. For example, if you want to put CR to indicate that it’s a credit, that has to be in quotes. Click OK. And it puts CR, instead of a minus sign, very clever the way that that works. Now, it’s possible to have three zones. Three zones, how does that work? With three zones, the first zone is for positive numbers. So let’s just put the zero there. The second zone, of course, is for negative numbers, negative zero. And then the third zone is for zeros. Now maybe for zeros, we don’t want to have anything up here. I don’t know why. So let’s just in quotes here, put dash, dash and click OK. And now you see that the zeros appear as a dash, dash. And you really have a lot of flexibility, you can put, you know, the word zero or, you know, whatever you need to. No sales or you know. Why didn’t you show up today? You know, whatever. You have a lot of control there. Now there is a fourth zone, and the fourth zone controls text. So, here let’s, let’s build this, and we’ll say four positive numbers, I want a zero. For negative numbers we want them in parenthesis. For zero, we want a zero and then for the fourth zone. If someone would enter some text, we could put invalid entry. Click OK. And you see that our positive numbers come out okay, the negative numbers are in parenthesis, and anytime that any words are there, we get invalid entry. Now if I enter a number, it shows up. If I enter some text, it shows up as invalid entry. Now the cells still contains that text that we entered, but it’s displayed in and printed using the custom number format. Now some cool things we can do here, if you wanted to prevent text from showing up. You just leave that zone blank, so in other words, just nothing after the semi colon. And the text does not appear. You can also use this trick as a fast way to hide things. Just put one, two, three semi colons with nothing and you’ll get nothing for positive, negative, zero, and text. Now one more cool thing we can do here, let’s say, we want to control some colors, and of course we can do this. We can traditional formatting, but let’s just talk about another way to go. In square brackets, the word green and zero that will make all of our positive number show up as green and then in square brackets red and zero. And then maybe zeros themselves should be neutral with a blue. And zero, and then just everything else as text we’ll let that appear. Click OK. And there you have it. The positive numbers in green, zero in blue, and the negative numbers in red. Notice without any sort of minus sign, because I forgot to put that in. Now you only have 8 colors here, I think it’s white, black, magenta, blue, green, cyan, few others you can check Excel help on that. But, interesting, most people, they just see the one custom number format that you get when you choose any of the build that items. But, it’s possible to go through and use two, three, or four zones. Check it out, next time you need to have some control over the way you display your numbers. I want to thank Crystal for that suggestion. We’ll see you next time for another netcast from Mr. Excel.

No Links were listed yet. Go ahead and share!

Hey, welcome back to the Mr. Excel netcast. I’m Bill Jelen. Face it you, you start out with a massive amounts of data. Say, how are you going to analyze this, well, let’s fire up activity table and see if we can solve this problem. Hey, welcome back to Mr. Excel netcast. I’m Bill Jelen. Well today’s tip is sent in by Crystal. Now Crystal is a Microsoft MBP in Access, and Crystal is running some videos at Youtube. Just search for learn access by Crystal, all one word, that’s her user ID, and you’ll be able to learn Access. You go. Crystal said, hey, why don’t you talk about the different formatting codes in Excel? Let’s just take a quick look. I’m going to choose this. Press control one. And, you know, we’ll set up some sort of a format code, maybe currency with two decimal places. Click OK. And what happens there, if I go back in and click on custom. You’ll see that we’ve created a custom number format with a single zone. It’s possible to have custom number formats that have two zones. So I’m going to choose these, control one, and we’ll go into custom. And I’m going to put two zones in. Each zone is separated by a semi colon. So let’s put number, comma, and number, number zero and then a semi colon, and second thing. This will be for negative numbers, number, comma, number, number zero. Now the way that this works is if you only have two zones, the first zone is use for positive and zero numbers. The second zone is used for negative numbers. So we click OK, and you see that our negative numbers now have a minus sign in front of them. But we can change that, if you wanted your minus to appear afterwards, we can put the minus sign after the fact. Click OK. And now you’ll see we get the minus sign as a trailing minus sign. You can also do any kind of thing that you want. For example, if you want to put CR to indicate that it’s a credit, that has to be in quotes. Click OK. And it puts CR, instead of a minus sign, very clever the way that that works. Now, it’s possible to have three zones. Three zones, how does that work? With three zones, the first zone is for positive numbers. So let’s just put the zero there. The second zone, of course, is for negative numbers, negative zero. And then the third zone is for zeros. Now maybe for zeros, we don’t want to have anything up here. I don’t know why. So let’s just in quotes here, put dash, dash and click OK. And now you see that the zeros appear as a dash, dash. And you really have a lot of flexibility, you can put, you know, the word zero or, you know, whatever you need to. No sales or you know. Why didn’t you show up today? You know, whatever. You have a lot of control there. Now there is a fourth zone, and the fourth zone controls text. So, here let’s, let’s build this, and we’ll say four positive numbers, I want a zero. For negative numbers we want them in parenthesis. For zero, we want a zero and then for the fourth zone. If someone would enter some text, we could put invalid entry. Click OK. And you see that our positive numbers come out okay, the negative numbers are in parenthesis, and anytime that any words are there, we get invalid entry. Now if I enter a number, it shows up. If I enter some text, it shows up as invalid entry. Now the cells still contains that text that we entered, but it’s displayed in and printed using the custom number format. Now some cool things we can do here, if you wanted to prevent text from showing up. You just leave that zone blank, so in other words, just nothing after the semi colon. And the text does not appear. You can also use this trick as a fast way to hide things. Just put one, two, three semi colons with nothing and you’ll get nothing for positive, negative, zero, and text. Now one more cool thing we can do here, let’s say, we want to control some colors, and of course we can do this. We can traditional formatting, but let’s just talk about another way to go. In square brackets, the word green and zero that will make all of our positive number show up as green and then in square brackets red and zero. And then maybe zeros themselves should be neutral with a blue. And zero, and then just everything else as text we’ll let that appear. Click OK. And there you have it. The positive numbers in green, zero in blue, and the negative numbers in red. Notice without any sort of minus sign, because I forgot to put that in. Now you only have 8 colors here, I think it’s white, black, magenta, blue, green, cyan, few others you can check Excel help on that. But, interesting, most people, they just see the one custom number format that you get when you choose any of the build that items. But, it’s possible to go through and use two, three, or four zones. Check it out, next time you need to have some control over the way you display your numbers. I want to thank Crystal for that suggestion. We’ll see you next time for another netcast from Mr. Excel.

Transcription by: Scribe4you Transcription Services