Google Sheets cheat sheet: How to get began
How to make use of Google Sheets to create, work with, and collaborate on spreadsheets.
Suebsiri / Savushkin / amtitus / Getty Images / Google
Google Sheets is a robust spreadsheet app that you just use by way of your internet browser. It shops your spreadsheets within the cloud with Google Drive. Anyone with a Google account can use Sheets and Drive free of charge. Both are additionally a part of Google Workspace (previously G Suite), Google’s subscription workplace suite for enterprise and enterprise prospects.
This information will train you easy methods to begin a brand new spreadsheet in Sheets or add one you have already got saved in your PC, together with a Microsoft Excel spreadsheet. It additionally goes over the essential interface and distinctive options of Sheets, similar to easy methods to share your spreadsheets and collaborate on them with others.
Google additionally presents a Sheets cellular app for Android and iOS. This information will be aware any main variations within the cellular app’s interface and options.
Create or open a spreadsheet
To begin a brand new spreadsheet or load one you’ve already saved in Google Drive, you’ll have to log in to your Google or Workspace account. Then:
From Google Sheets: At the highest of the Sheets dwelling web page, you’ll see a “Start a new spreadsheet” header, with a row of thumbnails beneath. To begin a brand new, clean spreadsheet, click on the Blank thumbnail or choose a template from the thumbnails to the appropriate. To see extra templates, click on Template gallery towards the upper-right nook; click on any thumbnail to begin a brand new spreadsheet in that template.
IDG
The Google Sheets dwelling web page. (Click picture to enlarge it.)
Below the thumbnails you’ll see a listing of spreadsheets saved in your personal Google Drive or shared with you. To open a spreadsheet, click on it.
This record seems in reverse chronological order beginning with the spreadsheet you most lately opened. Clicking the “AZ” icon on the higher proper of this record modifications the kind order to Last modified by me, Last modified, or in alphabetical order by spreadsheet title. You may browse to a selected folder by clicking the folder icon subsequent to the “AZ” icon.
Finally, you should utilize the search field on the prime to search out spreadsheets in Google Drive. Enter phrases or numbers that could be within the spreadsheet you are searching for.
From Google Drive: Spreadsheets saved in your Google Drive are listed in the primary window of the Drive dwelling web page. To see a list of spreadsheets that others are sharing with you, click on Shared with me from the left column. From both record, double-click a spreadsheet to open it in Google Sheets. You may use the search field on the prime of the web page.
To begin a brand new clean spreadsheet, click on the New button on the upper-left of the display screen after which click on Google Sheets.
If you need to use a template to begin a brand new spreadsheet: Click the New button, then choose Google Sheets > From a template. The template gallery for Google Sheets will open; click on a thumbnail to begin a brand new spreadsheet in that template.
IDG
Creating a brand new spreadsheet from Google Drive. (Click picture to enlarge it.)
Upload a Microsoft Excel spreadsheet for modifying in Google Sheets
If you need to edit a spreadsheet that’s not already saved in your Google Drive, it’s essential to first add it to Drive. You can do that from both Sheets or Drive.
From Google Sheets: Click the folder (“Open file picker”) icon that is on the upper-right of the spreadsheets record. On the panel that opens, click on the Upload tab. Drag-and-drop your Excel file (.xls or .xlsx) onto this panel, or browse your PC’s drive to pick it and click on Open.
IDG
To add an Excel spreadsheet, drag the file onto this panel or browse to it in your arduous drive. (Click picture to enlarge it.)
From Google Drive: Click the New button, then File add, and choose the Excel file out of your PC’s drive and click on Open.
IDG
Uploading a spreadsheet in Google Drive. (Click picture to enlarge it.)
Traditionally, whenever you uploaded Excel recordsdata to Google Drive, they had been routinely transformed to Sheets format. That’s nonetheless the case whenever you add Excel recordsdata by way of the Google Sheets dwelling web page.
However, Google additionally helps the power to edit Microsoft Office recordsdata of their native format. By default, any .xlsx recordsdata that you just add by way of Google Drive will stay formatted as Excel paperwork. You can edit and collaborate on an Excel file proper in Sheets, with all modifications made by you or your collaborators saved on to the Excel file. On the Google Sheets and Drive homepages, native Excel recordsdata will likely be denoted with a inexperienced “X” icon, and whenever you open a local Excel file in Sheets, you’ll see an “.XLSX” flag to the appropriate of the doc title.
IDG
Microsoft Excel-formatted recordsdata present the Excel icon (prime) as a substitute of the Google Sheets icon (backside).
If you’d fairly have Google convert Excel recordsdata to Sheets format routinely whenever you add them by way of Google Drive, click on the gear icon within the upper-right nook of the Drive dwelling display screen and choose Settings from the drop-down menu. In the Settings field subsequent to “Convert uploads,” examine the checkbox marked Convert uploaded recordsdata to Google Docs editor format.
To convert Excel macros to Google Sheets: If an Excel file you’re changing to Sheets format incorporates VBA (Visual Basic for Applications) code, which is used for macros, and also you’re subscribed to a Google Workplace Enterprise Plus plan, set up the Macro Converter add-on for Google Sheets. This will do as its identify implies: convert your Excel file’s VBA code to the App Script format that Google Sheets makes use of, in an effort to execute these macros in your spreadsheet when it’s loaded into Google Sheets.
Work in a spreadsheet
When you open a spreadsheet in Google Sheets, right here’s what you see:
Your spreadsheet’s title seems alongside the highest of the display screen. To change it, click on wherever contained in the title and begin typing.
IDG
The Google Sheets modifying interface. (Click picture to enlarge it.)
The menu bar is just under the spreadsheet’s title and consists of the next drop-down menus:
- File has instructions for taking motion on the entire spreadsheet, together with Print, Rename, and Share.
- Edit incorporates fast instruments for deleting columns or rows.
- View controls the way you see the spreadsheet, similar to displaying/hiding formulation, freezing rows or columns, and grouping collectively columns or rows.
- Insert permits you to insert cells, columns, or rows; graphical parts similar to charts and pictures; and different objects similar to capabilities, feedback, and checkboxes.
- Format has the identical instruments because the toolbar that’s immediately under the menu bar (described under), plus instructions for conditional formatting and alternating colours.
- Data has instruments for sorting a spread of columns in alphabetical order, making a filter, defending sheets and ranges, and validating and information.
- Tools results in miscellaneous objects similar to making a kind and operating spell-check.
- Extensions permits you to work with add-ons, macros, scripts, and apps.
IDG
The information menu consists of instruments for sorting, filtering, validating, and cleansing up information. (Click picture to enlarge it.)
The toolbar is immediately above your spreadsheet and has buttons for formatting the numbers and textual content in a cell. These embrace altering its quantity format (e.g., forex, date, p.c) and font (sort, dimension, model, shade). Other buttons on this toolbar allow you to change the background shade of a cell, merge cells, and alter the horizontal or vertical alignment of numbers or textual content in a cell.
To insert a chart, click on the Insert chart button towards the appropriate finish of the toolbar. This opens a sidebar from which you’ll be able to choose a preset chart or click on the Customize tab to create your personal chart.
IDG
Inserting a chart in Google Sheets. (Click picture to enlarge it.)
To insert a formulation, click on the Functions button on the very finish of the toolbar. This opens a drop-down menu of formulation (a.ok.a. capabilities) that Google Sheets helps. Click by way of the classes to search out the formulation you need. Once you discover it, click on the formulation to insert it contained in the cell the place you’ve set the cursor. For an entire record of formulation/capabilities that work in Google Sheets, see the official Google Sheets capabilities record.
IDG
Inserting a formulation in Google Sheets. (Click picture to enlarge it.)
Manage a number of sheets in a spreadsheet
Each web page of your spreadsheet is represented as a tab alongside the underside of the display screen. Clicking one among these tabs exhibits you its corresponding sheet on the display screen.
To add a brand new, clean sheet to your spreadsheet: Click the “+” on the lower-left nook.
To delete or rename a sheet: Click the down arrow on the sheet’s tab. From the menu that opens, choose Delete or Rename.
To change the web page order of your sheets: Click-and-hold a sheet’s tab, drag it left or proper to a different spot within the row of tabs, and launch it.
IDG
Use the tab menu to delete, duplicate, copy, rename, or transfer a sheet. (Click picture to enlarge it.)
Select a shade theme on your spreadsheet
On the menu bar alongside the highest of the display screen, click on Format and choose Theme. A sidebar will open alongside the appropriate, displaying you many giant thumbnails of themes. Scroll by way of the record and click on the thumbnail for the one that you just need to use on your spreadsheet. You can apply a theme to a brand new clean spreadsheet or to 1 that already has a design.
IDG
Applying a theme to a spreadsheet. (Click picture to enlarge it.)
Get help as you enter information
As you’re coming into formulation into cells, Google Sheets could make corrections to your formulation (if it thinks you’ve made a mistake), or fill in cells with instructed changes to your formulation. For instance, for those who’re coming into the identical formulation cell-by-cell down a column, but altering the formulation to correspond with the row quantity, Sheets could routinely insert your formulation into the cells additional down the column and apply what it predicts could be your succeeding changes to this formulation.
This clever help is on by default, however for those who discover that it will get in your method, you may flip it off: On the menu bar alongside the highest of the display screen, click on Tools > Autocomplete and deselect Enable formulation solutions and Enable formulation corrections.
IDG
If Sheets’ formulation solutions aren’t working for you, you may disable them. (Click picture to enlarge it.)
Clean up your information
Google Sheets will help you examine and edit the info consistency of your spreadsheet. On the menu bar on the prime of the display screen, click on Data > Data cleanup > Cleanup solutions. A sidebar will open to the appropriate of the display screen. This device analyzes your spreadsheet and presents solutions to enhance its look and efficiency, similar to including formatting to numbers, deleting duplicate rows, fixing inconsistent information, and extra. Try this out if in case you have an previous, complicated spreadsheet that might use a evaluate and freshening up.
IDG
Sheets can supply solutions for cleansing up your spreadsheet.
Navigate your spreadsheet sooner with named ranges
If your spreadsheet has a number of sheet pages, or a single sheet that may’t be seen suddenly in the primary window, use the “Named ranges” device to design it for simpler navigating. To open this device, click on Data > Named ranges on the menu bar on the prime of the display screen. Or, towards the upper-left nook of your spreadsheet, click on the down arrow and choose Manage named ranges. This opens a “Named ranges” sidebar alongside the appropriate of the display screen.
With this sidebar open, click on a cell or choose a spread of cells in your spreadsheet. Then, within the sidebar, click on Add a spread. Give this cell or vary of cells a novel identify. You can construct a listing of different named cells and cell ranges this manner.
From then on, everytime you open this sidebar or click on the down arrow on the higher left of your spreadsheet, you’ll be capable of leap your view of the spreadsheet proper to a cell or cell vary that you just named.
IDG
Naming cells or ranges will help you discover them quick later. (Click picture to enlarge it.)
Get solutions for a spreadsheet with the Explore device
On the lower-right nook, click on the Explore icon to open the panel for this device. Explore analyzes your spreadsheet and helps you calculate solutions to questions that you just sort in utilizing pure language.
For instance, say you could have a spreadsheet that lists gross sales figures (denoted as “price” in your spreadsheet) made by a selected individual (“Mary”). When you sort “What’s the sum of price by Mary?” Explore ought to present you the entire in gross sales that Mary made.
By default, Explore offers evaluation and solutions for the entire spreadsheet. You can alter this by clicking Edit within the higher proper of the Explore sidebar. Alternatively, you may choose a spread of cells that you really want analyzed after which click on the Explore button.
IDG
The Explore function helps reply questions on your information and suggests related charts, formulation, or pivot tables. (Click picture to enlarge it.)
Explore could present you charts that illustrate the info in these cells, however it doesn’t all the time accomplish that. This can depend upon how the info inside a spread of cells you’ve got chosen relate to row and column headings (e.g., the names of salespeople and the objects they have been promoting). Explore may counsel formulation and pivot tables on your spreadsheet.
You can drag a instructed chart or formulation from the Explore panel onto your spreadsheet, or insert a pivot desk into your spreadsheet by hovering the pointer over it and clicking the Insert pivot desk button.