 Guitar and Excel spreadsheet creation mapping the path to fretboard enlightenment part number five Get ready and don't fret because the board's been fretted. So you don't have to be 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 currently five tabs down below Example tab the start point tabs the blank tab the example tab being the end product the finished worked in essence The answer key the start points represent the different start and stop points that will line up to the video presentations as we work through this long practice problem the blank tab is where we started with a blank worksheet and are Continuing on that worksheet at this time. Let's give a quick recap of what we have done thus far We've looked at our musical alphabet and we listed it in a column form at starting with a a sharp b c c sharp d e and of course there's 12 notes and the Musical alphabet we numbered those notes both for help with our Excel table But also because the numbering is a great Memorization tool as we will see as we construct this the next part of our project So we then took both of those items with a nice Excel formula here And then we also created our fretboard then so we listed the strings lowest string or the the lowest pitch string the thickest string one closest to the ceiling on top and Then on down and we listed the notes in terms of numbers as well as the numbers and the letter of the note And so then we also put together our table here where we're now looking at a specific a Specific scale so in this case we're gonna start with c because that's typically the place to start because it doesn't have any of The sharps and flats because I believe that's kind of the scale that Western music basically started around So it's the scale that doesn't have any sharps and flats But the formula is the same for any major scale which is going to be whole or two notes Whole or two notes half whole whole whole two note two note two note half so if we use that formula and we think of the notes as Numbers absolute numbers then we could say well if four Represents the C then if I go two nuts notes up I get to a six and then two notes up I get to an eight one note up I get to a nine and then two notes up I get to an eleven and so on and so forth and then we can show that in terms of the Number and the letter and we can also look at the intervals now that we're using these numbers for notes between each of The notes and the scale now Compared to the root note So just remember that when we're looking at these numbers it can obviously get a little bit confusing because we're using Numbers for multiple different things but oftentimes the numbers that we usually hear I would call Relative numbers meaning the relative to a scale so this if I'm talking about C as Four or C being the root that we are working in and I'm calling that the one of the scale The one means that's the relative position to the scale The four represents the fact of an absolute number which doesn't change meaning if you were to number the notes From one to four starting at a for one it would be four so four doesn't change It's an absolute number. It is what it is just like C doesn't change numbers like the first number one note in a scale Will change because right now it's a C But I could say I'm just gonna do the same thing with a D right which would be a six now The one note is relative because we're saying it's the one note in the scale of D. All right So keeping that in mind what we're gonna do now if I look at the example tab is we'll use that information To create our table that will look like this and then a table that will look like this and this will give us a lot of information Such as the distance between the notes. It'll give us our scales It'll tell us what kind of chord that we can play whether it be major or minor It'll tell us the notes in the chord So this is really the heart of a lot of the stuff that can be quite useful when practicing the actual guitar because you could Align this information up to the actual fretboard and try to figure out what you're actually playing which could be quite useful Let's go to the blank tab and start on that. So I'm gonna make a skinny AJ I knew an AJ that was skin wasn't there a TV star named AJ was really a skinny a skinny guy Anyways, so this is gonna be major major major Ionian we're gonna say this is the one note from our Major which will make more sense once we get into some of the nodes Noting that the major scale is kind of the home base scale in Western music If you were to give it its Greek name as all the other modes are named It's mode name is a Greek name of the Ionian scale So you can call it the major scale or the Ionian scale the home base scale in Western music next header relative scale intervals from Cord root now this will make more sense once we start to populate that and then we're gonna say the next header is gonna be the absolute Intervals from Root and this is getting a little bit confusing right because now we've got these different intervals involved We'll talk about them as we build this and then we've got the cord Scale Intervals All right, so I'm gonna make this large right now so we can see those names as we build it and then later I'm gonna shorten that up so that you can look at it if you want to but we'll we'll tighten it up for Rooms sake. All right, so then we're gonna also need the Greek alphabet down below Let's actually list first. Let's list one two three four five six seven now These are gonna be the notes in the scale We're gonna start building the C scale But we want to build it in such a way that it can change if I was to change this one key To anything else that we want to change it to but no matter what we do There's gonna be one two three four five six seven notes And then it's gonna go back to one or eight as it starts over one here representing the relative one note of The scale that we're talking about the major scale in this case So then the next thing we want to do is be able to indicate once we build these scales whether or not we would want to be playing a Major cord around it or a minor cord around it. We'll talk about how to construct those But we usually show that with Roman numerals So we can use a Roman numeral construction here formula for that in Excel is equals Roman and then I'm gonna say Brackets, so there's the there's the function we want I'm just gonna point to that number so it's gonna take that number and it's gonna convert it into a Roman numeral So there we have it now. I'd like to copy that down So I'm gonna copy it down so now we have our related Roman numerals They look redundant, but there's something with the Roman numerals That's fancy because a lot of times if it's a if it's a small Roman numeral a lower case Then that means that you you're gonna have a minor instead of a major So how can now I got to say well, how can I make these? Lower case Roman numerals with our for function and there's a fairly intuitive way to do that I can go into this one and I'm gonna put in here I'm gonna say lower and then so now I've embedded in front of it that lower I got to put brackets around the whole thing and so now I really have two functions the lower function And then the Roman numeral embedded and that'll make it a lower case. So I'm gonna do that for the two I'm gonna do it here lower here Make that a lower case and then the six. I'm gonna make this lower So I'll say lower boom boom and Then this one is diminished. So I'm gonna make it lower and And put brackets around that and then you you also might want to put something else around it So we could use something completely different to indicate that that's gonna be a diminished But it has like a minor third to it So we'll say let's try to put like a dot next to it so we can indicate that that's the that's the diminished So I'm gonna double-click on it. How can I add like another thing here? I can use a text I can tie it to and and then add my text when I add text in Excel You need to put quotes around it. So there's a quote period and then end quote So that might not be the most technical musical way to do it, but that gives me my indication That's kind of like a minor, but then it's got the diminished for it now We can think about well, what does that actually mean? Well, that'll make a little bit more sense once we start to build Build this thing. So we're gonna say we're on the Scale notes, let's do that first. So here's the one two three four five six seven now over here What we had this table represents all of the notes and this table represents us pulling According to our formula whole whole half whole whole half just the notes in the scale So we're looking at these notes in the scale down from Four to three so we want to bring those over here So what I'm gonna do to do that We're gonna do a V. Lookup because I want to be able to find this Number here and tie it to this note So it's actually gonna be an X. Lookup because that's the newer latest and greatest one so it's an X. Lookup and I'm gonna say tab and The lookup value I want to find this value Comma and I want to find it in this group of numbers. I'm gonna say control shift down and then control backspace Control back Didn't do it. Well, should should take me back to the top, but there's that and then I'm gonna say, okay Then we'll say comma and then the the return or what array we want you to return The ones in here So you're gonna find that one and return the four in this case. Let's close it up and enter So there's our four. I want to copy that down So I'm gonna double click on it and anything that's over here in my table. I make absolute So I'm gonna say that's going to be the table stuff this one f4 in the keyboard This one f4 in the keyboard this one f4 in the keyboard this one f4 in the keyboard dollar sign before the letters And the numbers and enter and then I should be able to copy it down So I copy this down and now we've got the four six eight nine eleven one three Which are shown here four six eight nine eleven one three And those are useful to to memorize the numbers for like first because those are the C which are all of the All of the non-sharp and flat notes because we're in the key of C so so that's good and The reason I'm using the V look up and not just saying equal This number is because when we get into the modes and whatnot It might be a little bit easier to use the X look up not the V look up the X look up to make sure we're picking up The right numbers Okay, so then I'm gonna say all right. Well, what what now if I'm on this cord I'm going that's a that's a I so I'm gonna build basically It's a large high or an uppercase one which indicates it's going to be a major cord How do we build the major cord? I'm actually gonna make this black. Let's make this black and white Let's do some formatting font group make this black and then white and Let's make all of this black and white black white and then let's make this one a little bit smaller and We can make this one a little bit smaller And so it's gonna go out like to let's see one two three four five six should be seven of course one two three one two three four five six seven let's make all of these a little bit smaller so that we can See them a eat more easily over here Okay, so now we're going to pick up the notes now when we pick up the notes What we do is we just basically pick up every other note so note what happened here We had all of our notes over here 12 notes We picked seven of them according to our whole whole half thing to build our major scale And now all we're gonna do is go around our major scale at each of these starting points the number one note and Pick every other note because when the notes are too close together You kind of get that dissonance sound so it kind of sounds good now that we have our scale Everything should sound good on the scale, but we usually want to skip every other note to build our chords So that means that if I start at four, it's gonna be four Skipping a note to eight skipping a note to 11. That's gonna be the the notes that we will be pulling in So I could think of that in terms of the intervals. So so I could say alright That means if I look up here, I'm gonna say it's gonna go zero intervals for the first one Actually, I'm gonna put that over here zero intervals for the four or C and then it's gonna go up to and then the next one goes up another two intervals which will be four and Then it's gonna go up another two intervals to six so I can see the pattern here and I'm gonna pull this out Till I get to 12. So that's why this is going to be the relative scale intervals from the chord root In other words, there's four. I'm gonna go up to to not notes, but notes in the scale, right? I'm not talking about absolute notes. I'm talking about the notes in the scale Alright, so how can we do that with a formula down here? We could say alright That means then note that I cannot just say this is gonna be four plus two because that would be talking about kind of Absolute distances what I need to do is say hey look This is the one note in the scale which happens to be a four or a C and I'm gonna go up two notes in the relative scale positions from note one up to to note three in this case and note three is gonna be an eight or an E and so notice I can't just count up two notes because I'm not using all of the notes in the Musical alphabet. We're in the scale. We're counting up relative to the scale now So how can I do that with a formula over here? I can say this is gonna be equal to the X Lookup and then I'm gonna say the lookup value is gonna be this one Plus the two one plus the two and then comma and then the lookup array I want to find that number in here, which is the relative Scale positions that we want to be picking up and then comma the return array I want you to put that or give me the value in numbers over here We'll talk about adding the letters at a later time. So if I say enter then we get an eight That's what we would expect because relative position one here is a four going up two notes is an eight And then we're just gonna do that again We're gonna go up another two notes and get up to the 11 another two notes and get up to or to a three But we need to get a little bit fancy to copy this over. So if I go back in here, I know this I know that these two I don't want these two things to move. So everything that's in those arrays I could say f4 and then f4 and then f4 and then f4 So now we've got those that are not going to move the arrays when I move it to the left or the right Now this one notice that this cell right here Represents this number what I would like it to do is be able to move down But I don't want it to move to the right So that means that I want to keep the letters here flexible But I but I actually I want to keep the letters inflexible So I'm going to put a dollar sign before the letter and I want the number To be flexible because I wanted to move down. So that's what we call a mixed reference now Not two dollar signs, but just one dollar sign on this one by contrast However, we want to allow it to move to the right, but we don't want it to move down So we need then a dollar sign before the number So that we can easily copy this Both to the right and down So I'm going to say okay, let's say enter And so now let's check it out and see if that works if I put my cursor on the fill handle And drag it to the right is it doing what we expect This one is picking up the next one in the series, right? What would we expect to happen every other note? That's what we want from starting from four It'd be four to eight That's eight right here and then eight two notes up to eleven And then two notes up to three and then two notes up to six And then two notes up to nine and then two notes back to A one here. So that looks like it's doing what we would Expect then so when we think about the note intervals in a scale then we name them typically a one a three a five a seven a nine eleven and thirteen and all that means all that we're doing is saying That we're starting on the one note which in this case is A c so when we talk about a chord then we're going to go up in the scale To the three note in the scale so the three note and then the five notes. So these three notes are usually The major three notes that we're going to be working with and they could be either major or minor if we're talking about a major Scale or minor scale now notice when we look at the intervals between here We're thinking about every other note As we pick these up relative to the scale that we are in That means that the absolute distance between these these things will actually differ. So in other words The distance from one to three here Might differ when I get to the minors it will differ right and so that's why we also might want to have The absolute distances between intervals so then we can we can actually think about it in terms of the entire Everything on the fretboard that'll make it easier to kind of see where we are Rather than relative to a scale if it's relative to a scale We have to count up the scale Instead of just counting up the frets on the keyboard, which sometimes is an easier thing to do So so we might then say all right. Well Is there a way that I can show the absolute Intervals between these two well if there are numbers there's a way we could just subtract them Right, so I could say the first one. I'll just say zero But then I could say if this is note eight, which is an e minus note four Then there's an actual four note distance which we call two whole steps when you're talking whole steps half steps So there's a four note distance there and and uh, and we could see that now I could copy that across I could take this one and say this is this minus this But Uh, we're gonna run it. We're gonna run into problems where where if we get a result That's going to be greater than 12 for example or or greater than So or less than one If we subtract these out for example, if I subtract this three minus to eight I'm going to get a negative number. So we have to use another fancy Logic function, which is going to go something like this if If and then brackets and then I'm going to put another brackets and we want to take this eight minus the root of four if that Is greater than zero Then comma what do we want to do? I want to take the eight minus the four And then what do we want to do if it's less than zero? It's a negative number I'm still going to take the eight minus the four But if it's negative, I'm going to add to it another 12 Because if it's a negative number, there's 12 Notes in the musical alphabet if I add 12 to it, it should get us back to the positive number, which should be correct Brackets around it to close it up and enter the four looks correct I'd like now to copy it to the right but to do so I want this number to move as I copy it, but not this number So I need to make an absolute reference of this number, which is in Column a m So there's the a m column. I'm going to say f4 on the keyboard making it absolute Here's another one a m f4 in the keyboard making it absolute Here's another one a m f4 in the keyboard dollar signs before the letters and numbers and enter Then let's test it out putting our cursor on it fill handle dragging it across And it looks like it's doing what we would expect So we've got a zero four seven 11 two Five and nine now note. These are just showing the intervals from the root for Just this first chord So when we get to the other chords What we can do is try to say is it the same interval or is it different? Then the intervals from the root and that that helps us helps me to kind of Think about what is going on with these absolute intervals? It'll make more sense. Hopefully once we start playing with it I know it's a little abstract when we talk about it right now like this way But let's keep on going for the next one And so now i'm going to copy this one is is doing the x look up So if I copy this one down It's going to be the six to the nine So that looks correct and if I copy this one across just to kind of double check it Now we're saying we're starting at the six, right And we're just going up every other note if this was our six So now we're going to go six Two notes up goes to the nine And then two notes up goes to the one two notes up goes to the four two notes up goes to the eight two notes up goes to The 11 and then the three so that looks correct notice. This is why we had to put our Notes in here two times over at least so that we can keep counting up The scale because it's going to basically repeat you can also think of it as like a circle When you're trying to count these two notes up because it keeps on repeating or goes around in a loop We'll create a circle format of it and a future presentation so if I do that again if I say okay, let's bring this one down And then copy it across What are we doing? We're starting at now the 11 in our scale and we're going two notes up to The three two notes up to the six two notes up to the nine two notes up to the 11 Two notes up to the four and that's what that's what's pulling in. Let's do it here. We're now on the four Pulling this across And we're now going to say okay. We start on the nine this time Here's a nine And then i'm going two notes to the one two notes to the four two notes to the eight two notes to the 11 Two notes to the three two notes to the six and then let's do it again Starting at the 11 and copy that across And so now we're at the 11 we go two notes To the three two notes to the six two notes to the nine two notes to the one two notes to the four And then two notes and notice my table is not long enough over here. That's why I should have had at least two more I'm going to add some space down here selecting these two. I'm going to bring it down to 24 so it copies over let's bring it down to 24 that's what I originally had Just to be safe. So bring it down to 24 I think that's further than we need to go but now I need to adjust my tables up top again So now i'm going to go into this one. Let's go into the second one here and see now notice that my Tables aren't extending all the way down on the arrays. So i'm going to pull this all the way down And then this all the way down And i'm on this second one because that's showing this two added and I didn't adjust the first one to do the same thing So i'm going to say enter should have the same number. I'm going to copy it to the left So now that I have a uniform formula all the way across copying it to the right again Now copy it down and we should be good now I'm going to copy it all the way down to the bottom And everything looks like it's populating properly. All right, so then i'm going to make this the headers I'm going to make this black and white I'm going to make this black and white up top and I'm going to make this black and white And then I'm going to put some borders around this bit font group borders Now I'll talk a little bit more about these intervals and stuff Once we build the second part of this next time Which is going to be this time and if there's any errors I'll try to pick them up because we're going kind of long So now we'll add the letters and I think the letters will be a little bit Useful helpful when we start to think about what does this one of these intervals mean? What are the what are the things we're looking at here? So we'll talk about that more once we do the next step and build The same table with the letters in it