How to Reset an Excel Spreadsheet for Data Entryby
From time to time you may want to remove words or numbers from Excel worksheet cells while keeping formulas intact. This can happen when a user inadvertently forgets to save a worksheet as a new copy.
In this article I’ll explain how to reset an existing workbook, but also show you a way that you can minimize the need to use this technique in the future. As shown in Figure 1, to remove text or numbers from a worksheet:
- Select the cells that contain the information you wish to clear. This block of cells can include formulas, which is often what poses the challenge for many users.
- Choose Find & Select from the Home tab.
- Choose Go To Special.
- Double-click on Constants to skip the OK button within the Go To Special dialog box.
- Press the Delete key to clear the cells but keep the formatting intact. No formulas within the range you selected will be affected.
Figure 1: Excel’s Go To Special command enables you to strategically select data entry cells.
Once your spreadsheet is restored to pristine condition, there are a couple of approaches you can take to protect the spreadsheet in the future. The first involves using an often-overlooked menu within Excel’s Open dialog box. To begin, first take whatever steps necessary in your version of Excel to get to the Open dialog box and then:
- Click once on the file you wish to open for data entry.
- Click the arrow next to the Open button.
- Choose Open As Copy from the resulting menu.
As shown in Figure 2, your workbook will open with a new name that includes the words Copy Of so that if you reflexively save the workbook, your master copy will be protected.
Figure 2: The Save As Copy command enables you to preserve a master copy of a spreadsheet.
Another approach involves saving your workbook as a template. This makes it easier to prevent users from saving over your file as templates automatically get assigned a new name. To do so, take whatever steps are needed within your version of Excel to get to the Save As dialog box:
- Change the name of your document in the File Name field if needed.
- Choose Template from the Save As type list.
- At this point, Excel will redirect you to a special Templates folder on your computer. You can actually save templates anywhere you like, such as on a shared network drive, so change the folder if desired.
- Click Save.
Figure 3: Save your workbook as a template to prevent users from saving over your file.
There’s a bit of nuance involved in working with templates. If you’re using Excel 2013 and earlier, when you choose File, New, you’ll then see a version of the My Templates button. Click on this to display a dialog box that shows all of the templates that you’ve saved. Doing so ensures that you’ll always create a copy of the template, as opposed to the template itself.
In Excel 2016 you must first inform Excel where your templates are located. To do so, open a Windows Explorer or My Computer window, and then in the address bar, type the following text and press Enter:
This will expand into a “normal” folder address, as %appdata% is a shortcut to the AppData folder on any computer. Copy the full address that appears to the clipboard and then in Excel 2016:
- Choose File.
- Choose Options.
- Choose Save within the Excel Options dialog box.
- Right-click in the Default Personal Templates location folder.
- Choose Paste.
- Click OK.
Figure 4: Set up your template folder location in Excel 2016.
Now when you choose File, and then New in Excel, you’ll see links for Featured and Personal. Click Personal to access your list of templates that you’ve saved.
Alternatively, in any version of Excel, you can double-click on a template file from a My Computer or Windows Explorer window. Doing so will launch a copy of the template with a generic name. If you wish to modify the template in any way, browse to the template by way of Excel’s Open dialog box. You can open templates in the same fashion as any other Excel workbook.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.