Add Row Numbers And Skip Blanks in Excel

Do you ever have to input a list of numbers into a spreadsheet, 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 so that your data entry process is a little bit smoother. Let’s get started!

Add Row Numbers And Skip Blanks4

Generic formula

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

Summary

To add row numbers to a list of data, skipping blank cells, 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 Blanks4

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 Blanks4

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 Blanks4

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

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?

Add Row Numbers And Skip Blanks4

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

  • Excel ISBLANK function
    The Excel ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…
  • Excel IF function
    The Excel 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])….
  • Excel COUNTA function
    The Excel 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],…)…

How to Count Cells Are Not Blank or Empty in Excel

This post will guide you how to count cells that are not blank or empty in a given range cells using a formula in Excel 2013/2016.How do I count the number of cells that are not blank in a particular range with a VBA macro in Excel. You should know that excel offers several count functions to count the number of cells within a specific range, that meet the defined criteria or conditions in Excel.

If you want to count the number of cells that are not blank or non-empty in a selected range, you can use the COUNTA function or COUNTIF function.

Method 1: Count Non-Blank or Non-empty Cells with COUNTA Function


Assuming that you have a data list in a range A1:A6, and this range has blank cells as well. And you wish to count cell without blank cells. And you can use a formula based on the COUNTA function like this:

=COUNTA(A1:B6)

count non blank nonempty cells1

The COUNTA function can be used to count the number of cells that contain numbers, text,dates or errors. And it will ignore all empty cells automatically.

Method 2: Count Non-Blank or Non-empty Cells with COUNTIF Function


You can also use another function called COUNTIF function to achieve the same result of counting the number of cells that are not blank in Excel. For example, using the following formula:

=COUNTIF(A1:B6,”<>”)

count non blank nonempty cells2

From the above image, you would see that using the COUNTIF function to count the number of non-blank or non-empty cells in the range A1:B6 and return the same results as COUNTA function.

Method 3: Count Non-Blank or Non-empty Cells with VBA Macro


You can also create an newly VBA Macro to count cells that are not empty in a given range in Excel. just do the following steps:

Step1# open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

 Get the position of the nth using excel vba1

Step2#  then the “Visual Basic Editor” window will appear.

Step3# click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub CountCellNotBlank()
Dim ws As Worksheet
Set ws = Worksheets("Sheet7")
ws.Range("D3") = Application.WorksheetFunction.CountA(ws.Range("A1:B6"))
End Sub

count non blank nonempty cells5

Step5: back to the current worksheet, click on Macros button under Code group. then click Run button.

count non blank nonempty cells3

Step6: let’s see the result:

count non blank nonempty cells4

Related Functions

  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel COUNTA function
    The Excel 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],…)…

 

How to Create Dynamical Drop-Down List and Sort by Alphabetical Order in Excel

In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ features, and we also need the help of formula which is combined with excel functions. It sounds complex, but through this tutorial we will introduce you the way to create dynamical dropdown list clearly, we will show you the details by steps, you can follow each step to complete all operations and reach your goal finally.

Precondition:

Prepare a list full of different fruits. We may also add new fruits into the list. Now we want to create a dynamical dropdown list and sort fruits by alphabetically order.

How to Create Dynamical Drop-Down List 1

Method: Create Dynamical Drop-Down List and Sort Alphabetically


Step 1: Select this fruit list and click Formulas in ribbon, and select Define Name under Defined Names group.

How to Create Dynamical Drop-Down List 2

Step 2: In New Name dialog, enter Name as ‘List’, keep Scope unchanged, enter formula =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001)) into Refers to. Then click OK. Please notice that in this formula ‘Sheet1’ is current sheet name, $A$1 is the first cell of your selected data.

How to Create Dynamical Drop-Down List 3

Step 3: Create sheet2, in A1 enter the formula =IF(COUNTA(List)>=ROWS($A$1:A1), INDEX(List, MATCH(SMALL(COUNTIF(List, “<“&List), ROW(A1)), COUNTIF(List, “<“&List), 0)), “”). In this formula, ‘List’ is defined name in last step.

How to Create Dynamical Drop-Down List 4

Step 4: As above formula is an array formula, so press Ctrl+Shift+Enter to get value.

