Tag: Google Sheets

Using the IF Statement to Keep Your Data Clean in Google Sheets

By

Get Shift Done: Tips and Tricks

The IF statement surely is among the most commonly-used formulas in Google Sheets. It’s used for looking up data or making calculations under specific conditions. But did you know that the IF statement can help you keep your sheets looking clean and more readable?

That can be important, or at least a time-saver. When setting up a spreadsheet into which you import, add, or copy and paste data frequently, it can be a pain to drag or copy formulas down to your new data.

For example: We created a spreadsheet in which to track inventory for our gift shop. We have a tab for when we purchase inventory, and another for when we sell inventory. Instead of looking up the inventory number, we use a SUMIF statement that looks for the inventory name matching the item we sold, and then fills in the inventory number for that item.

Read More

How to Create a Dynamic Chart in Google Sheets Using Google Forms

By

Get Shift Done: Tips and Tricks

Google Forms is an extremely handy application in the Google Drive suite. You can use Forms to collaborate on documents, transfer files to other users, and create web forms that submit the data right into a Google Spreadsheet. But Google Forms are even more powerful when you integrate them with Google Sheets.

For example, you can create a Google Form that automatically saves the data collected into a Google Spreadsheet. The obvious benefit is collecting the results into a single spreadsheet for your own analysis or what-have-you. However, the form/spreadsheet integration can help you create a dynamic chart that updates each time with every new form entry.

Let’s say, for example, we are managing a charity fundraising website that includes a request for the site visitor to make a pledge for a donation. Each sponsor can fill out the fields in the Google Form, pledging the amount of money to donate. So far, so good. However, in addition to a list of sponsors and their contact information, we would like to track how much money has been donated, and what regional location is raising the most — ideally with some sort of visual to show progress.

Read More

Easily Add Images to Your Google Spreadsheet Using the =IMAGE Function

By

Get Shift Done: Tips and Tricks

You may already know that Google Sheets can insert images into the document using the “Insert Image” option, but it has limitations. In particular, you cannot control the display very well, and the image shows above the cells in the document.

Instead, by using the =IMAGE formula in Google Sheets, you easily can add images within the cell and managing how the image sizes.

Let’s say we have a series of photographs from a recent trip that we’d like to manage. We want to capture each image’s unique date, where the photo was taken, and notes about its purpose. By using the =IMAGE spreadsheet formula, we can display each image in a cell, as well as organize the other information in the rest of the row.

Read More

Add Colorful Charts to Your Google Spreadsheets

By

Get Shift Done: Tips and Tricks

Executives and clients often like to see a visualization of the numbers you are presenting, and they certainly can help tell your story clearly.

Google documents can include a chart to best illustrate what you are trying to say. However, a simple copy from a Google Spreadsheet and a paste into your Google Doc doesn’t work; at a minimum you have few controls over the display. The import feature from Google Sheets to a Google Doc is a better way to go.

The first step is to create a chart in the Google spreadsheet. After selecting the data to display, click the “Insert” menu item and choose “Chart.” Google shows a list of options; play around with your data and chart types to discover which chart works best for your purpose. In other words: Customize the chart (and its colorfulness) in Google Sheets, not in Google Docs.

Read More

Three Ways to Format Cells in Google Sheets So Numbers Stand Out

By

Get Shift Done: Tips and Tricks

Google Spreadsheets can be mundane to look at, with all those numbers just sitting there staring at you in black and white. It’s easy to fall into a vortex of numbers determining what number goes where, totals, what needs to be done, dates, and so on.

How to keep it all straight? Just as with text documents, color and formatting are your friends; conditional formatting makes it easy.


Read More

Google Sheets: Consolidating Data from Multiple Tabs with SUMIF

By

Get Shift Done: Tips and Tricks

The tabs feature in Google Sheets is a great organizational tool, and it keeps thing neat and tidy. However, sometimes we need a summary spreadsheet, an at-a-glance overview of the data. Or perhaps it’s useful to see a single chart that updates as we add new information. In either case, the quickest solution is to use the =SUMIF function, and it’s easier than it initially appears.

As an example, let’s create a summarized list of the trucking suppliers we pay. First, add a new tab to the Google Sheet that records the column headers that match the information to summarize.


Read More