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

[@SylvesterMarshallPune]

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 !!

[@Innozant]

Thanks dear.Really means a lot to me.

[@partipalsingh175]

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.

[@suvendujana3841]

Same problem

[@Ann_2208]

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 🙏🏻

[@rahul4238]

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....

[@ashishjuneja5745]

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

[@Priyanshu9q]

Bro check that if your macro doesn't add @ operator in formula because macro often add @ in array formulas to treat them as text and if you found the solution for this then please tell me also I also need that

[@rjraviraftarup7553]

Offset Formula par video banaye sir please

[@Innozant]

Apko bata chuka hu dear ,Course main step by step kra Ra hu na ki kahin se Kuch bhi video..Choose function finished then indirect uske baad bus offset hi aana hai ..HV patience 🙏♥️

[@rjraviraftarup7553]

@@Innozant okay sir Thank-you

[@arunkumarsinghark3024]

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

[@dipanshu7005]

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

[@chartkuchkehtahai]

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

[@falakshabir4258]

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 ?

[@Innozant]

Bilkul hi jayega..lekin chote dataset par to koi issue nhi hai but large amount of data hoga to?apka excle dam tod dega dear😀

[@falakshabir4258]

@@Innozant hahahha okay sir I will try fir ik or comment krk question puchunga- waise apki videos bht aachi or useful hoti hai sir - Abhi new subscriber hu apke channel ka ♥️🙂

[@Innozant]

Phir start se Seekhiye.. Playlist Announcement me jaiye wwahan ek vidoe hai mis learning process use dekhiye aur usi sequence me complete course kriye step by step

[@achalrajput4173]

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

[@sanjoypaul1256]

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

[@Nitin3mittal]

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?

[@sagarneel5867]

Thank You Sir 🙌 learning so much from you about Excel

[@TheWaqas7896]

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

[@Amitthedancer941]

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

[@sonukumar-xp5sd]

you r osm

[@biplobmondal201]

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

[@aftaballam3990]

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

[@Amitthedancer941]

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

[@notrllysmol962]

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

[@joshisonu2000]

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

[@uttamkumar-lr4zu]

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

[@kashishkataria3447]

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

[@PNRLakshmisilks-f2n]

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

[@bikky4431]

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

[@satyapanda3116]

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

[@Proteinbites2]

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

[@لاغالبَإلاالله-ح4ذ]

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

[@rudramani.s930]

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

[@DEADYBOYS]

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

[@becool4706]

I want that if different words come in a cell then its result should be a different word Like I have 4 teams with 4 team leaders, I want that if a team member's name appears in a cell, then the team leader's name appears in the selected cell.

[@لاغالبَإلاالله-ح4ذ]

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

[@ahmadfaraz9283]

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

[@gamehub8068]

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

[@SUNILKUMAR-vw3qs]

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

[@nehaaggarwal1618]

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.

[@uttamdhananja4908]

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

[@rahulrose2]

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.

[@iswarmittal7607]

When doing refresh, showing the pivottable field is not valid

[@fatehsingh485]

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

[@ransh7046]

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...

[@ayanintern4207]

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.

[@latestpunjabisongs2839]

If you clean your data before creating pivot table then yes your 2nd table would be used as Datasource, Ideally first we clean any data then we go ahead and create pivot out of it.

[@kishordhotre5752]

Very tuff formula using give me simple easy concept

[@iqbalimran39]

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

[@beingcreative4548]

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

[@sehgal_sheet_tricks3306]

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

[@prashantsamariya3138]

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...

[@debasischatterjee1328]

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

[@pknewknowledge6629]

good morning sir sir which is best dynamic or wstatic

[@DmiHindi]

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.

[@citynirmandevelopersltd.7249]

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

[@Innozant]

Thanks Nirman

[@samirkumarnandagoswami459]

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

[@spdas5942]

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

[@qaisermuhammad4708]

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

[@soumyanair9704]

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..

[@Innozant]

Thanks Saumya for your valuable appreciation,Really means a lot to me.Have a wonderful career ahead.Keep Learning and Keep exploring.

[@bhimkunwor3894]

This function only working when we used in same sheet

[@lukeshbhirud1583]

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

[@archieb1196]

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

[@rahulmahor191]

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

[@Innozant]

Nhi..aapne naming range sahi nhi le rakha hai.case sensitive issue check kriye

[@PratapSinghSoam]

Very nice 👍 sir ji

[@Ram_Ram]

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

