 Guitar and Excel spreadsheet creation mapping the path to fretboard enlightenment part number 10 So 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 So if you're using a blank worksheet, you may want to begin back there But if you do have access, there's a bunch of tabs down below now including the example tab the end result the finished product The answer key if you will starting point tabs corresponding to the video Presentations as we work through this long practice problem blank tab Representing the blank worksheet. We started on and are continuing on with at this time Let's first give a recap of what we have done thus far We started by thinking about the musical alphabet Listing it in a column format a the sharps and flats being represented with the lower case a b because it's in between a and b so it could be an a sharp or a b-flat and then b c c sharp D d sharp e f and so on until we get to G sharp and then it starts over again We numbered our notes which I think is useful both for Excel as well as for learning the music theory That allowing us to count upwards and backwards and not have to get confused about the sharps and flats So there's circumstances where the sharps and flats can be useful But there's also circumstances where it can be quite useful to just give one number To count the intervals and whatnot. We then combined them together We created our fretboard both in terms of numbers as well as with the numbers and The letters related to it on the fretboard then we constructed our scales Starting with the major scales starting with C because that's usually the starting point But using this key reference to be able to change any of the keys That's the point that green number if I change it to a 3 or a b everything else related to it should change And that's the goal so we have our formula whole whole half whole whole half Or you could think of it as two note distance two note distant one note distance two two two and then one So that we can have our running balance Which will pick up the relative notes in number format And then we picked up the relative notes in both letter and number format using the fancy V lookup We created our major Uh our our major table over here for the scale. So this is a C major. We have then the notes We have the uh roman numerals which tell us which relative position and the scale we're talking about and whether or not we're going to have A uh major or minor scale by whether it's going to be a capital or Lower case we then made a circle format, which is quite useful when you're trying to understand how the Chord is being created because we're just picking every other note from whatever starting point that we were on And it's useful to be able to see that in a circle, which is never ending And then we copied the whole thing to and converted it so we can see it in a minor as well Hopefully we've got everything correct here. We're going to kind of play with this as we start working with it and just work out the kinks of it But now we have the minor so the minor the reason we went to the minor now note Is because It's the it's the second most common That we're going to use mode that we're going to use But it's not really in order So you would think for example if I start on the one The next mode we would do is the dorian because that's the one to the right And if I just go around the circle we would go to the modes But we skipped ahead to go to the minor or aolean Mode because the major and the minor in western music are the most common ones that you really want to Understand and now we'll go into the the less common but quite useful And then now we're going to start back on the c and kind of go to the right and pick up The dorian so just like we did with the minor the other modes are the same We're just going to we're just going to do the same thing as we did with the minor all the notes are going to be the same We're going to do it relative to the c To the to the major scale that we're looking at in this case c But we'll just start now with the second note in the scale Which is a six or a d right second note in the scale six or a d Okay, so how can we do that? Let's we'll start off by just hopefully we try to get everything So that I can just copy this whole table that we did with the minor or aolean mode And then just copy that over So let's see if we can do that. So this is let's just put my cursor in the skinny from here And I'm going to go I'm going to go all the way out to here By and then right click and copy it And then I'm going to put that in bz So we're going to go over here and put it in bz right click and just paste it And then try to analyze this and see if everything Is populating the way we want it to so first thing we need to change the title Here, so I'm going to call this a dorian And it's going to be the this is going to be the two note From relative major so in other words I'm building a dorian when you start when someone talks about a dorian One way you might want to think about that is say, okay, what's the relative major? It's the two note of the relative major in this case Which would we're going to be talking about a d Once we fix this so so the d is going to be relative then to The c the two note of the c So that's one way you kind of want to be able to to visualize things if you start to Start to understand these different modes. Okay So now what I need to do is change this key. So just like we did with a minor I'm going to go all the way back to the major over here And I'm going to say, okay With the majors the one note was was whatever we're starting on the four So now I'm looking at the relative minor. So I'm just going to start with the two notes So I'm just going to pick up the six and and act as if it's the one note And in practice when playing music you're just going to kind of play around that note as though It's the center of the one note It's the one you always go home to and you'll be playing basically a dorian even though you're using the c major You'll be playing a d dorian Even though you're using the c major scales just like you would be playing an a minor even though you're using the c major Notes because you'd be hanging around the a minor instead of the c So in any case if I go over here We're going to say Let's just I'm just going to change this one note Right there. That's the key. So I'm going to say this is going to be equal to in All the way to the left. I'm going back to the majors And I want to be saying it's equal to that Six note and so I'm going to say okay And there we have it now the formula. I'm going to delete the formula for now Actually, the formula should be good. I'll just keep the formula And so now I'm going to copy down this six. I'm going to copy it down until it repeats So I'll copy it down to here. Let's delete everything else that's below this So I don't get confused. You're confusing me I don't like being confused. I'm trying to figure it out man So six to six So then it starts over then I can just Then I'm just going to say if after it starts over this equals the first point And then I could just repeat it however many times I want. So then it's going to repeat on down Taking it down to 24 So there we have it. I think that lines up. I think that is correct And then our formula should populate properly because I'm taking The difference the same formula the difference between these two notes So just a quick recap on the formula over here We were talking about the the major scale formula is whole whole half whole whole half But if I start the formula as though the six Is the one note then the formula is going to be a whole half whole whole half whole right So it's the same formula, but you're just starting from a different point So that so that's what we have over here. You could see that being populated I'm starting from the six, which is relative Which is a d which is relative to the four on the major meaning it's it's the two notes So if I went backwards Then I'd get to the four if I go upwards I'm going up to the next c here and you can see if I started at c Then I'd have my major I'd have my major progression, which would be whole whole half whole whole half But here if I start here, I've got I've got whole half whole whole half whole So that's gonna that's the that's the only difference that we have here and then let's see This should populate properly everything else should basically work. I believe And then we've got our majors and minors So let's just see I'm going to go down here and see if this if if these Uh worked because we had Now I could see right here something happened with my x lookup and if I look at it Notice I I kept that I have a dollar sign in there that's messing it up that was absolute So if I go back on over here and I say, okay, if I look at these formulas I had a dollar sign and this one That I should have removed so I'm going to go in here and I'm going to remove that dollar sign And then it should copy over properly. I'm going to copy that one So I think the rest of them look like they populated properly And so so there we have it. I think that looks good So now let's think if we think about the 145 and the c's Here's the the c in the majors the 145 Let's just take a look at it just so we can kind of get an idea what I'm talking about here You've got the 145 which was a c The four note when I'm looking at the major scale was a g I'm sorry the four was an f and then a g those should be capitalized Representing the the idea that we're going to be building a major chord. So these same notes c f and g Should have a capital or uppercase roman numeral. So if I go over here and say, all right, this is a c has a capital and then f has a capital and g Has a capital so that looks right and then on the minors We've got then the minors are a lower case So all the rest are lower case and then we've got the dot on the six Which is the 3b. So if I look over here On the major just to kind of check this Here's the major 3b was the seven over here Now 3b is the six and the dot represents that it's going to be building a diminished So just so you get an idea of these roman numerals the idea here being We were on the c Before so now the c is the Seventh note when we're thinking about it in the dorian But if I was to build it I'm going to get to the same c major So if I if I started on my wheel over here And I and I still took every other note I'd be starting I'd be starting on the c here skipping to the e skipping to the g That's building these three notes and we define it as a major because This third is going to be four notes away or two whole steps major third from the From the starting note of the chord Which we can see numerically because it's eight minus four is four Right whereas if I if I look at the minors here like this a minor Uh, it which was the six of the of the major scale and was minor represented with a lower case over here Should have a minor distance between the one and the three So four minus one is three a whole step half step or three note distance So that's the difference between a major and minor and it's going to map out So that all of the chords you are playing are basically the same in all the relative modes to The major scale would be the idea. So I think everything up here is populating properly So let's look at it on this side So these pulled over like we like we hoped now these ones You'll recall if you saw the prior presentations that I removed The absolute references which would have been helpful as I constructed this When we built it so that I can copy the same formula across But then I built it without those absolute references so that when I copied the entire thing over here It will it will populate properly and it did right so now those absolute references didn't mess this up However, I think I could have done it more efficiently before by first putting the absolute references in Then copying it across and then going in there and removing the dollar signs Right. I think what I did is I took a whole lot of time Reconstructing these long formulas. I could have just constructed them with the absolute references And then going in and just remove the dollar signs remove the absolute probably been faster So same thing. I I think I did down here Where I did the x lookup and I and when I first did the x lookup on the majors. I basically took I took Absolute references of these and mixed references so I can copy across the whole fretboard But this time I took an immense amount of time in a prior presentation Copying and pasting and redoing this formula without the absolute references so that I could copy the whole thing over here And not have the absolute references being referring back To this table and it worked. So that was great. It saved some time, but we could have been more efficient by basically Doing the absolute reference and mixed references when building this table And then going back into it and removing the absolute references once it's been built instead of recreating the x lookups Every time without the absolute references. So just something to keep note of So now we have the the dorian. I think it's populating. Okay. We'll test it out more Uh when we start playing with the worksheet, but it looks good Uh at my first uh glance here. So let's just kind of get an idea of what it's doing So now we've got the one is a d. So now the one the one is a d Uh and so we can look at our circle up here and we have the one is going to be constructing a minor chord for the for the one Chord because it's it was the two chord when we thought about the major chord Which is a d and then and then we're going to go to the two is now And an e and it's going to be constructing a minor And then we've got the the three is going to be the major for the and you can see, you know major here major here three and then Uh the four is going to be a major the five is going to be the minor and you should start to be recognizing that The you know, you got the same majors and minors no matter what mode you are in so you might be saying Well, what does it matter? I can just call it all the c C major and then play different and you just kind of play around Uh around different you can kind of think of it that way when you when you're doing it this when you're when you're trying to Look at relative modes. You could say well, I'm going to just Play the c major, but I'm going to be playing around the d Uh and therefore it's basically you're basically kind of playing a dorian mode or I'm going to make the c the d Uh, which which is a minor that's also in the that's going to be my home base And then you're kind of playing like in a dorian because that's your Root note you can think about it that way, but it gets a little get more messy when you're trying to think about If I want to switch from a d Uh a d dorian to like a major d a major d scale, right? Then it becomes a little bit a little bit more Difficult to just try to think of everything as related to its related major But that's the first thing to kind of note that it is, you know all the same to the related major You got to kind of just twist it in your mind reorganize it So that now that that's your home base and it's quite useful to be able to say okay that D dorian is related to of course the c major it's so if someone gives you a dorian mode Then it's useful to be able to say what's the relative major because it's likely That I have a better understanding of the notes in the major scale as opposed to me having Memorize them in the form of a dorian and then think about it as dorian so I guess Uh, so that so that's a useful a useful thing and you can you can see it here If you're in if you're in the dorian because it's the two note of the relative c major the c Is uh the the the seven here So you if you're in the dorian you can say all right well if I go back To the c now i'm in the you know the c Here so you can kind of see those patterns in the guitar. All right, so I think that is that is good I think that's all we need to change because we did it so efficiently So now next time I'll try to copy multiple of these over to do some of the the rest of the modes And then we'll work on the relative modes underneath Noting that it's useful to have all the modes Like listed out on the right that are related to the major because then I can basically go to my fretboard And if I want to hide some cells I can hide all the way out to this uh dorian Right, I can hide everything out to here to have this side by side right click and hide And so if I'm working in dorian, I can have the dorian right next to My fretboard and I can have the relative major Pretty pretty close because I'd have to unhide and find the relative major pretty nicely but I'd also like to have the The the the this worksheet related to the like the other modes that are also in d And that's what we'll build below it as well So so now we're gonna do a lot now that we have everything mapped out pretty well We're gonna do a lot of kind of copying and pasting to get all the other modes in place Hopefully if if everything's mapped out properly, this should be pretty easy to just do the copying and pasting I'm gonna right click and unhide and we'll continue on with that next time