GOOGLE SHEETS Features
Google sheets is a spreadsheet program which have different functionalities, which let the user to perform task very easily by using the features and formulas offered by google sheets. Google sheets allow the user to perform different operations like to edit, analyze and organize the data.
Google sheets have a great feature of to add collaborators as well, which let all added collaborators to edit, organize, analyze and to perform any action on that spreadsheet. Therefore, due to this feature any collaborator at any place can perform any action on that particular spreadsheet.
Google sheets have many Add-ons as well that make the task to perform more easily. Google sheets with many features also offer App script, which is used to do programming.
By using App script, one can make the sheets to perform many functions. Moreover, after completion of the Google App Script program, one can assign the script to some button and by clicking on that button, the program will be run and action will be applied on all data.
Google sheets offer functions, and by using those functions, many complicated calculations can be done easily. When there is a large amount of data, even it can be of some company consists of some years and if we want to calculate profit, expense etc. then it is difficult to do calculations manually and chances of mistake is large.
So, in this perspective as well, one by using google sheets or doing calculations on google sheets by using functions and other features, task or calculations can be done easily in less time and chances of mistakes is also negligible.
FORMULA AND FUNCTIONS IN GOOGLE SHEETS
By formula we mean, a mathematical relationship or rule expressed in symbols. By using functions in google sheets and combination of functions, cells, rows, columns and ranges used and from all these, one can obtain result.
There is a wide variety of functions available in google sheets like financial, Google, Array, Logical etc. and all these have different formulas by using them task can be done.
A list of functions will be displayed after clicking on the option:
After clicking on the option as shown in the above screenshot, it is highlighted in yellow. After clicking on the option, a list will be displayed, where there are many functions. Each function have its own functionality.
Excel is a program like, Google sheets, it also offer huge variety of features, formulas and functions. By applying these on dataset, a task can be done by organize, edit and analyze of data.
Excel like Google sheets offer us to code, apply any functionality, and let the data to behave like the way we want by using our own logic. Excel do not required any internet to code or to apply formulas.
Here, I will explain one of the function or formula in Google sheets, which used in Excel as well.
In Google sheets, it is included in the category “Math” and that is SUBTOTAL.
By Subtotal, in general it mean that obtaining a figure that is the result of doing some calculations of some numbers together but that is not the final total.
From definition, we understand that Subtotal is not same as total.
Further, if we want to understand the “subtotal” concept. Let’s take a practical example.
If we want to calculate the total profit of Year 2020, then for that, we would definitely need subtotal and that would be for each month. Therefore, this is the basic concept of Subtotal.
SUBTOTAL FUNCTION IN GOOGLE SHEETS
In Google sheets, the subtotal function subtotal for a range using a specific function.
Here “specific function” means that there is a list of functions available in database and by using their no, one can subtotal the range using that specific function.
Some of those specific functions with its no is:
- 1 is AVERAGE
- 2 is COUNT
- 3 is COUNTA
- 4 is MAX
- 5 is MIN
- 6 is PRODUCT
- 7 is STDEV
- 8 is STDEVP
- 9 is SUM
- 10 is VAR
- 11 is VARP
- Hidden values can be skipped for any of these codes by prepending 10 (to the single-digit codes) or 1 (to the 2-digit codes). e.g. 102 for COUNT while skipping hidden cells, and 110 for VAR while doing so.
For this list, a user can also get help by following these steps:
- Start writing the function in required cell by starting with “=” equals sign and then after staring bracket you will see a Pop-up for guide.
- In this Pop – up you can get the help and for the list for this database, select “Learn more” at bottom left of Pop-up guide.
- You will see a “Help window” at right side of the sheet. In this “Help window” you will get help for the specific functions which will be used in subtotal function.
SUBTOTAL(function_code, range1, [range2, …])
To apply the “SUBTOTAL” Formula in any required cell, start writing it with equals sign “=”.
The arguments in “SUBTOTAL” Formula mean:
functional_code: Here a code will be entered as in numbers like 1, 2, 3, 4, 5 … each for specific function respectively. I have explained for this argument above. Take reference from that.
range1: Here a first range will be entered, on which you want to apply the Subtotal function. You can also give reference of cells or for range where those required values exist.
Range2: Here the additional ranges will be entered, with commas in between. These are optional, You may use if there is requirement. Otherwise, no error will be occurred. For these as well, you can give reference of cells or for range where those required values exist.
As shown in the above example, I have input some values just to explain further. Here the first argument “1” is for the specific function which is in the list is “AVERAGE” and “A2:A10” is the range on which that specific function which is AVERAGE will be applied and in this way, the calculations will be made accordingly.
This was just a simple example to explain the functionality or calculations for SUBTOTAL formula.
Now, let’s begin with Step – by – Step procedure with screenshots are also attached for better understanding.
- Open the Spreadsheet on which you want to apply the formula.
- This is the data, which I will use to explain the steps.
- As you can see in the above screenshot as well, I have shown sales for each month for the year. So, in this scenario, I want to calculate firstly for sales of each month and then for each.
- This is the result after applying the formulas for each month.
- As you can see in above screenshot, that row 7 shows sales for each month.
- These are the formulas which I have applied:
- In the formulas applied “9” shows the functional code for sum as I want the sum for the sales.
- And the second argument is for the range
- Let’s use this subtotal formula for the total sales of Year. This is the result after applying the formula:
- So, with the subtotal formula, I get the sales for the year.
- The formula used is:
- In the applied formula, as shown above as well. “9” is the functional code for the sum and remaining are the ranges for the values of cells for each month respectively.
This is how one can use the subtotal formulas with its own requirements.
Get your hands on practice and follow a step – by – step procedure along with the screenshots are also attached for better understanding.