10 Best Tips to Format your data quickly


1. Format Painter to save time from formatting your Data again and again.

Use the Format Painter to quickly copy formatting from one cell in a document to another cell.

Simply select cells with required format, click on Format Painter (Home->Clipboard group-> Format Painter) and apply it to required cells. Double click on Format painter to apply format to multiple cells.

For Details: Copy formatting using Format painter

Format Painter-1 Format Painter-2

2. Format data as Table:

Do you want to format data in a quick way? Just select data and click on Format as Table (Home ->Styles group-> Format as Table) to choose from variety of excel default design templates.

Format Table

3. Create your own cell Style or Choose from default cell style to quickly format cell.

Go to Home-> Styles group-> Select Cell styles.  Choose from default cell style of create your own using “New cell style”  option.

Read more: Create your own Cell Styles in Excel


4. Set Column width and Row width in easiest and quickest way

  • Double -click the Column/Row border:This automatically sizes the data to fit the widest data it contais.
  • Select all the columns for which you want to specify same size. Now drag one column border to automatically sizes all the selected columns to that size.Same is applicable for Rows also. Select rows and drag one row border to change the size of all selected rows.

Have a quick look at this small video.


5. Make a habit of using this keyboard shortcut of Formatting Data to speed-up your work.

Use the keyboard shortcuts for your day to day activity with excel like apply border, change font size, Format cells, apply filter, freeze panes etc.

Must read Article: 100 Most Useful Keyboard Shortcuts of MS Excel

Data Formatting
Sr No To do this Press
1 Display the Style dialog box. ALT+' (apostrophe)
2 Display the Format Cells dialog box. CTRL+1
3 Apply the General number format. CTRL+SHIFT+~
4 Apply the Currency format with two decimal places (negative numbers in parentheses). CTRL+SHIFT+4
5 Apply the Percentage format with no decimal places. CTRL+SHIFT+5
6 Apply the Exponential number format with two decimal places. CTRL+SHIFT+6
7 Apply the Date format with the day, month, and year. CTRL+SHIFT+3
8 Apply the Time format with the hour and minute, and AM or PM. CTRL+SHIFT+2
9 Apply the Number format with two decimal places, thousands separator, and minus sign (–) for negative values. CTRL+SHIFT+1
10 Apply or remove Bold/Italic/Underline formatting. CTRL+B / I / U
11 Apply or remove strikethrough. CTRL+5
12 Hide the selected rows. CTRL+9
13 Unhide any hidden rows within the selection. CTRL+SHIFT+9 (opening parenthesis)
14 Apply the outline border to the selected cells. CTRL+SHIFT+&
15 Remove the outline border from the selected cells. CTRL+SHIFT+_
16 Apply Format Painter ALT+H+FP
17 Apply Wrap Text ALT+H+W
18 Align the text to Right / Center / Left ALT+H+AR / AC / AL
19 Increase Indent ALT+H+6
20 Decrease Indent ALT+H+5
21 Merge Cells ALT+H+M
22 Apply All borders to the selected range ALT+H+BA
23 Change Font Style ALT+H+FF
24 Change Font Size ALT+H+FS
25 Go To Specific Cell CTRL+G
26 Set Auto-Filter CTRL+SHIFT+L
27 Find / Replace Data CTRL + F / H
28 Freeze Panes (First select the cell where you need to freeze panes and then apply short cut) ALT+W+FF
29 Insert a Function or Formula from a list of functions SHIFT+F3
30 Paste Name in a cell range F3

6. Use this Paste Special Tricks to paste data in special way.

-Use Paste Special options to paste as Values or Formulas or Formats.

-Set Source data Column width while copying

-Transpose data (Switch row data to Column and Column data to row)

To use paste special, Copy data, Go to Home->Paste Special and choose required option from Paste Special dialog box.

Format As a Table

Related article: Paste Special tricks

7. Clear all Format in just one click.

Use Clear Format option to remove any type of formatting of cells.

Select cells and Go to Home->Editing Group-> Clear ->Clear Formats.

Clear Format

8. Format Numbers:

While dealing with date and time, we need to customize the format the way it is required.

Right click on cells with Date or Time and Select Format cells, Under Category select Date, From the Type box click on required Formatting.

Same way you can also change the way Time is displayed.

Format Date and Time

For more information: How to customize Date and Time format in Excel?

9. Format data for Perfect Printing

You might be facing difficulty while printing large spreadsheet. It takes lot of time in formatting data in such a way that print data the way it is required using minimum papers.

Read this article which will be really helpful for proper Print of Excel sheet.

5 Tips you must know before printing large spreadsheet

10.  Conditional Formatting:  Use the best feature of excel to format your data based on certain criteria.

Go to Home-> In styles group-> Select Conditional Formatting.

For more information: Use Conditional Formatting to change the cell color based on criteria

Conditional Formatting


2,706 total views, 3 views today

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>