 Guitar and Excel spreadsheet creation mapping the path to fretboard enlightenment part number one 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 work book That's okay because we'll basically build this from a blank worksheet But if you do have access there's two tabs down below example and blank Example being the finished product that completed work the answer key If you will the blank tab having a blank worksheet where we will construct our project from scratch From the blank worksheet Let's go to the example tab to get an idea of what we will be constructing as well as why We might want to construct it There's two main objectives that we have here if either of these objectives are met This may be a really good project for you one objective is just to hone down our Excel skills so we're going to be using a lot of tools Within Excel as we construct this worksheet, and it's a great project just for that The other is that we want to construct a useful worksheet Which can help us out with basically our music theory as we play mainly the guitar But you can also apply it to other areas as well So let's take a look at those two objectives So the first one the formulas that we'll be using and the formatting that we will be putting together in Excel Note that we of course are going to be using some Formatting in terms of what the cells are going to look like we're going to be numbering the cells here We'll use some series in order to number the cells we'll use some table formatting tools that are quite common and Then we'll format the headers and so on in the cells We'll have some more complex formatting and formulas such as Text type formulas well, which will happen help us to create a text type formula as we do this We'll have a lot of logic formulas here as well You can see this looks like a pretty intimidating looking formula Which has an if type of logic progression and so we'll have formulas like that that will come up fairly Often we've this is a Roman numeral formula that we have here This is a V lookup although we're going to be using the X lookup most of the time in our project We probably won't use the V lookup because it's kind of outdated for what we're using it for the X lookup Is the is the better tool typically we will use again another kind of logic formula that looks quite intimidating But is is not too bad once you wrap your mind around it We will also see how we can tie the worksheet together be able to copy and paste the worksheet So that we can kind of have similar formatting But have a different process that we're going to be using you can see we have more kind of somewhat complex formulas here fairly complex formula here to get our to get our Roman numerals So there's a there's quite a bit actually of of just Excel tools that we're going to be putting together And even if you're not all that interested in music theory or the guitar in particular It might be useful to do that in the fact that it has some music theory related to it might make it a little bit more Interesting as well the second objective on the music theory type of front We're going to actually create basically a fretboard here So here's the fretboard and I've got the the the the numbers or letters of Each note on the fretboard so we'll construct an actual fretboard here You'll notice that I have a number and a letter when I construct the fretboard I'm doing that intentionally because I Feel like if we add a number in there I'll try to convince everyone of this But you don't have to do it this way if you don't want to but I feel like if we number the notes There's benefit to that so that's one of the arguments that I'm going to be making as I as I go through this I'll get into that in more detail shortly But the general idea is these if we Visualized the guitar this high would represent the string at the top the E string And this would be the heavy or low E string the one closest to the ceiling and then we've got our notes E a dg be this is the high pitch E string the string closest to the floor now note This is the first thing that I think is Really useful especially to beginning guitar players that haven't had a lot of time looking at tablature or or music formal music because A lot of times they actually write the fretboard with the high note on top So and I think the reason they do that is because if you were looking at a guitar and you were looking at the fretboard like this You'd have to rotate the guitar around And you would be looking at it this way and if you look at it that way the high string Is on top which makes sense But that's not how most people visualize it. I don't think I'm a little dyslexic So maybe I mean tell me if I'm wrong here, but I mean I think most people are looking over the top of the guitar So they're actually kind of it's easier for most people to visualize the low or heavy string on top When they're trying to map out where their fingers will go. So I'm going to construct it like this So that you can kind of imagine the fretboard is being pressed On top of the on top here so that we have the low string on top And we're kind of looking over the top of the guitar It's kind of like you're looking through the back of the guitar because that's where your perspective is generally When you're playing the guitar, that's the that's the idea If you want to construct it with the high string on top you can You could just flip it all around if i'm just a dyslexic weirdo and you know, it's quite possible I've got some kind of but you can flip it and put the high string on top and that's kind of what most tablature looks like But I think again, I think intuitively a lot people kind of do it this way So we'll map out the fretboard. So now you have a fretboard that is mapped out And then notice it's going up to 12 frets. This is where it repeats again. That's why these are in black So here's the 12th fret that will repeat again. You might not need all of this stuff up top You might hide some of the cells, but sometimes it's useful just conceptually to have it run two times over So you have it starting over at 12 starting over at 24 sometimes conceptually That is useful if you want to hide some of it. However, I could put my cursor from Column m to column y and we'll talk about how to do this more later I'll just do it quickly now to show you what i'm talking about. We can hide this And now you've got just from one to 11 right before nothing doesn't start over So if you want to just if you just want to work on what you're looking at You can hide the fretboard and then I use this little tool to see where my finger position Is on the fretboard. So if i'm working on you know fret 5 to To 8 then I might put this around there then we'll actually construct Our our our major and minor scales over here and the way we'll do that is you just list out We'll just list out the the notes from one from a to g sharp And this is where i'm going to start to talk about the numbers here because Notice in in western music We obviously talk about the actual notes with letters and there's some benefits to that and there's some cons There's some pros and cons to that the bend one of the benefits are That you have something different than numbers that we're going to use for so many other things right We're using numbers to talk about like the relative positions in a scale and the chords and the scale And so you know that so there's a lot of things we use numbers for already So it's nice to be able to use letters for something so that we don't have to say which number are you talking about Are you talking about the actual number of the note or the number of the scale or the number of Of the interval or you know or what are what are you talking about? so So that's nice and it's kind of nice that we can build actual uh whole Whole scales with every note in the alphabet So the major and minor scale will have every note in the alphabet Although it might have sharps and flats in it which kind of messes up that whole concept So so you got every note, but then there's sharps and flats in it. So you've got this kind of interesting Interesting spelling of notes because of that Which again has pros and cons related to it some of the cons with that system however is If I if I count if I just count from a and I don't even worry about the sharps and flats If I go a b c d e f g up to g I can do that But trying to count that backwards is a little difficult and oftentimes you have to do that in music. So if I'm trying to count g f d c b a You can memorize that you might say that's not too bad But it's still a lot harder than going one two three four five six seven eight nine ten eleven twelve And then 12 11 10 9 8 7 6 5 4 3 2 1 right And then if you add the flats in there it gets even worse because now the alpha you can't even say the alphabet You got to go a a sharp or b flat and then it's a sharp or a flat But when you're going up it's usually a sharp So a a sharp b c and then there's no sharp between b and c But then there is a sharp c sharp to d and the d sharp to e and then there is no There's no sharp between there's no between e and f and then you know right And if you try to go backwards that way Then you got to remember that they're flats and not sharps possibly if you want to say it correctly And you have to memorize it backwards. That's quite difficult For most people that takes it, you know, it's worth doing But it takes a lot of memorization Whereas again, if you just memorized the notes like if you just name note a one a sharp two b three c four c sharp five d six d sharp seven e eight Then you would have to memorize the association Of the number and the note But once you do that it's so much easier To then count backwards Right and the sharps and flats don't have two names So you want to so you kind of want to know both methods because there's pros and cons to both of them But I highly think it's useful To learn that to actually number the notes. It's also helpful for our excel worksheet But I think just in practice that's why so that's why I'm going to then have the letters the numbers and then I'm going to add them together And so we have both of those And then I'm going to map it out over here using using Our whole whole half kind of progression scale And that's our going to be our major our major scale What defines a major scale and we'll start with a c sharp Well, I mean sorry, we're just a c and we'll map this out and then we'll create Our little worksheet over here Which will say this is in the major or ion in major scale for c sharp And you'll have all this information Related to that scale. So we've got then The notes now these notes are numbered without the letters So I'm going to construct this for two reasons one It's actually a little bit easier to look at notice Like if I look at this and I know those numbers and I know them as notes It's actually a lot less convoluted than looking at this which is quite messy So so that would take some memorization to do that But but it all it also helps me from an excel worksheet to then create this one Which has both the number and the note You don't need the number if you don't want the number if that if you really don't want to do that That's fine, but I highly recommend learning the number personally. So now you've got uh the c The the the the scale here And then and then you've got each of the the chords in the scale And then the roman numerals will give us Whether or not if you're playing in the scale of c major You should be playing each of the notes in the scale are going to correspond then To a chord and all you have to know is well Is it of a major chord or a minor chord that i'm playing in the scale? And these roman numerals if it's if it's a capital Or uppercase Then it's going to be A major scale that's the convention that's usually used if it's lowercase then it's minor So if you're playing a c In the c major scale you can play the chord of a c major Then you move to the two note in the c major scale Which is a d and you would be playing a minor then you'd play the e And it would be a minor the f goes back to major And then the g goes to major and then back to minor for a and then The diminished which we'll talk more about later Which a lot of people kind of just drop off when they're first learning it which might be good Just when you're first learning it so you can say when you're playing a scale it's Major minor minor major major minor diminished Okay, so then and then we'll also have the intervals in here And this is where again the numbers are so useful because if I say The third note in a c Then it's it's kind of like we think about that as a as a as a major third And that's basically You know two whole steps is how we think of it And then you got to know what a whole step is and whatnot But if you if you just say well, how many notes away is it it's four notes away And if you number it then it's eight minus four Right if i'm on if i'm on a c What's the third of c? Well if i see the c as a four Then four notes away is four plus four or eight And the eight if i number my notes is an e right and the fifth is always Seven notes away. It's a seven note away fifth. It's a fifth note in the scale right one two three four five There it is right there, but it's seven notes away when i look at all the notes And i'm not looking at relative position So it's always seven notes away so four plus seven seven eight nine ten eleven Is that right and so it's so much more difficult to know those intervals if you don't use the numbers Because then you're going to have to count on your fingers and deal with the the the the Sharps and flats to try to say okay c d And wait no c c sharp d and then d sharp and then e But then there's no sharp and then f and then g and then g right you'd have to count that on your fingers Whereas if you knew the number c is four and then i'm just going to go up seven Seven plus four is eleven and if you know that eleven is a g those intervals are so So much clearer. It's ridiculous now then and then you and then we could also work on the sevens The nine the eleven and the thirteen and look at those intervals as well But the three these are the main three then we can also see this in terms of a Of a circular Progression here. It's useful to do that because when we build scales we usually pick up every other note So this will help us to kind of visualize if we're trying to build our build What chords we're looking at and then it's useful for us to do this to to then say what the next thing is so First before I get to there notice if you're over on this side and you're working on something in here Then I can have these two things side by side. I can hide for example column z To to let's say aj hide those And now I've got this these two things side by side here And I can make this as large as I can make it and then I might want to highlight something in here Maybe I want to put the scale in place So I might then usually what I would do is I'd say let's go ahead and format this I'm going to go into the home tab format painter. This is a little tedious, but it's really useful I can say I want to make everything in here in conformity with the scale So I'm going to say if it equals a C sharp Then I want you to make it. I'm going to say more colors. I want you to make it fill blue And then if it's and then I'm going to say format if it's equal to this one I'm going to say I want that one to be blue as well. Let's go format blue boom And then if it and then I'll say okay, and then if it's equal to I'll say this one then I want you to format it blue as well And there's the e and then I'm going to say if it equals This one. I want you to format it as blue as well And then there's the f and then we want the g so I'm going to say equals this one format it as blue and then a equals format this one as custom blue boom one more round equals this one format it custom blue and that gives you your fingering position so if you so so now you can Here's your classic fingering position the pentatonic scale is in here as well as the major the major Court so then I can say Well, if I'm looking at this position that I can start to memorize I would call this position one which we'll talk about later But it's you can call it the major scale the pentatonic scale is inside of it And so is the chord so I can I can then start to say well, where's these three notes once I have this scale mapped out These are all the notes In our scale I'm going to do another conditional formatting. I'm going to say this is equal to this one the c Oh, hold on a second Cancel that let me highlight this first I'm going to say conditional formatting equal to this one and I want to make it green So I'm going to make that green and then I'm going to say conditional formatting Then this one I want to make it Uh red and then conditional formatting this one And I want to make it yellow Right and so now now I've got all the chords in the scale and I can look for The the ones I've got all the notes in the scale I can look at a particular position Where my fingers are at and try to see if I pick up each of these colors. I'm picking up a C major right and I could I can I can Pick around meaning I can just pick and play different things that are in the scale and that should sound good And then land on the C major which would be holding One of you know each of these colors right and then if I wanted to switch to the next note Then I can switch these colors To here right and that's a little bit easier to do I can say okay What if I what if I go to my rules and I say now I'm moving down to this one So I want to make the highlight. I don't change. I don't need to change the blue ones I just need to change The the colored ones of of red green and yellow so I could say okay Let's manage the rules. So now I just manage the rules once I've got them in place and the ones on top I just need to change these three So now I can change the new yellow one needs to be a so I'm going to change this one And I'm going to move it from here now. It's going to equal not this one This one so now we've got a For that one and then this one I'm going to say this is equal to Uh this one now and then I'm going to say okay and then the green is going to be equal to This one now I'm going to say okay And then okay And now you've got the the shift of the position and you're still in all all the notes are the same But now we're hovering over the ones that are highlighting these three and you can see how That can be really useful when you're trying to play this figure it out on the guitar And if you have the numbers in here again, you can kind of count the intervals as your finger in this Which will hopefully do in future presentations. All right next thing. I'm going to unhide These cells. I'm going to right click and unhide now if you're switching positions Then I can just change this one note So right now I'm on a four Which is a c if I want to go to an a I can just put like a one and that'll switch everything around So now I'm having a so now the a this is a major instead of c major And I can rotate to whatever I want if I want a g G is 11 And then I can say boom now i'm doing everything in the key of g and everything Changes automatically, which is great. And now the other thing that is nice is that if i'm in the key of a Uh, let's say c major Then I might want to try to say well What can I borrow from other keys to play and see major or Can I switch from from playing in one key to another key? Which is a common thing to do, right? I'm playing all these notes in this key And all these chords I have all these opportunities But what if I want to like shift to a whole another a whole another scale How how could I do that? Well, there's two major kind of ways you would think One would be well, I could go to another Scale that has all the same notes in it But now I'm not but now I'm not kind of hovering around the c as my central point But rather something else and the other way you might do it is to say I'm going to use the c here as my pivot point is what I would think of it And then and then use and then go to different scales But still hover around this as my central point now from a theory's perspective I think the first method is easier So if this is a c major then the the most common thing to play with is going then to a The related minor the related minor Is an a so then if you have the a next to it now we can switch to the a and and obviously we can put these side by side I can hide all these cells And say right click and hide And and now I can look at the a minor next to Next to what I'm doing over here, right? I can put my a minor next to my worksheet and Actually, I want to make it over here. I want to make it all the way to To here next to my fretboard So I can right click and hide And so there we have it. So now I've got so now I've got my a minor next to My fretboard that I can work with that way. And so That would be quite that could be quite useful because then I don't have to I don't have to then go up top and switch this to an a minor up top Uh, you know, I don't have to switch anything up here. I'm just going to say I can just look at the relative Miner so I can think of myself In the key of c But then just looking at the relative minor, which means I'm really kind of focusing in around the a minor Now the other modes are basically the same thing. I can then say the other modes dorian I can do the dorian mode, which is the same The same scale, but now I'm focused around a d and then I can go into the Frigian, but now I'm focused around the e same notes in the scale And then we can go to the lidian and The mix of lidian and the locrian, right? So we could so we have all those kind of mapped out But another way that so that's theoretically the first way I think most people kind of intuitively understand the modes And how they fit together and we'll talk about that when we create the sheet But I think what helped what's also quite useful when you're playing Is to say I would like to use I don't want to switch from a as my central note I want to use it as the pivot point and then find the relative The relative notes around it Let me go let me go to the major. Let's go to c Let's do this with c so I now I want to use c as my pivot point And I just I want to I want to find other scales with c as my pivot point So the notes aren't not going to be all the same in the scale, but c is going to be my root or tonic So if I go down then now I'm in the minor. So now this is a c minor Uh, so it doesn't have all the same notes, but now we have the same pivot point Of c now that we're paying so now we're switching from a c major to a c minor instead of going from a c Uh a c to the relative minor, which is a minor and then again And now I've repeated my worksheet down here so we can have the fret board right next to it So I can move from this fret board to this fret board and I can put that right next to The the minor scale so I can now map that out And then after that we've got the dorian We've got uh, these are the two dorian the phrygian And then the lidian and so on all with the same C as the root note now So then so then that's kind of nice because again You don't have to switch everything up top and here when you're switching from one scale to the other Because uh, you'll be able to at least map it around so you can imagine for example saying all right I'm going to do something crazy I'm going to start in the key of c and then I'm going to try to pivot from the key of c to The key of a and I can just scroll over here and see The key of a and the relationships if I'm thinking about a is the root and then I'm going to try to move from a as my pivot point Down to an a minor An a major So now I move into an a major instead of back to the relative major of a c major right And you can start to kind of maneuver around where you're wherever you want to go into different scales That way you can use those two methods to kind of move in and out of scales You can say I can either I can either move to to a scale with all the same notes in it Or I can try to use the root note as the pivot point All right, so I think those are the main some of the main benefits of Of the worksheet. So now next time we'll start making it