Dynamic Pivot Table | Dynamic Range for a Pivot Table using the Offset Function 

I conduct Corporate and personal Excel and VBA Training classes but MUST appreciate your style of teaching which is very simple yet POWERFUL. I'm not hesitant to mention that I've learnt and still learning new concepts from your videos and wanted to express my gratitude for sharing your videos. You are a wonderful Teacher and your teaching inputs will go a long way in educating Excel Users and mastering their skills. Thank you so much Sir !!

One of the Best excel Tutor for excel . Thanks for the Video

You are a gifted person, Sir.. May Allah bless upon you and your family..Ameen

Thank you sir for teaching us

Sir ji, good evening,mere computer me pivot dynamic ka offset formula copy karne per define name open nahi ho raha hai.pl.iska solution bata de.

great sir.......... very helpful

Very good sir

Gr8 sir actually u give super trick which I want know to do pratically thanks a lot

jabaradsst sir ji

Thanks 👍 a lot Guru ji

Thank you so much ❤️❤️

Lets jump to statics from Dynamic thanku sir for this video good night sir.

EK EK POINT KA PTA HAI APKO M HERAAN HU SIR

Thats really a great approach, I am learning daily new things, you are just mind blowing sir ji

Isme hi yadi koi chart 📊 banaye Or koi row ya column bhi offset formula ki maddat se add kar dete to kya chart update ho jayega refresh karne par q

you r osm

Hi sir, but how to update pivot table in same sheet where source data is changed.

I have one issue when I am deleting any row in raw sheet then pivot table not refreshing

Appreciate your teaching style sir. It was very difficult for me before but. As I join you my concept very easy and clear very well because of you sir 🙏🏻

What need to do if data start from different column and row

How to arrange 2 differnt series in one colom..such 1,2,3,4,5. Other number 201,202,203,204,205 I want arrange 1,201,2,202,3,203....

Sir, i have tried this and the formula is working. But when I try to use this in my macro, it doesn't work. Please make a video on that as i need to use this formula frequently but in different files every time. If i could use this using macro it will help me a lot. Thank you

Offset Formula par video banaye sir please

Sir, कोई formula या trick से productivity को dashboard पर show करना सिखाए, जैसे मुझे दिनभर के लिए 480 मिनट मिलते हैं काम करने के लिए और मैं 370 मिनट ही काम कर पाया तो दिनभर में कितना percent काम किया, इससे related productivity report dashboard, target report की तरह बनाना सिखाए l Please 🌹राधे राधे 🌹ARK 🔥🏹हमेसा हंसते मुस्कुराते रहिये l

Reference Isnt valid ka error aara Hai.. Kab source data Mai Mai change kr ra hu naam... Plesse suggest q aara hoga??

Sir, dash board banane mein multiple pivot table banana hota hai...tho kya multiple dynamic pivot table banana hoga ya ...single dynamic formula se ho Jayega...pls confirm

Sir I have question for u - 1st time jab pivot lagaya tha or data source main ja kar humne anmol ko bhi select kiya tha to kuu na hum puri excel sheet ko hi select krle? 🤔 3:13 pe table/range main puri sheet ka hi range lelo fir to add ho Jaega ?

Very interesting. While I like the offset, the only issue is if i have 2 or more data source one below the other. In that case table is a better option.

Table m convert krne m issue kya h smjh m nahi aaya sir....sahi to h table m convert krna.

Sir, Can I get Analyze tab in windows 2007 ultimate version?

Sir, you have 'Define name' for offset formula then using the name in 'change data source', but can we use directly the formula in change data source?

Thank You Sir 🙌 learning so much from you about Excel

Hi - Pivot table wali sheet ko protect karain to values auto refresh nai hoti jab tak sheet unprotect na ki jae. Kya iska koi solution hae? Kay protected pivot sheet ki values auto refresh hoti rahain jab Raw data change ho?? Pleae Rply

Sir change data source me jab dynamic likh rahe hai uske baad enter press kar rahe hai to error aa rha hai reference is not valid

Is there a way, where I do not have to refresh individual table. This DYnamic is working, but I have 10 pivots with same base data

Sair puri complete pivot table ka ek hi video bnaye awr strting se samjhaye

Sir pivot table 2nd sheet pe banao phir offset ka formula laga ke batao

Please provide a list of engineering document Deliverable Register and give us a lectures / make a video with pivot table

This pivot table filed name . To creat pivot table repor you must use organized as list with labled columan This is not valid I checked 2 time spelling but error is sams

Sir ji ek problem ka solution krdo .... Bde data pe formula eksath lgane pe hang ho jata hai immediate kyu nhi Formula lgta ji 🙏

Sir Offset formula work kerra h but Data source range mai pick nhi kerra

Sir, when I change the data source, the pivot table disappears. Why is it?

Sir mere pass ek google sheet me auto downloaded data hai. Jis se mujhe ek summary report banana hai:- kitne store ke kitne logo ne ek particular date me kitne bills banaye. Kaun kaunse store ka data nahi aaya and ek alag file se same summary report me Lana hai ki kitne total state wise store hai and kitne store wise staff hai. Pls help kare

Hi Sir, After copy the formula define name is showing greyed out. What would be th error. And how I will do it ?

In the last step when i was adding in change data source writing dynamicpivot so result is coming invalid reference Why ??

Isme hi yadi koi chart 📊 banaye Or koi row ya column bhi offset formula ki maddat se add kar dete to kya chart update ho jayega refresh karne par

Hi sir. I'm finding error refrence is not valid. I have tried so many ways of changing the xl name, same error appearing

I done it. while entering data in source file, pivot table can not be update value. Now i make it dynamically.

