Formatting as Facade

Learn Excel from MrExcel podcast, episode 2044 - Formatting as Façade!

Alright, I am podcasting all of my tips in this book, click the “i” on the top-right hand corner to get to the playlist!

We're talking about custom number formatting today, so I have Quantity, Revenue, and we are displaying a whole bunch of decimals here. And, you know, if we don't want to play all those decimals, and we don't want to use the ROUND function, I just come up here and using Decrease Decimal. it doesn't change the number that's actually stored behind the scenes, it's just displaying the way that Excel shows us the number. Alright, and so I'm stealing Mike Girvin’s term, that we're using formatting as a façade, right, there's a whole bunch of decimal stored in that cell, but we're using custom number formats to display that. And I want to go beyond the 11 formats here, and even click that dialog launcher, beyond most of the things that are here, and I want to talk about Custom. Custom number formats give us the world, alright?

And so here's a whole bunch of custom number formats that you can type in this box, and the results are shown to the left. So just General, you get all the decimal places, but here, zeroes before the decimal place are forcing zeroes to appear. So if the number < 100, I'm going to get a leading 0, sometimes that's useful for part numbers, zeroes after the decimal places force the numbers to appear. The hashtag or the pound sign (#) before the decimal place is optional, alright, so it can be there, it doesn't have to be there. Alright, so in this case we have the number 6, but because we have five zeroes, it's giving us leading zeroes. Now, if you need to VLOOKUP into a section of text that has 00006, this won't be a match, but if you just needed to look right, it'll work. OK, so some interesting things here, we use pound signs or hashtags a lot, “hashsigns” a lot. In the US we call this a pound sign, and I understand in England that's not a pound sign, £ is your currency that you call it a hash. Alright so, sorry, I've been calling it a pound sign my whole life, and it's hard for me to start calling a hash, but I'll work on that.

Alright, so down here the .000 says you're going to display the zeroes whether there's precision or not, and I've changed these numbers to be 6.42 and 6.4. Now this is interesting, this is one I never really knew, if you put a ? instead of the 0, it says that it'll display the precision if it's there, but if not, don't display it, but don't let things flow to the right. Alright so here, let's change these numbers, 6.42 and 6.4, alright, see here, the decimals don't stay lined up. If you want the decimals to stay lined up, use the question marks, if you want to force the zeroes to be there, use the zeroes.

Now hey, as we go through here, there is a LOT of detail in minutia, but I do not have the corner on the knowledge here. If you see me miss some cool number formatting trick, please leave that down in the in the YouTube comments. Alright now, here's the next concept, we can display 1, 2, 3, or 4 number formats in the Type box, if you display just 1, it's just the number that's used for everything, alright. But if you display 2 and they're separated by a semicolon, then the 1st one’s for numbers >=0, and the 2nd one’s for numbers <0. So right here I've used two codes, +0;-0, and so that means the negative numbers show up with a minus, but the positive numbers show up with the plus, but because I didn't specify what happens for 0, it shows up with a plus as well.

So we can add a 3rd zone: positive, negative, zero, alright, so +0;-0;0 , alright, so that handles zeroes. There can even be a 4th code of what to do if someone enters text. Alright now, next concept, in the custom number format, in addition to the zeroes, you can put text in quotes! Alright, so let's take a look at the custom number format here, for positive numbers, the words “Please Remit “ and then the number, for negative “Credit Balance of “ the number and “(Do Not Pay)”. And for 0, I don't even show the number, I just show “No Balance”, alright. So you can actually use this to replace the number, this is -1, 0, and 1, and these are formulas, to get the formula is just to copy that down. All negative numbers will say “Loser!” or “Down” or the word “Minus ” and the number, so we put in -5, still says “Loser!” or “Down” or “Minus 5”. For 0, we can have it say “Zero” or say “Flat” or say “0”, and then for a positive number, let's put it in 55, “Winner!”, “Up”, or “Positive 55”, alright, so you can actually add words. And what's cool, so this is 55, 55, 55, if I would SUM that, they're really numbers, and it's really going to work, like normally the SUM wouldn't work with text, but that actually gives me an answer. And so, if you want to keep numbers there, but display words in the cells, this is a great trick.

