 Guitar and Excel spreadsheet creation mapping the path to fretboard enlightenment part number three So don't fret or get bored because using Excel to learn the fretboard is fun 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 may want to begin back there If you do have access to this workbook, we currently have the three tabs down below Example start point and blank example the finished result the end product in essence the answer key The start point are where we're going to list the different starting points as we work through this long practice problem The blank tab where we started with a blank worksheet and are continuing at this point Let's give a quick recap of what we've done thus far The first thing we did was try to visualize where we're going and we did that with the crutch in this case of the Example or answer key. So we're building our fretboard So we started building our fretboard by saying let me just list out the musical alphabet and then number the musical alphabet and then I can combine them together with a letter and a number and Then we built a fretboard just based on the numbers And now we're going to build a fretboard that's going to include both the letters and the numbers the way We're going to do that is used a lookup tool This one has V lookup, but we're going to use X lookup, which is the more modern similar tool in order to to use this information up top and this table We built to pull in this information. So let's see how that will work. Let's go to the blank tab We're getting a little fancy here also just note with this excel on this side We can kind of see where I want to start. I'm not going to start the table right here I'm going to bring it down a little bit. Oh, maybe maybe we'll start it right here, and then I'll move it down Later, so I'll start it right here, but we're going to move it down The reason the table is down here is because it lines up when we start building this part of our worksheet To this area, but we can we can move it later so we can make it a little bit Next to each other might be easier. So we'll start out by just reconstructing The table in terms of the frets. So I'm going to say the fret is going to be equal to The zero so now I can just do that formula. So it'll just pull down the same number I can copy that all the way across to 24 and so now it's just going to always equal the one above it now I could format the exact same way by selecting this entire range and using my format painter Which is a little bit faster than reformatting manually So I could go up here. Maybe I want to center while I'm here home tab Alignment or center. So maybe I want those in the middle now that they're centered home tab clipboard Format painter and then I can just click on this first one and it should format everything Beautifully just a beautifully. Okay. So then what we want to do is is this eight when we built our table over here if I numbered my musical alphabet the eight Corresponds to an E. So I could what I'd like to do is say I want to pull the number from here Look it up in this table and then I could pull into this table Either just the E or I'm going to pull in the E the eight E because I'm going to try to emphasize the concept of learning both the numbers and The note. I really think that's helpful for Numbering the intervals. So we'll talk more about that later But if you don't if you really don't want to do the number thing you could just use the E and pull in the E instead of the eight E Same concept. You can do what whichever one you want now. I constructed a table around this one I think this will be a little bit easier if we do not have a table. So I'm actually going to remove the table so I'm going to select this whole thing and Then up top we inserted a table before and the insert tab I'm going to just right-click on it and you'll see this table area here and I want to Convert to a range. I'm just going to convert it back to a range It's still so then it says do you want to convert table to a normal range? Yes Now it still kind of looks like the format of a table So I'm going to select this whole thing and a lot of times I like to make this light blue for some reason so I'm going to right-click and say that I'm going to format these cells and then I'm going to go into the the Hold on. That's not what I want to do. I was going to go into the bucket up top You can right-click and go into the bucket or you can go into the home tab font group and bucket and Then that blue is what I'll use right there if you don't have it. I go to the more colors standard color wheel and then that blue right there that light blue and Then I'll usually put some borders around it home tab font group and border it So whenever I do data input, that's usually the what I would I use so now it's just a range Okay, so how can we do this V lookup thing? So we do it like this I'm going to say this is going to be equal to an X lookup now if you've used the V lookup This is a set this is like the latest and greatest V look it It basically has more flexibility than V lookup so it replaces V lookup and almost everything at that most uses that you would have for a V lookup you probably will now Be using the X lookup although you could still use the V lookup if you want the problem with the V lookup is One problem is it's a little bit restrictive in terms of having to label the tables And the columns of the tables where you don't have that restriction here. So we have the X lookup So what's the lookup value the lookup value is I want you to find this? 8 I want you to find that number 8 and then comma and then look up array Where do you want to find that number 8 in other words? I want you to find it and this series and this range So we have that range So that's what we have thus far and then comma to get to the next argument. What what do you want to return? Well, when you find this 8 I want you to give me this 8 e So I want you to return whatever's in this column return whatever's in that column and So there's our lookup formula you could close the brackets It'll probably do that for you if you just hit enter and then there we have it So it pulled in that 8 e that's great Now I could try to copy this to the right and it gets messed up Why because we have to use absolute values of some kind so if I double-click on this I can say this Cell right here. I want it to move both to the right and move down When I when I move from cell to cell but these two which are referencing the arrays I don't want these two arrays to move at all Therefore, I'm gonna make them absolute. I'm gonna put my cursor between the a and the to the a a to and say f4 Stop moving stop wobbling around. That's why you're in a a you got to stop like Shifting all the time and like swaying back and forth from anyway So then so now we've got we've got then Dollar signs between each of them the dollar signs, of course not having anything to do with dollars But the code for excel to say don't move those cells when I go to the left or down To the right or down So I'm gonna then copy it if I copied across now testing did that do what we wanted to do 9 is 9 an F Let's check it out in our table if I was to count up in our musical alphabet a a Bcd. No, that's not how it goes. It goes a a sharp or B flat B C C sharp or D flat D D sharp or D flat E F and Then F is 9 F right there it is so see how see how the musical alphabet doesn't really conform to the alphabet song Making it more difficult to memorize Whereas the numbers still conform quite nicely one two three four five six seven eight nine ten eleven twelve Twelve eleven ten nine eight seven six five four three two one. What if I was going from four to nine? What's the difference? Well, I can do a little math there nine minus four if I just pull my fingers out I got nine fingers minus four fingers. That's like five away instead of being like well F C to F is Like F and then E, but then there's no sharp to D See see how that intervals easier with numbers anyway, I Just think it's way useful to do, but I know that people will think well, yeah, it's cuz you're an accountant You're weird. You're weird man But whatever dude, I'm telling you. It's not just that I'm weird I think this is like a real thing for everyone And so now you can see it's a little squished up here, so I want to widen it out a little bit So I'm gonna select from a all the way to why why because that's just how it lined up That's why you go up to there because that lines up to the 24, but why But why I just said why I just said why that's where we go because I said so that's why Anyways, so then we can copy it down to I can test it out and copy it down this way And that is an a does that line up to number one? That's the first note number one is indeed an a if we were to wisely number them by the way If I just note that I am building this Which again some people might see as backwards because remember that the guitar if you were to take the guitar and Look at it and flip the guitar around and look at the face of the guitar You'd have to flip the neck around and I think that's why they put the small string up top when you're looking at tablature But again, I don't think that's how people envision the guitar just intuitively Because Because you're usually looking over the top of the guitar So I think like we're building it this way So it won't it won't it won't line up to like tablature this way But I think this is the way most people kind of envision it and once you get the fretboard in your mind Then you can kind of flip it around backwards and try to think of it like if it's upside down But I really think that most people think of it This way with the highest or lowest string on The top if you I think that's the easiest way to envision it It's not like the coolest way for like music theory people are gonna might like put their nose up at you and call you a like Uncivilized maybe But whatever it works. I'm telling you with the numbers too. They might be like numbers man numbers That's not that's not in the arts. That's not what the arts We're trying to create You know like whatever the numbers are you math the numbers man? You need the numbers. Anyway, home tab font group drop-down black white and Then we'll do that here black white and This way and Then home tab font group black white. So now we've constructed our fretboard Over here and we can see it we can see it either way we want now notice once we do this How much cleaner it is if you were able to just see the numbers, right? Even if I took out the numbers down here, and I just had the letters it would still be kind of convoluted It's a lot if you if you're actually able to see the all the notes as numbers Then it's really a lot easier to to look at this table and kind of start to see what's happening with the patterns and whatnot but You know, I It's you know, it's it's useful to use both ways I think because again, there's pros and cons to both the letters and the numbers. I'm not saying, you know, the all music History's Western music is stupid for using letters or anything. I'm just saying it's useful to do both There's pros and cons to both sides of that. Alright, so next time what we'll do is we'll then Create from this Table we will then create this table which which will then which will then take our musical alphabet and use Use the pattern of a major of a major in order to then create our table Which will allow us to see the notes in a particular key as well as The chords to play and all that kind of stuff Which will be great and we'll be able to change the key that we're in by changing this one cell so and then we can see this stuff side by side with our fretboard and Really be able to visualize What we want to play over here and then map it out on our fretboard over here And of course we can hide the cells to see them side by side to do to do it very nicely