How to Copy and Paste Data with Formulas from Google Sheets to Excel

This post explores copying and pasting data with formulas between the popular spreadsheet applications Excel and Google Sheets.

What typically happens when copying and pasting data with formulas between two different spreadsheet applications?

You may lose your formulas. The pasted range will contain formula results in cells containing formulas. However, the underlying content will not be the formula but the value itself.

Is there a solution to this problem? Yes, there is. Here is the solution for copying and pasting data with formulas from Google Sheets to Excel and vice versa.

Copy and Paste Data with Formulas from Google Sheets to Excel

Here is the correct method for copying data from Google Sheets and pasting it into Excel while retaining formulas.

In Google Sheets (Source)

Copy and Paste from Google Sheet to Excel with Formulas
  • Go to Google Sheets and open the spreadsheet containing the data you want to copy.
  • Click on the menu View > Show formulae (you can also use the keyboard shortcut Ctrl + ` [back quote]).
  • Select the cell range containing the data you want to copy.
  • Click on Edit > Find and Replace (Ctrl + H in Windows and ⌘ + Shift + H in Mac).
  • In the ‘Find’ field, enter the = sign.
  • In the ‘Replace with’ field, enter '= (equal sign followed by an apostrophe).
  • Check “Also search within formulae.”
  • Click Replace all, then Done.
  • Use one of the following methods to copy the data:
    • Press the keyboard shortcut Ctrl + C (Windows) or ⌘ + C (Mac).
    • Right-click on the selected cell range and choose “Copy” from the context menu.
    • Click Edit > Copy.

In Excel (Destination)

  1. Launch Microsoft Excel and open the spreadsheet in which you want to paste the copied data from Google Sheets with formulas.
  2. Move to the cell where you want to paste the copied data. If the selected range in Google Sheets is B2:C5, move to cell B2 in Excel.
  3. Use one of the following methods to paste the copied data:
    • Press Ctrl + V (Windows) or ⌘ + V (Mac).
    • Right-click on the destination cell and choose one of the paste options (keep source formatting or keep destination formatting) from the context menu.
    • Navigate to the “Home” tab, find the “Paste” button, and click on it.

Back In Google Sheets (Source)

Return to Google Sheets and click on “Edit > Undo” (Ctrl + Z in Windows and ⌘ + Z in Mac) to reverse the last action.

Additionally, apply Ctrl + ` to turn off the “Show Formulas” view.

This is the working method to copy data with formulas from Google Sheets to Excel.

Copy and Paste Data with Formulas from Excel to Google Sheets

Copying and pasting data with formulas from Excel to Google Sheets is a straightforward process.

Follow these steps:

Show Formulas in Excel
  1. Show all formulas in Excel by using the keyboard shortcut Ctrl + ` or by clicking on “Show Formulas” under the “Formulas” tab in the “Formula Auditing” group.
  2. Copy the data in Excel.
  3. Open Google Sheets and navigate to the desired location.
  4. Paste the data. Ensure that the copy range in Excel matches the paste range in Google Sheets to maintain correct formula results.

By following these steps, you can seamlessly transfer data with formulas from Excel to Google Sheets.

Conclusion

While many Excel functions are available in Google Sheets, not all functions may have a direct counterpart, and vice versa.

It’s important to note that some functions share the same name in both Excel and Google Sheets but may operate slightly differently. Consequently, errors or incorrect results may occur when you copy and paste data with formulas between Excel and Google Sheets.

Be mindful of these differences and, when transferring data with formulas, verify and adjust as needed to ensure accurate and consistent results in the destination spreadsheet.

You May Like: Google Sheets Function Guide [Quickly Learn All Popular Functions]

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

6 COMMENTS

  1. After “show formula” in Excel as described above, copy the cells into a google WORD doc with paste — no formatting. From there you can paste the formulas into Excel.

  2. Unfortunately it doesn’t work anymore (Google Sheets as of 11/3/2017, Excel 2016). Just copies a 0 into any place where a formula was that references any other cell. (Purely mathematical formulae that don’t reference other cells do seem to copy in OK)

    • Hi Peter,

      I’ve also noticed that. It’s now working like this. But only possible if you have access to Excel Online.

      1. On Google Sheets, use the short cut Ctrl+~ (Show formula)
      2. Copy the formula, then paste it into Excel Online.
      3. Copy from Excel Online and open Excel Offline.
      4. There also use the keyboard short cut Ctrl+~ (Show formula)
      5. Paste the content copied from Excel Online.
      6. Again apply keyboard short cut Ctrl+~ (Show formula)

      Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.