Excel OFFSET Function DYNAMIC Range | How to use Excel Offset Function for Dynamic Calculations

Excel OFFSET Function DYNAMIC Range | How to use Excel Offset Function for Dynamic Calculations
Download the Practice Workbook Direct Link
drive.google.c...
Download the Assignment Master Practice Folder here (All Playlist Examples at one place)
drive.google.c...
Music: www.bensound.com
SYNTAX of OFFSET Formula =OFFSET( reference, rows, columns, [height], [width] )
Parameters or Arguments
Reference - A range from where you want to start the offset formula
Rows - Which row you want to fetch. This can be a positive or negative number.
Columns - Which column you want to fetch This can be a positive or negative number.
Height - Optional. It is the number of rows that you want the returned range to be.
Width - Optional. It is the number of columns that you want the returned range to be.
===========VIDEO COURSE============================
✅ Mastering Excel Video Course
www.innozant.c...
✅ Mastering Power BI Video Course
www.innozant.c...
✅ Mastering VBA Video Course
www.innozant.c...
✅ Mastering SQL Video Course
www.innozant.c...
Visit - innozant.com
=====================================================
===========HYBRID CLASSES============================
✅ आरंभ EXCEL | सक्षम Analytics | निपुण Analytics
✅ Database Training(SQL , PL\SQL , Oracle Developer)
✅ Mastering Data Science
Visit - innozant.com
====================================================
Videos in a Sequence -Basic to Advanced EXCEL
Basic Excel- • Basic Excel-The Learni...
Advanced Excel Sorting and Filter- • Advanced Excel Sorting...
Subtotal TTC and Data Validation- • Excel Subtotal TTC and...
Excel What if Analysis- • Excel What if Analysis
Excel Array and Sumproduct- • Excel Array & Sumprodu...
Excel Lookup Functions- • Excel Lookup Functions...
Excel Pivot Tables- • Excel Pivot Tables
Excel Power Pivot- • Excel Power Pivot
Excel Power Query- • Excel Power Query
Excel Chart and Infographics- • Excel Chart and Infogr...
Excel Dashboard & Reports- • Excel Dashboard & Reports
Value add-ons Playlist
ProQuest-Check Point- • Excel ProQuest-Check P...
Career Guide:सिफर से सिखर तक - • Career Guide-सिफर से श...
StudsTalk -The Journey begins- • StudsTalk-The Journey ...
QUIZ Sunday- • EXCEL MIS QUIZ SUNDAY
Excel Tips and Tricks- • Excel Tips & Tricks
MIS Interview Cracker- • MIS Interview Cracker
MIS Job Live Interview :Based on Real Incident- • MIS JOB Live Interview...
Excel Maharaj- • EXCEL MAHARAJ - For Ex...
Microsoft Powerpoint- • Microsoft Powerpoint
Microsoft Word- • Microsoft Word
Office 365- • Office 365
Google Sheets- • Google Sheets
Microsoft Outlook- • Microsoft Outlook
Power BI- • Power BI
Excel VBA- • Excel VBA Macro
TezXL Jata Shorts- • #shorts | जो TRIM के ब...
Announcements- • Announcements
Website - "www.innozant.com"
Let's Connect:
Instagram ► / innozantinfotech
Facebook ► / innozantinfotech
Telegram►t.me/innozanti...
#offsetfunctionexcell#exceloffsetformula#Innozant #offsetdynamicrange#misinterviewquestions

[@remarkablegraphic_designer3668]

I am regular student of ur online class.... Today i can say that You are GOD OF EXCEL

[@Innozant]

Omg...rulaoge kya ab aap😊❤️

[@sreejithsreedharan3581]

​@@Innozantó⁸

[@remarkablegraphic_designer3668]

Best Teacher around the world. Aisi learning and Teacher paise dekar v kavi nhi milnewala kisi ko

[@Innozant]

Thanks Manish ji for valuable appreciation ❤️

[@umairzahid3743]

Sir ap yaqeen krain maine offset function smajhny k liye bohat video dhaikhi magar kuch smajh nahi aya lakin mjhy yaqeen tha k jab ap sikhao gy mjhy zror smajh ajaye ga aur aj qasam sy mujhy clear smajh aagya ye function. Sir you are great live you long life sir. I am very impressed from your way of talking. Thanks sir

[@Innozant]

Thanks Zahid Sahab for your trust and love ,Really means a lot to me,Aapke life me Ye Tutorial value add kar pa Raha hai aur aap seekh rhe hain mere liye ye bbt badi baat h🙏❤

[@monitsharma1988]

I am watching your some video apne itna ache se smjhya ki ab hr video dekhni hogi muje. God Level Excel Seriously sir. I am using offset with match only but is video me dekha ki iska or b way se use ho skta h.😍😍😍😍

[@ashishshiriskar123]

Sir become your fan,🙏🙏🙏🙏 it's very easy way to teaching tough topic...God bless you 🙏🙏🙏🙏

[@Innozant]

Thanks Dear Ashish ji..❤❤🙏🙏Happy learning dear.

[@muhammadsaqlain5162]

Allah apko kamyabi ki bulandi tak leker jaye Ameen, kamal ka trick bataya ap nay infact ap excel maharaj hai.

[@Innozant]

Thanks dear♥️♥️👍

[@Rahulsingh-kr6sx]

Sir you are best teacher for me and thank you sir you have good style teaching 🙏🙏

[@Innozant]

Thanks Rahul ji..Really means a lot to mw

[@pushpak5939]

Aapka explain karne ka tarika mujhe bahut accha laga

[@soumitradas4003]

You are great Sir

[@sanjanasingh682]

Your teaching skills are very nice and understand able .

[@Innozant]

Thanks Sanjana ,Apke ye words mere liye bahut mayne rakhta hai dear.Meri humesha koshish rhegi Kuch best content deliver krne ki..Seekhiye rhiye aur life me aage badhte rhiye ..❤️👍👍👍

[@bharatdewani4751]

The best teacher

[@Innozant]

Thanks Bharat❤️

[@AbhishekPandey-fl9cn]

Concept clear sir..... i like your teaching skills. =SUM(OFFSET(I8,3,COUNTA(I11:P11)-3,1,3))

[@Innozant]

👍❤️

[@AliAkbar-tr2ff]

Kamal hai sir apke videos...

[@Innozant]

❤️👍

[@Unfiltered_ind]

Sir tussi great ho 🖐️

[@sunilchoudhary859]

Sir je jabardast aapk ko Salute hain

[@Innozant]

Thanks Amit ji.. Happy Learning 🙏♥️

[@whitedove6878]

Super Super Excellent you are a very sincere Godly Person

[@Innozant]

Thanks dear.Keep learning 👍❤️

[@anirudhyadav1170]

Sir your way of expressing is osm

[@Innozant]

Thanks ❤️

[@manissery1956]

Very good explanation. You are really an excellent teacher.

[@nhashmi4621]

I love it bro, your style of explanation is unique 👌

[@Innozant]

Thanks

[@binaykumarnayak9114]

Thank you so much Sir. So nice of you.

[@Innozant]

Thanks Binay ji

[@KIRANKUMAR-qy3cy]

Your way of teaching is super sir

[@Innozant]

Thanks Kiran...Happy Learning dear 👍❤️🙏

[@vikishayar7288]

Great explanation

[@harindershah1180]

Very helpful video for students (beginners)

[@Krishankumar-gz2ns]

ans of my excel sheet 272=(=SUM(OFFSET(B3,4,COUNTA(B7:I7)-3,1,3))) thank you sir.....

[@Jitenderkumar-yp1rn]

Concept clear sir..... i like your teaching skills...😍😍😍❤❤❤

