Formatting data is one of the most important skills in Excel. It is the easiest way to enhance data usability. By using Excel formatting shortcuts, one can easily save time while working on large datasets.
In this guide, we are learning the top twenty of the best Excel formatting shortcuts you must know.
In this tutorial:
Top 20+ Excel Formatting Shortcuts
Source data that is taken for further processing in Excel is most of the time is often plain and raw with no formatting applied.
Before we start processing data in Excel, it’s a good practice to format the data. This is where Excel shortcuts come really handy to apply basic formatting swiftly.
Among hundreds of Excel keyboard shortcuts, here are my top 20 Excel formatting shortcuts to quickly format your data:
- CTRL + SHIFT + 1: Apply number format
- CTRL + SHIFT + 2: Apply time format
- CTRL + SHIFT + 3: Apply date format
- CTRL + SHIFT + 4: Apply currency format
- CTRL + SHIFT + 5: Apply percentage format
- CTRL + SHIFT + 7: Apply outline border to cell or selected cells
- CTRL + SHIFT + 9: Unhides hidden rows within selection
- CTRL + SHIFT + 0: Unhides hidden columns within selection
- CTRL + SHIFT + _: Removes outline border from cell or selected cells
- CTRL + 1: Opens format cell dialogue box
- CTRL + 2: Apply/Remove Bold formatting
- CTRL + 3: Apply/Remove Italic formatting
- CTRL + 4: Apply/Remove Underline formatting
- CTRL + 9: Hides selected row
- CTRL + 0: Hides selected column
- ALT + H + B: Apply or remove border to the active cell or range of cells
- ALT + H + O + U + S: Hides Excel worksheet
- ALT + H + O + U + H: Unhides Excel worksheet
- ALT + H + O + R: Renames worksheet
- ALT + H + O + T: Change worksheet Tab Color
- ALT + H + O + A: auto adjust or autofit row height
- ALT + H + O + I: auto adjust or autofit column width
- ALT + H + E + F: Removes all kinds formatting from the cell
Let’s learn how to use these Excel formatting shortcuts with examples in detail.
Open format cell dialogue box
CTRL + 1
Press and HOLD CTRL key then press 1 or ! key
::
Opens format cell dialogue box
This Excel shortcut opens the format cell dialogue box which is basically a portal access everything related to formatting in Excel. This includes number formatting, font selection, cell border and alignment and fill color and a lot more. Each formatting category i.e. Font, Border etc is divided in different tabs and you can access related options by visiting the relevant tab.
Bonus Tip:
By pressing CTRL+SHIFT+F you can open the format cell dialogue box with font tab open. This way, you can access font formatting options straightaway without the need of switching tabs.
Excel text formatting shortcuts
Excel offers several shortcut keys that performs basic formatting tasks without the need to open format dialogue box. For example, applying bold or italic styling or switching number format from general to data or percentage, following are some of the useful Excel shortcuts with examples:
CTRL + 2
Press and HOLD CTRL key then press 2 or @ key
::
Apply/Remove Bold formatting
This shortcut applies bold formatting to the cell value. Alternatively, you can use CTRL+B shortcut to perform a similar function. To remove bold formatting you need to press the combination again.
CTRL + 3
Press and HOLD CTRL key then press 3 or # key
::
Apply/Remove Italic formatting
This shortcut will italicize the contents of the selected cell. You can also use the CTRL+I shortcut to apply italic formatting to the cell. To remove the italic formatting simply hit the shortcut again.
CTRL + 4
Press and HOLD CTRL key then press 4 or $ key
::
Apply/Remove Underline formatting
This will underline the contents of the cell. Alternatively, you can use CTRL+U to apply or remove underline formatting from the selected cell or range of cells.
Excel shortcuts for Excel rows and columns
CTRL + 9
Press and HOLD CTRL key then press 9 or ( key
::
Hides selected row in Excel
This shortcut will hide the active row i.e. that had an active cell or all the rows of the selected range of cells.
CTRL + SHIFT + 9
Press and HOLD CTRL and SHIFT keys then press 9 or ( key
::
Unhides hidden rows within selection
To unhide the row, simply select the cells of rows above and below the hidden row and press CTRL+SHIFT+9 to unhide the row or rows in Excel. You don’t necessarily have to select the entire rows, a cell from the above and a cell from the row below the hidden row will do the trick.
CTRL + 0
Press and HOLD CTRL key then press
::
Hides selected column
Unwanted columns like helper columns or the ones with underlying calculations are often not required in the final presentation. To hide a column or columns simply have an active cell in the column you want to hide and hit CTRL+0.
If you have adjacent columns then select the cells of those columns you want to hide and hit the shortcut key.
And if you want to hide nonadjacent columns then select the cells from each of those columns by holding down the CTRL key on the keyboard and hitting CTRL+0 to hide the columns.
CTRL + SHIFT + 0
Press and HOLD CTRL key then press
::
Unhides hidden columns within the selection
Just like we learned for rows, simply select the columns before and after the hidden one and hit the shortcut combo to unhide them. Letting Excel know which area you want to unhide is important otherwise this will not work.
ALT + H + O + A
Press ALT, H, O and A successively
::
Auto adjusts or autofits row height according to cell content
ALT + H + O + I
Press ALT , H, O and I successively
::
Auto adjsuts or autofits column width according to cell content
Column width if not appropriate can cause several issues. With part of the content hidden or spilling over to the following cells, its quite annoying. And in some cases, you can get a hash error in Excel if the column isn’t wide enough.
To easily fix this issue Excel has this shortcut automatically adjust or autofit the column width. Excel will make the adjustment by changing the column width according to the widest content in the column or selected data range.
Also, if the column is unnecessarily wide, Excel will reduce the column width to the appropriate size as per the widest content in the column.
Apply Border to the selected range of cells
To make data ranges distinguishable from each other, we apply borders around each of the data ranges to make an outline. This makes them more legible as we can easily see the start and end of the data range. And to highlight different sets of each range we can have border lines inside the data ranges as well. Here is one example:
To access all the options Excel offers to apply border in different styles, you need to remember only one shortcut:
ALT + H + B
Press ALT, H and B keys successively
::
Access all the styles and options of cell borders in Excel
This shortcut will lead you to all the options under home tab. And to apply a particular style, you simply need to press the associated key.
For example, the Excel shortcut key to apply a thick outline border around the selected data range is ALT + H + B + T.
Similarly, the Excel shortcut to remove the outline border from the selected data range is ALT + H + B + N.
Excel shortcuts to format number
In Excel, we mostly deal with numbers. But not all numbers are the same. To distinguish between different types of numerical data, formatting the numbers appropriately is very important.
Following are the keyboard shortcuts for number formatting in Excel:
CTRL + SHIFT + 1
Press and HOLD CTRL and SHIFT keys then press 1 or ! key
::
Applies number format with two decimal places, thousandth separator in form of comma and minus (-) sign for negative for numbers
This shortcut comes especially handy while preparing reports that require figures to be two decimal places correct. Also, the addition of the thousandth separator makes numbers easy to read.
CTRL + SHIFT + 2
Press and HOLD CTRL and SHIFT keys then press 2 or @ key
Formats the numbers with hours and minutes expressed in 12 hours format with AM or PM
CTRL + SHIFT + 3
Press and HOLD CTRL and SHIFT keys then press 3 or # key
::
Apply date format with day, month and year
CTRL + SHIFT + 4
Press and HOLD CTRL and SHIFT keys then press 4 or $ key
::
Apply currency format with figures expressed correct to two decimal places and negative figures in round brackets
CTRL + SHIFT + 5
Press and HOLD CTRL and SHIFT keys then press 5 or % key
::
Apply percentage format with figures expressed in whole numbers i.e. without decimals
Excel worksheet shortcuts
ALT + H + O + U + S
::
Hide active worksheet in Excel
No right-clicking the worksheet tab and then pressing hide from the menu. Simply use ALT+H+O+U+S to hide the active worksheet instantly.
ALT + H + O + U + H
::
Unhide already hidden Excel worksheet
This Excel shortcut opens the “Unhide sheet” dialogue box that contains all the worksheets that are hidden in the active workbook.
This shortcut comes extremely handy if you have to hide and unhide worksheets several times when using the mouse disrupts the workflow and is often time tiring as well as you have to reach another input device.
ALT + H + O + R
::
Rename Excel worksheet
Instead of using the mouse to double click on the worksheet tab to rename the worksheets, you can press ALT+H+O+R to straightaway edit the worksheet name. A definite time-saver!
ALT + H + O + T
::
Change tab color of excel worksheet
With several worksheets to work with inside a single Excel workbook. Giving each tab its unique color is a good practice. One can easily identify the worksheets and makes workflow smooth. We can also give particular color to a set of worksheets containing a particular type of data.
With this Excel shortcut, you can define the worksheet tab color quickly. Have a look:
Remove or Delete Cell Formatting
ALT + H + E + F
::
Clear Formats from the selected range of cells
With this nifty shortcut key, we can clear or delete any kind of formatting applied to the selected cells.
Once formatting is removed, the contents of the cell will be displayed in the default font and font size with the general cell format applied. Everything else from color to alignment, borders, font style, etc will be stripped off.
So this is the list of awesome Excel shortcuts from my side.
Hope you find these useful.
Do check the following Excel tutorials to learn more tips and tricks:
