Excel 2016 Advanced Tutorial

hello and welcome to Excel 2016 my name is Shaun bugler and today we're gonna be going over some of the more advanced features inside of Excel 2016 let's go ahead and get started now as we're looking at this data set here it's interesting to see how this has already been set up now there's a couple of things that we need to take care of before we go any further we're looking at a couple of sales figures here from February of 2015 and we need to calculate the totals for each individual salesperson now I know full

well that we're all capable at this stage of typing in an equal sign summing up just like that but I'd like to introduce a concept to you guys today that's going to come in handy not just for this particular exercise but for many of the things that we're going to be doing in later lessons it's nice that we're able to tell Excel hey just sum up this range of cells but at the end of the day the problem is we're still trying to speak to excel in its own language instead of speaking on our own

if we were gonna ask somebody to sum up how much s jobs sold between weeks 1 and week 4 we would say hey can you sum up s job's sales not be 5 3 5 so why do we have to do that for excel why can't we say hey some up s jobs and have it know what we mean in fact we can this is called named ranges and to create them we're actually going to need to dive into the formulas tab go ahead and join me here inside the formulas tab and we're gonna find

the defined names command group now the defines names command group is here because in most instances named ranges are for formulas and functions in fact that's where they come in handy the most and that's where we'll be using them most so keep that in mind when you're trying to remember where the defined names command group is where do you use them inside formulas and functions so you'll find them in the formulas tab what we'd like to do is tell Excel that these four cells be 5 3 5 r s jobs sales so go ahead and

highlight from B 5 D 5 and we're going to use the define name button which allows us to define the name of a selected field of cells give it a click and check it out it's automatically guessed that we're gonna name it s jobs given the fact that it was the cell with text immediately to the left now it's not always gonna guess correctly so do make sure that you double check that before you click OK however you don't have to use that name you can always type in your own it is important to remember

however that spaces do not play nicely inside of named ranges notice that even in Excel it's placed in some underscores the next thing we're going to need to keep in mind is the scope where are we gonna be using this named range by default it's going to expand this to the entire workbook however we can always limit named ranges to specific worksheets instead however for the purposes of this class we're gonna leave it as workbook you do have the option of putting in comments if you'd like but in this case I'm gonna go ahead and

leave a blank it is important to note however that these can be incredibly useful especially if you're going to be creating lots of named ranges the last thing is the refers to field this is where you can make any last-minute edits in case it missed a couple of cells in this case by clicking inside the box I can see that the dancing ants are around exactly what I won so I'm going to lock it in by clicking okay it's a little anticlimactic and in fact it doesn't seem like a lots happened just yet but rest

assured what we've done now is we've told Excel that these four cells are s jobs so now I can place my cursor inside the totals field and just like with any function or formula I'm gonna do my equals sum open parentheses but this time instead of selecting the cells of the range I'm going to go ahead and press the f3 key and check it out there's s jobs go ahead and find s jobs and double click to add into the list you'll see that as jobs is now available by closing out the parentheses I can

complete the sum and by pressing enter you'll see now it's taken all four cells that I told were s jobs and it summed them up there's my function pretty cool go ahead and pause the video to catch up to me here once again the way we created that was by highlighting the cells we'd like to name navigating up to the formulas tab and using the define name tool to define the name as s jobs from there we simply in the totals field typed in equals sum open parenthesis s jobs go ahead and try that for

yourself and then come back welcome back so you've gotten your first taste of named ranges but I can hear your concern some of you might have tried to autofill down and you'll notice that when we did a simply replicated some s jobs wasn't quite what we hoped it would be it's important to note that this isn't necessarily a real-world use case this is simply an introduction into what named ranges are rest assured there are plenty of real-world use cases we'll be going over in the next couple of lessons another concern that tends to pop up

during these lessons is there are five other individuals here do I really have to go in row by row to define these names the answer is yes and no yes if you want to however no if that feels like a waste of time if you'd like to get this over with and believe me I would - we're gonna go ahead and highlight the remaining values starting this time by including the salespersons name so I'm going to click and drag from Iman to the final sale value of ten thousand two hundred and fifty two dollars this

time instead of using the define name tool here in the formulas tab I'm instead going to find the create from selection button it's important to note that this tool only works if there are row labels or headings in this case here I'm going to go ahead and click create from selection and check it out where are the names that it's going to create from are they on the top row that's so likely however they are in the left column e musk W Buffett and mayor as Sandberg and so on so those are our names let's

go ahead and click OK yet again at first glance it doesn't look like lots happened but let's take a look and see just like in the last process I'm going to go ahead and start by calculating eeeem usk s-- total sales I'm going to start with my equal sign now I can use my f3 key and pick the name from the list because check it out hey that create from selection created all of them in two clicks but I can also use the use and formula drop-down available in the formulas tab by clicking on that

I can choose another named range from the list for those of you with computers that don't have an a functional f3 key you can always use the paste names field from this as well and that'll pop up the pace name box either one will work in this case here I'm gonna go ahead and select from the list II must don't forget my sum close out the parentheses and there we go so go ahead and take this opportunity to catch up here and then calculate the remaining values for W Buffett and Mayor s Sandberg and a

Arend 'it's go ahead and pause the video in ketchup welcome back so already hopefully you're starting to see that creating named ranges is easy a little too easy really and in fact because it's so powerful you're going to increasingly see its value as we go through this process the next thing we're gonna need to do is calculate the total sales and average sales now in order to calculate those of course we can go through a standard process but for the sake of practice we're going to use named ranges yet again to sum up total sales

and average all sales so go ahead and highlight everything in the totals column from S jobs down to a Arend 'its and once you've done that inside the formulas tab go ahead and use the define name button to calculate February totals this time I'm gonna go ahead and add on to this already prefilled name so now I've got my February totals I'm gonna go ahead and click OK to lock that in and now I'm also going to go ahead and highlight everything from week one to week four having highlighted that there's not really any place

for me to create from selection because this time there is no header so I'm definitely gonna have to define a name here and in this case I'm going to go ahead and call this all sales click okay now that I've created those two named ranges I can now calculate the total and average sales here in total sales I'll type equals sum and then using either the f3 key or the use and formula drop down I'm going to select February totals and close up the parentheses and likewise I'm also going to in the average sales field

type my equals average open parentheses and then select all sales from the use and formula drop-down close out the parentheses and or lock it in another benefit of having use these name ranges is that now these formulas are practically in English meaning that I can glance at any one formula and find out exactly what totals I used what numbers I used where these data points are coming from go ahead and pause the video to catch up with me here once again we created two named ranges the totals named range which we then used to sum

up total sales and the all sales named range which we used to calculate the average everyday sales go ahead and catch up to me and then join me after the pause so that's your first taste of named ranges don't forget it rest assured that this is coming back very very soon in the meantime let's shift gears we've calculated all the total sales in fact we've calculated the total sales and average sales for all of February as well what we'd like to do now is complete the rest of the data set now currently it's asking was

the goal met and as I look around I see there is a monthly goal of thirty four thousand dollars so let's take a look in regards to s jobs s job sold thirty six thousand two hundred and forty five dollars compared to a monthly goal of thirty four thousand I think it's readily apparent that S jobs did in fact meet the goal so I guess I just type yes right we did it what's the problem with this why can't I just type yes well the problem is I didn't tell Excel how I knew that the

goal was met I just told it that it was the problem with that is let's say I turn this into a template and use it next month for March 2015 sales well if the numbers update and let's say s jobs doesn't do as well and the sales drop below 34,000 that cell is going to continue to say yes so it's not necessarily doing exactly what I hoped it would do but I thought Excel was smart it should have known right not yet we've got to tell Excel how we got there and how we got there

was by using logic so let's teach Excel the logic we used in this example we're asking a question was this cell greater than this cell did the total beat the goal in order to ask that question to Excel we have to posit it in a very specific way in a very simplistic way basically we have to ask is something true or not and to do that we have the if function the if function allows us to ask Excel a question and depending on the response respond accordingly with either if true do something or if false

to do something else so let's go ahead and type our first if function now I know many of you who already work with functions are comfortable typing these functions by typing equals if but you've actually been doing it the hard way not the wrong way and in fact if after this lesson you decide to continue doing this the exact same way you've always done that's fine but I'd like to show you guys a new way to inject functions especially complex functions into Excel placing your cursor in cell h5 we're going to go ahead and find

the insert functions tool this can be found to the left of the formulas tab by clicking on the insert functions button you'll see a little search field I'm gonna go ahead and search for if and once I've typed if I'm going to click go this is an actual library of every single function inside of Excel by typing if into the search function and clicking go I can bubble if up to the top and on top of that I actually get a description of what if actually does and we're better to get a description then from

the people who actually wrote the code in this case Microsoft describes if as checking whether a condition is met and then returning one value of true and another value if false makes sense to me once you find the if function go ahead and click OK which will bring you into the function arguments box the function arguments box exists for every single function inside of Excel even the simplest ones like some what it does is that compartmentalizes each individual part of the function into its own little box this allows you to focus on one individual part

of the function at a time allowing for the utmost focus and control as we go through this here you'll see the benefits the first thing it's asking for is the logical test now if you're not entirely sure what the logical test does you can always look down here at the bottom no matter where you are if you click inside the box it'll tell you what that box is asking for in this case the logical test is any value or expression that can be evaluated to true or false this is our question in this case the

question is is the total cell greater than the monthly goal so let's go ahead and take that into account I'm going to just click on the total cell so f5 was it greater than or equal to I too and interestingly enough because we're inside the function arguments box it's actually automatically answered that question in this case F 5 is greater than I 2 so we know because it says true that our question is right the next thing it's asking for is the value if true field what do we want Excel to do if the cell

is true if the question is true we probably want the box to say something like yes so let's go ahead and type yes however it's important to remember that you can't just type text into a function you have to remember that if you're typing an actual text field that's not a function you need to surround it with quotations once you've done that go ahead and tab down to the next field so if the question is true the box should say yes however if the question is false what do we want the box to say now

usually the response is no but let's have some fun with it right how about not a chance notice I remembered to put quotations in there now what's the scariest part about building a function for those of you that have built them in the past you might answer that the scariest part about building a function is remembering all the commas and the parenthesis however with the function arguments box you don't have to remember the commas or the parentheses this box takes care of it for you in fact take a look at the formula bar this is

the function we just built notice it took the commas and the parentheses into account already nice right on top of that before we even click okay we can even see whether or not this functions going to work so no more holding your breath before you tap the inner key simply look in the bottom left corner by the formula result and you'll see exactly what the cell output will be now of course if you're seeing an error here you should double check to make sure that your boxes match mine once we've asked the question we've got

