Auto adjusting serial numbers - Excel Tips and Tricks - video Dailymotion

RabiGurungXybernetics

Learn how to auto adjust serial number in Excel. At the same time you will learn how to autofill serial numbers or auto arrange serial numbers in Excel. Technically, how to automatically feel serial number in excel without dragging or fixable number in excel? Also learn how do I use dynamic numbering in Excel?   These are the hotkeys featured in this video.   Apply Filter Hotkey  Ctrl + Shift + L   Hide row  Ctrl + 9    These are the formulas used on the video.   Formula  =SUBTOTAL(3,$B$2:B2)   Formula  =SUBTOTAL(103,B$2:$B2)    Here's a breakdown of the formula for  =SUBTOTAL(3,$B$2:B2)   - "SUBTOTAL" calculates a subtotal based on the specified function number.  - "103" is the function number for the SUM function (COUNTA). In this case, the formula will calculate the sum of the values in the range. In another words, the "COUNTA" function in Excel is used to count the number of cells in a range that are not empty. It can count both numeric values and text values.  - "$B$2:B2" is the range of cells over which the subtotal will be calculated. The dollar signs ($) before the column and row references make them absolute references, which means the range won't change when the formula is copied to other cells horizontally.  - The "B2" part represents the current cell reference. As the formula is copied down to other cells, this part will change accordingly to match the row of each respective cell.    Here's a breakdown of the formula for  =SUBTOTAL(103,B$2:$B2)   - "SUBTOTAL" calculates a subtotal for a given range based on a specified function.  - "103" represents the function code for COUNTA. In this case, the formula will use the COUNTA function to count the number of non-empty cells in the range.  - "B$2:$B2" defines the range of cells over which the function will be applied. It starts from cell B2 and goes up to the current row, denoted by "B2" with a relative reference.  - The range is dynamic because it uses a mixed reference. The column reference, "$B," is absolute because the dollar sign "$" is used before it, meaning it won't change when the formula is copied horizontally. The row reference, "2," is relative, without a dollar sign, so it will adjust as the formula is copied vertically to different rows.    Link to PART 2 of my video.  Auto adjusting serial numbers... Another method - Excel Tips and Tricks  https://youtube.com/shorts/cX6Xr8zIoAU?feature=share    How do I autofill my serial number?,How do I auto arrange serial numbers in Excel?,How to automatically fill serial number in Excel without dragging?,How do I fix serial number in Excel?,

Transcript

00:00If you need to automatically generate sequential serial number or index number for your rows like

00:04this, you have two distinct methods at your disposal using countA function. All these methods

00:10work regardless of whether you delete, hide rows, and or apply filter. I'm going to introduce a

00:15filter by pressing Ctrl Shift L to start off with. And you're going to place your cursor at cell A2.

00:21For the first method, you're going to use subtotal. And the first argument on subtotal will be

00:26number three, which is function number three. Click here, double click, or you can put three here if

00:31you like, comma, B2. You're going to press F4 once to make B2 an absolute reference. And then say colon

00:39B2 again. So basically, I'm counting each row, making the first one as a reference. So as it

00:45increases, the number naturally increases in sequential order. If you hit enter, and I'm going

00:51to apply to the rest of the rows by double clicking on this handler here. And you can see all of them

00:57has a formula. Now if I were to apply filter, like for example, I changed my favorite color to only red.

01:03Notice how the index or the serial number adjusts by itself. And if you apply, say, highlight the row

01:10and press Ctrl 9 to hide the row, you can see that the number automatically adjusts, even if you hide the

01:16row. I'm going to undo twice here. We can reorganize the subtotal formula to use function number 103.

01:24But you have to change your cell reference. And you're going to make your row fix. So basically a

01:31counting row. And on this side here, you're going to fix your columns. And you go ahead, enter. And you're

01:35going to apply the same formula to the rest of the row like this. As you can see, that the row index is

01:42changing on the second one range here. So now if you were to apply filter again, say blue this time

01:49around, and then you delete couple of rows or hide, Ctrl 9 to hide it, you can see that the index number

01:54are still in sequential order. The second method of having a sequential index number or serial number

02:02would be to use an aggregate function. So this is how I use an equal aggregate. And the first argument

02:09will be count A here as well. And then comma, five to ignore hidden rows, comma, and then you're going

02:20to use B2 as a reference. I'm going to make it an absolute reference by pressing F4 once like this,

02:28similar to the very first one that we did with subtotal function name number three, colon B2 like this.

02:36And I'm going to close parenthesis and hit enter. I'm going to apply the same formula to the rest of

02:41the row. And you can see that the aggregate function has been applied. Now if you were to apply filter

02:46here, let's say this time around, let's go with only zones here, say north and south. And then you try to

02:52hide a few rows or maybe quite a few of them. Ctrl 9 to hide it, you can see that your index or your serial

03:00number are still in sequential order. Those are the two ways to get sequential serial number.

03:06and then you can see that you'll see that it's before the row. That's the two ways to do it.

03:08You can see that there is now a couple of rows. I'll take that as well.

03:09I'll take that as well. I have to paste it from the other side.

03:10So this is the second row. I have to communicate.

03:12I'll switch this through the row.

03:14So I'm going to close the groove, which is the key.

03:16So I'm going to give you a little bit of a second row.

03:16And you can see that for the year before I see the top row.

03:19So I'm going to close the row.

03:20And if you go ahead and look at the bottom row, take the row across the row.

03:23And if you apply the row.

03:25So I'm going to close the row.

03:27And if you go ahead and look at the row.

Auto adjusting serial numbers - Excel Tips and Tricks

Category

Transcript

Recommended