Google Sheets Video Training


Over fifty of us learned from Bob Mac Leod about spreadsheets in March and April. Last Thursday, Bob presented part two, focusing on Google Sheets. (Don’t miss part one: Excel / Google Sheets Tips and Tricks 2019.)

This video recording will be shared in an all-Campus email in a few weeks.

I’ve made some notes to go along with the video of his presentation, so join me for…

Google Sheets with Bob Mac Leod

Open Google Drive and get ready to learn something new about Sheets. I have. Try the keystrokes and other shortcuts that Bob mentions.

If you don’t have time for the entire seminar, use my outline to jump to the topics you’re interested in. The next link is Bob’s video on Google Drive:

Bob Mac Leod on Google Sheets Video

Outline and Links for Google Sheets Video

Introduction

Google may provide a keystroke, a button and a menu item for one function. We have different options to do the same task in a way that is most comfortable for us.

Google Sheets is helpful for sharing (as opposed to Excel).

Create New Sheet (3:30)

Start in Google Drive.

Often Bob uploads an Excel spreadsheet that he wants to share through Google Sheets. He can only look at it in Drive. The next step is to convert it to Google Sheets’ format before he can edit it.

8:30 Or create a new blank Google Sheets spreadsheet.

Some Uses for Spreadsheets (9:00)

I’ve added to what Bob mentions.

  • Lists
  • Mailing lists
  • Budgets and accounting
  • Inventory
  • Calculations
  • Tables
  • Schedules and itineraries
  • Agendas
  • Trip planning
  • Goal planning and tasks
  • Seating charts
  • Checklists
  • Project management
  • Data transfer
  • Forms
  • Logs
  • Graphs and charts
  • and more

Freeze Panes for Headers (10:15)

Format the Header (12:45)

How to Record and Use a Macro to Format the Header for You (14:00)

Click on Tools/Macro and follow along with Bob.

Most Features Are in the Button Bar (19:00)

  • Use Control-Z for “undo.”
  • Use Control-Y for “redo.”

Print (20:00)

Print the whole sheet or “selected cells.”

Keyboard Shortcuts and Formatting Your Sheet (21:00)

Try it out:

  • Control+an arrow key for “jumping”
    • Right and left: by words
    • Up and down: by paragraphs
  • Shift+an arrow key for selecting
    • Right and left: by character
    • Up and down: by line

Viewing the Version History of Edits on a Sheet (23:30)

Find out what the most recent edits were and who did them.

Formatting Numbers (24:45)

  • Percentages
  • Removing and rounding decimals

26:10 The formula Bob typed in Column E is =C2*D2.

Bonus on Formulas

Formulas are the same in Google Sheets and in Excel. Here’s a few you’re likely to use:

  • Basic functions are +, -, *, /
  • SUM (or the Σ button)
  • AVERAGE (or the Σ button)
  • NOW shows the current date and time

It’s easy to find the exact information you need about formulas. For all formulas, start with an equal sign and then reference a cell or put in the numeric value(s). This video shows how to do basic math in either Excel or Sheets.

Autofill (29:00)

If you start a series, like the days of the week or the months of the year, Excel will autofill these across a column or row. Select and then drag the handle.

  • Formulas
  • Days of the week
  • Months
  • Dates

We have more number format options in Sheets than in Excel.

How to Display Numbers with Leading Zeros (31:00)

Using the apostrophe key first will give a number a text value. It’s better to use a custom format to keep numerical formatting. Bob uses 0000000 as the custom format for a 7-digit staff number

Format Fonts for Text (33:45)

Borders, Alignment and Text Wrapping (34:30)

Border creation and options are slightly different in Excel.

Auto-sizing Columns and Rows (39:30)

Moving Rows or Columns (41:30)

Rotating Text (43:45)

Zoom Button for Larger View (45:00)

Beyond the Button Bar (45:30)

Bob was running out of time. He mentioned these next features so we know what we could do. I’ll supplement his video with some information and links for you.

Bob used the formula =WEEKDAY(C2) in cell B2 which returns a value from 1 to 7. To get “Tue” he had formatted column B in these steps:

  • Select column B
  • Select Format / Number / More Formats / Custom Date and Time Formats
  • Click on Day
  • Choose “Day as Abbreviation”
  • Click on Apply

Learn more about the WEEKDAY formula from ExcelJet.

Make Your Own Dropdowns (47:00)

Bob created a table with his dropdown data on another page of his Google Sheets. He named the tab “Table.”

He used Data / Data validation and selected his criteria as “List from a range.” Read this help article from Google for the details of creating a Dropdown list.

Display Part of a Spreadsheet in Another Sheet (52:45)

Use the IMPORTRANGE formula to import a range of cells from one page to another. This article from Google should help you.

The formula is IMPORTRANGE(spreadsheet_url, range_string). You may also need tab! as part of the range string. Follow the steps in the article.

Insert Cell Totals from Pages in to the Main Sheet (55:00)

If you’re collecting data on pages in a spreadsheet, you’ll want a main page to show a bigger picture. Your specific expenses and totals are under each tab. For instance, registrations, rental space, food, lodging, and speaker expenses would be under separate tabs. Carry the totals from these to show them on the main page for a conference. I found a basic video that may help you get started with this skill.

Name Your Spreadsheets So You Can Find Them Again (56:45)

Figure out a meaningful code system and stick to it. (More below.)

Use Search to Find Your Google Sheet (57:30)

Click search and use the Advanced Search Option. Here’s the search Bob was doing in-the-moment with a specific title.

More on Naming (58:45)

To make searching easier use YYYY-MM-DD (or  YYYYMMDD) followed by a description when you name your Google Sheet.

What other topics are you interested in?

MacLeod

Bob Mac Leod, Operations Director in Global Fund Development, is a spreadsheet whiz. He and his wife, Kathy, have two sons. Bob blogs about support raising software, and has more than 2,400 spreadsheets on his computer

 

 

NOTES:

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.