Make Perfect Scrollable Boxes in Worksheets in Excel - Great for a Dashboard 

Instead of eyeballing the size of the scroll control and the surrounding box to match their respective cell blocks, you could have used the Alt key to assist. Pressing the Alt key while using the sizing dots allows Excel to snap the dots' corners and sides to their blocks' rectangular grids.

Love the way you explain this. Very intuitive.

It is so cool. Thank you for discovering endless wonders of Excel for us.

That’s a great technique for dashboards! Thanks for sharing

wow. thank you for such a clear, concise tutorial!! Much Respect.

Amazing idea :) love it. Thank you very much for your hard work 👍

Hi, just a general tip for this when your doing dynamic row references since while this way works if you cut and paste anything from A1 or near there then you can end up with the wrong reference. I always try and make data sets into tables because its usually easier to reference and you can often do some things that otherwise would be hard. So when you have made your "Parts for Sale" into a Table by selecting all your data under the "Parts for Sale" Cell and click "CTRL" + "T" starting with the starting cell of "Part". Now when you use Row use the formula ROW()-(ROW(Table1[#Headers])) , the only thing you might have to change is the table name which defaults to Table# depending on how many you did. If you want to find the table name select the table and a new tab will show on the top saying Table Design and on the left side it should say table name with an example of " Table1", if I Change "Table1" to be "Steve", then the formula would be ROW()-(ROW(Steve[#Headers])) This now lets you move the table anywhere while having dynamic references and its much harder for users to break =)

this is a great video.. now i understand the basics of this tutorial.. but... what do you do when you are adding a row of data on a daily basis to you data sheet.. its not gona stay at 11 rows as per your video... could you explain how that would work.. ohh and thx for the videos.. im gona learn a lot from here

Amazing idea, till date I do not know how to use the scroll bar, It is useful for my project. Thank you

WOW !!! Thats GREAT !!! Thanks.

Super Great FEATURE TBH ... Keep up the good work ♥

This is great for building dashboards. Is it possible to make max value dynamic using VBA?

Brilliant & amazing, thanks.

That was great. Thank you.

Very Useful. Thank bro!!!

Love your tutorials. One question, when my sheet is protected. I cannot use the scroll. How or what can i do to be able to use scroll bar while sheet is protected.

Very cool. But can you make that scroll function work with the scroll wheel on your mouse?

could you in maximum value (control) put a formule if the table data can grows....???? thanks and very useful

Hello TeachExcel! I am your big fan. I am watching all of your videos to improve my skill (I like your teaching /Explaining style.) and i want to know your name.

Very very clever!

Great stuff;) Definitely useful trick xD

Nice! Thanks brah.

Thanks!

Nice trick! Why not use only one spill formula for the whole set though? in the top left corner (cell I5): = offset(sourceData; controlValue-1; 0; 6; 4) There is no need for multiple formulas so using offset should be fine right? You could also do it with one index formula but I doubt it would be more effective: = index(wholeSourceData; sequence(6; 1; controlValue; 1); sequence(1; 4; 1; 1))

Cool- will it work in excel online though?

Thank you.

Very clever

Hi, small advice - instead using ROW() function, now there is better way - its called sequence() function :) you should take a look for it. also when clicking I was hoping this area of data would be floating above the grid and its movable. I could use sth like this.

Wonderful

How would I make this scrollable box to miss out values in the original table, if I was to apply a filter in the original table. Currently I can render a 0 using subtotal, but I wish to ignore those values completely and have a compiled list in the scrollable box. Thank you!

Is it possible to do a sheet-within-sheet kind of thing? I don't want to change the data, I just want to scroll the data sideways in the table.

Have done some things to make it somewhat dynamic. Was mentioned couple time here. I played around and did this. =IFERROR(INDEX(Data!E$7:INDEX(Data!E:E,COUNTA(Data!$E:$E)+4),$S$3+ROW(A7)-1),"") Using the 2nd Index and counta to act as last row. The +4 is just because my data table starts on the 5th row in Data sheet. S3 is control value/cell link. In the scroll properties I just did max value as 1000. So with the IFERROR and null wrapped around the whole thing, I just see blanks past where there is no data..i.e if not 1000 entries in my data. Otherwise there'd be errors or #REF or whatever that was. Not 100% dynamic but it works!

Hi thanks for sharing, does this works with data from pivot table? Thanks again

I have a single worksheet (EXCEL 2000) and I freeze pane title area with 10 rows included, about 2500 rows of data with 8 columns, I want to put vertical scroll above the freeze pane and scroll the data below freeze pain. (Please help using VBA in this configuration)

Hi All, Is it possible to put checkbox with scrollbar? when I click up, the data will going up together with the checkbox. Is this possible?

nice

Love this! I'm thinking of use cases I can apply this to in my head... Question: Can you use a formula (or a formula in a referred to cell) to determine the "Maximum Value" of the Scroll bar so that it can be dynamically updated as new data rows are added?

very good

I am using O365, when I enter INDEX function, it shows all data in the list, it seems the list is a whole set, I couldn't delete a part because I don't need to show all list, I just need to show top 5 lines, then scroll to show the rest lines. How to solve this issue?

❤

Would be cool to make the data set dynamic vice fixed length.

I am using Office 365 and i donot get Control under Format Control, Please help me

💕💕👌👌👍👍

What about using mousewheel to scroll...stupid Excel. Lol.

Could you speak any slower?