Copy formulas without data.

I have a complex spreadsheet that has many formulas and much data. It was 
not well designed, but I inherited it. Now I have to start the new fiscal 
year and I want to delete all the data, but leave the formulas, headers and 
such intact. Is there a way to do this? Deleting cell by cell is going to be 
a big project.

---Mike



0
1611kjb (4)
7/8/2005 8:19:20 PM
excel 39879 articles. 2 followers. Follow

4 Replies
663 Views

Similar Articles

[PageSpeed] 24

You should be able to hit Edit-->Go to-->Special-->Constants. Then hit your
delete key.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Praising Jesus" <1611kjb@earthlink.net> wrote in message
news:elOCVp$gFHA.2880@TK2MSFTNGP09.phx.gbl...
> I have a complex spreadsheet that has many formulas and much data. It was
> not well designed, but I inherited it. Now I have to start the new fiscal
> year and I want to delete all the data, but leave the formulas, headers
and
> such intact. Is there a way to do this? Deleting cell by cell is going to
be
> a big project.
>
> ---Mike
>
>
>


0
ng1 (1444)
7/8/2005 8:28:42 PM
You are too brilliant!

Thanks,

---Mike


"Anne Troy" <ng@officearticles.com> wrote in message 
news:d0465$42cee1fa$97c5108d$19035@allthenewsgroups.com...
> You should be able to hit Edit-->Go to-->Special-->Constants. Then hit 
> your
> delete key.
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "Praising Jesus" <1611kjb@earthlink.net> wrote in message
> news:elOCVp$gFHA.2880@TK2MSFTNGP09.phx.gbl...
>> I have a complex spreadsheet that has many formulas and much data. It was
>> not well designed, but I inherited it. Now I have to start the new fiscal
>> year and I want to delete all the data, but leave the formulas, headers
> and
>> such intact. Is there a way to do this? Deleting cell by cell is going to
> be
>> a big project.
>>
>> ---Mike
>>
>>
>>
>
> 


0
1611kjb (4)
7/8/2005 9:38:02 PM
Perhaps to drastic for things with existing descriptions,  you might
want to first make a selection of the columns to be affected and then
use the   Edit --> Goto --> Special --> Constants,   Del key
I would think you have descriptions in at least column A.
In any case you do have to make a selection first even if it is Ctrl+A,
anything is preferable to letting Excel make a choice for you.

If you need to insert or delete rows when you are updating you might take a look at
 http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm


"Anne Troy" <ng@officearticles.com> wrote in message ...
> You should be able to hit Edit-->Go to-->Special-->Constants. Then hit your
> delete key.
>
> "Praising Jesus" <1611kjb@earthlink.net> wrote bl...
> > I have a complex spreadsheet that has many formulas and much data. It was
> > not well designed, but I inherited it. Now I have to start the new fiscal
> > year and I want to delete all the data, but leave the formulas, headers
> and
> > such intact. Is there a way to do this? Deleting cell by cell is going to
> be
> > a big project.
> >
> > ---Mike
> >
> >
> >
>
>


0
dmcritchie (2586)
7/8/2005 9:38:23 PM
I've been using Excel since 1.0 and I've been using PC's since 1983 - and 
it's amazing what I don't know. Thanks!

---Mike


"David McRitchie" <dmcritchie@msn.com> wrote in message 
news:euRzVWAhFHA.3544@TK2MSFTNGP15.phx.gbl...
> Perhaps to drastic for things with existing descriptions,  you might
> want to first make a selection of the columns to be affected and then
> use the   Edit --> Goto --> Special --> Constants,   Del key
> I would think you have descriptions in at least column A.
> In any case you do have to make a selection first even if it is Ctrl+A,
> anything is preferable to letting Excel make a choice for you.
>
> If you need to insert or delete rows when you are updating you might take 
> a look at
> http://www.mvps.org/dmcritchie/excel/insrtrow.htm
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
>
> "Anne Troy" <ng@officearticles.com> wrote in message ...
>> You should be able to hit Edit-->Go to-->Special-->Constants. Then hit 
>> your
>> delete key.
>>
>> "Praising Jesus" <1611kjb@earthlink.net> wrote bl...
>> > I have a complex spreadsheet that has many formulas and much data. It 
>> > was
>> > not well designed, but I inherited it. Now I have to start the new 
>> > fiscal
>> > year and I want to delete all the data, but leave the formulas, headers
>> and
>> > such intact. Is there a way to do this? Deleting cell by cell is going 
>> > to
>> be
>> > a big project.
>> >
>> > ---Mike
>> >
>> >
>> >
>>
>>
>
> 


0
1611kjb (4)
7/10/2005 2:27:41 AM
Reply:

Similar Artilces:

Estimated Data size for 3-5 years
Dears, I was looking for a document that give me an idea or talking about how to estimate Microsoft Dynamics GP database Growth and size. Any one could help me in this? I found an article on MSDN but it is about SQL server and it needs to go throw all GP tables and finde the size of each field and calulate that number. I have the following GP system: POP module FA module And Financial Module Any help? Best regards, Hi Hasan, It really depends on the number of transactions that go through your system. If I had to guess at an average, I would say a database in use for 3-5 years would ...

Paste damages formula in autofilter #2
Formula is merely an if if(B2 > 0,1,0) could also probably use countif nb: I can't use a summation here as some of my macro's use differen filters in combination with subtotal and sums alread -- shaw ----------------------------------------------------------------------- shawb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1420 View this thread: http://www.excelforum.com/showthread.php?threadid=26268 Hi if this formula is ion column C (cell C2) try: =IF(OFFSET(C2,0,-1)>0,1,0) or --(OFFSET(C2,0,-1)>0) But I'm quite sure you could use a ...

Formula Help Needed #3
I'm creating a volunteer list for a nonprofit group I belong to. I've been experminting with counta, dcountf, countif, ect...., but no luck yet. On the first worksheet I have all of the volunteer's names, phone number's, ect... Columns are the catagories and the rows are the volunteers. There is a series of four columns representing shifts. The column immediately following the shift columns indicates which work area of the event they have volunteered for. This column is validated and has a drop down list of predetermined choices. Currently I'm trying to create a table l...

Data base
I need to know as the table is called where registo keeps from the TPV arelysas HI I think you are talking about the Configuration table which store the information of store also there is table name called "register" please describe your main purpose and what exactly you are asking. Hope this will help you. "arelysas" wrote: > I need to know as the table is called where registo keeps from the TPV > > arelysas ...

how to show formula in formula bar
How do I make the formula show in the formula bar and keep it there? Select the cell with the formula. HTH, Bernie MS Excel MVP "cattywampus" <cattywampus@discussions.microsoft.com> wrote in message news:1B2F42DF-9B43-411C-96C1-D3110F033D0C@microsoft.com... > How do I make the formula show in the formula bar and keep it there? go to tools<>options<>view<> and tick the formula bar -- Greetings from New Zealand Bill K "cattywampus" <cattywampus@discussions.microsoft.com> wrote in message news:1B2F42DF-9B43-411C-96C1-D3110F033D0C@mi...

How to Trap Rogue Data ?
I have an application that generates hourly system performance logfiles which I graph to look for long term trending. The metric I use gradually varies from 1% to about 15% depending on various external factors - such as time of day and day of week. My problem is that the logfiles sometime hiccup and generate bad data resulting is huge spikes in my curve. I have trapped for the big ones > 20% in my source data but I need something smarter so I can catch large deviations from the curve. Unfortunately I do not have the option to fix the application that generated the bad data. Are ther...

Need Help With Formula #3
I'm trying to set up a formula using values on one worksheet to sum up values on a second worksheet. The first worksheet has these 3 columns, A, B and C where A has these team/unit numbers (often repeating), B has one of 5 text values, while C has a yes or no value aka "accepted" or "rejected". Referring to sheet one, on the second sheet, one formula sums up all the times that a team/unit has a value in B other than " ". The second formula sums up all the times that a team/unit has "Accepted" in column C. I haven't been able to figure this o...

