10 trucos y consejos para sacar el máximo partido a Google Sheets

Are you new to Google Sheets? Sheets is an intuitive and accessible piece of online spreadsheet software. Free to use, you can create and edit spreadsheets directly in your web browser without installing anything. It’s ideal for collaborating in real-time too: multiple people can work in the same file simultaneously, meaning you can see people’s changes as they’re made and everything is saved automatically. Working with Google Sheets can save your company or team a lot of time. To help you maximize the potential of Google Sheets, we’ve pulled together a selection of 10 spreadsheet tips and tricks.

Tip 1. Freeze rows and columns

Let’s start with one of the most useful spreadsheet tips and tricks: freezing rows and columns. How can freezing rows and columns help you? When you’re working with large volumes of data it’s easy to get lost. This is a problem when you can’t see the column name in the header row, for example.

The freeze function solves this problem by pinning the data to the same place so you can always see it on your screen while you navigate the spreadsheet. You no longer need to scroll up to read which column you’re in!

• Select a cell in the column or row you want to freeze.
• Ir a la Ver menú.
• Select and option.
• When you scroll, you’ll notice the frozen row(s) or column(s) are visible.

Tip 2. Use conditional and alternative formatting

Formato condicional

Formato condicional can help you to track the evolution of metrics like KPIs, with colored cells. You can make a cell, row, or column change color if the data meets certain conditions, e.g., if it contains a certain word or a number. This makes your spreadsheet dynamic and the data easier to read. For example, you can format your spreadsheet so that poor performance, low grades, or minus figures are automatically highlighted in red.

1. Open a spreadsheet in Hojas de cálculo de Google.
2. Select the cells you want to apply conditional formatting to.
3. Haga clic en Formato > Formato condicional.
4. In the pop-up menu on the right, create a rule.
   • Single color: Under Formatear celdas si, choose the condition. Under Estilo de formato, choose the color and style of the cell or text.
   • Color scale: Under Preview, select the color scale. Set a minimum and maximum value, and an optional midpoint value.
5. Haga clic en Hecho.

Alternating colors

This is another big timesaver. If you want your rows to have alternating colors in Google Sheets (which can make your spreadsheet easier to read) instead of coloring the cells manually, you can do it in two clicks. Hit the Formato option from the menu at the top and then click Alternating colors. Choose a default template or choose custom colors for the header and alternating row colors.

Tip: If you want to color every third or fourth row, use conditional formatting with a formula. Go to Custom formatting > Format cells if > Custom formula is.

Tip 3. Use add-ons

Add-ons run inside Google Sheets, Docs, and Forms. They’re small programs built by developers that enable you to do more with the spreadsheet or file. You can add menu items and sidebars, edit files, and connect to over a dozen Google services instantly. From inside Google Sheets, click add-ons in the menu bar, and then search. You will find add-ons to connect spreadsheets, import data from multiple sources, schedule mailings and more.

Tip 4. Use a template

Templates are one of the best ways to save time when you and your colleagues are working in G Suite. Recreating a report or newsletter layout every time you start a new one is unproductive. Google offers many time-saving templates that let you get on with using the documents, rather than struggling to put them together.

1. Open the homescreen inside Google Hojas, Diapositivas, o Forms.
2. Choose a template.
3. To see more options, click Galería de plantillas.

You can also access templates when you’re inside a file. Click Archivo >Nuevo > From template.

Tip 5. Learn how to use comments and notes

Google Sheets allows you to insert a Note or a Comment into a cell. Use the Note function to leave a simple note in the cell just like you would with Excel’s comment function. With Note, you can simply add information about the cell. Notes can be helpful but beware of adding unnecessary complexity to the spreadsheet. Keep in mind that users often overlook notes, so they’re usually best if it’s only you who will be using and viewing the spreadsheet.

However, if you have multiple users, especially working in different locations, the Comentario function can be very helpful.  It enables users to have a back and forth conversation about a cell without altering its contents. The Comment function is particularly useful for editors. It helps them to work and improve their spreadsheets with their colleagues.

Tip 6. Use Google Forms with Google Sheets

