Inserting a new column in excel doesn't work and formatting doesn't change properly whencopyingcells

[Thomas_Cane]

I've got a spreadsheet that is suddenly acting weird across all sheets. If I try to insert a column or a row into a gap where data is, then it will not move my formulas. It does however push Pivot tables that are further across from the data which causes formula to fall out of sync as they do not update to the data they were referring to moving. I am also unable to use the undo function to revert back these changes.

Additionally, on the same sheet, if I try to copy a set of data to a new location (to add in columns or rows the long way) the formatting doesn't always get overridden by the new content.

It is only doing this one a single workbook with other workbooks still allowing rows or columns to be inserted without issue. Does anyone know if there is an option or setting that would cause this or is it more likely a size of the file issue?

[#2]

@Thomas_Cane 

The behavior you described in Excel could be due to a few different factors. Here are some potential causes and solutions to the issues you are experiencing:

1. Formulas not moving when inserting columns/rows:
• Check if there are any merged cells or conditional formatting rules that may be affecting the behavior. Merged cells can interfere with the movement of formulas. Unmerge any merged cells and reapply the formulas.
• Ensure that your formulas are written correctly and do not contain absolute references (e.g., using "$" in cell references). Absolute references can prevent formulas from adjusting correctly when columns or rows are inserted.
• Verify that you are inserting the columns/rows correctly. Right-click on the column/row header and select "Insert" to ensure the correct insertion method is used.
2. Pivot tables affected by column insertions:
• Pivot tables can be affected when columns or rows are inserted near their source data range. To prevent this, consider adjusting the source data range for the pivot tables to include a larger range or using dynamic named ranges that automatically adjust when data is added or removed.
• Alternatively, you can convert your pivot table to formulas using the "Convert to Formulas" feature, which will remove the pivot table and replace it with formulas, allowing for more flexibility when inserting columns or rows.
3. Formatting not overriding when copying cells:
• Ensure that you are copying cells correctly. Use the "Copy" (Ctrl+C) command and then use "Paste Special" (Ctrl+Alt+V) to specify the formatting options you want to apply.
• Check if there are any conditional formatting rules applied to the cells you are copying. Conditional formatting may interfere with the formatting override. You can clear the conditional formatting rules before copying or adjust the rules to allow for the desired formatting changes.
4. File size or complexity:
• Very large or complex Excel files with numerous formulas, data connections, or calculations can sometimes lead to performance issues and unexpected behavior. Consider optimizing your workbook by removing unnecessary formulas, reducing the number of data connections, or splitting the workbook into smaller files if feasible.

If none of these solutions resolve the issues you are experiencing, it is possible that there may be some other underlying cause specific to your workbook. In that case, it would be helpful to provide more details or share the workbook (without sensitive data) for further analysis. The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.