Adding some cells or a column is some of the most common things users do in Google Sheets.
If you have a column full of numbers, you can easily calculate the sum of the entire column (or a specific range in the column).
In this tutorial, I will show you how to use a simple formula to sum a column in Google Sheets.
Table of Contents
Sum a Column using the SUM Function
Suppose you have the dataset as shown below and you want to get the sum of all the values in the column.
Below is the formula that will give you the sum of all the values in the column:
Enter this formula in cell A15 (or whichever cell where you want the sum of the column) and hit the enter key.
Google Sheets try to guess the range for you which you want to calculate the sum. When you enter the text =sum and then hit the tab key, Google Sheets will automatically select the range of cells that have the numbers.
Since this is a dynamic result, in case you change anything in any of the cells, the formula would automatically update.
While this works great, what if your data expands and you get new values that you need to be a part of the sum. While you can adjust the formula, there is a better way to make the formula dynamic.
Instead of just using the range that has the values, you include more cells that might get some additional data in the future.
In our example, I can use the below formula instead:
Although cell A11 to A14 are empty as of now, these are still used in the formula. In case a value is added to these cells, the formula would automatically update to reflect these new values in the total sum.
Sum an Entire Column using the SUM Function
Another thing you can do when calculating the sum of all the values in a column is to include the entire column as a part of the formula.
For example, suppose you have the data as shown below and you want to add all the values in the entire column. At the same time, you want to make sure that in case any new value is added anywhere in the column, it’s also added to the total value.
Below is the formula that will do this:
The above formula takes A:A as the input range, which represents the entire column.
Also, don’t worry about having any text value in the column, as text values are automatically ignored.
Note: For this to work, you need to enter the formula in a cell that is not in the same column. If you keep it in the same column, it would give a reference error as there would be a circular reference issue.
Remember that the SUM formula only adds those cells that have a numeric value. If there is any text, or of the number that has been formatted as text, it would be ignored.
So this is how you can use the SUM formula to get the total of a column in Google Sheets
I hope you found this tutorial useful!
Other Google Sheets tutorial you may like:
Google Sheets, a free alternative to Microsoft Excel, has a mobile and web version. Sheets does not have the full range of capabilities of Excel, but it works well with the touch interface of a mobile phone. I would not use Sheets to manage large datasets or do any serious computation, but it has the functionality that I am likely to use on the go. Google Sheets can be used offline, or it can sync with your Google Drive so that you can pull up all your Sheets on any computer or device. If you do not already have the Google Sheets app, you can download it by following these links for android or iOS.
One of the most common uses of spreadsheets is adding up lists of numbers. This is done by using the “Sum” formula. It is quick and easy and can be copied over multiple columns.
1. Enter your data. Tap the cell where you want to sum up your data, and then tap the formula bar on the bottom of the screen.
2. Next, type the following:
Then, select the range you want to sum. The easiest way to do this is to tap a cell in the range, and then drag a corner to highlight the whole target range in red. Type in a ). The total formula should look like this:
Note that the cells you are summing do not have to be in the same column. You can tap to select each cell manually that you want to add together. If you use this method, Sheets will separate each cell reference with a comma instead of using a colon to show a consecutive range. This would look something like this:
=sum(A2, A3, B2, B3, C2, C7)
Click the check mark to exit out of entry mode. The cell will now display the sum of the cells you selected.
3. Perhaps you have multiple columns that you want to sum. This is easy to do with Autofill. Select the cell with the formula you just entered and the cells that you want to fill with the sum. Tap on the blue selected region, and tap the arrow to the right until you see Autofill. Select this option, and the cells will now contain a sum. Note that this used relative cell locations. This means that if the first cell contained a formula to sum rows 2 to 7 in column A, and you autofill this formula to columns B and C, the autofilled cells will contain the sum of rows 2 to 7 in columns B and C.
A running total is a useful aggregation tool that helps you see the cumulative sum of a sequence of numbers at any point in the sequence.
It is widely used in a number of applications in both academics and the business world.
In this tutorial, we are going to show you how to add a running totals column to your dataset for a sequence of numbers using the SUM Function
What is a Running Total?
A running total, also known as a cumulative sum is a sequence of partial sums of any given sequence of numbers.
It is updated each time a new number is added to the sequence, by adding the value of the new number to the previous running total.
In this way, you get a feel of the growth in the sequence and also know the final sum at a glance by simply looking at the last total.
In the business world, a running total is widely used to keep track of expenses, revenue as well as inventory or employees’ hours.
How to Get a Running Total in Google Sheets
To explain how to get a running total in Google Sheets, we are going to use the following dataset:
We will demonstrate how to apply a running total to the sequence of numbers in column B (Daily Expenditure) of the above dataset.
Using the SUM Function to Get Running Total in Google Sheets
In this method, we will use the SUM function to add the values of column B’s first row to the value in column B’s current row. So to find the running total at row 9, for instance, the formula would be:
This will find the sum of all values starting from row 2 all the way to row 9.
Notice we used dollar symbols ($) in the starting cell reference and did not use them in the ending cell reference.
This is because we want the starting cell reference to be an absolute one, while the ending one should be a relative one.
These mixed references will allow you to create a growing total that is anchored to the initial value.
Here are the steps to get a Running total for the Daily Expenditure in column C:
- Type the formula: =SUM($B$2:B2) in cell C2.
- Press the fill handle for cell C2 (located at the bottom right corner of the cell). This will copy the formula to the rest of the cells in column C.
- You should now have a running total of column B in column C!
What are Absolute and Relative Cell References?
Cell references can be absolute or relative based on how they behave when copied to other cells. A relative cell reference changes when it is copied or filled to other cells. An absolute reference, on the other hand, remains the same no matter where it is copied.
An absolute cell reference is specified using the dollar symbol ($). For example, an absolute reference to the cell B2 is written as $B$2.
Here’s an example to demonstrate the difference between absolute and relative references. In the following screenshots, you can see there are two references to the value is cell A1:
- A1 (a relative reference)
- $A$1 (an absolute reference)
Both give the same result, which is ‘20’.
Now try using the fill handle to copy the formula to the next cell in the column:
You will notice that the reference in cell B2 changed from the A1 to A2 to reflect the change in the row number.
The reference in cell C2, however, remains the same.
Explanation of the Formula
The formula in cell C2 is =SUM($B$2:B2). This adds the values from cell B2 to B2, which is just a single cell, so the answer would be 72.34.
In cell C3, the formula now becomes =SUM($B$2:B3), This adds values from cell B2 to B3, which consist of the first two values in the Daily expenditure list.
Similarly, in cell C4, the formula becomes =SUM($B$2:B4), This adds values from cell B2 to B4, which consist of the first three values in the Daily expenditure list.
This goes on to the last row where you get the sum of all values from the first to the last row of the Daily expenditure list.
Creating a Dynamic Running Total Column
If you don’t like the idea of copying the formula for Running total every time a new value is added to the Daily Expenditure list, you could add an IF statement to the formula to ensure that a running total is calculated only if the corresponding daily expenditure value is not blank.
For example, for cell C2, the formula could be changed to:
Then you could copy this formula to the entire column in advance so that only when a new value is added to column B, the corresponding value in column C automatically calculates the running total up to that point.
In this tutorial, we showed you how to calculate and display a dynamic running total for a single column in Google Sheets.
All it requires is a simple application of the SUM function with a combination of both relative and absolute cell references. We hope this tutorial was helpful for you.
I hope you found this tutorial useful!
Other Google Sheets tutorials you may also like:
You may not always need all the columns that have the data or extra columns that are empty and need not be displayed.
In such cases, you can easily hide columns in Google Sheets.
And there are many ways to do.
In this tutorial, I will show you multiple ways of hiding columns in Google Sheets (basic + advanced)
Table of Contents
Shortcut to Hide Column in Google Sheets
If you are a keyboard person like me and prefer using a lot of shortcuts (instead of switching between the mouse and keyboard), this one is for you.
To hide a column (or a range of columns), select the column(s) and use the below shortcut:
When you use this shortcut, all the selected rows would be hidden
Hide Columns with Right-Click
Another quick method to hide columns in Google Sheets is by using a simple right-click method.
Below are the steps to hide a column in Google Sheets:
- Select the entire column (or columns) you want to hide. To do this, click on the column header at the top of the working area
- Right-click anywhere in the column
- Click on ‘Hide Column’
The above steps would hide the selected column.
The above steps also work for a range of columns and even non-contiguous columns.
For example, if you want to hide column D and G, hold the shift key on the keyboard and select both these columns using the mouse. Once selected, right-click on any of the columns and click on ‘Hide Columns’
Hide Unused columns in Google Sheets
If you don’t want unused rows and columns to be visible in the Google Sheets worksheet, you can easily hide these.
Below are the steps to hide unused columns in Google Sheets:
- Select the first column next to the last used column. You need to select the entire column by clicking on the column header at the top.
- Hold the CONTROL and SHIFT keys and press the right-arrow key. This will select all the unused columns.
- Right-click on any of the selected columns and click on the Hide Columns E-Z option (in your case, it may show different column alphabets based on what columns you have selected.
In Step 3, you can also use the keyboard shortcut CONTROL + ALT + 0 to hide all the selected columns.
You can also use the same methodology to hide unused rows as well.
Hide Columns In Google Sheets App
If you use Google Sheets app on Android on iOS, you can easily hide columns there as well.
Below are the steps that will hide the columns in a Google Sheets app in an Android device (same for iOS as well):
- Open the Google Sheets document and select the worksheet in which you want to hide the column
- Tap the column header to select the entire column. In this example, I tapped on Column E
- Touch the Column header E and hold it for a second. It will make available a menu of options as shown below. Click on the three dots at the right of the menu
- Tap on the Hide Column option and it will hide column E in the Google Sheets App
Unhide Columns In Google Sheets
When you hide a column or range of columns, you can easily unhide these using a single click.
Below is an example where I have hidden Column B and you will notice small double-pointed arrows in between columns A and C. This is a visual indication that there is a hidden column(s) in between the two columns.
To unhide a column, simply click on this icon and it will show all the columns that hidden in between the two columns.
You may also like the following Google Sheets article:
Microsoft’s Excel and Google Sheets have quite a few differences seen on both the surface and what lies just below. However, they both have quite a few things in common as well. One of those common features that are lacking is that neither possesses a DIVIDE function.
Instead, Google Sheets relies on the creation of a formula in order to perform division operations. Don’t let that scare you off. I know most who are likely to come looking for the answer to the title has very little if any experience creating formulas, let alone ones in Google Sheets.
So, for this article, I’m going to help you get over that fear and realize that formulas are nothing to be afraid of. In fact, it’s actually quite simple. Of course, errors may occur, but I also plan to teach you how to deal with the errors that you might experience, and how you can do the same thing from your Android phone.
How You Can Divide in Google Sheets
As I’ve said, to divide in Google Sheets, you’re going to need to get acquainted with creating formulas. So, I would think that it’d be best if we start there.
Creating Formulas in Google Sheets
In order to divide two numbers in Google Sheets, you’re going to have to create a formula .
Some valuable information to keep in mind when it pertains to formulas in Google Sheets:
- Every formula that you create will always begin with an equal ‘ = ’ sign.
- Whichever cell that you want the answer to the formula to go is where you’ll be placing that ‘ = ’.
- What you’ll be using to trigger the divide, also known as the division operator , is the forward-slash ‘ / ’.
- The formula is completed by pressing the Enter key on the keyboard.
Use Cell References Instead of Manual Input
It is possible to enter numbers directly into a formula manually but it may be better for you to instead, use cell references. By entering the data into the worksheet cells and then using the addresses or references of those cells in the formula, it can help with changes later. What I mean is that if it ever becomes necessary to change the data within the formula, you’ll make it so that you can just replace the data in the cells rather than needing to rewrite the whole formula. This makes things much easier on you in the long run as the results of the formula update automatically whenever the data changes.
Setting Up A Division Example
Time to get into the nitty-gritty of creating formulas. To familiarize yourself with how to go about doing so, we’ll create a simple division formula example to use in this article.
For this example:
- Type 20 into cell A2
- Type 10 into cell A3
- Type = into cell B4
Now, you could just type in A2/A3 directly into cell B4 to get the desired result. However, it may actually be easier to use the point and click method. If not easier it would at least be something that you could benefit from learning as it makes it so you won’t have to write out longer equations every time you want to use them. Instead, you can just highlight the results cell while clicking the cells that you want to add to the equation.
So, continuing on:
- Still in cell B4 after the = sign has been entered, click on cell A2 using the mouse pointer. It should have placed an A2 into cell B4 following the = sign.
- Type in the / (division operator) into cell B4 following the A2.
- Next, click on cell A3 with your mouse pointer.
- This will add an A3 following the / sign in cell B4.
- Press Enter on your keyboard to complete the formula and process the results.
- The answer will appear in cell B4. It should be 2 as 20 / 10 = 2.
If you look in the formula bar of cell B4, you’ll see =A2/A3. The formula took the data from A2 when you clicked it and divided it by the data in A3, which you also clicked. This is how point to click works when creating formulas.
Congratulations, you’ve learned how to create a division formula in Google Sheets.
Using Cell References
To test the value of using cell references in a formula, change the number in cell A3 from 10 to 5 and press the Enter key on the keyboard. This will change the results found in cell B4 to 4 automatically. This is because 20 / 5 = 4. Anytime you need to change the data, this is the easiest way to do so. If you need to change the formula, or just use a different formula, you’ll need to refer to the previous segment.
A Common Error
The #DIV/O! error value is going to be the most common error associated with division operations that you’re likely to face. If the denominator in the division formula results in a zero, you’ll see this error pop-up. This is because a zero denominator is not allowed in ordinary arithmetic.
The most likely reason for this happening is that an incorrect cell reference was entered into the formula or the formula was copied to another location using the fill handle, and the changing cell references results in the error.
A fill handle refers to the auto-populating results that Google Sheets recognizes from previous use. Think of when you type something into Google search and suggestions pop-up just below as you’re typing. A fill handle is just like that except that the suggestions are actually things you’ve previously typed in before.
Calculating Percentages Using Division Formulas
The general form of the equation to calculate results as percentages using a division formula is:
We can use the data you already have in cells A2 and A3 to create the results:
- Type = ( into cell B5.
- Use the mouse pointer to click cell A2.
- Type in the / sign.
- Use the mouse pointer to click cell A3.
- Now, type )*100.
- Finally, hit Enter on your keyboard.
This will result in a number with a decimal instead of a percentage. To change it to a percentage:
- Highlight the results cell.
- Open the “Format” tab in the main menu bar at the top.
- Select Number from the list, then select Percent .
This will change all results in that cell to a percentage-based result.
If you need to compare data or need to revisit a set of data often in Google Sheets, then it can be quite cumbersome to scroll back to it every time. Fortunately, Google Sheets lets you lock one or more rows and columns to keep them on-screen even if you scroll to a different section on the sheet.
You can do this on both the desktop and mobile apps of Google Sheets and in this post I will show you how to do it.
Freeze rows and columns in Google Sheets desktop
First, you should know that you can only freeze rows from top to bottom or columns from left to right, you can’t choose rows or columns from the middle. Therefore, all the required data must be in the starting rows/columns of the sheet. Let’s see how you can freeze rows and columns in the Google Sheets desktop version.
Click on the last row (if there is more than one) that you would like to freeze to select it.
Now click on the “View” menu at the top and hover your mouse cursor over the “Freeze” option.
A bunch of options will open to freeze both rows and columns. You can select 1st or 2nd row/column, or all the rows/columns before the row/column you have selected.
Once the right selection is made, you can scroll and see those rows/columns will stay in view. Furthermore, you can also freeze both rows and columns at the same time and they will all stay in view. A gray line will distinguish between frozen and regular rows/columns.
When you are done, you can unfreeze rows/columns by going to the View > Freeze menu again and selecting “No rows” or “No columns” options.
Freeze rows and columns in Google Sheets Mobile app
On the Google Sheets Android or iOS app, it’s actually even more easy to freeze a row or column. Here’s how:
Open the sheet and tap on the “Sheet” tab at the bottom.
Now scroll down and you will see two options “Freeze rows” and “Freeze columns”. Click on the upward and downward arrow next to them to select the number of rows and columns you want to freeze. A gray line will indicate the selected rows and columns.
You can decrease or increase the number as needed, and you can unfreeze them by setting the number to 0.
Locking rows and columns in one place is definitely a great way to keep content in check as you work on the sheet. Although be careful when selecting too many rows or columns to freeze as your screen must have enough space to show both frozen data and regular data. You can always adjust screen size if you really need to select a lot of rows and columns to freeze.
With LiveForms connecting to a Google Sheet is a simple way to capture and share data in real time. If you want to include a row that totals column data there are few properties you will need to set in Google Sheets.
First, please be sure that your column headers are set for your form or flow in the Google Sheet. If you make a change to the headers, you will need to repeat the steps.
Next, in the row below your headers, type in “Total” and identify the cell that will contain the sum value. **Although it is not ideal, you will need to have the sum cell in a different column than the values that are added. If the sum cell is in the same column you will get a circular dependency error.
Highlight the cell and set up the sum range for the number of rows you are totalling.
Next, highlight the total row. RIght click and choose “protect range”. A sidebar will open with a field for the protected range. It should say Sheet1!#:# with the # being the row number for the total. Click set permissions and then you can configure who can edit the range. You may want only the document author to edit or want the row to be protected from any edits. It is up to you.
Now when you add a row from a form or flow to the google sheet, the totals row will shift down and the sheet will create a new row with each submission . The total will calculate as the sheet is updated.
We have already shown you how you can convert PDF tables to Google Sheets, and now we can talk about what happens when you have a spreadsheet ready for further editing. On mobile phones the Google Sheets interface is different than on the web, which is why some things are done differently. The following tips for using Google Sheets on mobile phones are valid both for Android and iOS systems.
1. Freeze rows
To freeze a row or column, first select it. Tap anywhere on the selected section and new options will appear.
Tap on them and choose Freeze.
Sometimes the Freeze option is immediately visible and sometimes you need to continue tapping.
2. Resize rows and columns
To resize a row or column, tap on the column name (for instance column C). You will see two small arrows- left and right, something like this ⏩. Tap on the arrows and then drag the row or column to increase or decrease it. When you finish just tap anywhere outside the column/row.
On smaller screens this might not be easy to do, but you just have to tap very precisely on a particular spot.
It is not possible to select multiple rows in Google Sheets on iPad or any other mobile device.
3. Insert function
To insert a function, simply tap on the cell and then on the field below. Enter the equal sign followed by a function name, and enter all the necessary arguments.
Sometimes the autocorrect might interfere with entering functions, so be prepared for that.
4. Change cell format
This one might seem tricky. If your cell is formatted as a number but you want it to be formatted as text or date, here is what you can do. Tap on the A letter in the upper menu and choose Cell.
In the field Number format choose the appropriate number.
On the other side in the Text tab you can change the cell’s look, font, size, color and more.
5. Save as Excel
If you want to save the spreadsheet in Excel format, which you may want to send by email to someone, simply tap the More settings.
Choose Share & Export and then Save as Excel.
6. Use Autofill
The Autofill function is an extreme time saver. It exists in mobile version as well. To use it, simply enter a couple of the first elements in a sequence (for example: 1, 2, 3 in a row or column), select a portion of the row or column that needs to be filled till the end as started, tap on the selection and choose Autofill when it appears.
The selection will be filled with appropriate elements that logically follow what you entered (for example: 4, 5, 6…).