This is the kind of question that should make every spreadsheet user stop and rethink their workflow. You are wrestling with 1.4 million rows of forecast data, split across two years, six customers, and a dozen tabs, and the file lags so badly that even saving becomes a chore. The problem is not your data. The problem is that you have built a structure that fights against the tool, and the solution is not a clever formula or a faster machine. It is a fundamental change in how you organize the work.
The answer to your first question is yes, store the data in one worksheet and keep your pivot tables on separate tabs. But do not think of this as a linking problem. Think of it as a separation of concerns. The raw data is a single, flat table that should live in one place, with no formatting, no subtotals, and no extra columns beyond what you actually need. Then, on other sheets, you build your pivot tables that reference that one table. Excel and Power Pivot are designed to handle this exact scenario. When you keep the data in one table, you stop duplicating information across multiple tabs, and you stop forcing the file to recalculate a dozen different pivot caches every time you type a value. The lag you are experiencing is not because the data is large; it is because the data is fragmented and repeated.
Your second question about Power Query is the key to unlocking this entire workflow. Power Query is not a magic wand, but it is the right tool for this job. You have two tables with identical columns, one for 2026 and one for 2027. Instead of keeping them separate, you load both into Power Query, use the Append operation to stack them on top of each other, and then load the combined result back into your workbook as a single table. That combined table becomes your single source of truth. From there, your pivot tables reference that one table, and you can filter by year, by customer, or by any other dimension you have. The process is simple enough to explain to a five-year-old: you are taking two piles of LEGO bricks, dumping them into one bucket, and then building whatever you want from the whole pile instead of building two separate models.
What you are doing now is not sustainable, and it is not your fault. The tools have evolved, but the habits have not. The fact that you are asking these questions means you are ready to move beyond the old way of doing things. Start by consolidating the data into one table. Use Power Query to append the years. Then, and only then, build your pivot tables. The file will still be large, but it will be manageable, and more importantly, it will be correct. You will spend less time wrestling with lag and more time actually analyzing the forecast. That is the point. That is the transformation.