Speaker 1: Hi, and welcome back to another video. Today, we are talking about Microsoft Excel and databases. In this tutorial, we'll show you how to set up a database table in one of your sheets and how you can interact with that data. If you find this useful, please click the like button, hit the subscribe and tap that bell. It really helps the channel out. And with that said, let's jump on into it. So we're going to head over to our Excel document. And in here, we've just set up a tab called database. Within this particular sheet, we've got four columns, one for salesperson, one for region, one for month and one for sales. And it's pretty standard information. It's basically a list of salespeople, all of the regions they work in by month, and then the amount of sales that they've actually achieved. So what we want to do is actually take this information and turn it into a table so that we can interact with it in the way that a database would. So what we want to do is select anywhere within our data, head over to the insert tab and click table. This will then give you a dialog box like this, which basically shows you the range of data that has been automatically selected by Excel. If you need to expand that, then feel free to highlight, but it will basically find all of your raw data up to the space where there is no more data below it. Or if you have a blank row, then unfortunately it will stop there. So you might want to expand it further or just remove those blank rows before starting, which would be the best practice. The part underneath here just shows you whether or not your table has headers. And if you do not have headers, then you need to correct that before you jump to this stage. It's very important that if you want to start treating your data as if it were a database table, that you must include headers with every single column. So for my example here, it has selected everything I have downloaded into this particular sheet and my tables do have headers. So I can click OK. Now we jump straight over into the table design tab within the ribbon. First thing that we're going to do is actually change our table name so it makes more sense. And I'm going to call this sales data. Press enter. Now our table has a name and we can basically utilize that name in some of the formulas and functions. The next thing that we're going to do is change the horrible blue and make it something a bit more easy on the eyes. So I personally prefer black and white, but you have plenty of options to choose from. And do is just expand this out a little bit, make it a little bit easier to read. And there we go. Now, with this database table, there's many different ways that you can add information into the table and how to manage that. I won't talk about them in great detail in this video. But if you want to see anything specific, drop a comment down below and I will try to get that topic covered. Some of the examples may be how you interact with a database table within Excel and using something like Power Automate or SharePoint, VBA and macros and things like that. So if you need anything, hints and tips on any of that stuff, drop a note below and we'll get that covered. If however, you just want to add some data manually into this database table, then you can go to the very bottom to the next available blank row and just type your data in, press tab and automatically Excel now knows that that particular row should be a part of my table. I can then continue to populate the information in and press enter. Now, if however, you've left a blank row between where you start typing, then unfortunately, Excel does not know that you want to include that in your table. You can correct this, however, if you, if I just finish populating this 500 there, right in your table here, you'll notice at the bottom right hand corner, a little triangle. If you hover above that, you get a double ended diagonal arrow. If you click and drag, you can actually drag that database table over the top of the data that you entered, release it. And you basically now included that within your database table. What you want to do though, however, and best practice to make sure you always remove any blanks from your database table, just highlight that one row, right click and delete it. So now you have a more accurate and easy to manage database table. I'm just going to remove these two records that I added, because they're not needed. Okay. So that is how you create a database table and how you can manually add information into it. Now we have this particular table, it's time to start thinking about how we can interact with it. So if we wanted to use information from this particular table within our Excel document, there are a few specific functions that Excel have come up with that actually help you do such a thing. Okay. So these are some of the standard formulas that you'll probably be familiar with, such as average, count, count a min, max and sum, except they all have a leading D and the D basically represents a database function. So admittedly, you could still use the standard sum function, a standard max, a standard min or the standard counts and an average, but they would interact with your table slightly differently. These give you the most dynamic approach to how you would like to interact with your data. So if we just take this D average as an example, what we would do is we can create a criteria mini table, if you will. So if I select these two fields and paste them over here, I can use this as criteria for this particular function. Okay. So what that means is if I open up a D average and open up a bracket, it consists of three parts, the database itself, which is this table, the field that you would like to average, which would be our sales. And then the criteria, which would be the region by England and the month of January, right? So we could average this database table based on England for January. Okay. And to do that, what we would do is we'd head over to our table just here and select our database. If we hover above the top left-hand side, we get a diagonal arrow. We can click that and it'll automatically drop in here our sales data table. We then just want to open up some brackets and we want to include all of our data within that particular database. Okay. So sales data in brackets, curly brackets, hashtag all. Okay. From here, we press comma, and then we get to the field function. Now there's two ways to handle this. We can click on there and we can see that it's gone and said, and added in sales data. And then in brackets, hashtag headers, let's put a comma and in brackets sales, and then close those brackets up. And basically what that's doing is telling Excel that we want to average the sales field. Now that's one way of doing it. The other way would be to say that it's the fourth column. It's one, two, three, four. It's the fourth column in our database. That's another way of handling it. Or we can just in quotations type sales. And because that's the name of the field, we could use it, use quotations, actually just write out the word sales. For this example, however, probably the most accurate way of doing it is just to click on the heading that you actually want to average. And at least that keeps things nice and dynamic. As we feel like to change that particular header for whatever reason, then it will automatically update this particular function. From here, we will press comma, and we get to the criteria section. From this criteria, all we need to do is highlight our little mini table. So it's important that we include the headers and the headers have to match what's inside our database. And then the criteria sits directly underneath that. And basically, that's it, we can close our bracket off, press enter, and we have the average of England for January. And obviously, we can just dynamically change our months or our region. And our average will always calculate. The same applies to the count, the count a means maxes and sums, they work in exactly the same way. You just go D sum, and you see you end up with these three fields, right? So the database, so we can just click that, make sure that we open up our brackets and click on the all, we want to make sure it's the entire database that we want to bring back. And our field, which will be sales. And our criteria, which would be we want to summarize everything for the month of February, for example. Okay, close that off, press enter. And that's the sum of February. I can also go January. Or if I really wanted to, I can change our criteria to include the region. So we want to sum everything in Wales for the month of January. And I can do that too. Pretty straightforward and simple. And there's a whole host of other functions. So I suggest you guys kind of dig into it, they're not easy to find. So instead of kind of coming into here and just start typing away and trying to figure it out, you'd have to come into the insert function dialog box. And then from this drop down, you can go and click on database and then this shows you all of the various database functions and formulas that are available in Excel. And that's it guys. If you found this useful, please hit the like button, tap subscribe. It really does help the channel out a lot. And I'll catch you guys in the next video.