How To Convert Excel To Google Sheets With Formulas

09/24/21 • 6 min read

Convert Excel To Google Sheets With Formulas

Here are a bunch of handy tips and tricks for converting Excel files to Google Sheets, as well as some useful formulas you can use to augment and edit data within your spreadsheets.

Regardless of the fact that you use Google Drive, or specifically Google Sheets, for your spreadsheet files, you will almost certainly still receive an Excel file at some point.

You may choose to preserve it as an XLS document and work with it that way in some instances, especially if whoever you’re sending on to or back to has requested so, or, you might wish to convert an Excel to Google Sheets with all of the formulas included.

Not everybody has made the leap to the cloud just yet. Some companies prefer being offline, some people prefer the familiarity of the software they’ve used since 1987, or more modern versions of it like Microsoft 365, and some people simply don’t know Google Sheets exist.

Why Convert Excel To Sheets?

Google Sheets has a number of benefits over its Microsoft counterpart.

Efficiency

Users may edit, organize, and analyze many types of data using Google Sheets. It supports collaborations, and many users may edit and format files in real-time, with a revision history to monitor any changes made to the spreadsheet.

Tools

Users may utilize the Explore tool to ask questions, generate charts, visualize data, construct pivot tables, and color-code the spreadsheet. For example, if you’re creating a monthly budget and have entered all of your costs into the spreadsheet, you may utilize the Explore tool to find out how much food, travel, and clothing cost.

Offline as well as online

You can still edit your Sheets offline if you’d prefer, however, some of Google’s beneficial tools will not work without being connected to the internet, such as the aforementioned Explore tool.

Offline editing is available in Google Sheets, and users may modify the spreadsheet on their computers or mobile devices. To allow offline editing for Google Sheets and other Google applications on the desktop, users must use the Chrome browser and install the “Google Docs Offline” Chrome extension.

Users must utilize the Google Sheets mobile app for Android and iOS, which supports offline editing while using a mobile device.

Compatibility

Google Sheets accepts a variety of spreadsheet file formats and types, including:

.xlsx
.xls
.xlsm
.xlt
.xltx
.xltxm
.ods
.csv
.tsv

Google Sheets allows users to open, modify, save, and export spreadsheets and document files in all of these different format types.

Integration

Other Google products, such as Google Forms, Google Finance, Google Translate, and Google Drawings, may all be connected with Google Sheets.

If you want to construct a poll or questionnaire, for example, you may enter the questions in Google Forms and then simply and efficiently import the form straight into your Google Sheets file.

How To Convert Excel To Google Sheets

It’s simple to convert an existing Excel spreadsheet to Google Sheets – just take the actions outlined below.

Head to your Drive and create a new Google Sheets files. Open your Sheet, then select File, then Import.
Click Upload and follow the on-screen instructions to upload or drag and drop it. Sheets documents contain two crossed lines, while Excel documents have an x.
Choose the best choice in the Import file box, then click Import data.

Importing your Excel file to Google Sheets this way will include all of the formulas contained in the original Microsoft Excel file.

Handy Google Sheets Formulas

If you’re searching for a Google Sheets cheat sheet to help you ramp up your spreadsheet skills, you’re in luck. We’ve gathered a few common formulas you can use to both speed up your operations and have your documents looking professional. Here are 6 of our favorites:

SUM, COUNT, AVERAGE

SUM allows you to tally any number of columns or rows by selecting or entering them in. For example, =SUM(A2:A9) sums all values between A2 and A9, and so on.

COUNT is a function that counts the number of cells in an array that contains a number value. This might be useful in detecting whether or not someone has paid a bill, or in other database scenarios.

AVERAGE does exactly what it says on the tin: it calculates the average of the values you enter.

VLOOKUP

‘Vertical Lookup’ is what it means. It’s a function in Sheets that allows you to search for a certain value in a column in order to retrieve a value from another column in the same row. =VLOOKUP is the syntax for this formula.

MAX & MIN

These functions are very simple to use; simply write in the column or row of numbers you wish to search, then follow the function’s instructions, and it will return the MAX or MIN value, depending on the function. =MAX(A1:A10), for example, returns the highest numerical value within these rows.

IF STATEMENTS

IF statements come in handy in a variety of scenarios. If a case is valid or false, you may use this method to output text. =IF(B1>D2, “GOOD”, “BAD”), for example, where B1>D2 is the case, “GOOD” if the output is true, and “BAD” if the output is false.

Alternatively, =IF(C4>B4, “Over Budget,” “Within Budget”); where the IF function says IF (C4 Is Greater Than B4, return “Over Budget,” else return “Within Budget.”

SUMIF, COUNTIF, AVERAGEIF

These Spreadsheet functions are a mixture of the SUM, COUNT, AVERAGE, and IF functions and statements.

So you could type =SUM into SUM (A1:A15, “GOOD”, B1:B13). If the values of A1 through A15 were all GOOD, this would add B1 through B13. You may have noticed that many of these formulae may be stacked on top of one another to build more complicated spreadsheets and actions.

PROPER

PROPER can particularly handy when your database has a lot of strangely formatted text that seems muddled, such as capitalizations in incorrect places. If cell E1 stated “wE lOve spreAdsheets,” you could enter =PROPER(E1) to get “We Love Spreadsheets,” which doesn’t sound quite as sarcastic.

Google Drive Resource Hub: Learn All The Basics + Pro-Level Tips For Drive

Learn More About Google Drive ↗

Essential Remote Work Gear

External SSD – Regardless of what computer you use, having external storage is essential, especially if you work on the move. With an SSD, you can store and keep all your most important documents in one place and take it with you when you’re on the move. Our current favorite right now is the 2TB OWC Envoy Pro EX. It is outstanding.

Mechanical Keyboard – If you’ve never used a mechanical keyboard before, there’s a bit of a learning curve. But once you’ve gotten used to using one, you’ll never go back to flat, lifeless keyboards like the one on your iMac or MacBook. Our top picks right now are the Keychron C1 ($59) and the VISSLESS V84 ($99); they’re both wireless and feature RGB lighting.

Mouse – A good mouse will last you years at a time. It’ll also stop you from getting RSI on your wrist. I have used a bunch of third-party mouses in the past 24 months but my absolute favorite right now has to be the Logitech MX Master 3. Oh, and it is fully compatible with iOS too, so it’ll work fine on your iPad too.