our value of true and our value of false responses let's go ahead and click OK and there you go was the goal met in this case the answer is yes thirty six thousand two hundred and forty five is greater than the monthly goal of thirty four thousand go ahead and pause the video to catch up once again the way we got there was by using the insert function arguments box which can be found in the formulas tab on the far left hand side this tool can also be found here to the left of the formula

bar these are the same tool one is just a little bit more accessible than the other once you open the function arguments box simply make the changes that you need to ask the question input your responsive true your value of false and click OK go ahead and pause the video in ketchup all right welcome back so at this stage we've asked a single question did s jobs meet the monthly goal in this case the answer was yes now none of us want to have to rebuild the same function over and over and over again thankfully

of course we have autofill so let's go ahead and take a look at that by placing our cursor on the bottom right corner of the active cell that has the formula we'd like to copy we're simply going to ensure that we've got that thin black cross click and hold and drag until we get to the bottom of this box and then release now at first glance it looks like it works but of course as any good mathematician will tell you never trust your own work so let's take a look go ahead and place your cursor

on the first cell the one that we build okay so we've got 36,000 that's greater than 34,000 good ah 31,000 greater than 34,000 oh it's not but what's going on here why does it say yes in fact as I'm going through this here I'm seeing in a couple of instances it's saying yes when it should not in this case here unfortunately as Sandburg did not do what we hope to they would do move missed it by a dollar but it still says yes now either the computers being generous or something's gone wrong in any case

we're gonna need to take a deeper look at this and for that we're gonna need help to analyze all of our formulas that we built we're gonna need access to some of these formula auditing tools let's go ahead and place our cursor on the first cell now this is the cell that we built so we'll use this as a test run to make sure that we know everything's working go ahead and find the trace precedence button and give it a click and check it out this is a visual indication of where this cell and its

function are pulling reference data in this case and this cell is referencing the totals field and the monthly goal field okay everything looks good here I'm going to go ahead and go down to the next cell here and once again we're going to trace precedence huh that's weird when we built the function originally we were asking does the total exceed the monthly goal then why is it then when I trace precedents here it's asking does the total be nothing because of course the answer is yes for that let's take a look at the cell below

and once again we're going to trace precedents and now we're starting to see the problem when we autofill down we didn't remember to absolute reference the cell for those of you who aren't familiar with absolute referencing refer back to earlier lessons but for those of you who are what cell in this formula shouldn't have changed if you said I to you'd be absolutely right each total cell should be referencing 34,000 no matter what and in fact if we click on the show formulas button first things first let's get rid of these arrows go ahead and

select remove arrows and now let's show formulas this is going to reveal all formulas inside the worksheet so as we're going through this year we see that when we autofill because we did an absolute reference f5 became f6 became f7 which is right because we wanted to continue referencing each total one row after the other however I too should never have become I three which became I four and so on I too should have always stayed I too so we have to fix that to fix that go ahead and place your cursor either on cell

h5 and double click or click on the cell and in the formula bar go ahead and place your cursor on top of I 2 now it's a small change so we won't need to do anything too crazy we're simply gonna place our cursor on cell I to the cell that shouldn't have changed in the first place and we're gonna absolute reference it using our keyboard shortcut of f4 once again noticing that when you tap the f4 key we now have dollar signs on either side of the column value and the row label we've now absolutely

referenced the cell meaning that when we autofill Excel knows that I too should always be I too go ahead and press ENTER to lock that in we're now going to autofill this down to ensure that this works the way we expected to by clicking on cell h5 without going to autofill down all the way to age 10 once again turning my fat white cross into a thin black cross I'm going to click and drag down to the very bottom and there we go so now we see f5 to I to f6 to I to f7

f8 everything is now working the way it should let's go ahead and show formulas simply click the show formulas button once again to resolve it back to its original view let's double check 36,000 is greater than 34 so yes 31 not greater than 34 so not a chance okay 33 good all right so it looks like by absolute referencing cell I two we managed to salvage the function and we didn't have to build all the rest of them ourselves we allowed autofill to do that like work for us pretty good so it's nice to see

that we have some auditing tools that allow us to evaluate functions and formulas to ensure that they're all working properly and when they're not these tools vastly come in handy when it comes to making sure that we know where and how to fix these tools when the time comes go ahead and take this opportunity to catch up once again verify that your if functions are all answering the question properly if they're not use the formula auditing tools both trace precedents and the show formulas tool in order to evaluate whether or not they're referencing the appropriate

cells pause the video and join me after the break welcome back so we've asked our first question was the goal met and with a little bit of work we got excelled to answer that question for us now what do we want it to do well we probably wanted to calculate the bonus status let's say for example that if they hit their bonus they get a 2% Commission on top of their regular paycheck now if I just go into the cell right here and calculate 2% let's say I'd do that until I 5 I'll just do

equals the total times 2% okay seven hundred and twenty five dollars I autofill down done right except that only the people who hit the goal should get the bonus so what am I gonna do manually delete the cells that have bonuses where they shouldn't or can I let Excel do that for me if you followed along go ahead and clear out your bonus status column instead of just typing out the answer we're instead going to give Excel the power to logically determine whether or not they should even calculate the bonus once again we're going to

start by inserting our if function we saw that we were able to insert our if function using the insert functions tool either here or here inside the formulas tab this time let me show you another way this time using one of the function library dropdowns the function library dropdowns are simply books that contain specific functions the F function would be considered in a logical function simply because we're asking a question so by clicking on the logical drop-down you'll see there's if and I'm going to give it a click so once again we're going to answer

the question was the goal met this time however instead of asking the question by calculating one cell greater than or equal to another let's calculate by asking whether or not this cell says yes oops what's going on here why did it give me an error Oh remember what I said when you're putting text into a function you've got to remember to put quotes around the text otherwise Excel is going to try and read that as a function and there is no yes function so H 5 must equal yes and check it out it's true h

5 does in fact say yes now what do we want Excel to do if the value is true last time we just had it say yes but in this case instead of having to say yes how about we have it calculate the bonus for us in this instance the total sell multiplied by two percent the same function we used before we'll tab down and what do we want the cell to say if false how about a big fat goose egg in this case here we're pretty much done but we do want to verify does h5

say yes yes it does so if it's true it should calculate the total times 2% which comes out to 700 and $24.90 meaning that because h5 does say yes I should have a formula result that says boom 700 and $24.90 now that we've verified let's click OK to lock it in and there you go we can now autofill this down and in theory it should cross-reference to see whether or not the cell says yes if it does it should calculate the bonus if it does not we should see zero next to not a chance so

I'm going to go ahead and autofill down and moment of truth huh all right there it is so wherever it says not a chance the bonus is zero however let's say as Sandberg here picks it up sells 8,800 and one boom instantly calculates a bonus not a chance changes to yes bonus gets calculated pretty cool by automating this process all we have to focus on now is inputting the data where it's asking for it go ahead and pause the video to catch up with me here right we have got one box left how hard could

this be the Department bonus of eight hundred and fifty dollars per person alright cool so some of these people still have a chance the department as a whole must have total sales of at least $200,000 and average sales of nine thousand or more per week okay so we've calculated total sales and average sales now sadly I can already see they didn't quite meet it but again we don't necessarily want to be the ones to make this call because as these numbers update do you really want to be the one that's calculating all this or would

you much rather automate the process and allow Excel to tell you personally I'll always go for the ladder this leaves me time to focus on the more important nuances of my job like deciding what I'm gonna have for lunch go ahead and place your cursor and cell I twelve one last time we're going to start with our if function I'm going to go ahead and use my function libraries drop down of course remember you can always use the insert functions tool as well all right I'm in my if function arguments box and the question is

does the department as a whole have total sales of at least $200,000 okay so logical test is is total sales greater than or equal to 200,000 the answer is yes okay cool but now where do I put the second part because it requires both the condition is in order to get the bonus they have to have total sales of at least $200,000 and an average of two and an average of nine thousand or more per week so where do I ask excel the second question huh well this is the problem I have functions they only

allow for one logical test so we need a function that allows for multiple logical tests fortunately as it so happens I happen to know of one function that does allow for logical tests more than one it's called the and function so let's go ahead and type out our and function here in the logical test box go ahead and type a and D and in open parenthesis in parentheses is key in order to build another function within a function go ahead and start typing that a and D and that open parentheses you'll notice in the formula

bar it's built exactly that for you already with the closing parentheses once you've typed that a and D open parentheses you might be a little disappointed at this stage because you might have gotten attached to this function arguments box at this stage doesn't look like we have access to it however it's important to note that you can only see one function arguments box at a time in order for Excel to know which function argument box you need to see you need to tell it and the only way that we can really tell it is by

clicking on it in the formula bar so if you want to see the and function arguments box you have to click on the word and so go ahead and find and in the formula bar and give it a click instantly it drops you into the and function arguments box once you've clicked on and make sure to click back inside the function arguments box this is incredibly important and there you go so now we have room for two logical arguments let's try this again the first logical test we know is total sales must be greater than

or equal to 200,000 okay cool we see that one's true the second logical argument is average sales must be greater than or equal to 9000 and sadly they did not get that but regardless I don't seem to have access to a value of true or value of false in fact all I have is another logical arguments box fun fact you can have up to 255 logical arguments inside of an and function hopefully that's enough for you but where's my value of true and value of false well as it turns out and functions can't control value

of true and value of false that's a major limitation of and functions now if an functions can have multiple logical arguments but can't control the output and if functions can only have one logical argument but can control the output maybe we can combine them and using the limitations of both to circumvent and make them strengths so now that we've built our and function let's go back to our if function how do we get back into our if function well remember we can only be inside one function arguments box at a time and the only way

to tell Excel to get back to another function arguments box is to find it in the formula bar and click it so find if inside the formula bar and click on it boom and we are back inside the function arguments box and on top of that because both conditions weren't met the and output is a false response the only time and will ever say true is if all conditions are met now what's our value of true going to be now that we've got a false response we see here that it's not going to be a

value of true but we want to account for it if the data ever does meet the criteria in this case the value of true will be bonus in all caps what's our value of falls going to be everyone in this instance here I think an appropriate response would be sadface so now that we've got our logical test which is in and of itself another function we've got our value of true and our value if false let's go ahead and lock it in by clicking okay and there you go the Department bonus is not achieved because

