Skip to main content
Tag

Sheets

Google Sheets Cheat Sheet

By Product

One of the most useful tools in Google’s free online office suite is Google Sheets. It’s an online spreadsheet program that is easy to learn. It’s also a lot cheaper than Microsoft Excel, and includes some features that Excel doesn’t have. The Google Sheets Cheat Sheet lets you quickly access examples, browse a list of Google Sheets features/icons, or identify Google Sheets shortcuts. It’s described below. You can order a printed copy on the Cheat Sheets Store. It’s also possible to download the PDF edition of the guide.

Google Sheets Cheat Sheet: What’s inside?Google Sheets Cheat Sheet

This handy reference and cheat sheet contains tips, examples, and easy-to-read annotated screenshots of Google Sheets, the free online spreadsheet program and Microsoft Excel alternative. The four-panel reference is printed on 8.5 by 11 inch high-quality card stock, perfect for desks, walls, and shelves. It has holes for three-ring binders. Topics include:

  • Overview of Google Sheets on the Web, including annotated screenshots of the main page and formatting toolbar.
  • Sheets basics: Creating, renaming, saving, and copying spreadsheets
  • How to edit while offline
  • How to add, edit, and hide data
  • How to import and export .txt, .csv, .pdf, and .xlsx files
  • Examples of basic formulas, functions, auto-fill
  • How to perform simple math and calculate averages
  • Sorting and filtering data
  • How to create a chart or graph
  • Keyboard shortcuts for Chromebooks, Windows, and OS X

Note that the Google Sheets Cheat Sheet does not cover Google Drive, Google Docs, Google Slides, or other applications in Google’s free online office suite. Publisher i30 Media offers separate cheat sheets, including the Google Drive Cheat Sheet and the Google Docs Cheat Sheet.

The Google Sheets Cheat Sheet was created by the publisher of the top-selling guides Google Drive & Docs In 30 Minutes, LinkedIn In 30 Minutes, Excel Basics In 30 Minutes, and other titles.

How to order

To order a printed copy of the Google Sheets Cheat Sheet, visit the Cheat Sheets Store. The PDF can be downloaded using this secure order form.

PDF Printed 20 copies (25% off!)

Google Forms tutorial using the new forms interface

By Blog

I recently tried out the new Google Forms while writing an update to Google Drive & Docs In 30 Minutes. The new interface is a lot slicker than the old version of Google Forms, and gives more control over the look and feel of the forms. It’s a great way to create an online survey, or have people enter data for a variety of purposes. It’s also worth noting that Google Forms has been partially decoupled from Google Sheets, meaning it’s possible to create a form directly from Google Drive and see the results within Google Forms, instead of having to open up Google Sheets. This post will show what the new Google Forms interface looks like, and then give a brief Google Forms tutorial.

Google Forms is a tool to build online forms, which can then be emailed, shared on social media, or embedded on a public-facing website. The forms can really change the way you gather data. Think about it: Instead of manually entering data, you can make a simple form or survey, post it on the Web and let other people do the work for you! This tool is perfect for signup forms, surveys, and simple reporting.

Once a form has been created, it can be accessed via a Google link that you can email or post on a social network. The form can also be embedded on a blog or company Web page. Customization options can make the form look more professional, or match the fonts and colors you want to use. The data from the form is only visible to you and designated collaborators (as described in ).

The Google Forms tutorial below applies to the updated interface for creating new forms, which was rolled out in late 2015 and early 2016 for some users. I expect it will be rolled out to most Google Drive and Docs users later in 2016.

How to create a form

  1. You can either use an existing spreadsheet (select Tools > Create a form) or make a new form from Google Drive’s main screen by pressing the New button and selecting More > Google Forms.
  2. The form editor appears (see screenshot, below).
  3. Enter the title.
  4. Enter the description. Make it clear what the form is being used for, and add any instructions that can help people complete the form. Absent context or appropriate instructions, users may be reluctant to use the form, or they may enter the wrong type of data.
  5. Edit the first untitled question. Change the name of the question by clicking on the title. Change option labels by clicking on them. Select different question types from the drop-down menu labelled Multiple choice. Select Required to force users to answer a question.
  6. Add a new question using the Add question There are more than a half-dozen types of questions that can be used.
  • Short answer. A one-line text field.
  • Paragraph. Allows for longer answers.
  • Multiple choice. Create a multiple-choice question, with as many possible answers as you want.
  • Checkboxes. People can check off one or more items from a list.
  • Dropdown. Creates a drop-down menu.
  • Linear scale. Users choose from a range of numbers.
  • Multiple choice grid. Users fill in data according to a table.
  • Date or Time. Users can select the date or time (useful for scheduling purposes).

Google Forms tutorial based on the new Google Forms interface

The form builder has additional functions:

  • Icons allow form creators to add titles, sections, photos, and video.
  • Change the colors used in the form by clicking the easel icon.
  • Preview the form by clicking the eye icon.
  • The settings icon (look for the gear) controls who can use the form, as well as presentation options. Use the drop-down menu to select Anyone or one of the other options, if available.

When complete, click the Send button, which shows various distribution options. Email is the default choice, but social media icons, Web links, and embed code (which can be used to insert the form into a blog post) are other possibilities.

Data entered into the Web form can be accessed via the Responses tab at the top of the form editor. Click the Sheets icon to flow the data into a new or existing spreadsheet, which can then be formatted, sorted, filtered and otherwise manipulated. To return to a form, search for it in Google Drive or visit https://docs.google.com/forms.

I hope this Google Forms tutorial was useful. For more information, check out the updated version of Google Drive & Docs In 30 Minutes.

Google’s Office Compatibility Mode: Pros and Cons

By Blog

Users can edit Microsoft Word, Excel, and PowerPoint files in Google Docs, Sheets, and Slides using Google’s Office Compatibility Mode. This is a great feature that may save you the hassle of converting between Microsoft formats and the equivalent Google program — for instance, it may no longer be necessary to convert a Microsoft Word .docx file to Google Docs, make edits, and then convert the Google Docs file back to .docx.

Office Compatibility Mode comes built into Chromebooks and the mobile apps for Android and iOS, and can be activated on the Chrome browser on PCs and Macs (go to Window > Extensions, search for Office Editing for Docs, Sheets & Slides and install).

However, there are some limitations:

  • Office Compatibility Mode will not work with Internet Explorer, Firefox, Safari, or other browsers.
  • Files with the .doc, .docx, .xls, .xlsx, .ppt, and .pptx extensions can be edited if they were created in Microsoft Office 2007 or newer versions of Microsoft Office. Older files (created in Microsoft Office 2003 and earlier) are not supported unless they are resaved with a more recent version of Microsoft Office.
  • It may not be possible to edit large documents, especially large Excel spreadsheets.

Converting Microsoft formats for collaboration

Collaborative editing (described in Chapter 6 of Google Drive & Docs In 30 Minutes) is not possible when Microsoft Office files are opened for editing. However, it is possible to convert Office files to the equivalent Google formats for collaborative editing (see screenshot, below). Conversion can take place automatically during the upload process. Alternately, you can select the uploaded file in Drive and use one of the following methods to convert it:

  • Right-click over the selected file and choose Open with.
  • Click the More Actions icon (which looks like three vertical dots) at the top of the screen and select the option to open it in Google Docs/Sheets/Slides.
  • Preview the file, then select the Open with option

Google Drive Office Compatibility mode vs. collaborative editing

Google Sheets: How to make a pie chart

By Blog, Video

In the following quick video, learn how to make a pie chart in Google Sheets, using the new interface released in 2015. This covers the basics of creating a pie chart, but the instructions apply to bar charts, line charts, etc. It assumes the data used to create the chart is valid — for instance, if it’s a pie chart the values should add up to 100.

The video is just two minutes long, and starts below:

For more tips about Google Sheets, including conversion between Microsoft Excel and CSV formats, and how to use the Sheets app for iOS and Android devices, check out Chapter 3 of Google Drive & Docs In 30 Minutes.

