here i'm going to show you how to convert measurements in excel so gallon to liter fahrenheit to celsius pound to kilogram and vice versa there are so many options and the function to do this is so easy so after i show you how to use it which isn't that difficult although there are a number of caveats then i'm going to show you some more interesting ways to use it so let's say that we have a table in miles and we want to use a vlookup and have the result returned in kilometers or let's say that we have a table that has a mix of measurements but we only want kilometers returned and we want it done in a nice neat little way like this so we'll start simple and build from there now let me clear this out and let's get started and of course don't forget to download the file subscribe hit the like button and comment let's start this off with gallons to liters so how many liters is one gallon equals convert and we only have three arguments the number argument from unit and to unit so hard code a number or select a cell with a number and comma this is well not all but many of the units from which we can convert so it makes it pretty easy for us to select an item although there are a great many so let's go down and find a gallon there you are double click then comma and maybe the best feature of this function it shows you everything that it can convert it into so that makes it very easy to avoid mistakes or easier and we can double click a leader and that's all there is for the convert function close it up hit enter and there we go 1 gallon is 3.7854 and so on liters and of course we can combine this with a round function or well one of the round functions are many of them and get a nice little number like that if we'd like but this is the very simple easy nice way to use the convert function now let's talk about some of the caveats and some of the things you have to watch out for so let's say that we're going from 80 degrees fahrenheit to whatever that is in celsius so we use the convert function equals convert from this and what's the unit well we can go down and find fahrenheit or if you use it enough you will remember it's just capital f comma and here's what we can convert it into so let's go for celsius and notice that these are capitalized so everything is good now 26.662 degrees celsius but what happens if you remember it's an f and a c but you do not capitalize it so lowercase f error so you need to pay attention to capitalizations and there are some of them that are particularly goofy tricky interesting and they'll have letters in the middle capitalized if you use them enough you can remember but if you are using it for the first time you do want to be careful like this one horsepower capital h capital p lower case h or horsepower hour there are many interesting measurement units here so make sure that you get your capitalizations correct let's back that up and everything will be happy now let's move on to the next thing that you need to pay attention to and this one is pretty important so let's say that we're going from pounds to kilograms so we have 10 pounds and what is that in kilograms equals convert we select the number now the from unit first off you might not be used to how they refer to it so lbm pound mass available there's a lot of interesting words which i do not know how to pronounce here but more important than that than the wording is what you do for things like kilograms so we now have pounds in here and then we go comma and we're looking over here where is kilograms i don't see it but i do see a gram and that brings us up to prefixes so what is kilogram it is 000 grams it is a kilogram what is a kilometer it's a kilometer it's a thousand meters so if you can just break up the word in your mind a little bit and think about it like that it becomes a little bit easier so we start off with grams and then we add a prefix and the prefix is what you would expect it to be in this case a k but what's kind of confusing is that it doesn't appear in this list but it will still work so when i hit enter we get 4.53 kilos is 10 pounds so when we're dealing with metric units you need to add this to get certain measurements and how many modifiers are there a great many probably a lot that you've never heard of unless you've done a lot with math so let's take a look this is from microsoft's own documentation i'll put a link to in the description of this video along with the link to my tutorial on teachexcel.com where you can get the downloadable file for this tutorial and we can see here lots of interesting ones yoda zeta exa pata i'm definitely saying those wrong but then we have ones that we kind of expect like kilo and it is just a k so we have a lot of these and then we have these guys down here as well so it's a good idea to check these out if you can't find the measurement that you're looking for and then just put that modifier or that prefix in front of the measurement and we're going to be doing that with kilometers in just a moment now let's go for bit to bite one of my favorite little tricks for internet companies how they used to convince you you were getting a really fast internet speed when it wasn't actually that fast so how many bits is a byte equals convert this number and what do we want to go from i'm just going to type this guy in bit comma and we can go to byte or back to bit i don't know why you'd want to go to the same measurement again but there we go close it up enter and 10 bits is 1.25 bytes would have been good to use this function when they said you're getting 10 megabit internet but they never actually said that they just included i believe a capital b or a lowercase b which meant bit instead of byte and everyone thought it was byte but that's a side note now let's go for some table measurements here we have a table and all of the distances are in miles but we want to return them in kilometers and all you want to do first just make your regular vlookup so we have our lookup value our table array and index number two and false for an exact match you always want to start with a lookup in this case to make sure that it is happy and then we're just going to surround this guy with a nice little convert function what is the number the result of the vlookup function and we will go comma mi for miles comma and of course we do not have kilometers but what do we have meters and how do we get a thousand meters we just put a k in front of it so k m just like pounds to kilograms now we can close that guy up hit enter and our results will always be returned in kilometers so let us go down here to a more interesting example now we have multiple measurements here and we want to break this out so that we can return a vlookup as kilometers now i say break this out because that's how i'm going to show you how to do it we're going to be adding helper columns but i bet you could figure out one formula that could do it all right here and if you do that i'd love to see in the comments for this video but now let me show you a very easy simple way to do it and it's very easy to remember so you don't have to work with formulas that are too long or anything like that so we are going to have three helper columns let's call it helper one two and three and for this table let's say that we are always going to have a two letter distance abbreviation it may be different for your data set but the point is i always know the end will have two letters and we could also use text to columns to do what i'm about to do but formulas work as well so helper column one i want to get the number helper column two i want to get the distance abbreviation then helper column three we're going to get the result in the correct distance unit and then have our vlookup up here for helper1 we want to get something from the left of the cell so we use the left function and we want to get it from this cell now how many characters do we want to get from this well we don't know but we do know that we don't want the last two so what we can do is to use the len function to count the number of characters in here and then just subtract 2 and when i hit enter we get 5 then we go down 10 20. perfect and helper column 2 is going to be a bit easier because we are going to be getting a value from the right of the cell and we know how many characters we want so equals right the cell and how many characters just two hit enter and there we go kilometers miles and kilometers now all we do is figure out if it's kilometers or miles and then if it's kilometers we just output the value if it's miles we go ahead and use the convert function to turn it into kilometers so we can use a very nice little if statement equals if this equals km then i can just output this guy don't need anything else but if it doesn't equal km it'll equal miles which means we use the convert function what do we want to convert well this guy right here and from miles to kilometers close that up close up the if enter and there we go we have 5 and 1609 and 20. but notice that these are not all number values so we have 5 and 20 over here and 16.09 to the right if you want to make sure that everything is a number value we can go ahead and use the very easy number value function close that up enter and there we go now that we have that all we do up here is a very simple vlookup so look up value a leg 2 and go like this and column index number 1 2 3 4 5 and false close it up and now of course how do we get it like i showed you in the introduction well select these guys go to the home tab and make them white or you can hide the columns so that the user can't actually click and delete anything in them there are many many ways of for hiding data and i love showing them but this tutorial is not about that but that's how you can use the convert function and that's how you can break it out into helper columns and do all sorts of really interesting things with it and that's all there is for this tutorial make sure to hit the like button subscribe hit the little bell icon and any other icons you need to hit and if you have a question or a comment make sure to leave it below this video so i can check it out