unfortunately they didn't meet both conditions however let's say just hypothetically Elon Musk picks it up and sells $10,000 in week one bringing the average sales above nine thousand and check it out instantly the bonus appears because the average sales spiked over nine thousand not bad because we automated this entire process now we can use this every single month and it will always give us the right answers because we built the functions to logically reason with the data for us go ahead and pause the video and take this opportunity to catch up with us let's all

take a moment however to click on cell I twelve and appreciate this function right here you just built a nested function a function within a function this is just the beginning but hopefully you saw how easy it was using that function arguments box to build all of this function within one go ahead and pause the video to catch up with me here welcome back so hopefully you feel a lot more comfortable building if functions now and hopefully you feel even more comfortable nesting those functions now that we have access to the function arguments tool we're

gonna shift gears a little bit here and talk about a crowd favorite vlookup now vlookup is one of the most sought-after skills when it comes to Excel database management however many people don't take the time to learn vlookup because it feels scary complex overwhelming even vlookup is all about using a single search value to search an entire database and output a particular value it stands for vertical lookup in this example here we're searching for employee ID when we find the employee ID what we'd like to do is find the output for lastname firstname department and

pay rate to do that vlookup identifies a value and then we tell it how many columns to the right of that value the output is so let's get started go ahead and place your cursor in cell b4 just like in previous instances we could start by just typing an equal sign and then vlookup however again that's the long way instead what I'd like to do is insert functions once again this time I'll go through the process of selecting insert function once again from the formulas tab or you can use the FX button here on the

left of the formula bar using either one you'll pop up here go ahead and search for vlookup all one word and click go on the right hand side instantly vlookup bubbles up to the top and we get our definition vlookup looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify interesting so let's take a look at that first line there looks for a value in the leftmost column of a table this means that whatever value you're searching for must be the

furthest column to the left it can't search anything to the left of that so that's a really important limitation to keep in mind in fact many people consider it to be the limitation when it comes to vlookup so let's go ahead and find the vlookup option and click OK and here you go we're now inside the vlookup function arguments box the first thing it's asking for is the lookup value again if you're not sure what that is simply place your cursor inside of it and you'll get a breakdown the lookup value is the value to

be found in the first column of the table in this case here our lookup value is employee ID this is the field that we have that we like to use to find lastname firstname department and pay rate so we're gonna give it a click and because this is the only cell we're going to go ahead and absolute reference it just for good measure remember we absolutely reference by tapping the f4 key the next thing it's asking for is the table array not sure if the table array is boom right here table array is the table

of text numbers or logical values in which data is retrieved this is where we're searching for not only the value we're looking for but the outputs we want so essentially that's all of this so go ahead and inside the table array now go ahead and click on cell a9 now I'm going to show you a quick and easy way to select the rest of the data without having to click and drag because we have no idea how many rows could be in here by pressing and holding the shift and ctrl Keys again pressing and holding

those down tap the right arrow key once you'll notice that now the flashing marquee encompasses everything from a nine to I nine I'm still holding the shift in control keys I'm now going to tap the down arrow key and check it out it's brought me down all the way to row 46 pretty cool right once you've selected all that data go ahead and tap the tab key so our table array is a nine through i46 in fact we should probably absolute reference this as well because that's not changing either to absolute reference it highlight everything

inside the table array and tap the f4 key you should have four dollar signs total inside the table array field only two more fields to go the column index number this is the number in the table array from which the match and value should be returned in this case here how many columns from the left is the last name column so if employee ID is column one then last name must be column to remember the search column does count as a column still so the column n next number is going to be two two columns

from the left of the edge of the data the last thing it's asking for is the range lookup now the range lookup is really important because it essentially determines whether you're going to get a relative match or an exact match so let's say we're searching for an employee ID number that doesn't exist accidentally we type in 1055 instead of 1054 or 1056 what would you rather have an error that says that employee number doesn't exist or Excel guessing what it thinks you meant when it comes to database functionality you never want Excel to guess if

you're searching for something that doesn't exist you always want it to tell you hey that's not there for those instances and really for almost all instances the range lookup will be false indicating that we want an exact match now you can type false or you can use its binary equivalent of zero so let's recap here the lookup value is cell a4 where that cell is right there meaning whatever number is inside there that's what it's searching for the table array is where our search field is and our answer field in this case here our search

field is column a all the way to column I that's where our last output is and we've absolute referenced both instances because no matter what it should always reference the lookup value in that same spot and the table array in this same spot before we even click okay our formula result tells us the response will be Smith let's double check employee ID 1054 let's find it 1054 last name is Smith so our vlookup function is working let's click OK to lock it in not then go ahead and pause the video to catch up with me

here remember we inserted the vlookup function by using the insert function arguments box simply click on insert function search for vlookup and you're going to get this box right here our lookup value was the value we're searching with our terrible array was the data in which we are searching and in which contains the answer our column index number was how many columns from the left is the column output that we're looking for in this case the last name column was two columns to the left from the left and lastly range lookup do you want a

relative or an exact match if it doesn't match you want an error so our range lookup is false or is zero click okay to lock it in go ahead and pause the video I'll meet you after the break now that we've built that function once we probably really don't want to go through that process again fortunately we had the foresight to absolute reference our lookup field and our table reference so we can actually just autofill over to the right except it doesn't do exactly what we hoped it would do remember we can always use our

formula auditing tools to see what went wrong but in this case here I see that the column value simply didn't change in order to fix that I'm simply going to go in and change the value myself simply click on the first name field and I'd like to jump inside my function argument box again so select cell c4 and click on that FX button this will bring you back into the vlookup function arguments box I see here that the column index number is still 2 however the last name column is not 2 from the left how

many is it 1 2 it's three columns from the left this time so two should actually be three click okay there we go let's do the same for department columns from the left is Department one two three okay four four columns from the left last one pay rate once again gonna click on the cell hit the FX button how many columns from the left is pay rate 1 2 3 4 5 6 7 8 9 so 2 becomes 9 click OK to lock it in boom there you go so now let's test our work and

see how it goes so 10:54 let's time 11:52 and check it out everything updates appropriately mark Enders is 1152 what about 1310 boom all right let's try an employee ID that doesn't exist to ensure that it's not going to give us anything we didn't expect let's try 10 59 and we see here there is no employee 10:59 we get these n/a errors pretty good now one thing that we should keep in mind here is that this database isn't going to be just for us in most cases we're gonna be building this to hand off to

somebody who doesn't necessarily have the same skills that you do when it comes to working with an Excel one particular problem with that is this these errors when an error outputs we know that this is because this doesn't exist but you know who doesn't know that pretty much anyone who's not taking this class right now all they see is an error and to them that error means that whatever you built is wrong and they're going to come and find you so instead of having to deal with all these people coming back and saying hey your

thing is broken let's soften the blow and change this error alert to something a bit more user friendly how do we do that well we can use another function out there called if error if you'll remember if functions allow us to ask a question to excel and depending on that response control the output either with a value of true or value of false if errors work in a very similar way but instead of giving you a value of true or value of false it gives you a value if error indicating that if an error occurs

with a function that's already been built you can control the output so instead of getting this pound sign na you could have it say something like unavailable now I don't want to have to go and rebuild this function here so instead what I'm going to do here is by clicking on cell b4 I'm going to navigate up to the formula bar and place my cursor between the equal sign and vlookup with my cursor there I'm gonna go ahead and type if error and in open parentheses once I've type if error in an open parenthesis I'm

now going to move my cursor to the middle of if error and I'm going to click on my function arguments box remember cursor placement is incredibly important when using the insert function box and check it out we're now inside the if error function box now you're seeing that you don't even need to build a function with the function arguments box in order to gain access to it later so I've got my value the value simply being the original function however if that value outputs an error I have the value if error box so what do

I want it to say maybe I'd like my value of error box to say unavailable employee doesn't exist remember put those quotations around when you're typing text so go ahead and type in a value of error and let's click OK to lock it in and there we go so now instead of showing me that ugly pound and a Seng I'm getting no employee exists now I could replicate this here here and here or in these remaining cells I could build an if error function once again by clicking between the vlookup and the equal sign typing

that open parenthesis and opening the function arguments box I could have it give me a blank cell for the remaining three to get a blank cell simply type quotes twice the double quote equals a blank cell and go ahead and click okay go ahead and pause the video and take this opportunity to catch up build an if error wrapper around last name and give it an output response then build an if error function around first name department and pay raid giving that a blank cell value once you've done that unpause the video and come back

for the next part there you go so now we have a fully functional vlookup database so if I search for an employee that does exist like 1302 I get Randy Smith however if I search for an employee that doesn't exist like 10/55 I get no employee exists and a fully blank three additional cells not bad so we're starting to see that vlookup has a lot of power behind it and in tandem with iferror we can build a really nice-looking database now there's one part when we were building the vlookup function that we talked about and

it was the very last part the range lookup we talked about how in most instances we're going to want an exact match because we don't necessarily want Excel guessing the question often arises when would I use true when would I want a relative match to answer that question we have a bonus calculation table each of our employees here Mickey Donald bugs people you may know have their sales here and what we'd like to do is calculate their bonus now using vlookup let's try that out I'm gonna go ahead and build out a vlookup function starting

here in cell c4 by clicking on the formulas tab I'm gonna go ahead and jump into the insert function arguments box fortunately because vlookup was the most recent function I used it's up here at the top and I can just click that and select ok of course for you if it's not you can always use the search function to find it as well so what's our lookup value well our lookup value in this instance is going to be here Miki's sales the next thing it's going to be looking for is the table array where is

the data that we want output and in that instance that's going to be here now because the table array is never going to change and in most instances won't it's a really good habit to be in to absolute reference the table array the next thing is asking for is the column index number now in this instance right here I'd like it to just tell me the bonus so the column index is going to be one two cells to the right so my column index number will be two now remember we talked about using range lookup

how in most instances we don't want a relative match we want an exact match so I'm going to type 0 and I'm going to click OK and you'll see that I get an error message the reason being is that 1,200 doesn't exist inside the sales table instead the way this bonus table is set up it's when they exceed a certain value they qualify for a bonus so Mickey because Mickey sold 1,200 dollars in sales should have qualified for a $10 bonus however I'm only getting it an error because it was looking for an exact match

so let's fix that click on cell c4 and let's jump back into that insert arguments box this time instead of asking for an exact match using the absolute search I'm going to look for a relative match by changing the range lookup from zero to one indicating a true response and I'm going to click OK there you go so now we see that Mickey gets a bonus of ten dollars because Mickey sold more than a thousand but not more than 1,500 it's important to note that the relative reference will only find values that it's already exceeded

