How to keep formula and formatting when copying and pasting in Excel?
As we know, when copying and pasting a range with both formula and formatting in Excel, the formulas will be changed but formatting kept normally (See the second screen shot below). Some users may try the Paste Special > Value & Source Formatting, this way keeps the formatting and formula results but loses the formulas (see the third screen shot below).
Is there a way to remain both formula and formatting when copying and pasting in Excel? Here I will introduce a couple of methods to solve it.
Save range as mini template (AutoText entry, remaining cell formats and formulas) for reusing in future
Normally Microsoft Excel saves the whole workbook as a personal template. But, sometimes you may just need to reuse a certain selection frequently. Comparing to save the entire workbook as template, Kutools for Excel provides a cute workaround of AutoTextutility to save the selected range as an AutoText entry, which can remain the cell formats and formulas in the range. And then you will reuse this range with just one click. Full Feature Free Trial 30-day!
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Note: If you do not have Kutools for Excel installed, you can double click a cell with formula in the Specified range, highlight the formula (see below screen shot), and press the F4 key. And change each formula reference to absolute one by one.
Now each formula in the specified range is changed to absolute as below screen shot shown:
Step 3: Now select the specified range, copy with pressing Ctrl + C keys simultaneously, select a blank cell and paste with pressing Ctrl + V keys simultaneously.
Now the specified range is copied and pasted with remaining its formula and formatting as below screen shot shown:
Note: If necessary, you can change the pasted formulas and original formulas to relative references with selecting them and clicking Kutools > Convert Refers > To relative > OK.
Keep formula and formatting when copying by Kutools for Excel
If you have Kutools for Excel installed, its Exact Copy utility can help you keep formula and formatting when copying and pasting easily in Excel.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.