Forum Discussion
Automatically change chart scope (zoom) for data that exists in series
Hi there. I have an excel spreadsheet that is built around analyzing a large series of data entries. The primary data entry table has 100,000 rows to enter data (yes, I am potentially analyzing that much data). I'm trying to make charts that show the data. However, I have had the must frustrating time having the charts "zooming in" or changing the scope of the data shown. Specifically, the sheet needs to be able to analyze 100,000 rows of data, but will most of the time have far less data (2,000-6,000). The charts currently plot data for the cells that have entries, but the scope or zoom of the chart is for the full 100,000 cells, so the plotted data takes up a VERY small amount of the chart.
I have tried about every single possible way of getting around this based on other forums, tutorials, and even turning to ChatGPT and Copilot to help me, with no working solution. I've tried having formulas instead of blank cells that purposefully throw an error (NA()) in case a cell is blank, in hopes that I can use the "Hidden and Empty Cells" function of treating errors as blank cells. However, this only prevents the data from being plotted and the chart's scope does not change and still leaves room for the 100,000 possible entries.
This has been infinitely aggravating and am turning to the forums before losing all hope.
I look forward to hearing back. Thanks in advance.
Example of issue:
5 Replies
- still leaves room for the 100,000 possible entries
Do you want to dynamic set data souece (from 6000 to 100000 rows) for a chart
like this situation？
https://club.excelhome.net/forum.php?mod=viewthread&tid=1682274&page=1&mobile&_dsign=1c4cf1d9- A dynamic set data source does sound like it would work. I've tried this once but it didn't work. But maybe there's another way to do it.
Not sure if the situation you linked covers my issue exactly. I can't really tell what that situation is trying to accomplish. Although it does seem like it's similar.
In terms of the range, I would want it to work between any number between 1 and 100,000.billnowlin I you are on a modern Excel version, perhaps the attached (small scale) example does what you need.
