OFFSET tells Excel to “fetch” a cell location (address) from within a data range. Download the Workbook here:

Get the full course:

Excel OFFSET Function Tutorial Summary:

The offset formula looks like:
=OFFSET(starting point, rows to move, columns to move, height, width)
Here are practical uses of OFFSET:
• Dynamic Calculations: The OFFSET formula can be included in other Excel functions to get dynamic ranges. For example, calculating the average sales of the last 3 months based on a selection
• Flexible Chart Ranges: In conjunction with charts it can be used in the NAME manager to add flexibility to chart data. The OFFSET function behaves like an Excel table where the data range automatically expands and contracts when chart data is updated.
• Dynamic dashboards: It can be used together with Excel’s Form Options to allow for dynamic analysis and reports based on user-selection.
You can tell the OFFSET function to return one cell if you specify height and width as 1. It can return an array of cells when the height and width are greater than 1. Height and/or width CANNOT be zero, because you either need 1 cell or a range of cells. In the illustration above, the height and width are 2.
This video shows how you can use the Excel OFFSET function to calculate the average value for the last 6 months. You will also learn how to use the OFFSET formula to calculate the average value based on the date selected by the user.
(You will see formula combinations such as OFFSET, COUNTA and MATCH functions)
In general Excel OFFSET function is an interesting formula and it’s one that can do so much more than meets the eye. It can used inside many formulas to provide them with flexible ranges instead of fixed ranges. This makes it a perfect candidate for dynamic reports and dashboards.

★ My Online Excel Courses ►

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

EXCEL RESOURCES I Recommend:

Get Office 365:
Microsoft Surface:

GEAR
Camera:
Screen recorder:
Microphone:
Lights:

More resources on my Amazon page:

Let’s connect on social:
Instagram:
Twitter:
LinkedIn:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

Nguồn: https://otrasnoviaxeiro.com/

Xem thêm bài viết: https://otrasnoviaxeiro.com/category/cong-nghe

Leave a Reply

Your email address will not be published. Required fields are marked *

Comments

Mahaboob Hossain December 22, 2019 at 2:20 pm

https://www.youtube.com/watch?v=D3Uc4P5ljn0&t=351s———what is the conditional formatting for hioghlighting cells for different months dear lela???????explain plzzzzz dear

Reply

Dimitris Salogiannis December 22, 2019 at 2:20 pm

Wow! Your channel must be one of a kind in teaching ms excel! Thank you so much!

Reply

Abhilash C December 22, 2019 at 2:20 pm

God sent you from above, Leila. You and your family will have a Beautiful Christmas!

Reply

Benjamin Aragon December 22, 2019 at 2:20 pm

I have watched numerous videos and I am no way an expert. I appreciate the detail and step by step process as well as explanations as to why you do something. This kind of training was never around a few years ago so thank you.

Reply

Sanjay Kumar December 22, 2019 at 2:20 pm

Excellent method of explanation.. Thanks

Reply

rot rose December 22, 2019 at 2:20 pm

Thank you very much, I struggled for offset functions for years, you helped me to get out the fog. Your excel tutorials are simply amazing, inspire us to dig deep and find fun with it. Thank you very much.

Reply

dheeraj sharma December 22, 2019 at 2:20 pm

you r awesome… nice video

Reply

Rotha Yon December 22, 2019 at 2:20 pm

My beloved teacher! Your tutorials are very helpful.

Reply

gus tavo December 22, 2019 at 2:20 pm

Excelente Gracias

Reply

Toumi Djedilat December 22, 2019 at 2:20 pm

Please help:
OFFSET(VLOOKUP(C6,'Equipment ASOF This Week'!$C$10:$P$376,1,FALSE),2,13,1,1) is it possible???

Reply

cường nguyễn tấn December 22, 2019 at 2:20 pm

1 OF THE BEST TEACHERS ON YOUTUBE ^^

Reply

noor yassin Noor December 22, 2019 at 2:20 pm

The most useful channel in youtube

Reply

Milburn Grimes December 22, 2019 at 2:20 pm

Hi, Leila. I received a spreadsheet with the following formulas on 3 consecutive rows: 1) =OFFSET('Room Plan'!D$5,MATCH($F$10,'Room Plan'!D$5:D$96,0)-3,0,1,1), 2) =OFFSET('Room Plan'!D$5,MATCH($F$10,'Room Plan'!D$5:D$96,0)-2,0,1,1), 3) =OFFSET('Room Plan'!D$5,MATCH($F$10,'Room Plan'!D$5:D$96,0)*1,0,1,1). I've been using this spreadsheet to set class and teaching schedules for months now, but I can't find an adequate explanation for last four digits in each. Can you explain these to me in plain English, especially the asterisk (*) in the last formula? I'd like to get more comfortable with these so that I can manipulate them for different purposes without the fear of messing everything up.

