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

Have you ever tried copying and pasting data with formulas between different spreadsheet applications, like Google Sheets and Excel? If so, you may have encountered a common issue – losing your formulas. When you paste the data, the formulas are replaced with the values. But fret not, there is a solution! In this article, we will explore how to copy and paste data with formulas from Google Sheets to Excel and vice versa.

Copy and Paste Data with Formulas from Google Sheets to Excel

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

In Google Sheets (Source)

1. Go to Google Sheets and open the spreadsheet that contains the data you want to copy.
2. Click on the menu “View” and select “Show formulae” or use the keyboard shortcut Ctrl + ` (back quote).
3. Select the cell range containing the data you want to copy.
4. Click on “Edit” and choose “Find and Replace” (Ctrl + H in Windows and ⌘ + Shift + H in Mac).
5. In the “Find” field, enter the equal (=) sign.
6. In the “Replace with” field, enter an equal sign followed by an apostrophe (‘=).
7. Check the box that says “Also search within formulae.”
8. Click on “Replace all” and then “Done.”
9. Use one of the following methods to copy the data:
   • Press Ctrl + C (Windows) or ⌘ + C (Mac) on your keyboard.
   • Right-click on the selected cell range and choose “Copy” from the context menu.
   • Click on “Edit” and select “Copy.”

In Excel (Destination)

1. Launch Microsoft Excel and open the spreadsheet where you want to paste the copied data from Google Sheets with formulas.
2. Move to the cell where you want to paste the 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) on your keyboard.
   • 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)

Once you have successfully pasted the data into Excel, you can go back to Google Sheets and undo the last action by clicking on “Edit” and selecting “Undo” (Ctrl + Z in Windows and ⌘ + Z in Mac). Additionally, you can turn off the “Show Formulas” view by pressing Ctrl + `.

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. Just 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. Make sure that the copy range in Excel matches the paste range in Google Sheets to maintain correct formula results.

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

Conclusion

While many Excel functions are available in Google Sheets, it’s important to note that not all functions have a direct counterpart, and vice versa. Some functions may share the same name but operate slightly differently. Thus, when you copy and paste data with formulas between Excel and Google Sheets, be mindful of these differences. Verify and adjust the formulas as needed to ensure accurate and consistent results in the destination spreadsheet.

You may also like our comprehensive guide on popular Google Sheets functions, which you can find on Crawlan.com.

Now you have the secret to copying and pasting data with formulas between Google Sheets and Excel. Happy spreadsheeting!