Pivot Table Excel

Pivot Table:


Pivot Table is one of the most powerful tools of excel. Infact, justice to pivot tables cannot be done by using more words however, it is one of "the more you sink yourself, the more you challenge yourself" kind of a tool.
It is a tool which helps a user in analyzing and summarizing data and presenting it in a customized way. The USP (Unique Selling Point) of Pivot tables is that one can select a considerable data from huge data sets in performing various activities like (business) analytics, business intelligence, reporting etc.

To give an overview, once data has been selected, one needs to choose the option of Insert (in the ribbon) and then Pivot table (given at top left). Once we set up the Pivot table, we can simply drag and drop fields in various options given at the right which otherwise would have been by use of different formulae.
Therefore, Pivot table not only gives a deep consideration to aesthetics but also saves a millenia by avoiding to type the formulae in analyzing data sets.

Read More

Hlookup Function/ Hlookup Formula with Example

Hlookup Formula with example:

An advice - if one asserts to be an excel user and is sincere in his claims, then you just can't get without saying - "i don't know the lookup family in excel".
Vlookup and hlookup are the lynchpins of matching data. The alphabets "v" and "h" stand for vertical (lookup) and horizontal (lookup) respectively.
Talking about hlookup in this section, its use is less when compared to vlookup since data sets have columns arranged vertically, however, if one has read my first line, one just cannot abandon it.

The syntax for hlookup is - (lookup_value,table_array,row_index_num,[range_lookup]). To avoid you from scrtaching heads, please look below to decipher what the syntax means -
lookupvalue - value that needs to be searched in the first row
table_array - cell ran
ge  or name of the table that contains both the value to look up and the related value to return
row_index_num - serial number of the row whose value you want to return
[range_lookup] - An optional logical argument which is either TRUE or False;
TRUE - for an exact match
FALSE - for an appropriate match

HLOOKUP Example:

We have a data of sales of four regions months wise and we want the sale value in B2 cell of East Region in February 2014. Write [=HLOOKUP(B1,$B$5:$E$7,3,FALSE)] formula in B2 Cell and we can find the East Region sale value of February Month 2014 from the Sales data.

Read More

Offset Function/ Offset Formula Excel

Offset:

If the hlookup and vlookup belong to one family then one can say Match and Offset belong to the other one.

Offset is a pretty simple formula to be used in excel. However, as a suggestion from my end, one needs to use it in small data workbooks else, it becomes pretty tricky.

The syntax and its description is given below -

Offset(reference, rows, cols, [height], [width]) where,

reference - is a (output) cell or a range of adjacent cells from which you want to base the offset

rows - stands for no. of rows one wants to move up or down from the reference cell. +ve value denotes moving down and vice-versa

columns - stands for no. of coolumns one wants to move right or left from the reference cell. +ve value denotes moving right and vice-versa

[height] - which is optional and represents the number of rows selected. it has to be a positive value.

[width] - which is optional and represents the number of columns selected. it has to be a positive value.

Read More

Index Function/ Index Formul Excel

Index:


One propensitates towards complacency after learning the lookups, match, Sumif, Countif etc. because all these are day to day functions and one feels good about himself especially after he/she is tending towards being a rising star in the eyes of his reporting boss.
However, to get the respect which the "Halley's comet" gets, one needs to think out-of-the-box kinds so that he/she can also become like the force of attraction despite the sky being filled with beautiful stars.
To achieve that "Halley's commet" status - learn Index in Excel. It is one of those which can startle you and many others.
It works like any other data retrieving function but it has lot to offer beyond its main function as it can be combined with other functions in excel. There are two sytanxes for an index function which are explained below -


1)INDEX(array, row_number, [column_number]) where,
array - specific range of cells (or can be an entire table)
row_number - the value of the row number that one is wanting to retrieve
[column_number] - which is optional and is the value of the column number that one is wanting to retrieve.

2)INDEX(reference, row_number, [column_number], [area_number]) where,
reference - which means a range that needs to be specified
row_number - the value of the row number within the range that one is wanting to retrieve
[column_number] - which is optional and is the value of the column number within the range that one is wanting to retrieve.
[area_number] - which is optional and is the range that needs to be used from the reference parameter

Read More

If Function/ If Formula Excel

If:
"Ifs" and "Buts" only call the satan and by using If, you can think of all satanically conditional work that can be done in excel.
It is simple and easy to use and the output is all the more pleasing. So let me hurry up with the syntax -
if(logical_test, [value_if_true], [value_if_false]) where,
logical_test - which is nothing but the condition

[value_if_true] - is optional and returns the decided value when the condition is met
[value_if_false]  - is optional and returns the decided value when the condition is not met  

Read More

Match Function/ Match Formula Excel

Match:

Supposedly one is in a real hurry ( i think this needs not to be "supposed" as we have already been made to swallow that time = money), one needs to find the relative position of specific item in a speific range in excel.
In such cases, Match comes into play which is pretty self - explanatory and easy to understand.
The syntax and its description is given below -

Match(lookup_value, lookup_array, [match_type]) where,
lookup_value - is the specific item one is looking for
lookup_array - the specific range of excel
[match_type] - which is optional and gives the value as per type selected which can be either exact match or an appoximate match.

Read More

How to find duplicate words or value in a cell in excel with example.

How to find duplicate words in a cell in excel with example?

To find a duplicate value or word in a cell in excel we can use IF function with SUBSTITUTE function. Now suppose a data contains addresses and we want to know either that data contains duplicate word in a cell or not, so we will use IF with SUBSTITUTE.
We have a file which contain some data of State, City and Addresses.

We wants to find is there any duplicate city name exist in address column or not.

Now in cell D2 we will type formula as =IF(SUBSTITUTE(C2,B2,"",2)=C2,"","Dups") and fill the formula till the end of the data.

In D column there is remark "Dups" for the addresses which contain city name twice.

Explaination:- In Formula (=IF(SUBSTITUTE(C2,B2,"",2)=C2,"","Dups")), First the SUBSTITUTE function replace the city name (city name on the second position, will not replace city name which is on first place because in SUBSTITUTE function we provide the Instance No as 2) which is in city column (B Column) and if substitute the result will match with C column data it means no changes will occur means no city name is on second place in address data but if the substitute result will not match with C column data it means the city name on second position is exist. So if substitute result will not match with C column data there is a remark dups will be filled in D column else it will remain blank.

Read More