Wednesday, July 8, 2020

How to use Google Sheets: Basic tutorial, formulas, and more

How to use google sheets featured image - schedule templateHow to use google sheets featured image - schedule template

Spreadsheets are an incredibly powerful tool for countless professions — if you know how to use them. In years past you'd have to spend hundreds of dollars on software and countless hours researching to do anything worthwhile. Now, with the help Google Sheets and our in-depth guide, you can be up and running in just a few minutes for free!

Whether you're a complete novice or have some experience with Excel or other spreadsheet programs, our beginner's guide to how to use Google Sheets will cover exactly what you need to know to get stared. Feel free to use the menu on the left to skip ahead to the sections most relevant for you.

What is Google Sheets?

Google Sheets is a cloud-based spreadsheet program that's an part of Google's office suite, called G Suite. Alongside Google's other cloud-based services like Google Drive, Docs, Slides, and of course, Gmail, G Suite offers a comprehensive productivity platform for businesses and students alike.

Read also: How to use Google Docs: Basic tutorial, plus tips and tricks

Odds are you've at least seen a spreadsheet program before, and Google Sheets doesn't stray far from the tried-and-true design. You enter or import data to analyze it, do complex calculations, or just keep it stored safely for later reference.

What really sets Google Sheets apart from competitors is the fact that it's completely free to use. You just need a Google account, which takes a few minutes or less to make.

How does it compare to Microsoft Excel?

The best comparison for Google Sheets is Microsoft Excel, which has been the leading spreadsheet program more than three decades. Those with Excel experience will have a much easier timing learning how to use Google Sheets, as it works almost exactly the same.

For those who have a large catalog of documents in Microsoft's program, you'll be happy to learn that Google Sheets is entirely compatible with Excel files. You can convert the two back and forth with ease.

The vast majority of established Excel functions have exact equivalents in Google Sheets. In fact, for the basic data management tasks we cover in this guide there is virtually no difference whatsoever between the two. For very advanced users, Excel offers slightly more powerful charting tools and support for extremely large datasets. Google Sheets is limited to 2 million cells of data.

Even so, Google Sheets does have a few advantages over Excel:

  • It's free. The standalone Excel software still costs $140 to purchase, although there is a free limited online-only version.
  • It's cloud based. All of your work is saved automatically, so it's nearly impossible to lose data.
  • It's built for collaboration. Like the rest of G Suite, Google Sheets makes it easy to collaborate with others on any device.

Basic terminology

Before we get into the nitty gritty of how to use Google Sheets, there are a few key terms you should know. They're the same terms you'll find in any other spreadsheet program, so you may already be familiar with them.

  • Cell: A single data point in the spreadsheet, represented by a box.
  • Column: A vertical set of cells.
  • Row: A horizontal set of cells.
  • Range: A specific selection of cells.
  • Function: A built-in operation that can be called to manipulate data, calculate values, etc.
  • Formula: Any combination of functions, cells, ranges, and operators used to calculate a value.
  • Sheet/worksheet: A single "page" of rows and columns.
  • Spreadsheet: A document containing one or more sheets of data.

How to create a new Google Sheets spreadsheet

How to create a new spreadsheet - New Google Sheets templatesHow to create a new spreadsheet - New Google Sheets templates

The first step in learning how to use Google Sheets is creating a spreadsheet. There are a few ways to do this, but the most convenient one is directly through the Google Sheets website.

Once there, you may have to log into your Google account if you're not already logged in. Once you do that, all you need to do is click one of the templates at the top of the page in the Start a new spreadsheet section.

Most often you'll want to select Blank, but there's also a variety of templates that might save you some time. You can see the full list of templates by clicking Template Gallery in the upper right.

Regardless of which one you choose, the new spreadsheet will automatically open. Before you start entering or importing data, you should give the spreadsheet a name in the text field at the top left of the screen. Here are those steps again in brief:

How to create a spreadsheet in Google Sheets

  1. Navigate to the Google Sheets website.
  2. Log into your Google account if necessary.
  3. Select one of the templates in the Start a new spreadsheet section.
  4. Enter a name for the spreadsheet in the top right.

