 Hello everyone and welcome back to another video. In this video we're going to be looking at the benefits of using tables when you're collating your data and I'm going to focus on two specific topics, well three I guess in total. So what we have as you can see our basic table here already set up with some what five headers there and the purpose of this is we want to circulate this information around maybe colleagues or other people for them to populate this with the information that we require. In order to do so we have a couple of dropdown lists we'd like to include so for status you can see column F here we've got a dropdown so we want one of these three options to be available to us in the dropdown and like so we've assigned two we're going to go one of these contacts again that would be another dropdown and the third thing we're going to look at well I guess there's they're both dropdown so it's the same thing really and the third we want to do is include some conditional formatting for the status matching exactly as you can see here. It's worth mentioning if you're going to be using your dropdowns you might want to well actually I'll touch on that as soon as we get into it and that'll probably become more clear then. So I'd say the purpose of this is to be able to send share this around with some colleagues so that they can populate this and share the information back to us. So what we have by starting we've got this basic table here what is there's not a table it's just basic it looks made to look like a table should we say at the moment it's someone to populate this and we had the information to put in we put in a date maybe say the 1st of April you could say the incident was a trip and I'm just I've used the word incident but I'm literally just making this up as I go along just to give an example let's just move as I said say the location was in London and obviously we've got no dropdowns so we'd have to do ground clear and status could be pending so you've got to type everything in there to get the information in if I now want to add a dropdown for the assigned to obviously we know how to do dropdowns let's say I want to select four rows we're going to data we're going to validation we missed it there go into a list and then I can just select my source as being all these assigned to contacts like that so quite simple to put that dropdown list in there and if you want to go in a bit more detail into drop down this you'll see the video on our channel I'll try and put a link to it on the screen now but if you search our channel you'll soon be able to find it so this is great we've got a dropdown list that allows us to select the individual required and likewise for status we can soon do exactly the same so set the same four rows and go to data validation list source and it's going to be this status here so that works all for well for us the problem we have with this is if we were to populate some information and I'm just going to copy all of this we've got here and let's say we've got that all random information so obviously no no value in this detail populate here at all so that's all great for with our dropdowns we've done there but as soon as we get down to this row 11 we can see our dropdowns are no longer available and that's because obviously when you put the dropdown in there you have to define the number of rows or the range that you want that dropdown to be applied to alternatively what you could do when selecting your dropdown or adding your data validation is you could select the whole column what would mean the whole column would then be populated with these dropdowns so we can just see what that looks like here so let's just do that yeah select that range and go okay so obviously now if you want to do this dropdown you can see it's it's literally everywhere in that column what is an ideal and it doesn't really look too professional if you're recirculating this form with people so avoid that one so let's just clear all these so your other option is you have to select a larger range that you know people might populate up to and a way you could do that is you might want to use some formatting and just to say okay for this area here I'm not going to have any background or no fill oh actually it's changed that let's just say everyone has some grid lines on here so border let's do a light thing here I'm being way too detailed with this so I don't need to be so you can just see that I'll put hopefully you can see that on the screen I'll just put some cell borders on here so you could say your predefined area base of validation list sorry I must be getting really boring but you're probably getting very used to how to do this if you weren't familiar with data validation before so by doing this defined range you can see it kind of looks clear that if you move out of this area then the formatting is not going to be there but still it's not ideal and as people are populating this it's you don't want to be going backwards and forwards or trying to explain to people how to incorporate these drop downs alternatively it's just it's just not professional and not how we really want to work with it so the better solution to this and I've used that example for the assigned to column e column it applies exactly the same for the status drop down and lastly the conditional formatting aspect of this works exactly the same so again if you're not familiar with conditional formatting of how you can have particular cells automatically change that color as you can see here so automatically pending would go to orange approved go to green you can find that video on our channel as well once again I'll try and link that on the video screen somewhere now but if not let's say just go to our channel you'll soon be able to find it on there so if you're just not formatting again you need to select or predefine the range that you want that formatting to apply to with data that's going to be growing and ever changing again it's not ideal again so the solution I have for it is to start using tables and if you're not familiar with using tables I'll do a quick sort of introduction on basically how you insert a table and how you can format it but there's not really too much you really need to cover the main importance of this is just showing you how using drop downs and additional formatting with tables is a really good and slick way of working when it comes to like obviously populating information or especially when you'll be sharing that file for it to be grown by more and more people adding their information to it so what we'll do is we'll just remove this formatting that I've got here or we can simply do just go no borders remove the information I've got here and I think there's some drop downs there so let's just quickly remove those and go clear oh cool so we've got a complete fresh what I will do is I will just add manually add this in again well no I won't manually add an order and I'll just put some starting information the first of April 2020 incident was a trip and the location was in London okay so you can see at the moment and the clear giveaway that there's not a table here is pretty much the formatting that you'll see in order to insert a table or convert your existing information which is this one row here into a table all we need to do is select your header row and your rows of data and you could have more rows of data here if applicable but I always find personally it's helpful to put start doing your first row and then you can easily see where you need to enter information from having said that what I will do is just go back and what I've just done remove this row just so you can see what it looks like so let's just select this first header row here and we're going to insert select table and you can see it's highlighted our range there and we've got this little pop up here and it's just identifying to us this this is the row of information that we currently have selected just need to select this tick box here to say that your table has headers or the only row I'll set to the moment is headers and what that will do is we'll just say okay the first row of this range is your header row select okay and you can see via formatting we have now got a table inserted here for us I will stick with that formatting for now just because it makes it nice and clear on the page but I'll show you how you can either remove the formatting or completely or how you can change it to suit what your preference is so what we can now do when it comes to populating our status and our contacts is we can actually assign it to a column within this in this table and in order to do so all we need to do is if you hover above the column what you are assigned for so for me it's assigned to just above the header you'll see the arrow goes to this black down arrow so you can also see how it's normally and just as you get to that top line there you'll go to that if you select that what it's actually done even though it's only selected one cell for me here is it's actually selected the whole column but all I need to do is go into data data validation and I can go into list select my assigned to list there's those there and go okay and you can see there's now a drop down available to me there as required likewise if we go to status and do that black arrow again this time go data validation and this time go list source is status okay you can see we've also now got a status column there as well perfect and if I was to click above the table obviously there's no drop down there and if I was to click below the table again you'll see there is no drop down available to us so we'll start populating we'll go to 1st April 2020 again the incident was a trip location was in London and it's assigned to let's say Jill Peters and the status is pending perfect so that's given us what we need but I now want to add another row of information so now I go into 2nd of April 2020 this time it was and you can see upon entering a new piece of information on the road below it's Excel's already identified that our table is now grown and you can see slightly different formatting it's now white line but you can see this bottom corner the blue bit there has extended down to show you that our table now contains two rows of information so it's at a trip we're going to call it a four and let's say this is in Edinburgh and let's assign up and I'll just now jump across without even thinking you can see that our because our table has now extended so has these parameters so we're now going to drop down available to us here as well so Tim Smith and also the status we can see is there really for us as well so the real benefit and what I'm getting around about way is by signing your drop downs to a particular column within a table it just means that those drop downs are only going to be visible for applicable data and by that I mean obviously if you put data anything down here there's not going to be any drop downs the drop downs will only appear within your desired column so that is the crux of obviously how to use the drop downs when it comes to using the conditional formatting it works exactly the same except for obviously programming dealing with conditional formatting so go to my home tab I'm just going to select the status column by getting the black arrow there I'm going to go to conditional formatting I'm then going to go to a new rule then let's go only for so the contains specific text containing and let's say my first one is pending I want that to go to orange okay and then I can do you can see it's already updated there now there's going to new rule there's only cells that contain specific text containing how many ones are now approved and this one was green okay and then the last one was going to be cancelled so specific text containing cancelled and that was red okay so we're just now applied our conditional formatting although I went for that really quickly you can watch the video back again just to see how I was doing it alternatively currently if I mentioned it for conditional formatting already but again you'll see there's videos on our channel going over in more detail how to work with conditional formatting so we can now see if we were to change any one of these statuses so that one goes to cancelled the conditional formatting is automatic on the update for us and once again if I was to type cancelled below the table it's not going to do anything because the conditional formatting only applies to the column within our table and we'll just add one more piece of information 2020 and what could the incident be here trip form I don't know go trip again for lack of lack of ideas just so it's in Bristol I'm just putting sit random cities on top of my head here in the UK signed to Joe Wright and let's say this one is pending just so we've got all of them you can see it's now updated there as well so this is a really useful way of collating data and ensuring that your drop downs are obviously being enforced where applicable and it just keeps everything tidy as well and together and alternatively if you want to when you're working with tables if you do need to remove any rows all you simply need to do is right click the row go down to delete lead table rows and it's going to update that for you and obviously remove any drop downs and conditional formatting there as well so it just gives you the flexibility when working with the information as well I did say I was going to quickly show you how to deal with formatting in the table so as long as you're selected within the table you'll notice you get this new tab at the top here called table design if you select table design and then you can see you've got some table styles over the side here so what you can do is do this drop down and you'll see all the available options if you don't want any format in the tool you can select this top left one here and it goes back to a sort of format looking like I had to start with or if you hover over any of these different options you can see all the formats that you want or that you can have and as soon as you see one that you like all you need to do is select that and it will apply that format for you I'm just going to stick with light so if I select light you can see it doesn't look like there's a table going on there at all so it's a really nice one to work with you only know you're in a table really when you select into the range you'll see this table design tab at the top here and also when you're not selected in the table or when you are if you see the very bottom right hand corner there's this blue triangle just in that corner of the approved one there it's just all made it hide now just here somewhere you'll see is that blue triangle again indicates the range of the table so it's going to show you the bottom right hand corner of that table range and there's other benefits again of using tables when it comes to working with charts or pivot tables it allows you to reference a table to pull data in rather than having to set your defined range so again that's another little reason of why it's beneficial to use tables and also if you're going to be using data that obviously has a lot more columns and then just the five I've got here again a table is a really nice way to keep it all together and nicely formatted as well so I hope you enjoyed that video if you did if you could give the video a like it would be greatly appreciated by me because it also shows the content that you'd like to see more of and most importantly again really helps that YouTube algorithm as well for promoting the channel if it's the first time you've seen one of the videos or you're a return visitor please do subscribe make sure you hit that bell notification button as well so you're notified of all of our future videos last thing to say is thank you very much for watching and we'll 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 playlists 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