Google Forms help you plan events, conduct a survey or poll, give students a quiz, or collect other information in an easy, streamlined way. You can create a form from Google Drive or from an existing spreadsheet that can record the responses to your form.

Read our guide on how to connect Google Forms and Google Sheets.

Tip 7. Check the version history

Users with edit access to a file can view the full history of the Google Sheet. This includes changes made by colleagues, via the revision history feature. G Suite’s editing tools (Docs, Sheets, Slides, Drawings) automatically save all changes as you work in real time. You can revert to earlier versions of the file(s) and see which person made specific edits.

• Open a Google Sheet.
• Ir a Archivo > version history.
• You can also click on the All changes saved in Drive text (or Last edit was X minutes ago) in the menu bar.
• Note the Show more detailed revisions in the lower right corner of the revision history panel.
• Select a timestamp to show a previous version and who made the edits.
• Text that has been added will be highlighted in a different color.
• Text that has been deleted will be shown with a strikethrough.
• To revert the file to the version you’re currently viewing, click Restore this version.
• To return to the current version of your file, click the back arrow in the top left.

In the version history also allows you to make copies of files from certain points in time. If you need to regularly preserve different versions of your spreadsheet, check our our guide on how to automatically track changes in Google Sheets to create a historical data log.

Tip 8. Find and replace

Manually finding and replacing parts of the text can be exceedingly tedious for big spreadsheets. With the Find and replace function, you can quickly make revisions. Here’s how to do it:

1. Open a spreadsheet in Google Sheets.
2. Haga clic en Editar > Find and replace.
3. Next to Encuentre, type the word. If you want to replace the word, enter the new word next to Sustituir por.
4. To search for the word, click Encuentre. To see the next time the word is used, click Encuentre again.
5. Optional: Narrow your search by using an option below.
   • Caja de cerillas: Makes your search case-sensitive.
   • Match entire cell contents: Searches for cells that are an exact match.
   • Search using regular expressions: Searches for cells that match a pattern.
   • Buscar también dentro de las fórmulas: Searches include formulas.
6. To replace the highlighted word, click Sustituir. To replace the word every time it’s used, click Sustituir todo.

Tip 9. Create filters

There are several ways to filter data in Google Sheets. With the FILTRO formula, filters and the filter view function.

Sort your data numerically or alphabetically

1. Select the cells you want to sort.
2. Haga clic en datos > Sort range.

Apply a filter to the spreadsheet

This option lets you hide data in the spreadsheet. Note that anyone who has access to the spreadsheet will see this filtered version of the data. Those with edit access can change the filters.

1. Open a spreadsheet in Google Sheets.
2. Select a range of cells.
3. Haga clic en Datos > Crear un filtro.
4. Click the Filter icon at the top of the range to choose from a list of conditions.
5. To turn the filter off, click Data > Turn off filter.

Create filter views in a Google Sheet

This option is better when you have multiple users who need to see different data in the spreadsheet. You can save and name different filter views, allowing different team members to view their own filtered data in one click.

1. Go to Data > Filter views > Create new filter view.
2. Sort and filter the data.
3. Al hacer clic en Close the filter view will be saved.

Learn more about the different types of filters and how to filter Google Sheets without affecting other users. 

Tip 10. Protected sheets and ranges

Google Sheets is great for collaborating but sometimes you might want to lock part of the spreadsheet to prevent others from making edits. You can do this with named and protected ranges. If you want to learn more about how this works, check out Google’s Developer documentation on spreadsheet.get requests.

Note that protecting a range or a tab(sheet) stops others from being able to make changes to that part of the spreadsheet but it does not hide the data from view.

1. Ir a Datos > Protected sheets and ranges.
2. Haga clic en Add a sheet or range.

Get detailed step-by-step instructions here.

To check which areas of the spreadsheet are locked, click View > Protected ranges. A striped background appears over those cells.

To protect and control who can access specific data, try using separate, but connected Google Sheets.

Do more with spreadsheets

Start improving the way you and your team work in spreadsheets with these 10 spreadsheet tips and tricks. Want to do more with Google Sheets? Check out our guide on how to filter one Google Sheet to another.