and in these instances it's incredibly important to remember that when using relative range lookups this table the column that you're searching in must be in ascending order I'll say that again when using relative range lookup you absolutely must ensure that the column you are searching inside is sorted in ascending order ranges don't work well without that order sort now that we've built this function out because we absolute reference to the table field we can now autofill down and boom there we go Donald sold $1,300 and therefore also gets a $10 bonus bugs sold 2,500 and

therefore gets a hundred and twenty five dollar bonus Daffy sold 850 notice that because Daffy did not exceed a thousand gets a zero dollar bonus so now you're starting to see that there is a real-world use case for using range lookups that are not absolute now let's take a look here we see that there's also a third column for holiday bonuses maybe we'd like to handle that now Before we jump into that go ahead and take this opportunity to pause the video and catch up with me here you should have a bonus that directly correlates

with the bonus column here go ahead and pause the video and catch up with me welcome back before we went on pause we referenced that there's a third column that we don't seem to be able to search just yet however we do have the ability to build that functionality in is this a standard or a holiday bonus well by clicking in that gray cell you'll see that somebody I won't name names injected data validation so now I can click on the drop-down and determine whether this is a standard or a holiday let's go ahead and

click holiday and see what happens nothing yet because we haven't done anything with it yet however how could we get Excel to in one situation do one thing and in another situation do another well how can we ask excel a question somebody out there is screaming at the top of their lungs if function if function and that person is right we can use an if function to build out a if this says holiday calculate the holiday bonus and if it does not say holiday calculate the standard bonus so let's go ahead and do that just

like what we did with the if error function we can build an if wrapper around the existing function however for the purposes of this class we're going to keep things simple instead go ahead and highlight the function that's already been built and delete it we'll only do this for Mickey and we'll carry it on for the rest we're now going to go ahead and insert our if function from the logical function arguments box go ahead and find if from the logical function library and give it a click so what's our logical test if this cell

equals day then what well in both instances we wanted to calculate a vlookup output response however each of those will be slightly different one vlookup will look in the bonus column and the other will look in the holiday column so in this case if it's true that means that this cells is holiday meaning that we should build a vlookup function that outputs the holiday bonus so let's go ahead and start by typing vlookup open parentheses remember that open parentheses is critical once we've typed that open parentheses we need to open up the vlookup function arguments

box within this if function so let's go ahead and find vlookup in the formula bar and click on it we're now inside the vlookup function arguments box so what's our lookup value well it's still the sales and the table array is still everything here remember to absolute reference your table array and now our column index number in this instance we're trying to determine the value of true meaning that we want to pull if true that means that this says holiday and they should be getting the holiday bonus meaning the column output should be one two

three it's the third column from the left lastly do we want a relative or an exact match in this case we want to allow for a relative match so the range lookup will be either true or it's binary equivalent of one now don't click OK yet because we're not done it's really easy to forget that we're still inside the if function in fact that's probably the most dangerous part about the function arguments box forgetting that you're still inside an if function or any function for that matter once you're done building your vlookup function go ahead

and jump back into the if function arguments box by taking your cursor again to the formula bar and clicking on if there you go so we've got our logical test if c12 says holiday calculate the value of true vlookup and the output is the holiday column however if the value is false we still want it to calculate a vlookup but instead of pulling from the holiday column we want it to pull from the standard bonus column now I really don't want to have to go and rebuild that vlookup function fortunately this function is almost identical

to the one that we're going to build anyways so go ahead and highlight that value of true and using the keyboard shortcut of ctrl C or from the Home tab clicking the copy button go ahead and drop down into value of false and paste it the only thing we have to change is the column output you can change the column output by simply identifying that number three and changing it to the output of the column for the standard bonus in this case column two so we've got our logical test if c12 says holiday calculate the

vlookup with the holiday output if this cell does not say holiday calculate vlookup with the output of this column the standard bonus let's click OK and make sure everything works and there it is I'm now going to go ahead and autofill this function all the way down to ensure that everybody has the same F function built but it doesn't seem to have done anything huh what do you think went wrong someone out there probably that same person that shouted if 'function earlier is shouting you didn't absolute reference c12 meaning that while this one cell is

double checking to see if c12 says holiday every other cell is checking the c-13 see 1415 and so on and those cells don't say holiday meaning they're still getting this standard bonus so let's fix that by clicking on the function that we built already in cell c4 we're gonna simply place our cursor on top of c12 an absolute reference it using the f4 key having done that we're gonna go ahead and tap enter to lock it in and now finally absolute reference it for the final time and autofill down and there you go just like

that everybody gets the holiday bonus but if I click and I change the holiday drop down to standard everyone gets a standard bonus so there you go by building this if function we now have a fully automated bonus calculation table that depending on whether or not it's a holiday or a standard pay day they get a bonus relative based on that content we don't have to do the math anymore with vlookup and that relative range lookup all we have to do is input the sales and tell it whether it's a standard or holiday bonus go

ahead and pause the video and take this opportunity to catch up it's time to talk about the redheaded stepchild of the lookup family h lookup h lookup is one of the most underutilized functions but it is important to acknowledge that it does exist H lookup is the polar opposite of vlookup instead of using the vertical search in order to output a value it uses a horizontal search to find the individual row outputs in this instance we're searching for a specific product code and we're trying to find the output in particular warehouses around the globe in

this case here the products code is here on the top row meaning that we're going to be searching a horizontally and from here finding the row output from the top let's go ahead and build it out placing your cursor here in cell b6 we're going to go ahead and insert our function arguments box by finding the FX button here or in the formulas tab either way go ahead and search for H lookup all one word once again once you find it go ahead and click okay it looks almost identical because it is identical it's the

same function simply searching from a different direction in this case our lookup value will be here b5 and because we have the foresight to know we're probably going to autofill this down let's do ourselves a favor and absolute reference it now the next thing it's looking for is the table array where is it going to be searching for XP 200 and on top of that where is it going to be searching for the outputs for warehouse 1 2 & 3 it's going to be searching here from a 11 to G 14 we're gonna need to

absolute reference that one as well because remember always absolute reference the table array you never know if you're going to be auto-filling so head your bets the next thing it's looking for our row index number which just like our column index number only starting from the top this time for this first one warehouse 1 how many rows from the top is warehouse 1 in this case here the first row values we're searching is Row 1 so warehouse 1 is row index number 2 lastly do we want an exact or a relative match well this time

we're talking about database functionality so we don't want a relative match we don't need Excel guessing so our range lookup will be false or it's binary equivalent of zero having built that all in go ahead and click OK to lock it in and there you go now that we've built that out we can autofill down and adjust the RO value number as necessary in warehouse 2 how many rows down is the warehouse 2 1 2 3 so either by opening the function arguments box and changing the row index number here or by changing it in

the formula bar up here at the top go ahead and make changes to warehouse 2 & 3 to ensure that they're outputting the appropriate value the last one for warehouse 3 how many rows down is it 1 2 3 4 rows down so 2 becomes 4 and there you go so there you have it H look up just like vlookup only the other way go ahead and take this opportunity to pause the video and try this out for yourself keeping on the trend of database functionality let's talk about some other really useful database functions vlookup

is great but it only gives us a single output value if we're trying to organize a lot of output values into a single response vlookup isn't quite what we want it to be in this instance right here let's say we're trying to calculate the total expenses of particular categories in those instances what we'd like to do is search for a particular category in this column and once identified take the total expenses and sum them up and output here in the total expense field vlookup can't do that for us however there is a database function that

can now this is a conditional statement if this cell says something then we should find the total expenses that correlate with it and sum them up some and if this is the sum F function we're talking about here go ahead and place your cursor here and sell j14 with me and we're gonna go ahead and insert our sum if function we can insert our sum if function from either the insert function arguments box and searching for some if or you can always use the math and trig function library and find some if here now it's

important to note that there is a some ifs as well please don't mistake the two there is a difference and we'll talk about it but for now simply find some if either using insert function search or the function library drop down and here we are now the first thing it's asking for is the range now the range is the failed of cells that you want evaluated in this particular instance where are we searching to find software now I could go here and just click-and-drag everything but that's tedious so let's not do that go ahead and

close out your function arguments box if you have it open and let's help ourselves out here ahead of time go ahead and place your cursor anywhere inside the data set and tap control a to select the entire data set if you feel more comfortable clicking and dragging you're more than welcome just simply ensure that you've highlighted everything from the headers down to the very bottom of the data set I promised you it was coming back let's go ahead and create some named ranges out of these now we have headers meaning we can use which of

these define name or create from selection well as I hover over create from selection you'll notice it says automatically generate names from the selected cells in this case here this is the tool we want I'm going to click create from selection and in this instance it's a little confused it doesn't know where my headers are am i pulling names from the left or from the top I'm not pulling it from the left because that's just another column I'm pulling my headers from the top row so I'm going to deselect the left column and leave top

row selected and click OK I've now created categories division category January February March and total expenses all derived from this highlighted data let's see if that makes a difference to us when we're building out our sum if once again let's go ahead and open up our sum if function arguments box I'm going to use the math and trig function library and find some if once again ensure that you are not using some ifs but some if so once again what's our range our range is where we are looking to find software which of these columns

contains our search criteria in this case here we're searching for software in the category column now instead of clicking and dragging to highlight all of that I'm going to use the use and formula drop-down to select category and check it out on the right-hand side here I see technical support telephone copying those are the first three entries in the category column so I know that it's referencing the right data next I'm looking for the search criteria what am I looking for in the category column in this case here I'm searching for software in the category

column I want to find the word software so I'm going to go ahead and click here no matter what this cell says I'd like Excel to search that word in the category column so currently I'm finding software in the category column now once I find software what would I like to do my some range this is the most important part here once I find each individual instance of software which of these cells am I going to from this row some together in this case I'd like to sum up the total expenses that correlate with software

in the category column my some range while I could click and drag from the top down for total expenses I've also created a named range called total expenses so just like that my range is category this is where I'm searching for my criteria in this case software once I find all instances of software I'm going to sum up all of their correlated total expenses look how simple that is with named ranges isn't that something once you've got that up and running go ahead and click OK and there you go so in software we spent seventeen

thousand two hundred and fifteen dollars in quarter one now the cool part about some if wasn't that we were able to pull that number out although that is pretty cool in and of itself it was that we told it to reference this cell we didn't tell it to search for software we told it to search for whatever word was in this cell so I can change this word to any category and the number should in theory change so let's try Brent tap enter and check it out the total expense field automatically updated what about advertising

