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

[@bobbysteurer]

Dude, I cannot tell you how grateful I am for this tutorial. Every other one I’ve watched has been needlessly complicated or had features my version lacks. This was so simple and makes a great visual impact on this cruddy little report I’m making. Seriously, THANKS!

[@c17nav]

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.

[@LunaStarFire]

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 =)

[@TeachExcel]

"Much harder for users to break" is always a great idea! Tables really can be a life-saver! Thanks for this detailed and helpful comment Matthew! :)

[@LunaStarFire]

@@TeachExcel I end up spending hours just idiot proofing forms and even then people surprise me. OOO another thing you can do with this if you want to make sure people cant break it, you can insert a square shape and reference the cells in your table, do this per cell (really only useful for small tables like this), you can then group all the individual cells into one object and boom, you now have a completely free to move reference table with a scroll bar. Not sure if it would ever be useful but you know =)

[@LunaStarFire]

And then you can put all the data on a new tab which the end user cant see by opening VBA and set it to very hidden so they cant ever select the tab the data is on to break it =)

[@TeachExcel]

xlSheeVeryHidden is one of the best things!!! And password protect the vba project as well so they can't unhide it by hand

[@attaulmomin]

is there any way to use it with filter formula since i'm using filter function to get specific data in my dashboard.

[@bryonlim3183]

Thanks!

[@deft08]

Love the way you explain this. Very intuitive.

[@daeln6065]

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

[@McKaySavage]

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

[@caspy1uk241]

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

[@sircorn4248]

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

[@gandhiviji]

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

[@TeachExcel]

You're very welcome Gandhi! I'm glad I could help!

[@4xpdiablo]

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

[@TeachExcel]

Thank you very much Mahmud!!!! :)

[@asadrauf5902]

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

[@bertvantol9669]

WOW !!! Thats GREAT !!! Thanks.

[@ZaheerKhan-zp5wo]

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.

[@ExcelInstructor]

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.

[@alexborovik2534]

If you want moveable window try excel camera tool

[@Ghost83tv]

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.

[@Pseudify]

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

[@vuduylinh8509]

Very Useful. Thank bro!!!

[@TeachExcel]

You're welcome!!

[@TravisFX]

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!

[@nadermounir8228]

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

[@TeachExcel]

Thank Nader!!! I'm glad you like it :)

[@Carloskursh]

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

[@tuto79]

That was great. Thank you.

[@lucasgarecht2082]

What if you don’t know how long the list will be, if it updates dynamically? Is there a way to handle that also, or should you just plan for maximum the max number of rows you expect to get?

[@Weehawk]

Nice! Thanks brah.

[@TeachExcel]

Thanks Lachdanan!!

[@UTJK.]

Very very clever!

[@Ekitchi0]

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

[@arindampaul1589]

Brilliant & amazing, thanks.

[@k.chriscaldwell4141]

Thank you.

[@TeachExcel]

You're very welcome Chris!

[@enrikekasijas705]

Great stuff;) Definitely useful trick xD

[@TeachExcel]

Thank you Enrike! I'm glad you think so :)

[@martyc5674]

Cool- will it work in excel online though?

[@finsntins3425]

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!

[@c4a6]

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.

[@Adam_K_W]

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?

[@chaiyya345]

I think this can be done, convert the table source to an excel table

[@DM-py7pj]

@@chaiyya345 I think you would need an event with VBA to update the appropriate object (scrollbar) property. Perhaps something like worksheet_activate on the page with the visual in. The benefit of Excel table in backing would be the easier retrieval of current row count.

[@Ekitchi0]

You could leave the maxvalue of the scrollbar object to a large amount and replace the contolValue in the formula with min(controlValue; nbVal(dataColumn)-displayRows)

[@bpisan]

Could the Count() function work for something on this?

[@johnboffin476]

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)

[@TheMarkGross]

Very clever

[@erikysilvagomes5496]

Wonderful

[@aponijuanzosimo9923]

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?

[@ivalneisena]

very good

[@TeachExcel]

Thank you :)

[@chaiyya345]

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

[@irfanabbassi9202]

Yes

[@macozhao8205]

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?

[@grendbelema]

nice

[@Rapture1469]

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

[@Thehavemans]

To piggyback on this, can the data set be a table or data model?

[@colsadventures]

I’ll be playing with a table data set to see what happens.

[@TeachExcel]

Good question! The issue that I see is making the scroll bar go to the correct max number. But, maybe if it was replaced with a spin button that could be a better experience or just add a formula to the scrolling data that changes the Errors to a blank value, then it could at least still be usable even though the scroll wouldn't perfectly fit the data.

[@asitharanatunga8858]

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

[@arokiasamyjosephraj6487]

❤

[@zaighamuddinfarooqui1705]

💕💕👌👌👍👍

[@NewYears1978]

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

[@TeachExcel]

hahah, there is always something they kind of permanently 'forget' it seems.

[@lewiskelly14]

Could you speak any slower?