Add Row Numbers And Skip Blanks in Google Sheets

Do you ever have to input a list of numbers into a spreadsheet in Google Sheets, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have to scroll up and down the sheet to look for the right spot. Well, today, I’m going to show you how to add row numbers and skip blanks in Google Sheets so that your data entry process is a little bit smoother. Let’s get started!

Add Row Numbers And Skip Blanks in google sheets1

Generic formula in Google Sheets

=IF(ISBLANK(B2),"",COUNTA($B $2: B 2))

Summary

To add row numbers to a list of data, skipping blank cells in Google Sheets, you can use the formula based on COUNTA and ISBLANK. Just using the following formula:

=IF(ISBLANK(B2),"",COUNTA($B$2:B2))

Add Row Numbers And Skip Blanks in google sheets1

A simple way is to use IF statements with anondata formulas to show certain information when it comes up while keeping other things hidden from view unless desired by users who will open them up specifically looking for these details themselves!

Explanation 

The purpose is to add row numbers in column A only when there’s a value present elsewhere.

The IF function checks whether or not cell B2 has any values with ISBLANK.

=ISBLANK(B2) // FALSE if not, TRUE if empty

Add Row Numbers And Skip Blanks in google sheets1

When B2 is empty, ISBLANK returns TRUE, and the IF function produces a plain string like this: “”. If there’s anything in between them (i.e., not an expandable reference), we get COUNTA with whatever values are stored at each cell of that array; namely, nothing if it exists or NaN for non-numbers.

=COUNTA($B$2:B2) // expanding range

Add Row Numbers And Skip Blanks in google sheets1

As the formula is copied down, each row’s count expands to include all non-blank cells in that range. COUNTA will count both numbers and text, making it easy for you to determine who was counting what during your data entry!

Method2: Autonumber Rows If Adjacent Cell Not Blank in Google Sheets

The easiest way to auto number your column is by using this formula: =IF(B2<>””,COUNTA($B$2:B2),””) and then dragging down with the filehandle. You can also add row numbers manually if you want, but why not make things easier on yourself in Google Sheets?

Add Row Numbers And Skip Blanks in google sheets1

Note: When you enter a formula to auto number cells, make sure that B2 is populated with the relevant value.

Entering new data or deleting existing values will automatically cause the sequence number in column A to be renumbered.

Related Functions

  • Google Sheets ISBLANK function
    The Google Sheets ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…
  • Google Sheets IF function
    The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Google Sheets COUNTA function
    The Google Sheets COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…

 

Related Posts

Add Months To Date In Google Sheets
Add Months To Date in google sheets1

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months ...

Add Leading Zeros in Google Sheets
Add Leading Zeros To Numbers in google sheets1

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

Add Minutes to Time in Google Sheets
Add Minutes to Time in google sheets1

Adding whole hours or decimal minutes to a given time in google sheets is a fairly simple process. You can do this by using the Start_time+TIME(0,minutes,0) and Start_time+minutes/1440 functions. In this add minutes to time guide, we will show you ...

Add Hours to Time in Google Sheets
Add hours to time in google sheets1

Adding whole hours or decimal hours to a given time in Google Sheets is a fairly simple process. You can do this by using the Start_time+TIME(hours,0,0) and Start_time+hours/24 functions. In this add hours to time guide, we will show you ...

Count Cells that are Case Sensitive in Google Sheets
counting the cells that are case sensitive in google sheets1

If you are a frequent user of Google Spreadsheets, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Average Last N Values in Google Sheets
Average last 5 values in google sheets1

Average is a valuable function in google sheets that allows you to rapidly compute the average of the past N values in a column. However, you may need to insert new numbers beneath your original information from time to time, ...

Assigning Points based on Late Time in Google Sheets
assign points based on late time in google sheets1

It is shown in this lesson how to utilize the IF function in Excel to allocate points based on the amount of time that has passed. If you intend to pursue along this guide, you may do so by downloading ...

Google Sheets Nexted IF Functions (Statements) Tutorial (15 IF Formulas examples)
google sheets nested if function1

This tutorial will guide you how to use nested Google Sheets IF function  (include multiple If statements in Google Sheets formula) with syntax and provide about 15 nested IF formula examples with the detailed explanation in Google Spreadsheets. Description The ...

Google Sheets IF Function
google sheets IF function1

This post will guide you how to use Google Sheets IF function with syntax and examples in Google Spreadsheets. Description The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return ...

Google Sheets ISBLANK Function
google sheets ISBLANK function1

This post will guide you how to use Google Sheets ISBLANK function with syntax and examples in Google Spreadsheets. Description The Google Sheets ISBLANK function returns TRUE if the value is blank or null. The ISBLANK function is a build-in function ...

Sidebar