If you’re new to Google Sheets, you’re at the right place! Today I will help you to start using the tool just in 15 minutes! To create a new spreadsheet, go to drive.google.com.
You need to have an active Google account. If you don’t have one, click pause and create it now. It takes just a few minutes. We are now in our Google Drive. It contains folders and files.
I will create a new Folder by clicking + New on top and call it Google Sheets for Beginners. Go to folder -> Click + New on top -> Google Sheets -> select whether you want to create a blank sheet or use a template.
For templates, you can either create and upload templates specific for your organization, or use Google templates gallery. I would prefer to create a blank sheet now. One of the coolest tricks to create a google sheet, is to use.
new. I simply type sheets.new in my browser and it creates a new spreadsheet for me! It is automatically saved on my Google Drive. Name the sheet in the top left corner to easily find it next time using search in Google Drive.
To organize it, I click on the folder icon, and here I can either create a new folder to store this file or select an existing one. *How to upload an existing spreadsheet* I usually drag and drop excel or csv files to the folder on Google Drive. To avoid converting each file manually, go to the Settings menu and select Convert uploaded files. Now, any file added to GDrive will be automatically converted without copies. If, for some reason, you have an excel or csv file that was not converted, don’t worry – it is still readable.
However, you can only view it. Click Open with Google Sheets button on top. Google will create a Google Sheet copy in the same folder. We can now start working with the spreadsheet. Lets go back to our blank spreadsheet.
Google Drive automatically saves everything, so you don’t have to worry about this. Just make sure you are online and this message on top appears. There can be a number of sheets in one spreadsheet.
This is super cool to organize your data. I recommend you to keep your raw data, calculations, and dashboards on separate sheets.
Sheets are represented here in the bottom. To rename a sheet, double click on it and type the name you want. Lets call this one Data. To add one more sheet, you click the “plus” here and the new sheet appears. If you have several sheets with raw data and with calculations, color coding can be very helpful.
I will color the Data sheet purple, and the Calculations yellow. If you click All Sheets button here, you will see the list with color marks and can navigate through them. This helps a lot in case you have 10 or 20 sheets in the same file, and they get hidden on the right. Each sheet has cells, columns and rows. Columns are indicated alphabetically, rows are indicated numerically.
Select the whole column or the whole row by clicking on the index.
To select the whole sheet, use this button in the top left corner. Freeze columns and rows using these markers. This is how you can keep the columns and rows headers visible at any time. Cells allow you to both store data and to make calculations based on the data in other cells.
Each cell has an index – this is a combination of the column and the row indexes.
You will be using the row, column and cell indexes in formulas in the future. We can input text, numerical information, dates, currencies. To input data in the sheet, click on the cell. You can simply type the data right away, or use a field here on the top.
You can adjust the size of the field. But don’t forget to select the correct cell before you use this input field.
Entering data here is super useful when working with large functions. Lets create a simple dataset – information about employees, their age, country of residence, and monthly income. Lets freeze the row using this marker.
Spreadsheet automatically recognizes the type of data you enter. However, you can adjust the format manually. Use FORMAT to select the type of data you input. You can convert your numbers into $, or into % using these buttons in the menu. You can decrease or increase decimal places here as well.
Select the cells you want to apply text formatting to, and design your texts by selecting the font, text size, bold, italic, strikethrough, text color etc. I will make the headers bold. You can manage text wrapping in each cell using this button in the menu. You can overflow, wrap or clip text. However, for this specific case, I would prefer to resize the columns.
Color cells to mark the headers, different types of data, or cells containing formulas and function outcomes. Select the cells to color, and use the fill color button. Watch our video about spreadsheets design, where I will show you how to use color coding and conditional formatting. Here are some hotkeys and keyboard functions for you to use when working with spreadsheets. You can use arrows to move around the sheet.
For Mac, you can use standard Command+C and Command+V to copy and paste, Command+X to cut, Command+Z to undo and so on. For PC, use the same combinations, but with a Control. An important note: If you copy several cells, the pasted output will contain the same cells in the same order.
Lets copy names and age. We click on the cell to start from to select the range we want.
Command+C, then click on the cell to paste – Command+V. The values and the order are saved. This might re-write the data, so make sure you paste into empty cells. There are different parts of the data that you can copy and paste. Copy the set -> right click on the cell to paste values to -> paste special -> select what you want to paste.
For example: Values only – the values will be pasted without format. Format only – the format will be applied without values and so on. A really cool feature of Google Sheets is to paste transposed. If you use it, the data will turn around – what was in the columns will now appear in the rows, and vise versa. There is a hotkey to select a set – Command+Shift+Direction Arrow for Mac.
I click on the cell, I want to start from, and press Command+Shift. If I use the right arrow, it will select all of the cells that contain values on the right. If I press it once more, it will select all of the cells in the row. Use the left arrow to select the cells with values only. Now, we can press Command+Shift+Down, and Google Sheets will expand the selection to all of the columns in the set.
You can select 2 individual cells by holding the Command key for Mac, or Control key for PC. If you input text and pull the bottom right corner, it will simply copy this text to the next cell.
However, spreadsheet is smart. If you input a date, or a day of the week, or a month, select it, drag, and the sequence will be expanded. The same works for numbers in case you select two of them and drag the bottom right corner of the cell.
Cells allow you to both store data and to make calculations based on the data in the other cells. Use simple math operators in spreadsheets to make calculations. Lets count the sum of monthly salaries of our employees. Add a header first – Salaries total I click on the cell, type equality sign, click on the cells with salaries and add a plus. You can add numbers manually or refer to the cells with values like I did just now.
Hit Enter and here is the sum of salaries. If you want to get an average salary, add a header – Salaries Average, type equality sign, the sum we’ve counted just now, and divide it by 3. If there is a repeatable calculation and you drag the bottom right corner down, the spreadsheet will extend the calculation using the values in the new rows. Lets count the annual salary for each of our employees.
We create a new column, add a header Annual Salary.
Now, we type equality sign, refer to the cell with the monthly salary by clicking on it, or typing its cell index and multiplying it by 12. Press Enter. You can refer to cells in the same sheet, or in a different sheet. Moreover, you can refer to a totally different spreadsheet from your drive. Subscribe to our channel and watch our video about importing data in Google Sheets.
I can add the number of months to a separate cell and refer to it in the formula.
I add it to the formula and hit Enter. Now, I drag this formula, and it doesn’t work properly. This is because the formula pulls each next cell after the 12 months one. I need the month’s number to stay the same for all of the copied formulas.
For this, I can anchor the values inside the formula using the $ sign. Put an anchor before the letter to lock this one if formula moves to a new column, and add a dollar sign before the number to anchor this value when moving to a different row. In our case, we will copy the formula to several new rows, so I need to lock the number.
Put the dollar sign and press Enter. Now, I drag it again and the formula works.
One of the most useful features of Google Sheets is using different functions. To use a function, type ‘=’ sign and start typing the name of the function. Once you do that, the list of possible functions will pop up, and you can choose one of them. You can find a list of quick functions in the menu here. Lets try them!
Click on the cell, select the function – lets start from the SUM – then select the range to be summed (monthly salaries, for example). Hit Enter and here is the result! Watch our video about advanced SUM, SUMIF and SUMIFS functions to total values based on a specific criterion. You can calculate the average salary using a function or count the items in the selected range.
You can also display the maximum and minimum value.
Follow the same logic: ‘=’ sign, function name, open parentheses, add a range, close parentheses, hit Enter. There is a huge number of functions in Google Sheets. Subscribe to Railsware channel to learn the most useful ones. The last but not the least. Google Sheets is a great tool for teams to collaborate on data.
There are several features that allow multiple users to work together in one spreadsheet at the same time. If you work with your teammates on the same document, but not at the same time, you can leave your comments and notes for them.
Right click on the cell, and insert a comment or a note. The notes pop up if you hover over the cell. Notes are used to add descriptions to the data in the cells.
You can leave explanations and hints for your colleagues in notes. Comments are actionable. You can see who is the author of the comment, reply, ask questions, tag users, and even have a dialog on the issue. You can mark people who should take actions on this comment by typing ‘+’ and the email of the person. They will get notified by email and any other integrated tool (we use Slack, for example).
Comments are often used for tasks tracking. Once the issue is fixed, the author should mark the comment as resolved.