How To Excel in Google Sheets! – Become a Pro at Spreadsheets

You are here: Home \ Uncategorized \ How To Excel in Google Sheets! – Become a Pro at Spreadsheets
27 July 2020 - 17:41, by , in Uncategorized, No comments

Formatting

Formatting your Google Sheets spreadsheet in a clear and logical way is one of the most simplistic – yet most effective – ways to get your data looking good! Once you’ve got some good knowledge of how to format your sheet well, you can begin to think about some of the other tricks available.

Use line breaks or wrap text

When typing into a spreadsheet cell, a piece of text too long to fit inside the cell will continue on one line, and disappear behind the neighbouring cell. In order to prevent this, you may want to use line breaks whilst you are typing. And no… you can’t just press ‘enter’ like you would in a normal text document. Instead, you must hold CTRL and ENTER at the same time. 

Alternatively, you may want to apply the ‘wrap text’ feature to an existing cell in order for Google Sheets to automatically create these line breaks. Follow the steps below to see how to do this:

how to: line wrapping

Use colours and fonts

This may seem awfully obvious, but using colours and fonts effectively can really elevate a spreadsheet. This isn’t all about making the spreadsheet look pretty – it’s more so about making the information and data in your sheet much easier to read and interpret. Simply put, this will effectively make your work better, without too much extra effort.

Use conditional formatting   

Similarly to using colours to help make your sheet easier to read and interpret, conditional formatting can be immensely helpful. This tool will essentially change the colours of your cells automatically, depending on the conditions you apply. For example, if your spreadsheet is filled with numerical data, you could condition Google Sheets to make the largest numbers red, the lowest numbers green, and everything in-between. Follow the steps below to find out how you can use conditional formatting:

how to: conditional formatting

Importing Data

Importing data from elsewhere into your Google Sheets can be super helpful. This can save huge amounts of time that would have been spent manually inputting each piece of data. 

Use the Google Sheets import functions   

Google Sheets has an inbuilt set of tools which allow you to import data in various different file formats. Check out the steps below to find out how:

how to: import data

Use an external tool

Alternatively, you may want to use an external tool to import your data. This can allow you to import data from another app into Google Sheets, and have this data automatically update daily. A great example of one of these external tools is Supermetrics. This add-on can import data from various third party apps, including: Facebook ads, Facebook insights, Google Adwords, Google Analytics, Twitter, Instagram, YouTube, LinkedIn, Mailchimp, even your own database, and a whole lot more. All you need to do is:

  • Install the ad-on
  • Choose the data source
  • Login and allow permissions
  • Start running queries in Google Sheets. 

Using Formulas 

One of the greatest things about creating spreadsheets is being able to implement formulas. When working with data in the form of numbers, there are countless formulas you can use to manipulate your spreadsheet. However, in this article we will just be covering a few of the best ones that you should get to know: 

google sheets formulas

Other Handy Tools 

We could go on forever listing the tools and tricks available in Google Sheets. But, we like to keep it short and sweet here! Let’s cover some of the most fundamental tools that you should be using to help elevate your spreadsheet-making game!

Freezing  

You can ‘freeze’ rows or columns in Google Sheets, which means that no matter how far down or across you scroll, those sets of data will still be visible on your screen. Freezing certain rows or columns can be super helpful for analysing or reading spreadsheets, or inputting data. Most commonly, users will want to freeze column A and row 1, as these will usually contain headings that will need to be visible at all times. 

Follow these steps:

  • Select the column/ row that you would like to freeze
  • Go to ‘view’ in the toolbar
  • Select ‘freeze’

Autofill

The ‘autofill’ option is excellent if you are inputting a series of numbers or dates into your spreadsheet, or if you want to duplicate the same data across a number of cells. It saves you having to manually input this data. 

Follow these steps:

  • Enter the first pieces of data in at least 2 cells next to each other
  • Highlight the cells. You should now see a small blue box in the bottom right corner of the cell. 
  • Drag this box across however many cells you would like to autofill. 
  • If your first pieces of data were a series of numbers or dates, this will automatically continue the series. If not, it will simply duplicate the cell. 

Drop down lists  

You can create dropdown lists which will appear inside a cell. This means the editor of the spreadsheet can choose from this list which piece of data they would like to fill the cell with. This tool can be great for collaborative projects.

Follow these steps:

  • Select the cell
  • Click on ‘data’ in the toolbar, and then ‘data validation’
  • Next to ‘criteria’ choose one of the options, and then input your desired data

Copy data between sheets

This tool can be great if you need to duplicate pieces of data between multiple sheets within the same spreadsheet, without having to manually input. 

Follow these steps:

  • Select a cell
  • Type in ‘=’ followed by the sheet name, then ‘!’, then the cell being copied. For example: =sheet1!A1

Don’t forget to bookmark this article in your browser to ensure you can refer back to it whenever you need a little help. Check out our other articles for more tips, tricks, and advice.

About author: