5 Ways to Copy Excel Sheet Without Formulas

If you've ever needed to share or reproduce parts of an Excel worksheet without those pesky formulas messing things up, then you know the struggle. In this post, we'll delve into 5 methods to copy an Excel sheet without formulas. Whether you're looking to create a simplified version of your data for presentation purposes, or just want to ensure no confidential formula leaks out, these methods are designed to keep your data clean and your formulas hidden.

Method 1: Paste Special

The ‘Paste Special’ function in Excel is an incredibly versatile tool that allows you to paste only the values from your cells, effectively removing formulas:

• Select the range of cells or the entire worksheet you want to copy.
• Right-click on your selection and choose 'Copy' or press Ctrl + C.
• Move to where you want to paste the copied data.
• Right-click and hover over 'Paste Special', then choose 'Values'.
• Alternatively, you can press Alt + E, S, V, Enter for a quick sequence to paste values.

💡 Note: The Paste Special method is effective for transferring data within or between Excel workbooks. It keeps the cell formatting intact, but formulas are discarded.

Method 2: Using the ‘Formula to Value’ Add-in

For those who frequently need to convert formulas to values, Excel’s add-ins can save you time:

• Open Excel and go to the ‘File’ menu.
• Click on ‘Options’ then ‘Add-Ins’.
• At the bottom, select ‘Excel Add-ins’ from the drop-down and click ‘Go’.
• In the Add-Ins dialog box, check ‘Formula to Value’ (if available, or download from the Microsoft Store) and click ‘OK’.
• Select the cells or worksheet, go to the newly added tab, and click ‘Convert Formulas to Values’.

Method 3: VBA Macro

Automating the process with VBA is a smart move for those dealing with large datasets:

• Press Alt + F11 to open the VBA editor.
• Go to ‘Insert’ > ‘Module’ to add a new module.
• Copy and paste the following code:

  Sub ConvertFormulasToValues()
      Dim ws As Worksheet
      For Each ws In ThisWorkbook.Worksheets
          ws.Cells.Copy
          ws.Cells.PasteSpecial Paste:=xlPasteValues
      Next ws
      Application.CutCopyMode = False
  End Sub

• Close the VBA editor, then from Excel, press Alt + F8, select ‘ConvertFormulasToValues’ and click ‘Run’.

💡 Note: This VBA macro can be customized to run on selected sheets or specific ranges. Remember to save your workbook as a macro-enabled file (XLSM).

Method 4: Power Query

Power Query is a game-changer for data manipulation in Excel. Here’s how you can use it to convert formulas:

• Select the sheet you want to copy.
• Go to ‘Data’ > ‘Get Data’ > ‘From Other Sources’ > ‘From Microsoft Query’.
• Select ‘Excel Files’ and navigate to your file, then choose ‘Connect’.
• In the Query Editor, load and transform your data, ensuring the ‘Load To’ destination is set to ‘Table’ or ‘Connection only’.
• From the ‘Home’ tab, select ‘Close & Load’ to load the data without formulas.

Method 5: Manual Copy and Paste

Sometimes, the simplest solution is the most effective. Here’s how to do it manually:

• Select the range or entire sheet you want to copy.
• Right-click, choose ‘Copy’ or press Ctrl + C.
• Go to the destination sheet or workbook, and instead of pasting normally, do the following:
  • Select an empty cell or range.
  • Press Alt + E, S, V, Enter to paste only values.

💡 Note: The manual method can be time-consuming but ensures you retain control over what's being copied, including data validation and formatting.

Each of these methods has its advantages, from quick actions to more automated solutions. The choice depends on your comfort with Excel, the complexity of your task, and how often you need to perform it. Whether you're preparing data for a report, sharing it with others, or just keeping your workbook clean, knowing these techniques is essential for any Excel user.

Can I copy an Excel sheet without formulas using Excel Online?

+

Excel Online has a limited version of these features. You can use the ‘Paste Special’ option, but features like VBA macros or Power Query aren’t available in the browser-based version.

How do I ensure cell formatting is retained when copying?

+

Using the ‘Paste Special’ option and selecting ‘Values and Number Formats’ will retain most formatting. However, some complex formatting might need to be reapplied manually.

What if I accidentally lose the formulas when copying?

+

Always keep a backup of your original worksheet. If you accidentally overwrite formulas, you can restore from your backup or use the ‘Undo’ command (Ctrl + Z).