How to format cells in Google Sheets

By Uncategorized

The following video covers formatting basics in Google Sheets, using the Google Sheets toolbar. Bold, text color, fill color, and other options are demonstrated. The instructions apply to the new version of Google Sheets on a PC, Mac, or Chromebook:

Note that formatting options are far more limited on the Google Sheets app for iPhones, iPads, and Android devices.

For more tips and tricks about Sheets, check out the latest edition of my guide, Google Drive & Docs In 30 Minutes.

Google Sheets: How to embed a live spreadsheet

By Blog

Recently, I published Sample spreadsheet: AutoFill on the official Excel Basics In 30 Minutes website. In addition to including a sample Excel .xlsx file, I also embedded a live version of the same spreadsheet that people can edit and play with, right on the blog post. This post explains how to embed a live spreadsheet using Google Sheets, the free online spreadsheet program offered by Google. It involves a small hack, which I will describe below.

While Google Sheet’s Share button makes it easy to share a link to a spreadsheet, and allows the owner of the spreadsheet to enable anyone to edit it, editing is not possible if the embed option is chosen for the File>Publish to the Web feature.

That is, when you embed the spreadsheet on a blog post or Web page, you won’t be able to edit or format the cells, or create formulas. For example, here is an embedded spreadsheet. Notice you can highlight cells, but can’t input or change information, or use any other features of Sheets:

Google Sheets how to embed a live spreadsheet that can be edited

But there’s a workaround. Follow these steps:

  1. Open the spreadsheet, and select _File>Publish to the Web
  2. In the Publish to the Web popup, press the Start publishing button
  3. Under Get a link to the published data, change Web page to HTML to embed in a page
  4. Copy the embed code, which will look something like this:

After copying the text (Edit>Copy) and pressing the Close button, paste the embed code into a text editor or the blog editor (make sure the raw code or HTML view is showing). At this point if you publish the page, the sheet will be viewable but not editable. Follow these steps to enable live editing of the spreadsheet on the page:

  1. Go back to the spreadsheet in Google Sheets, and press the Share button
  2. In the Share with others popup, click Advanced
  3. Under Who has access, click the Change link and select On: Anyone with link
  4. At the bottom of the Who has access popup, change the Can view drop-down to Can edit and click Save
  5. The Sharing settings popup will appear. Copy the Link to share and close the popup

Now what needs to happen is most of the URL in the iframe embed code needs to be deleted, and replaced with the link you’ve just copied from the Sharing settings popup. Basically, delete everything in the quotation marks after src= starting with https:// but leave &widget=true. Then, paste in the other URL before the ampersand. I’ve highlighted the part of the iframe code that needs to be replaced:

iFrame Google Sheets URL

At that point, once you’ve published the page, the live spreadsheet will not only be visible, but it will also be editable. To see an example, visit this page on the official Excel Basics In 30 Minutes website.

If this tutorial has been helpful, please consider tweeting or sharing it elsewhere.

Google Drive shortcuts: A basic reference list

By Blog

Google Drive shortcuts: Why use them?

Google Drive shortcuts

Google Drive shortcuts use combinations of keys to issue commands or perform formatting.

Keyboard shortcuts let users issue commands and perform basic formatting. They can save a lot of time. Instead of moving the cursor with a mouse to select a menu item or toolbar icon, you simply hold down two or more specific keys at the same time.

Here is a basic reference list of Google Drive shortcuts. It applies to Google Drive as well as Google Docs, Sheets and Slides.

To get a printed list of Google shortcuts, check out our Google cheat sheets for Drive, Docs, and Sheets — each one costs less than US$5, is printed on high-quality card stock, hole-punched for easy storage, and contains shortcuts, examples, and annotated lists of features.

All of the listed shortcuts work in the Google Chrome browser. Some may not work in Internet Explorer or Firefox. Google recommends the Chrome browser for Google Drive and other Google applications.

Google Drive home screen shortcuts

The following keyboard shortcuts work on Windows and Mac desktops and laptops as well as Chromebooks.

c – Create new file

u – Upload new file

o – Open file

d – Information about file

j or down arrow – Advance to next file

k or up arrow – Go back to previous file

x – Select file

t – Open settings pane

n – Rename selected file

Keyboard shortcuts for Google Docs, Sheets and Slides

Some keyboard shortcuts are identical to those used in Microsoft Office and other programs. For instance, copying and pasting text is the same in Google Docs and Microsoft Word.

Here is a list of shortcuts for Google Docs, Sheets and Slides:

Windows

Control + / – Show all keyboard shortcuts

Control + – Go to next misspelling (Docs only)

Control + Shift + c – Word count (Docs only)

Control + o – Open file

Control + p – Print file

Control + f – Find text

Control + z – Undo

Control + y – Redo

Control + b – Bold text

Control + i – Italicize text

Control + u – Underline text

Control + a – Select all

Control + x – Cut selected text

Control + c – Copy selected text

Control + v – Paste

Control + k – Create link to Web address

Alt + Shift + f – Open file menu

Alt + Shift + e – Open edit menu

Alt + Shift + v – Open view menu

Alt + Shift + i – Open insert menu

Alt + Shift + t – Open tools menu

 

Mac

Command + / – Show all keyboard shortcuts

Command + – Go to next misspelling (Docs only)

Command + Shift + c – Word count (Docs only)

Command + o – Open file

Command + p – Print file

Command + f – Find text

Command + z – Undo

Command + y – Redo

Command + b – Bold text

Command + i – Italicize text

Command + u – Underline text

Command + a – Select all

Command + x – Cut selected text

Command + c – Copy selected text

Command + v – Paste

Command + k – Create link to Web address

Control + Option + f – Open file menu

Control + Option + e – Open edit menu

Control + Option + v – Open view menu

Control + Option + i – Open insert menu

Control + Option + t – Open tools menu

Each one of our Google Drive, Google Docs and Google Sheets cheat sheets contains lists of keyboard shortcuts.

Storage of non-Google formats in Google Drive

By Blog

A reader of “Google Drive & Docs In 30 Minutes” wants to know more about storage of non-Google formats in Google Drive. This could include anything from images to PDFs to Microsoft Office documents. It’s possible to upload such documents to your Google Drive account and use it as an online drive that synchronizes to all PCs and devices that have the Google Drive application installed. However, there are a few issues that make Google Drive a bit different than Dropbox or other online storage services:

  1. There are no account limits on the size or amounts of documents saved in native formats (Docs, Sheets, Slides, etc.). In other words, you can save as many of these files as you like, as long as they were created using Google Drive.
  2. Non-Google Drive formats are limited to 5 gigabytes of free storage space per account. When you hit the limit, you have to buy more storage space, or start deleting files.
  3. Non-Google files that other people share with you will not be counted toward your Google Drive account total.
  4. When browsing the files in your Google Drive account, non-Google formats are clearly identified in both the online and offline version using icons (see image at the bottom of this page).
  5. Google Drive’s online interface has many advanced features that cannot be found in Dropbox or other services, such as automatic conversion of Microsoft Word, Excel, and other Office file formats.
  6. There are lots of synchronization options, such as being able to control which subfolders are synced with the master repository online.
  7. For cross-device syncing, I’ve found that the Google Drive application needs to be manually nudged in order to update. This is unlike Dropbox, which is completely automatic and requires no manual intervention to update.

I’ve extensively discussed conversion options between Microsoft Office and Google Drive in “Google Drive & Docs In 30 Minutes“, as well as in the free videos available here. However, in the coming days on this blog I will also cover:

  • Basics of uploading non-Google formats to Google Drive
  • Syncing and “nudging” the Google Drive application on PCs and Macs
  • Accessing previous versions of a non-Google file

Follow the links above to read about these specific issues.

Image: Non-Google formats are identified by different icons in a Google Drive folder. In this example, native formats have their own icons (.gdoc, .gsheet, .gslides), while a Microsoft Word doc and Adobe PDF file have their own icons.

Google Drive non-Google format