Hello there. This time we'll talk about some of the very unique formatting tricks which will help you achieve something which otherwise you thought could not have been achieved. For example, if I look at a sheet, it has a column of date, client name, amount, check number. Now, the problem with this kind of data is a check number usually is a six digit code. Now, when you take a printout, it doesn't look that good, why? Because when a particular staff member thought about writing the check number, he would have written the three digits 000 and then maybe the check number 123.

Now, if you had not made the single quotation, and you would have pressed enter, the zeros would have been lost and this something that you have already known. The second point is I may wish to apply a prefix in all these numbers talking about rupees r s, and in India, it's a customary thing to have put rupees dot sign before the numbers. Also, just to avoid confusion. Whether the date is third June or sixth of March, in this case, I would want to apply a trick, which allows me to have the format of the cells, which not only signifies date with the date also signifies the month name in letters, pure letters. So how do I achieve this, compared to what I have in the sheet call after I have a column of date, perfectly formatted. The names of the client has these trailing dots, and the best part is as I increase the size of the column with notice itself adjusts even if the name was a very simple one, let's say letter A letter A.

Let me delete that enter. Notice the dots self adjusts, you have the prefix rupees and in fact they are just acting like a number despite the word rupees being there. Also, in check numbers have all the six digit code now how to achieve this first, to convert this data in a format which looks like this Mmm, why by format, choose the entire column A and press Ctrl Shift three. That's a shortcut key Ctrl Shift three. Next, if you want to have the trailing dots in all these client names, I will have to use this format at the rate star.in the column B, so I right click, I go to Format Cells, or instead I can choose column B and press Control digit one, I will go to custom in the place where I'm supposed to write the general I'm deleting that I am putting at the rate star dot.

And if I press OK, well, exactly the same thing as I was looking for not only that, I can go to Format Cells. And in the custom I can also perhaps put dash is not the dot i can put the dash Okay, now you have the same effect as this one, although I would not want to have the same effect in the heading. So I quickly copy the format from date and from ply name I simply going to paste special and format Next, I would want to have a prefix of rupees in column C. So what should I use? I go ahead and choose all the numbers. I press Control digit one, I go to custom. Now notice, I'm going to put double quotation, r s dot space double quotation close, and then 0.0.

This ensures the numbers are there with one decimal. Notice these are numbers with one decimal, but I don't find any comma separated values just the way you have in millions and lakhs. So what do I do? First, I go to Home tab, I apply this comma separator, I reduce one decimal. Then again, I go to right click Format Cells or you may press Control digit one. And within that, notice this custom it has a long list of our custom code which signifies the comma separator should be there with the millions now before the start point of this custom code, I put a double quotation, I put in our s dot double quotation closed.

Notice in the preview section, the rupees has been added. And as I press OK, you get benefit of both the words, you have the rupees prefix as well as the number with comma separator as well as one decimal towards the last I would want to have the prefix check number 00 wherever it needs to be added, and the point is the total digit should look like six digit code. So, the trick is I choose all the check numbers, I press Control digit one that will take me to format cells. And once I'm there, let me go to custom. And in the place where the general is posted, I'll put in six times zero. Now, notice in the preview window, it has shown me the same number four digits but before that there are two zeros that seems to have been added up.

Okay. And there you go. This is the kind of format you were looking out before getting a printout. Also, just keep in mind the length of the data remains to be fooled it is not six. This is just a display value that has changed through the format of cells. Hope you enjoyed the session because these tricks are very difficult to find over the internet and I've given all that in one place.

Do apply this and let me know your feedback.