July 2, 2022


the blog news

Google Sheets cheat sheet: Learn how to get began

Google Sheets is a robust spreadsheet app that you simply use by way of your internet browser. It shops your spreadsheets within the cloud with Google Drive. Anybody with a Google account can use Sheets and Drive free of charge. Each are additionally a part of Google Workspace  (previously G Suite), Google’s subscription workplace suite for enterprise and enterprise clients.

This information will educate 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 cell app for Android and iOS. This information will notice any main variations within the cell 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: On the prime of the Sheets residence web page, you’ll see a “Begin a brand new spreadsheet” header, with a row of thumbnails beneath. To begin a brand new, clean spreadsheet, click on the Clean thumbnail or choose a template from the thumbnails to the fitting. 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.

google sheets cheat sheet 01 home page IDG

The Google Sheets residence web page. (Click on picture to enlarge it.)

Under the thumbnails you’ll see an inventory of spreadsheets saved in your individual 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 not too long ago opened. Clicking the “AZ” icon on the higher proper of this record adjustments the kind order to Final modified by me, Final modified, or in alphabetical order by spreadsheet title. It’s also possible to browse to a selected folder by clicking the folder icon subsequent to the “AZ” icon.

Lastly, you need to use 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 in search of.

From Google Drive: Spreadsheets saved in your Google Drive are listed in the primary window of the Drive residence 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. It’s also possible to 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 wish to use a template to begin a brand new spreadsheet: Click on 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.

See also  Google brings automated summaries to Workspace ‘Areas’
google sheets cheat sheet 02 open sheet google drive IDG

Creating a brand new spreadsheet from Google Drive. (Click on picture to enlarge it.)

Add a Microsoft Excel spreadsheet for modifying in Google Sheets

If you wish to edit a spreadsheet that’s not already saved in your Google Drive, you have to first add it to Drive. You are able to do this from both Sheets or Drive.

From Google Sheets: Click on the folder (“Open file picker”) icon that is on the upper-right of the spreadsheets record. On the panel that opens, click on the Add tab. Drag-and-drop your Excel file (.xls or .xlsx) onto this panel, or browse your PC’s drive to pick out it and click on Open.

google sheets cheat sheet 03 upload sheets IDG

To add an Excel spreadsheet, drag the file onto this panel or browse to it in your arduous drive. (Click on picture to enlarge it.)

From Google Drive: Click on the New button, then File add, and choose the Excel file out of your PC’s drive and click on Open.

google sheets cheat sheet 04 upload drive IDG

Importing a spreadsheet in Google Drive. (Click on picture to enlarge it.)

Historically, if you uploaded Excel information to Google Drive, they had been robotically transformed to Sheets format. That’s nonetheless the case if you add Excel information by way of the Google Sheets residence web page.

Nevertheless, Google additionally helps the power to edit Microsoft Workplace information of their native format. By default, any .xlsx information that you simply add by way of Google Drive will stay formatted as Excel paperwork. You’ll be able to edit and collaborate on an Excel file proper in Sheets, with all adjustments made by you or your collaborators saved on to the Excel file. On the Google Sheets and Drive homepages, native Excel information can be denoted with a inexperienced “X” icon, and if you open a local Excel file in Sheets, you’ll see an “.XLSX” flag to the fitting of the doc title.

google sheets cheat sheet 05 file list excel icon IDG

Microsoft Excel-formatted information present the Excel icon (prime) as a substitute of the Google Sheets icon (backside).

When you’d somewhat have Google convert Excel information to Sheets format robotically if you add them by way of Google Drive, click on the gear icon within the upper-right nook of the Drive residence display screen and choose Settings from the drop-down menu. Within the Settings field subsequent to “Convert uploads,” test the checkbox marked Convert uploaded information to Google Docs editor format.

To transform Excel macros to Google Sheets: If an Excel file you’re changing to Sheets format incorporates VBA (Visible Fundamental for Purposes) code, which is used for macros, and also you’re subscribed to a Google Office Enterprise Plus plan, set up the Macro Converter add-on for Google Sheets. It will do as its title implies: convert your Excel file’s VBA code to the App Script format that Google Sheets makes use of, so as to execute these macros in your spreadsheet when it’s loaded into Google Sheets.

Work in a spreadsheet

Whenever 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 vary it, click on wherever contained in the title and begin typing.

google sheets cheat sheet 06 sheets editing interface IDG

The Google Sheets modifying interface. (Click on picture to enlarge it.)

The menu bar is slightly below the spreadsheet’s title and contains 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 helps you to insert cells, columns, or rows; graphical components 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 beneath the menu bar (described beneath), plus instructions for conditional formatting and alternating colours.
  • Knowledge has instruments for sorting a spread of columns in alphabetical order, making a filter, defending sheets and ranges, and validating and information.
  • Instruments results in miscellaneous objects similar to making a type and operating spell-check.
  • Extensions helps you to work with add-ons, macros, scripts, and apps.
See also  The enterprise consumer's information to Google Voice
google sheets cheat sheet 07 data menu IDG

The information menu contains instruments for sorting, filtering, validating, and cleansing up information. (Click on 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, %) and font (kind, dimension, fashion, colour). Different buttons on this toolbar allow you to change the background colour of a cell, merge cells, and modify 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 fitting finish of the toolbar. This opens a sidebar from which you’ll be able to choose a preset chart or click on the Customise tab to create your individual chart.

google sheets cheat sheet 08 insert chart IDG

Inserting a chart in Google Sheets. (Click on picture to enlarge it.)

To insert a formulation, click on the Features 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 on by way of the classes to search out the formulation you need. As soon as you discover it, click on the formulation to insert it contained in the cell the place you’ve set the cursor. For a whole record of formulation/capabilities that work in Google Sheets, see the official Google Sheets capabilities record.

google sheets cheat sheet 09 insert formula IDG

Inserting a formulation in Google Sheets. (Click on picture to enlarge it.)

Handle a number of sheets in a spreadsheet

Every web page of your spreadsheet is represented as a tab alongside the underside of the display screen. Clicking one in all these tabs exhibits you its corresponding sheet on the display screen.

So as to add a brand new, clean sheet to your spreadsheet: Click on the “+” on the lower-left nook.

To delete or rename a sheet: Click on the down arrow on the sheet’s tab. From the menu that opens, choose Delete or Rename.

To vary the web page order of your sheets: Click on-and-hold a sheet’s tab, drag it left or proper to a different spot within the row of tabs, and launch it.

google sheets cheat sheet 10 tab menu IDG

Use the tab menu to delete, duplicate, copy, rename, or transfer a sheet. (Click on picture to enlarge it.)

Choose a colour theme in 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 fitting, displaying you many giant thumbnails of themes. Scroll by way of the record and click on the thumbnail for the one that you simply need to use in your spreadsheet. You’ll be able to apply a theme to a brand new clean spreadsheet or to at least one that already has a design.

See also  Android tablets? Chromebook tablets? How Google thinks each can thrive
google sheets cheat sheet 11 themes IDG

Making use of a theme to a spreadsheet. (Click on picture to enlarge it.)

Get help as you enter information

As you’re coming into formulation into cells, Google Sheets might make corrections to your formulation (if it thinks you’ve made a mistake), or fill in cells with steered changes to your formulation. For instance, should you’re coming into the identical formulation cell-by-cell down a column, but altering the formulation to correspond with the row quantity, Sheets might robotically 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 should you discover that it will get in your approach, you’ll be able to flip it off: On the menu bar alongside the highest of the display screen, click on Instruments > Autocomplete and deselect Allow formulation options and Allow formulation corrections.

google sheets cheat sheet 12 autocomplete IDG

If Sheets’ formulation options aren’t working for you, you’ll be able to disable them. (Click on picture to enlarge it.)

Clear up your information

Google Sheets might help you test and edit the info consistency of your spreadsheet. On the menu bar on the prime of the display screen, click on Knowledge > Knowledge cleanup > Cleanup options. A sidebar will open to the fitting of the display screen. This device analyzes your spreadsheet and presents options to enhance its look and efficiency, similar to including formatting to numbers, deleting duplicate rows, fixing inconsistent information, and extra. Do that out if in case you have an previous, advanced spreadsheet that would use a assessment and freshening up.

google sheets cheat sheet 13 data cleanup IDG

Sheets can supply options for cleansing up your spreadsheet.

Navigate your spreadsheet quicker with named ranges

In case 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 Knowledge > 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 Handle named ranges. This opens a “Named ranges” sidebar alongside the fitting 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 title. You’ll be able to construct an inventory of different named cells and cell ranges this fashion.

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 to soar your view of the spreadsheet proper to a cell or cell vary that you simply named.

google sheets cheat sheet 14 named ranges IDG

Naming cells or ranges might help you discover them quick later. (Click on picture to enlarge it.)

Get options for a spreadsheet with the Discover device

On the lower-right nook, click on the Discover icon to open the panel for this device. Discover analyzes your spreadsheet and helps you calculate solutions to questions that you simply kind in utilizing pure language.

For instance, say you have got a spreadsheet that lists gross sales figures (denoted as “worth” in your spreadsheet) made by a selected particular person (“Mary”). Whenever you kind “What’s the sum of worth by Mary?” Discover ought to present you the whole in gross sales that Mary made.

By default, Discover gives evaluation and options for the entire spreadsheet. You’ll be able to modify this by clicking Edit within the higher proper of the Discover sidebar. Alternatively, you’ll be able to choose a spread of cells that you really want analyzed after which click on the Discover button.

google sheets cheat sheet 15 explore pane IDG

The Discover characteristic helps reply questions on your information and suggests related charts, formulation, or pivot tables. (Click on picture to enlarge it.)

Discover might present you charts that illustrate the info in these cells, but it surely doesn’t all the time accomplish that. This will rely 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). Discover may counsel formulation and pivot tables in your spreadsheet.

You’ll be able to drag a steered chart or formulation from the Discover 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.