How to Create Dynamical Drop-Down List 5

We get the first fruit ‘Apple’ from original table.

Step 5: Drag the fill handle down till blank cell displays.

How to Create Dynamical Drop-Down List 6

Note:

If you are confused about above formula and cannot apply it correctly in your work with your instance, you can directly copy the whole list from sheet1 to sheet2, and then sort them by A->Z. Then when you adding new parameters into sheet1 original list, you have to copy this list into sheet2 and sort again.

How to Create Dynamical Drop-Down List 7

Step 6: Click Formulas in ribbon, and select Define Name under Defined Names group. Enter ‘SortList’ as Name, enter =Sheet2!$A$1:$A$20 into Refers to. Then click OK. Please notice that as we create a dynamically dropdown list, so we can add new parameters into the list without limit, in this instance we set ‘Refer to’ range $A$1:$A$20, so only the first 20 cells in A column can be listed in dropdown list. You can change the reference per your demand.

How to Create Dynamical Drop-Down List 8

Step 7: Select C2 to locate dropdown list. Click Data->Data Validation under Data Tools group to create data validation.

How to Create Dynamical Drop-Down List 9

Step 8: In Data Validation window, under Settings tab, under Allow dropdown list select ‘List’; Enter =SortList in Source. Then click OK.

How to Create Dynamical Drop-Down List 10

Step 9: Verify that dropdown list is created. Values are sorted by alphabetical order properly.

How to Create Dynamical Drop-Down List 11

Step 10: Add a new fruit ‘Longan’ into sheet1 original list. You can see that it is added into sheet2 list automatically with proper order, and it is also displayed in dropdown list properly.

Sheet1:                                    Sheet2:                                 Dropdown list:

How to Create Dynamical Drop-Down List 12
How to Create Dynamical Drop-Down List 13
How to Create Dynamical Drop-Down List 14

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel IF function
    The Excel 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])….
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel COUNTA function
    The Excel 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],…)…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel SMALL function
    The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …

How to Calculate the Percentage Based on Different Data Type and Table Structure in Excel

We often do statistics analysis by Excel in our daily work, and sometimes we need to count the percentage for different data in a series of data, so that we can see the data distribution of this series of data and we can also find out which data accounts the largest proportion. Due to different data type and table structures, we will use different methods to calculate the percentage. So, in this article, we will provide you some methods to calculate percentage based on different situations. You can use the formula including COUNTIF and COUNTA functions to calculate percentage, or use SUM function, or insert a PivotTable per your demands. Please continue to read this article, select one proper method to solve your problem.

Method 1: Count the Percentage by COUNTIF and COUNTA Functions in Excel


See the example below. We do a simple statistic for fruit category on weekdays.

If we want to know which fruit is the most common occurs for a week, you can count the percentage for each of them. Let’s follow below steps to learn how to count the percentage.

Step 1: Create a table like below. D column lists all fruit categories. E column lists the percentage for each fruit.

Calculate the Percentage 2

Step 2: In E2, enter the formula =COUNTIF($B$2:$B$11,D2)/COUNTA($B$2:$B$11). In this formula, COUNTIF counts the number for D2 (Apple) in the absolute range $B$2:$B$11, COUNTA counts all cells number in the absolute range $B$2:$B$11. Then use COUNTIF divide COUNTA we can get the percentage.

Calculate the Percentage 3

Step 3: Click Enter to get the result.

Calculate the Percentage 4

Step 4: Drag the fill handle to fill the following cells. Verify that all percentages are displayed.

Calculate the Percentage 5

Notes:

  1. This method is typically used to count the percentage of the times a value appears in a set of data.
  2. This method doesn’t take effective if the data is a number. So, we need another method to calculate the percentage for a number among a batch of numbers in Excel.

Method 2: Calculate the Percentage by SUM Function in Excel


See the example below.

Calculate the Percentage 6

If we use above method 1 to calculate percentage of the sales for product A, we will get an improper value. See screenshot below:

Calculate the Percentage 7

In this case we can get the percentage by ‘Sales of each product/Total Sales’, so we can use a simple formula to calculate the percentage.

Step 1: In E2, enter the formula =B2/SUM($B$2:$B$5).

Calculate the Percentage 8

Step 2: Click Enter to get the result.

Calculate the Percentage 9

Step 3: Drag the fill handle to fill the following cells. Verify that percentage is displayed for each product.

Calculate the Percentage 10

Method 3: Calculate the Percentage by Insert a PivotTable in Excel


See the table below. It is a little more complex than above two tables. Products are duplicated displayed in the list. To calculate the percentage for each product, we can use a PivotTable.

Calculate the Percentage 11

Step 1: Click Insert->Recommended PivotTables.

Calculate the Percentage 12

Step 2: In the pops up Create PivotTable, check on ‘Select a table or range’, click the arrow button and select the range you want to count the percentage on sheet1. In this case, you can select $A$1:$C$17. Then click OK.

Calculate the Percentage 13

Step 3: Verify that Recommended PivotTables window pops up. Click OK here.

Calculate the Percentage 14

Step 4: Verify that PivotTable is created on new sheet2.

Calculate the Percentage 15

Step 5: Double click on ‘Sum of Sales’ to load ‘Value Field Settings’.

Calculate the Percentage 16

Step 6: Click on ‘Show Values As’ tab. In ‘Show Values As’ dropdown list, click the arrow button, and select the third one ‘% of Column Total’.

Calculate the Percentage 17

Step 7: Update the Custom Name to ‘Percentage of Sales’, then click OK.

Calculate the Percentage 18

Step 8: Verify that percentages are calculated properly for all products. You can also directly click on A3 to update the Row Labels per your requirement.

Calculate the Percentage 19

Related Functions


  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel COUNTA function
    The Excel 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],…)…

 

How to Count the Number of Cells with non-zero value in Excel

This post will guide you how to count the number of cells with non-zero value with formula in Excel 2013/2016.

Count the Number of Cells with non-zero value


Assuming that you have a list of data in range A1:C6 which contain text string values. And you want to count the number of cells with non-zero value in your data. You can use a formula based on the COUNTIF function to accomplish the result. The formula is like below:

=COUNTIF(A1:C6,"<>0")

Type this formula into a blank cell and press Enter key on your keyboard, you would get the total number of non-zero values in the given range of cells.

count number of cell with non-zero1

Count the Number of Cells Ignoring Zeros and Blank Cells


The above formula can only be used to count the number of cells ignoring zero values, and if you also want to ignore blank cells at that same time while counting the cells in the given range of cell, you can use another formula based on the COUNTIF function and the COUNTA function. Just like below:

=COUNTA(A1:C6)-COUNTIF(A1:C6,"=0")

Type this formula into a blank cell and press Enter key on your keyboard.

count number of cell with non-zero2

Related Functions


  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel COUNTA function
    The Excel 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],…)…

How to Create Dynamic Named Range in Excel

This post will guide you how to create dynamic named range that can expand automatically when you add a new value into the current range in Excel 2013/2016. How do I create a dynamic named range with a formula in Excel. How to create a dynamic named range by creating a table in Excel.

Assuming that you have created a static named range called “test” in range B1:B4, and you want to insert one cell into this named range and let it expands automatically into this test named range. How to do it. You can refer to the following three approaches to achieve the result.

Creating Dynamic Named Range with OFFSET Function


To create a dynamic named range in your worksheet, you can use a formula based on the OFFSET function in combination with the COUNTA function. Just do the following steps:

#1 select the range B1:B5, go to HOME tab in Excel Ribbon, and click Name Manager command under Defined Names group. And the Name Manager dialog will open.

create dynamic named range1

#2 click New button in the Name Manager dialog box, and the New Name dialog will open.

create dynamic named range2

#3 enter one name into Name text box (such as: test) in the New Name dialog box, click Ok button. Then back to Name Manager dialog box, click Close button.

create dynamic named range3

#4 you would notice that one named range called “test” has been created based on the selected range.

create dynamic named range4

#5 in step 1-4, you have created one static named range, and if you want to change it as dynamic named range, you need to click Name Manager command again. Select test name range in the Name Manager dialog box, and click Edit button, then enter the following formula in the Refers to text box. Click OK button.

=OFFSET($B$1,0,0,COUNTA($B:$B),1)

create dynamic named range5

#6 you can try to add a value to test name range, and it would be expanded automatically into test name range.

Creating Dynamic Named Range with INDEX Function


You can also use another formula based on the INDEX function and the COUNTA function in the Refers to text box to create a dynamic named range in Excel.  Like this:

=$B$1:INDEX($B:$B,COUNTA($B:$B))

When you add a new value in one existing name range in column B, the name range will expand automatically.

Creating Dynamic Named Range by Creating a Table


There is another method to create a dynamic named range in Excel, you can create a table based on the existing name range. Then that name range will be changed as dynamic named range. Just do the following steps:

#1 assuming that you have defined a name range as “text ” in range B1:B5. Select all cells in this name range.

create dynamic named range6

#2 go to INSERT tab, click Table command under Tables group. And the Create Table dialog will appear.

create dynamic named range7

#3 check or uncheck My table has headers in the Create Table dialog box, if your data have header, then check it, or uncheck it. Click OK button.

create dynamic named range8

#4 you can add one value after the data in test name range, new cell should be added into table and also be expanded into name range.

create dynamic named range9

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel COUNTA function
    The Excel 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],…)…

How to Pick a Random Name from a List in Excel

This post will guide you how to pick a random name from a list in Excel. How do I select random value from a list or a table with a formula in Excel 2013/2016.

Pick Random Name from a List


Assuming that you have a list of names in range B1:B6, and you want to pick random names from this list. How to do it. You can use a formula based on the INDEX function, the RANDOMBETWEEN function and the COUNTA function to achieve the result. You can use one of the following formulas:

=INDEX($B$2:$B$6,RANDBETWEEN(1,COUNTA($B$2:$B$6)),1)

Or

=INDEX($B$2:$B$6,RANDBETWEEN(1,ROWS($B$2:$B$6)),1)

Type this formula into a blank cell and press Enter key on your keyboard. And then drag the Fill Handle down to list the random names that you need. And then press F9 key, it will generate the different name list randomly.

pick random names from list1 pick random names from list2

Note: you should know that the name list will be changed when you refresh the current worksheet.

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel COUNTA function
    The Excel 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],…)…
  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

 

Generate All Possible Combinations of Two Lists in Excel

This post will guide you how to generate all possible combinations with a formula in excel. How do I create list with all possible combinations of two lists in Excel. How to list all combinations from two separate list or range in Excel.

Generate All Possible Combinations of Two Lists in Excel


Assuming that you have two list of data in different column, A and B, and you want to get a list of all possible combinations from those two list in Column A and B. How to achieve it.

You can use a formula based on the IF function, the ROW function, the COUNTA function, The INDEX function and the MOD function. Like this:

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

Type this formula into Cell F1, and then drag the AutoFill Handle down column F until you get cells that look empty.

generate combination list1

Note: if you want to use this formula into other cell, you need to change the absolute cell reference to that cell. Such as: form F1 to E1.

=IF(ROW()-ROW($E$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($E$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($E$1),COUNTA(B:B))+1))

generate combination list2

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel IF function
    The Excel 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])….
  • Excel MOD function
    he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
  • Excel INT function
    The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
  • Excel COUNTA function
    The Excel 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],…)…

 

Create a Chart That Updates with New Data Automatically

This post will guide you how to create a chart that updates with new data automatically in Excel. How do I create self-updating chart in Excel. How to get charts to automatically update with new data point in your current worksheet. Or how to use defined names to update a chart with new data point automatically in Excel.

Create a Chart That Updates with New Data Automatically


Assuming that you have a list of data and you have created a column chart based on these data. And you want to add one row or data point in the original data source, and you also want the existing column chart can be updated automatically. How to achieve it. This post will introduce two methods to update chart automatically.

Method1: create a table based on the original source data

You can create a new table based on the source data of the chart, then you can add one row data point under the created table. And you will find that the newly added data point will be updated to the current column chart. Just do the following steps:

#1 select the source data in your worksheet.

create chart update with new data1

#2 go to INSERT tab, click Table command under Tables group. The Create Table dialog will open. If you data has headers and you need to select My table has headers checkbox.  Click OK button.

create chart update with new data2

create chart update with new data3

#3 the table has been created based on the selected range of cells.

#4 add one data point under the table. And the newly added data is added into the current column chart automatically.

create chart update with new data4

Method2: Use Defined Name to Update the Chart Automatically

You can also use the defined name for each column to update the chart with new data point automatically. Just do the following steps:

#1 Go to FORMULAS tab, click Define Name command under Defined Names group to create one defined name for each column.

create chart update with new data8

#2 Enter Product in the Name text box, and type the following formula into the Refers to text box.

=OFFSET($A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

#3 repeat the step 2 to create defined name for Sales column. Type the following formula into the Refers to text box.

=OFFSET($B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

create chart update with new data9

#4 click on the chart ,and the click any data series in your chart. Changing the current formula in the formula bar to the following formula. Then press Enter key.

=SERIES(,Sheet1!Product,Sheet1!Sales,1)

create chart update with new data6

#5 add one new data under the source data. The chart will update automatically for the newly added data point。

create chart update with new data7

Related Functions


  • Excel COUNTA function
    The Excel 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],…)…

How to check if Cell contains one of many values from range

In the previous post, we talked that how to check if cell contains all of values from a list in excel, and this post will guide you how to test a cell if it contains one of several values in a range or a list in excel. How to check that at least one value in a list can be found in another range or a list.

check if Cell contains one of many values from range

Assuming that you have a list of text strings in the range B1:B3 and you want to check each text string if it contains one of several values in a range E1:E3. If it contains any of text string in range E1:E3, then it should be return TRUE value, otherwise, it should return FALSE. You can use a combination of the SUMPRODUCT function, the ISNUMBER function and the SEARCH function to create a new excel formula as follows:

=SUMPRODUCT(--ISNUMBER( SEARCH($E$1:$E$3,B1)))>0

Let’s see how this formula works:

=SEARCH($E$1:$E$3,B1)

The SEACH function returns position of the first character of find_text in a text string. And this formula will search each value in the range E1:E3 inside within_text in Cell B1, then returns position of each text string in Cell B1, so it will return an array result like this:

{1;7;12}

The returned result goes into the ISNUMBER function as its argument.

 

=ISNUMBER(SEARCH($E$1:$E$3,B1))

The ISNUMBER function will check if a cell contains a numeric value, and this formula will check each items of array result returned by the SEACH function, if the item is a numeric value, then return TRUE, otherwise, returns FALSE. So it will return another array result like this:

{TRUE;TRUE;TRUE}

 

=– ISNUMBER(SEARCH(E1:E3,B1))

The double-dash is known as a double unary operator, it can convert the TRUE values to 1 and FALSE values to 0. So this formula returns an array of numbers like this:

{1,1,1}

 

=SUMPRODUCT( — ISNUMBER(SEARCH($E$1:$E$3,B1)))

check cell contains all values1

The SUMPRODUCT returns a total sum of the array result returned by the ISNUMBER function with double-dash operator. So it returns 3.

 

=SUMPRODUCT(–ISNUMBER( SEARCH($E$1:$E$3,B1)))>0

If the result returned by the SUMPRODUCT is greater than the number of items in the range E1:E3, then we can know that at least one value in range E1:E3 can be found in Cell B1. So it returns TRUE, otherwise, this formula returns FALSE.

check cell contain one value1

Then you can drag the fill handle down to the cells that you want to apply this formula to check if a cell contains one of several values in another range E1:E3.

 

And if you want to return “yes” or “no” instead of “TRUE” or “FALSE”, then you can use the above SUMPRODUCT function to combine with the IF function to create a new formula as follows:

=IF(SUMPRODUCT(--ISNUMBER( SEARCH($E$1:$E$3,B1))),"Yes","No")

check cell contain one value2


Related Formulas

Related Functions

  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel COUNTA function
    The Excel 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],…)…
  • Excel IF function
    The Excel 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 syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…

How to Check If Cell Contains All Values from Range

This post explains that how to check if a cell contains all of several values from a range in excel. How to check if cell contains all of many values in a list in excel 2013. How to check if all of values in a list can be found in a cell, if TRUE, then return TRUE, otherwise, returns FALSE.

Check if Cell Contains all values from range

