Dollar ($) symbols are used to freeze a Row or Column or Cell or Range in side the formulas. Based on freezing cell references can be broadly classified into two types VIZ 1.Absolute Reference 2. Relative reference.

What these references mean it?

Absolute = Fixed

Relative = Changing

When you copy and paste absolute reference formula to other cells, exact replica of the formula will be copied. In case of relative references, cell references will be changed.

If you put $ symbol both in front of  a Column and Row number that is called Absolute Reference e.g. $A$10 . Inside a formula when you don’t use $ symbol for cell references it is called Relative reference.

What is the necessity of these references?

Tweet This 

There are many ways to prepare debtors aging report in Excel. Among all of these, the below method is considered to be the best.

Step1: Arrange your data into Tabular form and name that sheet as “Data”

Tweet This 

The most frequently used formula in Excel is Vlookup. Every body thinks that they know fully about it but very few knows. Now we will walk through step by step.

What is Vlookup?

Vlookup mean Verticle Lookup. There are two types of tables VIZ. Verticle table and Horizontal Table. See the pic. If your data is in Verticle table format we use Vlookup otherwise Hlookup.

What is the Syntax for Vlookup?

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) . Article title is on this range_lookup argument.  See the below Picture.

Tweet This 

Array formulas are the most powerful formulas in the Excel. But one should know when to apply or not apply these array formulas

1.What is the necessity of  Array Formlas?

Say, you have data in range A2:A10. Now you want to add all the values in this range what will you do? simply press =SUM(A2:A10)

Suppose you want to multiply each cell value in the range A2:A10 with 2 before summing up what should you do? or Say you want to multiply Range A2:A10 with range B2:B10 before summing up what shall you do? To do this you have two options:

Tweet This 

For tax consultants who wants to calculate income tax here is the formula. In income tax usally tax rates are in slab system as shown below:

Now how to calculate tax of a person whose Net Income is 8,00,000 within a single cell?

Tweet This 

What is sumproduct?

Sum of the products of the the given ranges. Plese see the below picture.

When we will use sumproduct formula?

In general business scenario for calculating weighted average rate (WAR). Hold on i will tell you different ways to use this.

What is the syntax for the sumproduct formula?

See the above picture.

What is that Microsoft documentation doesn’t tell about Sumproduct formula?

Tweet This 

To create smart tags without writing programming code follow the below steps:

Smart Tag

1.Download this file by right clicking  smart ekanna

2.Save this file in the path C:\Program Files\Common Files\Microsoft Shared\Smart Tag\LISTS\1033

3.Open MS-Excel and go to Tools–>AutoCorrectOptions–>SmartTags–> Tick on Smart ekanna as shown in the picture. In Excel 2007 press Alt+F+I+Proofing+AutoCorrectOptions

Tweet This