spelling does count Wow a hundred and forty four thousand dollars go ahead and take this opportunity to pause the video and make sure you're some f function is working once again the way we got there was by inserting a sum if function either from the insert function arguments box or by from the math and trig drop down towards the bottom once we did that we implemented named ranges to determine the category which is where we were searching for the criteria our criteria is what we were searching for and our sum range was what we wanted

added together when we found our fields go ahead and pause the video and take this opportunity to try this for yourself all right now that we've tried that once let's try it with another one this time we're looking for the average expenses of a particular category so we're not gonna be using some if this time but we are still asking an if question we're just not summing so what function is out there or might be out there that instead of summing based on an if averages based on an if please don't make me spell it

out any more than that we're looking for the average if function now the average if function can be found from the insert functions box or from more functions statistical average if once again there is an average ifs however we'll be talking about that one adjust a little bit go ahead and find average if and give it a click it looks identical to the Sam F function because it is the only difference is the mathematical calculation that takes place within the range field so in this case here are range once again where are we searching to

find our criteria our criteria is the category rent so where are we searching for the word rent well in the category column we could click and drag to select the category fields but this is tedious instead of doing that because we created a named range all we have to do is click that use and formula drop down here in the formulas tab and select category once again verify that on the right hand side you should see the first three instances of the category column technical support telephone and copying our criteria or the cell that we're

using to determine what we're searching for is here in cell I 20 so whatever this cell says right here we're going to be searching for in the category column lastly what field are we a virgin together once we find all instances of rent we're going to go ahead and use total expenses once again so go ahead and click on that use in formula drop down and find total expenses once again verifying that these instances match the column 2,150 2,150 26 26 good hopefully you're starting to see here that named ranges can really pay off when

it comes to building out database functionality once you've got everything where you want it go ahead and click okay and there you go it's just that easy so the average expense for rent is seventeen thousand seven hundred and ninety I can now change this to something like overhead to see what the average there is in this case 3700 or maybe telephone 20400 pretty cool so it's searching for all available instances in the category column and giving us the total and average expenses I could even link this cell using linking data which we learned about in

a previous lesson to reference this cell meaning that whatever I type inside this cell right here will reflect in both so if I type rent I can get the total expense and the average expense instantly pretty cool huh go ahead and pause the video and take this opportunity to try that for yourself and then come back for the last part all right welcome back so we've got one less box to search in we're trying to find total expenses for category but this time not just the category but a specific division category so in this example

here some if isn't gonna cut it some if only allows for one search criteria instead we're going to need to turn to a some function that allows for multiple ifs oh come on I know you know where I'm going with this yes we're talking about some ifs so using either the insert functions argument box or the math and trig drop-down we're going to find some ifs with an S at the end indicating multiple conditions and here we are back again this time instead of starting with the range it's starting with the sum range which is

the number of fields we want added together in this case here it's going to be total expenses once again so I'm going to click on my use and formula drop down select total expenses the named range that I created earlier and now I can focus on the next field in this case here criteria range one well what's my first range going to be in this case we'll say that the first range is going to be division we're going to be searching inside the division column to find the word east as with all of these I

could click and drag to select that entire column but instead I'm going to use that named range I created in this case the division named range and I can see here east east east east east east yep that all lines up so I'm searching inside the division column for what well I'm searching for the word East here in Sal I ate so whatever I ate says that's the division I'm starting for the next thing it's asking for is criteria range - okay so we're on the next field in this case we're searching for category so

we're going to need to find the category named range total expenses is the sum range criteria range one we're searching for division we're searching for East in the division column we're searching in category for the second range and we're searching for software in cell j8 and there you go now of course you can allow for multiple criterias you can actually have up to a hundred and twenty seven different criterias in this case here however we only need two so having done all that and click OK to lock it in there you go so in the

East Division category of software they spent five thousand eight hundred and seventy-five dollars meanwhile in the North Division for the same category they spend less 3805 what about the North Division in contractors 3,750 what about the West is there a west there is a West and they spent the same amount convenient what about the south Wow the South spent a lot on contractors so it's interesting to see how we can pull this information out how am I going to look into this if this was my company and there you go so if some ifs average

ifs allow for multiple conditions however all of these functions some average count even allow for us to build out database functionality that based on certain conditions pulls out a predetermined two cell and sums averages or counts it up go ahead and take this opportunity to pause the video and try this for yourself it's pretty cool welcome back time for a bit of a brain break let's talk about working with dates inside of Microsoft Excel dates aren't that hard to work with in fact there's a misinterpretation that because their dates they can't be manipulated the same

way regular numbers can it's not the case at all let's go ahead and start with something simple now I'm gonna go ahead and insert today's date in this case the day I'm creating this video is November 24th 2015 but instead of typing that I can let Excel do that because surely it knows right I can use the keyboard shortcut ctrl semicolon to insert today's date go ahead and try that for yourself once again pressing and holding the ctrl key and tapping the semicolon key now we fell into a coma a couple of days ago or

weeks ago and we let a couple of bills slip past maybe we took a really long nap let's say that now we need to calculate how many days overdue we are so we can prioritize which bills are the most overdue and pay those first now how do I calculate that how do I take the due date and find out how many days overdue any one particular bill is well how would I calculate the difference in any other number let's say I had a number 15 and a number 11 if I wanted to calculate what the

difference was between 11 and 15 how would I do that well I would subtract 11 from 15 so why can't I do the same thing with dates well really you can in Excel they actually figured out a way to take two dates and subtract them just like with any function we're gonna go ahead and start with an equal sign and we're gonna take today's date in this case cell c1 and subtract it from the bills due date b5 now I have the foresight here to see that I'm probably gonna autofill this down so I'm gonna

go ahead an absolute reference that's c1 meaning that I always want c1 to be the reference field however I do want b5 to become b6 b7 and so on once you've got this function up and running go ahead and tap the Enter key and there you go so this bill is 10 days overdue now that I've built that I'm just gonna go ahead and autofill down whoof those poor guys at Comcast they're hurtin for that bill with that having been said how does it know how does it take a date and add and subtracted well

Microsoft had to figure out a way to turn dates into numbers go ahead and highlight all of the bill due dates here and from the Home tab find the number command group and let's change the number formatting from custom to general many of you have seen this before but probably by accident what do these numbers mean how could this possibly be a date well it actually is Microsoft had to figure out a way to convert dates into numbers that could be added and subtracted so these are actually the number of days since January 1st 1900

in this case here this date right here is forty two thousand three hundred and twenty two days past January first 1900 the same thing works for today's date if I change the number formatting from custom to general I see 40 2332 so what's 40 2332 - 40 2322 well it's 10 and that is how Microsoft figured out how to add subtract multiply and divide dates let's try another one let's play that how old are you game in this case let's go ahead and start by typing our birthdays in this case my birthday September 11th if

I should give you guys the year will say 1980 so September 11th 1980 and from here now I'd like to calculate how many days it's been since birth so how do I do that how do I calculate how many days between this day and let's turn that back into a date this day well the same way we calculated how many days passed to these bills were subtracting so once again I'm going to start with my equal sign and I'm going to take today's date subtract the birthdate and tap enter so it's been twelve thousand eight

hundred and fifty seven days since my quote-unquote birthday this is the actual birthday this is not my birth year now how do I turn that into an actual age well how many days are in a year was 365 days in a year but there's also leap years to take into account every four years so how do I take that into account well I'm gonna go ahead and start with my equal sign and I'm gonna take the days since birth / let's see there's 365 days in a year every four years of this little there's a

leap year meaning that we're going to divide it by 365 point two five meaning that for every four times we divide into it that accounts for an extra additional value this should account for leap years and from here I'll go ahead and tap the energy so according to this I would be 35 years old there you go it's that easy working with dates in Excel doesn't have to be hard and in fact knowing how they work makes things that much easier go ahead and take this opportunity to try this out for yourself and then join

me after the break welcome back so we're looking at names here on the left-hand side we've got a last name column and a first name column now we started doing this interestingly enough because of form letters you know those form letters those letters you get that tell you your credit has been approved for a $10,000 gift card hooray yeah well we started separating first name and last name because of those form letters however we're starting to get smarter at that and so we don't necessarily need two separate columns but all of our old databases still

have them segmented so how do we bring them back together we can use a text-based function in this case the text-based function is called concatenate it's okay you'll be forgiven for not necessarily knowing how to spell that one fortunately we have the formulas tab text function library so placing your cursor here in cell e5 go ahead and click on the text drop down and thankfully mercifully there is our concatenate tool so go ahead and select concatenate and that'll pop that box up so in this case our text one box is going to be first name

okay with you so far what's our text to box going to be last name cool all right but as I'm looking at this year I see that the formula result is giving me Howard Smith as one word but Howard Smith isn't a name what should have happened there should have been a space in between we actually have to tell Excel to put a space in it's a very literal program so go ahead and delete text too and how do we inject a space with a quotation mark a space bar and another quotation mark indicating one

space so once again the way we did that was with a single set of quotes a space and another set of quotes lastly for text three we'll go ahead and choose the last name once again and now we see the formula result is now Howard Smith instead of Howard Smith once you've done that go ahead and click okay fortunately we don't have to build that any more than once we can simply autofill down by clicking and dragging boom and there you go concatenate pretty cool right there are plenty of real-world uses for this this is

just one common example another fun text function is the left mid and right functions here in the middle I've got nevertheless now nevertheless has three separate words in it currently they're all residing in cell I eight so let's say I'd like to segment them out into their individual parts here in cell h5 I'm going to go ahead and insert yet another text function this time the left function the left function simply allows us to pull a number of specified characters starting from the left side of a given cell the text box here is asking what

cell is the word in in this case I ate now by default it'll pull just one character however in this case I'd like to pull one two three four five characters in order to pull the word never out so I'm gonna go ahead and type five and there you go so by selecting cell I ate and saying I wanted five characters from the left using the left text function I'm now going to hopefully get the word never in this box right here let's click OK to lock it in there it is never let's go ahead

and try mid once again I'm gonna go ahead and find my text drop down and there's mid the first thing it asks for just like with the left function is where is the text well it's in cell I 8 again now this time it's got two fields after the text a start number and then a number of characters so the start number is the number of characters from the left you'd like to start before starting to collect a number of characters now in this case the first character is number one so how many cells do

you want to skimp before you start collecting numbers in this case we'd like to skip never so we want to start one two three four five six we want to start at character number six okay how many characters from character number six do we want to collect well if we're pulling the word the out of the middle of this we need three characters so starting from six we want th and E and there you go in the formula result we see the and we're going to go ahead and click okay the last function right so