Reply

Andrew McDermott December 22, 2019 at 2:20 pm

Great explanation – thank you. 🙂

Reply

Raju Joglekar December 22, 2019 at 2:20 pm

I have tried several times earlier but failed. Now after this video, I am able to use offset correctly. Thanks and gratitude

Reply

puglover1217 December 22, 2019 at 2:20 pm

This video helped me so much at work. Thank you so much!!!

Reply

عبدالواحد فریدی December 22, 2019 at 2:20 pm

Hello Dear… Should I ask or not but I could not find a way to use SUMPRODUCT function with filtered table….can u please suggest any formula …..Thanks in advance.

Reply

Satish Kumar Dommeti December 22, 2019 at 2:20 pm

Seldom used powerful "Offset" function explained very well. It has certainly helped me with one of my simulation EXCEL.

Reply

Sym-john December 22, 2019 at 2:20 pm

Range D1:D30 containing Pass/Fail results and F1:F30 Containing URL. I want to concatenate the unique URL which result is "Pass". I have been trying in different ways but cant solve. Could you please help me or any tips to solve.

Reply

mohammad khojasteh December 22, 2019 at 2:20 pm

thanks for Good explanation

Reply

Taro Suji December 22, 2019 at 2:20 pm

When I use offset function having multiple rows or columns, it only can be used to others function's range as argument . Got it!

Reply

Berns Buenaobra December 22, 2019 at 2:20 pm

Time and time again even at the advent of Python one has to go back to Excel from time to time. Thanks, it was a good refresher actually good for everyone.

Reply

Ravi Kumar December 22, 2019 at 2:20 pm

Is it possible to zoom in when you provide the next demo. That way, one could see things more clearly…. That's for those who are watching your demo session in mobile. Thank you. Overall your session are very worthy and appreciate that.

Reply

Footage December 22, 2019 at 2:20 pm

Awesome.

Reply

Suraj Lahane December 22, 2019 at 2:20 pm

Thank you Leila

Reply

Ashok Nagdev December 22, 2019 at 2:20 pm

Too good Leila , Thanks , you explained it so well

Reply

Mahaboob Hossain December 22, 2019 at 2:20 pm

I like the video dear Leila DAAAAAAArling

Reply

Mahaboob Hossain December 22, 2019 at 2:20 pm

excellent video plzzzzzzzzzzzzzzz send to my e-0mail dear darling Leila plzzzzzzzzzzzzzzzzzzz

Reply

Muahammad Daniyal December 22, 2019 at 2:20 pm

Very nice content and awesome teaching style, pls also start to teach us that "How to teach"..:-)

Reply

Hemanth Kumar December 22, 2019 at 2:20 pm

Poor video quality unable to understand. It gets blurred

Reply

James Anderson December 22, 2019 at 2:20 pm

you are a great teacher!

Reply

Kevin Jackson December 22, 2019 at 2:20 pm

Wow, I can't believed I trusted Udemy more than Youtube. I really could've saved 20 bucks if had chosen to watch your videos. Very Nice Job!

Reply

Rodislav Ivanov December 22, 2019 at 2:20 pm

Hi! Thank you for this video, it helps a lot the understand how this functionality works! I have a table with some rows that are empty, but I still need a dynamic table to use for one of my pivot tables! Could you please suggest me a solution? 🙂 Thank you!

Reply

Njoroge December 22, 2019 at 2:20 pm

Your explanation is awesome.

Reply

Simon Phoenix December 22, 2019 at 2:20 pm

Explaining it so clearly shows how knowledgeable you are on Excel..
A smart and beautiful lady..

Reply

Raphael Jan Cañete Ferraris December 22, 2019 at 2:20 pm

my excel vba goes to ask to debug whenever i left the excel open for a few minutes maybe 20 or more minutes. if i click debug. it goes to show that my home.show is the code that needs debugging.

Reply

Naga Satish December 22, 2019 at 2:20 pm

Please tell how to use CORREL with OFFSET

Reply

Sudhir Hiwale December 22, 2019 at 2:20 pm

What an easy way to explain! Simplicity comes when the knowledge is at its best.

Reply

Jb Seph December 22, 2019 at 2:20 pm

Thanks

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *