• Home  / 
  • Excel
  •  /  5 Powerful Tricks to Format cells in Excel

5 Powerful Tricks to Format cells in Excel

In this article, you will learn 5 tricks on how to format cells in Excel. Applying these formatting cells techniques in excel will make your worksheet look attractive and easy to read. Almost all Microsoft Excel users know how to change date format, numbers and text formats. But very few know how efficiently and quickly customise it to suit your requirement.

These excel tips are covered in detail in our video based advanced excel training on our online school for professionals.

Let’s discuss 5 tricks to format cells in excel:

1. Date Format:

For example, you have a long set of dates in cells like below.

long set of dates in cells and you want to change the date format while using format cells

And you want to change the date format as a dd-mm-yyyy format. So that it can show the date format like 2-Apr-2014, which is easy to read. Normally what we do is, we select the entire range of cells. Then we right click and then click on Format cell in excel. But there is a shortcut key for this. Simply press ctrl +1 to format cells in excel.

1. So after selecting the range just press ctrl+ 1.
2. Then select date category and then choose your Date format from the selection pane at the right

In the Format cells then select date format

Now you have your date as per your required date format. Besides this, there is also another trick if you want to show your date as dd-mmm-yyyy format. Select your range and then press Ctrl+ Shift + 3.

If you want show your date as dd-mm-yyyy format press shortcut key ctrl shift 3 - Format cells

Now your date is properly formatted with one shortcut key. Remember, the shortcut key is Ctrl + Shift + 3 to format a date as a dd-mmm-yyyy format.

2. Trailing Dots:

Here I will talk about how to format cells in excel with trailing dots. For example, you have a list of names of your clients. I want to add dots (.) so that it looks good and is also self-adjusting. Let’s look at this example.

You have list of names and you want to add dots so that it looks good - Format cells

I have the list of client names and I want to add dots (.) so that it looks good.
You may have questions running in your mind regarding this technique of formatting cells in excel
How will we achieve this?
Will we go ahead and manually put dots (.)?
What will it happened if I delete few letters from a client’s name?
Will it be self-adjusted?

Solution:

1. Select your range where you want to put dots (.). You can select the entire column and then press Ctrl +1 and dialog box format cells in excel will open.
2. Then go to custom and in place of general type @*.
As soon as you will press ok you will see the trailing dots are added and it is self-adjusted. If you want to test then delete few letters from a client’s name and then check. You can also change the width of the column. The dots will be self-adjusted.

Format cells - then go to cutom and in place of general type @*.

3. Currency Format (Prefix):

I have a large record set of the amount. I want to add prefix i.e. Rs. in my records so that I can apply sum, average, min, max function. I can easily do it by typing it manually but then I will not be able to apply any function.

If I enter any amount in this column I will need to add Rs. prefix manually. If you know this trick then you do not have to do this manually.

I enter any amount in this column I will need add Rs. Prefix manually with the help of format cells
Now from the above picture, it is clear what my raw data is and what I actually want.
1. Select your range and then press Ctrl +1.
2. Now I am going to custom format in excel and then type “Rs.” 0.0. This ensures that numbers are there with one decimal.

Go to format cells then go to custom and in place of general type "Rs. "0.0

As soon as you click on ok you will find that numbers are formatted with Rs. as a prefix. And it will look like below.

Format cells - that numbers are formatted with Rs. As a prefix

Notice these are numbers with one decimal but I don’t find any comma separated values, the way you have in million and lakhs.

So what will be my approach? To apply this you need to go to home tab then apply the comma separator, reduce one decimal and then press ctrl +1 and press custom. You will find a long list of custom codes which signifies comma separator should be with the million and lakhs.

Now before the starting of the custom code put “Rs. “. You will notice in the preview section Rs. has been added. And then click on OK.

In the format cells in excel start with custom code put"Rs. " you can see preview section Rs. Has been added

As you press ok you get the benefit of both prefix Rs. and the numbers with a comma separator.

You get benefit of both prefix Rs. And numbers with comma separator - Format cells

4. Format Cells in Excel (Prefix)

Now I will tell you how to add prefix “00” for a check number. For example, you have the data like below (before).

How to add prefix "00" for a check number - Format cells

Now the point is how you will format cells in excel so that total digit should look like six digit code. It is possible to add zeroes manually. But the zeroes will go away until and unless you change the format of the cells as text. And if you do so you will not be able to apply any functions on these cells.
1. So the trick is to choose all the cells and press ctrl +1 to get the format cell dialog box.
2. Now go to custom in general type six (6) zeroes.

Custom Format cells in excel - custom then go to type six zeroes

Notice as soon as you type six zeroes, in the preview, you can see it has added two zeroes before the numbers. And then press ok. Now you are done. This is the format you are looking for. Remember one thing it is just the format and the length of the character does not change. It is just for representation purpose.

Learn Excel Keyboard shortcuts from this online excel course

5. Format cells in Excel (Suffix):

Suppose you have a weight record of children and it looks like below:

Enter kg as suffix in weight column with help of format cells

You want to change the column as the ‘After section’. You can add another column and write “Kg“, and then concatenate these fields. But again the same problem will be there and you not be able to calculate. So how will you format cells in excel such that it looks like “kg” is there and you are also able to get the sum of the total age?

Here is what you need to do.
1. First select the cells you want to get formatted. Or you can choose the entire column and then press ctrl +1 to get the format cell dialog box.
2. From the category, tab chooses number and then click on custom.
3. In the type box please type 0″ Kg”.

Select entire column then go to format cells and click on custom and type 0" Kg"

“Kg” suffix is now added to your chosen cells. Now if you want a sum of these cells, you write the formula and the result will be also formatted as “Kg” (if you format the entire column).

I will talk about the more techniques in my later blogs. Just practise all these and check how friendly this is. Please let us know your feedback.

If you liked these techniques for format cells in excel, do share it with your friend and colleagues

Check out our E-Learning Video based Free online Excel Courses, Best suited for busy professionals

About the author

Rishabh Pugalia and Niladri Sekhar Biswas

Author CA. Rishabh Pugalia has 10+ yrs. of work experience in Audit, Interest Rates Trading & I-Banking & Author Niladri Sekhar Biswas has 9 years of experience working in Microsoft Excel, as Excel and VBA corporate trainer

  • Pingback: Excel VLOOKUP Function: Why and How? – yodalearning()

  • birkenstock australia outlet

  • converse tw

  • Hmm it seems like your site ate my first comment
    (it was super long) so I guess I’ll just sum it
    up what I had written and say, I’m thoroughly
    enjoying your blog. I as well am an aspiring blog writer but
    I’m still new to the whole thing. Do you have any points for
    rookie blog writers? I’d genuinely appreciate it.

    • Niladri Sekhar Biswas

      Thanks

  • Great goods from you, man. I’ve have in mind your stuff
    previous to and you’re just too fantastic. I really like what you have received here, certainly like what you’re
    stating and the way during which you assert it.
    You are making it entertaining and you still care for
    to keep it wise. I can not wait to read much more from
    you. This is actually a terrific web site.

    • Niladri Sekhar Biswas

      Thanks. Please follow other blog also.

    • Niladri Sekhar Biswas

      Thanks Body Building. Please go through the other blogs also.

  • toms鞋專櫃

    鞋 toms

  • Vikash

    Hi,
    The tips were very simple and interesting and we use these in our daily work. Please keep posting such interesting tips.

    • Niladri Sekhar Biswas

      Hi Vikash,

      Thanks for your reply.

  • Murali

    Hi,
    Iam an excel learning student and love to get tips regarding excel and word as well.Keep updating regularly so that I may follow you.

    • Niladri Sekhar Biswas

      Hi Murali,

      Thanks. We are trying to post more interesting blogs on Excel.

  • Sudhakar

    The tips are not working for me. I am using 2010 version.

    • Niladri Sekhar Biswas

      Thanks Sudhakar

      Can you please let us know which tips are working for you? We will definitely help you.