Excel Hacks - Part I

Whether you are required to use graphing to represent statistical data, or for data organisation and analysis, or for managing office operations, you need Microsoft Excel for your work. Through our Excel Hacks blog series, we shall try to help you learn some time-saving shortcuts that can help you get things done faster and more efficiently.

Check Out: Excel Club

1. Excel Smart Drag Down

All of us use the convenient drag down function to continue a number series, month series, etc. We also use the custom list function to add the series of our choice and other commonly used series in our Excel workbook. But it is noticed that when we write the number 1 and try to drag down, it does not continue the number series. 

The easiest way to start a number series is to press the CTRL key and drag down. For the month series, if you press the CTRL key and drag down, it will NOT start the month series but will drag down the month typed by you in the cell. The logic is reversed for month and day series and number series.

Reference Video:

2. Blank Cells

Normally, in large data sets given to us for analysis or report generation, there are a few blank cells that do not contain data. The conventional method is to select the entire row and apply filters to either remove the blank cells or fill in the blank cells.

An easy method to mark these blank cells is:

Select the entire data -> Press F5 -> Special -> Select Blanks -> Press ok -> Fill colour under Home Tab

Reference Video:

3. Repeat Function

This function is often used by all of us. You can use this function in 3 ways:

a. F4 

b. Ctrl + Y 

c. You can add the repeat function in the Quick Access Toolbar

To add the repeat function in the Quick Access Toolbar, follow these steps:

Go to Quick Access Toolbar -> Select the dropdown menu -> More Commands -> Under Commands, Select Repeat -> Add -> Ok

The video below shows the various utilities of this function. 

Check it out:

4. Finding and Highlighting cells

An extension of finding blank cells sometimes is that the data given to us will have few cells that are not valid—that is, those cells will be VOID. An easy way to find which of these cells are void is by following these steps:

Ctrl + F (Find) -> Find what: void -> Check Match entire cells contents -> Find All -> Select the first Cell detail -> Press Shift -> Select the last cell detail -> Highlight cells/ Replace cell

Reference Video:

Hope you find this blog helpful. If you have any suggestions, help us learn more by dropping your valuable comments in the comments section below. If you have any doubt, feel free to communicate with us.