Hello, everybody, welcome back to the dashboard course. In this video, we will learn how to convert static data in Excel into dynamic data that can understand itself when data is being expanded by a column or via a row. So let's see how it works. As we all know currently if I make a pivot table with this particular data, and in the future if a new field is added, it is not going to help me as I have to either expand the range or I may have to do the entire pivot table once again, how the same is applicable. Even for road data is in the current example we have 1884 line items rather 1883 line items as the first row is the heading. Now if in the future, there are another 10,000 line items added at the bottom, I have to recreate the data for the to pivot or maybe expand the range, most of us have a habit of selecting the whole column from A to G, or a to z, or whatever the number of columns we have, and then make a pivot table from this.

Now that practice is not good as the pivot table has to go through even the blank rows that we have selected, even though there is no data in the cell that is represented in the pivot table as blank. So we are not using the pivot table to its optimum level. Now, what can we do for that there are multiple ways of which the best way is convert this static data into dynamic data. It's like going from 2d flat data to 3d dynamic data. And let's see how that works. To do so, first place the cursor anywhere in the data condition or data should have a heading and there should not be any connected Data after the data which is not relevant to it, if there is you can add one blank row or column between both of them.

And that will separate the data from the chart which is not needed. Now, once done from the Insert tab, there's an option here that is table and the shortcut for that is Ctrl T as we can see in the toolkit. Now let's use the shortcut. Keep the cursor inside the data Ctrl T and we see the box. The box suggests us create a table with a reference or the range from a one to G 1800 84. Now we do have a heading.

So by default this particular option is turned on if it is not done or it is off. It will provide us a heading which we can change in the future. Or else it will just use the existing first row as the heading. Just click on OK to finalize. As we can see on the screen there are some lines in color and We have filters. Now these are all just complimentary to what is actual.

Now if you just keep the cursor inside and scroll down, you'll see the heading is actually visible even though the data is scrolling, so we don't have to freeze the row. Now that's the first benefit. The second benefit is we have the filter as a compliment. So we do not have to additionally add a filter. So and the last, the core important part is that we can change the formatting without any trouble. Though the default formatting for this particular table is pretty good to begin with.

So since we don't require formatting, I will simply go to the drop down and change the formatting back to normal. Don't worry, it will still be dynamic data. So how do I know that if you look here in the menu bar, when your cursor is in the inside of the table, a tab will appear and if the cursor is out, it will vanish. The tab name is Table Tools design. And now in this particular tab, all the features that are used to control this particular table. Now let's see the benefit of the table.

So some of the benefits have been explained already major benefit is if in the future if I add a new column just by adding a heading, the data automatically expands. And the same rule applies for the row. Now, let me give you an example. In the previous video, we learned of our formula called text. And yes, that's to convert a date into a date format. Okay.

Now the dynamic tables provides the benefit whereby if a new field is added after an existing field, which is connected to the column, or row, it will be connected and a table expands itself. Let's see a small example of this. Like we do have some in US dollars, say The company also distributes a bonus. I'm just using a hypothetical example here. So let's go with bonus. And as soon as I press Enter automatically, the bonus is also included in the data.

And we can see the filters activated there. Another feature is when we put in a formula like equals, and we select any of the values from the table, it displays which field has been selected. Rather than giving us the cell address, it provides the fields name, and that can help you understand what data source has been selected. And that can help reduce the number of errors. I don't have to change this so just keep it as it is just simply put a multiply and say hypothetically, my company gives a 90% bonus. Just press Enter.

And there it is. calculation for the relevant data has been done for me, so I don't have to drag it. And you can see that this data is expanding automatically. Now if in the future if there is a new record added to the bottom, say for example, ID number 1933 US dollars, so I'll just put in the salary to show you an example. As we can see, the last cell has already been calculated. And if I put in the salary, the calculation is done for me directly.

So the only field that I have to enter is over to experience. Now if the data has to be removed, don't simply select the row and delete the data. Rather delete the entire row by using Ctrl minus or by right clicking on the row and deleting it. That's the best way to delete the data because the table when it's expanded, does not reduce its size directly. But when the row is deleted, the size automatically gets reduced. So that's how we convert static to D flat.

Data into a dynamic database. In the next video, we will see how to name this data and how to use it to create multiple pivots till then keep practicing and I'll see you in the next video. Thank you