I am composing Excel document with my task-tables. It contains tasks, divided over several scopes. Tasks from every scope should be displayed in separate sheet. In every sheet, associated with a scope, would be a task-table with the same columns and format as other's scopes tables.

So I want to have a tamplate of such table, from which would be generated table headers and fetched column properties. I want to have only one place where i can change table's header style or column properties, and they would be applied to all task tables.

So the question is:

  1. Is there functionality in excel to share entire rows through sheets?
  2. Is there a way to copy cells or columns format (and other properties) using cells formulas?
  3. In which way i can make a sheet, that aggregates rows from all sheets in one table?

It would be greate to have scripts or documents examples.

Thank you.


Since in several parts and with numerous answers hopefully a wiki answer is appropriate.

1. Share rows through sheets

Group all the relevant sheets together (eg hold down Ctrl and click on the tabs one by one for a specific selection, or click on the first tab, hold down Shift and click on the last tab for all tabs). Then anything you do on one sheet is repeated throughout.

2a. Copy cells using cell formulae

To copy A1 within the same sheet use =A1. To copy A1 from Sheet1 into a different sheet select A1 and Paste Special, Paste Link, or use =Sheet1!A1.

2b. Copy cell’s format (and other properties) using cells formulae

Maybe use Format Painter (Home > Clipboard - ).

3. Aggregate rows from all sheets

Maybe use Consolidate (Data > Data Tools - ).

  • You described steps of manual copying of table headers. But i want to do it automatically. I want to have one sheet for table template, and copy it's headers rows to other sheets. If i change template, other tables would be changed. – Architect Sep 30 '13 at 8:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.