?
Solved

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

Posted on 2007-12-03
7
Medium Priority
?
883 Views
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?
Thanks,
Al
0
Comment
Question by:alanlsilverman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 1000 total points
ID: 20399141
Select the data where 0 need to be made...for example...zero 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...
0
 
LVL 1

Assisted Solution

by:chrisezard
chrisezard earned 1000 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
0
 

Author Comment

by:alanlsilverman
ID: 20400322
Thanks, I'll give it a try.
Al
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 1

Expert Comment

by:chrisezard
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.

Chris
0
 

Author Closing Comment

by:alanlsilverman
ID: 31412442
Worked like a charm.  Thanks to both of you.
Al
0
 
LVL 1

Expert Comment

by:chrisezard
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 :)
Chris
0
 

Author Comment

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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

762 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