Fixing Formula Auto-Fill / Replication / Calculated Column Problem in an Excel 2016 Query Table

One of the great new features of Excel 2016, is that it has Power Query and Power Pivot built-in.  This makes connecting to a database a LOT easier, where a database table, view, or query will get imported into an Excel table, and then new columns can be added to that table with formulas.  For folks like myself who are good with Excel formulas, this is often easier (and sometimes more powerful) than trying to have the SQL query itself do calculations.

But, I had run into a problem, which was that on one of my spreadsheets, every time I would refresh a query, all the new data from the query would not have the formula column automatically fill in like I was expecting.   And since I had a fairly complex spreadsheet, if I forgot about this problem, I would get bad data and reports….   So I needed to fix it.

It was hard for me to find the answer, because searching Google didn’t make it easy since “auto fill” often is associated with the “fill handle”, and not much is written about Excel 2016.   But I finally found this thread on a Microsoft Forum, where while Microsoft was no help, in the end one of the other users posted the solution.  So I’m sharing it here too:

  1. Click on the table connected to the Query
  2. Go to the Data Tab
  3. In the Connections section, click on Properties
  4. Make sure “Preserve column sort/filter/layout” is checked
  5. Refresh the query, and then re-implement the autocomplete, by double clicking on the formulas, pressing enter, and then choosing the smart tag to have the formula in all cells.

Leave a Comment

Your email address will not be published. Required fields are marked *