Solved

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

Posted on 2007-12-03
7
868 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
  • 3
  • 3
7 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 250 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 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
0
 

Author Comment

by:alanlsilverman
ID: 20400322
Thanks, I'll give it a try.
Al
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXCEL formula that pulls formatting as well 12 46
Getting rid of #VALUE! 7 23
V-Lookup 11 19
Excel Sheet Data Finding 14 17
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). 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 …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

860 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