creating a template from an existing table

[adhocnyc]

so i've recently started a small retail business in new york.

i've been caught unawares that i have to pay my quarterly taxes. i had already created a table of all my transactions in table format. with formulas separating city an state taxes (it took the better part of a day, i'm rusty). but it works. i plan on compiling my transaction data into tables at the end of each month. so now i have table full of data but no template for future months.


Is there a way to copy the table onto a new sheet omitting the data but keeping all formulas intact?

 

[jeremymjp]

adhocnyc,


If the formulas you have in your table are consistent all the way down each column, you could just copy the headers and then one row, then clear out any non-formulas just in the first row.


I hope I understand your question...

 

[Elesario]

If you've got a lot of constant data intermixed with formulas, then perhaps the Goto Special dialog may help editing it.


Press Ctrl+G then click the Special button (or press Alt+S), the dialog you get allows you to pick the kind of data you want to select. So you could use it to select all formulas to copy and paste them elsewhere; or you might prefer to work on a copy of the original and select all constants to remove them.


Note that if you select an area before applying this then the new selection will be limited to only those that meet the criteria within that selection (so you can make sure it doesn't also select any headers you've set up)


In the long run it might be best to find a layout for your data and formulas that's consistent in the way that jeremymjp described, as it will be much easier to maintain longterm. Excel's Table features also work well with this layout and will auto extend your formulas for you, and you can add totals (if I recall correctly).

 

[Mike86]

It may just be me, but wouldn't a pivot table work for this? Worst case, you have to make a column for "Month". That way all the data stays in one worksheet and it'd probably be easier doing the year-end calculations.

 

[Jordan]

When you copy any cells, all formulas should be retained as is (so long as you do a regular paste and not paste special). If you simply copy the desired region and paste into a new spreadsheet (or tab) your formulas should remain intact, you'll just need to adjust your input data in the new region to reflect newer transactions.

 

[adhocnyc]

thanks for all the replies. i've tried them all, and they all seem equally easy and adequate for my needs.

i think i may choose them all, copying the headers to start a new pivot table for the new quarter, adding a column for the month.

basically one table per quarter


the only thing is, i still need to pull data monthly to pay out consignees (vendors who provide merch upfront and collect half at the end of the month) after the first month i would have to separate the table in to the current month then do a sort. maybe the pivot table resolves this, but i'm not familiar with its function.


thanks again, you guys are smart!