[@Innozant]

Thanks Jitender ji❤️🙏GN.. Really means a lot to me

[@Jitenderkumar-yp1rn]

@@Innozant thats why, i always like your videos... Good night sir..😍😍

[@Excelpro75]

I was waiting for this function. Thank you.

[@Innozant]

Ok..seekhiye step by step

[@sairaaslam3292]

Kamal..kamal andaz

[@Innozant]

Thanks Saira.Happy learning dear..

[@yashpatil3375]

Truly exllent sir

[@kanitar786]

Brother u r doing Great work God bless u

[@Innozant]

Thanks Waseem Sahab..We welcome you to experience the taste of Success with Innozant-Seed of success.

[@arijeetkumarsharma226]

Awesome way sir, No Words for this.

[@Innozant]

Thanks Arijeet ji❤️

[@shubhamtyagi7299]

Shi me sir bhagwaan ho aap Excel...

[@mariantonys]

really smart teaching!!!

[@Innozant]

Thanks .Keep learning

[@battlefansshorts8941]

Dhanyavaad sir ❤

[@Innozant]

Thanks Ravi Ji

[@mistermanoj3181]

Thank you

[@remarkablegraphic_designer3668]

As usual best teaching

[@Innozant]

Thanks Manish Ji ❤️👍

[@kinghundred]

very superb sir

[@anooparora2515]

Thanks Sir!

[@msexcelgooglesheet374]

Khoob Bhalo Video, Sir Ji🙏.

[@Innozant]

Thanks ❤️😀🙏

[@Herukamahesh]

Great bro ...TQ so much ❤️

[@Innozant]

Thank you

[@rhashmi1]

Very nice.

[@situsahoo2482]

Naya hatke .and so much needful.. Ek bat sir.jatadhari ji kab aayenge.i am waiting 😀😀❤️❤️

[@Innozant]

Haha..itna Pasand aa gye Jatadhari Ji aapko

[@rajdeepsaha9059]

Great Interpretation

[@Innozant]

Thanks Rajdeep for your valuable comment dear 🙏♥️

[@rajibdebnath6778]

U r impeccable

[@pramodbhavsar4488]

Very nice functions offset and counta combination for dynamic use in database tricks like sum function thank you very much sir I await

[@Innozant]

Thanks Pramod Ji..for Supporrting 👍❤️🙏

[@indranimukherjee8994]

You know magic, Sir.

[@Innozant]

Thanks Indrani ji.Happy learning ❤️

[@MdIlyasShaikh]

Thanks a lot, sir ji.

[@Innozant]

❤️🙏

[@pawankumarpandagre1111]

Good evening sir

[@kailashupadhyay3452]

Very informative. English trainers are making fool on various online learning platform.👍👍👍👍

[@Innozant]

Thanks Kailash ji,Really means a lot to me..Happy learning..God bless you dear

[@satishojha6240]

I understand offset thanks sir 😇😇

[@Innozant]

Thanks Satish Ji..GN♥️🙏

[@ShaibulHoqueMozumder]

Super sir

[@mahabiraggarwal8426]

Please upload a video on "how to dynamically set print area with offset function in Excel"

[@gola0071]

Sir i always confused with offset function the way you explain I think I will never forget this function forever.

[@swetarani2418]

Finally done 😁😁😁😁 after more and more practice 😊☺️😊☺️

[@Innozant]

Gajab kar diya..dekha maine bola tha na us din khud se try kriye kaise nhi hoga..❤❤good job Sweta

[@swetarani2418]

@@Innozant yss sir👍👍

[@bhoopendraraghav9781]

Great sir ...

[@Innozant]

Thanks Bhoopendra ji

[@Meditation5235]

very well explained! Thanks.

[@Innozant]

Thanks Saurabh Ji❤️🙏

[@dibyajyotisaha5493]

Superb 🙏

[@Innozant]

Thanks dear❤️🙏