Alright now, these are the characters can be used in the custom number format without quotes, so if you happen to just need a + or a -, that's why you don't have to put it in quotes. And there's some additions to this, like once I saw this list, I'm like “OK, let's see what we can screw around with.” All of these are the numbers 1 2 3 4, so if I used a custom number format of {(number>&, all of that will display. If I want to separate the digits by dashes, this is a custom number format to get that to work. And these are all real, so 7854, see everything changes, these are all formulas coming down here. So I can kind of screw with the custom number format, and put characters before it, put characters after it. One that's not in the list that I know it works is K, you're allowed to put a K at the end, and it's not in quotes. But M, because M means months or minutes, you'd have to put it in quotes or put this \M to get an M to appear at the end.

Alright, next concept, in each zone you're allowed to specify color, right, so up here the positive numbers are green, and the negative numbers are blue. Ctrl+1 to get to Format Cells, before the custom number format, in the first zone, the positive zone, I said green, and for negative numbers I said blue. What color are we allowed to use there? Goes back to the days of 8 colors, so black, green, white, blue, magenta, yellow, cyan, and red. But thanks to Mike Alexander, you're allowed to use 56 other colors, Color 1, Color 2, has to be in [], like that, those are the 56 colors. If you need to change these, we're getting really deep now, now we're going into Charlie kid territory! Excel Options, Save, and then click Colors, alright, and like, if I wanted to change that orange to something else, maybe the purple, click OK, click OK, click OK, and one of these, I can't tell which one it is would have changed to that color. Another cool trick, what if you want to hide all numbers? So you have some secrets here, and you don't want anyone to see them. Ctrl+1, put a custom number format, I'm saying “Display nothing for positive, nothing for negative, nothing for 0” click OK, and everything's hidden. Of course, they can still see it up here in the formula bar, so it's not perfect, alright, here's some other customer formatting characters.

An asterisk says “Display the next character like a check-writer”. So those ** is saying “You know, fill the area up until the number with asterisks”, but you can change that, you can make that character anything you want! So *! will put all exclamation points in there, let's change this, Ctrl+1, will change it to *X, and I will get all X's up until that point. So you can create the check-writer effect, but it doesn't have to be with an asterisk, you can put whatever you want in there. Put a comma at the end, so this is 123456, custom number format with a comma after the zero says “Display this in thousands, divided by 1000”, I put the ,K there for thousands. When you want to do it in millions, so there's our 0.0,, each comma says “Display, but divide it by another 1000”. So that's dividing by a million, put the “M” to display millions. I know some people use MM there, my sister Alice(?) who works for Procter & Gamble, “MM” has to be in quotes.

Now here, this is one where we're forcing a number, if someone enters something is not a number, the message in the 4th zone is going to overwrite it. So if we put in the number 1, it’s great, put in -1, it’s great, if we put in “Hello”, he yells at us, “Enter a number!”. Alright, again, these are formulas that are pulling whatever I type into A over to B. You can use conditions, alright, so the positive, negative, zero, you can override that. So I'm going to say red if it's <70, blue if it's >90, and you can only have two conditions, and then a 3rd zone for things that are not <70 or >90, old, old-fashioned conditional formatting. Another one, here's another one, this is just crazy, is putting an underscore and then any character leaves enough space to fit that character. You see this a lot with _( to keep the positive numbers lined up with the negative numbers, but you can really screw with Excel. So W is the widest character, so this is putting the 1st digit, and then enough space for 2 W's, and then the 2nd digit and enough space for an N, and then the 3rd digit and enough space for an I, and then finally, the 4th digit. Is this useful for anything? Gee, I don't know, but it's kind of wild, that you can force different spacing between the digits by using the underscores.

Alright, dates, m/d/yy, you get a 2-digit year, expand to yyyy, you get a 4-digit year, put mm, it forces a leading 0 before the 7, dd forces a leading 0 before the 3. If you want to use some old-fashioned cobalt date format, don't put any /, just all the characters, YYYYMMDD. mmm spells out the month abbreviation, mmmm spells out the entire month, mmmmm gives you the JASON format. Why do I call it the JASON format? If you look at the Wall Street Journal, their financial shorts always have JFMAMJJASOND. Back in 1984, I want a vinyl copy of “Frankie & the Knockouts - Below the Belt” for answering the trivia question “What man's name can be spelled by using the month abbreviations?” And because I was reading The Wall Street Journal in school, I knew that Jason was the name, and I won that. I probably gave it away at a garage sale for $50c, my bad. Alright, dd gives you the leading digit before the date, ddd spells out the abbreviation, dddd gives you the entire day name, weekday name, ddddd doesn't do anything. You can combine these however you'd like. dddd spells out the day, Tuesday, put the word “ the ” in quotes, the day itself of mmm, gives you the abbreviation, alright, so you can combine these however you’d like.

