The Best Google Sheets Tips And Shortcuts
When it comes to spreadsheets, a lot of business users are evangelical about Microsoft Excel and it's easy to see why. It comfortably remains the go-to application for any business with more complex data handling needs.
In terms of users Microsoft still flattens its competition but an increasing number of businesses are testing out the G-Suite to take advantage of its real-time collaboration features and unbeatable usability. (For a fuller analysis, see our full review of Google Sheets vs Microsoft Excel.)
For all the G-Suite users out there, we run through all of the most interesting things you can do with the application, as well as the best tips and tricks for getting the most out of Google Sheets.
1. Add a drop-down menu to your cells.
Finding the best way to display your data is often half the battle. Using drop-down menus is a space-saving technique and should make your data much more readable and intuitive.
Here's how to do it:
- In your Sheet, click on the cell (in the sheet) you want to put the drop-down menu in.
- Next, right-click and go to 'data validation' and in 'Criteria:' make sure 'list from a range' is selected.
- You'll then need to add in the cell range you want to filter into the drop-down menu. So if you want cells A2,3 and 4, you can enter either 'A2:A4' or be more specific depending on what sheet you want to pull the data from, so something like 'Sheet2!A1:A4'.
2. Import data from another spreadsheet
Even though most businesses use either Google or Microsoft, you'll still find formatting issues between both firms' spreadsheet offerings. This makes collaborating with business teams using opposing software a little tricky.
To combat this, here's how to import spreadsheets to Google Sheets:
Go to 'File', then 'Import'. Next, select the data you want. Then select the file and click 'Open'.
However, if you want to import data from one Google Sheet to another, you can use the Import Range function.
To do this you should write in a cell (not the Sheet you want to import into) "=IMPORTRANGE (insert Google Sheet URL), range_string". You can simply find the Sheet URL by clicking on the Google Sheet you want to take data from and copying its URL at the top and pasting it in.
And the range string refers to the cells from the other Sheet that you wish to import across. So your formula could look something like this:
=IMPORTRANGE (https://docs.google.com/spreadsheets/d/1hr5t8P-HjfHfkYSOBKtsgGKEaskfDFEOAg/edit#gid=0, Sheet4!A1:C35
3. Create an app with Google Sheets
AppSheets is an add-on available in Google Sheets which lets you put your data into a readable app. This is best for simple apps displaying listicle data or simple table data.
Here's how:
1. Make sure your spreadsheet has a title
2. Insert your data
4. From the menu, choose "add-ons > AppSheet > Launch" and click "GO" in the sidebar
5. The browser opens the AppSheet site and your app is auto-created.
You'll then be able to use the dashboard to design your app.
4. Make your own custom Google Map
With Google Sheets, you'll be able to create a custom Google Map packed full of your own data. This is very useful for tracking the location of customers and also common places of interest relating to your business.
1. Download the 'Mapping Sheets' add on (just click on the 'add-ons' button, go to 'get add-ons' and type it in)
2. Enter address data into the Sheet, the headings name, category and address will work best
3. Select these three categories and click 'add-ons' then 'Mapping Sheets' and finally 'start mapping'.
You will now be able to view and build your map and select the way the information is presented via the title, location and filter buttons on the right hand side.
5. Create surveys and collect the data
Many businesses rely on surveys to gather important information on their customers and markets but survey creation and gathering its data can be a chore. Here's how to do it faster and easier:
Click 'Insert' and then 'Form'.
You'll then be prompted to 'ask questions'. This will open a new window and you can add questions and their format (e.g. multiple choice).
6. Create a data heat map
A data heat map allows users to identify cells and important information depending on its colour. This helps businesses see trends and patterns within its own data. Here's how to do it:
1. Input and select data in Google Sheets
2. Go to format and click 'conditional formatting'
3. This will open a side-panel. If you select 'colour scale' you'll be able to choose a colour for the 'minpoint', 'midpoint' and 'maxpoint'
4. You can add new rules or just click done.
7. Create a chatbot
If you run a customer-facing business you might feel the pull of creating a chatbot. And why wouldn't you? They can provide a simple way of communicating with customers and can manage simple tasks to keep things ticking over.
If you're really invested in building a chatbot, you might want to check out these building platforms, but Google Sheets is a good platform to test the water.
Here's how to do it on Google Sheets:
1. Get the Chatbot add-on from AppSheet
2. Fill in at least five lines of data (or use a sample set for inspiration)
3. Select 'Go' and a new window will open. From there you can use the dashboard to perfect your chatbot
8. Create QR codes
QR codes are ubiquitous these days. They're a great way to share contact details, URLs, product information, and other brief amounts of text.
1. Install the QR Code Generator add-on
2. Input the data required (i.e. name of customer and unique code)
3. Click 'add-ons' and then 'QR Code Generator'. From there you can click 'generate' to create QR codes.
9. Keyboard shortcuts
Google Sheets is packed full of handy little shortcuts, but you have to be in the know first. Here's how to pull up the entire list of shortcuts:
Windows: Control and /
Mac: Command and /
10. Get more currencies
To access more currencies couldn't be simpler, but not many people know how.
Click the '123' button and then 'more formats', you'll see a tab marked 'more currencies' click that and you can choose from a huge list.
11. Email notifications
This tip might not be for everyone, depending on how often a sheet is updated, but here's how to turn on email notifications on Google Sheets.
Go to 'tools' then 'notification rules'. From there you can choose whether to be notified when a change is made or when a user submits a form. You can also limit notifications to a 'daily digest' so not to be bombarded with emails.
12. Use templates
Like all Google Apps, a variety of templates can be used, from annual budget documents and travel planners to invoices and expense reports. Here's how to do it:
Go to 'file', then 'new' and select 'from template', from there you can browse all available templates.
13. Make the most out of Twitter
With social media playing an obvious part in most businesses, the Twitter Curator add-on can save, search, filter and curate Tweets.
Searches can centre on hashtags, @mentions and general search terms.
To enable this add-on, download it from the add-on centre, login to Twitter and allow the add-on to connect.
14. Insert images into your sheet
Sickened by the sight of rows upon rows of numbers? Why not spruce up your spreadsheet with a picture or two. Add style and flair to any sheet project by clicking on the =IMAGE and pasting the URL of your chosen image within the brackets.
15. Spell check in Google Sheets
Not automatically correcting or highlighting spelling mistakes could leave fast typers a little red-faced. Now Google Sheets can spell-check entire sheets or individual cells for you.
Highlight the chosen area (or don't for the whole sheet) and go to 'tools', from there click 'spelling' and it will open a panel with a familiar spell check interface.
16. Lock up your data
0 comments