if left pulled a certain segmented number of characters from the left and mid pulled a certain segment into characters from the middle go ahead and take a wild guess wherever you think you're going with this right function here using the text function library drop-down we're gonna find right just like the left function it asks for us to start with where is the text got I ate the number of characters is asking for from here how many characters from the right is the word less in this case it's four characters from the right and there you

go go ahead and click okay to lock it in and there you have it never the less all pulled from one cell pretty cool go ahead and take this opportunity to try that out for yourself hello and welcome back we're gonna get right into it when we're talking about working with inside of Excel there's a lot of different things that that can mean it can mean we're gonna be typing a bunch of stuff into a spreadsheet it can mean that we're analyzing reports as they're coming out of a database it can also mean taking lots

of reports from lots of different places and consolidating them into one small spot the benefits of this of course is that we don't have to manually do it ourselves if we know how to do it in this particular instance I'm looking at database outputs for my Connecticut Division my main division my New Hampshire division and between all three of those they're all tabulating the same things software training maintenance and miscellaneous expenditures for quarters one through four what I'd like to do is take all of these reports from all of these different sources and consolidate them

here into the all division summary so let's go ahead and take this opportunity to do just that the first thing we're going to need to do is start here from the summary page where we'd like the output to be we're going to go ahead and highlight all of these empty fields from item down to the 0 at the bottom of quarter for totals we're gonna highlight all of this because we'd like to maintain this structure from all of the different outputs now that we've highlighted these empty fields go ahead and find the consolidate data tool

this can be found inside the shocking data tab on the far right hand side with the word consolidate find that consolidate tool and give it a click the first thing it's going to be asking for is what kind of function are we using in this case because we're consolidating all of these individual items into a single division summary we're going to be summing the next thing it's asking for is our first reference we'll go ahead and start in Connecticut our first reference is going to include all of the heading items to and including the quarter

for totals once you've highlighted all of that go ahead and click Add and then we'll add Connecticut in all of those fields to the reference field let's go ahead and jump over to Maine for the next one conveniently those dancing ants continue to remain exactly where we want them so all I have to do now is click Add again one last time New Hampshire once again everything is exactly where I need it to be of course he wanted double-check that to be the case every single time and we're going to go ahead and click Add

having pulled from all three resources in this case Connecticut Maine and New Hampshire all we've had to do now is determine where to use the labels from and in this case the labels exist in the top row as well as the left column so both will be check boxed once we've got everything up and running verify that all references are in place we're gonna go ahead and click OK let's lock this in and just like that everything has been consolidated into my all division summary from all three worksheets we can verify by going to Connecticut

and let's say for quarter 1 software Connecticut pulled 200 Maine 400 that's 600 total so New Hampshire must have 200 coming out to 800 total check it out that's pretty cool go ahead and take this opportunity to try it out for yourself once again the way we got here was starting by highlighting where we'd like the output to be we then turned on the consolidate tool from here we selected each individual reference and clicked ad for each of the three state divisions from there we told which rabl we told it which labels to use in

this case the top and left column and then clicked ok go ahead and try that for yourself and join me after the break welcome back so now you've gotten a taste of consolidated data now that one worked really well but it also had everything in the exact same order so software is in the same place on every single sheet and so as quarters one through four let's talk about one where everything might not be exactly the same here I'm looking at consolidated sales data for Henry Albertson alan patel and carolyn Bren Shaw now in each

of these they're selling different products in some instances but even when they're selling the same product it's not in the same place so in this example here we're not necessarily going to have the luxury of being able to highlight and select so that we can autofill it into some pre-formatted structures go ahead and place your cursor and sell a five here and we're gonna go ahead and start with the consolidate button once again reference we're gonna go ahead and start with Henry Albertson once again we are going to include the headers so in this case

the Albertson worksheet from cell a4 to c7 once again we're gonna click Add we'll go ahead and jump over to Allen Patel fortunately the flashing marquee is exactly where we need it to be and we can just click Add lastly in this case for Caroline Bren Shaw there's a line missing here that wasn't missing for the other two so we're gonna need to readjust the flashing marquee and then click Add once we've got all three references in place once again we need to verify where the labels are in this case the labels still continue to

be on the top as well as on the left so with that having been said go ahead and click OK and there you go it's that easy now let's verify to make sure that it matched up everything with what it should have been in this case now we see there are four lines now in none of these were there four lines so already that's a good sign let's take a look at encyclopedia Henry Albertson sold $34 okay oh boy 54 so 34 plus 54 we're talking about 88 meaning that should mean that Caroline didn't sell

any encyclopedias because that's already 88 dollars so I'm gonna go ahead and jump over and she did not so that comes out to 88 and in fact we can verify that with each individual item it's actually verified each individual row value identified it and matched it up give us our sales summation and commission go ahead and try that for yourself and join me after the break welcome back so enough of the cupcake talk let's talk about something a little bit more substantial in this case transportation and shipping costs now it's never too soon to prepare

for the next holiday season chopped in my shipping costs the following constraints must be put in place each of these plants must produce at least 20 units a quarter to justify remaining open Charlotte has a maximum capacity of 92 units per quarter Portland 45 and Quebec 55 plant production must equal the requirements of the warehouse since there is a short shelf life what is the lowest possible cost of shipping we can achieve so once again we're gonna be turning to our solver tool to calculate this in this case here what's our objective we're trying to

calculate the lowest possible cost of shipping so our objective cell is going to be the total cost once again in this case h7 only now instead of calculating the max we'd like to calculate the minimum the next thing is asking for is just like last time what variable cells do we have well in this case we're actually going to be handing over all of this from b5 to e6 we're surrendering control of all of the product shipping so be 46 are the variable cells with that in play the next thing we need to do is

simply implement the constraints because at this stage right here the lowest possible option by changing these values is zero so let's go ahead and add some constraints each plant must produce at least 20 units a quarter to justify remaining open so something must be greater than or equal to 20 and in this case this is specific to each plant in each corner so that's all the cells b4 through e6 must be greater than or equal to 20 so that's our first cell reference we're gonna go ahead and lock that in by clicking Add Charlotte has

a maximum capacity of 92 units per quarter so that's going to apply to everyone in Charlotte each of these cells at a minimum are at a maximum rather must have at least 92 units so Charlotte in each quarter must be less than or equal to 92 Portland must be less than or equal to 45 and Quebec with a maximum capacity of 55 units must be less than or equal to 55 per quarter the last constraint plant production must equal the requirements of the warehouse since there is a short shelf life this means that on each

quarter these fields must equal these fields so in Quarter 1 we absolutely have to create a hundred and eighty units meanwhile in quarter two we absolutely can only increase to 80 units one hundred and ninety four quarter three and one hundred and sixty four quarter for this is our last constraint so instead of clicking add we're going to click OK this time now we have five separate constraints that's a good chunk of change right here now there's really nothing left to be said except do we have our objective sure minimize the total shipping costs do

we have variable cells yes control how much output in each plant per quarter and do we have the constraints yes we've implemented all of them no plant must produce less than 20 units per quarter Charlotte must produce at a maximum ninety-two units Portland and a maximum must create 45 and Quebec and a maximum must create 55 now each of these plants has its own shipping per unit cost so now the only thing that's left for us to do is leave it to Excel to find out exactly how much we should produce we're go ahead and

click solve and just like that it is calculated the lowest total possible cost of shipping is 1011 dollars in almost all instances it seems to have overloaded Charlotte now logic might dictate that we would try to overload Portland but Portland has only 45 units max so naturally Charlotte being the second cheapest shipping option maxed out in nearly every quarter except for quarter to because of limited warehouse capacity Microsoft figured all of that out by just implementing these constraints pretty cool to restore back to original values simply check restore original values and uncheck return to solve

for parameter dialog which brings us back to square one go ahead and pause the video and take this opportunity to try this out for yourself I'll meet you after the break welcome back so now that we've seen that there are some tools available that allow us to kind of offload this decision-making process to excel especially when it comes to just something as simple as consolidating data it makes you wonder what else can Excel do for us for this next part I'd like to talk about a tool that's actually not readily available inside of Excel it's

a tool called the solver tool as we're looking at this first example it says the client is paying per item oh we're a bakery now cool the client is paying per item calculate the highest total cost we could sell the treats for and then we've got some constraints ah alright I'm getting a little bit of PTSD back to my SAT days something about a train leaving a station in blah blah blah I really don't want to have to make any sort of effort on this right how would you even go about solving this we need

400 desserts at least 50 of each dessert you can't have more than 100 how do you solve this I wouldn't want to go through this process and frankly you don't have to Excel actually has a tool available that's designed to solve complex questions like this with specific limited parameters however in order to use it we have to go install it first now don't worry you don't have to leave Excel it's relatively easy to find go ahead and dive in with me to the file tab inside the file tab go ahead and jump into options once

we've jumped into options we're gonna go ahead and need to find the add-ins tab here here inside add-ins we're going to need to find the manage add-ins and click go and it's here that will see the solver add-in here at the bottom go ahead and check box that and once you've checked it go ahead and click OK it only takes about a second to download and once it's done you'll see it here on the far right-hand side of the data tab the solver tool so let's go ahead and turn it on now the solver tool

is an example of what we call what-if analysis meaning that we can say what if it had all of these constraints and we were trying to find a specific output the first thing we're going to need is to set an objective meaning one cell that we'd like to control the output of in this case we're trying to control the total cost we want to find the highest total cost we could sell the treats for so our objective is going to be to set this cell right here to the highest possible option so here set the

objective d8 to max the next thing we're going to need to set are the variable cells we're actually going to give control of some of these cells to excel in order for it to determine what the best combination of certain cells are in order to get us the output we want in this case the highest possible in this case our variable cells will be the quantity of each particular bakery item so the variable cells are going to be b4 through b7 once you've selected b4 through b7 go ahead and click down and away so our

variable cells are going to be here Excel is going to be able to control all of these cells right here however if we have it just go right now the max could be anything it'll just go on for infinity we need to set some parameters some constraints and that's where this comes into play subject to the constraints go ahead and find add and give that a click we've got the add constraint box so the first constraint we need 400 desserts okay so 400 desserts what cell must equal 400 so I'm gonna go ahead and change

this to equals 400 okay what cell must equal 400 well it would probably be the total quantity of all desserts so here be 8b8 must equal 400 so now we're converting this sentence into an Excel phrased structure once you've put that into play go ahead and click Add to move on to the next constraint we need at least 50 of each dessert okay so something must be greater than or equal to 50 what cells must be greater than or equal to 50 well it says each dessert so that means all of these cinnamon buns cronuts

