Google Sheets Tips: Work Faster & Smarter

These Google Sheets tips will help you cut repetitive work, avoid errors, and get more done in less time. You’ll learn keyboard shortcuts, formula tricks, data tools, and automation features that work right now in the current version of Sheets.

Why Google Sheets Feels Slow (and How to Fix It)

Most people lose time in Sheets because they repeat manual actions that have a built-in shortcut or formula. Clicking menus, dragging formulas down hundreds of rows, and retyping the same headers are all avoidable. The biggest time-savers come from learning the keyboard shortcuts, using array formulas, and letting Sheets handle formatting automatically.

Another common slowdown is a bloated file. Sheets with thousands of conditional formatting rules, heavy images, or entire columns referenced in formulas (like A:A instead of A1:A1000) can lag. Keep your data range tight and remove unused formatting to keep everything snappy.

Keyboard Shortcuts That Save Hours

Shortcuts are the fastest single upgrade. Here are the ones that matter most:

Ctrl + C / Ctrl + V – copy and paste, but use Ctrl + Shift + V to paste values only (no formatting or formulas).
Ctrl + Shift + L – toggle filters on and off.
Ctrl + Shift + Down Arrow – jump to the last filled cell in a column, then hold Shift to select the whole range.
Ctrl + Alt + Shift + H – open the full shortcut list (on Windows; Cmd + Option + Shift + H on Mac).
F4 – repeat the last action, like adding a row or applying a fill color.
Ctrl + Shift + 1/2/3/4/5 – apply number, date, currency, percentage, or scientific formats quickly.

If you’re on a Mac, replace Ctrl with Cmd for most shortcuts. The shortcut list inside Sheets is searchable, so you can find the one you need without memorizing everything.

Use Array Formulas to Avoid Dragging

Instead of typing a formula in one cell and dragging it down 500 rows, use an array formula that fills the whole column at once. For example, if you have quantities in column A and prices in column B, put this in C1:

=ARRAYFORMULA(A2:A100 * B2:B100)

This calculates every row in one go. Any new row added within the range will automatically calculate, too. The same works with IF statements, VLOOKUP, and text functions. Just wrap the function in ARRAYFORMULA and reference the full column range.

One caution: array formulas can slow down very large sheets if you reference entire columns like A:A. Use a bounded range like A2:A1000 instead.

Data Cleanup Tricks You Can’t Live Without

Messy data is the #1 reason people waste time. These built-in tools fix common problems in seconds:

Split text to columns: Select a column, go to Data → Split text to columns, and Sheets will detect the delimiter (comma, space, semicolon) automatically.
Remove duplicates: Data → Data cleanup → Remove duplicates lets you choose which columns to check. It keeps the first occurrence and deletes the rest.
Trim whitespace: Use =TRIM(A1) to remove extra spaces, or use the Data cleanup menu to trim all cells in a range.
Find and replace with regex: In Edit → Find and replace, tick “Search using regular expressions� to do powerful pattern matching, like replacing all phone number formats at once.

For example, if you have a column of full names like “Doe, John� and you want “John Doe,� you can use Data → Split text to columns with a comma delimiter, then use =B2 & " " & A2 to recombine them.

Pivot Tables and Filters for Instant Insights

Pivot tables are the fastest way to summarize large datasets without writing a single formula. Select your data, go to Insert → Pivot table, and choose where to put it. Then drag fields into Rows, Columns, Values, and Filters.

For example, with sales data you can drag “Region� into Rows and “Amount� into Values – Sheets will sum the amounts per region automatically. You can change the aggregation from SUM to AVERAGE, COUNT, MAX, or MIN by clicking the value field and selecting “Summarize by.�

Filters work hand-in-hand with pivot tables. Use the filter icon on the pivot table to focus on a specific date range or product category without altering the source data. Slicers (under Insert → Slicer) give you a visual button to filter charts and pivot tables with one click – great for dashboards.

Conditional Formatting to Spot Problems Instantly

Conditional formatting highlights cells based on rules, so you don’t have to scan rows manually. Select a range, go to Format → Conditional formatting, and set a rule. For example:

Highlight overdue dates: Custom formula =A2<TODAY()
Highlight duplicate entries: Custom formula =COUNTIF(A:A, A1)>1
Color-code based on a number: use “Greater than� or “Between� presets.

Use the “Custom formula is� option for the most flexibility. You can apply multiple rules to the same range – Sheets will layer them in order. Keep the range as narrow as possible to avoid slowing down your sheet.

Automation with Macros and Apps Script

Macros let you record repetitive actions and replay them with a button or keyboard shortcut. Go to Extensions → Macros → Record macro, perform your steps (like formatting a header or applying a filter), then save. You can assign a shortcut like Ctrl+Alt+Shift+1.

For more complex automation, Apps Script (under Extensions → Apps Script) lets you write JavaScript to manipulate data, send emails, or create custom functions. For example, a simple script can automatically email a report every morning or move completed tasks to an archive sheet. You don’t need to be a programmer – many useful scripts are available in the Sheets template gallery and online communities.

Collaboration Features That Prevent Chaos

Working with others can be a time-sink if not managed well. Use these features to stay organized:

Named ranges: Instead of typing Sheet1!A1:B10 in formulas, name it “SalesData� and use that. Go to Data → Named ranges.
Comments and action items: Highlight a cell, right-click, and choose “Comment.� Type @ to mention a collaborator – they’ll get an email notification. Assign action items by checking the “Assign to� box.
Version history: File → Version history → See version history lets you see every change and restore an earlier version. Name important versions so you can jump back.
Protected ranges: If you don’t want others editing certain cells, go to Data → Protected sheets and ranges to lock them down.

These features reduce the back-and-forth of emailing files and merging changes, which is often the biggest hidden time-waster in team projects.

Frequently Asked Questions

How do I freeze rows and columns in Google Sheets?

Select the row or column you want to keep visible, then go to View → Freeze and choose how many rows or columns to freeze. You can also drag the gray bar in the top-left corner of the sheet to freeze multiple rows or columns quickly.

What’s the difference between a macro and Apps Script?

A macro is a recorded sequence of actions you can replay – it’s simple and doesn’t require coding. Apps Script is a full programming environment where you write JavaScript to create custom functions, automate workflows, and integrate with other Google services. Macros are actually stored as Apps Script functions, so you can edit them if you need more control.

Why is my VLOOKUP returning #N/A?

VLOOKUP returns #N/A when the lookup value isn’t found in the first column of the range. Check that the value exists, that there are no extra spaces, and that the range starts with the column containing the lookup value. Also, make sure the last argument is FALSE for an exact match, unless you truly want an approximate match.

Can I use Google Sheets offline?

Yes. Go to Settings → General and turn on “Offline� – you need to be connected to the internet the first time you enable it. After that, you can view and edit files in Chrome or the mobile app without a connection. Changes will sync when you’re back online.

How do I count cells with text in Google Sheets?

Use =COUNTA(A1:A100) to count non-empty cells, which includes text and numbers. To count only cells that contain text (not numbers), use =COUNTIF(A1:A100, "*?") – the asterisk matches any sequence of characters, and the question mark ensures at least one character exists.

What’s the best way to compare two columns for matches?

Use =IF(COUNTIF(B:B, A1)>0, "Match", "No match") in a helper column. This checks if each value in column A appears anywhere in column B. For a more visual approach, apply conditional formatting with a custom formula like =COUNTIF(B:B, A1)>0 to highlight matches in green.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →