I've done something, it works, but I don't know what I did!

    With the excessive heat in the northern hemisphere this summer, I had set up an additional spreadsheet to gather data from my weather station.


    The WMO standard for daily average temperatures is 4 measurements at 01:00, 07:00, 13:00 and 19:00 plus the daily max and min temps divided by 6 to give you an average. (In 2021 we are three to 6 degrees above the rolling average from previous years) To this I added the high and low relative humidity, the average and the difference between the two. All is on the attached spreadsheet, short version.

    Hot weather extra 2.xlsx

    Calculations are being done in columns H, I, J, M and N. This is all in an Excel 2007 spreadsheet.


    What I noticed was that I didn't have to copy the calculations down to the next blank cell in the column.


    Looking at Row 27, the first temperature is in cell B27. As soon as I populate C, D, E, F and G27, then the spreadsheet calculated H27, I27 and J27 with no input from me?


    I have looked at the cell formatting but I can't see what it is that I have enabled to get this to happen. It's a useful feature that I have somehow got working.


    Please can someone tell me what I did because I would like to use it on other spreadsheets.


    Thank for reading


    Norman

    There's a setting in Excel's Options


    1. Click File top left.
    2. At the bottom you will see Options. Click it
    3. Click Advances

    On the right of the dialog that opens you will see Editing options. I n this list you will see "Extend data range format and formulas.


    If that is ticked then that is why your formulas are self populating.


    In newer version of Excel you can format as a Table to get the same effect.



    Overview of Excel Tanles

    Thank you Roy


    Yes, exactly as you say, that box is enabled.


    As this is a setting, I would have expected the feature to work across all spreadsheets.


    I have created a new spreadsheet this afternoon, and the "extend data range.." in options is still enabled, but the calculations do not auto advance.


    Is there something else which turns the feature on in an individual spreadsheet/worksheet please?


    Norman

    I think you need a few rows of data with identical formulas before the behaviour kicks in.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Rory is correct. I remember now that you need to start filling in manually and then the feature should start. I suggest you look at tables though.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login