Pivot Table and Slicer in Excel

In Excel, A Pivot Table is a table of summary of massive or big data table. In this summary you can include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.

Let's learn it in below steps...

1. How to Insert Pivot table in excel

(Click on Image to see)

2. How to add Rows and Columns in Pivot table

(Click on Image to see)

3. How to insert Slicer in Pivot table

(Click on image to see)

4. How to turn cell range into table and use slicer on it.

(Click on image to see)

5. How to use Slicer for Dynamic Chart.

(Click on image to see)

6. How to change Pivot Table Layout.

(Click on image to see)

I hope you have learn to use Table, Pivot Table and Slicer to make dynamic data in Excel.

Daily useful Excel FORMULA

There are many small and day to day useful formula in excel for smart solutions. below are the list of them. more are coming soon...

(Click on Image to see)

Comment which one you like the most.

Useful Excel Shortcut and Short hands

Below are useful and handy excel shortcut which can save your time by regularly practicing it while working on Excel.

Short hands: You have to press keys one by one, not together.

ALT  O  C  A    =    Auto fit Row with content

ALT  H  A  C    =    Central Align in cell

ALT  H  B  A    =    Apply All Border in selected cell

ALT  H  B  N    =    Remove All Border in selected cell

ALT  H  M  M    =    Merge selected cell

ALT  H  A  C    =    Central Align in cell

ALT H V V       =    Paste as Value

F4                    =     Lock row or column in formula

F12                  =    Open Save As dialogue box

F7                    =     Spell check

F2                    =      Edit selected cell

Shortcuts: You have to press keys together.

CTRL  D         =    Copy content from above cell

CTRL  R         =    Copy content from right side cell

CTRL SHIFT L =   Apply / Remove filter in selected row

CTRL 1          =     Format cell window

CTRL  F        =      Find window

CTRL H        =       Replace window

CTRL PGUP or PGDN = Switching between sheets

CTRL Arrow key  = Go to last cell of data or Table

CTRL Shift Arrow key  = Select upto last cell of data or Table

Shift Space   =        Select Row

CTRL Space   =      Select Column

ALT    =            =    Auto sum

CTRL  T            =   Convert Range into Table

CTRL  ;            =   Insert today's date

CTRL SHIFT  ; = Insert Time

Which Shortcut you like the most? share in comment.

Combine VLOOKUP and MATCH formula and see the MAGIC !!!

    

    In previous post, we have learnt how to use VLOOKUP, but when you are required to fight with massive data with lots of columns and row then it will be time consume to use VLOOKUP.

But in that case, when we add one more small formula in VLOOKUP, It will work like professional database management software, and the ingredient is MATCH formula. You wonder how?  its very easy and can be done in two below steps.

Step. 1

(Click on Image to see)

Step. 2

(Click on Image to see)

Working smooth? Great.

Learn VLOOKUP in 40 Seconds in excel

In Excel, sometime we need to fetch data from table with some criteria. for that you can use many methods. Vlookup is one of the most popular of them.

In this post, we will learn Vlookup in just less than minute by following image.

(Click on Image to see)

Usage of Excel Status bar instead of basic formulas

Hello Friends,

    Welcome to Gyaan of Excel. Many a times in excel, we need to have quick look on our data or number's sum or average or count or minimum or maximum value without putting much efforts or formula on sheet. we can do it it quickly by following simple steps.

1. Right click on excel status bar and check all shown options

(Click on Image to see)

2. Just by selecting data in you sheet you can see all the calculation on bottom status bar.

(Click on Image to see)

I hope you like this small and silly trick of excel. stay tuned and keep learning with Gyaan of Excel.