347 647 9001+1 714 797 8196Request a Call
Call Me

Formatting Tips in Excel

February 16, 2015
, , , , , , , ,

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.


About the Author

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 71343