Welcome to the Lesson 1 – Navigate Around Google Sheets
INTRO
Some of the main features of Google Sheets:
1. Editing
One of the key elements of Google Sheets is that it allows collaborative editing of spreadsheets in real-time. Rather than emailing one document to multiple people, multiple users can simultaneously open and edit a single document. Users can see every change made by other collaborators, and all changes are automatically saved to Google servers.
Google Sheets also includes a sidebar chat feature that allows collaborators to discuss edits in real-time and make recommendations on specific changes. Any changes that the collaborators make are tracked using the Revision History feature. An editor can review past modifications and revert any unwanted changes.
2.Explore
The Explore feature in Google Sheets was first introduced in September 2016, and it uses machine learning to bring additional functionalities. This feature provides much information based on the spreadsheet’s data. It can auto-update itself depending on the selected data.
With the Explore feature, users can ask questions, build charts, visualize data, create pivot tables, and format the spreadsheet with different colors. For example, suppose you are preparing a monthly budget, and you’ve added all the expenses to the spreadsheet. In that case, you can use the Explore feature to get the cost of specific expenses such as food, travel, clothing, etcetera.
There is a box where you can type the question on the sidebar and return the answer. When you scroll down further in the Explore panel, there is a list of suggested graphs that are representative of the data entered in the spreadsheet. You can choose between a pivot table, pie chart, or bar chart.
3.Offline editing
Google Sheets supports offline editing, and users can edit the spreadsheet offline either on desktop or mobile apps. On the desktop, users need to use the Chrome browser and install the “Google Docs Offline” Chrome extension to enable offline editing for Google Sheets and other Google applications. When using mobile, users need to use the Google Sheets mobile app for Android and iOS, which support offline editing.
4.Compatibility with other programs:
Google Sheets supports multiple spreadsheet file formats and file types. Users can open, edit, save, or export spreadsheets and document files into Google Sheets. Some of the formats that can be viewed and converted to Google Sheets include:
- .xlsx
- .xls
- .xlsm
- .xlt
- .xltx
- .xltxm
- .ods
- .csv
- .tsv
5.Integration with other Google products
Google Sheets can be integrated with other Google products such as Google Docs, Google Slides, Google Forms, Google Finance, Google Translate. For example, suppose you want to create a poll or questionnaire. In that case, you can input the questions in Google Forms and then import the Google Forms results into Google Sheets.
Now, let’s create a new Google Sheets file.
For this, open a Google Chrome browser and then, in the address bar, type: sheets.new.
ENVIRONMENT
You will have the file name on the upper side of the file, a star that you can toggle on and off, a folder icon, and the confirmation that your file is well saved in the cloud.
Just below this, you will have the main menu bar grouping over 100 sub-menus from file manipulation, view options to tools, add-ons, and help. In case you do not see this line and you need to access it, you can click on the down arrow here or use the keyboard shortcut: CTRL+SHIFT+F. Let’s make our menu bar disappear and reaper using the arrow.
Tip: why would you like to hide this menu bar? Well, it can free space on your screen so you can see more rows, and at the same time, you have a smart search area where you are getting fast access to the action you need. Another shortcut that you can take advantage of is ALT+/. This will bring the smart menu search feature in Google Sheets and in Google Docs and Slides.
Below the menu bar, we have the main buttons used in formatting the data or the cells.
Above the table, we have one more line reading the cell name and the value/function of that cell. For example, we are now on the top left corner of the table, on Column A, and the first row. This means that we are in Cell A1. Let’s enter a value inside and see where this number will appear. And yes, it also appears on the line above our table, reading the cell value in our case. And let’s move to the next cell to the right, the cell B1 (column B, row 1), and now let’s enter the value on top and see what’s happening. Of course, the cell got populated with the value we enter.
Back to our environment and what we can see here: the page’s central part is the table itself. This is defined by the column names and row numbers and goes almost to be bottom of the file.
The last line visible on our page shows us the sheets presented on this file and two buttons on the left. One button to create new sheets (the plus button). Another one to easily navigate between the sheets (the hamburger button). You might debate the hamburger button’s added value next to the + sign but imagine that we have many, many sheets in this file. Once you reach a certain number, these sheets are hiding, and you need to scroll Left and Right to find them. In that case, the hamburger button starts to become useful as you can easily find the required sheet to activate. Once you move to another sheet, you will see that the sheet’s name will be highlighted at the bottom of the file.
You can ask yourself why do you need more sheets. Every file contains at least one worksheet by default. When working with a large amount of data, you can create multiple sheets to help organize your file and make it easier to find content. And creating a new Sheet is as easy as pressing this PLUS button.
And now, let’s move to the right side of the screen and discuss two essential areas making Google Sheets a potent tool: Collaboration area
On the upper side of the table, just near your account picture, you will see three buttons:
The first one will be visible if you have a paid Google Workspace account and show you details of the file’s activity. Suppose you have the basic, FREE google account. In that case, you will only see the Comments and Notifications button and to the right, the Share button. We will go more into details in future lessons while talking about collaboratively using Google Sheets.
Moving now to the last part of our Google Sheets environment overview, you have the Explore feature on the bottom right corner. This feature provides much information based on the spreadsheet’s data. It can auto-update itself depending on the selected data.
Enough about the Google Sheets environment. Let’s move to some practical exercises.
DATA ENTRY
We want to create a new table where we are gathering some sales information. Suppose you do not yet have any specific data to work with. In that case, you can always search on the internet to dummy datasets to have a wide variety of numbers. In our case, we are entering a list of products, and then we add the sales per country of each product.
Let’s enter a few numbers together, and after that, magically populate the entire file to save time.
Starting with the table header, I will enter the Product Description in cell A1, and then I will list the first two products: 36 PENCILS TUBE POSY and 12 PENCILS SMALL TUBE SKULL.
In the second column, I will first write the country name Australia and then enter the sales values of 50 and 65. Let’s add one more country: Austria in cell C1, and below add the sales 48 and 24. Now moving fast forward or, in reality, using the Copy / Paste function, I am populating the rest of the file.
At the first look, my file looks very ugly. All the columns have the same width, but there are descriptions I can’t read, and on other cells, there is a lot of wasted space. Generally, before moving forward, I would like to get the information more visible. Therefore I will ask Google Sheets to automatically change the cells widths to make all the text visible. For this, I am selecting the entire sheet by clicking on the table corner, on top of row 1 and left of column A. With the entire sheet selected, I am moving my mouse on the edge of any column (let’s say between Column A and B). Once the mouse pointer transforms into a double arrow, I DOUBLE CLICK the left mouse button. It is slightly better now. I see a significant variation of cell widths for the county data, but for now, I am happy with the way I can see the data.
Now that we know how to enter data let’s make some changes. For this, it suffices to select the cell you want to update and type the new data. Suppose you only want to change a part of that data. In that case, we have two options. We double-click on the cell and change the data there, for instance, deleting the * at the end of the product description in cell A5 followed by pressing the ENTER key. The other option is to select the cell you want to adjust and add or subtract the information you need on top of the table. In this case, let’s clean cell A9 by selecting the cell, and now, on the cell content line, select the two characters and delete them.
And now, the easiest part: to delete data on your table, just press Delete or Backspace, and the data is gone.
Tip: If you delete data by accident, you can always undo that operation by clicking the back arrow or keyboard shortcut Ctrl+Z.
CELL MANIPULATION
Let’s move now to the last part of this first lesson: cell manipulation. And I am not talking about psychological manipulation of the cell but about how to select, copy, move, clear, and delete cells with a bonus on how to auto-fill cells.
Selecting a cell is as easy as clicking on the cell. Once you have done this, you can copy the cell data by clicking Control+C and then paste the data into another cell you want by using the shortcut Control+V. This will keep the data on the source cell and add the data to the target cell.
If you want to move the data from one cell to another, you have two options:
- You CUT the data in the source sell with Control+X and then Paste the data in the Target Cell with Control+V
or
- You select the cell you want to move and then hover your mouse to one of the cell’s edge. The mouse pointer will transform into a hand. Click and hold the mouse’s left button, select the new place where you want to move the data, and release the mouse button. This will move our cell data to the target cell you pointed.
As described before, if you want to delete a cell, select it and hit Delete or Backspace.
So now that we know how to select copy, move and delete a cell, let’s see how to select multiple cells at once.
For this, let’s see how you can select multiple cells. The first exercise will be to choose the first five products and then copy them at the end of the table.
I am selecting the first product of the list, and while holding the left mouse button, I am selecting the rest of the cells. Once I am happy with the selection, I will release the mouse button, and I have now the selection ready. I can now hit Control+C, move my mouse above the cell C30 and click Control+V to copy its values. If I intended to move the selection, instead of Control+C, I would use … … … indeed … Control+X.
Another way of selecting the first five products would be to click on the cell A2, hold the SHIFT key and then click on the cell A6.
If, on the other hand, I would like to select only the first and the last product of the table, I will click on the cell A2, hold the CONTROL key down and click on the cell A26. Note that in this case, when you copy the items in another location, the two values will be copied one after the other. Let’s see it live by clicking now CONTROL+C, move the mouse below the table. When clicking Control+V, you see how the two values are copied one after the other, without keeping the space between the rows you had initially.
To close our cell manipulation exercise, let’s clean the table a bit. I will select all the rows below the table by clicking on row number 27 and holding down the left mouse button while moving the mouse to the end of the page. Then, after my selection is made, I am clicking Delete Backspace to clear the data.
For the sake of this exercise and in preparation for our next lesson, let’s keep the sales data of the first three countries and delete the rest of them. I am selecting the cell D by clicking on the D letter on top of the table, and then while holding my left mouse button pressed, I move to the right of the file and hit DELETE.
To instantly move to the first cell of the table, I am pressing the HOME button, and .. here we are, with a lovely, clean small table.
AUTO-FILL CELLS
I told you during the lesson that I will show you how to auto-fill cells.
For this, we will enter a new column at the left of the table, just before the Product Description. Here we will add a number for each table line. We select Column A, Right Click on the mouse and then choose Insert 1 left. The entire table moved to the right, and now we can enter the Column Name—line Number. Now enter the first two numbers of our list: 1 and 2. If we select these two cells and move our mouse on the lower right corner of the selection, you will see that our pointer is changing into a cross on top of the small blue dot. Click and hold down your left mouse button and now drag your selection until the cell A26, the last line of our table. Once you let go of your mouse button, you will see that all these cells are populated with numbers from 1 to 25. Depending on the incremental interval you select for the first 2 data points, the auto-fill option will generate the rest of the selection. This is useful when you need to add data following a pattern, and it applies not only to numbers but also for days, months, and even more complex configurations.
This ends our first lesson on navigating around Google Sheets and how to enter, change, copy, move, and delete data in the documents.
