 Guitar and Excel spreadsheet creation mapping the path to fretboard enlightenment part number two 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'll basically build this from a blank worksheet But if you do have access, there's two tabs down below example blank example being the finished product They end result the answer key if you will the blank tab having a blank worksheet Where we will build the practice problem from scratch from the blank worksheet Let's go to the example tab to get an idea of what we will be doing and why Remembering that we have two major objectives the first objective to practice our excel skills in a practical problem The second objective to practice our music theory and put together a worksheet that can further help us to practice With our music theory so we're gonna start out by building the fretboard So this is the fretboard which has both the Key or the note names on it as well as the number of the note which we discussed a little bit in the prior presentation when we went over the Overview will also make a fretboard which just has the notes as numbers which helps us from an excel standpoint But also I think can really help us from a music theory standpoint We'll talk about that as we go as well in order to build the fretboard We will first think about just listing out our musical alphabet and then see how that will kind of fit on To our fretboard and that'll give us some kind of basic understanding of the fretboard So let's go to the blank tab to build this now note I'm gonna first build this part in The first column and then I'll move it over to give us room to then construct the fretboard from it So let's go to the blank tab now the first thing I usually do is format the entire worksheet for my baseline formatting that I want to be using so I'm gonna select the entire worksheet I'm gonna right-click on the worksheet, and I'm going to format the cells And then I'm gonna lay down a baseline formatting. I usually use currency I used to make the negative numbers Red, but we probably don't want to in this case So I'm just gonna put brackets around any negative numbers instead of a negative sign Then I don't think there will be any negative numbers I'm gonna get rid of the dollar sign because we're not talking in dollars I'm gonna get rid of the decimals because we don't need any decimals. I'm gonna say okay I'm also gonna make this a bold by going to the home tab notice how how I format When you discuss where you're going in Excel you discuss the location by the tab home tab and then by the group Fonts group and then by the item in the group bold in the font group So when you're trying to explain to someone over the phone or something or Skype or something like that You know, it's easiest to say home tab Fonts group and then bold. Okay, so then we're gonna go down here and build our table So I'm gonna say our table is gonna have first the numbers I'm just gonna say numbers and then I'm gonna say letters I'm gonna abbreviate this and then I'm gonna say both in other words I'm going to try to list our musical alphabet by letter And then I'll number them and then I'll add them together with a fancy Excel tool So let's highlight these three I'm gonna go up top and we'll say that we want to say this is going to be Fonts group home tab font group bucket drop down I usually make my headers black and then on the letter drop down and white I'm also gonna center it. So I'm gonna go to the alignment and Center it and so there we have it and I'm gonna scroll in hold control and scroll in you can see the zoomer down here I'm at three hundred and ten on the zoom in I'm gonna start in the middle because we usually think of our musical alphabet as letters And we go from A to G However, we've got those half steps that we have to worry about as well So we have if I make them all capital we've got the a and then We've got the the a sharp now I'm gonna skip it for now and just say the B is gonna be down here Then there is no sharp between B and C. So then it's C and then there is a C sharp So I'm gonna skip it and say D and then there's a D sharp So I'm gonna skip it and say E and then there's no E sharp So I'm gonna just say this is gonna be F and then F has an F sharp So I'm gonna skip it and say G and then there's a G sharp and then it goes back to a again Then the pattern repeats so you can see our alphabet Looks like this if you were to remove the sharps and flats Now then you have the sharps and flats the problems with the sharps and flats is that you're going to To name them as either a sharp or a flat so you can get to some fancy Notations on how you're going to do that I think the easiest thing to for me to do here is to call it an an A B and lower case That's what I that's what I do here because the a means that you can call it an a sharp It's a small a that's what I mean by this This isn't the this might not be the most accurate the way that music theory people Represent it, but it works well for me. It works well for my worksheet. That's what I tend to do So if you're typically if you're going this way a to B You would say a a sharp B and if you're going this way you say B B flat a and there's reasons for doing that when you start to kind of map out notes Because typically we don't want like two a's like an a and an a sharp In the same scale or the same note or the same cord oftentimes So if there's an a and an a sharp we will typically call it an a and a B flat, right? That's the reason Why why you have that in there and that could be a useful Tool although confusing and when we number it then we lose that kind of nuance to it Which we'll see shortly, but in the case this would be between C and a d so it's either a C sharp or D flat This is between a D and E So I'm going to call it lowercase de it's either a D sharp or an E flat And this is between an f and an e so it's either an f sharp or a g flat And then we've got this is going to be between a g and an a so I'm going to say g a and There we have it now. It's useful to actually repeat this at least another time and That'll help us that'll help us as we'll see shortly So I'm just going to copy this entire thing Selecting it right-click Copy and I'm going to paste it right underneath it. So now I've got the musical alphabet Happening just two times over now It's useful for us both in excel for excel as well as for musical terms I believe to name to number our Our notes because it's so much easier to count the notes and numbers So for example, if I just number them from one two three four five six seven eight nine ten eleven twelve That's all the notes that we have right so and so if you start to memorize a is one A sharp is two B is I mean B is three C is four C sharp or D flat is five right and then a D is six D sharp is seven and so on is eight f is nine f sharp is ten g is eleven Then then you can start to use these numbers when letters are Inconvenience because the letters are convenient sometimes when we're trying to spell out certain chords and with the sharps in the flat But if we're trying to count intervals for example Then the numbers like if I'm trying to say well how far away if I'm in the key of a is a D from the root note Well, if I know the number of D is six and I know a is a one Then I can use simple math right I could say well six minus one is five right right as opposed to if I know it's a D and The a is a is just an a I have to count it up on my fingers and say like a and then there's a sharp So a sharp and then B But then there's not a sharp between the B and this so there's C and then the C sharp, right? So that's kind of a lot. So the intervals are way easier If you are can memorize the numbers and you're gonna have to memorize a ton of stuff anyways So I feel like memorizing the numbers is is Really really helpful. I don't know why Like it's not emphasized to do so because I find it to be just amazingly helpful But any case then I can combine these together like let's say I want I want these in one cell I want the a to be represented by number and letter in one cell I can use a pretty neat formula equals and we're going to say this equals This cell and then I'm going to combine it with an and an and and Then this cell So now it combines them together it's kind of a text type of thing here But we combined it together right with an and I think of an and in excel as a knot, right? It's tying two things together looks kind of like a knot and so then if I put my cursor on there and double-click it It'll copy it all the way down. So now I've got it tied together I need to repeat this one to twelve. So I'm gonna select one to twelve copy that and paste it right here One to twelve there. We have it. So now we've repeated the musical alphabet and letters I mean in numbers and letters and then combining the letters and numbers I'm gonna insert a table the tables can be useful sometimes. They're not always You got to decide whether or not when you're using excel It will be beneficial to add the table and this case. I think the table is useful So I'm going to go to the insert tab I'm just gonna put my cursor somewhere in here selecting only one cell at a time and then I'm in the insert tab table group selecting the table and This then is selecting our table. You can see the range a1 to c25 That's selecting the entire range. So they call those the dancing ants That's what some people call them. That was the the funny name people gave them Around there they're doing their dance around there and then we're gonna I'm gonna select these now and make them a little smaller So I'm selecting all of the rows up top put my cursor in between any one of the three of them So the cursor looks like that and then I'm gonna drag it left click and drag it on in and so now you've got a pretty tight Format so now what I want to do is I want to move it if I go to my example tab I want them. I want to I want to start building my fretboard with that Theory now, so I want to move this so I'm gonna move this all the way to Column a a so let's go back on over so I have room for my fretboard So I could do this a couple different ways one is I can insert Rows to the left so if I select the entire column here and I go to the right and I right click and Insert it's gonna have to insert the columns to the left Because that's that's the way Excel works. It always inserts to the left So I'm gonna say okay insert so now you've got it over here and then The other way I could do it is I could try to select all of my data here and pick it up or cut it You could drag it like this I can put my cursor here and then grab it and drag it That's actually the same thing as just saying right click or control x instead of control C cut and then move it over to a a so way over here a a you're going to alcoholic anonymous Table because you've been causing trouble you've been causing trouble you're gonna hurt someone if you don't get some help All right, so when oh so now the tables over there and a a I don't know I don't know why it's in a a but so we're gonna say All right, so now I'm gonna make my fretboard So let's make my fretboard and so the fretboard starts at zero fret I'm gonna say that's the zero fret and then the first one that we put our finger on is fret number one fret number two and Three and so on now excel can of course see that pattern so if I select those four cells and I put my cursor on the fill handle and Then drag it to the right. I can bring it out to twelve frets. Remember. There's twelve notes in In in the the the musical alphabet ate it to G sharp Then I'm gonna make all of these a little thinner So I'm gonna select from M on over to the a and let's make them a bit thinner So there we have something that we can work with so so now I'm gonna make this a header type So I'm gonna select all of these and make them black and white as I typically do with my headers home tab Font group know the bucket drop down black number and then white Okay, and then this you might like want to indicate that this is the zero fret with a little bit more emphasis We could make it red. That's what we did over here or I think I'll put a box around it You could like make it bucket red just to show. Hey, look, that's the that's the open Strings so then we can number I'm gonna do this with numbers first and then we will we will add the letters So the the frets are E a D G be Now if I look at those in terms of numbers over here, I just numbered these So it's gonna be all right the frets and an E is an eight and a is a one a D is a six a G is an 11 and A B is a three So I'm gonna I'm just gonna put these that's that's gonna be the number related into the eight one six eleven Three eight now that's gonna look funny at first if you don't understand this whole numbering system And we will put the letters in there shortly but I just want to start with the numbers both for Excel purposes and Because I really think it's it's cleaner actually to look at now I'm also gonna make this kind of like it's a header Because it this these are the open strings you might ask Why do we have it at the open strings in this format E a D G B E and you know that gets into other You know music theory questions. Why did they why did they I mean it works quite well in this format? You might think it's a little bit odd Like what what are they doing here? But it actually works quite well with your fingering system to try to be able to reach Whatever you need to reach in essence within four fingers. You've got four fingers So within four frets We would like our fretboard to be arranged so that we can kind of reach as many things as we can within like a four to five fret span and this this fretboard alignment actually works quite well on that so That's my general Explanation for that as of now, but we'll just take that that is what it is. That's what the Standard tuning is so home tab font group and Make this black and white Okay, so now once we have that Then then I can just continue counting this out because it just goes if that starts at eight then it's gonna go to nine ten eleven and then twelve and Then it goes to a after that which is actually a one So I could go through here and just list this out right and then I go one two Three but we can practice using fancy formulas to do this now if I one way you might do it is you might say okay Well, wait a second. I can take these three and start to do this But that doesn't work too too far because then it gets messed up right here because it has to go to an A right there or Another common method that I often use is I'm gonna say I'll take the prior cell And then add one to it which will work in a series because then it's just gonna take the prior cell and add one So now it's just taking the prior cell and add one which is a good count But you run into the same problem and that is that this should go down to an A So is there any way any cool way? I can do that with a with a formula there is and there is we can use a logic formula Now you don't have to do it this way, but we can do it this way and it's good practice to do it this way It's also good practice to be able to recognize when some tools such as this can be used That's often part of the problem or part of the issue when you're working in Excel You may know the tool, but you not might not recognize when it could be applied So I'm gonna put my cursor here. We're gonna say this is gonna be if this is our logic test Basically telling Excel if this is true. This is what we want you to do if this condition is not true This is what we want you to do So we're gonna say if Brackets and the logic is gonna be if this cell is less than 12 because there's only 12 Notes, so if it's above 12, we're gonna need to do something different But if it's below 12 then comma, what do we want you to do if it's below 12? Then we just want you to take this cell plus One which will get us to 10, which is what we want this time However, what do we do if it's not less than 12 if that's 12 or above What we want you to do then is take that cell still Plus one, but then we're gonna subtract 12 from it Why are we subtracting 12 from it because there's only 12 notes and there and that'll get us back to where we should be Right, which is gonna be like a or one So let's see if that let's see if that works I'm gonna close this up and then all those cells are relative So I should be able to copy this across putting my cursor on the fill handle copy it to 12 and It goes eight nine ten twelve and then back to one just like we wanted it to do now We might have to modify this when we bring it out to 24 frets But for now that's working. Let's do it again down here. Let's just practice that again This is one the next is gonna be a two and then a three and then a four and so on Let's just go to go to right here and then do my logic test My logic formula again to practice that this is going to be equal to if brackets The logic is going to be this if that is less than 12 then comma what we want you to do take that cell plus one Comma, but what if it's greater than 12 then then we want to take that cell plus one still But then subtract 12 from it which should get us back to where we need to be closing it up and Copy it across So there we have it so it looks like it's doing what we want two three four five six seven and nine ten eleven twelve back to one Now I should be able to copy this down as well So so I'm gonna say I should have put the formula here. I'll say this plus One and I'll copy that all the way down and then I'll see if I can copy these all the way down So I'm gonna select all of these copy them down and the formula is now being applied and it looks like everything is working the way it should now this I'm gonna I'm gonna highlight this as black and white because we're back to our Our repeating place right so we've we started here and now we're repeating again when we're on the 12th fret so then Let's say we want to go up to 24 frets those because now I might want to go up higher than that because even though it repeats Again, we might want to go higher. So let's say we go to 13 14 and so on I'm gonna select those two and then drag out to 24 there's 18 20 For it goes out to why I'm gonna select all of my columns again and try to make them the same length or or width Changing the width just a bit so that shortens the width on all these Let's I'm gonna format paint this time putting my cursor here Going to the format painter home tab clipboard group format painter and then I'm just gonna paint that black Formatting which could be a little bit easier than the method we used before and so now if I if I keep copying this I could do this to a couple ways. I could say well 13 is gonna repeat so I could say this equals this number like the nine and Then copy that across and it'll copy the relative cells Across that's one way. We could do it or We should be able to just continue this format and copy it all the way across as well and that formula should still work and and you can see it repeats again so I'm gonna select these and copy this all the way across and Then I'm going to once again format this part as Black and white to show that that's when it repeats and then it's gonna repeat again at 24 so as kind of a double check. We should have the same numbers here here and Here and of course the same logic would be that I should have the same numbers here as Here and I should have the same numbers here as here I should have the same Seattle. Everything is gonna basically repeat So really what we need to memorize of course is in essence 112 which is not easy Really a lot of work to do but that would be that would be 112 and then we can repeat it which is get it's is Once it repeats It's still not easy because you don't have the open strings anymore and you're playing on a different place in the fretboard So you might think that you could just get this down and that would be it if but when you put your fingers actually on things You deal with the open strings, you know, you have to do your muscle memory But in theory it just repeats man I'm gonna select this whole thing and then go to the Home tab font group Dropping it down and put some borders around it All right, and then I'm gonna hold control and scroll out a little bit just to see this is what we have thus far Let's make this one a little bit thinner And now what we'll do next time is we'll use this to now make another fretboard But we'll add our both the numbers and the letters So and again, look notice how much cleaner this one is if you could see it with just numbers It's so much cleaner to look at it looks like an a soak What do they call that game with just the the cards and you got to get up to not the numbers in the cells? It's gonna drive me crazy I don't know but in any case it looks a lot this looks a lot more chaotic with with the letters Even if I took the numbers out because you've got these sharps and flats So and oftentimes if you're using just the letters It can be useful sometimes to remove the sharps and flats and just look at the whole notes and note that the ones in the middle of the sharps and flats, but We're gonna do it this way. So we have this and we'll construct that next another one Using the letters and the numbers next time