How To Clear All Data From Excel Spreadsheet

How to Clear Spreadsheet of All Numerical Data

You have a large Microsoft Excel worksheet that tracks numbers and/or dates. To reuse this worksheet, you need to quickly delete all the data that was entered, but keep the formulas and text. Is there a fast way to do this in Excel? Yes!

○ This tutorial contains affiliate links. Read our disclosure policy to learn more. ○

We create a lot of financial worksheets. At the beginning of the new month or year, numerical data must be deleted so we can start afresh. Some sheets are large, so keep reading to learn an efficient way to quickly delete all numerical data.

A few notes before we begin. Keep in mind that in Excel, a date is stored as a number, though you can format a cell to display the date in various ways. Also, if you want to delete ALL literal values, i.e. everything but formulas and headings, see our separate tutorial How to Delete Data in All Unprotected Worksheet Cells for instructions.

A KeynoteSupport.com Tutorial

Follow these steps to have Excel quickly delete all numbers (literal values).

Step 1. Unprotect the worksheet if it's protected. Click the Review tab and click Unprotect .

Step 2. Open Excel's Go to Special dialog window in one of two ways (see screenshots):

On the Home tab, click Find & Select and click Go to Special...
Press the F5 key to open the "Go to" window, and click Special .

Display the Go to Special window via ribbon or keyboard shortcut

Step 3. In the Go To Special dialog box, click the radio button next to Constants and un-check everything in the list except for Numbers . See image below.

Step 4. Click OK. Excel selects the spreadsheet cells whose contents will be deleted, as shown below. The top left cell IS selected, but not be gray like the other selected cells.

Step 5. Review the cells whose values will be deleted. If all is well, press the Delete key and the data is deleted.

data deleted on the Display the Go to Special window

A KeynoteSupport.com Tutorial

Summary

There may be a stray bit of text here and there that also needs to be deleted, depending on the nature of your worksheet, so take a glance before saving the worksheet.

If you regularly reuse a spreadsheet whose formulas and text NEVER change—just data entry values, use a master spreadsheet. The master sheet will contain only formulas and static text, and should have MASTER in its file name. Then just copy and rename this file each season.

If you look at the Go to Special dialog box shown in Step 3, you'll notice that you can also delete only the formulas in a worksheet.

We hope this article on deleting all of a worksheet's numerical input values has been helpful. Cheers!

↑ Return to the top