Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel spreadsheet with multiple worksheet tabs for collecting data... and a final tab with a lot of formulas, to summarize the other tabs for a given date range.

The goal is for the person doing data entry to keep this "master spreadsheet"... and distribute copies of only the summary tab.

You can't simply cut-n-paste the cells from that tab into a separate spreadsheet (or use "Edit->Move or Copy Sheet")... because that will copy over all the formulas. Those formulas will break when opened on another computer that doesn't have master spreadsheet.

On the other hand, using "Edit->Paste Special" and selecting only "Values" has problems too. This approach strips away not only the formulas, but also cell formatting (and there is quite a bit of formatting).

Is there any way to copy over cell values AND cell formatting from one worksheet tab to a separate spreadsheet file? I just want a visually-pleasing snapshot of that summary tab for distributing to other people.

share|improve this question

6 Answers 6

up vote 0 down vote accepted

Can you not make a template of the destination "summary" sheet and format it the same as the sheets you're taking the data from? so when you paste-special as values your destination sheet is already formatted and you don't need to copy across the formatting.

share|improve this answer
    
Well I'll be! It never occurred to me to use a pre-formatted blank spreadsheet for the destination. I saw that "Paste Special"->"Values" was resulting in a plain vanilla spreadsheet... so I just assumed that the operation would overwrite any formatting that was there (rather than conforming to it). Thanks! –  Steve Perkins Jan 27 '11 at 15:37

5 easy steps:

  1. Copy summary worksheet 'as is' to a new workbook.
    • Right-click worksheet tab
    • Move Or Copy
    • New WorkBook, Create a Copy
    • OK
  2. Select all (CTRL+A)
  3. Copy (CTRL+C)
  4. Paste Values (over itself)
  5. Save and distribute
share|improve this answer

I need to do this quite frequently. I've found the easiest way is to do a normal paste followed by a Paste Special -> Values in succession. I use the keyboard shortcuts so it is a very quick process.

share|improve this answer

In the first sheet:

1. Copy

In the other sheet:

2. Paste
3. PasteSpecial and choose only value.

Step 2 to copy formulas and format settings. Step 3 to overwrite formulas by values.

share|improve this answer

Alternatively, once you've done Paste Special -> Values, do Paste Special -> Formats immediately.

The destination range should be the data you've just copied over, so as long as you don't move or hit escape (to clear the copied information) you should be fine.

share|improve this answer

I like to use an add-in called ASAP UTILITIES http://www.asap-utilities.com/ You can do lots of things with it--and one of the things is to CONVERT ALL FORMULAS TO THEIR VALUES which I find very useful. Doesn't touch the formatting.You might leave the master alone, make a copy of the updated summary, convert formulas to values, and then distribute that page.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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