Discover the Convenient Bilty Format in Excel for Support

Bilty format in excel for Support

Let's cover a few Excel custom formatting tips that can save you a ton of time. Actually, they're going to help you avoid common spreadsheet mistakes, I'm first going to show them to you just so you have an idea of the power of custom formatting. And then I'm going to explain the rules behind it. It's actually a very simple rule, if you remember it, you've nailed it. (hip hop music) Here I have some sample data, I have information of compensation for these individuals. Alyssa my boss comes to me and says, could you please format this, that we have 1,000 separator and new decimal places, so all I have to do is highlight these right mouse click, go to format cells or use the shortcut key, CTRL 1. I'm going to come to number here, activate 1,000 separator and zero decimal places. I can also decide on how I want negative numbers to be shown. I'm fine with these. I'm going to go with okay, and that's done. But let's see now, he comes to me and says, could you please blend out the zero values? I don't want to see them in the report. How would I do that? That's when I could use custom number formatting. So let's go back, with CTRL 1, I'm going to come to custom here. Now, I can see that that's the format I applied to these. Now to blend out the zeros, what I'm going to do is add a semicolon here, copy this, put minus this. And that's it. And my zero values are gone. Now, first of all, before I explain to you how that's done, are the zero values really gone? They're not really gone, right? They're still in the cell. I just can't see them. Because remember, number formatting is just like a disguise that you apply to the cell. The actual number behind this is what you see here. So whether I show the decimal places or I don't show the decimal places, it doesn't actually change the number that's behind this. All this is is just a disguise. Now there are some exceptions to this, for example, with the percentage that actually shows the percentage format in the formula bar. But other than that, whether we use the accounting format or another format right here, the number behind the cell always stays the same. And in this case, I've just used a custom rule to hide the zero values from view. What's the rule behind this? So let's go back with CTRL 1, the rule that you need to remember is that the first position before the first semicolon is how you want positive numbers to be shown. The second part is how you want negative values to be shown. The third part is how you want zero values to be shown. And the fourth part is how you want text to be shown. So right here for the third part, I typed in zero values. When I press OK, what am I going to see here? I'm going to see the word zero value, but what is inside the cell is actually a zero, I could use this. And let's say add five to this, it understands that this isn't text, there is actually a zero in this cell, right? So that's basically all you need to remember, positive, negative zero and text. Now, the moment you add your own new custom format to your spreadsheet, it becomes available so you can reuse it, you don't have to type it from scratch every time. You can use some of the existing custom formats that you see here to help you write your own format faster. Now, there are two symbols here that catch our attention. One is zero and the other one is the pound or the hash sign. Both of these are just placeholders for numbers. The only difference is that the pound sign is a variable placeholder, whereas the zero is a fixed placeholder. So you generally use it for decimal places or here to visually indicate this is a 1,000 separator. I'm going to explain this in more detail in a second. One thing I want to show you what if I did this, what will happen to these values? They're going to disappear. But they don't really disappear, right? Because they are still sitting in the cells, they just disappear from view. If you want to erase the type of format, you just have to go back to the general format, that's a disguise eraser. Here, let's take a closer look at the difference between zero and the hash or the pound sign. So I'm going to highlight this here because there are values behind this, I've just hidden it from view. I'm going to apply my eraser right here. Here, I want to input some product codes, I want to show them in my report as four digits. I don't actually want to change the number here. I just want to change the way it's shown. So what I can do is highlight this, press CTRL one under custom, I'm going to use the custom format 0000. This forces this to become four digits and press OK. That's the difference between the zero and the pound sign. So if I come back here, and I change this to ####, what do I get? I get back what I had, because the pound sign is a variable placeholder, whereas the zero actually forces it to be, in this case, four digits long. Here, let's say my boss tells me, could you add text to this, so every time we come across a negative number, we mention in the cell that this is "returns". If I was going to do this the wrong way. I will come here, type in returns. If I press enter, Excel's not going to like it because the cell starts off with a minus number. So I'm going to say OK, and I'm going to go and put this as text, I'm just going to add a quotation mark to this. If I wanted to add these together, obviously, this is not going to be included. But I could make my boss happy and make my spreadsheet happy too at the same time, if I use custom formatting instead. So I'm never going to change these to not numbers, I'm always going to keep them as numbers. But I'm going to go and define a specific custom format, and say that if I come across a negative number, it should put the word returns after this. Remember, we start off with how we want positive numbers to be formatted. So in this case, let's just keep it simple. I'm just going to keep the number as I have it, then minus pound sign. And now I want to say returns. So I'm just going to type this in, then I can decide how I want zeros to be formatted, well I can just hide it if I want. And if I don't specify anything, it's going to take the positive format for zero. If I don't specify a negative format, and just one format, it's going to use that format for negative and zeros as well. So now I get 120 returns is exactly what my boss wants. But inside the cell, it's the number that my spreadsheet likes, right? And my calculations like, so I can add these up without problems now. I'm going to get to sum of this, I'm going to use the shortcut key, ALT equals, I want to say that this is the total number. The wrong way to do this is to go and say and add "total" to this. But this is not a number anymore. I can't use it in formulas. It's not going to work because this is text, right? So I'm going to use custom formatting for this. And how can I do that? I'm going to use a 1,000 separator in this case, because these are big numbers is easier to read, add total to this. And that's it and say, OK, now my formulas are going to work. So these are some tricks you can use in custom number formatting. Now we have a lot more details in a blog post that was created by Michael, he goes in a lot more detail explaining how custom number formatting works, taking you through some of these symbols here, and at the bottom, you can download an Excel workbook that takes you to the example I did right here. But it also gives you a more detailed explanation of what these symbols are. And some examples that you can play around with yourself here, details on how to use this file is in the blog post. Basically, in this column, you can see the number format that is used here. If I, for example, change this to this accounting format, I go in this cell and press enter. I can see the actual format applied to this. This saves you some time, so you don't always have to go back to the custom formatting dialog box to see what type of format is used. Also in the second tab here for the home ribbon formats, it takes you through the different formats that you have available here. And what's actually behind these formats. So if you're interested to learn more about custom formatting, make sure you download this file, go through the blog post and play around with this. You can get super creative with custom formatting. I have a few more videos and blog posts that show you different ways you can get really creative with it. I've added them to the cards into the description of the video. What about in your files? Do you use any funky custom formatting rules there? Let me know in the comments below. I hope you enjoyed this video. If you did, don't forget to give it a thumbs up. And if you want to improve your skills, consider subscribing to this channel. (light rock music)