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)

• 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:

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]