Copying portion of page
This was discussed a while back, but for some reason, I can only access back to June. There was some sort of little "camera" icon in Publisher, and I have used it (to copy only part of a page), but now I cannot find the icon. How can I restore/find it? (I have MS Office 2003). TIA Joy wrote: > This was discussed a while back, but for some reason, I > can only access back to June. There was some sort of > little "camera" icon in Publisher, and I have used it (to > copy only part of a page), but now I cannot find the > icon. How can I restore/find it...

XLS2000 IE6 No Data Sort
I've got users with IE6 and Office 2000 who are opening XLS sheets from the web. From the IE browser the Data menu appears but is not functional. I attemped to unlock the toolbar and move it so that none of the menus were covered up thinking this might help. Office XP (2002) with IE 6 works just fine, problem is I have 1500+ users on Office 2000. Can anyone offer any suggestions? I've check permissions on file, users rights on PC and Office/OS updates. Craig ...

Formula Help #58
I can't seem to remeber how to do this simple formula... I know that 661.2 is 11% of X number. How do I figure out what the total of X is? Thanks! -- babz ------------------------------------------------------------------------ babz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27810 View this thread: http://www.excelforum.com/showthread.php?threadid=473177 Hi, maybe X=661.2/11*100 J -- pinmaste ----------------------------------------------------------------------- pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&...

Can't see any data in Workflow Monitor
Hey there, Have been finalising my server install and re-adding Opportunities to the newer Workflow. But as doing this I now cannot see any data records whatsoever in the Workflow Monitor. It tells me how many items are Active/Paused ..etc in the Summary creen, but when I got to have them listed no data appears and claims that there are no Active/Paused ..etc records? No idea as to why???????? ...

seeing the formula (not the cell)
Hi All, Suddendly, I have this strange behaviour. I have a cell say C3 which contains 2b as a text. Then on cell I4, I want to se the content of cell C3. on cell I4, I used to type =C3, then on I4, I would see 2b. What is strange now, is that on I4, I see =C3 (the "formula" and not the result). What is more surprizing, is that on this excell file, I have 2 sheets. On the first sheet, referencing works fine. On the second sheet, I can see only the formula. What do I do to not see the formula, but the resut ? I don't remeber touching any option. Regards Jean Simon Your cel...

Journal Entry Data Dump
Our auditors want a data dump (into Excel preferably) of all Journal Entries made during our fiscal year. Does anyone know of a way to accomplish this? Thanks! Kurt, You can do this in SmartList, under Account Transactions. Limit the transaction date range to get just your fiscal year. Remember to change the number of transactions SmartList brings back, since by default it's only 1000. Also, if you get more than about 65,000 transactions back, you will run into Excel limitations, so you may want to break this up further - maybe one export per month. Hope that helps. -- Vict...

Importing Data from Access -- Parameter Errors
I am attempting to import a query from access. I get to the point of view or saving query in excel and get message "Too few parameters, expected 4." There are several parameters in my query. How do I avoid this message and get my query into excel? Does your query work properly if you invoke it directly in Access? And must the user supply the values of the parameters directly, or can those values be picked up from a worksheet? I assume you're using Microsoft Query to return the data from Access. Microsoft Query is not as powerful as Access (and thus any other true relationa...

Restricting query data
I have a query set so I pull DISTINCT dates in the SQL from 1/1/08 thru 12/31/08 to take out duplicate accounts, this works great when I run the report quarterly or yearly but I need to run this report monthly. I have 3 tables that work together and these dates are coming from my Activities table, I keep track of when I have had contact with them. I am looking to only count these people 1 time in a year (filter by start date does not work is it is driven by actibvity date) for example if I work with them in 1/08 run my report in 1/08 they should show up. If I work with them in 2/0...

how do I copy my calendar in office 2010?
I am trying to make a copy to back up on an external hard drive but cant find how to do this. I dont want to delete info. Find the pst and make a copy. By default, outlook 2010 puts them in the documents folder. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Poll: What version of Out...

