Hello, everybody, welcome to the second dashboard. In this video we will see how to use offset in Excel. Here we have IDs and salaries. So we want to total the salaries in such a way that if data is added further down, the total salary should also be summed in this particular block. Let's see first, let's see, we need to start here. So I'll say offset.

I'll keep the cursor on a one because in the previous example, we had selected a one as a prior cell. So now how many rows Do I have to move down so excluding the first block, it's 1234, I had to come down for sale. So I'll say four. Since I'm starting at sell a one, the offset locates, so a five. So from a five to get to be five I had to jump right one cell. That's the number of columns I need to jump.

So column one. Next is the height of the data that I'm trying to select. Currently, I just get this cell. So we cannot have zero as a parameter of any type. So the minimum parameter for height should be one, the width will also be one for now, current example, I close the bracket and enter. So there it is.

And now if I put in the height one and width is one, but rather than increase the row number to five, so it will move down one cell. So, Isn't that fascinating. So that's how it is dynamic. Now, how do we get set this range? It's fairly simple. You just have to input the height and width.

So currently, the width will always be one column. But the height might increase with additional data. So if I count manually, it is six, so I'll put six. So now it's not giving me an answer, because a range can not be stored in a cell. But a formula can use the range to get some answers. So I'll use the sum formula For that matter, I can use any formula.

Now once I'm done, close the bracket. So what I'm doing is I'm writing offset inside the sum formula, which in turn gives me this particular range, which will give me the result. So that is the total of that given range. So I press enter, and there it is. So I have a total number being provided over here, there's a total of the entire given range. Now what if a new entry has been added, say I add a new ID with additional 10,000 rupees.

Normally, it should become 5000. Over here, it does not happen because I have to manually increase the range rather than six I have to make it seven. Now I want to automate this so I'll be using the count formula. So I'll be using count. The reason I'm using count is because I'm selecting the numbers. If it was an alphanumeric character, I would have used count a.

So count from here. I'll be selecting That is where the selection has started in the offset itself, that I'm reaching at this particular number. So I'll be selecting the same reference. And from there Ctrl Shift down to the extreme bottom of the sheet, as I'm not going to drag this particular formula, so I'll keep it as it is, or it is planned to be dragged, then we have to press f4 to freeze this particular reference. So once done here is the answer. Now if in the future, you add up a new record, say within a oddish addition of 5000.

Now according to that, it should become automatically 40. So there it is. And Isn't that fascinating. And the best part is even if I delete a record, the total also gets altered automatically. So that's offset how offset works in the Excel function. In the next video, we will see how to implement the offset in the dashboard that we have created.

Till then keep practicing. See you in the next video. Thank you