Catalog

How to Copy an Excel Sheet with Formulas (Quick & Simple)

October 16, 2023 4.9K views

Microsoft Excel is a powerful tool that lets you effectively collect and monitor data. When people have a complex problem, re-entering it multiple times can be time-consuming. When people want to reduce copy-and-paste errors in Excel, they don’t know the most effective methods. So, how do you copy an Excel sheet with formulas? This article will provide helpful ways to do this.

How to Copy an Excel Sheet with Formulas?

To learn how to copy an Excel sheet with formulas, follow one of the 3 methods below:

Method 1: Use right-clicking (Suitable for all types of users)

Step 1: Right-click on the tab and select “Move or Copy” from the context menu.

Microsoft Excel with sheet tab selected and Move or Copy option selected in menu


Step 2: This will open the Move or Copy dialog box.

Move or Copy dialog box open in Microsoft Excel


Step 3: Under “Before sheet”, choose where you want to place the copy. Put a tick in the “Create a copy” box then, select “OK”.

“New sheet tab selected along with Create a copy box and OK button in Move or Copy dialog box”


Step 4: Select “OK”.

“New sheet tab open with copied data”


Method 2: Using the clipboard group (Suitable for beginners)

The Clipboard section contains all the functions you need to copy and paste values in Excel. It sits in the “Home” tab of the ribbon.

Ribbon in Microsoft Excel with Clipboard section selected in Home tab

You can use the “Scissors” option to cut data and the “Two Sheets” option to copy the data.

Clipboard section showing cut and copy options

The “Clipboard” icon is the paste button that holds all the copied data. The “Paint Brush” icon below is the “Format Painter,” which lets you copy the formatting.

“Clipboard section with the clipboard and th paint brush selected”

And the options don’t end here – Click on the arrow in the bottom right corner to view more copy/paste options.

Arrow indicating where to click with additional copy and paste options showing

Method 3. Use drag-and-drop (Suitable for masters)

Usually, you drag and drop to move something from one place to another. But this method also works for copying tabs and is, in truth, the fastest way to copy a sheet in Excel.

, click on the sheet tab that you want to copy, hold the “Ctrl” key, and drag the account where you want it:

Alt: “Microsoft Excel showing how to copy an Excel sheet using drag-and-drop”

This image can be found here

Timesaving Tips for Copying an Excel Sheet with Formulas

If you want time-saving tips to know how to copy an Excel sheet with formulas, then simply take a look at the pointers provided below:

1. Use Copy and Paste

To quickly copy an entire sheet with formulas, right-click on the sheet tab, select “Move or Copy,” choose the target workbook, and check the “Create a copy” checkbox. That duplicates the sheet, including all formulas, in a straightforward step.

2. Copy Multiple Sheets

If you need to copy multiple sheets with formulas, hold the Ctrl key and select the desired sheet tabs. Right-click on one of the selected tabs, choose "Move or Copy," and follow the above steps to duplicate the selected sheets.

3. Copy Formulas Only

After selecting the range, press Ctrl+C to copy formulas without any cell reference adjustments. Then, use "Paste Special" (Ctrl+Alt+V) and choose "Formulas" to paste the formulas directly without altering their references.

4. Use Drag and Drop

Instead of copying and pasting, you can also drag and drop cell formulas. Select the cells you want to copy, hover over the border (not the corner) until the cursor turns into a 4-sided arrow, and then drag the selection to the desired location. This method preserves the formulas without the need for additional steps.

5. Use Named Ranges

By assigning named ranges to your formulas, you can easily copy and paste them to other areas of the sheet or even to other sheets. This way, the formulas will automatically adjust to refer to the correct cells based on the named ranges, saving you time and effort in updating cell references.

6. Lock Cell References

If you want to copy formulas and keep specific cell references fixed, you can use the "$" symbol to lock them in place. For example, if you have a formula referencing cell A1 as "=A1B1" and want to keep A1 fixed while copying, change the formula to "=$A$1B1". This way, the reference to A1 won't change when you copy the formula.

WPS Office to Edit Excel, Word, and PowerPoint for Free

WPS Office is an incredible tool with various features and functions, making it an ideal choice for users. One of its top advantages is that most of its features are free! That provides a cost-effective solution for individuals and businesses while reducing the financial burden.

Its strong compatibility with Microsoft Office formats ensures smooth document exchange and easy sharing with others. With its user-friendly interface and user-friendly experience, people who struggle with Excel can easily use WPS Office.

That makes it an appealing choice for professionals and novices, ensuring effective productivity for all levels of users.

How do you copy an Excel sheet with formulas in WPS?

To learn how to copy an Excel sheet with formulas in WPS, follow these simple steps:

Step 1: Select the copied sheet and press "Ctrl+H" (Windows) or "Command+H" (Mac) to open the "Find and Replace" dialog box.

Find and Replace dialog box in WPS Spreadsheets with dataset in the background

Step 2: In the "Find what" field, type “Gadget”, and in the "Replace with" field, type “Gadget” again. Then, select “Replace All” to copy the formulas to the new sheet.

Find and Replace options in Find and Replace dialog box in WPS Spreadsheet

Step 3: Thereafter, all the cell references in the copied sheet will be updated to refer to the correct cells in the new location.

Replace all option with changed requirements in WPS Spreadsheets

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

  1. Will my cell references update automatically after copying?

Yes, cell references (e.g., A1, B2) in your formulas will automatically update when you copy the sheet. That ensures that your calculations remain accurate.

  1. How do you lock formulas in multiple cells at the same time?

Step 1: Select the cell reference you want to lock.

Step 2: Press the “F4” key on your keyboard. That will add the “$” symbol to the cell reference.

Step 3: Copy and paste the formula to other cells. The locked cell reference will not change.

  1. How do I copy and paste Excel so it is the same?

Press “F2” on your keyboard or double-click the cell to enter the editing mode. From there, choose the formula in the cell using the mouse, then press Ctrl+C to copy it. Choose the destination cell and select Ctrl+V. That will then paste the formula precisely without changing the cell references because the procedure was copied as text.

Summary

In closing, understanding how to copy an Excel sheet with formulas is a skill that can quickly be learned. Knowing how to copy an Excel sheet with formulas can significantly enhance your productivity and speed up your workflow so that you don’t constantly have to go back and forth.

The best tool to use for spreadsheets is WPS Offices. It is an excellent tool compatible with Microsoft Office and with a friendly and intuitive user interface. Users are assured of satisfactory results with the benefits provided by the WPS Office.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.