How do I count unique items in a pivottable data field.
I have a pivottable and I am trying to setup a data field that will count unique values. ie. Listing customers as the row and setting up a field to count the number of sales people that sold to the customer. The base data has multiple rows identifying the customer, sale id, $ and sales person. If I have 100 rows with sales person A, B & C each selling multiple orders to several customers I want the field counter to tell me if 1,2 or 3 sales people sold to each customer. Not the most elegant answer, but I'd probably create my pivot with Customer in the row field, sales rep...

Formula help please!!!!!!!!!!!
Hello. I am doing a spreadhsheet to keep track of attendance in my classroom. I have the students names in column A7-A40. I am looking to see how can I add up how many "A"'s (for absent) a particular student got over the course of a trimester. I have a combo box on a second page at B2 and a box that adds up the number of "A's". In each column on the first page I have three boxes stacked on top of each other- which trimester the date is in, the date, and then an "A" for absent or "L" late. It looks like this Column 5 ...

Copy row based on cell content
Hi I have a workbook with about 100 sheets; they are all formatted the same as far as rows, columns, etc. The data in the cells is different. I would like to copy a range of rows based on the contents of a cell on the worksheet. To further explain, I have one of three possible values in cell A1. Based on this value, I want to copy the range of A5:M13 to another sheet, named the same as the cell. I need to repeat this process for all of the sheets in the workbook. I would like to automate the process because the sheets are going to be updated often, and I would need to revise this "...

32,000 data points error message
I keep getting the following pop up error message as I'm creating a line chart. The source data includes two colums with nearly 900 rows of data. Basically the data is temperature readings from two probes (coulmn A&B) at 1 second intervals (900 rows = 15 minutes of data) There's also a 3rd column that has 60 points of data, but this colums in only used to provide the data for the X axis labels. So by my calculation, I have less than 2000 data points, correct? Why the error message? This is what the message says; "The maximum number of data points you can use in a da...

Cell with data validation allows blanks, pastes, deletions
Thanks mzehr, this appears to stop anything other than what you specif being entered, even though for it to work I have to add everything i my list into the macro. But how do I stop a deletion or a blan entry? Fuzz -- fuzzyfrea ----------------------------------------------------------------------- fuzzyfreak's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1465 View this thread: http://www.excelforum.com/showthread.php?threadid=26275 Fuzzy, For that you will probably want to consider forcing the user to input the information into a user for. For starters ...

Ability to copy Fixed Assets.
Need the ability to copy Fixed Assets. If I purchase 7 trucks, I don't want to create the asset 7 different times manually - should be able to copy. Customer - Convergence 2005. -- This posting is provided "AS IS" with no warranties, and confers no rights. ...

using excel form to input data into access
Is it posible to use an excel spreadsheet to enter data into access? please reply to group You can link access to Excel. But why? before going any further, explain the reason why you would want to do this. Let us evaluate what your needs are. Access Forms are much better for data entry, for a number of reason, than Excel. What would you use acces for then if your data is being entered and stored in Excel? Explain some more so we can guide you better. -- Hope this helps, Daniel P "billb" wrote: > Is it posible to use an excel spreadsheet to enter data into acces...

Charting data against dates where dates are not at fixed intervals
I have several data points that are the result of a pivot-table and I'm trying to plot them in a pivot-chart (I can easily get them out of the pivot table if the graph format is not supported for pivot-charts). Here's an example of the data: 2005/01/15 2005/02/22 2005/03/18 2005/05/20 X 16 18 22 12 Y 4 6 3 2 Z 14 19 12 34 What I'd like to do is plot the data (preferably on a line chart) such that ...

open database to input data
When a user opens the file for the database in Windows, how do we make it so the form for inputting data is already pulled up, so the user will not have to remember which form we want? One of the easier methods is to use the AutoExec macro. Create a macro; name it "autoexec"; then set its actions to open the form you want; and maybe minimize the database window, hide the toolbars, etc. When the mdb is opened, the autoexec macro is called and all its actions are run. A slightly more involved approach would be to create a Switchboard type form to serve as an on-scree...