Time, one weird thing with time so, h:mm, or AM/PM, so if you don't put the AM/PM it forces into 24-hour time, if you do, then you get 2:15 PM. If you have hh, it forces the leading zero before the 2, you can even add .0 after the seconds to get milliseconds. Now here's the bizarre thing, actually before I get to the bizarre thing, I don’t know, let's do the bizarre thing first. Let's do =SUM of all of those, so all of those are 14 hours, 6*14=84, it should be 84 hours, but if I format it as a time, it's saying 1:30 in the afternoon, alright, and here's the thing. So we have a number here, this morning 10/9/2016 at 6 AM, and I just have formulas copying that down, and I can override, remember, it's formatting as a façade. I can say “Hey, just show me the years, just show me the month, just show me the days, just show me the time portion of that.” And over here in this SUM, when we're asking for a time, we're saying “Hey, ignore the years, the months, the days, just show me the time.” and what this really is, this is three days + 1:30 in the afternoon.

So in order to get this to display correctly, you either come into Time and look for the one that says 37 hours, or go to Custom, and you'll, see putting the [h] says “Show us the full number of hours.” So if I need to see hours and minutes, [h]:mm will show me that, that is actually 85.5 hours, I see, I was writing off the 15 minutes, trying to get the 84 in my head, I was close. Where else can you use square brackets? So 6 AM, you can show the hours, you can show just the minutes, 6 AM is 360 minutes, or just the seconds using the square brackets. Alright so, let's go back to one of the number formats we have under the Accounting format, and see if now, after this really long episode, we understand what's going on.

So what this is saying, _( says “Leave as much space as a ( in the positive value.” “* “ says “Repeat spaces until you get to the numbers.” The #,##0 says “We're going to use the comma separator, don't put leading characters if we don't have the precision, always put two decimal places.” And then _) says “Leave as much space as ), and that's for the positive numbers.” OK now, for the negative numbers, it's really bizarre out here, before the $ they're leaving as much space as a ( so in other words, they're never going to display that. Again “* “, but here they're now going to put in the ( and the ). And then for 0, leave as much space as the ( and as much space as the ). Don't put anything, we're replacing the zero with the “-“, but then leave as much space as two decimal places, so that way that it's going to line up with this. And then finally in the 4th zone, if they type in text, leave enough space for ( and a ), and then put the text in.

Alright, this was always just a lot of nonsense than me, but there's specific meanings for all of those characters. It's crazy that all of this information, 18 minutes worth of podcast, comes from 3 pages in this book, just imagine how much is there in all 200 pages of the book. Buy the book, click the “i” on the top-right hand corner, you'll appreciate it, and I'll appreciate it. Alright, long, long episode: Number formatting is a façade, Excel stores one thing, it shows us another. Decrease Decimal and Increase Decimal is a great example of that, they still store all the decimals, but it simplifies the display. There's 11 formats here, but really to get to them, you go to the dialog launcher, and then you can choose, something and then modify it in Custom if you want to. Talked about all this custom number formatting codes, if you have better tips, please put them in the YouTube comments.

Zero in the format code says “Excel MUST display the digit, whether there's precision or not.” Hash sign, pound sign says they may display the digit if there's a precision, but they don't have to. Question mark says “Leave the space for the digit if there's not enough precision.” There can be up to 4 formats separated by a semicolon, that way we have a different number for positive, negative, zero, and text. 2 formats, the 1st one is for non-negative, the 2nd one is for negative, if you had a 3rd format, the third format is for 0, the 4th format is for text, if it's there. Alright, we can display text with a number, or display text instead of the number, “Winner”, “Loser”, or “Up”, “Flat”, “Down”. You can specify a color for each zone, to change the color codes visit Excel Options, Save, Colors. Asterisk any character to repeat that character before the numbers, like a check-writer would do. Put comma at the end to divide by a thousand, two commas to divide by millions, 3 commas divides by billions, and so on. We can use conditions in the zone, and then underscore X will leave a space the size of X. Alright, the different variations of months, whether you put one digit, two digits, the abbreviation, spell it out, or just the first initial. Days, one digit, two digits, abbreviate, or spell it out, there is no 5th. And then finally, to get times in excess of 24 hours, you have to put that h in square brackets, and other variations of that.

Thanks for hanging out to the end! We'll see you next time for another netcast from MrExcel!