Trendline that emphasizes certain points

Well, for a large dataset such approach will be indeed tough to implement and pretty time consuming.

I think that 2 approaches could be easy:
1) use Power Query to multiplicate the results (to automatically prepare content of your columns P and Q
2) find the best fitting line using Solver and the same math (minimize sum of squared differences) which is used by excel trendline.

The second approach is presented in a copy of your original sheet

the calculated Average gas consumption as a linear function of Average surface area is in column I. The slope and constant are first taken from the initial fit (either from your graph, or from linest formula - calculated in G12:H12 and values copied to G16:H16. these will be variables for solver. The aim for solver will be to minimize squared differences between calculated and real-data avg gas consumption in column J.
But this squared difference shall be multiplied (weighted) by a number of cases (column K).
And now the weighted sum is calculated (G18). We will use solver (make sure to uncheck "set non-negative values for all variables") to minimize this sum.

Now the fitted line (Col I vs Col G) can be added to the plot.

To my knowledge, Excel does not have built in support for weighted least squares regression. I see four possible paths forward on a problem like this:

1) As the sign on my statistician coworker's door says, "Friends don't let friends use Excel for statistics." I would expect that a more robust stats package (like R or Minitab) would have built in support for weighted least squares. Would you be allowed to abandon Excel completely for this and seek out a more robust statistical package?

2) Install a 3rd party add-in to Excel that knows how to do weighted least squares. When it comes to statistics in Excel, this is my go-to source: https://real-statistics.com/multiple...ar-regression/ He not only explains how the statistics works for a variety of statistical topics, but his add-in provides a wide array of functions that you can use in your spreadsheets.

If you are required to use Excel, but cannot use outside utilities, then you are left programming your own weighted least squares.
3) You can do like you are currently doing, where you use the information in the original data to build a new input matrix with weight copies of each data point. If this is your preferred approach, I expect that Power Query will be the easiest tool for reshaping your x,y,weight data into a suitable input matrix for the LINEST() function or the chart trendline utility (whichever you prefer. at present you seem to prefer the chart trendline utility. I usually prefer the LINEST() function).

4) (Mathophobe warning: talk of matrix algebra ahead) If you do the research, I expect that weighted least squares (like ordinary least squares) reduces to a simple sequence of matrix operations. Excel has built in support for matrix algebra. Once one understands the sequence of matrix operations, I would expect it to be fairly simple to program those matrix operations into the spreadsheet using Excel's built in matrix functions [MMULT(), MINVERSE(), TRANSPOSE(), and so on].

As the programmer, you have choices. Which approach to this problem do you like? How can we help you implement that solution?

PS. As your data is already categorized, the quality of fit (for instance R^2) is just indicative. Obviously it would be the best to have access to full dataset with just required columns, may be you can have access to it?

And just a side comment: The interesting issue is (as quite often with linear regression) the meaning of the intercept value. Slope is quite clear - it shows how quickly gas consumption increases with increasing area. Quite natural. But the intercept? If the area is 0, the consumption shall be 0 too. Or may be if there is any activity even in very small housing, certain amount is used - probably that's it. Or may be non-linear function shall be used for approximation? This could be better visible if not-categorized (full data) set is used.