The zero line on your chart isn't a bug. It's a symptom of a spreadsheet that wasn't built for how you actually work. When you add a formula column to a dataset that gets cleared and refreshed from a website, Excel treats those empty cells as zeros. Your sum column dutifully adds nothing to nothing, and your graph dutifully shows a flat line of nothing across the whole range. That's not a user error. That's a design limitation.
The fix you've been using, manually dragging the formula down after each refresh, works, but it's the kind of work that spreadsheet users have been conditioned to accept as normal. It's not normal. It's a friction point that breaks your flow, introduces the risk of forgetting a step, and turns a simple data update into a two-stage chore. You shouldn't have to micromanage your formulas just because the data source refreshes. The tool should handle that.
What you're running into is the difference between a static calculation environment and a dynamic one. Traditional spreadsheets calculate what you tell them to, when you tell them to. They don't anticipate your next move. An AI-native approach would recognize that when data is cleared and repopulated, formulas should follow. It would treat your sum column as a persistent rule, not a fragile manual extension. It would see the pattern in what you're doing and offer to maintain it automatically.
The practical takeaway here is straightforward: if you're spending time every week re-dragging formulas to avoid a flat zero line, you've outgrown the tool. The chart isn't the problem. The spreadsheet's inability to adapt to your workflow is. The next step isn't to find a better workaround. It's to find a tool that doesn't require one.