 PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

## INTRODUCTION

EXCEL provides us with a vast variety of options to do almost whatever we wish to do concerned with our data analysis. And if, by chance, something is not available, we have the tools with the help of which , we can do that easily.
Similarly a very simple but challenging situation occurs when we need some leading zeroes before a number in EXCEL.
In this article we’ll discuss how to sort out this problem easy and fast.
We’ll discuss different ways to apply leading zero to Text as well as Number.

There is nothing to be done if we need to add zero to the text.

Simply, add the zero anywhere in the field and it would sustain. If somehow there is any problem, follow the following steps.

• Select the cell containing text.
• Right Click the text and choose FORMAT CELLS.
• Choose TEXT format for the cell.
• We can also choose TEXT FORMAT from the HOME TAB directly.

Adding leading zero to a number is the tricky one for many of us.

Suppose we have enter the bank account number. some bank account numbers have leading zero

or

We want to create a series and want to have that all the number have same number of significant digits.

In both of the cases we are in trouble.

#### WHERE IS THE PROBLEM??

The Excel removes all the leading zero from the numbers which we type in Excel. So it becomes a problem for us.

#### SOLUTION

We have many solutions for this problem. We’ll discuss the main two of them. But the solution will depend on the

nature of the requirement.

#### SOLUTION 1:IF THE NUMBER NEED NOT TO DO ANY MATHEMATICAL TASK

If the number, which needs the leading zero, need not to be involved in any type of mathematical task, which means

that we are not going to apply any formula on that number, we can simply change the format of the cell to TEXT.

STEPS:

Select the cell containing text.

#### SOLUTION 2:IF THE NUMBER NEEDS TO BEHAVE LIKE A NUMBER ONLY

A situation may arise when we need the leading zero and still number needs to be a part of the function or formula, it needs to behave like a number only.
The SOLUTION 1 won’t work for this as if we convert the format to TEXT, it won’t be able to participate in a function or formula.
Here we’ll make use of the custom format.
CUSTOM FORMAT IS THE FINAL DESTINATION FOR ALL FORMAT RELATED PROBLEMS.
STEPS
• Right click the cell (which needs the format change) and click format cells.
• The format dialog box will open.
• Go to the bottom and choose CUSTOM.
• Click on GENERAL and delete it and enter 000… and look on the top of the dialog box, where sample is shown.
• Stop when sample shows the format you want.
• Click ok

## finding last USED ROW in a SHEET

Let us find out the last used row in a column i.e. the row which contains the last data in a row. We can take the leads from the above examples of finding the last used cell in a column.

## SOLUTION 1-find last USED ROW IN A SHEET

Let us try to check the cells of a column and find out the last filled cell in a column. Usually this kind of task is easily done in VBA but we will still try in Excel. We’ll take help of LOOKUP function in this as this is a very basic function for looking up a value in a given table and it runs throughout the range.

Suppose We have the following data in a column.

 RANGE 1 3 23 4 54 5 6

• ## WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

• EXCEL
• JAVASCRIPT
• MORE TO COME…

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]