Custom formats using asterisk, semicolon, and brackets

- For most Excel users when it comes time to adjusting numeric formats, go to the Home tab, and in the Number group here use dollar sign, comma, percent, possibly increase, change the decimals. But behind the scenes, there are additional formats and anytime you press Control 1 you might or might not have to click the Number tab you could be viewing custom formats. Many times when you come here you're seeing General or Number or Currency. If you click Custom and start to scroll up and down you might be a bit overwhelmed if you've never seen this capability.

As we start to scroll up and down here we see various parentheses, semicolons, brackets sometimes we see colors like red, black possibly. All kinds of unusual symbols in there. Asterisk in there also from time to time. Without going through all these, some of these will have meaning I think anybody who is interested in different kinds of formats will learn something from what we're about to show you here. So let's back out of here for the moment. Take a look at the entries in Column A. All the entries that are above zero are black.

Zero is blue. All those below zero are red. We could have done this manually but these are actually set up by way of a custom format. If these cells are highlighted and I press Control 1 we see the format that's being used here and that's quite a lot, looking at it here. Now, if you were to scroll up and down here the one symbol, it's one of the ones I mentioned, that is key is the semicolon because that allows us to divide a formatting list here into multiple formats.

At most we can have four kinds of formats. So let's look at the example that's actually being used in Column A. Here it is depicted larger, up above in this tan panel. You wouldn't know this at first, but every time we see a semicolon that's key to the idea of formatting. To the left of this semicolon is the format that's going to be used for all numbers that are positive. Down below here, there are four possibilities in the way that we could use or in one case, not use, a semicolon.

If you use semicolon once, everything to the left of it will be a format for those numbers that are positive and zero. Everything to the right of the semicolon will be the format for those numbers that are negative. If you use two semicolons, thereby splitting the description into three formats, to the left, as above, it's going to be just positive, not zero this time. Between the two semicolons, that will be the format for negative entires, and following the final semicolon, the format for zero.

If it's four parts, you can see how the breakout is there. So the example above that's being used in Column A says in effect, all positive entries will be in black. You see that happening over there. All negative entries are in red. If it's zero, it's blue. Now notice the numeric format in all cases is the same, a single decimal place. If it's a text entry, it's magenta. So we see that in cell A1. We can change the numbers in here, they don't necessarily to be in any order but just to test this, if I click right here and make that be -200 it should turn red.

Or down here, if I make this be +150 it's going to be black. If I type in here nothing unlikely, but you see what it's going to be? Magenta. Let me undo that. And undo the previous ones too just to point out how that works. You can also have logic and here, possibly, conditional formatting could be a better choice but look at the entries in Column C. All these numbers are magenta and these are blue and then these are red.

This too could have been done manually but it's based on logic using a custom format, and in this panel here we can see the logic being used. This second line here in the tan panel is the conditional format for what we're seeing in Column C. As we look back and forth at Column C and this description here, we're saying, in effect, all numbers greater than or equal to 200 are going to be magenta. So in our list over here if we were to click over here and I'll make this be 199 that's not going to be magenta.

Notice how the logic is structured, though. It is left-to-right logic. This happens automatically to these values if they are 200 or more. That's it. The logic that follows then says if this number is less than a -200 it's going to be red. Here is a -200 down in cell C16 but it's not less than that, so it stays blue. Why does it stay blue? Everything else that doesn't fit the logic is blue.

In a sense what we're doing here, we're rewriting these rules here. In other words, positive, negative, zero, text don't really apply here. We're dividing this into, and we can only still have at most three semicolons, but instead of this simply being positive, we're now structuring it to say that it's 200 or more. This format here is for all entries that are less than a -200. Everything else is going to be blue. And if it's text, it's going to be green, and we see how that's playing out too.

You could in certain situations display the word zero automatically. In other words, in the example right here going back to our first example up here, if we were to change this we could actually put in the text "zero" if we wanted to, while it's blue as well too. You have to experiment with these a little bit. I wouldn't say that that second option is going to be used very often for very many people. In fact, if you are familiar with conditional formatting, you could have more layers than this. I wouldn't exactly recommend that but you could have data in six or seven different colors here by way of conditional formatting if you wanted to.

