PPT Excel: 2 ways to format data in excel

Formatting is very important in your report, dashboard or presentation. You have to give more attention in formatting while creating your reports and formatted reports are attract your users.

In this article, you will learn how to format the tables or reports in excel using keyboard shortcut method and format painter. Maintain the same format across all tables in your report, dashboard or simulator is a best practice. Formatted tables are very helpful to quickly find KPI and compare values between reports.

You can use Home tab Format Painter tool in the Excel. It helps you to apply the same font color, style, size and border from one cell to another cell or range of cells. 

Suppose you have two tables Sales Table and Profit Table. Sales table is already formatted and you have to apply the same format to your profit table for your final reports.

Notice that in the sales table there are three different formats used for column header, row header and sales values. You can either use three times or select entire table once and apply the format to profit table. Since, both the tables the number of rows and columns are same.

How to use Format Painter in Excel

Apply the entire sales table format to profit table

Please follow the below steps to use format painter to apply entire table format to profit table

1. Select the entire Sales Table B2:G7
2. Go to Home, Press Format Painter – you can see the mouse pointer turns with a paint brush with plus symbol
3. Just select Cell B11

Profit table is formatted as Salest table just in a single click.

Use format painter to column header, row header and table data

Please follow the below steps to format column header, row header and table data

1. Select the column header B2:G2
2. Go to Home, Press Format Painter
3. Just select Cell B11

1. Select the row header B3:C7
2. Go to Home, Press Format Painter
3. Just select Cell B12

1. Select the row header D3:G7
2. Go to Home, Press Format Painter
3. Just select Cell D12

How to format cells using Paste special in Excel

Apply the entire sales table format to profit table

Please follow the below steps to apply entire table format to profit table

1. Select the entire Sales Table B2:G7
2. Press Ctrl+C
3. Go to Cell B11
4. Press Alt + H + V + K

Profit table is formatted as Salest table

Use keyboard shortcut to format column header, row header and table values

Please follow the below steps to format column header, row header and table values

1. Select the column header B2:G2
2. Press Ctrl+C
3. Go to Cell B11
4. Press Alt + H + V + K

1. Select the row header B3:C7
2. Press Ctrl+C
3. Go to Cell B12
4. Press Alt + H + V + K

1. Select the row header D3:G7
2. Press Ctrl+C
3. Go to Cell B12
4. Press Alt + H + V + K

Conclusions

You can use either method to apply format to your table. Apply the entire table format once If your source and destination tables are identical matrix same number of rows and column.

You have to format your worksheet before send to end user. Formatted tables or reports are user friendly to read and analyse.