2 MIN Super Funda Trick 2 - Find, Select & Replace

Hello Everyone. In this blog you will learn how to replace blanks in the whole sheet.

Many a times you get tables with several blank cells and you are required to perform various functions in it like SUM, AVERAGE, MAX, MIN etc. These blank cells cause problems in calculating the formulas and functions.

To overcome this problem, I will teach you a simple trick with which you can replace all the blanks in one go.

Suppose you are given this table and asked to find the weekly sum of each softdrink.

When you apply AUTOSUM at the end of the "Coke" row, excel will select the last three cells only because the blank in between breaks the continuity in the table. This problem will be faced in each row.

To solve this problem you have to put "0" into these blank cells but doing it manually in each blank cell of the excel sheet is not possible as it would waste a lot of your time.

In order to replace these blanks in one go, firstly select the entire table.

Then press FN F5

In the window that opens, go to "Special"

Then select "Blanks" and click OK

You will see that all the blank cells are highlighted and the first blank cell is selected.

Now type "0" into this first cell and press CTRL ENTER

There is desired value in all the blank cells now and calculation of various formulas and functions can be easily done.

This 2 min trick can be of huge help as it saves valuable time and makes your tedious task very enjoyable.

For a detailed explanation watch this video and subscribe the channel for more such tricks.

6 views0 comments

Recent Posts

See All