Excel Level-Up: Day 4

Rows and Columns

In a lot of time, we have to play around with rows/columns, for example, insert new columns, hide rows or delete rows.

Today, you will learn how to do all of the above without touching your mouse.

Level 1: Selection

To select a row, press [Shift] + [Spacebar]

To select a column, press [Control] + [Spacebar]

Level 2: Insert

To insert a new row, select a row (refer to Selection), then press [Control] + [Shift] + [+]. It will add a new row above the selected row.

To insert a new column, select a column (refer to Selection), then press [Control] + [Shift] + [+]. It will add a new column to the left of the selected column.

Level 3:

To delete a row or column, select the row/column (refer to Selection), then press [Right Click Button] then [D].

Level 4:

To hide a row or column, select the row/column (refer to Selection), then press [Right Click Button] then [H].

Go back to previous awesome trick: Excel Level-Up: Day 3

Proceed to next awesome trick: Excel Level-Up: Day 5

Excel Level-Up: Day 3

Advance Copy & Paste

I’m sure you are pretty good in copy and paste, i.e. [Control] + [C] and [Control] + [V].

Today, we are going to show you a more sophisticated technique of pasting.

You might have known the “Paste Special” function in the Excel but do you know that it can be done without using mouse?

The trick is very simple, pressing [Alt], then [E], then [S] will prompt the “Paste Special” dialogue out.

From here, you can choose the option that it has by simply press the corresponding letter underlined in each function.

For example, to paste special as value, press [Alt] [E] [S] [V] (one at a time).

To paste special as formatting only, press [Alt] [E] [S] [T]

To paste special as formula only, press [Alt] [E] [S] [F]

Same apply to all functions.

Now, are you excited to use this trick to level up your pasting skills?

Go back to previous awesome trick: Excel Level-Up: Day 2

Proceed to next awesome trick: Excel Level-Up: Day 4

Excel Level-Up: Day 2

Dragging Formula

Okay, now you can navigate within a worksheet using just keyboard. So, what’s next?

Do you have the problem of having to drag some cells in order to duplicate formula?

Well, that is too slow and troublesome.

To copy formula downwards: [Control] + [D]

Let’s say you wanted to sum Column A, B,C, D in Column E. Sum the first cell. Select Column E. Press [Control] + [D]

To copy formula to the right: [Control] + [R]

On the other case, let’s say you want to apply the summation to the right. Select the all the relevant cells. Make sure the left-most cell contains the formula that you wanted to duplicate. 

Press [Control] + [R].

 Done.

These two formulas will help you duplicate formula without hassle to the right and down.


Go back to previous awesome trick: Excel Level-Up: Day 1
Proceed to next awesome trick: Excel Level-Up: Day 3

Excel Level-Up: Day 1

Navigation by Keyboard

To work fast in Excel, you must not rely too much on your mouse. Learn to navigate in Excel using keyboard will save your time tremendously. It could be difficult in the beginning, but it will certainly be paid off in long run. The following are some useful shortcuts for navigation.

Level 0: To move to the next cell, I’m sure you know we will be using the [Up], [Down], [Left] and [Right] keys.

Level 1: Slightly harder, use [Control] + [Up] / [Down] / [Left] / [Right] to move to the edge of the data range. This is particularly useful if you are constantly working with big lists or tables.

Imagine you have a table with 25 columns and 10,000 rows.

How do you move from the top of the list to the bottom of the list?

Scroll down?

Nope.

Use [Control] +[Down]

How do you move from the first column to the last column?

Press [Right] continuously.

Nope.

Use [Control] + [Right]

Level 2: If you would like to select cells, use [Shift] + [Up] / [Down] / [Left] / [Right]. No more dragging hassle in the future!

Level 3: Even slightly harder, now if you combine [Control] and [Shift], you can select cells faster. For example,

If you want to select the whole row of a table: [Control] + [Shift] + [Right]

If you want to select a column of a table: [Control] + [Shift] + [Down]

Try applying these tricks in your work today! You might not see the results immediately as it require some practice, but I assure you that your colleague will be impressed. 

Go back to the intro Excel Level-Up: Introduction !
Proceed to the next awesome trick: Excel Level-Up: Day 2

Excel Level-Up: Introduction

On your first day working, you are assigned to summarize data or generate some graphs using Excel. You look around and realize how other colleagues work in lightning speed but you are struggling to set the axis of your graph to the right unit. You are so impressed how your seniors can draw a table in Excel within 3 seconds. You start grumbling on how ineffective the education system is. How could Excel not be a compulsory subject in school? How do your super colleagues do it?

Of course, experience plays a vital role in sharpening one skill. But surely there are some tricks that can be learnt in short time that will save you from looking dumb.

In the following 10 days, we will show you some Excel tricks that will definitely make you looked pro in Excel.

Stay tuned for  Excel Level-Up: Day 1 tomorrow to start impressing!