 Hello everyone, welcome back to another tutorial. In this video, we're going to be looking at pivot tables once again, but this time ensuring that we can make sure that any new data that's added to our data set is automatically fed through to our pivot table. So what do I mean by that? Well, if we go to a pivot table and I'm using a sheet that we recently used in another tutorial, so if it does look familiar, we're basically just recycling that same file. If I was to go into this pivot table and we were to go into a pivot table analysis and go into change data source, you can see at the moment we've got a very affixed range of which where we're sourcing our data from. So as long as our data obviously stays the same, we can keep refreshing our pivot table and it'll be fine. But if we were to enter some data into row 45 of our data set, so let me just jump across to that. So let's say, and again, just try and try and make up some dates here. So let's say the 22nd of the 12th, 2019, and we'll call this one central rep. We'll go with Tim item, say it's done a desk, one unit. That cost maybe, I don't know, 4.99 and total sale was 4.99. Obviously you added some more values in there. So if we are now to go into our pivot table and again, look at that range I was just on about. So you can see we've added some new data to this rate or to our data set or sales data. But our pivot table is still only going to be looking up to this row 44. It's not going to pick up that new value. The only way for our pivot table to get that new value is for us to again re-manually select this range or obviously update to go into row 45 here. And if I do okay there, you can see that our table update and we didn't look at the numbers before, but I think central was on 24, but it's now gone up to 25. But the most important thing we notice is that if we go into our change data source one more time, we can see that again, it's now putting all that updated information for us. Well, a really quick way to make sure that the data is always going to be picked up is to simply convert the data or sales data into a table. By creating into a table, it'll be defined as a range. And then what we can then do is have our pivot table always look at just that table as a whole. So any values that are added or removed will automatically get pulled into our pivot table. To do that is really simple. All we need to do is select our entire data as we've done here, go to our insert tab. And just below that you'll see there's option of table. So it's going to select that table option here. And you can see it's just getting us to validate our information. So where is the data for your table? It's going from row one of column A all the way through to column G row 45. That's correct. And yes, my table has headers. So we can just take that to make sure it knows that the first row of that data row number one is the headers for our data. And then just click OK. And you can see that it's now changed the formatting slightly, but we can actually see that it's now a table. And there are some different formats you can play around with this table. So if you wanted to be a different, I know had a particular preference on what color you wanted to be, you can obviously have a player around here. But obviously I was going to leave it as the default for this because it's not important to us at this moment. So having got this selected, you can see or tell us our table name is table number one. You can see obviously by having selected, we've got this additional tab here for table. If I click away, that tab is now gone. But in order to get it back, we've got to just click any cell within your table range. And you can remind yourself of what the table name is and also all the other information as well. What I'm going to do is actually just rename this. So we'll call our table sales data and hit enter. And that's now been saved for us. So again, if I click away, click back in, we can see that that value has been saved. So it's saying sales data. Now we can go back into our pivot table. So going to our pivot summary. And this time, I was going to go into pivot table analysis and change source. And what I'm going to do is just go into select our table. And what we can do is we can either as you have already done that bit too quick, you can either go into obviously the first row of your data and hold down control and shift and do your right arrows to go to your last column and then down arrows go to the last row. Or and you can see by doing that is picked up that this range is obviously a table. So it's our sales data. Or what you could do is just type in here sales data, the name of the table, whatever one is easiest for you to do. Once you've done that, if you just click OK, you can see it doesn't look like anything's changed. But if we're to go into and what I'll just do this time is we'll write down these numbers just so we can see what does change. So and six. Well, so as long going off. So if we go, I've got those numbers here, so we can reference these ones we've updated. And let's go into sales data and let's add a new row. So we've got a 23rd of the 12 2019. And you can see just by tabbing along this automatically added a new row into our table. So our range is already getting updated. So our table is growing. Let's do central again. Let's do John, let's do a binder. Let's say we did one unit of again, something at 699, all made up numbers. Also some tab again so it's updated our table. We don't need that one. So we can just delete this row here. Table and we go to delete table rows. There you go. So it's removed that one. So we can anyway, we can see that a new value of added has been updated into the table. So we'll go back to our pivot summary. We know these are the values that were in here. So we can see them here as well 25 13 six. Let's just refresh this. And you can see that the pivot table has now picked up, obviously are updated table. So the main thing here is the table is going to continuously update as you add rows to it. And the pivot table is then going to obviously pick up those new values. So this is a really quick and simple way of how you can combine or convert your data into a table. And then obviously reference that table in your pivot table to ensure that every time you refresh your pivot table, it's going to be updated with the new information. And it's worth just going back through this. So let's just remove our last row here we've added. So we go into delete having right click and go delete table rows, go into our pivot summary summary, refresh, and you can see it works the other way. So obviously if rows removed, it's going to remove that from our pivot table as well. So I hope we found that useful. It's a really important one to touch on and I should touch on this early really is often when working with pivot tables, one of the simplest thing people forget to do is to update their range. What can obviously cause a lot of problems if you're adding new data all the time, you go to refresh your pivot table, and then you're wondering why the new data hasn't come across. So this is a one to sort of really sort of tick the box and make sure you understand and probably start implementing this one to avoid yourself any of those embarrassing errors or any problems down the line. So if you did enjoy that video, please do give it a like it's greatly appreciated and obviously helps identify the videos and the content you want to see more of. But it also really does help the channel in terms of that YouTube algorithm. And lastly, if you haven't already, do subscribe to the channel. Just hit that bell notification button as well so you're notified of all of our new videos as they come out. And that again will be greatly appreciated. So thank you very much and we shall see you in the next video. Before you go, don't forget to check out the other videos on our channel. You'll see everything from other functions and formulas through to tips and tricks. We've also created some playlist so you can see these categorized together. So make sure you check those out and get all those useful information. And obviously, as always, don't forget to subscribe and hit that bell notification button.