cheesecake and brownies must all be greater than or equal to 50 so here in the cell reference I'm gonna click and drag to select b4 through b7 must be greater than or equal to 50 and I'm going to go ahead and click Add the last constraint is that we cannot make more than a hundred brownies so something must be less than or equal to 100 and in this case it says we cannot make more than 100 brownies so this is specifically related to the quantity of brownies b.7 must be less than or equal to 100

now that's our last constraint so instead of clicking add I'm going to click OK and there you have it so we have our objective cell meaning that we want to control this one cell and its output by changing the variable cells in this case all of the quantity fields Excel is going to be able to try all the different varieties of options here to find the maximum possible output for the objective subject to these constraints no desert may have less than 50 you must have less than or equal to 100 brownies and all desserts must

total up to 400 once we've got all of these settings in place the only thing that's left to do is solve deep breath and check it out so it calculated that it would need fifty cinnamon buns 200 cronuts fifty strawberry cheesecakes and a hundred marble brownies so the maximum total cost possible with all these constraints in place is 1525 dollars pretty cool in order to get back to our original solver parameters we're going to check box return to solver parameters dialog and we're going to click restore original values but it's pretty cool to see that

we were able to set these conditions and it met all of them and in a split second figured out exactly what we would need in order to get the maximum desired output go ahead and select restore original values and check box return to solver parameters dialog to jump back go ahead and pause the video and take this opportunity to try this again for yourself making sure that you follow through on all the individual steps I'll meet you after the break welcome back so that's the solver tool in a nutshell now the use cases for that

are enormous but it does come with a little bit of practice of course you're gonna have access to these practice files after the session as well feel free to utilize them to get a little bit more familiar with all the tools and what they're capable of now the next thing I'd like to talk to you guys about is very much in the same vein of the solver tool which is what if what if certain things happen what if there are certain constraints in this particular instance we're looking at company sales projections based on certain sales

growth quarter to quarter to quarter with that having been said however there are going to be plenty of instances where certain things impact our predicted sales growth and we'd like to be able to view all of them to do that we're going to need the scenario manager the scenario manager is only available underneath the what-if analysis drop down here inside the data tab so underneath what if analysis find the scenario manager you're going to get this little pop-up right here now currently there are no scenarios so I'm going to go ahead and click Add to

change that now currently sales growth is predicted at three point three percent in the north two point three in the east four point three in the West and one point one percent in the south now we're planning potentially a rapid expansion in the south because that's our weakest market we're talking an aggressive advertising campaign new locations the whole shebang this scenario is going to be called southern expansion so if the board approves our expansion into the south we're going to be able to increase our predicted sales growth by four percent so let's go ahead and

tick date so let's go ahead and say that in this scenario the southern expansion scenario we're gonna be changing this cell from 1.1 percent to 5.1 percent now of course we do have the ability to add comments as well and in this case we'll say massive expansion the southern region mass advertising campaign in this particular instance comments can be a really useful tool when it comes to elaborating on what these scenarios represent now of course we want to ensure that we're actually preventing changes so that we don't accidentally overwrite our current predicted sales growth go

ahead and click OK so go ahead and enter the value for each of the changing cells in this case here we'd like to change from one point one percent or point zero one one to point zero five one indicating a 5.1 percent predicted sales growth if we like to add additional fields we can but in this case this is all I want to change and we're going to click OK so that's our southern expansion let's see what that would look like here I'm gonna go ahead and click show and there you go just like that

it's changed that 5.1% so now I can see what Southern's yearly total could be with that 5% quarterly growth let's go ahead and try a different one I'm gonna go ahead and click Add again and this time let's go ahead and say that this is for a new potential CEO our company's been operating without a CEO for several months now and because of that morale is low however if we were to implement a new CEO we could predict potentially growth in the double digits across all of our regions so in this case changing cells is

going to impact all regions we'll be something to the effect of a new CEO will increase morale across the company leading to higher productivity all right so let's go ahead and click OK and we'll say that for each of these changing cells will increase by 2% so three point three becomes five point three point two three or two point three becomes four point three four point three becomes six point three and the original 1.1 becomes 3 point 1 okay to lock it in so we have our new CEO our southern expansion and we're going to

want to make sure to add our original as well so I'm going to go ahead and click Add the standard scenario this will impact the same number of cells and we'll simply leave everything the way it was from the beginning so we've got our standard scenario our southern expansion and Arce new CEO by viewing each individual one I can see how this would impact the entire company for example compared to the standard scenario we're only making right now on projected 1 million three hundred and twenty six thousand however with the new CEO we could be

looking at 1 million three hundred and sixty seven thousand interesting isn't it versus the southern expansion which puts us at 1 million 375,000 so it's interesting to see how this can impact how we come at our scenario projections now if you'd like to get a view of all of these different scenarios you can use the summary tool by clicking on the summary tool we can go ahead and say hey what's the result cell and how would we like to report that so in this case I'd like to build a scenario summary based on my yearly

total once I've selected the result cell I'll click OK and here we go based on a standard scenario we're looking at 1 million three hundred and twenty-six thousand with a new CEO we're talking 1 million three hundred and sixty seven thousand and with southern expansion we're talking 1 million 375,000 so southern expansion actually Trump's getting a new CEO it can be interesting to see how these scenarios play out and using this information can actually help you make these kind of decisions not bad go ahead and take this opportunity to try this out for yourself I'd

like to point out very briefly that this is a new worksheet so ensure that you get back to the original scenarios before you go too far go ahead and pause the video and try this out for yourself all right so let's keep on keepin on when it comes to what-ifs there is no greater what if then credit card payment plans now I surely can't be the only one that wonders what if my interest rate was lower what if I paid more per month what if I took less time to pay it off what were my

payments look like how much would it take to pay off my current balance if I keep doing things the way I am in all of these instances we can actually turn to excel to answer these questions for us now we've taken this opportunity ahead of time to calculate what these payments would look like based on these projections if the current balance is $8,000 at fourteen point two percent APR and you take 48 months to pay it off the average monthly payment is two hundred and nineteen dollars without having been said of course we can change

these details to make them a bit more realistic depending on your circumstances maybe six thousand maybe twelve thousand but with each of these all of the payment plans adjust now having calculated that there's plenty of what-ifs for example what if my interest rate was 12% or 15% what would my payments look like alternatively what if my monthly payment plan was for 12 months versus 72 in all of these instances I can actually ask excel these what-if questions and it will handle that for me in this instance right here we've got a single variable data table

set up with a single variable data table and this formula already in place calculating my payments based on this data I can actually substitute these interest rates one by one and get their predicted payment in that cell next to it so let's go ahead and do that this is called data table analysis and in order to trigger data table analysis we're gonna need to highlight everything so go ahead and take your cursor and highlight everything from the top left corner including the payment here down to the bottom right corner now from here we're gonna need

to trigger our what-if analysis tool the data table go ahead and find the data table and give it a click the data table tool is incredibly simple it's asking for a row input cell and a column input cell now because this is a single variable data table we're only going to need one variable in this instance when we're doing a single variable data table that one input is going to be the column input cell so what cell is going to be interchanged in the column input which of these cells is going to be subbed out

inside the original formula and in this case it's interest rate we're substituting 12% for 14% and then 12.5 and then 13 each of these is going to take its turn substituting 14.2% interest rate to get a predicted payment all you have to do is click OK and there you go we now get to answer the question what if what if our payment was at 12% interest well then our payments would be two hundred and ten dollars a month instead of two hundred and nineteen on the other side what if it was at 15% well then

we're talking 222 versus 219 pretty cool now that's with substituting one variable however we also have the ability to substitute multiple variables with multiple very idatd tables it's just as simple first things first we're going to need to highlight the entire data set once we've done that we're going to need to trigger our data table tool once again by clicking on the what-if analysis drop-down we'll select data table now because this is a multivariable data table we're actually going to be using a row input and a column input the first thing we'll choose is our

column input which of these values is being introduced column by column well it's going to be the interest rate we're going to take this percentage and multiply it in every single column so this value is going to go from column to column to column and so on so that's where our interest rates going to go the next thing that's asking for is our row input cell in this case what are these values replacing in the original formula in this case they're replacing the months so I'm going to click on the months value here inside the

original formula so I've got my row input cell which is my month's field being substituted inside the original formula and I've got my column input cell which is my interest rate being substituted inside the original formula all that's left to do is click OK and just like that we can see exactly what this would look like so if we took a hundred and ninety months to pay off this $8,000 balance at 12 percent APR we're talking 94 dollars a month if we took 420 months at 12 percent APR we're talking eighty one dollars a month

so it's interesting to see how we can work with this here but it's also very very depressing to see that it would take thirty-six that's 25 years I believe in order to pay off an $8,000 balance at 12% APR 18 2002 and try this data table out for yourself i heavily advise against putting your own information in there for your own sanity I'll meet you after the break welcome back so we've talked about a lot in these last couple of videos in these last few videos we talked specifically about asking Excel what-if questions and we

saw that there are some pretty amazing things that we can get Excel to figure out for us we're gonna shift gears a little bit but the topic we're gonna be focusing on is no less amazing the ability to take a number of different steps and automate them using something called macros macros are these incredible powerful tools that are essentially micro programs that allow us to tell Excel step by step by step what we'd like it to do and then have it do that without us having to go through the whole process every single time this

allows us to remove more tedious steps that don't have tools readily available for them however in order to get macros to work we need access to the appropriate tools there's actually a tool tab available that isn't already here through this entire lesson we've had the file tab the home tab insert page layout but there's a tab that's actually been missing this entire time the developer tab so we're gonna need to turn that tab on in order to turn that tab on simply move your cursor up to the very top of the ribbon and right-click on

any of the tabs you'll see that we have the ability to customize the ribbon go ahead and find that customize the ribbon button and give it a click on the left hand side you'll see some popular commands and on the right hand side you'll see all of our tabs all of them have checkboxes except the Developer tab consequently this is the tab we're going to need access to in order to fully build out our macros so go ahead and check out the Developer tab and click OK and just like that we now have access to

the developer tab go ahead and pause the video and take this opportunity to catch up all right now that we've got a Developer tab on let's go ahead and dive in and see what's what and we see here on the left-hand side we actually have an entire command group that's essentially dedicated to building out macros this is where we'll be spending the remaining time focusing on now what kind of macro would we want to build most popular macros are traditionally ones that automate some tedious process of ours let's do something simple let's say I'd like

a macro that when I type my name here or better yet I've got a macro that will type my name drop down a cell type my company name drop down and type my email address it would be nice if I could have a macro that did that for me so let's go ahead and delete that and in order to record a macro I'm gonna need to find that record macro button which can be found here on the left hand side of the Developer tab or here in the bottom left in the status bar either one