[@devbhumi7072]

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

[@sangeetagusain5197]

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

[@NiwantGadgil]

Thank you Sir. I found it easiest way

[@mamidiparameshwar279]

unable to copy and past the formula in define name

[@geniusclasses1]

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

[@kamalsinghrawat3219]

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

[@sachinKumar-ow9sl]

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

[@Liitlebitly5458]

window 10 me analize ka option nhi aa rha kaise kre

[@shvetaanandarora6182]

“Define name” was not highlighted

[@RameshChandra-rs9eo]

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

[@Innozant]

👍👍❤️

[@pankajverma19811]

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

[@Innozant]

Thanks Verma ji for your valuable appreciation..Happy Learning ❤️

[@rjraviraftarup7553]

Excellent 👍

[@gargibhattacheryya4386]

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

[@saralachaudhari9903]

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.

[@Innozant]

Ok .Kindly share your screen shot @innozantofficial telegram id with detail explanation..will guide u

[@umaghosh1282]

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... 💥💥💥💥💥

[@Innozant]

We never did marketing or paid promotion on KZbin or other platforms. Have a wonderful career Uma👍❤️

[@syedumai8315]

Sir cube ka formula bhi btay

[@excelprofessional8505]

instead of NameRange better to Convert Source to Table Format

[@Innozant]

Do you feel converting range into table is good or bad option 😊Think and reply that why i don't convert it into table when though I knw that converting into table is easy option

[@Arjun-Shah-b2r]

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

[@bawabawa1004]

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

[@ajitrai1566]

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

[@Innozant]

Thanks dear

[@preethandal6136]

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

[@Innozant]

Dear ..already playlist bana hua hai pivot table ki jisme in sequence videos hain..aap koi bhi series Playlist wise dekhiye aur seekhie Videos in sequence playlist (video description me bhi rakha hai link)

[@ghanashyamc]

Did the formula and defined name but when selecting the defined name in step 3 I'm getting "reference isn't valid error". Can you help?

[@Innozant]

Case sensitive issue...again check the naming range

[@amitsonavane6195]

Sir, You are really Good Par Sir mujhe ek query hai ki agar dynamic pivot table wali file ko copy ya rename kar diya to pivot ka link nahi carry forward hota Iske bareme kuch batao kaise hum pivot wali file ko copy ya rename kare aur pivot ka link bhi naye file salamat mile.

[@amitsonavane6195]

Sir Please iska answer karo

[@abhivlogs1570]

Thank very much brother ❤

[@muhammdliyaqatali7352]

What is difference between Dynamic Pivot Table and Auto Refresh Pivot Table.....Thanking for Anticipation

[@Ashutosh-dl7cg]

Very helpful Sir, dynamic pivot bahut he easy concept se bataya thanks you so much, Sir

[@Innozant]

Thanks Ashutosh ..Keep Learning..Keep exploring ❤️

[@vikasgawaria5330]

Sir SQL and python pe basic to advanced complete tutorial build kar dijiye please

[@Innozant]

Ok .one by one bnta rhega series

[@dhanpatisahu6429]

Alt+F5 refresh

[@soumensaha4426]

Sir I have tried to do dynamik pivot as per your video but I am unable to do it. It shows some message. Can I send screenshot? Please help me.

[@Innozant]

@innozantofficial telegram id par share kar sakte hain apni query

[@gauravbhatia7987]

Very good sir

[@Innozant]

Thanks Gaurav Ji👍❤️🙏

[@navy4456]

it is very helpful learning

[@shivanigusain8026]

How this will work if data is on other tab?

[@ManjeetSingh-th4fu]

Very useful information thanks. Sir, SQL ka series kb aayega?

[@ManjeetSingh-th4fu]

Sir, SQL ka video kab aayega?

[@pranshushakya8067]

1. Created Master Data Sheet Using Your Guided Method In My Current Organization Working As Data Analyst Thank You 2. Work Flow Is Optimised And Automated. You Are Amazing Sir. #Respect

[@rajushah0522]

Great Sir ! it's working nice

[@syedumai8315]

Asalam o alecom sir mujhay cube ka formula skhna bht zarori hay sir ap say request hay cube ka formula Btay plz reply

[@Innozant]

Ok..bna denge cube par ek video

[@sarkarhere]

I am having an issue. After writing the formula, the formula is not not getting dragged to other cell, there is a blue highlight rectangle shows up covering the range

[@mohammedshamim9568]

Thank you sir.. amazing.