 Guitar and Excel spreadsheet creation mapping the path to fretboard enlightenment part number four Get ready because it's time for our guitar skills to Excel 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 starting with a blank worksheet, you may want to begin back there if you do have access to this workbook There's four tabs currently down below an example tab to starting point tabs a blank tab The example tab is the finished product the end work in essence the answer key The starting point tabs will give those various starting points as we work through this long practice problem Which will correlate to the video presentations the blank tab is where we started with a blank worksheet And now we're continuing at this point in time Let's give a quick recap of what we have done thus far and then what we're planning to do from here We started out by mapping out our musical alphabet and letters a a sharp b c c sharp d d sharp e and then there's no sharp to the f f sharp g and then G sharp and then back to a and then we repeated it two times We assigned a number to it because we noted that it's kind of hard to sing the musical alphabet with the sharps and flats And what not in there and be able to say it forwards and backwards And if you number them one two three four five six seven eight nine ten eleven twelve Twelve eleven ten nine eight seven six five four three two one much easier to go up and back Therefore we are recommending that it might be something that you want to memorize highly Recommending to do that then we combine them together so we can see them as one here seeing both the number and the letter Representing the actual notes remembering that these numbers are not Representing the numbers in a scale they are representing the absolute numbers the numbers that don't change a c is the fourth note in the musical alphabet if you start counting from A being one Okay, so then we used we also created our fretboard starting with the concept that that Using our numbers first and just noting that a fretboard is designed with an e a d g b e Set up putting the related notes mapping out the fretboard so it maps out two times over So we are repeating the fretboard out to 24 so it repeats two times over the black areas are the repeated areas And then we used the fancy x lookup tool to then create this information and pull in the Letters and the numbers so now we've got this great fretboard that is mapped out that we can use to basically Start to analyze Whatever it is we want to do on the guitar and so now we're going to go over here and Continue let's go to the example tab to see what's next so we've created This and now what we want to do is make this table And that's going to help us to make this nice little chart, which will then show us the the Scale that we're working in and it will show us the related chords that we can work with as well as the intervals And we'll do it both in numbers as well as numbers and letters And these then will be right next to our fretboards so that if I wanted to hide some cells for example going from here to here And right click and hide Those cells they're going to be side by side and I can see this right next to the fretboard And it's really useful to work like that. So I'm going to right click and unhide Let's go back to the blank tab And get it get into it All right, so I'm going to be over on the left hand side I'm going to make a skinny ad I'm just going to make a skinny ad here. So we don't want anything right next to we don't like the tables next to each other We like to have a spacer Column that's just standard excel practice. It's kind of like a picky eater That doesn't like like the mashed potatoes squished together with the peas in there They need to have a divider between them if not a physical one at least some space And then we're going to say we're going to make our table. This is going to be the number So we'll make our table up top. This is going to be the number. This is going to be the The formula. Let's call it the Formula And this will make sense once we start work once we start adding the information And this is going to be the note number I'm going to call it the note number And this is going to be the note number number and Letter i'm just going to put l for letter And then this is going to be the distance Or interval Turbo from root Let's call it for now. We might change that later. All right. I changed it a little bit I'm going to change this to scale relative number. This is going to be the formula This is going to be the note number note number and i'll say letter letter And this is going to be the interval from the root So i'm going to select these as my headers then i'm going to go to the home tab And we will go to the font. Let's make it black and white as we normally do with the headers alignment Let's wrap the text this time and then i'm going to alignment and center it now when I wrap the text it makes this cell Quite large and it kind of messes everything up to the left But that's usually not too big of a problem when you're in a header when you're in the header Cell so I don't think it's going to bother us too much at this time Now if it does become problematic some workarounds of that is we can come up we can come up with abbreviations Of our headers so they don't have to be so long Or we can cut the header up and put the scale here and then the and the relative number down below it So it takes up two cells which will still look like one cell once we highlight it And so we so we'll look into possibly those possibilities if we becomes a problem later But for now we'll keep this and we're going to say all right So the scale relative number the reason I want to say relative number Is because we're unless make this I can make this as small as I can To have it three size high so is because it's going to be different than the number that's absolute This is an absolute one is an a if we're seeing it as an a the relative number means What's the number of the scale that we're working in so so i'm going to call it This is the one two and it's just going to be going down to 12 So i'll just copy that down to 12 Uh, right there Okay, and then the formula And let's look at it this way first. I'm going to put the note number here The note number is going to be let's start with a four which is c and c is the easiest To work in because there's no sharps in flats So and also if you're the the guitar is kind of tuned to work quite well in the key of c And it's relative minor a and to work in the key of g and it's relative minor e So those are some common Those are some common Scales to start practicing in and they also line up c of course lines up quite well to a piano people that are Starting to play the piano and whatnot. So i'm going to make this as small as we can to still fit there and then So then we're going to say all right Let's then also just show the The related letter and number so what I want to do is use my x lookup tool here To say if that's a number four, I want to pick up the the four c from this table So we'll use our nice x lookup tool again equals x lookup Tab and we have the lookup value. I want to look up that number four comma the lookup array I want to look it up in these numbers because I want that four right there. So i'm had control shift down Selects the entire thing and then comma and then the return array I want you to give me this one with both of them in there That's what I want you to give you and it'll come up with that c4 or 4c Control shift down will select the entire thing if you hold control backspace It'll take you back up to the top And that's what we want so we can close that up and so there's the c4 Okay, and then I'm going to want to be able to copy that down. So if I double click on it This cell right here. I want that cell to be able to move therefore I don't need any absolutes or mixed references no dollar signs here But there I want these two arrays to stay the same so everything in here I'm going to say f4 Putting a dollar sign before the a's and the two making it absolute same here I'll put my cursor in here f4 on the keyboard Dollar sign before the a's and the number here f4 dollar sign before the letters and numbers and here Dollar sign before the letters and numbers. I should be able to copy this down So next what I would like to do is put the interval when we think about a major scale The introvert of the major scale is what we think of as The holes in half steps now when I think of intervals a whole step is two Notes so two notes whole step whole step half step one note whole step whole step whole step half step That's the formula for a major a major scale. So what does a major scale mean whatever note we're starting on We're applying these distances. We're going from that note a whole step up which is defined as Two notes up. That's what a whole step is And then two notes up from there and then a half step Which is just one note up a whole step a whole step a whole step and then a half step So you kind of memorize that formula that you just have to memorize it right whole whole half whole whole half Now if you remember it in numbers It would be two notes two notes one note two note two note two note one note Remember that the whole whole half stuff too doesn't have anything to do With the sharps and flats Because the sharps and flats Really have nothing there's nothing special about a sharp or a flat. It's not any different than any other notes I think from a music theory standpoint It looks to me like what happened is music developed around kind of like the c scale and that's why the c to g is all in uh The musical alphabet And then when you started to to widen that Perspective that has now been lettered to add the other notes You had to use these sharps and flats that came into play Which makes it seem like the sharps and flats are something different than the other notes But they're not because they're just they're just if you start from a different point Then you'll be using the sharps and flats now the guitar and the piano are tuned So that they work better and I mean, you know, they work differently in different notes. So obviously Uh a piano if you just play all the white notes It sounds good in and of itself because that's in the key of c But if you want to play in any other key, it gets a little bit more complicated because you have to change the pattern Uh with the guitar The open strings become quite useful So so if you want to play in open position Then it lends itself to playing certain keys, but in theory There's no difference really between the sharps and flats. So anyways, here's there's our formula So then I can say what I'm going to do is take the four plus two So this is our running balance This is like a running balance for for accounting and then I can just put my cursor here and copy that down So now I'm just taking the last one plus The interval the last one plus the interval gives us our running balance We had four we added two now we're at six then we added two we're at eight We added one we're at nine we added two we're at 11 But there's a problem because I shouldn't be able to go past 13 And notice if I copy this down We'll get the right Numbers here until we get to here and then we get an error an error So if we go from four to six we got four five six or c Uh c sharp to d right because it because there's a c sharp here, right? We we'd be we'd be at c c sharp and then d two notes and then I go from Uh two notes up to eight So we we've got six seven eight eights and e or d two notes up d sharp and then e right So here's a d and then d sharp and then an e and then I only go one note up from there But note that when I go from e there's no there's no sharp after e that's where it's weird That's where on the piano you don't have the black note in between And you just have to know that but if you just number them Then it's not an issue right because you're just going to say that's from eight to nine And and it's just one note, right? It's just one note if you just count it from one to 12 And so that sharp and flat thing Isn't really an issue if you just number the notes because then that's why it can be easier sometimes And then it goes to 11 two steps so nine 10 11, which is a g Uh, but if you if you count this way nine Is an f and then uh f sharp and then the g But then it goes to 13 and we can't do that so I have to do a more fancy formula So let's say okay. How can I do a formula? So I so this will all work out like good And I don't have to do So I'm gonna I'm gonna delete this and I'm gonna do this again using our logic function equals if brackets I'm gonna say if this cell plus that cell And let's put some brackets around that just to make sure It takes that whole thing if that whole thing brackets around it is If that whole thing is less than 13 we're taking 13 this time because this is going to add up to If it's something over if it's 12, it's okay because then it would be a g sharp But if it's 13, then it's no good. We can't do that. So if it's less than 13 What do we want you to do comma if that's true, then I just want you to take this Plus this just do what you would normally do, but if it's not true. What do we want you to do? I want you to take this plus this And then it would be over 13 If it's over 13, I have to subtract out 12 because 12 is the number of Of notes in here. So I'm going to take that plus that I mean if it came out to 13 you'd subtract 12 and it would get you down to 1 or a right so minus minus 12 And then I'm going to say okay, so there it is Boom, so it still gives us the 6 here, but when I take it down to To here copying it down See see then it takes me so then I'm okay right here, right? It didn't take me above 12 It took me back to 1 or a so there's our there's where the fanciness happens Fanciness has happened right before our eyes with excel. It's helped us greatly Oh, by the way, so now I did now I messed up this though. This is uh Whole whole half whole whole half and then this is going to repeat itself again So So I can then say okay And let's take it down. I think we should only really need to take it down to like 16 and because note that We we start at c and then we've got one two three four five six seven notes And a scale a court and then it goes back to the one Uh, which is which is going to go back to the c So if I number you can see this note as either A one or an eight if you were going to continue up an octave, right? So I can see this as it's going from the one in the scale two three four five six seven And then back to one or I could say it's going up to the next octave And I might name it the eighth and the ninth and you know an eight nine ten You could kind of continue on so i'm going to go ahead and continue on Numbering it up this way To 16 So let's number it up to 16 And then I will copy my intervals By saying the intervals are going to then repeat or back at one Which isn't represented by an eight and then i'm going to say that this equals the two above And then when I copy that relative cell down It'll copy the the same Pattern and the pattern will repeat itself. So nice little trick little excel trick and then I can copy these down And everything should work Uh, very nicely. So I'll copy this down. And so there's our formula So now we've got We start at c which is is the four up two notes to a six Which is a d up two notes to an eight, which is an e up one note to a nine, which is an f up two notes 11 Which is a g up two notes 11 12 and then back to one Which is an a and then again now we go to two notes up which is going to a three And then we go One note up which is going to a four or a c and then two notes up to a six or d and so on and so forth Okay, and then it's sometimes it might be useful to see how far away Something is from The root note. So this is the interval from note to note Right from each note to note But sometimes it might be useful to say well, if that's the one note if that's my root How far away? What's the interval from the root? So obviously the first one is going to be just zero Because it's it's it's is the root the next one I can say well the the distance from the note to the root is this six minus the four So notice how the numbers are useful here then the next one would be eight minus four But once again, we're going to run into a problem when we do this Because we're going to end up going over Like like what if this is a one? Is the the interval wouldn't really make sense if it was One minus four is going to give us like a negative number So it's like, okay, how can I do a logical function to try to give us each of these notes? Distance from that root note So let's try to do a fancy formula like that So i'm going to say on the second one i'm going to say Logic test if so equals if brackets here's our if then function again or formula We're going to say if this minus this And let's put brackets around it Just to make sure it's that minus that if that thing is greater than Zero if it's greater than zero, so it's a positive number. What do we want you to do then comma? We just want you to take this minus that But comma if it becomes less than zero, what do we want you to do? We want you to take this minus that still But it will be a negative number and to get it back to where it should be We're going to say plus 12 because there's 12 notes And that should get us back to where we want to be so i'm going to say there it is boom now to copy that down However, there's some of these cells I don't want to move down because I want to compare everything to this note right here. So the four which is an ag Ag2 this one needs to be absolute. I want the six to move down, but not the four So everything that has an ag2 i'm going to make absolute by selecting f4 Dollar sign before the letter and number So here's another ag2 putting my cursor there f4 dollar sign before the letter and number. Here's another one ag2 f4 dollar sign before the letter and number and enter Now if I put my fill hander here and copy it down You can see the intervals look like they're doing what we would expect So this would be six minus four. This is eight minus four. This is nine minus four. This is 11 Minus four and this here's where the key is to see if it works with this negative number because it this would be one minus four But no, it's an it becomes a nine not a negative three because The the distance between the two if we go like around The horn is going to be nine not negative three In other words, for example, if you counted up from four You'd say four if I counted my fingers four five six seven eight nine ten eleven twelve and then It goes back to one Which is the nine one if you count that out on your fingers four five six seven eight nine ten eleven twelve And then back to one So nine it looks correct. So then I can copy that down. So I think that's I think that's right And so i'm going to copy that all the way down here And it gets a little bit tricky when we get above You know above here because you can kind of think of this as another octave up Or I can think of it as a repeat of that d So if it was another octave up, you might think it's like 12 plus two because it's that's the distance But I'm just going to keep it. I'm not going to do that. I'm just going to keep it at this distance here Which means the distance between the closest d and the closest c you could you could get fancy and try to do something Different with that but I'm going to keep it where we're at. So then I'm going to select all of this stuff I'm going to make it my normal formatting home tab font group. We'll hit the bucket drop down. I'm going to make it blue And bordered and it's about as thin. I'm going to make this a little bit thinner as thin as I can without Making it to wrap So there we have it. All right. So next time we're going to take this information and we'll use it to create Then this bit Which will give us which will give us that and we'll see this one As well giving us the letter and the number of whatever Key that we're in notice if I go back to the blank tab that we can change the key. We might want to make this one green Home tab font group. I'll make that one green because that's kind of the key To switching everything up. So if I wanted to switch this entire thing to any other major Scale all I have to do is change this if I change it to an a that'd be a one Everything else will change automatically and this formula will change automatically if I want to change it to a d That would be a six and then everything else will change automatically because The formula the whole whole half whole whole half formula Is based off whatever root we put into it So we're going to keep it at four which is a c because the c's don't have those sharps and flats And that's usually the first You know scale that people get familiar with so we'll keep at the familiar scale But you can obviously that's the key to flipping it to different scales and then experimenting with them