Google Sheets – Understanding the basics of spreadsheets
Commission may be earned if you purchase through links on this site - learn more
Google Sheets is a spreadsheet program that you access from the internet. All of the work you do with Google Sheets is done online. This has many advantages because it means you don’t need to worry about making sure you have the latest version.
It also means you can share certain worksheets with your colleagues, so you can all work on one spreadsheet at the same time. When Google Sheets originally launched it was the only spreadsheet program, which truly offered online collaboration. Since then, Microsoft has also launched an online version of Excel, which is the most well known and used spreadsheet program in the world.
The great thing about Google Sheets is that it’s free to use, which is great when you consider Excel can cost a lot of money. Especially if you have to buy many licenses to cover your users.
This guide explains the absolute basics of using Google Sheets. It’s worth pointing out spreadsheets, in general, whether you’re using Google Sheets or Excel work on the same principle. So if you already have an understanding of spreadsheets this guide may already be familiar. However, there are some differences with Google Sheets, so it’s still worth a read.
What is a spreadsheet?
A spreadsheet is a powerful application and is used in every business you can think of around the world. Whether that business has one employee or is a multinational employing thousands they’ll be using a spreadsheet.
A spreadsheets primary goal is to calculate numbers and manipulate data. However, the spreadsheet has become so commonplace in the working environment that it’s usually used for many other tasks. For example, a lot of businesses use a spreadsheet to store data. This is fine, but it’s not perfect because to store data you should really be using a database.
A spreadsheet isn’t just limited to the business sector as it can be used for personal use as well. For example, you may want to create a spreadsheet to help with your household budget. A spreadsheet is perfect for this type of task. It’s not all about big business in registered offices either. Anyone who works from home; such as an eBay seller will definitely use a spreadsheet to calculate costs and track sales.
Understanding the basics of spreadsheets
If you’re wanting to master spreadsheets it’s important to understand the basics. There is no point trying to use a spreadsheet without understanding the basic principles of how they work. We’ll now take a look at what you need to understand about spreadsheets and Google Sheets in particular before you should even start entering data.
- Columns and Rows
When using Google Sheets you’ll always be working with cells. The cells are what make up a spreadsheet and each cell contains its own individual data.
The image above is typically what a spreadsheet looks like when you first open it. You’ll notice at the top there are columns A to E and rows 1 to 10. In the first row, there are five cells and in the first column, there are ten cells. You can quickly work out that there are 50 cells in the image above.
A spreadsheet can contain millions of cells and each cell can contain data. This is why it’s important to recognise how cells are located in a spreadsheet. Cells in a spreadsheet are located very similar to how a map is used to locate a certain point. You use the columns at the top and the rows to the left to locate any particular cell.
You can see this from the image below. The cell that is currently selected is cell C5. You can also see column C and row 5 has changed colour, which makes it easy to see what cell is currently selected.
When working with Google Sheets understanding cell references is important. The cell reference in the above image is C5. When you begin doing calculations and formulas you’ll use cell references to let Google Sheets know what cells you want to include in your calculations.
A range of cells
A range of cells simply means more than one cell. You will regularly use a range of cells in your calculations as it’s an easier way of getting Google Sheets to bring back the desired value.
For example, you may have cells containing numbers and you want to add all the numbers together in those cells. To do this you would use a formula to get Google Sheets to bring back the value of all of the numbers. You could use a formula that adds up all the cells individually, but it’s more efficient to use a formula that provides the range of cells you want to calculate.
Take a look at the image below. There are numbers in column A in rows one to four. If you wanted to add these cells together individually you would need to do the following formula.
In the above image, you can see we’ve created a formula in cell A5 and this formula brings back the value 114. This is the all the numbers in cells A1 to A4 added together. You can see the formula used at the top of the image. This formula does not contain a range of cells. It simply asks Google Sheets to add cells A1 to A4 together.
This may appear fine because it does bring back the correct result. However, imagine if you had numbers you wanted to add together and these numbers were in cells A1 to A100. Your formula would be much longer than the one used above. In fact, the formula you would use is the following.
Formula used if not defining a range of cells
You can immediately see how this would be a tedious formula to carry out. Just imagine if your spreadsheet contained lots of formulas similar to this. It would take you so long to do the formulas. This is why it’s useful to understand how a range of cells can be used instead.
You could get the same result by using a range of cells in your formula. This is a much quicker and easier way of getting the same result.
Formula used if including a range of cells
As you can see it’s much quicker to use a range of cells in any formulas that you ask Google Sheets to perform on your data. The more you use Google Sheets the more comfortable you’ll become with working with a range of cells. In the formula above the ‘:’ could be seen as ‘to’. This makes it much easier to understand what the formula is doing. You’re asking Google Sheets to add all the numbers in cells A1 to A20.
The data you can enter in cells
You can enter different types of data in cells when using Google Sheets. The most obvious being numbers, but you can also enter formulas and alpha data in cells. By alpha data, I mean data that only use characters from the alphabet. Cells can also contain no data. Cells containing no data will have no impact on any calculations you ask Google Sheets to perform.
You can create a formula in Google Sheets to add numbers together and any blank cells or cells containing text will be ignored. It’s unlikely you would ever come across this situation because if a spreadsheet is designed correctly you would very rarely come across a situation like this. However, it’s good to know that they have no impact.
Columns and Rows in Google Sheets
We’ve already looked at how cells make up a spreadsheet. These cells have their own unique reference. The cell reference is located by looking at the row and column for that particular cell. It will benefit you to understand how rows and columns work in Google Sheets, so let’s take a look at what you need to know.
How to add further rows and columns in Google Sheets
When you first open a Google Sheets spreadsheet by default the sheet has 1,000 rows. You can easily add more rows than this by scrolling down to the bottom of the spreadsheet. Here you’ll see an option to add a further 1,000 rows. You can continue to do this as you need more rows.
It’s the same with the columns in Google Sheets. By default, you get 26 columns, which are A to Z. Again, you can add further columns as and when you need them. It’s slightly different to how you add rows, as there is no button to add a further 26 for example. To add further rows you would insert more columns, which will be looked at later.
How to insert columns and rows in Google Sheets
There will be times when you need to insert columns and rows when using a spreadsheet. At first, you may be wary of doing this, certainly if your spreadsheet already contains a lot of data. The great thing about spreadsheets is that you can insert or delete rows and columns and providing you do it right it will not have an impact on your data or formulas.
We’ll use an example to show how this works
A fruit shop keeps a record of its fruit sales throughout the week. The shop does not open on a Wednesday or the weekend. Because of this, when the owner created the spreadsheet they did not include Wednesday or Saturday or Sunday. You can see the spreadsheet they created below.
You’ll see from the image above that the owner only added the days they were open. You’ll also see they added a formula in column F and this formula totals the quantity of each food type sold during the week.
The owner has done some research and reckons they’ll make a lot more money if they open on Wednesday. To do this the owner needs to add a new column between columns C and D. This is so they can add Wednesday to the spreadsheet, so they can record the number of food products sold on that day.
Formulas change automatically when rows and columns are inserted or deleted
The great thing about spreadsheets is when you delete or insert rows or columns any formulas automatically change. If they did not do this you would need to change every formula on your spreadsheet to reflect the changes.
To add a column, so the fruit shop owner can add Wednesday into their spreadsheet is easily done. You would do this by ‘right clicking’ on the column letter D at the top of the spreadsheet. This would then display a menu with a number of options. The option to choose would be ‘Insert 1 left’. This would then insert an empty row between columns C and D.
One this is done Wednesday can be added to the spreadsheet, so the number of products sold on that day can be recorded. You’ll notice from the image below that call the data from column D onwards have moved to the right by one cell. The formula that was in column F is now in column G.
You’ll also notice the formula has also automatically changed to take into account the newly inserted column. Where the formula was previously =sum(B3:E3). It’s now showing the formula =sum(B3:F3). This means the fruit shop owner can add the number of goods now sold on a Wednesday without having to worry about changing the formula.
So when you come to insert or delete columns or rows in Google Sheets you don’t need to worry about it impacting any formulas. This is just one of the great benefits of storing data in a spreadsheet. You can easily add or delete information without having to adjust the formulas you’ve previously written.
How to delete Columns or Rows in Google Sheets
So we’ve looked at how you can insert a row or column in Google Sheets. Now let’s take a look at how you can delete a row or column. Again, when you delete a row or column any formulas will change automatically.
This time we’ll look at deleting a row. Whether you are inserting or deleting a row or column it works on the same principle. Using the image above, if you wanted to delete row 5, which is the row that contains bananas, you would right click on the row number ‘5’. This would again bring up a new menu with a number of options as shown below.
You can see from above after clicking on the row number 5 there is an option to delete the row. You simply click on this and it will delete this row. It will then automatically move up all the rows below this, so what was previously row 6 would become row 5.
You would then be left with a spreadsheet that looks like the one below
How to resize columns or rows in Google Sheets
You may want to resize columns or rows in Google Sheets. This is easy to do, but there are different ways of getting the desired result. Let’s take a look at the different ways you can resize rows or columns in Google Sheets.
Resizing a row or column manually
If you want to resize only one column or row you can do this manually. To resize a row or column you need to move your mouse cursor to the end of the row or column you want to resize.
So if you wanted to resize column D you would move the mouse cursor to the line between column D and E as shown below. You would then continue to hold down the mouse key and drag left to right to resize.
Resizing more than one column or row manually
You can also select a range of columns or rows to manually resize. To do this you would highlight all the columns or rows you want to resize. This will then resize all of those columns or rows to the size that you set. You can see this below.
Resizing rows or columns automatically
You can automatically resize rows or columns depending on the data that is inside the cell. For example, if you wanted to resize column C automatically. Google Sheets would check, which cell in column C is the longest. When you automatically resize the column or row Google Sheets will resize it to fit the cell containing the longest data string.
The video below shows when you automatically resize column D it will resize it to fit the cell in column D with the longest piece of data. In this instance, it is the data in cell D2, which is ‘Wednesday’. When you double click between row D and E it resizes that column to fit in the text ‘Wednesday’.
Worksheets in Google Sheets
Like most spreadsheets Google Sheets uses worksheets. A worksheet is a blank spreadsheet where you can add new data. When you save your file any worksheets you’ve worked on will be saved within that file. In Excel, they call it a workbook. Essentially the workbook is the same as the saved file.
You can have more than one worksheet in a Google Sheet file. You can insert new worksheets, delete worksheets, perform calculations between worksheets, rearrange worksheets, rename worksheets and colour code them to make them easier to work with.
It’s a good idea to use worksheets to break down data in a Google Sheet file. For example, you could have a worksheet that contains all the data about a supplier and in another worksheet, you could have the items you’ve ordered from that supplier.
That’s the Google Sheets basics covered
There is so much to learn when using Google Sheets or any other spreadsheet program. However, these absolute basics will help you along your way and should make you feel more confident using a spreadsheet to perform calculations.
Learning to use Google Sheets is a great thing to do because it opens up so many doors. Whether you are learning for use at home or wanting to learn to further your career. Mastering spreadsheets will help you immensely if you work in an office based environment and will also help you deal with your finances at home.
The great thing is all spreadsheet programs work roughly the same. So if you master how to use Google Sheets you’ll find it easy transferring these skills to other spreadsheet programs.
Google Sheets is also the ideal spreadsheet program for anyone who owns a Chromebook. The Chromebook has been designed to work with other Google Products seamlessly, so why pay to use Excel when Google Sheets is provided free of charge.