{"id":8417,"date":"2021-11-17T14:22:12","date_gmt":"2021-11-17T14:22:12","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=8417"},"modified":"2024-03-28T11:46:44","modified_gmt":"2024-03-28T11:46:44","slug":"number-filtered-cells","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/number-filtered-cells\/","title":{"rendered":"Numbering Filtered Cells"},"content":{"rendered":"\n
We have already explained how to number our rows in Excel in various ways, and how to format these numbers.<\/p>\n\n\n\n
In the example below, we will show how to number the rows that are filtered in Excel.<\/p>\n\n\n\n
For our example, we will use the table with NBA players, and their statistics from several categories: points, rebounds, assists, and turnovers:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will create another row that will be the new column A<\/strong>, and we will input the ordering numbers for our players:<\/p>\n\n\n\n<\/figure>\n\n\n\nIf we now filter only the players from the Eastern conference<\/strong>, we will notice that these numbers will not change, i.e. that we do not have the new order:<\/p>\n\n\n\n<\/figure>\n\n\n\nTo make this work, we need to use the SUBTOTAL formula<\/strong>.<\/p>\n\n\n\nWe will now remove the filter and delete the numbers in column A<\/strong>. In cell A2<\/strong> we will input number 1<\/strong> and in cell A3<\/strong> we will input the following formula:<\/p>\n\n\n\n=SUBTOTAL(3,B$2:B2)+1<\/code><\/pre>\n\n\n\nThe SUBTOTAL function<\/strong> allows us to create groups and after that to perform various functions, such as SUM, COUNT, MAX, <\/strong>etc.<\/p>\n\n\n\nWhen we create it, we first need to choose the function (by the number) among many:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will choose the COUNTA function<\/strong> for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column B<\/strong>– cell B2<\/strong>, which will be locked, and cell B2<\/strong> (which will be changed when dragged). We will then add the number 1<\/strong> to this number.<\/p>\n\n\n\nResults in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
We will create another row that will be the new column A<\/strong>, and we will input the ordering numbers for our players:<\/p>\n\n\n\n<\/figure>\n\n\n\nIf we now filter only the players from the Eastern conference<\/strong>, we will notice that these numbers will not change, i.e. that we do not have the new order:<\/p>\n\n\n\n<\/figure>\n\n\n\nTo make this work, we need to use the SUBTOTAL formula<\/strong>.<\/p>\n\n\n\nWe will now remove the filter and delete the numbers in column A<\/strong>. In cell A2<\/strong> we will input number 1<\/strong> and in cell A3<\/strong> we will input the following formula:<\/p>\n\n\n\n=SUBTOTAL(3,B$2:B2)+1<\/code><\/pre>\n\n\n\nThe SUBTOTAL function<\/strong> allows us to create groups and after that to perform various functions, such as SUM, COUNT, MAX, <\/strong>etc.<\/p>\n\n\n\nWhen we create it, we first need to choose the function (by the number) among many:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will choose the COUNTA function<\/strong> for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column B<\/strong>– cell B2<\/strong>, which will be locked, and cell B2<\/strong> (which will be changed when dragged). We will then add the number 1<\/strong> to this number.<\/p>\n\n\n\nResults in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
If we now filter only the players from the Eastern conference<\/strong>, we will notice that these numbers will not change, i.e. that we do not have the new order:<\/p>\n\n\n\n<\/figure>\n\n\n\nTo make this work, we need to use the SUBTOTAL formula<\/strong>.<\/p>\n\n\n\nWe will now remove the filter and delete the numbers in column A<\/strong>. In cell A2<\/strong> we will input number 1<\/strong> and in cell A3<\/strong> we will input the following formula:<\/p>\n\n\n\n=SUBTOTAL(3,B$2:B2)+1<\/code><\/pre>\n\n\n\nThe SUBTOTAL function<\/strong> allows us to create groups and after that to perform various functions, such as SUM, COUNT, MAX, <\/strong>etc.<\/p>\n\n\n\nWhen we create it, we first need to choose the function (by the number) among many:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will choose the COUNTA function<\/strong> for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column B<\/strong>– cell B2<\/strong>, which will be locked, and cell B2<\/strong> (which will be changed when dragged). We will then add the number 1<\/strong> to this number.<\/p>\n\n\n\nResults in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
To make this work, we need to use the SUBTOTAL formula<\/strong>.<\/p>\n\n\n\nWe will now remove the filter and delete the numbers in column A<\/strong>. In cell A2<\/strong> we will input number 1<\/strong> and in cell A3<\/strong> we will input the following formula:<\/p>\n\n\n\n=SUBTOTAL(3,B$2:B2)+1<\/code><\/pre>\n\n\n\nThe SUBTOTAL function<\/strong> allows us to create groups and after that to perform various functions, such as SUM, COUNT, MAX, <\/strong>etc.<\/p>\n\n\n\nWhen we create it, we first need to choose the function (by the number) among many:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will choose the COUNTA function<\/strong> for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column B<\/strong>– cell B2<\/strong>, which will be locked, and cell B2<\/strong> (which will be changed when dragged). We will then add the number 1<\/strong> to this number.<\/p>\n\n\n\nResults in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
We will now remove the filter and delete the numbers in column A<\/strong>. In cell A2<\/strong> we will input number 1<\/strong> and in cell A3<\/strong> we will input the following formula:<\/p>\n\n\n\n=SUBTOTAL(3,B$2:B2)+1<\/code><\/pre>\n\n\n\nThe SUBTOTAL function<\/strong> allows us to create groups and after that to perform various functions, such as SUM, COUNT, MAX, <\/strong>etc.<\/p>\n\n\n\nWhen we create it, we first need to choose the function (by the number) among many:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will choose the COUNTA function<\/strong> for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column B<\/strong>– cell B2<\/strong>, which will be locked, and cell B2<\/strong> (which will be changed when dragged). We will then add the number 1<\/strong> to this number.<\/p>\n\n\n\nResults in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
=SUBTOTAL(3,B$2:B2)+1<\/code><\/pre>\n\n\n\nThe SUBTOTAL function<\/strong> allows us to create groups and after that to perform various functions, such as SUM, COUNT, MAX, <\/strong>etc.<\/p>\n\n\n\nWhen we create it, we first need to choose the function (by the number) among many:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will choose the COUNTA function<\/strong> for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column B<\/strong>– cell B2<\/strong>, which will be locked, and cell B2<\/strong> (which will be changed when dragged). We will then add the number 1<\/strong> to this number.<\/p>\n\n\n\nResults in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
The SUBTOTAL function<\/strong> allows us to create groups and after that to perform various functions, such as SUM, COUNT, MAX, <\/strong>etc.<\/p>\n\n\n\nWhen we create it, we first need to choose the function (by the number) among many:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will choose the COUNTA function<\/strong> for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column B<\/strong>– cell B2<\/strong>, which will be locked, and cell B2<\/strong> (which will be changed when dragged). We will then add the number 1<\/strong> to this number.<\/p>\n\n\n\nResults in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
When we create it, we first need to choose the function (by the number) among many:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will choose the COUNTA function<\/strong> for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column B<\/strong>– cell B2<\/strong>, which will be locked, and cell B2<\/strong> (which will be changed when dragged). We will then add the number 1<\/strong> to this number.<\/p>\n\n\n\nResults in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
We will choose the COUNTA function<\/strong> for our needs. Then we need to define the reference cell. In our case, it will be the range that is the combination of the first cell in column B<\/strong>– cell B2<\/strong>, which will be locked, and cell B2<\/strong> (which will be changed when dragged). We will then add the number 1<\/strong> to this number.<\/p>\n\n\n\nResults in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Results in cell A3<\/strong> will be number 2 (1+1),<\/strong> in cell A4 3 (1+2)<\/strong>, etc.<\/p>\n\n\n\nWe will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
We will drag this formula till the end of our list:<\/p>\n\n\n\n<\/figure>\n\n\n\nWe will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
We will then filter only Eastern conferences in our table:<\/p>\n\n\n\n<\/figure>\n\n\n\nYou can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
You can notice that although Kyrie Irving was numbered as player number 6<\/strong> in the original table, he is now second on our list, and thus has the number 2<\/strong> by his name.<\/p>\n\n\n\nThis functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
This functions on a basis that the SUBTOTAL formula<\/strong> only observes the filtered data in our list, and it changed accordingly.<\/p>\n","protected":false},"excerpt":{"rendered":"We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nNumbering Filtered Cells<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
We have already explained how to number our rows in Excel in various ways, and how to format these numbers. In the example…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\n