5 Powerful Excel Formatting Shortcuts That Will Transform Your Data

5 Powerful Excel Formatting Shortcuts That will Save You Tons of Time

Formatting data not only makes it look good but increases the readability of the data set.

If you work with data and create good looking client-ready reports, it will pay off to learn some Excel formatting shortcuts that you can use regularly and save time.

In this tutorial, I am sharing my top five Excel formatting shortcuts that I almost every day.

Watch Video – Excel Formatting Shortcuts

If you prefer reading instead of the video, here you go!

5 Powerful Excel Formatting Shortcuts

I often get a lot of data download from colleagues or files from databases that have no formatting to it look raw and ugly.

My immediate next step is to apply some basic formatting so that it can further be used.

Here are my top five Excel formatting shortcuts:

  • CONTROL + B: Applies bold font formatting to headers
  • ALT + H + B + A: Applies borders to the cells
  • ALT + H + B + T: Gives an outline border to the dataset
  • ALT + H + O + W: Autofits column widths
  • CONTROL + 1: Opens Format Cells dialog box

Read on to learn more about these Excel formatting shortcuts.

Applying Bold Font Formatting to Headers

This formatting shortcut comes in handy when you have headers, and you want to highlight it by making the font bold.

Use the following keyboard shortcut:

Control + B (hold the Control key and then press B).

Similarly, you can also use similar shortcuts such as Control I to italicize and Control-U to apply the underline format.

Applying Border to the Selected Range

You’ll be surprised to see how better the data/report looks when you give it a border. This simple formatting transforms the data from raw data dump to something that looks more like a refined report.

Use the following keyboard shortcut to apply the border to the selected data set:

ALT + H + B + A (press the ALT key followed by H and B and A).

While the default border color is black, you can change the color Home –> Font –> Line Color.

Applying OUTLINE Border to the Selected Range

If you have multiple tables/sections in the same workbook, you may want to apply a thick outline border that will separate these different sections.

For example, you may want to separate data for different regions in the same dataset.

Use the following keyboard shortcut applies a thick outline border to the selected range of cells

ALT + H + B + T  (press the ALT key followed by H and B and T)

If you want to apply a border to all the cells and the thick border as the outline, first use the shortcut ALT HBA and then use ALT HBT (and not the other way round).

Autofit Column Width

It’s quite irritating to copy and paste some data in Excel and see the text/data in the cell going out of the cells. This happens when the text/data in a cell needs more space that the existing column width.

You can use the following keyboard shortcut to autofit the content so that column width would change to accommodate all the contents.

ALT + H + O + W (press the ALT key followed by H and O and W)

This shortcut will change the column width to accommodate the cell with most text.

Opening Format Cells Dialogue Box

While this shortcut would apply any formatting to your data, it will open the Format Cells dialog box, which is a gateway to many important formatting options (such as Number Formatting, Alignment, Font, Border, and Color Fill).

Use the following keyboard shortcut to open the Format Cells dialog box:

Control + 1 (hold the control key and then press 1)

Excel Formatting Shortcuts - Format Cells

So now when you get a raw data dump, just use these quick Excel formatting shortcuts, and you will have a more refined and better-looking data in seconds.

Note: If you have a formatted data set and need to replicate the same formatting for another data set, using Format Painter would be the right way to go.

I hope you find these helpful.

Do share your favorite keyboard shortcuts with me in the comments section.

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

7 thoughts on “5 Powerful Excel Formatting Shortcuts That Will Transform Your Data”

  1. Great shortcuts!! Those are the ones I use the most!!

    I also use:
    Alt + H + W to wrap the content of a cell
    Ctrl + Shift + ~ to apply general format
    Ctrl + Shift + $ to apply currency format
    Ctrl + Shift + % to apply Percentage format
    Ctrl + Shift + # to apply date format
    Alt + H + E + F to remove all formats
    Alt + H + B + N to remove borders
    Alt + H + AC to align center
    Alt + H + AM to align middle

    Keep up the good work Sumit!

    Reply
  2. CTRL + Arrow Keys :- One can use Ctrl + Arrow keys to go to the last continuous blank or non-blank cell in that direction. For example-
    In any excel One is currently on cell A1 and presses “Ctrl + down arrow key“ , then he will reach to the end of the Column A because from cell A1 till cell A1048576 there are all continuous blanks.
    To go back to cell A1 from end of column A in this case, one will press- “Ctrl + Up Arrow Key”.

    Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster