Spreadsheets are ubiquitous in the office, whether you’re a solo entrepreneur or an analyst at a large corporation. However, many folks don’t utilize the full potential of spreadsheets.

In this post, you’ll learn seven techniques that will set you apart at work. You’ll become more efficient with keyboard shortcuts and macros. You’ll see how to add interactivity to your Sheets with checkboxes. You’ll learn about sparklines, which are miniature charts that show trends in your data.

Today, try mastering one of these techniques and implement it in a work project.

1. Master Keyboard Shortcuts

One of the best long-term efficiencies you can learn is to master Google Sheet keyboard shortcuts.

It might feel clumsy at first, but persevere and it’ll pay off in spades as you become more efficient at using Google Sheets. Think of it as moving from block letters to cursive writing, but in your spreadsheets.

Here are five of the best shortcuts:

1. Clear All Formatting in a cell or range

Mac: ⌘ + \

PC/Chromebook: Ctrl + \

2. Insert the current date in a cell

Mac: ⌘ + ;

PC/Chromebook: Ctrl + ;

3. Select all the data in a table

Mac: ⌘ + A

PC/Chromebook: Ctrl + A

4. Find and Replace

Mac: ⌘ + Shift + H

PC/Chromebook: Ctrl + H

5. Open the drop-down menu on filtered cell

Mac: Ctrl + ⌘ + R

PC/Chromebook: Ctrl + Alt + R

To see all of the available shortcuts, go to the menu: Help > Keyboard shortcuts

2. Create Checklists with Checkboxes

Checkboxes are an easy way to add some interactivity to your Google Sheets. Users can check or uncheck the box to cause an action to happen.

Consider the classic To-Do list, where a check indicates that a task is complete.

Add checkboxes from the menu: Insert > Checkbox

Adding conditional formatting to highlight completed rows will make comprehension of the data quicker.

To achieve this, follow these steps:

Go to the menu: Format > Conditional formatting
Under the Format Rules, select: Custom formula is
Enter this formula: =$B2=TRUE
Set the format to: red background color, dark red text and strikethrough

The crucial detail is the “$” before the B of the custom formula, which locks the conditional test to column B for each row.

The conditional formatting only tests the custom formula for column B, but applies it to the whole of each row.

3. Use Sparklines To Visualize Your Data

Sparklines are small, lightweight charts, typically without axes, which exist inside a single cell in your spreadsheets.

They’re a wonderful, quick way to visualize your data, without needing the complexity of a full-blown chart.

You’ve almost certainly seen them in financial literature, to show the trend of a stock over some period of time.

Here’s an example of sparklines implemented in Google Sheets:

The sparkline formula syntax is:

=SPARKLINE(data,[options])

Data refers to the dataset (the range of values) you want to plot as a sparkline.

The options is an optional argument, used to specify things like chart type (e.g. column), color and other specific settings. If you omit the options, the sparkline defaults to a line chart.

An example sparkline looks like this:

=SPARKLINE(A1:A20,{“color”,”red”})

Here’s everything you need to know about sparklines in Google Sheets.

4. Create a Status Bar in your Sheets

The Sparkline formula can be used to create a status bar showing progress towards a goal.

For example, consider this checklist in Google Sheets. Users are given a visual indication of their overall progress as they complete the steps in the process.

Here’s the sparkline formula that creates that dynamic status bar:

=SPARKLINE(COUNTIF(A6:A,TRUE),{“charttype”,”bar” ; “max”,10 ; “color1″,”red”})

The COUNTIF(A6:A, TRUE) function counts how many of the checkboxes in column A have been checked (i.e. have a TRUE value).

The output of this is a single number, between 0 and 10 in this example, which is passed to the sparkline function.

The sparkline is set to bar chart type, with the first option: “charttype”,”bar”

Next, a maximum value is specified, so that it can compare the count of checked checkboxes (e.g. 4) against the maximum possible number (10 in this example) to get the percentage completion.

Finally, set a custom color for the sparkline with the option: “color1″,”red”.

Here are the full details on how to build this checklist template in Google Sheets.

5. Use the Importrange formula to connect Google Sheets

Because Google Sheets are files in the cloud, and not on our local desktop, you can’t simply point at a cell in a different file and expect it to connect.

The Importrange function brings data from one Google Sheet file into a different Google Sheet file.

The name is a giveaway. It imports a range of data into your Sheet.

The syntax is:

=IMPORTRANGE(“URL of other Sheet”,”the data range”)

When you’re typing the data range of your IMPORTRANGE function, you can’t click across to your other sheet to highlight it. Rather, you have to type the range reference (e.g. “Sheet1!A1:D10) into the Importrange formula.

When you use Importrange, you have to authorize the function by clicking the “Allow Access” button. You only have to do this the first time.

You need at least view-level access to the other Google Sheet you want to retrieve data from.

6. Group Dates In Your Pivot Table

Suppose you have a table of data with a column of dates and you’re interested in summarizing that daily data at a higher level, e.g. by month.

It’s cumbersome to summarize with formulas but it’s super easy and just a few clicks in a pivot table.

Create a pivot table and add the date column to the rows section of your pivot table. You’ll also want to add a value to the pivot table (e.g. revenue, profit, pageviews etc.)

Right click on any date in the pivot table and choose “Create pivot date group” and select the grouping level you want.

The individual dates will collapse down to the date groups you’ve chosen.

To remove the grouping, right click on any row and select “Ungroup pivot items”

7. Macros

Macros are small programs created inside of Google Sheets to automate processes. They don’t require you to write any code.

They work by recording your actions as you do something and saving these actions as a “recipe” that you can reuse again with a single click.

For example, you might apply a standard format to your charts and tables. It’s tedious to do this manually each time. Instead record a macro so you can apply the formatting at the click of a button.

Macros are accessed in the menu: Tools > Macros

Select “Record macro” and the macro recorder begins recording your actions. Once you’re done, save the macro and give it a name.

You can now run that macro with a single click from the macros menu. It will run through the actions again, saving you the time of having to do it manually.

For more information on how to use macros, see this automation guide.

Want more tips? Sign up to my newsletter to get weekly Google Sheet tips.

Ben Collins is a Google Sheets developer, data analytics instructor and recognized Google Developer Expert for Google Workspace technology. He’s taught over 30,000 students from 1000+ companies through his online courses and tutorials on his website benlcollins.com.

Ben Collins

Must Reads