Create eye-catching scrollable charts, a game-changer for large datasets
When you have tens of thousands of records in Microsoft Excel, scrolling and resizing the chart manually isn't feasible. These scrollable charts using Form Controls are hence handy, you can view your data engagingly, scroll through different segments of the chart, and create powerful Excel dashboards that double as clean and dynamic ones.
The following is a stepwise approach to creating scrollable and interactive Excel charts using scroll bars and dynamic ranges.
🔍 Why Use Scrollable Charts in Excel?
-
Focus on a subset of data without overloading your viewer
-
Create interactive dashboards that impress clients and teams
-
Navigate large time series, sales, or performance data smoothly
-
Add interactivity without writing VBA code
-
Great for financial reporting, KPI dashboards, and project tracking
🛠️ Step-by-Step How to Make a Scrollable Chart Using a Scroll Bar
✅ 1. Prepare Your Dataset
Example:
- Column A: Dates or Categories (e.g., Jan to Dec)
- Column B: Sales, Revenue, or Metric Data
- Create a named range like FullData
✅ 2. Add a Scroll Bar Using Form Controls
- Go to Developer -> Insert -> Scroll Bar (Form Control)
- Draw the scroll bar near your chart area
- Right-click -> Format Control
- Set:
-
- Minimum value = 1
- Maximum value = Rows in dataset - number of points to show + 1
- Cell link = G1 (or any empty cell)
✅ 3. Create a Dynamic Named Range with OFFSET or INDEX
This formula updates the range based on the scroll bar value:
=OFFSET(Sheet1!$B$2, $G$1-1, 0, 12, 1)
OR use INDEX for stability:
=INDEX(Sheet1!$B$2:$B$100, $G$1):INDEX(Sheet1!$B$2:$B$100, $G$1+11)
✅ 4. Insert and Link the Chart
- Insert a line or column chart
- Right-click on the data series -> Select Data
- Edit the series to use your dynamic named range (e.g., =Sheet1!SalesRange )
✅ 5. Add Axis Labels Dynamically
Use a separate range for X-axis values, linked to the scroll bar with OFFSET or INDEX as well.
💡 Pro Tips on Scrollable Excel Charts
- Double scroll bars with drop-down lists as multi-layered filters
- Conditional formatting in the table is useful in highlighting key trends in data
- Set your chart titles with dynamic date stamps like the one below:
="Sales Overview (" & TEXT(TODAY(),"mmm yyyy") & ")"
- Slicers can add more interactivity to the PivotTables.
- Named ranges are helpful for reuse and for keeping formulas clean.
These scrollable charts are an epitome of data visualization and allow users to move around the table with just a few clicks. Whether on sales for the month or project progress, or tracking year-on-year changes, scroll Excel charts will always help your users sift through the piles of data to land on what matters most.
The very moment you build your first scrollable chart, you will level up your Excel data visualization skills that use these interactive design features to wow and inform.
🎓 Learn More with Other Levels
Take your Excel skills to the next level:
-
🌐 Visit our Other Levels Website
-
📺 Watch free tutorials on our Other Levels YouTube Channel, and learn Microsoft Excel from beginner to advanced.
✍️ Other Articles, Tips & Tricks you would like:
- Create Interactive Timeline in Microsoft Excel
- Calculate Age from Date of birth in Microsoft Excel
- Experience the Power of CONCAT Function in Microsoft Excel
Share:
How to Create Dynamic Date and Time in Microsoft Excel
Build a Simple CRM (Customer Relationship Management) system in Excel