Five useful Google Sheets tricks

This morning, I have been using Google Sheets to prepare and organise my weight loss plan. Yes, I’m taking it very seriously so feel free to keep me accountable! As I input diet and exercise data, I’m reminded of some very simple tricks. I’d like to share these with you in case you’re not already aware of them. They are very useful and could save you lots of time and frustration. As well as describing the processes, I have added short screen capture videos to show them visually. I hope these are helpful and highlight just how easy these functions are to use.

Drag to autofill

This is amazing! As soon as Google Sheets can recognise a pattern in your cells, you can stop typing. Simply highlight the cells and drag from the bottom-right corner. Google Sheets will automatically continue the pattern to however far you drag.

Hide columns/rows

For very long spreadsheets, it’s often useful to hide some of the data that you don’t currently need. Highlight the columns/rows that you don’t need, right-click and select ‘hide columns’ (or rows). This does not delete the data. It can easily be brought back by clicking on the arrows between column/row headings.

Protect range/sheet

When we share Google Sheets with others for collaboration, we do not need to grant the same permissions for the whole document. If there are sheets, columns, rows or cells that you want to protect, it is possible to change the editing permissions for those selected areas. Alternatively, you can provide a warning message. Go to Data > Protected sheets and ranges. Alternatively, highlight the area that you want to protect, right-click and select ‘Protect range’. After you click ‘Set permissions’, you’ll see the options to provide a warning or restrict editing access.

Sort sheet/range

You can sort the data in your whole sheet or in a selected part of your sheet. This is useful for putting data in numerical or alphabetical order (either ascending or descending). This can be done in the Data menu or by right-clicking highlighted cells. Choose the column that you wish to sort by and then select ‘A – Z’ or ‘Z – A’ depending on which direction you want the data to go. Very handy for organising sheets by students’ names of assessment scores.

Conditional formatting

Conditional formatting allows you to assign certain rules to particular cells, rows or columns. Google Sheets will change the cell or text colour if it meets the conditions. For example, you can make a cell go red automatically if the value is less than 10 (as shown in the video below). Conditional formatting is useful for highlighting assessment scores that are high or low. There is a long list or rules that you could apply to your sheets depending on your needs. Go to Format > Conditional Formatting… or right-click on the sheet and select ‘conditional formatting…’. Both of these procedures will open the sidebar that shows your existing rules. You can make new rules from here.

For more Google Sheets tips and tricks (including much more advanced functions), visit Alice Keeler’s site (she is famously known as the Queen of Spreadsheets). Also check out her books about purposeful Google integration, including Teaching Math with Google Apps. Highly recommended!

To receive blog updates, find the ‘Follow’ icon (below or in the sidebar) or ‘Like’ my Facebook page. Your ongoing support and encouragement are very much appreciated.