Share with your network!

Formatting Excel sheets is a thing that you will start today and format it till the next week and still it will look the same or even worse. But formatting is important as it does not only make the excel sheets look more presentable but also makes it much easier to understand. So today, we bring to you some important formatting tips that will make your data presentable and user friendly.

Use Tables to Format your data quickly – It is important that your data looks presentable before formatting and tables are the best option. Tables will ensure that your data is quickly formatted as required by you. Follow these simple steps to create the table.

1. Select that data that want to format and press Ctrl + T.

2. Verify the cells in the dialog box launched and click enter. ( refer image 1)

3. Your table is ready. Now you can sort your data anyway you like. (refer image 2)

Image 1

Table formatting in excel

Image 2

Table formatting in excel

Using Autofill Option for Formatting – If you thought that auto fill option is used just for copying the formula and the series, then you are wrong. The autofill option also helps you in formatting the cells. Just follow these simple steps:

1. Select the cell that contains the formatting. For example (refer image 3) I want to add a yellow background to the entire column of months.

2. So I will select the cell containing yellow background and shall double click on cells fill handle.

3. Don’t Panic when you see that your data has changed just click on auto fill control box that is created at the bottom of the selected area. (refer image 4)

4. Select the option of Fill formatting only and you will see that the column has been formatted. ( refer image 5)

Image 3

Using autofill option in Excel

Image 4

Using autofill option in Excel

Image 5

Using autofill option in Excel

Use Format painter – Format painter is one of the most easiest tool that one can use for formatting. If you have same data that needs to be formatted then you can use this tool by following these simple steps.

1. Select the already formatted data that you wish to copy. As it can be seen in Image 6 I have seleted the data in from A1 to E13 as I want the same formatting in the data ranging from H1 to K10.

2. Click on the option of of format painter which is available extreme right of the ribbon. ( refer image 6 )

3. Go to that part of the sheet where you wish to copy formatting. ( H1 to K10 in image 6)

4. Select the cells and your data shall be formatted. ( see image 7 )

Image 6

Using format painter in Excel

Image 7

Using format painter in Excel

Clear all Formats – Sometimes when you want to start afresh and do not require any of the formats, then you can simply select the cell which need the clearing of formats and go to Home> Clear > Clear Formats and your data shall be format free.

Image 8

Clearing all formats in Excel

We are sure that you would love to know some shortcuts for formatting so here goes the list



Bold the selection

Ctrl + B

Italicize the selection

Ctrl + I

Underline the selection

Ctrl + U

Strike through the selection

Ctrl + 5

Switch between worksheet tab

Ctrl + PgUp/PgDown

Enter Current Time

Ctrl + Shift + :

Enters Current Date

Ctrl + ;

Apply the time format

Ctrl + Shift + @

Apply the Date format

Ctrl + Shift + #

Apply the currency format

Ctrl + Shift + $

Apply the percentage format

Ctrl + Shift + %

We hope that these tips will help you in impressing your Boss/Professor and if you think that we have missed out some important ones then you can let us know through the comment box below.