Use of Pivot Table In Excel

Create Dynamic Pivot Tables with this Expert Tip

A powerful feature of Microsoft Excel that I encourage everyone to check out is the Pivot Table. This element of Excel allows you to get great insights into your data.
One thing that confuses people though is when they add to their source data but the Pivot Table does not reflect the changes.

Creating a Dynamic Pivot Table

The trick is to create a “dynamic named range”. Rather than just add your table in the usual way, you need to create your pivot table using the named range, then you can add data, refresh, and the new data will automatically show up.

Creating the Named Range

To create your named range in Excel 2007 go to Formulas > Define Name





You will need to supply a name for the range, for example “Data”.


In the Refers To box, enter an Offset formula. This defines the range size in the following way:

==OFFSET(Sheet1!$A$1,0,0, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

• Reference cell: Data!$A$1
• Rows to offset: 0
• Columns to offset: 0
• Number of Rows: COUNTA(Data!$A:$A)
• Number of Columns: 4

Create the Pivot Table using Your Named Range

Now you need to create the pivot:

1. Choose Insert > PivotTable
   
   
2. Select Table/Range
3. For the range, type your range name, e.g. Data
4. Click OK
5. Continue creating the pivot table as you normally would …
   
   
6. Click OK

 I Hope after using this usefull trick in excel you will be able to use whenever you have such a this type of requirment.

 

!!!!HAPPY LEARNING!!!!

Use of Conditional Formating With COUNTIF Function

Today I am going to share with understanding the Comparison Logic COUNTIF with Conditional Formating, How you can find your Matched and Unmathced data data in two Columns with condition formating.

For Example:- Lets assume your data looks like this:

 

In your data range as per above you want to find Duplicate and Unique value and  with conditional formating. If your Select values in first list (assuming the values are in B21:B29).

I have used here Formulas>Define Name  Tab here for giving a Range Name(B21:b29) to lst1.

Same option I have used for giving a Range Name (C21:C28) to lst2.

 Also, you should know how to use COUNTIF Excel Formula

So in order to find-out if a value is in list 1 only, we use a formula like =COUNTIF(lst2,value)=0.

This function will check whether “value” occurs anywhere in lst2 and returns false if that is the case.  (it assumes that value is already in lst1).

Highlighting Items that are in First List Only 

Go to conditional formatting > add rule (Use COUNTIF Function and Set Your Formating as per mentioned Step:-

 Select the rule type as “formula”

 Write a rule like this: =COUNTIF(lst2, B21)=0

 Double check the reference and make sure it is relative (and not like $B$21). Select the reference and press  F4 repeatedly to change it to relative reference.

Set the formatting you want.

It will highlighting Items that are in Second List Only:-

 

 Select values in second list (assuming the values are in C21:C28)

Go to conditional formatting

 Select the rule type as “formula”

 Write a rule like this: =COUNTIF(lst1, C21)=0

 Repeat steps  as  per above. 

Highlighting Values in Both Lists:

Now, it gets interesting as you should apply conditional formatting individually to both lists.

Select values in first list (assuming the values are in B21:B29).

Set the conditional formatting rule as =COUNTIF(lst2,B21)>0

Apply formatting as you want.

Now select second list (assuming the values are in C21:C28)

Set the conditional formatting rule as =COUNTIF(lst1,C21)>0

Again, apply formatting as you want.

Hope you learned something out of this trick.  "HAPPY LEARNING"

Use of vlookup With-wild-card-example

Dear All,

For Example. In the below data, we may not remember the full name of sales person, but we know that her/his name starts with jac. Now how do you get the sales amount for that person?

Data:

Solution

Simple. Use wild cards. Like this: =VLOOKUP("jac*",$B$5:$E$17,3,FALSE) to fetch the value from 3rd column for the person whose name starts with jac

Examples:

Hope you learned something out of this trick.  "HAPPY LEARNING"