Lets excel in Excel - Funda # 1

Have you ever searched on google for the shortcuts in excel or useful excel tips/ functions? Was it actually useful? I have also made such searches many a times, but found out that such searches are particularly helpful when we are looking for some particular function/ feature of excel. Else, too many results are returned, actually confusing the person which one to take a note of. Another challenge faced is being appreciative of its practical applicability.

So, through this series of posts, my effort shall be provide insight into some of the useful (basic as well as advanced level) excel functions/ commands etc with their practical usage in financial analysis work.

Some of the functions might be too basic, so if at anytime you feel that you already know it, please skip to the next post, you might find something useful.

Funda # 1: Zero filing

Zero filing refers to replacing the blank cells, or the cells where no value exist, with ‘0’ or ‘-‘ or any other relevant character. This is particularly useful for the reasons stated below:

1. Completeness, so as to ensure blank cells are intentional; and
2. Better presentation.

Let’s try this with an example below:

We have break of salary and wages in the table below. Now, I have highlighted the blank cells in the table. No value exists for these cells in FY14 and FY15. One way is to let it be the way it is as total value shall not be affected, or do ‘zero filling’ in these cells.

Step 1: Select the range of cells where zero filling is required to be made.

Step 2: Press F5 (for Go To), then select ‘Special’. A window with various options shall appear (refer screenshot below). Select ‘Blanks’.

Step 3: Clicking on ‘blanks’ shall select all the blank cells in the range. Once the blanks cells are selected (refer screenshot below), press ‘0’. Don’t press enter at this stage, just enter ‘0’.

Step 4: Finally press, ‘Ctrl + enter’ and the value (here ‘0’) shall be copied to all cells.

Step 4 (continued): Sometimes, we need to replace the values with ‘n/a’. In that case, rather than entering ‘0’ in step 3, enter ‘n/a’ and press ‘Ctrl + enter’.

Hope this funda will prove useful. Remember, the entire focus of this exercise shall be on 2 things- Accuracy + Time Saving.

Let me know of any comments/ suggestions. Will try to incorporate in my next post.

Cheers!