 Guitar and Excel spreadsheet creation mapping the path to fretboard enlightenment part number six. Get ready and don't fret because it's just a board with strings on it and Excel will show us how it works. Here we are in Excel. If you don't have access to this workbook, that's okay because we basically built this from a blank worksheet, but we started in a prior presentation. So if you're using a blank worksheet, you might want to begin back there. If you do have access to this workbook, there's currently six tabs down below the example tab, the starting point tabs, the blank tab, the example tab being the end result, the finished product, basically the answer key. The starting point tabs represent different starting points that will tie into the video presentations as we work through this long practice problem and the blank tab is where we started with a blank worksheet and are continuing at this point in time. Let's give a quick recap of what we have done thus far. So we've been thinking about our musical alphabet. We listed out the musical alphabet here starting at A. We're representing the sharps and flats with a lowercase AB. So this is A, A sharp B, C, no sharp or flat between there. C sharp D and then D sharp E and then there's no sharp or flat between E and F. So F and then F sharp G and then G sharp and then the thing starts over again. And so we mapped it out a couple times over here. So we've mapped it out two times over and then we numbered the notes, which is useful both for Excel as well as for your own interval understanding, being able to count the notes up backwards and intervals just one to 12. And then we repeated that one to 12. Then we combined them together so we could see both the number and the note, both of these being what I would call absolutes, meaning they're just the name of the note. They are two names for the same note. This number gets a little bit confusing because we use a lot of numbers for other things as well, such as the relative number of the scale position of a note. So if we're in the C scale, then now this represents the number one position of the scale note, which is now a C because we're in the C scale. If we were in something else like a D, then this number one would be relative to the scale that we are in, which is now the scale of D. Let's go back to C, which is usually the easiest scale to start off with. We did our musical formula, which is whole, whole, half, whole, whole, whole, half, or two notes, two notes, one note, two note, two note, one note, in order to map out our scale. So now we took all of the notes in the musical alphabet, chose a central note, a starting point of C in this case, and applied our musical formula, whole, whole, half, whole, whole, half, in order to get the notes in the scale. And this could be adjusted by changing this keynote of number four to whatever we want to change it to, and then everything will change automatically from there. We repeated it a couple times. This will help us as we build then our worksheet on the right. So now what we wanted to do is we wanted to build a worksheet. If I go to the example tab over here, we ultimately want to get to kind of this worksheet right here, which will give us all of the notes, it'll give us the chords, and it'll tell us if we can use a major or minor chord, and it'll also give us some indications of the intervals of those chords, so we could better find where the fingering is on the fretboard. That's what we're working on here. Now we basically did this with just the numbers. That will help us now to format the same kind of things with the letters, and then we'll talk about it a little bit more as we do that. So we'll say, okay, let's just pull this whole table down. I'm going to do this with simple formulas. I'm going to pull it down here. I'm just going to say that this is equal to, and I'm just going to scroll up and pick up this first cell. So I'm just going to pick up the same cells. This is going to be equal to this cell. I could just copy that down. I'm going to copy that down to here. So that looks good. And then the Roman numerals, I can even copy those down. So let's just copy the Roman numerals down as well, because everything is going to be exactly the same. So that looks good. And then I'm going to copy this to the right. So same idea. I could just take my same thing. I'm going to copy it to the right here. And then it didn't pull over. What happened? K-Paw. So I'm up one. So let's not do it right there. Let's do it right here. This is going to be equal to the zero. And then I'm going to copy that across. Let's copy that across. Hold on. Even that's a little bit off. Let's remove that. Let's copy this bit first. So this one and so on is going to be right here. Let's equals that one and then copy that down. So that represents the relative positions of the notes. And then I'll be up top here on this one. And this is going to be equal to this zero. And I can copy that across. So that looks good. And then I'll do the same thing here. Well, I could just copy these down. Let's just copy this down. I'm going to say boom to here. Now I can't do the same with the numbers of course because I want to add the letters. That's where it's going to change things up. I also would like to indicate that this is going to be the C up top. So I could say up top in this upper right hand corner. I'm going to say this equals and I'll pick up this C4C. So that that will give us an indication of the notes that we are in or the key that we are in which is pulling in from our table. Alright, so then I'm also going to and I can pull that up here. I could say this is going to be equal to that 4C. And then I'm going to copy the formatting of this. So I'll select the entire thing. Go to the home tab, clipboard, format painter. And I'll just format paint this entire thing right here. And so there we have it. Now I just need to fill in both the number and the letter. So I want to do a lookup tool to be able to do that. What I want to say is I want to find this number in my table to the right. And I could just use my overall table this time. So I could use basically either table I want, but I think it might be easier to use this table. And actually, no, let's let's use this table because that'll that'll be easier once we build the ones in the future. So we'll do that. We're going to say this is going to be equal to I'm going to use an X lookup. So we've been using this tool a few times. I'm just going to be picking up this cell. I want to look up that cell comma. And then the next argument, where's the lookup array? I want to find it over here. So we want to find the actual note number. So here's the note numbers here. I'm going to say control shift down. So we pick up the entire array and then comma and then the return array. What do we want to return? I want to return this column, which gives us both the number and the letter control shift down. And so there is our formula. So I'm going to say now when I copy this across, I don't want the arrays to move. I do want this cell to move. Therefore, the arrays need to be absolute. Put my cursor in the arrays at $4 sign before the letter number at $4 sign before the letter number at four and at four. There we have it. Let's say enter and then I can copy it copying it to the right. And everything should populate same numbers as we had above. But now we have the letters as well. And then we can copy it on down to so there we have it. Let's make this whole thing a little bit wider so we can now accommodate these larger cells. I want to make it large enough to pick up like this cell, which has that G11 because that's probably the widest cell that we have. So there we have it. Okay, so now we can kind of think about this a little bit more in terms of what do these items mean above. So quick recap, remembering that these are basically the intervals that we used to count relative to the scale that we're in. So if we started on the four, we're going up every two notes to build our scales. So we went up to four to eight, which is an E now, and then up two notes in the scale to the G and up two notes in the scale to the B and so on. And it's nice that you can kind of count that this is counting from the root note. Each of these each of these notes that are going to be relative to the chords that we are creating. However, these distances we can think of these as distances, but they're not absolute distances because they're relative to the scale. And we've removed some of the notes in the scale. So when you think about an absolute distance, then we could try to that's what this one was you'll recall. So so now we're looking at how far away is something from the root in terms of absolutes not relative to the scale. And then this one is is naming the scale interval. So let's take a look at this in terms of a chord. So if I look at this as the one chord, it's going to be a C major typically. And if we look at it and say analyze what is happening, this is the one three five. These are the three notes that need to be you need to have a handle on in order to play the major C. Now, when you play a guitar, you might you might hold multiple multiple of the same notes. But as long as you have those three in place, then you have a C and then you could add other stuff to it as well. But those three make up the triad. So if we analyze that, we could say, OK, this is the one, which is a C. This is the three. Now, how far away is the three? We know that it's it's going to be it's going to be two notes away from the one in our scale. Meaning if my scale I had one going two notes away to get to the eight or I can think of it here one and two notes away to get to the eight in my scale. But if I think of absolute notes, then if we look at the numbers, it's quite helpful if four is a C and I'm going up to eight, it's four notes away eight minus four. And so that's why the numbers are useful. In other words, if I look over here and my entire musical alphabet and I look at starting at four, I'm going up to eight, which is four notes away as opposed to over here. When I've removed some of the notes and I'm going up to eight, I'm only going to relative positions away relative to the major scale. So this is so that you can think of it in terms of absolute distances, which is nice because that helps you to see every note on the fretboard. So we'll do practice problems, hopefully at some future point. And that'll make that a little bit more clear and see why that would would be useful. Now notice that the fifth then is the fifth note, right? But in our scale, but it's the. So if I was to look at the fifth note, here's 12345, which is this G here, which I'm going to say is 11 in terms of numbers. But if we look at the absolute distance, it's actually seven notes away, which we can see if you look at the numbering 11 minus four, right? 11 minus four is going to give us the seven notes away. So it's actually an absolute distance of seven, but it's note number five in our scale position. Note five in the scale position, seven notes in terms of absolute position for relative scale positions from the from the root from the root of the chord, which is four. Now, when you get a little bit more tricky, then we could keep going. That's why we keep on having these up top. So I could say why stop at just the three notes? All I'm doing is picking every other note here. Why don't I just keep going? And if I go two notes up again in relative to the scale position will be at what we would call the seven. So now we can have the 135 and then add the seven. And if I keep going, I can add a nine and then an 11 and then a 13. So usually when you're looking at the guitar, it's kind of useful to say here, how can I pick up my three notes? Where are my missing? Where are my fingers still able to pick something else up and then start to think, could I pick up a seven, nine, 11 or 13? And so we'll start to play with these once we get to the once after we basically build the worksheet. So also note that when you look at these notes, the the now we're on a minor. The reason this is a minor is because when we construct this, now we've got the one here in our chord. So now this is the two. So now we're talking about a two note in the scale. It's the two notes. So we build a minor chord around it. And the reason we build a minor chord is because when I think about this D and I go two notes up every time relative to the chord, I go two notes up from the scale that we're in, which we could use the scale of D and we end up with the same result for the first three notes. But in the scale that we're in, if we go two notes up and two notes up from here, then then we get this right. So I mean, if I started a D in other words, and I go two notes up, boom, boom, we're at an F and then two notes up. And then we get an A. So D F A, that's going to be what we have there. Now it's a minor. Why is it a minor? Remember, when I put this interval up here, this interval represents just related to the one chord. So if I compare that to the other intervals, I can see where the differences are. So the minors are always going to be different. That's what defines it as a minor. So in other words, if I take this nine minus the six, seven, eight, nine, I get three, which is a minor third. That's why they call it a minor third instead of four between the two, four notes between the two, which is a major. So we might then want to kind of list out where the differences are, right? So like this one has differences on the minors. So you could start to say, well, the intervals are different on these two notes and the intervals are different on possibly these two notes, right, from the major. And then the one is always going to be the same because the number one and then the five is always going to be the same. So you'll notice on the minor, if I look at the distance between the five and the one, we have a seven, a seven note difference because we go six, seven, eight, nine, ten, eleven, twelve. I'm counting on my fingers and then back to one, which is seven. And so and that's why you get so you still have a seven note difference, whether it's major or minor. Now when you get to the seven chord, that again, that's where it gets a little bit weird because you're going to have possibly different intervals. And so we'll talk more about that later. So this is the interval for just the one chord. And then we could start looking at our seven, nine, eleven and thirteen and start to say, okay, when is that interval the same and when is it different? And that'll help us to kind of think about where we can put our fingers on the guitar. That's why these things these things up top can be useful. So we'll get into that later right now. We'll just build our worksheet. So I'm going to make this a little bit smaller here. And so there we have it. And so now we have really the core for us to kind of compare this to our fretboard. If I'm working somewhere on my fretboard, I can hide from say M to to here a J right click and hide. And now we have our ability to kind of work these two things together. Now it'd be nice if I move this down to have it level with this one. So I'm going to unhide put my cursor on L to a K right click unhide. And I'm just going to move this whole thing down. I'm going to take this and I want to move it down to be level here. So I want to move it down to 11 or 14 and grab it and move it down. That's one way you can do it. That's too far. Another way you could do it is you can say control X or cut. And then I want to put it right here. So I'm going to put it right here, control V. And so now I've moved it down. So now when I put these things side by side, I can say, okay, let's hide all of this stuff right click and hide. And now I can look at it in terms of numbers or I can look at it in terms of the letters. I might want to hide this as well. Maybe I don't need all this right click and hide. And so now I've just have my fretboard one to 11 next to my worksheet. And I can start to say, okay, what if I want to build this chord right here? I can start to highlight and use my home tab formatting and my formatting tools and say this is equal to say a C, for example, and find all the C's on the fretboard. So I can start to build this around. And the general thing I would like to do here normally is if I clear everything, I usually start off putting everything down, right? And then I find a different color for the ones I'm working on. So I might make everything blue, right? I can say, okay, everything I'm going to make blue for all of the notes. And then I'll change the color for the ones I'm working on. So I'll say this is going to be light blue up for the C. And then I put in letters. Let's do that again. Format, I don't want that there. What I want instead is I'll make that black again. I want the field to be blue. So there's that one. I'm going to say, okay. And then I can say, boom, next one is going to be the next note in the scale. I'm going to make it blue too. And then I'm going to say, okay, next note in the scale. I'm going to say is this one. I'm going to make it blue too. And then, okay, next note in the scale. I'm going to make it blue too. And boom. And then that's an E. I think F next note in the scale is it would be nice to do this all at the same time. But I think that you have to do it one at a time as far as I can tell, which is kind of annoying, but it is what it is still way better than anything else I have seen. So we'll pick this one up as far as I know. And then one more round. And I'll pick this one up and blue boom for this one. So now you've got your major, your scale mapped out. And then sometimes I will insert like maybe a square where I'm going to remove the border. So I'm going to go up top and say I want to make the fill no fill and then add the border making it red or something. And then maybe I'll make it a little bit thicker. So now I've got this maybe like that. I've got this thing here and I can say, OK, if I'm looking at my positions, that's my finger in position. Now this is your most famous kind of rock and roll finger in position. You can see the pentatonic position in here, which is here. That's the pentatonic. Right. Boom, boom, boom, that you can see in here. And then we add the two other notes, which is the, which gives us the major scale. So it's nice to be able to map this stuff out first and then you can build inside of it. The whole, this is the whole major scale inside of that is the pentatonic scale. And inside of that is whatever scale you're working on. In this case, if I'm looking at a C, I can add the last couple of rules and say this is going to be equal to the C. And let's make that green. I like to make the root green. And then maybe I'm going to say, hold on, wrong spot. Let's do it again here. Boom, boom, equal to this one and make that green. And then green because it's the root. That's where you want to be. And then the next one may be red. And then the next one I'm going to say is yellow. So now, now you can see everything that's colored is in our major scale. And then the ones that are different colors than blue are the ones that you would need to grab in order to get a C to get a triad of a C. So right to get a triad of a C, you'd need to grab one of each color somehow. You can see that most clearly back here where you see the C in the open position. So now, well, I should put it back here and I should have made it blue and everything for these ones to the open position. So here's your C right here. Well, there's the three notes that we hold down. And then we also have the open notes when you're dealing with the C. Let me add these. I'll do this the same formatting here. All right. So now I've redone it and just added these open notes in here as well. So now, so, and sometimes also I might make another one of these and just put it around that zero and maybe make it like brown just to indicate to me that those are the open. That's the open fret. So then I would say home tab or let's go to my shapes and I'm going to make the outline of this like brown or something. So just to indicate those are my open items and then maybe I want this one on top. So I'm going to cut and then hold on a second undo undo. I'm going to try to cut it and then paste it again to put it on top. So it comes on top here. So the red is over the open position. And so there we have it. So now you can see this is where your fingers would be. And then here's the open another open note here and here. So so now you can see that what's happening when you play a C when you start to analyze this you can say well what what is happening because there's only three notes to make a C. Am I adding other notes like the seven nine third eleven and thirteen? No, you're playing the same notes over again. So if you use that as your that's the root note and then you've got then the this would you start calling the major third and you can start saying OK the main and the major third is is for absolute notes away. And you can start to see where that is positioned on the fretboard when you start counting in absolutes. Right because I can say there's the C one two three four would be right there. And then if I went on the string below it it's right here which is just one string back. And if I count to the next position up top it would be one two three four five and up one. You can start to see that when you look at absolute positions not relative positions to the to the scale you're in because if it's relative to the scale you're going to have to count through the scale. In order to see the numbering in the scale right and so then and then you've got this this open note which is which is the fifth another another fifth which is open you're not putting your finger on that and then another C and then another E. So you can see everything that you're playing when you play the C. You know is is one of these three notes right so it doesn't matter that you're repeating them. It doesn't really matter that they're in verse if you grab this eleven in other words you might call it like an eleven on top of a C major or something like that. But because this is the lowest note but even if you've just played these three notes you can still think of it as some kind of augmented C right it's still a C because you have a you have a C E and G it's just at the G. So is the lowest is the lowest key wise note when you grab those three but you can still think of it as a C and those different voicings are really useful because they sound different. So even if you just play the C like all the different ways you can play a C once you go up and down the fretboard you get all these different voicings of it which is kind of which is nice in and of itself. I say well why do I need to do that I know this C I don't need to know any other C because I already have a C I want to learn other stuff that's what I used to think sometimes you know and you say well no. Like if you play C a bunch of different ways that's really useful because because then it sounds different even though you have the same three notes because all the pitches are different that are in the note. So even one one different note playing these three versus these three or these three is going to sound different which is good. All right any case there it is for now let's unhide and we'll continue on with our worksheet next time.