If you want to check if a cell contains all values in a list, you can use a combination of the SEARCH function, the SUMPRODUCT function, the ISNUMBER function and COUNTA function. And you need to create a new formula to count all matches at once, then compare to the total number of all values in list, if equal, we can know that that cell contains all values of list.

For example, assuming that you have a list of text strings in the range B1:B3, and you want to check if the Cell B1 contains all of values in another range E1:E3, you can write down the following formula:

=SUMPRODUCT( -- ISNUMBER(SEARCH($E$1:$E$3,B1)))=COUNTA($E$1:$E$3)

Let’s see how this formula works:

=SEARCH($E$1:$E$3,B1)

The SEACH function returns position of the first character of find_text in a text string. And this formula will search each value from the range E1:E3 inside within_text in Cell B1, then returns position of each text string in Cell B1, so it will return an array result like this:

{1;7;12}

The returned result goes into the ISNUMBER function as its argument.

 

=ISNUMBER(SEARCH($E$1:$E$3,B1))

The ISNUMBER function will check if a cell contains a numeric value, and this formula will check each items of array result returned by the SEACH function, if the item is a numeric value, then return TRUE, otherwise, returns FALSE. So it will return another array result like this:

{TRUE;TRUE;TRUE}

 

=– ISNUMBER(SEARCH(E1:E3,B1))

The double-dash is known as a double unary operator, it can convert the TRUE values to 1 and FALSE values to 0. So this formula returns an array of numbers like this:

{1,1,1}

 

=SUMPRODUCT( — ISNUMBER(SEARCH($E$1:$E$3,B1)))

check cell contains all values1

The SUMPRODUCT returns a total sum of the array result returned by the ISNUMBER function with double-dash operator. So it returns 3.

 

= COUNTA($E$1:$E$3)

check cell contains all values2

This formula returns the number of cells that are not empty in range E1:E3. It returns 3.

 

=SUMPRODUCT( — ISNUMBER(SEARCH($E$1:$E$3,B1)))=COUNTA($E$1:$E$3)

check cell contains all values3

If the result returned by the SUMPRODUCT is equal to the number of items in the range E1:E3, then we can know that all values in range E1:E3 can be found in Cell B1. The number of items of range E1:E3 can be got from the COUNTA function. So it returns TRUE.


Related Formulas

  • Check if Cell contains one of many values from range
    Assuming that you have a list of text strings in the range B1:B3 and you want to check each text string if it contains one of several values in a range E1:E3. If it contains any of text string in range E1:E3, then it should be return TRUE value, otherwise, it should return FALSE…

Related Functions

  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel COUNTA function
    The Excel 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],…)…

How to Reverse a List or Range

This post will guide you how to reverse a list of values or a range of values in excel. How to reverse the order of a column data or a list of items using excel formula. How to reverse a range with sort command. How to reverse a list or column value with VBA.

Reverse a List or Range with Formula

If you want to reverse a list or range, you can use a combination of the INDEX function, the COUNTA function, the ROW function or ROWS function to create a new formula.

For example, to reverse the range A2:A5, you can use the following formula:

=INDEX($A$2:$A$5,COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1)

Let’s see how the above formulas works:

= COUNTA($A$2:$A$5)

reverse a list counta function1

The COUNTA function returns the number of cells in the range A2:A5.  The above COUNTA formula used the absolute reference as its arguments, so it won’t be changed when dragging Fill handle to other single cell.

 

=ROWS($C$2:C2)

reverse a list rows function2

The ROWS function returns the number of rows in the range $C$2:C2. The first Cell is an absolute reference and the second Cell is a relative reference in the argument of the ROWS function. So the second Cell reference can be changed when dragging the Fill handle.

 

= COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1

reverse a list3

