How to zero out all data fields in Microsoft Excel 2003 Spreadsheet

Posted on 2007-12-03
Last Modified: 2010-05-18
I have a customer with a Microsoft Excel Spreadsheet problem.  He  does a yearly budget.  When he gets to the end of the year he wants to keep the template for next year, with all the header's, etc, but with all the data fields zeroed out.  Is there any way to do this, still keeping the formatting, without having to zero each field individually?
Question by:alanlsilverman
  • 3
  • 3
LVL 59

Accepted Solution

Saurabh Singh Teotia earned 250 total points
ID: 20399141
Select the data where 0 need to be made...for need to be done from b coloumn row number 2 to l column till row number 600...

Once the data is selected...Do Ctrl+H

Then in find what just write * nothing else..

and then in replace with write 0  only..

This will do the trick for u...

Assisted Solution

chrisezard earned 250 total points
ID: 20399721
The quickest way to select the cells you need to clear is...
Highlight a sheet or range you want to clear. Press F5 (GoTo).  Press the 'Special...' button. Select 'Constants' and check the 'Numbers' check box. Now you have all the numbers that are not formulas highlighted. Press delete. Then do as saurabh726 suggests, pressing the 'Replace All' button.

Good Luck, Chris

Author Comment

ID: 20400322
Thanks, I'll give it a try.
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.


Expert Comment

ID: 20402177
A couple more points...
When you select 'Numbers' from the list of Constants make sure all the others are unchecked.
Either do NOT press Delete and then do Ctrl+H with * in top box and 0 in lower box.
Or DO press Delete and then do Ctrl+H with the top box empty and 0 in lower box.


Author Closing Comment

ID: 31412442
Worked like a charm.  Thanks to both of you.

Expert Comment

ID: 20486555
Thanks, Al
These are my first EE points! I am a dedicated asker and searcher of questions, mostly to do with Access VBA. The night you asked your question I was bored and had a look at open Excel questions, which I normally would not do. Your question stood out - as an accountant I've grappled with many budget spreadsheets in the past :)

Author Comment

ID: 20486642
Well congratulations on your first points.  They helped a lot (and made my customer happy.)
Thanks again,

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel VBA - Collection of checkboxes and labels on UserForm 8 26
Excel filter by strike though 8 25
Excel 3 22
Macro 3 19
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now