The previous article was an introductory one, where we talked about the importance of Google Sheets. And covered a few fundamentals like creating a new spreadsheet and filling cells with data.
In this article, we will continue using arithmetic formulas and discussing how data transfer and handling work in Google Sheets Offline Mode. Enjoy!
Adding, Averaging and Filtering Data with Formulas
If you work with spreadsheets, it’s important to use formulas to be able to perform the required statistical and data manipulation tasks on the given data. As with most spreadsheet apps, Google Sheets also provides a bunch of formulas and you can even combine them to create higher-level calculations or string tasks together. It uses the exact same formulas as Excel. So it’s definitely an advantage if you already have some experience with them.
In this tutorial, we will show you the five most popular formulas. You just need to click on the sigma symbol right above the first row, and a formula drop-down menu will appear. There are two ways to add a formula to a cell. You can either click on it or type it into a cell with a = sign before the formula’s name. As you start typing, the sheets will suggest or auto-fill formulas in advance, so you don’t need to remember them. Let’s take a look at the five fundamental formulas:
- With the SUM, you can add up a range of cells
- The AVERAGE displays the average of specified cells
- COUNT adds up all the cells that have a value
- MAX and MIN are used to find the highest and lowest value of specified cells
- Basic Arithmetic allows you to use functions such as multiplication, addition, and subtraction directly without the need to call a formula
The SUM Formula
The SUM formula can come in handy every time a total number of a certain data set needs to be calculated. It just simply adds each cell value together and displays the total amount. You can select a range of cells, and once you click the formula, the result will appear to the side or below the range.
Another way to do it is to select the cell where you want your result to appear. And then either click on the formula or type it in starting with the = sign before selecting the range of cells with the values.
If you choose to type in the formula, you can define the range of cells as well (for example =sum(C1:C10)). A range of cells can be selected by clicking on the first cell, holding shift, and then clicking on the last one. Let’s suppose you want to select B1 through B10, then click B1, hold shift, and click B10. You need to press Enter to activate the formula.
Every time you create a formula, a help section will pop up when you start typing. By clicking on the question mark, you can toggle the help context on or off. These tips always provide you with tips and examples, thus making formula creation much easier for you. The SUM formula can be applied to other cells as well by dragging the blue dot across the desired cells.
The COUNT Formula
The COUNT formula is good for measuring how many filled cells are present in a particular set of data. It checks each cell in the range to see if they are empty or not and returns the total amount of filled cells. This formula can be used for long lists with many gaps to count the number of items in a particular row.
When you select an array of cells, a pop-out menu will appear in the bottom right corner that displays the SUM if your list consists only of numbers or the COUNT if you have a mixed range of text and numbers. Once you click on it, you will see six results calculated with the simple arithmetic operations we previously mentioned. You can select a default formula there whenever you select a range of cells. Therefore every time you highlight them, it will perform the formula you lastly selected.
The AVERAGE Formula
Consider an economic problem where you have the minimum and maximum value of certain items and you need to estimate the average value for each of them. Of course, you can do it on paper by adding the two numbers and dividing them by 2. But what if you need to calculate the average for 100 items?
Well, you can copy the minimum and maximum values side-by-side for one item in each row into a spreadsheet. Select the two related values for each item, and click the AVERAGE formula. It will return the answer immediately to the cell on the right, and then all you need to do is to select the result and drag the blue square in the corner down across the column until you reach the last Min and Max pair. Release the mouse button there, and all the selected cells will be filled with the correct results. Then just give the “Average Item Cost” name to the column, and you’re finished.
Simple Arithmetic Formulas
Let’s suppose that you work in a shop and get a list of products a customer wants to buy. To get the total cost, you should multiply the price of each product by its unit count and add up the results. In Google Sheets, this is a quite straightforward task and you can get to the final result by using a simple arithmetic formula.
For example, if the customer wants A1 amount of laptops for B1 price and A2 amount of LCD monitors that cost B2 and so on.
Then the formula will look like this: =A1*B1+A2*B2+…+Ax*Bx and, of course, X represents the last cell in the A and B columns.
However, there are cleaner ways to perform a formula like this one. The same calculation can be accomplished with this advanced formula: =SUM(ARRAYFORMULA(A1:Ax*B1:Bx)). But this was just an example, there are many more formulas that can solve the more difficult tasks for you, and we will talk about those shortly.
In the next part, you will learn more about how to share your spreadsheet and invite people who can view, use, and edit our data.
How to Share, Protect and Move Data?
With Google Sheets, it’s much easier to work in sync with your coworkers. Editing a spreadsheet together is one of the most powerful functions of Sheets, and they have made it a seamless experience.
To share a spreadsheet, just click File > Share, click the „advanced” option. And then add the emails of people who can edit or view your work. At the advanced panel, you can change permissions for each user or restrict access to your spreadsheet.
There is also a share link which can be sent to people via messenger and email, and it will immediately bring them to your spreadsheet. Although they can only edit it if you selected the “Can Edit” option while inviting them. Otherwise they need to ask for permission to change the data.
To give editor-level access to anyone within your company or organization, look for the “Change” option in the “Who has access” section, and choose “On” next to your organization name (this option appears only for those who are using Google Apps for Work). Therefore, everyone who has a Google account and email address for your company will have permission for editing the spreadsheet.
For example, if your company is called “Jellybean”, then everyone in your company will have a @jellybean.com email address. So they are free to access your spreadsheet.
Sharing Spreadsheets via Devices and Apps
Google Drive and Sheets were mostly built for sharing, but notice that your spreadsheets are often created as internal documents. And getting work done is actually more important than sharing. You can use the following helpful add-ons to streamline your workflow and data-sharing capabilities:
- Google Docs for mobile. You can use this mobile app to work on your spreadsheets. Share links with your coworkers on the go, and manage users. It’s a decent companion for the web app.
- Google Drive sync. With Google Drive, you can easily upload files to your storage drive from your local desktop. This way, it will be easier to import them and your collaborators can access them as well.
- Third-party tools like Zapier. Zapier has some awesome functions like automatically adding data to your spreadsheets, alerting you of changes, sending files to your Google Drive, and more.
How to Download Your Data?
Whenever you need to send a spreadsheet to an external collaborator or upload it to another system. Then you can choose one of Google Sheets’ many file export options.
Spreadsheets are most commonly exported as .xls documents (Excel) or .csv (comma-separated values). It’s better to choose .csv if you are not sure which one to use.
Use Spreadsheets in Offline Mode
(Google Sheets Offline Mode)
There is no need for an Internet connection in order to use Google Sheets since it has an Offline Mode as well. You can continue working on your documents offline. And your changes will be automatically synced as soon as you reconnect to the Internet.
This feature can be quite useful if you are on a road trip or a flight, for example. Then it may come in handy to treat it like a desktop application.
Conclusion –
Hope you the article ” Editing Google Sheets In Offline Mode” useful. Let me know your suggestion in the comment section.