How to delete all data but keep formulas in Excel?
Excel is a versatile tool for managing data, but there are times when you need to clear all the data in a worksheet while preserving the formulas. This is especially useful when working with templates or reports where the structure and calculations need to remain intact. In this guide, we’ll walk you through simple methods to delete all data while keeping your formulas safe.
Delete all data but keep formulas with Go To Special function in Excel
Delete all data but keep formulas with Kutools AI Aide
Delete all data but keep formulas with VBA macro (custom range or worksheet)
Delete all data but keep formulas with Go To Special function in Excel
While Excel does not offer a built-in one-click action to remove all cell contents except formulas, you can leverage the handy Go To Special feature to systematically select all non-formula cells—referred to as constants—within a selected range and then clear them in only a few steps.
This approach is especially effective for medium-sized ranges, such as when updating recurring templates or cleaning up reports before each reporting cycle. It preserves all formula-based calculations regardless of their complexity or layout. However, for very large sheets, this process may need to be repeated in sections to avoid performance delays.
1. Start by selecting the range where you want to clear data but keep formulas. For maximum control, you can select the entire worksheet by clicking the Select All triangle at the top left, or just highlight a specific data range as required. Then, press Ctrl + G simultaneously to open the Go To dialog box. In the dialog, click the Special button to open the Go To Special dialog. See screenshots below for reference:
2. In the Go To Special dialog, select Constants and click OK. This action will select all cells within your chosen range that contain manually entered values, leaving cells containing formulas untouched.
3. Once all constants (non-formula data) are highlighted, simply press the Delete key on your keyboard to clear them. Alternatively, you can right-click and select Delete from the context menu. All data except formulas will now be removed from the selected range. This deletes only the visible values and text, and will not affect the contents of formula cells.
Tip: If your worksheet includes formatting (such as conditional formatting or cell colors) linked to the data, the formatting will remain unaffected when clearing constants. Review your selection to ensure only desired cells are cleared.
Precaution: This process cannot undo deletion; make sure to save your file or duplicate the worksheet if you need a backup before proceeding. If you accidentally clear cells you didn’t intend, use Ctrl+Z to undo immediately.
Delete all data but keep formulas with Kutools AI Aide
Utilizing the highly practical Kutools AI Aide streamlines your workflow by providing a smart way to clear all data entries while automatically preserving formulas. This handy feature is particularly beneficial for users who frequently create reports or standardized templates, eliminating the need for manual selection and improving both speed and accuracy.
After installing Kutools for Excel, launch the Kutools AI Aide pane as follows: click Kutools > AI Aide. The AI Aide panel offers a simple text-based interface for entering specific requests.
- Select the data range that you wish to process. This can be any contiguous range, table, or even your whole worksheet as needed.
- In the chat box, type a clear instruction such as:
Delete all data but keep formulas in the selection. - Press the Enter key or click the Send button. Kutools AI will quickly analyze your request, identify all constants in the selected range, and prepare the operation to leave all formulas intact. After the analysis, click the Execute button to complete the process. Review the result: formulas and structured calculations remain, while all other data is cleared.
Helpful tip: The Kutools AI Aide understands a wide variety of natural language instructions. You can further refine your command to clear data from specific sheets, ranges, or exclude certain types of data as required for specialized workflows. If you want to revert the operation, simply use Excel’s undo function. Always verify the processed range to confirm that only constants were deleted and that all formulas have been preserved.
Delete all data but keep formulas with VBA macro (custom range or worksheet)
For users who need to automate this operation across multiple ranges, entire worksheets, or frequently repeat the action, using a VBA macro is a robust and flexible approach. This method allows you to clear all cell values except for formulas based on your selection or an entire sheet, making it well-suited for practical application in template preparation, periodic data refreshes, and large data sets.
1. To begin, open Excel and press Alt + F11 to open the Visual Basic for Applications editor. In the VBA window, click Insert > Module to create a new module. Copy and paste the following code into the module:
Sub ClearAllDataButFormulas()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim answer As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.InputBox("Enter Worksheet Name to Clear Data:", xTitleId, ActiveSheet.Name, Type:=2)
If ws Is Nothing Then
Set ws = ActiveSheet
End If
Set rng = Application.InputBox("Select the range to clear (leave blank for whole sheet):", xTitleId, ws.UsedRange.Address, Type:=8)
If rng Is Nothing Then
Set rng = ws.UsedRange
End If
Application.ScreenUpdating = False
For Each cell In rng
If Not cell.HasFormula Then
cell.ClearContents
End If
Next
Application.ScreenUpdating = True
MsgBox "All data (except formulas) have been cleared!", vbInformation, xTitleId
End Sub2. After adding the code, click the 
Run button (or press F5) to execute the macro. A dialog box will prompt you to enter the worksheet name where you want to clear data—if you wish to use the currently active sheet, simply press OK. Another prompt will ask for a specific range—if you leave this blank, the macro will process the whole sheet. The macro will then loop through your selection and remove all cell contents except those containing formulas.
Tips and Notes:
- This macro automatically avoids clearing cells with formulas, and works regardless of whether formulas reference other sheets or use array logic.
- If errors occur, check your input for worksheet names and range selection. Misentered sheet names or invalid cell references will cause the macro to skip the operation or affect undesired areas.
- For frequent use, the macro can be saved in your Personal Macro Workbook to be available in all future workbooks.
Clearing all data while keeping formulas in Excel is an essential skill for maintaining productive spreadsheet environments. Whether you prefer manual selection methods via Go To Special, leveraging AI-driven solutions from Kutools, or using automated VBA macros, each approach offers unique benefits for different user scenarios. Always review your worksheet after clearing data to ensure formulas remain accurate and your structure is preserved. If issues occur, double-check selected ranges and use the undo function promptly.
If you're interested in exploring more Excel tips and advanced automation, our website offers thousands of tutorials.
Related Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in