Google Sheets is a powerful online spreadsheet application.
Whether you need to make a budget, outline your next proposal, gather data for a research project, or manage your team’s task list, working hours, Google Sheets is a one-stop solution for it.
Though simple to use, Google Sheets is jam-packed with comprehensive tools and facilities.
For anyone who is just a beginner, extensive features and add-ons can be overwhelming.
Therefore, we broke it down for you in simple steps.
Before we begin,
Let us first understand,
It is a free online spreadsheet application that is provided by Google within its comprehensive Google Drive service. It lets you create and format spreadsheets conveniently, with different users.
Also, you can add collaborators to projects, see changes as they occur, receive notifications
Google Sheets makes it extremely seamless for teams to work together. You can track every change and format in real-time with revision history, for edits that happen while you’re away, and chat with colleagues in the same document. All changes are automatically saved as you make them.
The best part? With offline access, you can create, view, and edit files wherever and whenever.
Now, let us see
Google Sheets supports a wide range of file types, including XLS, XLSX, XLSM, TXT, ODS, and CSV, making it easy to view and convert Microsoft Office files directly from Google Drive.
A prerequisite to using Google Sheets is to have a Google account.
Getting started with Google Sheets is incredibly simple and easy. To begin with, let us first understand,
Following are the different ways to create a new spreadsheet:
As soon as you perform any of the above steps, a new blank spreadsheet will be created. The interface will be most similar to any other spreadsheet app (Just like MS Excel) you’ve used before, with basic and familiar text editing icons and tabs for extra sheets.
This is what your blank Google Sheet will look like:
You can rename your Sheet in the top left corner.
Click on where it says Untitled spreadsheet and type in whatever name you want to give your Sheet, in this example “My Sheet”.
The sheet is the collection of cells. An individual cell is a single rectangle at the intersection of one column and one row.
The columns are vertical ranges of cells, labeled by letters running across the top of the Sheet.
Rows are horizontal ranges of cells, labeled by numbers running down the left side of your Sheet.
After creating the spreadsheet, let us see
Adding data to the spreadsheet is pretty easy. All you have to do is:
Hit enter when you’ve finished entering data and you’ll move down to the next cell, having completed your data entry.
Protip: Hit the Tab key instead, you’ll move across one cell to the right!
Remember, clicking ONCE on the cell will just highlight the whole cell. Clicking TWICE enters into the cell, so you can select or work with the data only.
You can press the ESCAPE key to deselect the contents and go up a level to just having the cell selected.
If you don’t want to type in everything manually, you can also add data to your Sheet through:
However, one has to be really careful while copying and pasting. Ensure that you are copying something that is already in table form, otherwise, you will end up copying everything in one cell.
Let’s understand,
Importing a file is simple as well. You can either import directly into the current spreadsheet, create a new spreadsheet, or replace a sheet in the existing spreadsheet.
To import a file on your Google Drive, go to the FILE > IMPORT > UPLOAD menu.
Other than these, the basic functions like opening a spreadsheet, making a copy, and all other options are available under the File menu.
So, these were the basic factions with which you can get started with Google Sheets.
Let’s move one step further and see,
You can format your data as and when you want with the basic formatting options:
Print, Undo / Redo, and the Font Settings / Styling function are very similar to Excel or word processors. The shortcut keys are the same as well.
Moving ahead, let us see,
1. To add additional columns or rows,
If you want to add over one column or row, highlight that many columns and then right-click and choose to insert new columns.
For instance, if you have selected 4 rows then it’ll then insert 4 new columns for you!
2. To change the width of a column or the height of a row, hover your cursor over the grey line separating column and row. As soon as you will hover, it will look like this:
You can stretch it as per your desired width or height.
Pro-tip: To keep the width of all columns uniform, double-click when you’ve hovered over the grey line.
Besides, many times working with Google Sheets might get a little chaotic. In that case, you can use the following formatting options to use it conveniently.
3. To freeze a column or a row
Freezing a row means, even if you scroll down the spreadsheet, the first row will still be visible, no matter how much data lies below it. Similarly, you can freeze a particular column too. This allows you to have a long list, and you can easily monitor a humongous amount of data without getting lost.
This is how you can freeze a row or a column:
Click VIEW > FREEZE > 1 ROW in the navigation bar to lock the first row in place ()
Similarly, you can freeze the column too.
4. How to add alternate colors:
Working frequently with the sheets might get a little tedious or boring to you. Well, you can use alternating colors to make it interesting.
It is also known as banding.
All you have to do is simply highlight the table and then from the menu:
Select Format > Alternating colors
5. How to add or remove different sheets within one spreadsheet?
Many times you need to work along with hefty data on different subjects. In that case, instead of stuffing all data into one sheet. You can organize a large amount of data into different sheets.
All you have to do is click the big plus button in the bottom left of your Google Sheet to add a new Sheet (also called a Tab).
To rename or delete a sheet, click the small arrow next to the name (e.g. Sheet1) to bring up the menu. Here you’ll see the option to rename, delete, or even hide (and unhide) Sheets.
So far we saw about how to style your spreadsheet. Now let us see
6. How to remove formatting from your Google sheet?
To remove all formatting from a cell (or range of cells), hit Cmd + \ on a Mac or Ctrl + \ on a PC.
This will remove formatting that isn’t yours or you no longer want or need.
This is an insane time-saving hack!
Now, let’s see
7. How to add an image to a spreadsheet?
It is not just boring cells that you have to see all the time. With Google Sheets, you can even add an image to your spreadsheets. On your computer, open a spreadsheet in Google Sheets.
Till now, we saw all the basic things, now let us move a little advanced.
Let us understand
Apart from basic formatting and all, Google Sheets has a bunch of built-in formulas for better data management. If you have experience working with formulas before on other spreadsheets app, this may sound familiar to you.
To begin with, let us first understand different data types in Google Sheets.
Google Sheets spreadsheets application holds information of many types viz. text, numbers, and dates. The data-format settings define how we see the information on a particular cell. By default, it is set to ‘Automatic’. Meaning that the application reads the data within the cells, and it automatically decides how to show that information to us.
Google Sheets is intelligent enough to know which cell contains what data type, and accordingly renders it to us.
The different data formats are:
While dealing with statistics using Google Sheets, you may need to present data in a certain percentage or specific scientific representation. You can select the required format for your data from Format-> Number tab.
Here we will understand how to use built-in formulas. If you are already well-versed with existing formulas, you can also combine formulas to create more powerful calculations and string tasks together.
You can click formula to add it to a cell or start typing any formula with a = sign in a cell followed by the formula's name.
Google Sheets will auto-fill or suggest formulas based on what you type, so you don't need to remember every formula.
The most basic formulas in Sheets include:
SUM: adds up a range cells (e.g. 1+2+3+4+5 = sum of 15)
For example: =SUM(B2:H2)
AVERAGE: finds the average of a range of cells (e.g. 1,2,3,4,5 = average of 3)
For example: =AVERAGE(B2:H2)
COUNT: counts the values in a range of cells (ex: 1,blank,3,4,5 = 4 total cells with values)
For example: =COUNT(B2:B16)
MAX: finds the highest value in a range of cells (ex: 1,2,3,4,5 = 5 is the highest)
For example: =MAX(B2:B16)
MIN: finds the lowest value in a range of cells (ex: 1,2,3,4,5 = 1 is the lowest)
For example: =MIN(B2:B16)
Apart from this, you can directly do the basic arithmetic in the cell to get the results. You can perform functions like addition, subtraction, and multiplication directly without calling a formula.
Let us explain to you,
For instance, if you want to call SUM or any other above-mentioned function. There are three ways to do so:
Select a range then click the formula. By doing this, you will get the result either below or to the side of the range.
Select the cell in which you want the answer. Then click on the formula you want to use from the toolbar and select the range of cells to perform your operation on. Voila! Your answer will be there.
You can even directly type the formula into the result cell beginning with the = sign.
Similarly, you can use other functions like average, count, and likewise.
Protip: To select a range of cells, click the first cell and hold SHIFT, then click the last cell in the range.
To understand different types of functions, you can click the question mark to toggle help context for formulas on or off. It will tell you what type of information can be used in each formula and will make your formula creation.
If you make a mistake with your formula, you’ll see an error message, probably something like #N/A, #REF!, #DIV/0, etc.
You’ll need to re-enter your formula and correct it before proceeding.
Let’s move ahead and see,
Inviting others to collaborate in your sheets is one of the important features.
Here’s how it works:
The default functionality when you click the "Share" Button is to copy a link to the spreadsheet to your clipboard.
When you share this link with someone, and they click the link it will bring them to the spreadsheet, but they won’t be able to access your data.
You will have three options to manage your Sheet sharing:
Google Sheets give you the provision to permit the collaborator to either just view or edit the data.
If you’d like to give anyone within your organization or company editor-level access, click the "change…" button in the "Who has Access" section and select "On - (Your Organization Name)". (Note: this option will only appear if you're using GSuite for Work.)
Someone is "In your organization" when they have an email address and Google account for your company and anyone signed in to one of those accounts can access the spreadsheet.
You can even download the Google Sheets in different formats.
Let’s see some of the unique, powerful features that Google Sheets has:
Do you want to add some context to notify other collaborators? All you have to do is add a comment to a cell!
You can tag people (via their email address) to who you want to see the comment. They can reply and mark it resolved once it’s been acted upon.
You can also add simple notes to cells as well if you wish.
Comments and Notes can also be deleted when not required anymore.
To add a comment to a cell, first, select the cell, then right-click to bring up the menu of options. Select “Insert comment” and then simply type in your comment.
To tag somebody in your comment, type the plus sign (+) and their name or email address (you’ll see autocomplete options from your contacts, so you don't have to type in the whole email address).
You’ll notice a small orange triangle in the top right corner of the cell to indicate the comment. The comment will show up when you hover over this cell. If you click on the cell, it’ll also add orange shading to the cell background.
Comments can be edited, deleted, linked to, replied to, and resolved (comment disappears from Sheet and is archived).
You can reach and control all the Comments in your Sheet from the big Comments button in the top right of the screen, next to the blue Share button.
(The first time you tag someone in a comment, you’ll be asked to share the Sheet with them. See more on this below.)
At the same time, multiple users can work on the Google Sheets without hampering each other’s data. Besides, you might have shared your Sheet with someone. If they open it whilst you’re still working in the Sheet, you’ll see their cursor show up on whatever cell (or range) they’ve selected. It’ll be a different color, for example, green to your blue. If there is more than one person, their presence in the sheet will appear in a different color.
If they enter data or delete data, you’ll see it happening in real-time!
In this case, my active cell is the blue-outlined cell. I see somebody else, denoted by the green-outlined cell, show up in this Sheet and enter data into a few cells before deleting it.
Now, you may think that you can use Google Sheets only online. Well, that‘s not the case. You can still seamlessly work with Google Sheets even without the internet.
Let us explain to you,
Google Sheets has an "Offline Mode" that will automatically sync your changes to the document when you reconnect to the internet.
Here’s what you’ll need:
Instructions for setting up your offline sync are really straightforward, but the bulk of the process is just downloading and using the three core components above.
So, this is all about the basics. Let’s get advanced with Google Sheets:
First, let us understand
Conditional formatting is one of the great features provided by Google Sheets to apply background shading to cells based on some conditions.
Let’s see an example of conditional formatting, that is, formatting based on variable conditions.
For example, in an attendance sheet, you want to show full attendance with green font color and light green background, whilst leaving with the red font on a light red background. This gives extra context to your data and the colors help to analyze the data easily.
Here’s how you can do it:
1. Format > Conditional formatting
Here you can choose a rule, for example, values less than 8 hours and highlight them red:
Let’s see,
With conditional formatting, you can highlight data dynamically. The formatting is based on a rule, so if another value is less than 8 hours, it will apply the formatting rule and be highlighted red.
Moving ahead, one of the most important needs is to sort data.
Let us hop onto,
Sorting your data is a common requirement. It is generally used when you want to show transactions or any data from highest to lowest value, or show the names in alphabetical order.
Go to Data > Sort range…
And check the “Data has header row” option. Then you can select the column you want to sort by, and sort the option from A to Z, or Z to A.
And thus, your data is sorted.
Last but the most important function is to filter data. Let us see:
The next step after sorting your data is to filter it to hide the stuff you don’t want to see. In case of an extensive set of data, filter the data is the best way to go through them.
Click on a cell containing data in the table and then add the filters from the menu. Go to,
Data > Filter
or you can simply click this icon on the menu bar above your Sheet:
Google Sheets Filter
You’ll notice a light green shading applied to row and column headings of your filtered table, and also a green border around your table. Most importantly though, you’ll now have little green filter buttons in each of your heading cells.
You’ll notice you can manually select or de-select items to show. Let’s create a rule this time though.
For instance, you want to filter the data whose working hours were less than 8 hours,
Under the “Filter by condition” section, choose “Less than” and enter 8 into the Value box, and click OK!
There you go!
You’ll see a reduced table with employees having less than standard working hours.
To remove the filter, click the green triangle button again (now solid green) and under the “Filter by conditions” set the rule to “None”.
There’s also a native FILTER function, by which you can formulaically filter your data.
“Practice makes perfect” is as true for Google Sheets as anything else, so hop to it!
We have cleared some basic concepts of google spreadsheets and they are more than enough to get started with them.
Though there are many other features as well, we have covered basic ones here.
We hope they help you.
In case you have any comments, doubts, or queries, please feel free to drop in the comment section and we will be more than happy to help you.
We make every part of the contact management process easier. Getting started with building relationships has never been easier.
