When I paste new rows at the bottom of my Excel table, the table expands properly, but none of the column formulas or formatting apply to the new rows of data.
I've tried paste special with values only, formula only, formatting only, etc. No matter how I paste the new rows, the formulas and formatting do not apply to the new rows of data.
Is there a way to automatically apply/keep the formulas and formatting when pasting new rows of data into an Excel table?
Formatting Issues: The Inc No column is formatted as text to keep the leading zeros for our incident numbers. This shows correctly in the existing data in the table, but the new rows (starts row 22 in image) are not formatted properly.
Formula Issues: Survey Match should be blank in the new rows of data (row 22 and below), because another column (not show in image) "Survey Inc No" is blank. The new rows of data (row 22 and below) have no formula applied, but the rows above are formatted by this formula:
=IFS([@[Survey Inc No]]=CONCAT([@Year],"-",[@[Inc No]]),"TRUE", [@[Survey Inc No]] ="","", [@[Survey Inc No]]<>CONCAT([@Year],"-",[@[Inc No]]),"FALSE")
Note: I am using Excel for Office 365 (opened in Excel app, not online). Also, our IT department does not allow me to connect my SQL report directly to Excel, so we have to run the report in SQL and the copy/paste all rows and columns into this Excel table (where we later manually input additional data). I tried copying straight from SQL, and I also tried pasting from SQL into another Excel spreadsheet, then pasting into this table, both results are the same (not keeping formulas and formatting).