Sir mecro regard karte time pivot table kaise banaye ki dynamic bame??

can also convert the database into table.. so that we can do dynamic pivot..right?

Hello sir, I tried to create the dynamic range via offset but when i am trying to add the dynamic range in the change data source for the pivot table , it is not allowing me to add as showing me the error reference is not valid. plz help me on this error.

Agar ek hi pivot me yani issi me dusri sheet ka data add Krna ho to kese karein sir ... Thanks for your teaching support Sir

Isame ek issue hai. Aap jab bhi blank template banaoge to ye kam nahi karega. Usake liye kam se kam 2 row me data hona hi chahiye.

When doing refresh, showing the pivottable field is not valid

Sir, main name define karne ke baad pivot table me change. Data,sourse did save , masage ata hai refrence is not valid.

Sir meri ek query he jese hum same sheet me raw data se pivot banate he or fir pivot se same sheet me reports ke liya data fill karna hota how to automate the same... Please help in same sir...

Hi, Thanks for the video. I have a doubt here. We have to download a large dataset set from ERP which is the data source and then we modify the downloaded dataset by deleting some data, thus a new dataset generates, This becomes the new source data and we have to work on this newly created dataset. So while creating the pivot table what will be my Datasource the second one or the first one.

Very tuff formula using give me simple easy concept

sir konse version se ye formula use kr sakte h ? 2013 mai kaam nhi kr raha ye formula

Dynamic pivot name dene ke bad ok kiya to massage arha hai li name pivot table name is not valid kya kru

Sir I watch you videos a lot please help me to solve one query of mine suppose we have order I'd item 2222. Jam 2222. Bread 2323. Orange Result would be 2222. Jam, bread 2323 orange And so on please help with the formula

5 pivot table ko jab ek refresh macro bnakr refresh krte h to wo bahut time leta h refresh hone me fir chahe ek hi data new add hua ho... Is speed ko kese improve kr sakte hai...

want to avoid hide cells, not want to taken hide rows/columns data in pivot table, is it possible ?

good morning sir sir which is best dynamic or wstatic

I really appreciate your work. When I apply the same formula on the existing sheet . It display the entire date on the same sheet. And after copying the formula. When I go to DEFINE NAME IN formula menu then DEFINE NAME IS disable. Please help me in that.

I'm from bangladesh really nice explanation, many many thanks..............@

Formula ke last main jo freeze kia... Kaise? Kon sa key se hoga

Sir, how to make and manage pivot table on mobile phones, please.

I tried different sheets the same offset formula, but not working

Gajab

I have been referring so many videos to learn excel but accidentally came across your video,now am thankful to that one random video clip which changed my life,your way of teaching is so simple and easy to understand each and every concepts...keep going in this way sir...many people will be very helpful with your videos...Thank you so much sir and God belss you..

This function only working when we used in same sheet

Agar data table me convert krte he to apne aap change hota he pivot table bhi

Reference is not valid.... Kyu aa rha hai mujh lgta hai online sheet pr hi kaam krta Hai

Very nice 👍 sir ji

Sir can we make pivot table with formula ?

sir same sheet pe to work kr rha h offset bt pivot alg sheet pe create kre tb work nhi kr rha

Sir, offset formula put karne par puri range aa rahi hai, value error nhi show ho raha h

Thank you Sir. I found it easiest way

unable to copy and past the formula in define name

"Super Sir"

वीडियो का सर मुझे एबीसी कॉलम में एक नंबर कॉलम में जो डाटा है उसे जब भी रिफ्रेश किया जाए वो टेबल में ऐड हो जाए यह कैसे किया जाए प्लीज एक्सप्लेन क्योंकि मुझे ऐड नहीं करना है हमेशा उससे रिफ्रेश करने के बाद वन बाय वन रो me जाएhelp

Sir it is fetching date aa blank in pivot, what can we do?

Sir mere pass ek error a rha h (Reference isn't valid) please help

window 10 me analize ka option nhi aa rha kaise kre

“Define name” was not highlighted

In office 2019 it is getting updated automatically. we need not give offset formula with dynamic range... I practiced it today..

Let me say, you are awsome 🙂Appreicate your efforts to share the knowledge. Dynamic Pivot it wonderful and very useful in my reporting

Sir ji Isme hum Refresh ke liye VBA code use kr sakte hai Worksheet > Change (VBA CODE)- Activeworksheet. RefreshAll

Excellent 👍

Thanks for your efforts . I have one question regarding pivot table. I have one dynamic pivot table and i had applied vlookup to pivot tabel coulmns , but i want to automatically update vlookup formula when i added any other record in dynamic pivot table coulmn. Pl. help me for this.

Sir, i think ur marketing is not good enough, otherwise ur visitors or viewers should be much more higher than present, as ur training style, ur presentation is above the expectations... 💥💥💥💥💥

Dear sir, please prepare a tutorial about power BI with link Primavera P6

Sir cube ka formula bhi btay

Sir when I put tablename in change data source I am getting an error i.e refrrance name is not valid

instead of NameRange better to Convert Source to Table Format

If We make the current simple data set and change it to excel table with using CTRL + T It will definitely update the pivot table

Agar sir range me puri sheet kr ke v kr sakte hai

Very useful video, i appreciate your true efforts for this wonderful lesson with example., which helps end user to gain Faster.🎉

sir plz,,meri hath jodkar nivedhan krta hu,, pivot table ki starting kaha se krni hogi ,, plz lesson wise bnayiye ,, esse new students ko jase muje bhi smj nhi aayega,, plz ,, me bahut main baat ki gujarish kr rha hu