will work but before I do that I'm gonna need to talk about this button right here the use relative references button now it's important to note that when you're recording a macro by default it's using absolute referencing meaning that if I go ahead and type my name here and hit enter if I'm using relative referencing it notes that I went one cell down but because it's using absolute referencing it's actually instead making a specific note about this particular cell g2 so if I ever try to run this macro somewhere like let's say j1 it's gonna

type my name but then it's going to go straight to g2 I don't want that I just wanted to go one cell down so you're gonna need to keep that in mind when using that kind of referencing you're going to need to turn on relative references so go ahead and give it a click so we now have relative references turned on and I'm now gonna record my first macro well go ahead and call this macro name my first macro it's important to note that Microsoft doesn't really like spaces in these so no spaces in the

macro name it's also gonna ask for a keyboard shortcut key role hold off on that one for now where do you want to store this macro by default it's going to sort in just this workbook however we do have the ability to store this macro in every new workbook we also have the ability to store it in our personal macro workbook where we can make it available to all documents at some point or another for the purposes of this class we're going to leave it in this workbook and we're going to give it a description

types my name drops of cell types my company drops another cell types my email okay so I've got my macro name no keyboard shortcut this time I'm gonna store the macro in this workbook only and I've got a description so I'm gonna go ahead and click OK and I am now recording my macro now it's really important to note that it doesn't really like when you click on cells so try to avoid clicking in this particular instance I'm here in cell G 1 and I've got relative referencing on so that's not really gonna matter all

it's gonna matter is from start I'm gonna type my name Shawn bugler and I'm gonna hit the enter key so I've dropped one cell down and I'm now going to type my company name learn it and I'm gonna drop another cell down and lastly I'm gonna type my company email s bugler at now my email com once I've done that I've got everything done this is all I wanted my macro for now so I'm gonna stop recording using either the stop recording button up here at the top or the stop recording button here on the

bottom left either one will work and just like that you've recorded your very first macro but how do we know what worked let's go ahead and delete that I'm gonna go ahead and place my cursor here and cell f1 and I'd like to go ahead and run this macro where are my macros here we go the macros button so I'm gonna go ahead and click on the macros button and there's my first macro all I want to do now is just run it so I'm going to click run boom there it goes it automated my

name company name and my email how about that go ahead and pause the video and take this opportunity to try this out for yourself record your own first macro where you type your name company name and your not email remember this worked because we used relative references so make sure that toggle is turned on how do you know it's on well when it's off you won't see it highlighted so keep that in mind and go ahead and try this out for yourself welcome back so we've recorded and run our first macro pretty cool right but

let's say that I'm not necessarily in love with the fact that it types my email in fact maybe I'd like it to type something else completely but the problem is I've already recorded my macro that is lost in the code forever right not necessarily however in order to edit this macro I'm gonna need to come to terms of the fact that I'm gonna be working with Visual Basic this is very very early days code that Microsoft used when they were first working with Microsoft Office it's very very powerful code without having been said it's not

always the most intuitive so it does take a little bit of practice to work with now let's go ahead and click on that macros button again here I'm looking at my first macro and what I'd like to do is step into this macro so go ahead and click on my first macro and click step into and it's automatically going to open you up into your first macro here's our description and then underneath that there's the code you built that now with that having been said we can actually take a look at this and using a

bit of contextual analysis figure out exactly what was going on so here we have active cell so whatever our active cell was this is where it starts typing next from the active cell offset by one meaning that it goes down one cell it's now going to using that active cell type the word learn it the next line of code offsets the cell by one again and types the email address so this is pretty simple code now if at any point I'm not in love with part of this code I can actually work on this now

you'll notice that it's got the description up here but it doesn't seem to be ruining the code in any way so how is it that this is just sitting here and it's not impacting anything well if you'll notice at the beginning of each line there's a little apostrophe these are what we call comments comments don't run and in fact these apostrophes signify as Excel is running this code to ignore all lines that start with an apostrophe so if I don't necessarily like that it's typing my email address I can actually go to that line of

code and put an apostrophe in front of it and see now it's turned green so I've common ties to this line of code now why did I do that instead of deleting the code well I don't necessarily want a delete code that I'm not entirely familiar with by comment izing this code I can run it and make sure that didn't break the rest of my macro before I actually delete the line of code so I'm going to go ahead and comment eyes it and in order to get back into it you'll notice that we're currently

inside a break we're gonna need to click that play button in order to jump back into our macro so I'm going to click continue and now I'd like to get back that no longer says break at the top so I'd like to get back into Excel top left-hand corner you'll see we have the Excel logo button you'll also notice that in parentheses it gives us a keyboard shortcut alt f11 either clicking on that button or using the keyboard shortcut all to f11 let's jump back into Excel now in this case here I've now edited my

line of code so in theory I should have removed that one line that typed in my email there's only one way to find out though so I'm gonna click on my macros button and I'm going to click run and check it out where it normally would have typed my email it now cut that line of code out completely pretty cool let's build one more style of code here let's say I'd like to change the formatting of the cell first things first I'd like to start recording so I'm going to click on the record button in

the bottom left-hand corner here I'll call this one the formatting macro this time I'm going to give it a keyboard shortcut of control are now it's important to note that you can give this shortcut key any letter it will override any existing keyboard shortcuts inside of Excel however so do keep that in mind don't give it a keyboard shortcut for a shortcut that you use all the time my description will be changes the way the cell looks and I'm gonna click okay so once again I'm now recording and my active cell is right over my

name so I'll go ahead and change the way this cell looks I'll make the font so let's say 18 point font I'll change the font style to Algerian and I'm going to go ahead and change the font color to purple with a call center it too so just like that I've implemented quite a few steps here I'm now gonna go ahead and hit the enter key to drop down a cell to do the exact same thing to the cell beneath it so in this case here this cell I'm gonna go ahead and change to Broadway

I'm gonna increase the font size to 14 point font also going to Center this I'm going to change the fill color too I think in the light shade of blue there you go now that I've made quite a few changes to this all while recording my macro I'm now gonna stop recording okay cool so I've done it now let's go ahead and see what that does for me here it's lh1 I'm gonna run my original macro so in this case here the Developer tab committed to macros select my first macro and run it and now

I'm gonna place my cursor back on top of this text right here and using my keyboard shortcut of ctrl R I'm now going to run my second macro and check it out wow that happened fast so that macro ran on top of the existing text and it did exactly what I did when I was recording it again all of that took place because I was using relative referencing so please do keep that in mind not bad so go ahead and take this opportunity to pause the video and try recording your second macro to change your

first macro is text and then come back welcome back so now at this stage I've got two macros but I have to go through this whole process of running the first one that seems a little tedious and in fact I might be kind of regretting not adding a keyboard shortcut for that first one of course we can add keyboard shortcuts later by selecting my first macro and clicking edit I can always add a keyboard shortcut here however I don't necessarily want to have to do that so instead what I'm going to do is I'm going

to assign a button up here in the quick access toolbar to run that macro that types my name and my company to add a button to my quick access toolbar I'm going to need to click on the drop down arrow to the right and select more commands for those of you that were in the first part of the video you've already seen how we can edit the quick access toolbar to add some commonly used tools however within this you can also add macros by clicking on the drop down arrow on the right hand side from

the choose commands from drop down you can choose macros and check it out here's my first macro I'm gonna go ahead and click Add and I'm going to click okay so now up here at the top there's a button for my first macro I'm gonna go ahead and give it a click to make sure it works and there it is instantly pasting my name and my company so you just do this all day but buh-buh-buh-buh-buh buhbuh let's take this opportunity to create a button for your first macro and toss it into the quick access toolbar

once again the way we got there by is by clicking on the drop-down arrow and selecting more commands from the more commands window we selected instead of popular commands macros we then found my first macro and clicked add to add it to the quick access toolbar go ahead and try that for yourself and then come back welcome back so we've recorded two macros we've edited one of them in Visual Basic and we've created a button in the quick access toolbar that runs one of our macros I'd like to show you one other way to run

a macro and that's using a button here on the worksheet now in order to get this button we're going to need to be inside the Developer tab here inside the Developer tab find the insert drop down inside the controls command group and give it a click it's here in this drop-down that you'll see we have many form controls now there's form controls and ActiveX form controls it's really important to remember choose from the form controls option ActiveX controls our legacy tools and in most cases don't necessarily play as nicely with some of the newer tools

as regular form controls do here inside form controls I've got a button so I'm going to go ahead and click that button and I don't see a button but my cursor is actually a crosshair so I can actually now just click and drag to draw my button now as soon as I draw that button and let go it's automatically going to try and get me to assign a macro if I don't already have a macro I can record one but in this case I'd like to apply it my formatting macro to this button and click

OK now it says button 5 button 5 is not really intuitive so I'm gonna call this the stylize ur yeah that sounds pretty cool so I've got my stylize er button and I'm just gonna bring it over here so now I've got my stylize er button which should trigger this fancy stuff right here but I'm gonna go ahead and need to run my first macro to get some text so I'm gonna go up to my quick access toolbar I'm gonna run my first macro boom there's my name and company now gonna bring my cursor back

up and now using this button I'm gonna run the style either and there you go I now ran a second macro using this button now these are simple macros but start to imagine all the different things that you can do all the different dashboards you can build that are interactive pivot tables that auto refresh all these different things and it all starts with knowing that you can even make macros go ahead and take this opportunity to inject a button into your workbook and assign your second macro to that and then once you've done that run

your first macro and then using the button run your second one pause the video and I'll meet you after the break all right welcome back that brings us to the end of Excel 2016 now that's not to say that you know everything about Excel now by no means we've talked about a lot but there's so much more to be said about what Excel is capable of however with the tools you've been provided you're now capable of doing so much more than the average user inside of Excel you can manipulate data organize it sort it filter

it crunch numbers using pivot tables ask what if questions using the solver tool analyze data using charts and sparklines and automated all using macros now there are plenty of other classes available through learning such as the power user course and the power pivot courses that we have available if those are the things that you're interested in click around the website you'll be able to find them I'd like to thank you for spending the last few hours listening to these videos if you're listening to just this last part here why why this video thank you so

much I hope this has been worth it to you this is Shawn bugler and thank you so much for joining me for this video thanks for watching don't forget we also offer live classes and office applications professional development and private training visit learning comm for more details please remember to Like and subscribe and let us know your thoughts in the comments thank you for choosing learn it [Music] you