The maximum options that we have here ultimately are three, and have to use the logic in a somewhat creative way to get there. There are other things we can do with conditional formatting too. Look at this entry right here. It's 23, or a certain number, followed by dashes. Here's another number. Five six seven four. Here's a number preceded by dots. It's got additional formatting as well. A lot of times when you don't understand the formatting that's being used jump right into Format Cells, Control 1, and you will see the format being used.

The format being used here is asterisk period, and then the pound signs, meaning suppress the leading zeros or don't show the leading zeros and we see the remainder of the format there. So you wouldn't exactly guess or know that whatever follows the asterisk will be repeated throughout the width of the cell depending upon the width. If I make column G wider, keep an eye on cell G10, or if I make it narrower you see what's happening that way. Fewer dots to the left of it.

It doesn't have to be dots, doesn't have to be dashes, it can be any symbol you want. Maybe just jumping back here momentarily I'll press Control 1, I'm on cell G10. The format here has a period that follows the asterisk. I'll put in maybe a left arrow. So I want a left arrow repeated, and we see how that looks. So you can imagine at different times using that capability. The colors over here by the way are the colors we saw in the previous example. We didn't use every single one of these, cyan doesn't show up, nor does light green show up very well on a white background, so you might want to shy away from those colors and you want to be really sensitive to the idea too that red very often signifies, at least in accounting and finance it signifies negative entries so you want to be a little bit careful about using red so that it's not misinterpreted as being negative, although you could use it for negative.

Here's another option too for displaying entries. These are unusual formats what they have in common are that they, at least the last three of them, they end in a comma. It's certainly not obvious but a trailing comma, a single comma means display a number in thousands. It's as if you're saying, "Let's hide three characters to the left of the decimal." So for a value like this, for example, or all these, we could do them all at once, or maybe just a single one. Suppose we want this to be displayed as 6,811.7 in other words, that's how many thousands it is.

Or how about millions? Suppose we want this to be displayed as 6.8? We can do that with a trailing comma. Let's try that first with millions, let's display this as millions. Control 1. Custom. And we can see the number although we don't necessarily need to. I'm going to take just one of the basic formats that appears one that's often the default here for numeric entries and alter it and put in a single trailing comma and for the moment I'm ignoring decimal.

A single comma, display it, it will be in thousands. We're about to see 6812. Now it does visual rounding, it's not changing the result of this. When you look in the formula bar you see the pure value. If you double-click you see the pure value. But a single trailing comma means hide three characters to the left of the decimal. In other words, display it in thousands. Let's jump back in. Control 1 again. Let's alter the format again, put in a second comma.

We will now display it in millions and that too will do rounding so it'll just come out as 7. It's closer to seven million. Look like that. Probably not what we would want. If you want to display it as 6.8 come back again, Control 1. Take that same format, and immediately after the zero put in point zero. So that's easier to read and we don't have to have the numbers exactly anyway. We could copy this format downward by the way real easily by pointing to that fill handle lower right-hand corner, hold down the right mouse button, drag downward and simply fill Formatting Only and we see that.

Again, we did not change the numbers they are still here, you see it by double-clicking or you see it in the formula bar but it's an easier display to read and much of the time, the exact information doesn't have to be displayed although it is there being stored. Here's some variations on how we do this, the display numbers. We can even do this in hundreds in a slightly different way. Or thousands, a single trailing comma. Millions, two commas trailing, and billions, three commas.

One other use and you'll see this sometimes with phone numbers this is probably the more standard way we see phone numbers but suppose you'd like to use periods here. That will be, in effect, using less information because we've got here parentheses, a space and a dash. If we put in periods separating the area code first and then the exchange portion of this, it will take up less space. So how do we do this? Column Q, let's just click it. Go to Format Cells by way of Control 1.

Phone Number is a built-in format by way of Special and I would recommend using it but if you want to change it and use periods in there instead, let's wipe out whatever we're seeing here and do the following. The zero character means a numerical position that will display regardless of its content so we want three zeros, that will be for the area code, and then within double quotes, period, double quote, and then three more characters, zeros, numeric characters and now another period: double quote, period, double quote and four numbers, that's four zeros.

I'm going to copy that and display it larger. Click OK and we see that display. The format there, and I'll just pop it in right here, is this. That's the format being used in column Q. So some variations on how we used custom format. There are lots of other possibilities as well. Remember, if you're interested in some of these and you happen to see them when you click on a cell simply by going to custom format, you can see by pressing Control 1 the actual format being used.

In this case, General*- remember, that's how something repeats the dash. So quite a few variations here on how we use custom formats.