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

Make a scrollable box in a worksheet in Excel that allows you to scroll through a table of data without scrolling in the worksheet or moving around the worksheet.

This feature allows you to create Dashboards or Snapshots of data in your workbook so that you can see everything that is important in one location.

In this tutorial you will learn how to combine Form Controls in Excel, a vertical scroll bar, along with custom formulas in order to create the effect of scrolling through a data set without actually scrolling through a worksheet.

One of the best parts about this trick is that it allows you to hide the original data set.

Resources:

#msexcel #tutorial #freetutorial #exceltips #excel

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

[cnav]

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.

[deft]

Love the way you explain this. Very intuitive.

[sircorn]

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

[daeln]

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

[McKaySavage]

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

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

[gandhiviji]

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

[nadermounir]

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

[xpdiablo]

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

[caspyuk]

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

[asadrauf]

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

[ZaheerKhan-zpwo]

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.

[Pseudify]

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

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

[enrikekasijas]

Great stuff;) Definitely useful trick xD

[Ghosttv]

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.

[Carloskursh]

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

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

[martyc]

Cool- will it work in excel online though?