How to Share or Save Excel Workbooks Without Formulas

Want to share a Microsoft Excel worksheet without including formulas, comments, and other cell attributes? This tutorial highlights two ways to create formula-free duplicates of Excel documents you intend to share or distribute.

Remove Excel Formulas Using Paste Special

The “Values” paste option in Excel removes specific attributes from cells or data in your worksheet. You can use the paste option to create a formula-free duplicate of your worksheet.

Open the Excel workbook you want to share or save without formulas and follow the steps below.

  1. First, duplicate the worksheet (to a new workbook) to avoid losing the data in the original workbook. Right-click the sheet in the worksheet tab and select Move or Copy on the action menu.
Share or Save Excel Workbooks Without Formulas Image 1
  1. Select (new book) in the “To book” drop-down menu and check the Create a copy tickbox. Select OK to close the dialog box.
Share or Save Excel Workbooks Without Formulas Image 2

Excel will duplicate the sheet into a new workbook. Proceed to the next step to remove the formulas in the duplicate worksheet/workbook.

  1. Press Ctrl + A (Windows) or Command + C (Mac) to select all cells in the new (duplicate) worksheet. Alternatively, select the Triangle icon in the top-left corner of the first row and column.
Share or Save Excel Workbooks Without Formulas Image 3
  1. Press Ctrl + C (Windows) or Command + C (Mac) to copy the selected cells.
  2. Again, press Ctrl + A (Windows) or Command + A (Mac) to copy the entire worksheet.
  3. Open the Home tab and select the down-arrow icon below the Paste icon in the “Clipboard” section.
Share or Save Excel Workbooks Without Formulas Image 4
  1. Next, select the first icon (Values) in the “Paste Values” section.
Share or Save Excel Workbooks Without Formulas Image 5

Alternatively, select Paste Special, choose Values in the “Paste” section, and select OK.

Share or Save Excel Workbooks Without Formulas Image 6

Pro Tip: You can also use the Ctrl + Alt + V keyboard shortcut to open the Paste Special window in Excel.

The paste value option prompts Excel to remove formulas, formatting, and data validation rules from all cells in the worksheets. Cells in the worksheet will display only their values and nothing more.

  1. Press Ctrl + S (Windows) or Command + S (Mac) to save the workbook as a new Excel file on your computer.
  2. Enter a file name and select Save. Ensure you save the workbook in “.xlsx” format so the recipients can view or edit the sheet in Excel.
Share or Save Excel Workbooks Without Formulas Image 7

Save Worksheets Without Formulas Using VBA Code

Share or Save Excel Workbooks Without Formulas Image 8

The Visual Basic for Application (VBA) tool can help convert cell content in an Excel worksheet to values only. If this is your first time running VBA in Excel, our VBA guide for beginners is a must-read.

We recommend running the VBA code below on a duplicate worksheet to avoid losing any information/data in the original document. Open the duplicate document and follow these steps:

  1. Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) window. Alternatively, open the Developer tab, and select Visual Basic.
Share or Save Excel Workbooks Without Formulas Image 9
  1. Select Insert on the top menu and choose Module.
Share or Save Excel Workbooks Without Formulas Image 10
  1. Paste the following code in the Module and press F5 on your keyboard to run the code.

Sub Formulas_into_Values()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

Cells.Copy

Cells.PasteSpecial Paste:=xlPasteValues

Next ws

End Sub

Share or Save Excel Workbooks Without Formulas Image 11

The VBA code removes formulas from all cells in the worksheet and converts their content to values only.

  1. Press Ctrl + S (Windows) or Command + S (Mac) to save the formula-free worksheet to your computer.

Create and Share Formula-Free Excel Sheets

The methods above are surefire ways to create value-only Excel sheets without formulas, cell formatting, comments, and other attributes. Remember to create a backup of your Excel workbook (for reference purposes) before stripping its formulas.



Source : How to Share or Save Excel Workbooks Without Formulas

Comments

Post a Comment

Popular posts from this blog

How to Add or Remove a Secondary Axis in Microsoft Excel Charts

Image
When you have a chart with mixed data types or a dataset that varies broadly between series, consider using a secondary axis in Microsoft Excel. With it, you can display the data you need without overwhelming or confusing your viewers. When to Use a Secondary Axis As mentioned, if you have different data types or your series amounts widely vary, these are good times to use a secondary axis. For instance, you can see that the column chart below doesn’t accurately depict the visitors and sales in our dataset. There’s a large variance in the sales amounts, making it hard to get a decent picture of the number of visitors per month. Plus, the sales are in dollar amounts, whereas the visitors are basic number values . If your chart would benefit from a secondary axis like this one, here’s how to add one as well as remove a secondary axis later if you change your mind or your data. How to Add a Secondary Axis in Excel To add a secondary axis to your Excel chart, you’ll use the combo ...
Read more

How to Use and Customise the Status Bar in Microsoft Excel

Image
Excel’s status bar is one of the hidden gems (and an unsung hero) that makes it the default spreadsheet software you turn to. It quietly resides at the bottom of the Excel window, often overlooked but brimming with valuable information and customization options. In this article, we will look at it and show you how to take advantage of it. Excel Status Bar Default Options Even before you customize the Excel status bar, it’ll display some helpful information and give you access to some useful functionalities. The common Excel options you can find as default on the status bar are: Cell Mode Macro Recording Tool Accessibility Check Page Number Statistics/Mathematical Options Worksheet Views Zoom Options Let’s see what each of them does and how you can use them. Cell Mode At the leftmost side of the Excel status bar, you’ll find the cell mode indicator. It’ll inform you about the status of the currently selected cell. There are four different status modes: Ready . It ...
Read more

What Is Windows AI-Powered Copilot?

Image
Microsoft has released its brand-new artificial intelligence (AI) companion, Microsoft Copilot. Previously known as Bing Chat, Copilot has a series of new abilities that can help you get more done in less time. In this article, we’ll provide a brief description of each of Microsoft’s new AI copilots, then we’ll focus on the Windows 11 copilot and what it can do to boost your productivity. What Is Microsoft Copilot? Microsoft Copilot is an artificial intelligence chatbot you can ask to perform different tasks, including generating text and images and answering general questions. Microsoft’s plan is to have a Copilot in each of its apps to provide assistance and improve user experience across the Microsoft ecosystem. Here are the different Copilots that are currently available or slated for future release: Windows 11 Copilot: Available for free as a preview in North America, Asia, and South America, Windows 11 Copilot lets you perform tasks, get answers, and create AI-generate...
Read more