Important formatting and editing shortcuts in MS Excel

Shortcut Keys	Description
Ctrl + Shift + ~/$/3/%/!	Allows you to change the formats as follows
Ctrl + Shift + ` (or ~ button)	Excel Default format (general number)
Ctrl + Shift + 1 (or ! button)	Comma (Thousand, million separator) and Decimal format
Ctrl + Shift + 3 (or # button)	Date format (dd-mm-yy)
CTRL + Shift + Home	(Currency format)
Ctrl + Shift + 5 (or % button)	(Percentage format)
Hiding and unhiding Rows and Columns
CTRL+ 9/CTRL + shift + 9		Hide/Unhide the row
CTRL+ 0/CTRL + shift + 9		Hide/Unhide the column
Drawing and removing outline borders, quickly
CTRL + Shift + &		Makes outline border for selected cells
CTRL + Shift + _		Removes all the lines, outline boarders in your selection
CTRL + D/R		Fills cells Down/Right

Have you noticed that you get ‘#VALUE!’ error when you work with dates in Excel? Or sometimes you are performing date based calculations and you get numbers! Or sometimes you have to quickly get a percentage format or change % format into the regular Excel default format.

Formatting is necessary to showcase or present the data in the intended manner. Without formatting the information generated through the data may be misconstrued. An example of formatting is changing the format of the value in a cell from a general number (78406) to the comma and decimal format (784,406.00). This is needed when we want to show a huge value precisely, like in the case of a company’s balance sheet.

Often we find formatting a headache due to the efforts we have to put in. Formatting shortcuts come handy for us here as hitting few keys only will do the necessary job.

An Example for understanding the shortcuts for changing the format

Let’s try to understand with the help of an example. Suppose we have imported some data from another file as shown below. The data consists of the current market capitalization of 5 firms. 

As we can see above, the values are given in two different formats. We wish to have these values in currency format. So, first of all we will select all the values and then use the shortcut Ctrl + Shift + 4 

As we can see above, the values are in their correct format now with commas & decimals. In this way we can change any data in the desired format using shortcuts. 

Hiding and unhiding Rows and Columns

Upon using CTRL + 9 the row, in which the selected cell is located, disappears. It is a straightforward action. However, unhiding the same row later requires little more knowledge than just knowing the shortcut. To unhide the row you first need to select the rows next to the hidden row. Then use the shortcut (CTRL + Shift + 9) to reveal the hidden row. The same theory applies to the hidden column. 

Shortcut for filling cells Down/Right 

Now Ctrl +D/R, a shortcut to fill cells Down/Right is also a very useful shortcut as it saves 50% of time in a copy-paste operation (Ctrl + C and Ctrl + V). This is how it works!

a. Select the area where you wish to paste the content (destination area) starting with source cell. Then press Ctrl + D or Ctrl + R

b. If you don’t select the destination area and just press Ctrl + D in the source cell, it will copy and paste the details in the cell immediately below.

c. Note that it fills the cells with content and formatting (cell comments are not copied)

Now that you have learnt the important formatting skills, it is time to teach your co-workers too how to format a file in short time easily. It will eventually ease your burden only, isn’t it?

Our next article will be the final article in the series of shortcuts for Excel and will help you make the use of shortcuts easier.