While there are all kinds of spreadsheet programs for professionals, sometimes you just need a place where you can fumble around with some formulas while you attempt to make a monthly budget.
Making a couple of graphs, charts and tables doesn’t have to be a painstaking experience, especially if you’re using a program like Google Sheets. With a few add-ons and some slight modifications to your settings, you can make Google Sheets work for you, whether that’s creating an invoice template or some charts to put in your next work email.
Shortcuts
Like Google Docs (and other Google Drive programs), you can swap out the shortcuts already existing in Google Sheets to ones that you already use. You can also add hyperlinks within your sheet if you want to make things slightly easier to navigate around – right click on the cell, press insert link, and you can hyperlink to cells in the spreadsheet, or even other sheets within that Google Sheet. It’s handy for complicated sheets, or if you want to create a version of a table of contents (which Google Sheets doesn’t have the option to do).
Google Forms
One of the often overlooked functions of Google Sheets is Google Forms – you can create a survey / questionnaire that’s intuitive and easy to use. Go to Insert, then Form – this opens out a new, blank form that can be customised for whatever you need it for. You can change the kind of answer format, the number of questions and who can answer it. When you’re done, you can send it to anyone with the email button in the top right hand. When you want to stop people from answering it, you can take the link offline and collate the data in Google Sheets.
Images
You can add images into Google Sheets relatively easily, even if you don’t have them to hand. Go to a blank cell, and type in =image(“URL”), with the URL of the image you want to insert into the cell. You can scale it to fit the cell by adding a one, after the “URL” but before the closing bracket. You can also insert 'drawings', by going to Insert and pressing 'Drawing' – you can add arrows, text or speech bubbles to your spreadsheet without compromising the formatting.
Templates
There’s nothing as intimidating as a blank page, and that applies to spreadsheets too. Use the library of sheets templates to make it a little less intimidating, especially if you have some idea of what you need. When you open a new Google Sheet, from your Drive, click on the little arrow to the right of the Create new Google Sheet option, and press Templates. You can modify these to work better for you – for example, changing the colors or the fonts, and save them as copies into your own folder by pressing Make a Copy. You can also do this with your own sheets.
Insert Sheets into Docs
One of the benefits of using Google Sheets as opposed to a regular spreadsheet program is that you can insert Sheets directly into a Google Doc file, or a Google Presentation.This also means that if the data changes in the sheet, it will show an updated version in the Google Doc. You can do this by going to the document or presentation you want to modify, and selecting Insert. Then go to Chart, and then From Sheet. You can insert a table easily by copying and pasting it directly from the Sheet. If you want to, you can press Edit, then Paste and choose whether to link back to the original Sheet or not.
Conditional formatting
Using conditional formatting can help make large amounts of data much easier to understand. If the value in the cell of a spreadsheet meets certain conditions, then you can format it so that the background of the cell turns a different color. Go to Format, then press Conditional Formatting, and you can modify the values and colors involved. You don’t even have to write out the formula yourself – you can use the inbuilt drop-down menu to pick the conditions for formatting. This formatting is particularly useful for large Google Sheets, budgets or something collaborative. You can also get rid of some of the work yourself by using the Alternating Colours option, which is the option just below.
Lock cells
If you’re working on something sensitive, or find yourself prone to getting two cells mixed up, it’s possible to lock a cell so no one can edit it, unless you unlock it. To do this, highlight the cells you want to lock, and then go to Data in the toolbar. Click on Protected ranges and sheets, and select the one you want to protect. This just means that while other people will be able to see it, they won’t be able to modify it in any way.
You can also pin cells, which is useful for navigating large spreadsheets. This means the heading stays in place even if you scroll down a while. Go to View, then Freeze and then select the rows or columns you want to pin. That means they stay in place while you work through the sheet
Use the add sheets function liberally
If all your data is getting clogged up, or you’re just trying to figure out a formula, use the plus sign at the bottom to add extra sheets in one document. You can even link between different sheets using the hyperlink function, which makes it easier to jump from one sheet to another. If you’ve got formulas that you want to preserve – or just prefer some white space to make sure you’ve got the right information – create another sheet, put your formulas in and set it as a protected sheet so that no one changes anything about it.
Add filters
Sometimes you want everything organised by a category, but it’s too much to do it manually. You can create your own filter by right clicking on the cell of a sheet, and then pressing filter – a drop down menu with a list of options will pop up. You can also create your own filter – repeat as before, and then choose custom formulas.
A new tool called Slicer can also help you filter data by one value – click on the chart that you want to filter, and go to Data, then Slicer. On the right hand side of the widget that pops up, you can change the value that the data is filtered by. If you want to save that filter, click on the values on the right and press More, then Set Current Filters as Default.
Keep track of the changes
As Google Sheets is particularly useful for collaboration, you can also insert a checklist for other people looking at the document. You can press Insert, then Checkbox, which people can tick off as they go down the sheet. You can also insert comments or notes – comments are particularly useful in Sheets, as you can tag individuals with their emails (using + or @ and their email after).
Sometimes a Google Sheet can get complicated – there’s a lot of people working on it at once, and it’s hard to tell where a change happened, or who was responsible for it. If you want to make sure you’re aware of every little change, go to Tools, then Notifications. You can change your settings so you get an email every time something changes. If you want to view the version history, you can click on the grey line of text next to help, which usually says, All changes saved in Drive.
Validation
You can even use Google Sheets to check if what you’ve entered is actually valid. So, if you’re creating an inventory – say of email addresses, then you can use the formula =ArrayFormula(ISEMAIL(X1:X8)) to double check that the email addresses have a valid format. Swap out the X1 and X8 for the desired range of cells. It can’t ensure that someone is still using those emails, but it stops you from missing any obvious mistakes. You can also check that a URL is valid by swapping EMAIL out for URL.
Creative inspiration
Sometimes it can be difficult to look at a chart full of numbers and picture the way that it’s best represented. In order to do so, the Explore tab presents you with a number of options, ones which might best suit the data that you do have. You can also make your spreadsheets look somewhat more appealing with other creative functions – like the alternating colors function, which creates a pattern to put onto your spreadsheets. You can modify the colors used so that they’re easier on the eyes.
Automate some of your work
Macros are a turbocharged version of shortcuts – you can record your own, basically making it possible to perform a long series of commands with one shortcut. Go to Tools, then press Macros, and select Record Macros. Use “relative references” to make it a command that can be done on any cell, and then carry out the formula that you want – for example, if the value rises above a certain number, the number is multiplied. You press save and you can add a shortcut for it, which saves you the trouble of having to do the whole set of steps again every single time. For more information, go to the Macros page on Google Sheets.
Looking to make your next big career move? Check out WIRED Hired for the latest tech jobs
This article was originally published by WIRED UK