The number of Cells in the range ($A$2:$A$5 subtracted the number of rows in the range $C$2:C2, then add 1, it returns the position of the last value in the range A2:A5. And it returns 4.

 

=INDEX($A$2:$A$5,COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1)

reverse a list4

We can use the INDEX function to extract the value based on the position result returned by the above COUNTA-ROWS formula.  So it returns value 403.

Next, we just need to drag the Fill Handler in Cell C1 to other single cells, such as: C2,C3,C4…

reverse a list5

You can also use the below two excel formula to reverse a list or a range:

=INDEX($A$2:$A$5,ROWS(C2:C$5))

reverse a list6

=INDEX($A$2:$A$5, COUNTA($A$2:$A$5) + ROW($A$2:$A$5)-ROW(),1)

reverse a list7

Reverse a List or Range with Sort command

You can also use the Sort command to reverse a list or a range in excel, for example, if you want to reverse the values in the range A2:A5, just refer to the following steps:

1# enter the value 1 into the cell B2, and then fill cells to B5 with a series by using the fill handle.

reverse a list sort command1

2# click any cell in the range B2:B5

3# click “Sort Z to A”command under Data tab.

reverse a list sort command2

4# delete the Column B, you will see that the range A2:A5 is reversed.

reverse a list sort command3

Reverse a List or Range with VBA

You can follow the below steps to create a new excel macro to reverse a list or range in Excel VBA:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3#click “Insert” ->”Module” to create a new module.

How to split text string into an Array with VBA code2

4# paste the below VBA code into the code window. Then clicking “Save” button.

reverse a list with vba8

Sub ReverseRange()
    Dim Rng As Range
    Dim WorkRng As Range
    Dim Arr As Variant
    Dim i As Integer, j As Integer, k As Integer
    On Error Resume Next
    xTitleId = "ReverseColumnValue"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Arr = WorkRng.Formula
    For j = 1 To UBound(Arr, 2)
        k = UBound(Arr, 1)
        For i = 1 To UBound(Arr, 1) / 2
            xTemp = Arr(i, j)
            Arr(i, j) = Arr(k, j)
            Arr(k, j) = xTemp
            k = k - 1
        Next
    Next
    WorkRng.Formula = Arr
End Sub

5# back to the current workbook, then click Macros button under DEVELOPER tab, or Press F5 key to run the above macro.

reverse a list with vba9

6# click “RUN” button, then input the range value A2:A5 that you want to reverse.

reverse a list with vba10

7# let’s see that last result.

reverse a list with vba11


Related Formulas

  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
  • Lookup the Next Largest Value
    If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula.you can use the following formula:=INDEX(A2:A5,MATCH(200,A2:A5)+1)…

Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel COUNTA function
    The Excel COUNTA function counts the number of cells that are not empty in a range. And it returns the number of non-blank cells within a range or value. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)
  • Excel ROWS function
    The Excel ROWS function returns the number of rows in a cell reference.The ROWS function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROWS function is as below:= ROWS(array)…

Excel COUNTA Function

This post will guide you how to use Excel COUNTA function with syntax and examples in Microsoft excel.

Description

The Excel COUNTA function counts the number of cells that are not empty in a range. And it returns the number of non-blank cells within a range or values. It can be used to count all the cells in a range that contain numbers, text, logical values, error values and empty text string.

The COUNTA function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.

The COUNTA function is available in Excel 2016, excel 2013, excel 2010, excel 2007, excel 2003, excel XP, excel 2000, excel 2011 for Mac.

Syntax

The syntax of the COUNTA function is as below:

= COUNTA(value1, [value2],…)

Where the COUNTA function arguments are:

  • value1 -This is a required argument.  The first item, cell reference, or range within which you want to count numbers.
  • Value2 – This is an optional argument. You can enter up to 255 value arguments.

Note:

  • The COUNTA function does not count empty cells.
  • If you want to only count cells that contain numbers, just use the COUNT function.
  • If you want to count logical values, text, or error values, you can use the COUNTA function.
  • You can provide up to 255 number arguments in excel 2007 or later. If you are using excel 2003, you can only provide up to 30 number arguments.

Excel COUNTA Function Examples

The below examples will show you how to use Excel COUNTA Function to count the number of cells in a range that are not empty.

#1 To count the number of cells in the range B1:B4, just using the following excel formula:

=COUNTA(B1:B4)

excel counta function example1

More Excel CountA Function Examples


  • Generate All Possible Combinations of Two Lists
    You can use a formula based on the IF function, the ROW function, the COUNTA function, The INDEX function and the MOD function to get a list of all possible combinations from those two list….