The battle between Google Sheets and Excel users rages on. These are two of the most popular spreadsheet programs, with Google Sheets enthusiasts swearing by it, while Microsoft Office Excel lovers prefer to stay within their Excel comfort zone.
But what happens when an Excel user sends a spreadsheet to a Google Sheets user? Unless you have Excel, you can’t open and edit it.
If you’re one of those loyal Google Sheets users who receives an Excel document, you have the option to simply view the document as it is. But if you want to work on the sent file in your familiar and comfortable Google Sheets environment, you might want to convert Excel to Google Sheets.
In this tutorial, I will show you how to easily open files and convert Excel to Google Sheets.
The Easiest Way to Convert Excel to Google Sheets
You can directly upload an Excel file to Google Sheets. Here’s how you can import from Excel to Google Sheets using your drive:
- Open a blank spreadsheet in Google Sheets and go to “File” > “Open”.
- Click on the “Upload” tab in the “Open a file” menu.
- Drag your Excel file into the space and click “Open”.
- The Excel file will open in a new window. Click on “Open with” and choose “Google Sheets”.
Your Excel file will open as a Google spreadsheet and will be automatically saved to your Google Drive.
How to Convert Excel to Google Sheets using Import
Another direct way to convert Excel to Google Sheets is by importing it into Google Sheets. Here’s how to do it:
- In Google Sheets, go to “File” > “Import”.
- Click on “Upload” and drag your Excel file into the window or browse for it on your computer.
- Once the file upload is complete, choose either to replace the existing spreadsheet or create a new spreadsheet. Then click “Import data”.
Your Excel file will be imported with its formatting intact.
How to Convert Excel to Google Sheets using Google Drive
Google Sheets can work with the following Excel file formats:
- .xls
- .xlsx
- .xlsm
- .xlt
- .ods
- .csv
- .tsv
- .tab
If you need to open a file in Google Sheets, it first needs to be present in your Google Drive. So, the first step to opening an Excel file in Google Sheets is to import it into your Drive.
Here’s how to export Excel to Google Sheets:
- Open your browser and go to Google Drive by typing
drive.google.comin the address bar. - At the top left of the Google Drive screen, click on “New” and then go to “File Upload” in the dropdown menu.
- Select the Excel file you want to upload from your computer. Wait a few seconds for it to upload.
The above steps will add your Excel file to Drive, and you can now open it even if you don’t have Excel installed on your system.
To view the contents of the file, double-click on it. At this stage, you can view the file, but you can’t make any changes to it.
If you want to edit it, you need to open it in Google Sheets by clicking on the “Open with Google Sheets” option at the top of the screen.
NOTE: If you don’t see this option, you can still open the file in Google Sheets. Right-click and select “Open with Google Sheets”.
Now, you can edit your file, share it, and collaborate with other users on data import using Google Sheets. It’s an easy way to transfer data from Excel to Google Sheets.
The best part is that when you save this file, all the changes are saved in the original Excel file.
How to Convert an Excel File to Google Sheets Format
You might not only want to edit your file in Google Sheets but also save it in the Google Sheets format instead of the Excel format.
If that’s the case, make sure your file is already open in Google Sheets, then select “Save as Google Sheets” from the “File” menu.
You will now have two copies of the same file – one in Excel format and one in Google Sheets format.
So, even if you make changes to this Google Sheets file, your original .xls file will remain unchanged. And that’s how you can turn Excel into Google Sheets files.
How to Sync Excel with Google Sheets using a Free Extension
Do you prefer to automatically sync Google Sheets with Excel? Install a free extension like Coefficient to synchronize live data from Excel to Google Sheets.
Here’s how to sync Excel and Google Sheets:
- Sign up for Coefficient (for free).
- Follow the instructions to install the Google Sheets extension.
- Launch the Coefficient extension. It will appear as a sidebar on the right-hand side of your Google spreadsheet.
- Select “Import” from the sidebar and choose “Excel”. You will have the option to import Excel data from One Drive, Google Drive, a URL, or your local hard drive.
- Choose the sheet, range, headers, and columns you want to import into Google Sheets. Bonus! You can also filter your import to have the same format as your original file.
- If you want to sync live data at predefined intervals, set up an “auto refresh”. You will also have the option to manually refresh your data at any time by clicking a button in your data tab.
How to Automatically Save an Excel File as a Google Sheet in Google Drive
You can set up your Google Drive to automatically convert any future downloads of Excel files to Google Sheet format.
This will save you a lot of time if you need to use Excel files in Google Sheets. You can set this up as follows:
- Open Google Drive.
- Click on the “Settings” (gear) icon at the top right.
- Click on “Settings” in the options that appear.
- In the “Settings” window, you will see an option that says “Convert uploads”. Check the box that says “Convert uploaded files to the Google Docs editor format”.
- Click “Done”.
This will convert all Office files to Google Docs, Sheets, or Slides.
Previously uploaded files, however, will not be affected by this setting and will remain in their original file format.
Using an Online Excel to Google Sheets Converter
You can also find many online converters that convert Excel to Google Sheets. These converters transform an Excel file to formats like .xls, .csv, or .ods. A good example is Cloudconvert.com.
To use this online converter:
- Go to Cloudconvert.com. You may need to create an account or sign in with Google.
- Click on “Select file”.
- Choose where you want to upload your Excel file from. In this case, we will choose “From my computer”.
- Select the file.
- Click the “Convert” button and choose the .xls format from the dropdown menu.
- Click “Convert”.
- Once the file is converted by the website, it will be saved to your disk.
- Click “Download”, and it will automatically open in Google Sheets.
How to Export Large Excel Files to Google Sheets
One advantage Excel has over Google Sheets is that it can handle more data, while Google Sheets has a limit of 1000 rows and 1000 columns.
So, if your Excel sheet is large, you will encounter issues when transferring from Excel to Google Sheets.
The file size limit for files you want to import into Google Sheets is 5 MB. If your Excel file exceeds this size, you will get an error message when you try to import it.
What you can do is remove empty or unnecessary cells from your Excel file. You can also split your data in Excel into multiple pages and put them in different sheets within the same spreadsheet when uploading from Excel to Google Sheets.
How to Export a Google Sheets File to Excel
Converting Excel to Google Sheets is great, but sometimes you need to do the opposite.
It’s also quite simple. If your file is already open in Google Sheets:
- Go to “File” > “Download”.
- Select the “Microsoft Excel (.xls)” option.
Note that you also have the option to save the file in other formats like PDF, HTML, or CSV. It’s one of the things that make Google Sheets so versatile.
If you want to directly convert the Google sheet to an Excel file from your Google Drive:
- Right-click on the file icon.
- Select “Download”.
Your sheet will be automatically converted to the Excel format when downloaded.
Compatibility Issues
While it’s great to be able to work with Excel files in Google Sheets, Excel still offers many more features. Google Sheets doesn’t support many Excel features, and you might encounter compatibility issues when working with a file that has been converted from Excel to Google Sheets.
Google is continually improving and enhancing its document editing features. However, in the meantime, you might encounter compatibility issues with certain features.
Before we conclude, here are some compatibility issues you might face when converting Excel files to Google Sheets:
- If your Excel file is password protected, it won’t be possible to import it into Google Sheets.
- Google Sheets doesn’t consider links from your Excel file to other workbooks.
- Google Sheets doesn’t support PowerPivot, Power Query, or other features of Excel’s data model.
- If your Excel file contains macros written in VBA, they are not supported in Google Sheets. However, you can write your code in AppScript when working in Google Sheets.
If your Excel file contains any of the above elements, they will be hidden, lost, or worse, modified! So make sure to get rid of these features before converting.
Google Sheets Formulas that Don’t Work in Microsoft Excel
The following Google Sheets functions are not compatible with Microsoft Excel, so make sure not to include them in a file intended for an Excel user:
- UPLUS
- UNIQUE
- UNARY_PERCENT
- UMINUS
- TO_TEXT
- TO_PERCENT
- TO_DOLLARS
- TO_DATE
- SPLIT
- SPARKLINE
- SORT
- QUERY
- POW
- MULTIPLY
- MINUS
- LTE
- LT
- JOIN
- IMPORTXML
- IMPORTRANGE
- IMPORTHTML
- IMPORTFEED
- IMPORTDATA
- IMAGE
- GTE
- GT
- Google TRANSLATE
- GoogleTOURNAMENT
- GoogleFINANCE
- FILTER
- EQ
- DIVIDE
- DETECTLANGUAGE
- COUNTUNIQUE
- CONTINUE
- CONCAT
- ARRAY FORMULA
- ADD
Potential Issues After Conversion
Even now that you know how to convert Excel to Google Sheets, you may encounter issues. When transitioning from Excel to Sheets, some functions and formatting may unintentionally change. Some of the issues you might encounter include:
- Conditional formatting
- Macros
- Interactive controls
- Split worksheets
- Certain charts
Most of the time, you won’t encounter any issues, but it’s good to double-check your spreadsheet when converting.
Why Use Google Sheets Instead of Excel?
Microsoft Excel has been around for a long time and is widely used in businesses and offices around the world.
However, since Google released its equivalent, Google Sheets, many people have come to appreciate it. Here’s why:
- While installing Microsoft Excel costs $99, Google Sheets is available in your usual Google browser, completely free (you just need a free Google account).
- Excel has been around longer, and many people have become accustomed to its interface. Google Sheets drew heavily from Excel, and their user interfaces are clean and intuitive (with fewer features, of course).
- Your Google Sheets are saved on your Google Drive, so you can access your files from anywhere using any browser. You don’t need any special software to run them.
- Google Sheets allows you to collaborate with other users on the same sheet, so you can work with your teammates in real time and make simultaneous changes. You can even chat with these collaborators in real time.
- When Google Sheets rolls out a new feature or functionality, it is available to everyone simultaneously. You don’t have to worry about versions and upward compatibility of documents with Google Sheets.
- When working on a Google sheet, your files are automatically saved to your Google Drive, no more errors.
- You can add code and run APIs using Google Sheets, adding much more flexibility and functionality to your use of data in your spreadsheet.
Frequently Asked Questions
- How to convert Excel files to Google Sheets without losing formatting?
When you import an Excel file into Google Sheets, it usually retains its formatting. You can convert it by using the “Open” option in Google Sheets or by importing it first to your drive.
You can also open it using the import option in Google Sheets. All these methods will upload your Excel file to Google Sheets without losing its formatting.
- Can you convert Excel to Google Sheets? How to convert xlsx files to Google Sheets?
The simplest way is to import the file into Google Sheets directly with the following steps:
- Go to “File” > “Open” in a new spreadsheet.
- Click on the “Upload” tab in the “Open a file” menu.
- Drag your Excel file into the space and click “Open”.
- How to automatically import Excel into Google Sheets?
You can set up the file to save your Excel sheets to your Google Drive. Then, you need to go to the “General settings” tab in Google Drive and check the box that says “Convert uploaded files to the Google Docs editor format” under the “Convert uploads” tab.
- Can Google Sheets open .xlsx files?
Yes, you can convert .xlsx to Google Sheets by going to “File” > “Open” > “Upload”.
- Do all Excel formulas work in Google Sheets?
No, unfortunately, not all formulas work. However, Google Sheets is constantly updating its compatible formulas. There are also equivalent ways to achieve the same result. A simple Google search with the function name will help you find alternatives.
In conclusion
In this article, you have learned how to convert Excel to Google Sheets and how to transfer Excel to Google Sheets and vice versa. As you can see, it’s quite easy to switch formats between the two spreadsheet giants, Google Sheets and Excel.
I hope you found this tutorial helpful!
If you want to learn more about Google Sheets and other related topics, visit Crawlan.com.
