How to use pivot table to create Summary

In this article we will learn how to add pivot table in excel sheet and how to use it to create a summary of data.

Step1: We will pick a excel file with some sample data, click here to download

Step2: Select any active cell from data set and go to insert tab :

Step3: Click on Pivot table option as shown below

Step4: Select range for pivot table as shown below-

Step5 Select new worksheet for PivotTable report

Step6: Now select he field as per requirement, for example here I want to see the number of topics completed by Suman.
P1- Name I have added in in Rows section and P1- status into summation section.

Basic summary table is ready now, we will cover more in next article about Pivot table.

[Resolved]How to handle divided by 0 error in Excel Formula

[Resolved]How to handle divided by 0 error in Excel Formula

In our daily life we use divide formula very often in excel. One common issue we get while using it “#DIV/0!” specially if the value of divisor not known while writing formula.

Let us see the above issue with an example:

download example for more details:


Sl No Value1 Value2 Formula Result Explanation
1 A2=5 B2=5 =A2/B2 1 5 divided by 5 result 1
2 A3=5 B3=0 =A3/B3 #DIV/0! 5 divieded by 0 giving exception “#DIV/0!”

Solutions to this is very simple. Use your division inside IF statement.

  • IF Function
    A brief introduction to IF statement before we use it for above issue:-

IF function have three section as IF(logical Test ,value if true,value if false)

As explain in above line if the logical test will pass 2nd section value will be the output else 3rd section will be the output.

For above scenario whenever divisor is ‘0’ I want output as “NA”, you may opt for any digit or a sentence as per your requirement.
Please refer the example sheet for more  deatails:


Sl No Value1 Value2 Formula Result Explanation
3 5 0 =IF(C4=0,”NA”,B4/C4) NA “NA” comes as an output when divisor’s(c4) value is 0.

How to Use INDEX+MATCH With Multiple Criteria.

index match excel

post we will provide solution to the below listed questions:

1.How to Use INDEX+MATCH With Multiple Criteria.
2.How to Lookup a value With Multiple Criteria.
3.How to find the row number in excel with multiple match Criteria.

Lookup Table
Company Id Location Turnover Rating
100 India 1 AA
101 US 3 BB
102 UK 1 AA
103 India 2 A+
104 Japan 1 AAA
105 India 2 BBB

Problem Statement: Lookup the value of rating from Lookup table where
1.Company Id=100
2.Location= India
3.Turnover=1

Solution: You can download example sheet for details.

Index+Match as an array

Final Function will be {=INDEX(D3:D8,MATCH(E13 & F13 & G13,A3:A8 & B3:B8 & C3:C8,0))} and it will return AA

Let us see it in details:

This is very common requirement when you are working on data analysis or you are creating some automated script or if you are trying create some dashboard for financial calculations.

Before Reading this article, I am assuming visitors are aware of INDEX, MATCH and VLOOKUP.

I will use INDEX and MATCH combination to solve our problem. Let us discuss briefly these function before we use it:

INDEX function’s syntax- INDEX(array, row_num, [column_num])

If both row_num and column_num parameters are used, the INDEX function returns the value in the cell at the intersection of the specified row and column.

Example:

Lookup Table
Company Id Location Turnover Rating
100 India 1 AA
101 US 3 BB
102 UK 1 AA
103 India 2 A+
104 Japan 1 AAA
105 India 2 BBB

INDEX(A3:C8,3,1) will return 102.

MATCH function‘s syntax- MATCH(lookup_value, lookup_array, [match_type])

The Excel MATCH function searches for a lookup value in a range of cells, and returns the relative position of that value in the range.Match function excel

Now, lets combine Index and Match function-INDEX(D3:D8,MATCH(101,A3:A8,0)).

Match function will return the row where match found and Index will return the corresponding value.

Index+Match excel funtion

INDEX(D3:D8,MATCH(101,A3:A8,0)) will return BB from above table.

Problem Statement: Lookup the value of rating from Lookup table where
1.Company Id=101
2.Location= India
3.Turnover=3

Type INDEX(D3:D8,MATCH(E13 & F13 & G13,A3:A8 & B3:B8 & C3:C8,0)) and press Ctrl + Shift + Enter.

Final Function will be {=INDEX(D3:D8,MATCH(E13 & F13 & G13,A3:A8 & B3:B8 & C3:C8,0))} and it will return AA

Index+Match as an array