SEC | S20W6 | Data Analysis with Google Sheets: (Importing and Exporting Data: Convert Excel to Google Sheets Etc)
Hello everyone! I hope you will be good. Today I am here to participate in the contest of @josepha about the Importing and Exporting Data: Convert Excel to Google Sheets. It is really an interesting and knowledgeable contest. There is a lot to explore. If you want to join then:
.png)
What do you understand by importing and exporting data in Google Sheets? Use the Coefficient method and input the Excel Work that you did in previous lesson 2 into a new Google sheet and shared with us all the necessary procedures. Note: Should in case you didn't participate in the previous lesson 2, you can upload any Excel work that you have.
What do you understand by importing and exporting data in Google Sheets?
Importing and exporting data in Google sheets is the process of transferring data between Google Sheets and other formats. We can import or export Excel, CSV or other Google sheets to a Google Sheet. We can do it to work on existing data in the different environment. On the other hand we can share the data in a more accessible way.
Importing Data into Google Sheets (via Coefficient Method)
Coefficient method is used to import data from external sources such as Excel or database into the Google Sheets. Coefficient is actually a Google Sheets add-on. It helps to automate the data importing. It helps to build dashboards and to manage workflows.
Here is a complete step by step guide to import data from an Excel file into Google Sheet using Coefficient method:
As in order to use Coefficient method we need Coefficient add-on by the Google Sheets so first of all I will install that add-on.
 |  |
|---|
 |  |
|---|
Here is the complete step by step process for the installation of the Coefficient add-on. First of all we need to open the add-on menu then we need to search the Coefficient add-on. After that I have selected the first option of the Coefficient. It lead to installation with a pop up. I confirmed the installation by giving permission from my google account. And finally I have installed it successfully.
 |  |  |
|---|
Here is the process of importing an Excel sheet in the Google Sheets.
 |  |
|---|
There were different options I selected Excel to import sheet in the Google Sheets. The Google drive connection was not working at that time. So I selected One Drive. It required me to sign in via my account to give permission to Coefficient add-on to accept file from it.
After the successful login It showed me this interface where I can see all the available files in my One Drive. I selected steemk.xlsx file for the importing purpose.
 |  |  |
|---|
After selection of file it got some loading. After the data loading it fetched all the available data in the sheet. It gave the detail and we can select specific data to load in Google Sheets from that file. Then It got some time for the loading and at last it showed the columns available in the sheet. For ,more precision we can select the columns and rows to import in the Google sheets.
 |  |
|---|
File importing is in the final stages and these are the last steps. And finally after waiting for a few moments the file was imported successfully.
Finally the file has imported in the Google Sheets successfully. You can see the data of the sheet is also visible. In this way we can import any file from Excel to Google Sheets with the Coefficient method.
But I saw a problem while import. My file has a chart in the Excel and when the file was imported in the Google Sheets the chart was not there. I want to ask from the professor why it happened?
Share with us the needed procedures for converting Google Sheets Files to Excel File using the Google Sheet that You created in Lesson 4 and detail out the limitations that you observe while importing and exporting your data.
As we did import similarly we can export files from Google Sheets to Excel files. It is again a simple process. We can follow these simple steps to export Google Sheets files to Excel files.
First of all we need to download the Google sheets file to our local storage from where we can then import that file to Excel. Actually in this process there is import of file as well. It will also be useful to understand how can we import files to Excel files from the local storage.
In order to download we have to follow this procedure as shown in the above picture. I have selected the same format as Excel accepts which is xlsx.
The file has already downloaded in the required format.
Now there are two ways to open this file downloaded from Google sheets in Excel.
We can simply open it from its downloaded folder by double click.
We can also open this file just by opening Excel and following the procedure given below:
- Select open
- Click on browse
- Open the folder where the file is available. In my case I will open downloads folder
- Then we need to select the desired file which we want to open in Excel.
My file has loaded successfully with all the details.
It has a total of 5 sheets as it has in the Google Sheets. So It has loaded all the data correctly.
 |  |
|---|
 |  |
|---|
Here you can see that all the pages, charts and other data has loaded correctly without any fault. In this way we can export any file from Google Sheets to Excel in simple and easy way. moreover if we have file in our local storage or at any other platform we can export that file also in this simple way. We just need to download the file in our system. And it is very easy while exporting file from Google Sheets and importing it in the Excel.
Limitations of Importing and Exporting Data between Google Sheets and Excel
Limitations of Importing and Exporting Data between Google Sheets and Excel
When we convert Google sheets to Excel or similar from Excel to Google we can face different limitations with respect to the features, formatting as well as regarding the formulas.
Formulas Compatibility
- Google Sheets uses some formulas that are unique such as
GOOGLEFINANCE(). This formula is not supported in Excel. If we are using this formula and we export our file to Excel then it will not work there. - Array formulas and scripts may break or they need modification to work in Excel.
Formatting Issues
- Complex formatting such as conditional formatting may be affected while transferring. Some filters and pivot tables may not always transfer perfectly. While performing the first task I export file from Excel and then I imported it in Google sheets but it did not transfer my chart.
- We can face changes in font types, cell sizes as well as colors while converting from Google sheets to Excel and similarly from Excel to Google sheets.
Data Validation Rules
- Some data validation settings such as dropdown menus or custom rules in Google Sheets may not work or look different in Excel.
- Check boxes may also look different when they are imported to Excel.
Macros and Scripts
- Google Apps Script such as automation scripts in Google Sheets do not transfer to Excel. Because Excel uses Visual Basic for Applications for macros and scripts.
- If Google Sheet contains Google Apps Scripts they will not work after exporting the file to Excel.
Collaboration Features
- Google Sheets allows for real time collaboration. Multiple users can edit simultaneously. Excel is on the other hand may not offer the same level of simultaneous collaboration. It can offer but for this purpose all the users should use online Excel or a shared network.
- Comments and notes in Google Sheets may appear differently or be missing when opened in Excel.
File Size Limitations
- Google Sheets has a file size limit of around 5 million cells. While Excel can handle larger datasets. We can face performance issues if we are working with a huge dataset. And it will not work when we export it into Google sheets.
Dynamic Features
- Dynamic features such as dynamic charts real time updates from APIs or third party services and Google Sheet specific add-ons may not function when the file is exported to Excel.
If I had to choose based on the general use I will choose Google Sheets. Because these look more user friendly because of their simplicity. It has collaborative features. It is very useful for the tasks especially for the beginners.
Real-time Collaboration: The most easy to use feature of Google sheets is its collaboration feature. Multiple users can work on the same document at once. And the changes can reflect in real time. This makes it ideal for working in a group.
Cloud-based and Accessible: As we know that Google is cloud based. So we can access it anywhere with an internet connection. We can use it irrespective of the devices. We do not need to install any dedicated software.
Simplicity: The interface of Google Sheets is very easy to use. And it makes it easy to understand for the beginners as well.
Integration with Google Ecosystem: It works smoothly with other Google services such as Google Drive, Gmail and Google Forms. It makes it easy to manage the data on different platforms.
Add-ons and Automation: Google Sheets has plenty of add-ons. And we script these add-ons with the help of Google apps. It allows moderate automation because in automation Excel works more.
I invite my friends @sergeyk, @fombae, @suboohi to join this contest.