I have a workbook with some workbook-scoped named formulas. When I copy a worksheet that has some (different) worksheet-scoped names, the copy ends up also having worksheet-scoped names that are identical (other than scope) to the workbook-scoped names.

Example: Say the workbook has workbook-scoped name 'a', the worksheet has worksheet-scoped name 'b'. The copy of the worksheet ends up with 'b' (as expected), and a worksheet-scoped name 'a' that refers to the same formula that the workbook-scoped 'a' does.

I guess I haven't used named formulas enough to have noticed that this is how Excel works, but it's definitely not how I want it to work. Does anyone know how to copy sheets without spewing extra names everywhere? (This includes the related question of how to copy a worksheet from onw workbook to another without also copying over all the workbook-scoped names from the source workbook.)

2 Answers 2


For the sake of completeness, here is my answer from your follow-up question that ended up working:

You should be able to select all of the cells in the original worksheet, copy them, and then "paste special" (Edit > Paste Special) in the target workbook. From within the "paste special" dialog, you can specify only formulas, values, etc. and omit style information.

  • I'll accept this too unless there is a more direct answer soon. Thanks again!
    – jtolle
    Aug 3, 2010 at 14:46
  • 1
    As with my other question, this answer gets me what I need for convenience in my case. If I were trying to automate this and copy code behind the worksheet, etc., it's not complete. Note that copying cells seems to copy local defined names that are actually used by the copied cells, but not any other local names. (In my case, though what I care about is not creating a local copy of a workbook-scoped name, and this satisfies that need.)
    – jtolle
    Aug 7, 2010 at 15:32

In current versions of Excel you can easily remove all the names scoped to the new worksheet through the Name Manager.

Open the name manager using either:

  • Old shortcut: Alt, I, N, D
  • Ribbon: Formulas -> (Defined Names) Name Manager


  • Sort by 'Scope' by clicking on the 'Scope' header
  • Select the first name that refers to the sheet in question
  • Hold 'Control' and click the last name that refers to the sheet in question
  • Click 'Delete'
  • Click 'Close'

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.