The word add can mean a couple of things when incorporated into a task in a spreadsheet. In this case, it can mean either automatically to add a series of numbers or performing a summation of numbers in Google Sheets.
Both of these processes are commonly utilized in Google Sheets, and in this article will cover both instances.
Let’s get started.
Automatically Add Numbers in Sequence in Google Sheets
Adding serial numbers is a common practice in a spreadsheet. It is a simple task that may seem daunting if there is a huge amount of data to serialize.
Thankfully, Google Sheets provides its users with simple features to automatically add serial or sequential numbers in a worksheet. It is called Autofill.
For this example, we have the following worksheet:
We will try to add Serial numbers to the names and we can do so with Autofill in two ways:
- Drag the Fill Handle
- Double-click the Fill Handle
1. Drag the Fill Handle to Generate Serial Numbers
For this process, we must first input the first two values of the number series. Which is this case is 1 and 2:
Next, select these two cells. You’ll notice a tiny blue square on the bottom right of the selection. This is the Fill handle.
Placing the mouse cursor over the fill handle will transform it into a plus (+) symbol.
At this point, click and drag the fill handle down the column to automatically add the serial numbers to the cells in Google Sheets:
2. Use the Double-Click Shortcut on the Fill Handle
Now imagine you have hundreds of data that need to be serialized. Using the dragging method can prove to be quite tedious as you will have to click, drag, and scroll down the entire column!
Fortunately, we have a shortcut to let Google Sheets do the dragging for us. That is double-clicking on the fill handle.
We start the same way as before, by inputting the first two numbers of the series and selecting them:
All we have to do now is double-click on the fill handle to perform the autofill:
However, this method has two limitations and both of them stem from the same reason: lack of user control.
The first is that this method can only autofill columns. Auto-filling rows will still take a human touch.
The second is related to how Google Sheets perceives the autofill.
You may have noticed the double-click autofill perfectly stopped at the end of the list:
This is because when using this method, Google Sheets looks at the adjacent cells of the autofill column. If a blank adjacent cell is recognized, the autofill will stop then and there.
This can pose an issue if there’s no uniformity of the columns or if there is a blank cell in the adjacent column.
Add Numbers to Sum Automatically in Google Sheets
Next, we will look at how to add, or more specifically, sum numbers automatically in Google Sheets.
The idea is to create a dynamic sum of values in the worksheet. And this can be approached either by using functions or built-in features of Google Sheets.
1. Create a Dynamic SUM formula to Add Numbers in Google Sheets
Here we have a simple dataset that calculates the sum of the quantities of the Quantity column:
Now, if we are to add another number in cell B12, that number will not be added to the total sum in cell D2.
This is because the SUM function has a limited range of B2:B11 and thus stops counting values after cell B11:
To make the SUM function dynamic and allow it to include newly added values to the column, we must remove the row number that acts as a limiter for the column from the end of the range.
That is, updating B2:B11 to just B2:B:
Let’s see what happens when we add new values to the Quantity column:
Notice the change in the Total value.
2. AUTOSUM in Google Sheets
If you come from MS Excel or have used the application in the past, you should be familiar with the AUTOSUM feature.
Essentially, the feature recognizes a range of selected cells and adds the numbers together to give a sum result.
However, AUTOSUM is not available in Google Sheets, at least the feature is not named as such. But an automatic sum function is available in the application.
For this example, we have the following worksheet:
We will try to automatically add or sum all the values in the Quantity column.
Step 1: Select the range of cells in the column you want to perform the sum on.
Step 2: Navigate to the SUM option from the Functions button in the Google Sheets toolbar.
Functions > SUM
This SUM option automatically generates a SUM function for the selected cells in the next cell of the column:
Step 3: Once you are satisfied with the formula, press ENTER to apply.
This method can also be applied to single-row values:
Important Note: This SUM feature is not the same as the AUTOSUM of Excel for two reasons:
- AUTOSUM smartly recognizes different columns and rows and then adds the values according to the different columns and rows. SUM sees all selected cells as a single data range and generates a single result.
- SUM generates a SUM formula in a Google spreadsheet. AUTOSUM simply recognizes number values in the selection and adds them.
That concludes our simple tutorial on the different ways to automatically add numbers in Google Sheets.
We’ve discussed both instances of the meaning derived from the word “add” in the spreadsheet application.
Feel free to leave any queries or advice you might have in the comments section below.
Related Articles for Reading
- How to Autofill Formula in Google Sheets (3 Easy Ways)
- Google Sheets Smart Fill: Recognize and Autocomplete Patterns (A Complete Guide)
- Google Sheets: How to Autofill Based on Another Field (4 Easy Ways)
- How to Add Up A Column in Google Sheets
- How to Perform Conditional Sum in Google Sheets (Easy Guide)