As mentioned before, this is only one of many ways to create a new spreadsheet. You can also create them in Google Drive via the menu in the top right (New -> Google Sheets) or in the Google Drive app by tapping the plus icon in the bottom right, then selecting Google Sheets.

Note that each spreadsheet can contain multiple pages, or Sheets. At the bottom of the page you'll see a tab for each sheet in your spreadsheet, as well as a plus button to add a new sheet.

How to use Google Sheets: Basic tutorial

Now that you've created a spreadsheet, it's time to truly learn how to use Google Sheets. Before we get into entering and manipulating data with formula, take a moment to get familiar with the interface.

When you first open a blank document, you'll see something that looks like the image below. Here's a brief description of what each element does to help you get more comfortable.

Google Sheets interface guideGoogle Sheets interface guide

  1. Spreadsheet name
  2. Menu bar
  3. Basic controls (undo, redo, print, format painter)
  4. Zoom
  5. Number formatting options
  1. Text formatting options
  2. Cell formatting options
  3. Advanced controls and functions
  4. Active cell

Most of the interface is self-explanatory, but we'll get more into the individual controls later in the guide. For now, it's critical to understand how cell numbering works. This is the backbone of everything that a spreadsheet can do.

Each column is labeled across the stop with a letter, and each row is labeled on the left with a number. A single cell is named by a combination of the two. For example, the active cell above is A1. A range including the first three cells on the left would be A1-A3.

Note that while the active cell above is blue, the color may be different when multiple people are editing the same document. You will be able to see what every editor's active cell is, which can help prevent mistakes caused by two people editing the same cell at once.

It's also worth mentioning that you can resize any row or column by clicking and dragging the border between it and an adjacent row. You can also add or delete entire rows or columns by right clicking and selecting the appropriate option from the drop-down menu.

How to add data to Google Sheets

The first real step to creating a functional spreadsheet is adding data. The easiest way to do this is simply click in a cell and type in the data directly. You can press the Enter key to move down to the next row, or Tab key to move right to the next column.

However, this is far from convenient for large data sets. It can take hours to manually enter data (and will take hours to clean up for certain data sets), but you can save that time by importing entire tables at once.

There are several ways to do this. The first is to simply select a table, copy it, and paste it directly into your document. You can use this method to copy lists and other data from websites or other sources with ease.

The other method is to import an existing spreadsheet or supported file type. Virtually all popular spreadsheet file types are supported, and you can find the full list below.

Supported file types in Google Sheets:

  • .xls (newer than Microsoft Office 95)
  • .xlsx
  • .xlsm
  • .xlt
  • .xltx
  • .xltm
  • .ods
  • .csv
  • .txt
  • .tsv
  • .tab

Import data Google SheetsImport data Google Sheets

To import a file into Google Sheets, click File in the upper right, then Import. Next, you need to find or upload your document and click Select.

Now you can select the import options for your file. Not all of the options seen above may be available depending on the file type. Once you've made your decision, click Import data and you're good to go. Here are those steps again in brief:

How to import data to Google Sheets

  1. Click File, then Import.
  2. Select or upload the file to import.
  3. Configure import options as necessary.
  4. Click Import data.

How to format data in Google Sheets

Google Sheets formattingGoogle Sheets formatting

Now that you've got data in your spreadsheet, you might need to adjust the formatting before you start working with it. Often Google Sheets will automatically format your data correctly, but sometimes it needs to be fixed manually. Much of this can be done directly from the toolbar, but there are advanced options in the Format tab, as well.

For numbers, you can select between a simple decimal, percent, dates, currencies, and many others. You can also increase or decrease the number of decimal places displayed using the main toolbar. Note that this doesn't change or round the real value, just the number displayed.

Read also: How to wrap text in Google Sheets

For normal text, you have the standard options for bold, italic, underline, and strikethrough, as well as font types, font sizes, and alignment. Cells themselves can be formatted as well. You can change the background color, merge or unmerge cells, or add borders.