[@muddassirification]

How to dynamically sum the columns in next blank column, Please teach

[@Rejuanhaque]

Amazing sir

[@Innozant]

Thanks Zoie Raem.. Happy Learning dear..❤️🙏GN

[@sanjanasingh682]

Answer of sum of last 4 column of South East is (230)= sum(offset (B3,5,CountA(B8:I8)-4,1,4)).

[@Innozant]

Bilkul Sahi Sanjana..Ab ready rhiye Offset ko match ke Saath combine krke example Saath hi Advanced Offset ke liye❤️👍ok

[@saddammalik5566]

Superb example 🎉🎉

[@Innozant]

Thanks Saddam Sahab 🙏♥️👍

[@vijaysahal4556]

good 👍🏻👍🏻👍🏻👍🏻👍🏻🔥

[@Innozant]

Thanks Vijay ji❤❤

[@shivkeshkanoujiya2183]

Seriously combination is awesome offset and counta A Only i know offset (in Macros writing) but now i learned in other form also. Thank you Sir Pls share contact details of your if possible because i want to query about SQL and Access

[@Innozant]

Thanks Shivkesh ji.. Happy Learning ❤️🙏You mag share your query on telegram id -@innozantofficial

[@shivkeshkanoujiya2183]

@@Innozant Query means i want to join SQL and Access Course I want to know, do you have any plan for SQL and ACCES course ?

[@Innozant]

Live Classroom Training is Running for Sql ,You may join ,For more INFO Call/Whatsapp-7065069985

[@amitapattanaik1071]

Classic ecplanation

[@Innozant]

Thanks Salil ji...Happ learning

[@adarshgupta6012]

Thank you for your effort sir ♥️

[@Innozant]

Thanks Adarsh Ji..Umeed hai aap Regular Naye videoa ke Saath hi purane topic Ka Practice bhi Kar rhe honge

[@adarshgupta6012]

@@Innozant yes sir, pr mujhe khi v structured way me Excel video nhi mil pa rha tha.. isliye regular nhi tha.. pr ab main aapke channel ko regular follow kr rha hu ...dil se aapka dhanyawad sir.

[@Innozant]

Haan Adarsh Ji yahi sochke Maine channel bhi start kiya ki wo student Jo fees afford nhi kr skte h ya aur issue unhe is platform ke madhyam se Complete course tutorial mil paye🙏❤️

[@rajadey1552]

Sir meine bill gates ka ek interview pada tha jaha unhone likha "If we stop employing foreign engineers especially from India. Then there will be another Microsoft born in India" And apka chennal dekh liya bill gates ji ne sayad un heartattack ho gayga😅😅😅

[@swasi001]

Amazing. Please advise if I am correct. what ever we subtract from counta, is actually the height(of range we extract) in case of sum the rows and is actually the width(of range we extract) in case of sum the columns.

[@gauravbhatia7987]

Thnq sir

[@brkohli5455]

Nice sir

[@Innozant]

🙏🙏

[@pramodbhatt6906]

Lovely

[@Innozant]

Thanks ❤️❤️

[@Unfiltered_ind]

Sir samjh mei aa gaya column.... Column jo hai wo width wala column hai na ki na ki reference wala...

[@Innozant]

❤️👍

[@hamidshafiquebutt3241]

when you select the column what key use to appear $ sign

[@Innozant]

F4

[@zoobihassan]

COUNTA to be erplaced with Col

[@johnnaqi7244]

Sir, What If i select a range in reference? If choosen then how will I count col & rows?

[@shivanisingh-jc7on]

Sir, I have a query regarding date format questions Like we have given unequal date format and we have to make it in one format eg.(12/3/1994,12-3-1994,12-feb-1994,12.3.1994), If i am using ctrl1 and selecting custom option ,every date format is changed as per require format but (12.3.1994) will not change. Plz tell me the reason behind this .

[@sumanjain1224]

Use text to coloumn

[@Unfiltered_ind]

Sir hight mei 3 row toh samjh mei aa gaya but width mei single column nhi aaya ... Ye pta hai ki hight row ki hoti hai width column ki ...

[@uttamkumar-lr4zu]

Can anyone solve my problem i.e. ek sheet-1 (result wali) me dusri sheet-2 (adjacent) se values uthani hai pr duplicate Lookup values hain Kyki sheet-1 me columns me agle wale group ka data Lana hai (jo next duplicate Lookup value ka hoga) mene indirect fxn lgaya pr 3rd duplicate Jo sheet-1 ke 3 column me Lani hai wo nhi aa rhi ... kya offset fxn kam krega ya (note: sheet-1 pe lookup value ke samne 5 columns me next duplicate Lookup ki value le kr ani hai) I can send you Excel sheet to look for real problem

[@brkohli5455]

Trend set Karna shikh

[@pawanverma7220]

Sir Row aur Column Manually Hi Count Karna Padega Kya bade data mai ?

[@Innozant]

Na na...abhi to ye concept seekh rahe Hain dear ...Abhi bht Kuch banki hai offset me...♥️🙏HV patience

[@SunilYadav-jc2ux]

सर youtube पे बहुत से एक्सेल सिखाने वाले चैनल TITTLE और THUMBNAIL येसे लिखते है जैसे एक्सेल नही जादू टोना सिखा रहे है ...😀.

[@Innozant]

Hahaha..You are Right Sunil..Thumbnail dekhke hi Laura hai..Jaado tona start hone walk hai.baad me practice challah hai video ke and are like kar do aur subscribe kar do😜😜😜

[@SunilYadav-jc2ux]

@@Innozant Han sir 😀

[@sameer2532]

Sir FMS or PMS pr video bnaye interview me bahut pucha jata h

[@Innozant]

Haan FMS video bhi layenge pehle main conceptual topic finish ho jaye

[@Unfiltered_ind]

Sir counta place pr hm toh...simple count use kr sakte hai...

[@Innozant]

👍❤️

[@amolmandavkar08]

Nice video I have one question Suppose row contain, roll number,name and attendance Attendance start from 1to 30, But some row start attendance from 8,12,15 After that he left some blank cell, I want to replace blank with A ,how can I get cells are blank after date 8,12,15 and replace A. I have 200 rows

[@Innozant]

Aise Ques se better aap sheet share Kar dijiye query apni ..#innozantofficial telegram id

[@ramanmangat3320]

Nic sir, Sir i have need your's help Plz mnu ae dass sakde o ki basic excel ch ki ki aa jaanda a sir

[@Innozant]

Basic Excel Playlist video dear..kindly watch it.uske baad advance me sorting filter data validation vlookup Pivote table ye sab Hain♥️👍

[@ishutiwari5567]

sir ye offset function me agr hum match function aur use kre to ye aur dynamic bn skta h for example mene c21 and c22 me month and directions ke respectively list bnayi data validation se and offset formula ke row and column ke reference me match function use kiya like this : =OFFSET(A12,MATCH(C22,A13:A18,0),MATCH(C21,B12:H12,0)) isse data validation ki list se jo month and jo direction(east west....) select kroge vo answer milega

[@ishutiwari5567]

and sir apke question ka aur dynamic method se answer ye rha for sum of last 3 rows: =sum(offset(A12,COUNTA(A13:A20),MATCH(C21,B12:H12,0),-3,1)) for sum of last 4 column: =SUM(OFFSET(A12,MATCH(C22,A13:A18,0),COUNTA(B12:J12),1,-4)) again c21 is for months and c22 is for direction(south, east, west ........)

[@Innozant]

Wow..Very Nice Ishu..Next Video offset with Match Ka hi different perspective aayega..Achi baat hai..Good Initiative Ishu🙏♥️

[@Innozant]

Very good..M impressed 👍♥️100 percent aapko samajh Aaya hai offset formula..Ab complex offset ke liye ready haina?

[@ishutiwari5567]

@@Innozant thank you sir 🙏😊

[@ishutiwari5567]

@@Innozant yes sir, im ready for complex offset😊🙏

[@pawanverma7220]

Upset Nahi Only Offset 🔥🔥🔥

[@Innozant]

Thanks Pawan..♥️🙏

[@tejpalsain1507]

Very nice sir jee. सर इसमे counta मेसे 3 ही क्यों माईनस किया गया। क्या यह भी डायनेमिक हो सकता हैं।

[@Innozant]

Phirse dekhiye video...Apne dhyan se nhi Dekha..again repeat it..kyu minus Kiya hai 3 ..kyunki.55 par pahuchne ke liye..

[@tejpalsain1507]

@@Innozant yes sir. क्योंकि हमें 55 पर या लास्ट थ्री पर पहुचना था। Thanks

[@chowdhurysunzidmahbub79]

Sir -3 is not clear to me. how, why and when we will minus?

[@Innozant]

Oho... - 3 isliye kiya kyunki wo 3 value himrn nhi chaiye na id name. Aur ek aurbcell ka value ydi use count kr lega to apna output galat aayega na

[@yugalupadhyay1549]

Sir, there is a doubt after watching this video so many times. Agar hum offset with CountA use krte hain to apne jo formula bataya vo syntax ko support nhi krta. Sytnax is =offset(reference, rows, columns,height,width). If we use it with CountA to hum reference ke baad D column par kaise pahuche. Or agar D column par pahuche to columns k lye jo 3 pass kiya hai to fir vo kis lye coz column par to hum pehle hi pahuch chuke hain. Sir I follow your channel from long time and try to learn excel from your videos. This point is not clear. Please reply sir Regards

[@Innozant]

Why Yugal,Kindly offset concept ke concept ko ek baar phir se dekhiye ..cell reference concey claear hoga aapka

[@puransingh-sx2tk]

Sir aap to excel ka sagar ho jitna nikalo khatam he nahi hota rose kuch naya aa jata hai Hahaha 😂😊👌🏻

[@Innozant]

Omg...my pleasure Puran...Ab rulao kya 😭❤️😀❤️

[@puransingh-sx2tk]

@@Innozant sir innozant vo tufaan hai jo ab majil par ja k he rukega or aap ka gyaan to infinite 🙏 hai

[@Innozant]

Love You dear ❤️🙏 koshish yhi rhega

[@tmniy]

bhai excel 2007 mei bhi possible hai ye? mujhse nahi ho raha

[@Innozant]

Dynamic kyu nhi hoga..dear

[@srikanthpillamari6598]

Bhai, practise sheet ka link chal nahi raha...

[@Innozant]

It's working...aap master link ko access kriye

[@brkohli5455]

Sir

[@pramodbhatt6906]

Hi Sir, I have a question here can I?

[@Innozant]

Sure Dear

[@pramodbhatt6906]

@@Innozant Sir can u provide me your email id so that I can share my data set

[@rajanbhagat1806]

can any one tell how to freeze

[@Innozant]

Using F4

[@SunilYadav-hm6no]

First view i am

[@Innozant]

My pleasure dear 🙏♥️

[@swetarani2418]

Sir mai try kr rii hu niii ho raaa countA ka use se no value # aa raa h🙄🙄

[@Innozant]

Galat Kar rhi hai Sweta😀Try again..dekhna output apke samne ❤💖

[@Arun-hs9gd]

Offset ne bilkul upset nhi Kiya

[@tarjitmalka3978]

⭐⭐⭐⭐⭐ sir plz give my certificate

[@Innozant]

Already sent ..kindly check your mail

[@brkohli5455]

Do

[@ranjeetbisht4529]

Balo par mar gae chore 😂

[@Innozant]

😀😀😀