To speed up your formatting, you can also select an entire column or row by clicking on the letter or number at the top or left of the screen.

How to merge cells in Google Sheets

Merging cells is one of the most basic ways to format your spreadsheets, and it should be one of the first things you learn how to do. Thankfully, it's also one of the easiest things you can do in the program.

All you need to do is select more than one cell and click the merge icon in the upper middle of the toolbar. The icon has two arrows pointing together, and it's greyed out unless you have more than one cell selected.

Learn moreHow to merge cells in Google Sheets

How to use Google Sheets offline

One of the scariest things about switching to a cloud-based application is the thought of losing your internet connection. Thankfully, Google Sheets makes it easy to access and edit your spreadsheets, even if you're temporarily offline.

This only works in the Chrome browser, and you will need to sync your Google account to Chrome. To do this, click the People icon in the top right, then Sign in to Chrome.

Read also: 20 Chrome tips and tricks you should know about

Once you're signed in, head over to the Google Sheets website. Click the menu icon in the upper left, then Settings. In the middle of the window, you'll see a toggle for Offline. Flip it and you're good to go! Here are those steps again in brief:

How to use Google Sheets offline

  1. Navigate to the Google Sheets website.
  2. Open the menu in the upper left and click Settings.
  3. Turn on the option for Offline.

How to use Google Sheets formulas and functions

Google Sheets formulasGoogle Sheets formulas

Formulas and functions are a hugely powerful part of any spreadsheet software, and Google Sheets supports both. But before we get into some of the advanced tasks, let's talk about the difference between a formula and a function.

A formula performs simple arithmetic on values or cells that you specify. A function performs a more complex tasks ranging from simple sums to payments on annuity investments or even pulling data from the web.

The main thing to know as a beginner is that all formulas and functions start with =. This signals to Google Sheets that what you're entering is a not simply text or a number.

All formulas and functions start with the = symbol.

Formulas are the easiest to use. Once you've typed =, you can enter specific numbers or refer to values in other cells by typing A1, A2, etc. You can then manipulate these values with +, -, *, and / symbols to perform basic arithmetic.

Functions work similarly, but require more specific input. We won't explain every function in this Google Sheets tutorial (there are hundreds of them), but you can see the full list by clicking on the ∑ symbol in the upper right. You can also search for them by beginning to type the function name after the = symbol in any cell.

How to use the SUM function

To give you some idea of how these work, let's have a look at the SUM function. This function simply adds up all of the values in a given range. It looks like this: =SUM(values or range). Those values can be specific cells or numbers such as =SUM(10, A2, A3, 15) or a range =SUM(A1:A10).

You can select a range with your mouse once you type =SUM(, then hit Enter when you're finished and watch the program do the work for you!

Check out a full list of Google Sheets functions here, or learn how specific functions work by clicking the ∑ symbol, selecting the function, then clicking Learn more at the bottom.

How to use Google sheets: Advanced tutorial and tips

One of the best and worst things about spreadsheets is just how much you can do with them. There are far too many possibilities to cover in this basic guide to how to use Google Sheets, but we wanted to include a few more things that even beginners may want to do.

How to make charts in Google Sheets

Google Sheets graphsGoogle Sheets graphs

Although we're including it as an advanced tip, it's actually very easy to create charts in Google Sheets. There's also a wide variety of chart types to choose from, each of which is completely customizable.

Once you have your data, select the cells you want to include in the graph, including headers if possible. Then, you can either click the Insert chart button in the top right or click Insert at the top of the page, then select Chart.

Learn more: How to make a graph in Google Sheets

Google Sheets will analyze the data and select the best graph type for you. You can change to any other type of graph in the Chart Editor panel on the right hand side of the screen. Here you can also add series to display in the chart, adjust labels, and do countless other tweaks to the chart itself.

If you really want to get into the weeds, you can change the appearance of the chart in the Customize tab of the Chart Editor. It allows you to rename axes, change the chart's appearance and colors, and countless other adjustments. The best way to learn about the different options is to play around with it for a while.

How to sort data in Google Sheets

sort in google sheets 2sort in google sheets 2

Sorting data is a great way to analyze a data set at a glance, especially when it contains hundreds or thousands of points of data.

To sort data in Google Sheets, all you need to do is select the entire table, click Data at the top of the page, then Sort range. Now you can select the column to sort by, but make sure to check the box for Data has header row if applicable. Otherwise it will be sorted with the rest of the data!

Learn more: How to sort in Google Sheets

Using this method, you can quickly sort any kind of data, whether it's alphabetical, numerical, dates, times, or just about anything else. Here are those steps again in brief:

How to sort data in Google Sheets

  1. Select the entire table of data to sort, including headers.
  2. Click Data at the top of the page, then Sort range.
  3. Check the box for Data has header row if applicable.
  4. Select the column to sort by.
  5. Click Sort.

How to share and collaborate in Google Sheets

One of the biggest advantages Google Sheets has over Excel and other competitors (apart from being free) is how easy it is to collaborate with others on the same document.

Forget about renaming documents and having complex version control systems — all of that is handled by Google Sheets behind the scenes. Plus, anyone can access the spreadsheet from virtually any device, even if someone else is already editing it!

How to share a Google Sheets spreadsheet

The first step to collaboration in Google Sheets is giving access to other users. By default, all new spreadsheets are private, and only your account can access them.

To share a Google Sheets spreadsheet, all you need to do is click the big Share button in the top right. From there you have two options: manually enter email addresses to share with or get a shareable link.

Shareable links are more convenient if you're sharing with large groups of people, but they can also open up the document to literally anyone who has the link. Selecting Restricted will limit the document to just the email addresses you've shared with, although those people will also have received a link via email.

No matter which option you choose, make sure to set the correct permissions on the right hand side. Here are the permissions and what they mean:

  • Viewer: Can only see the document in read-only format.
  • Editor: Can change and edit all aspects of the spreadsheet.
  • Commenter: Can only see and add comments to the document.

Here are those steps again in brief:

How to share a Google Sheets spreadsheet:

  1. Open your spreadsheet.
  2. Click the Share icon in the upper right.
  3. Do one of the following:
    1. Manually enter email addresses in the top box.
    2. Click Get Link for a shareable link.
  4. Set permissions for other users.
  5. Click Done or Save.

Note that you can also change or remove permissions in the same menu once the document has been shared.

How to add comments in Google Sheets

Google Sheets commentGoogle Sheets comment

One great way to provide feedback in Google Sheets is with comments. These work just like you'd expect and provide a simple and easily findable way to fix any potential issues.

Both Editors and Commenters can comment on Google Sheets spreadsheets, but Viewers cannot. Make sure to set (or change) permissions to allow comments from the right users.

To leave a comment, all you need to do is right click on a cell and select Comment. Alternatively, you can use the keyboard shortcut Ctrl+Alt+M. After that, just type in your message and click Comment. A small orange triangle will appear at the top right of the cell with a comment, and other users can reply to or resolve the comment by clicking on that cell.

Other FAQ

Is Google Sheets really free?

Yes, Google Sheets is completely free. There is a paid version for businesses and enterprises, but it only adds more storage space and access to support staff. Functionality remains the same.

Is Google Sheets secure?

Yes, Google Sheets are generally safe and secure. Unless you've shared the document with other users, it remains locked behind your password on Google's servers in a secure location.

Are Google Sheets formulas the same as Excel formulas?

For the most part, Google Sheets formulas are the same as Excel formulas. Some complex formulas are unique to either Sheets or Excel, so be sure to check documentation for details.

Are Google Sheets compatible with Excel?

Yes, Google Sheets are compatible with Excel. You can both import and export all standard Excel file types newer than Office 95.

Can you download spreadsheets from Google Sheets?

Yes. To download a file, click File, then Download and select your file type.


That's it for our beginner's guide to how to use Google Sheets. Check out some of our other G Suite guides at the links below.



from Android Authority https://ift.tt/2WgFRtJ
via IFTTT

No comments:

Post a Comment