How to Use Microsoft Excel Effectively (Video Training)

so [Music] [Music] so [Music] [Music] oh [Music] so [Music] [Music] good afternoon everyone thank you so much for joining us today on our session on excel i'm amanda pritchard i'm the microsoft 365 specialist at ut dallas and i just want to go over a couple of things in teams live in case this is your first time joining us here we do have chat enabled it's on the right side bar of your screen so if you want to engage with us ask any questions if you need clarification on anything that we're demoing today please feel free to drop a line in that chat it is moderated so we look at it review it and then we'll get back with a response to you we do also have closed captioning available if you'd like to enable that it's at the bottom right corner of your screen and you can turn it on there so uh we're going to be posting a few links in the chat i think we've already got our welcome message out there if you see that hover over that welcome message and give it a thumbs up that's just going to let us know that you're able to find the chat and engage with us because we want to make sure that you're able to have a voice in today's session your camera and your mic are automatically muted so feel free to enjoy your snacks and kind of just know that you have that that quiet time and um we won't be able to see or hear anything that you're doing in addition a really great feature in the live event if you've never used it before if you hover over your screen now as an attendee you have the ability to hover over your screen you'll see a little line you can pause or rewind any part of the session while you're watching it live so if there's something that our speaker is talking to you about that you want to refresh her on you can always jump back in time and go over it again okay before we get started i see we've got um people that are still joining so i wanted to go over a couple of upcoming events that are happening in the office of information technology the big one that's happening this week in two days i know the technology bar is officially having its grand opening if you have already visited the technology bar why don't you throw a comment in the chat i would love to see those that have already been there and get an idea of who's already made their way to the student union to visit us at the bar this is a location on campus and the lower level of the student union and it is designed to have tech support on tap for anyone that needs it so whether you're looking at getting better security on your system if you need help with duo or vpn whatever the case may be you can walk up and get that assistance we're going to put the link in the chat so that you can if you would like to join you'll be able to join the live stream of the ribbon cutting it's going to be virtual in the morning so you're able to just join and get a preview of what the space looks like and listen to some of our keynote speakers that morning then in the afternoon is the in-person activities if you would like to come out and join me at the bar i'll be there from 12 to 5 in the afternoon we have a full agenda of different campus groups we've got esports the chess club multicultural center um career center so there's some amazing groups that are going to be utilizing our space to talk a little bit about how they use technology to better support students in campus so we wanted to invite you it's an all-day event you can either join virtually or come in the afternoon but we want to show off this space and show how you're able to leverage it as faculty or staff or student it is open to everyone so we've got those links posted in the uh chat if you'd like to stop by we would welcome you there i'm checking the chat really quick too to see if anybody has mentioned if they've already visited us i'm not seeing i'm not seeing very many so it'll be a great opportunity if you want to join us on thursday to be able to see our new space in action so okay the next thing i just wanted to call your attention to we have a very active instagram account and there right now this very moment we have a giveaway going on so if you would like to participate in a giveaway we're going to pull a winner at the end of today's session i've got the qr code there on the screen so if you want to pull up your phone you just hover over your camera you can hover over that qr code and it's going to direct you to our instagram account if you already follow you'll see there's a picture here where we're tagging people that have helped us excel whatever that is for you however somebody has helped to support you to excel in what you're doing and to excel in supporting campus go ahead and tag them in that post it counts as an entry it's just a way to give somebody some recognition of how they help support you and then we're going to do a drawing at the end of today's session for uh whoever wins uh wins that'll just be a random winner so we also have the link in our chat you're able to pull it up there if you're not able to access that qr code okay well great well hopefully we'll get lots of participation there i'm going to leave that up for just a second i know that we're all here for one reason and that is to enhance our ability and using excel whether it's your your use of formulas however you like to do that data analytics your spreadsheets we have an excel and you know i had to presenter with us today from microsoft who's going to show you how you can accelerate your use of microsoft cell so uh without further ado i want you all to please give a warm welcome neil i'll stop sharing so that you could start pulling up your uh demo information here um and i'll get your screen to share it as well but neil is here to present with us to whether you're uh if you're just a beginner user if you've used excel some that still could use a little assistance neil's going to take it away and help make sure that we all know how to properly use this technology and neil if you're talking you are still muted sir i need it anymore hello wonderful thank you do it i was i was trying to i was trying so hard to beat you too and i'm like i know she's going to tell me about me all right uh hi good afternoon um hello ut dallas on this overcast day here in dallas uh my name is neil and i will be your instructor uh here for our microsoft excel basics training um and even though uh i will it'll take me a while to uh forgive amanda for those very bad puns regarding microsoft excel i will be very happy to present to you and show you how to utilize the application itself as part of the microsoft 365 suite so um just to confirm once again you guys are able to see my screen am i correct i can see it yes perfect excellent all right great um alright so let's go ahead and get started let's talk about what we're going to get into today first of all as we stated earlier teams live event that's what we're in right now so you have that ability to ask us any questions also since this is a live event you're able to rewind as well so if you need to go backwards in time say you missed something or something of that nature no need to explain it again you could just go backwards in time so there's these playback uh these playback options down here now these you'll see right beneath the screen so right past where my mouse cannot go so uh you'll find the options for playing and pausing the event as well as rewinding using the little uh little meter that goes across then at the same time you also have the option for closed captions if necessary if you need to turn on those closed captions definitely be my guest if they help you out that's an icon that looks like this it should be found in the right side of the screen then you should also be able to find an icon that looks like a little cog wheel that'll give you your settings for those subtitles actually so things like for instance changing the size and the color of those subtitles furthermore you're able to change your video quality there as well as playback speed so if you need to slow me down then we can do that right there via this option like i said uh that that live event q a should be on the right side there if it's not go ahead and select the two overlapping chat bubbles with a question mark icon up in the top right hand area of the screen and that she'll allow you to then open it if you don't already see it please go ahead and ask any questions that you may have regarding that particular uh this particular content all right let's go ahead and get a little bit more into it like i said excel basics level 100 we do a level 100 and at 200 this is going to be the level 100 so it's all the basics of excel so first and foremost here's our agenda the very first thing that we're going to do is we're going to talk about basic navigation so we're going to talk about how you get around the application as well as the easiest way to find those particular functions especially considering the fact that if you've used any of our microsoft 365 applications especially the traditional office suite word excel powerpoint as well as onenote you'll find that there are a lot of tools and you might not always remember where they are so because of that we will go over navigation then we will talk about basic functionality of formatting the excel workbook and worksheets then we will talk about using formulas this is gonna this isn't gonna be an advanced look at formulas it's gonna be a very basic look at them but it's to be talking about using formulas and functions and validation to work with your data and then um we're going to work with the print process so i'll show you how to utilize that print process with excel workbooks considering the fact that worksheets can go on for a very very very long time uh in in in both directions both horizontally and vertically they can go on for ever so because of that uh you want to be able to uh you want to be able to make sure that you're printing off what you want to print off when you are printing a document so we'll be talking about that all right so the basics of navigation that's the first thing we're going to do we're going to talk about those uh those navigation ribbons uh various tools that we have available and then once again formatting excel we're going to talk about things like not only how to format the different cells that you're working inside of inside of a workbook but also how to create charts as part of that as well so there's some very easy ways to work on charts both creating one from scratch as well as even finding recommendations based on the kind of data that you're working with then we're going to talk about using formulas functions and data validation so that's the next thing that we're going to cover and that's going to be how to do various various things with that data that you actually already have entered into the different cells on your different worksheets and then data validation allowing you to decide to lock specific cells to specifically only um being able to have certain kinds of data inside of them that's also something we'll be able to do with that data validation and then after after that like i said that print process is going to be the last name we cover so that you know that you're printing off exactly what you want to and none of what you don't want to from certain excel workbooks all right very very first thing that we should go over here especially for those of us who are brand new to excel even if you're coming back to excel you got to know your terminology so let's go ahead and get started with that the very first thing we're going to do is go over this quick sheet right here just on some of those basic terms that you should know about when you're going through excel the very first one is the cell if you've ever looked at an excel worksheet before you'll see that that worksheet has a whole bunch of boxes in it it's just a whole grid of cells it's just a whole uh it's it's just an entire grid of cells that's what each and every one of those boxes is called a cell now every one of those cells has a set of coordinates so to speak it uses two different axes uh uses the columns and the rows going across one is alphabetical the other one is numeric and utilizing a combination of the two gives you the exact location of a specific cell on the sheet so that's your cell reference so i'll give you an example uh if i was to go ahead and say that um i want this the the cell that's in column g and row number seven then that would be g7 think about it like playing chess if you've ever seen those people who play chess and they they can rattle off the names of all the different areas of the board um it's just like that then you have your value axis so there's two different kinds of axes there's a value axis and a category axis and these are for when you're creating things like tables or different kinds of information that you're trying to display and you're trying to really organize it and really explain it a value axis is uh is an axis that displays value going from maybe least to greatest usually numeric values so if you're maybe creating some kind of table and you're trying to have like a least to greatest uh numeric value going across or going down that's a value axis then there is a category axis and that is when you're creating an axis of differences of kind as opposed to differences of value so think like for instance i think if you're creating like a like some kind of a table in excel and really you're trying to show how much uh let's see uh maybe you want to see what you're uh if you have a small business and you're trying to see how much you guys made in revenue um from quarter one quarter two quarter three and quarter four the fiscal fiscal quarters of the year um then what you can do is create a category axis quarter one quarter two quarter three quarter four and then for each of those displaying the proper information so that's those are the two different kinds of axes that you can create inside of excel and then i mentioned this earlier this is called data validation data validation is a system where you can decide to set rules for specific cells on the worksheet so that when people especially this is especially very useful when you're working with multiple people on the same on the same document data validation allows you to set it up so that those specific cells can only have a certain kind of data inside of them so you can decide maybe that a certain uh these cells over here can only have numeric values uh they can only have numeric values they cannot have decimal points uh they can only be positive numbers these are different things that you can set so that that that way you're always getting um the uh valid data in certain areas especially if you're using formulas in that particular table speaking of formulas there's the function in the formula and these two play a big part in what we can do in excel now a function first of all is uh is essentially a pre-written uh it's a pre-written formula it takes a value or values and then performs an operation think about it like things like for instance some of the most basic functions are ones that we've learned in mathematics you know um finding the sum of multiple numbers finding the average of multiple numbers with even more advanced functions going even further um and those are ones that we won't be getting into today but i will show you that there is an entire library's worth of them that you can definitely pull apart for yourself then there's the formula and a formula is actually a sequence of values cell references names functions or operators so this is actually this is actually where you can create you can create a formula so that you can find an answer to something you can take multiple cells and then uh and then take some take some cell references and then combine them with a couple of functions and then add some other kinds of information in there and then uh find an answer and that will be what's on that cell that you're typing in that particular formula on and then last but not least there is an argument and an argument is where you can set things up like certain um you can you can decide like the values that a certain function uses so for instance uh you could decide whether or not the type of argument is specific to the function uh for instance some particular kinds of arguments are maybe if there's a certain text that reads in a certain way maybe if there's a particular term inside of that cell like a like like you know four letter word specific four letter word if that if that word is inside that cell then that's an argument that you can then use as part of a function it's a it's a it's part of a formula even that you could use all right so now that we've gone over all of those key terms at the beginning here i think it's now time for us to jump right into our demonstration environment so we're in here inside of microsoft excel and remember as i go through this training please go ahead and ask any questions in that q a i do want to make sure that they are answered speaking of which are there any that we can answer right now before we really get started no questions neil there was just one comment um from an individual that felt a little overwhelmed i think by some of the terminology and i know you were sharing that just to create that basic building block of what things are called and how to identify them but there's no quiz right nobody has to have pressure to memorize every term that you just shared absolutely yeah there's no quiz um yes i introduced those terms um as part of that that slide at the beginning but you'll see the context of those terms as we continue on and then it'll start to make more sense here so hopefully we'll be able to go from there um hopefully always ask those questions if you like i said if you uh find that you're lost or that you need some extra help there just ask a question uh and we will we will be here for you so let us begin okay so first of all we're here inside of the main area and it looks like it just started raining in dallas so i guess um that's that's hopefully we're not you're not picking that up on my side but um here we are in the main area this is where you will first find yourself when you first open up microsoft excel so you'll see that we're in this home section you'll find some options for some brand new things that we can open up maybe a blank workbook for instance um as well as maybe some recent documents that we might have open uh recently so these are some some examples of some other documents that we can bring into uh into excel ones that we have might we might have worked on ones that might have been shared with us etc then there's an entire menu specifically for new documents so if you're trying to create a brand new document this is the area to do so uh you will have your blank workbook option always at the very top but you also have the option of templates and that's what we have right here look at all these templates that we have this isn't even all the templates this is just a couple of them if you were trying to do a specific thing with excel there's likely a template for it for instance let's go ahead and find a template for creating invoices so these templates are coming directly from our database uh in like on the microsoft cloud so you will have access to all of these um and you can use them so if you're if you're you know for instance running a business and you need a billing statement you need uh you need a service invoice different things like this this is a great place to start and then from here you can edit that invoice to add the information that you need it's very much a template so it's just uh you just you just add in the information where you need to then you're good to go as you see that was just one example of one kind of thing that we can find here i'll just type in one more let's go ahead and type in schedule some people like to make a schedule or like an agenda utilizing excel that's another thing that you can use and uh and here you here you see right now we have a whole bunch of different examples of schedules calendars um you know even like things like for instance calorie amortization schedules so for those people who are counting their calories um that that comes into play uh shift calendars as well for if you're working uh you know if you're if you're managing people at in in a workplace and you're trying to make sure that people know when they're supposed to work etc and also the last thing i want to point out from this section is that there are a bunch of different uh options as part of this template system there's a bunch of options of tutorials so you'll find some tutorials inside of the template section right here very very useful for getting started with particular areas of the application and then if you go to the bottom you will find your options for opening documents both from on your local device as well as from cloud locations both in one drive your personal cloud storage and on sharepoint sites that includes the ones that you might be part of if you're part of a specific team in microsoft teams if you're looking for files from a team they're actually stored inside of a sharepoint site of the same name so if you're part of a team that's called you know the octopus project then there will be a site called the octopus project in here that you can then select and then that will be where you'll find those files all right neil before you go any further i'm sorry to interrupt you we had a great question and i know you had excel already pulled up can you show how to navigate to excel from your desktop sure it's just uh if you have it installed on your computer you can just go ahead and select it via the all apps section right here so if you now this is windows 11 but this has always been the way to use it so you'll find the same functionality in windows 10 uh even you know going further back so if i was to go and select the start menu whether it's in the center here or on the left side um you can go to all apps if you don't immediately see it you can go to all apps scroll down and typically it's in the e section and can i add on to that too for our for the utd audience as well if you need to navigate to office.com office.com is kind of the warehouse for us to have all of our microsoft products and one storage location so from there you can see neil's demoing his screen it appears on the left so you for us we do have to log in and authenticate with duo but any of the apps that you need for microsoft that is your one-stop shop for for going and getting instant access to those and you'll know that those are the latest most updated versions available so we always encourage you to go through the web if you're trying to access it and it ensures your documents are stored on the cloud so that you can access them anywhere anytime and then on top of that if you look right here to the right you'll find the option to install office on your device as well so as long as you're logged in with your credentials and you have a valid copy of office which especially if you're using microsoft 365 in the in the university of texas system then you should be able to install office directly on your device and these are the web versions remember this is a short list though this isn't the entire list of all the applications so if you go to the all apps here you'll find all the applications this this list sometimes changes based on which ones you've been using so it's not the entire list but you'll always find it here all right so let's go ahead and backtrack we're going to go back to excel right so we're here in excel and we're gonna go ahead at this time and i'm actually gonna instead of opening a blank workbook at this time i'm actually gonna open up a specific one just so that we have some information on our document now i've created this particular uh document right here a fruit supply document and it has a couple of different uh some some different effects to it that we will be really demonstrating as part of this training so that's the reason why i wanted to bring it up so first of all uh like i said based navigation you'll find your basic functions all across the top here so all of your functionality of microsoft excel is found up here via the different tabs and under each tab you'll have different tools available to you the first one is your home tab which will have your basic text functionalities things like that your font your alignment if you're familiar with word processing system such as microsoft word you might be familiar with your alignment align left align center align right however this is excel so we can do things a little differently here if i was to go down a little bit here and i was going to open up a specific uh say maybe like a specific cell and maybe if i was to resize it as you are able to do with excel you can resize these individual cells like so then what i can do is i can select the cell and maybe i want to place a word inside of this cell just like you see there's words and numbers here i want to place a word here now i can just double click in the cell and type here but i also have the capability of just typing here as well whatever you're typing inside of the formula bar here will enter into whatever cell you currently have selected so that's also another easy way to enter in that information so maybe i just want to type in the word octopus it's kind of octopus kind of day to day i guess i keep referring to octopi uh but here we go we have octopus inside of this section right here in this inside of this particular cell now if you're using alignment you'll find that currently i am aligned to the bottom left hand corner but i wanted to point out that you can align yourself to the top to the middle or to the bottom and then you also can align yourself to the left the center or the right so if i wanted to i can go dead center right in the center of that particular uh cell same thing with the bottom right or the top right so it's all up to you the way that you want to have your octopus positioned or the word the word of yours using then i also have the option to change orientation now this is really useful for different uh display of information on different kinds of tables and such maybe you want to angle the word in certain ways angle it clockwise or counterclockwise clockwise vertical text up and down rotating the text as well so there's a lot of different options that you have here you also can change the uh whether or not the words wrap around the area and this all works with numerics just the same as words and if you noticed when i first started working with this particular cell i adjusted it uh via the edges so i was able to select it and then i was able to go to the different axes once again here's those axes again you can get a visual representation of those axes and how we are able to then go in here and resize them if we need to so you select a specific axis uh like right now i'm on that vertical axis which has the numerics and then there is the horizontal axis that has the alphabetical inde uh indication as well and then because i'm in this specific cell the address of the cell or what we call the cell reference is d 25 and you'll know it when you have it selected because it'll show up there so whichever cell you have selected there's the cell reference so that way you know the name of that cell if you ever need to refer to that cell as you'll find if you start using um things like extra kinds of functions and formulas um you'll want to know the name of that cell that cell reference and that's the one that you use whenever you're trying to refer to this specific cell all right now if we were to keep continuing on i want to talk about some of the other kinds of things that you can bring into excel so we're working with our basic functionality here being able to change that font i want to talk about how you can also format these cells in different ways so for instance maybe what i want to do is i want to have a list of maybe i want to have a list of different numbers right here that i'm going to list going off for like each one of these is going to have a different number inside of it but i want these numeric values to automatically be currency amounts this is one of the ways in which you can format a cell so unlike how these are just loose numbers the way they are six four nine zero i'm actually going to make it so that these cells right here from e24 down to e28 these five cells whenever you enter in a numeric value it will automatically convert it to a currency so i'm going to show you the way you can do that is using these number tools and there's plenty of them not just currency there's all kinds of them so if i go to the top here and i select the drop down you'll find that the currency options are right here immediately available there's plenty more than what you're seeing here too if i gonna hit more accounting formats then you'll see that there's all kinds of symbols for all different kinds of currency you can decide how many decimal places as well if you see here there's this is the the larger section of categories of different ways that you can format the cell for different purposes various kinds of number formatting um as well as even if it's like something like for instance if you want to set a custom one you can actually even create custom formats if you wanted to but we have even things like time date as well so you can set that date and time and configure whichever one that you like specifically if you want to select whichever particular date and time you want you can go ahead and select that but like i said i'm using currency so i'm going to go ahead and select currency um and i think i'll use the you know the the dollars and cents so we're going to go ahead and just use the option right here hit ok and let's go ahead and get down to it all all of these different numbers here or all these different cells are currently using that numeric function or that numeric formatting so if i go and i hit 10 it will automatically be 10 same thing with if i hit five five dollars how about three fifty three dollars and fifty cents automatically because i formatted those cells that way so they will automatically be those kind of uh those kind of numeric values now if i go and i just type uh chocolate instead it's not going to have that but if i type in a number then we have the uh we have currency so when you so whatever you set right here for whichever of the cells that you currently have selected if i click and drag these cells uh and and you set a certain kind of formatting for numbers inside of that cell and that's what's going to happen you'll also find something very interesting that happens by default and you don't have to have it this way but it is something that happens by default and it's usually because of the way numerics often are entered in certain situations as these and that is that alphabetic values something like for instance maybe if i want to type um let's go ahead and type um poundcake they automatically um alphabetical values automatically align left whereas numeric values align right so what's five dollars let's go ahead and say five dollar watermelon and this one actually i had edited this one if you remember this is the one that i edited the values for so that one um that one had aligned in the center but yes as if i wanted to i can go ahead and create uh different kinds of um different kinds of values and this is a very easy way to create something like a very quick table um maybe you just want to kind of indicate some information here this is exactly the way that you do so now there's other kinds of formatting you can do as well if you were to go into the section um if i was go into the section specifically about formatting if i go in here and select format then there's also different things you can do you can change your cell size visibility you can organize your sheets but then there's also cell styles and cell styles allow you to change uh that visual style of that particular cell you can decide to manually change the font and the the color of the cell at the same time so you can create your own cell styles down here as well so if you wanted to make the pound cake entry pink and white then you can do that just like so now select it again and set it back to normal so you can do this on an individual basis and then you also have the capability of using something called conditional formatting now this is usually something that we show inside of like level in level 200 but i love it so much i'm just going to show you very quickly right now if you were to for instance take a look at these numeric values in this table that i created um and you see that these numeric values uh are all all over the place there's even a zero in here there's two zeros there's no lemons and there's no tomatoes i don't like tomatoes so that's why there's zero but if you see here there's a whole range of values here what if i wanted to indicate very very easily uh for somebody who wants who then looks at this table what if i wanted to automatically indicate the number um in a very easy way to communicate this information perhaps there's something that we can use called conditional formatting something where format is automatically applied based on a certain condition in this particular range of cells now i've selected this this range of cells right here and if i go and i select conditional formatting and i scroll down here you'll find one of my favorite ways to really show is color scales so if i was to select color scales here you'll see that there's a couple of different options now this is the one i like the most uh having a color scale based on value so the lower the number is is it shows up as as red the higher the number is it shows up as green you can customize these two maybe you want uh maybe you want to have a a setting for yourself you want to customize that color go ahead and click more rules here and you'll be good to go but personally i think i'm happy with just you know the more fruit we have the better so i'll make it so that green is more fruit and red is deficient fruit so we don't have any lemons for instance so these are different ways in which you can format a cell uh and and even use conditional formatting based on whatever information is already in that cell select the entire thing and then you can create formatting based on this on an entire range in relation to each other questions so far how are we doing amanda hey neil we had a couple of great questions some of it i think you'll probably be covering in a little while one was a question for sorting the fruit for example when you have the fruit right now you've got it sorted in alpha order just reminders of how to filter and sort without including that title i think some people were struggling um trying to do some of their spreadsheets and it was sorting the title as well oh i see okay so um now this this is getting into tables usually is level 200 but i can i absolutely can explain it all right so take a look at what we have here so what i have here is a formatted table that's what this is it's actually a formatted table i'm gonna actually undo the conditional formatting i've done and i'm actually going to actually convert this back to the way it was before i turned it into a table just so i can show you what it actually is so i'll convert it back to a range and a range is just a range is just loose data it has no relation to each other none of this as far as excel is concerned right now after i've converted this back to a range it's back to being just as significant as this information it's not actually related to each other it's just it's just information inside of cells that's it and also let me go ahead and i'll even make the cells look back like as if they were as if they never had any sort of table creation now i entered in this information like this okay um with the with the um the headers that i wanted right here uh fruit indicating what kind of fruit an amount indicating the amount of fruit that i have okay now if i've done this and i wanted to create headers what you want to do is when you go to create a table which which you create a table by and this is my way of doing it i select the range that i want to be a table and then i go up to here and i say format as table and then from here then i will select the style i want that table to be in i like this one let's go with the green this time now the important thing before i select uh the table itself before i i select okay it already has the range because i selected it beforehand so it already knows that it's from d6 to e19 right here so but the important thing is if your table has headers then you select my table has headers and make sure that you have them included but make sure you select my table has headers hit ok and then it knows that this is the header it's not part of the it's not part of the data then from there you're able to do things like sorting a to z sort z to a sort smallest largest so this is the way that you're able to adjust that and this is how you can make sure that your headers are not part of the data hope that it's fantastic and that was perfect and in doing that you just answered one of the other questions and then we've had a few coming in the chat i'm just answering those directly so that was that was wonderful i am a little concerned that there's so many mangoes as somebody who is personally allergic to mango neal what are you trying to do to me see i'm not allergic to mangoes you can eat all that see i'll i'll order you some lemons and tomatoes how about that okay good that'll balance it out no this is great thank you excellent i'll eat all the mangoes all right so the next thing i'm actually going to do in terms of this formatting part is i'm going to show you how i made that table or not that table i'm going to show you how i made that chart that i just deleted now you'll know you'll know i'm about to show you something because i'm going to delete it first and then i'll show you how to do it so i have this table full of data right here and from this table i'm going to go ahead and create a new chart so what i'm going to do is hit insert and from insert we have a whole bunch of charts here okay now the way i like to very easily do this is by selecting the data first once again excel smart like that if you select the data that you that you're going to use first and then you go to create a chart it's going to figure out what you're trying to do so for instance i just want to see how much fruit i have by type in a bar chart so i'll go ahead and select all these and then i'll see my different options how about these or bar charts i'll go ahead and hit the drop down right here and i have plenty of them so i have and as i as i hover over them you'll start seeing some examples that's exactly what it's showing me right now so i got all my examples showing up do i want something 3d do i want bars do i want columns there's all different options i think i'll just go ahead and select this one right here and if you notice it's already taking the the information from the chart or from the table and creating a chart from it it's also indicating what information is being used where if you select it then you'll know that it's taking the amount from here it's taking the uh the um the amount of fruit of each kind from right here the actual amount it's taking the title from here and it's taking the um the types from here now all you have to do if you want to edit any of this is just go ahead and select it i can edit it manually if i wanted to um it may be taking its info from here but if i go in here and change lemons let's oh wait not lemons um if i go in here and i take a look at the different labels the numbers i can really go in here and edit everything if i wanted to and then also i have the i have the capability and this is one of my favorite things to do i have the capability of going in here and selecting chart styles so maybe this maybe i want a bar chart but this isn't exactly what i want that's okay i'll go ahead and click chart styles here and i can look through a whole bunch more different styles of charts maybe i like this one actually i do like that one uh maybe i like certain colors so i can maybe change the theme the color theme of my chart just like this so even after you've started creating that chart you can go in there and keep editing that style of that chart just like this so here we go this is this is my chart actually maybe i want yeah i think i'm good with that all right so i have i have my my chart right here i got my fruit right here it's indicating there maybe you want this chart maybe you don't want this table alongside the chart that's okay because with a workbook there's multiple sheets we've only been working on one but we actually have the capability of working on multiple and they're all part of the same file so this excel document currently has three sheets i like to have maybe my data that i'm using in this chart maybe on a separate sheet so what i'll do is i will go ahead and take this chart i will cut the chart ctrl x and i will control v paste that chart and it's still taking that data directly from uh the first sheet so the sheets can still refer to each other i want you to point i want you to make sure that you know that as well that the sheets can refer to each other so even if um even if the uh the the chart is on a separate sheet then the data that it's referring to it's still it insists on itself so it knows that that data is over here all right so let's go ahead and continue on from here all right so um here's our so like i said here's our chart it's still pulling from over here and the next thing i want to talk about is another thing that i actually have already set up inside of this particular document that i'm going to show you how i did now you see these particular you see these fruit and you see the amounts of fruit however if you've ever worked at a grocery store you know that you could potentially either have fruit or not have fruit you could potentially have a tomato or not have a tomato however you can't have a negative tomato and this is what i have set up see what i wanted to do especially considering the fact that when you're working with office documents you can share them with others so that you can work on the same document or they can view it but in the case where you want to to have them work on that document alongside you you want to make sure that they are providing the right kind of information to that document so in this particular case what i have done is i have set up data validation for this cell what is data validation that is where you can set up rules for what kind of information can be in that cell so i'm going to show you how i did this and if you notice it's telling me this value does not match the data validation restrictions defined for this cell you can only try it it's not going to work you can retry it but it's not it's it's uh it's looking only for positive whole numbers and the reason why is because one we don't sell half fruit except for i guess watermelon um but we also can't have negative fruit there's no there you can't have uh a deficit of fruit where that that would be fruit that is now taking up negative space so um so here we are with our data and i'm going to show you how to create validation for that data so i'll select all this data right here and i'm going to show you exactly how i decided or how i was able to set that rule that we can only use those particular kinds of information you go to data up here so the data section and then from there you want to go to and it's a small little icon in the bottom right here right here or on the right on the right over in this area it's called data validation and this actually brings up another particular point there's there's tools all over excel um we know it you know it we all know that there's tools everywhere in excel if you ever are looking for a specific tool you can actually use the search to find those tools so say for for instance that i might have forgotten where data validation is i can just type it in right here there it is there's data validation if i want to go and find my logic functions then i can go ahead and type in logic and i'll find my logical functions complete with the drop down menu of what they are but i'll go ahead and jump in here one more time i know that we don't have as much time as we would want and i want to make sure that we also at least get the um the functions and formulas in there too so let's go ahead and do this data validation real quick so you click on data validation after selecting the cells that you want to have that validation and this is what it looks like this is the criteria that i had selected so i selected these cells and i said allow and then there's a whole bunch of options for what we allow right uh and you can say allow any value only whole numbers decimals list date time text length custom then you can select to ignore this rule if it's blank then you can say that the data has to be greater than or equal to or as you see a whole bunch of different other options i had greater than or equal to because i want it to be at least zero it cannot be a negative number so it has to be a whole negative number we do not sell fractions of an apple so because of that we want to make sure it's a whole number so uh with that then i go ahead and hit okay if i'm satisfied also you have the option to customize that message that shows up so when a cell is selected show this input message so if there is so maybe like enter a whole positive number and then you could say error alert and then if you notice there's a whole bunch of if there's any kind of um invalid data you can decide that there's that there's that that that error alert that you saw earlier and you can actually customize it so you can decide to say i told you to use a positive whole i'm actually gonna capitalize positive whole number exclamation point and i'll hit okay so now when i go in here and i try to not follow the rules and type in negative seven for lemons i told you to use a positive whole number see there we go this is how you can set data validation and the very last thing that we're going to do uh before we before we close out because i know that we're almost out of time here very last thing that we're going to do is we're going to talk about how to utilize functions and formulas now i'm going to show you something very quickly you see these numbers down here total fruit and average fruit well i didn't type these numbers in i didn't go over here and look at all these numbers and then tally them up you know count my fingers or pull up the you know pull up the microsoft calculator or write it down somewhere and figure out the the sum instead i used a function now what function did i use i use the sum function and if you see it may say 113 here but if you look up here this is what actually is on that cell this is what excel sees and then based on this formula that i've created it gives me this answer on that cell so uh i'm going to show you exactly how i did this first by deleting it so we're going to delete it and i'll do it from scratch now what i want to do here is i want to find the sum of all the fruit that is in this table so what i'm going to do is i'm going to select that whole section yes including the amount right here and you'll see why i'm doing that or when i or when i do this i'm going to i'm going to even include the amount now if i go in here and hit if i select this cell which is where i want this particular uh sum to be i will hit equals okay so i'm hitting equals equals whenever you use equals first inside of a cell it knows it's looking for a formula so as part of this now we're going to go to the formula section and you'll find one of the easiest ways is to select autosum here and hit sum this is one of those very easy mathematical equations hit hit autosum and then oh sorry one second so we'll go ahead and select this we're going to hit sum in this section here and then from there then it asks us what are we finding the sum of so what i'm going to do is i'm going to select all of this including amount and the reason why i'm selecting including amount is so that it understands not just these cells inside of the table but this section of the table this particular uh column of the table so even if the table and say for instance we decide that we want to add on i don't know uh figs to this table then it will also include that as part of this uh total amount so if you see when i selected this as what i want to find the sum of it says table 1 all amount and i can keep actually adding other values i can select other values from even other areas on this particular on this particular sheet i can go to other sheets and find even even more values if i wanted to and combine them together to find the sum and when i hit okay oh there's one issue there's a problem with the formula all right and if there's an issue there then we go ahead and we fix it so let's go ahead and select one more time and we will edit it one more time equals the sum so i'll go ahead and equals sum select entire area table one amount part of me yes you are not supposed to select amount i'm sorry about that so table one amount it knows that this area even if i add more entries to this table we're back to 113 that's the total if i change any of these numbers lemons i'm adding five lemons it's now 118. the average is using the average cell reference to also give us an average of all these fruit so that's why this number changed too or it's using the average function the same way that i just used the sum function you can use the average function and find the average but i know that we did just hit 3 o'clock are there any final questions that we have before we have to close out unfortunately we're all out of time but are there any final questions that we can answer the only thing i would just kind of draw somebody's attention to is somebody asked if there was a spell check in excel which i know excel is a little bit different than some of our other platforms but if you go under the review tab you can see there is the option for checking your spelling it doesn't appear like it does in word and other platforms you have to kind of navigate to it since this is primarily data based the other questions are very personalized i think that's more of a me to follow up one-on-one with some of their specific spreadsheets sure um questions but neil this was incredibly helpful and we have a large request for the next level of excel so that they can put into practice some of the things that you were able to demo today so thank you so much for being willing to to train us at ut dallas and share some of these techniques so that we can enhance our excel usage absolutely absolutely thank you ut dallas um and i definitely will let the team know that you are interested in learning even more about excel going forward once again i have been neil down here in midtown dallas so not too far away from y'all but y'all enjoy yourself enjoy the rest of the week as we continue on um and i hope to see you again on the next one all right thank you all for attending

good afternoon and happy holidays everyone we are so excited to be finalizing the team's tip sessions for this year this is our final one before we go on break for the holidays and this was requested by popular demand after our previous session hosted by microsoft for the basics of excel today's session is going to be building on the skills that we learned in that session and enhancing those so feel free to have a spreadsheet up you can follow along our presenter is going to go step by step through this very specific areas so that you can practice a little if that helps you along the way before we get started i want to go ahead and go over a couple of things just to make sure we do our housekeeping for teams live events if you haven't been in one before at any time you are able to pause you're also able to rewind so if you just hover over the screen you can jump around and if you need to see what the presenter does you can go back and watch it again to be sure that you have your your formatting of your cells and everything correctly so you can replicate the process also we are posting in our chat so if you navigate to the right side of the screen you'll see our chat there if you can give the thumbs up icon to the post that we posted in there that just ensures us that you're able to see our comments and you're able to follow along with what we're presenting on today so if you haven't already navigate over there in the chat there's a little bonus a portion for you in today's chat in that we selected three random prize winners who went ahead and registered early for today's session so their name names were drawn and we posted the prize winners there so make sure you check it out because you don't want to miss the opportunity to win some really fun swag i'm going to go ahead with our slides i'm amanda pritchard i'm in the office of information technology if you have not attended these sessions before we offer them routinely sometimes our team hosts them sometimes we bring in someone from campus and today we're especially grateful for microsoft coming and being able to present on this topic to our campus if you don't already follow us on social media i put our two top channels there linkedin and then instagram if you want to follow us you can scan those little qr codes down at the bottom there and then you'll be able to hear about any future trainings or upcoming events and then the number one question that we always receive is if this is going to be recorded how do you get it after the fact and we understand we want you to be able to go back so that you can excel at everything that you're doing and have the information that you need so if you need to watch it again if you want to look up some of our past videos and presentations they're all available on youtube so you can navigate there we're also streaming this live to youtube which is a new feature that we've just uh recently be doing so if you want to catch it on the live stream or you can catch it in teams live we've got you either way you can use both of those tools and then just a quick heads up on some of our spring trainings we've already started planning for the new year we've already gotten feedback from various departments and groups on some things that they'd like if you haven't heard we just recently migrated our all-campus ticketing system it's a service management tool and so we're working out all of the the testing and the final product right now and in january we're going to be ready to share that with campus to let you know a little bit more about how you can go and get help if you need any technical help and then what resources are already there if you want some self-service to to have access to different materials software or graphing issues with hardware all that's going to be covered in that training we're also going to kick off new features in teams and then information on duo so i'm going to stop sharing my screen for now and i'm really excited to bring to you all a a new presenter new to us new to us um holly is here and she is in the holiday spirit holly is here um to talk to us a little bit about advanced excel and she's going to walk through like i said step by step with all of us and we'll be able to learn from her if you do have questions the chat is moderated so just be aware that there's a little bit of a delay as we're getting back to answering those questions you may or may not see your particular question published but just know that we do see it and we will respond to you as quickly as possible so without further ado welcome holly thank you so much for leading today's session thank you of course i am very excited to be your trainer for today so we'll go ahead and kick it off so once again another hello my name is holly and i will be your presenter for today and as we've already discussed we'll be going over excel level 200 so if you do have any comments questions or concerns please don't be afraid to let us know i'll be here to answer anything you might have at the end if you need me to read demo something or explain it differently i can certainly go ahead and do that for you so we'll kick this off by diving into another powerpoint that i have here so we can get a little bit of an introduction into today's advanced functions and formulas in excel so our first portion is actually going to cover some of our live event controls if you are joining us from the teams live event you'll have some controls available to you at the bottom of your screen so you can play or pause you can adjust your volume you can turn on closed captioning and subtitles or if you click on that cog icon there you can also alter the look and feel of your captions and subtitles as well as your playback speed you'll also have many of those options on the youtube player i believe as well so you have a couple of different choices there but you also have that q a on the right side of your screen if you don't see it there go ahead and click on that little speech bubble with the question mark in it in the top right hand corner that way you can ask us anything that you need to and we'll go ahead and move into the agenda today's class will cover some of the more intermediate and advanced features of excel you'll want to already have a strong understanding of the basic features and navigation of excel and have used basic functions and formulas before attending this session today we'll cover tables including pivot tables we will apply intermediate and advanced functions i will demonstrate how to review your data for errors and manage accessibility you will learn to share and collaborate and also manage access and restrictions to your work excel pivot tables are a powerful tool that allows you to review analyze summarize and calculate data in various ways you can track trends patterns compare information and easily use data to make decisions or create presentations with excel you can turn a data set into a formatted masterpiece using logic functions and lookup features will provide you with a smooth analysis of your information to share a workbook you can click the share button in the top right hand corner if you have data that you don't want altered that is not a problem you can go ahead and set permissions before you send someone your workbook office 365 makes it simple to choose who you want to share your workbook with and set their permissions without having to leave the document and with that we'll go ahead and start to move into the live demonstration here so give me one moment to go ahead and re-share my screen with you and we'll start to take a look at that let me make sure we have everything sharing properly here and then we will go ahead and continue perfect thank you all right so our first topic today is going to be over tables so here i already have a preset table that i have created and we'll get to do a couple of different things with this table when creating a table in excel first we have a normal range of data turning it into an actual table set is going to give you a list of different available functions and settings that you can pick from there's two different ways to make a table the first one is to click into your data and choose control t on your keyboard which is going to produce this small window for you here excel is a pretty smart program here and can assume where our data starts and stops but if it does get something wrong you can always choose the upwards arrow here and change the highlight to select your data and then press the down arrow again make sure you have this box checked if your table has headers like mine and then you can go ahead and choose ok for now we'll cancel and i'll show you the second way we can move into our insert tab here and from here we can also choose table which will go ahead and give us these same options so we'll go ahead and choose ok and notice that our data changes ever so slightly now mine already had the coloring format but if yours didn't it would go ahead and populate that for you we can also tell by the drop down boxes that appear next to our headers when we have something selected inside of excel it's possible for us to get something called a contextual tab this tab will only pop up in the context of whatever we're currently working on since i'm working in a table we'll go ahead and get the table design right here one of the first pieces in the table design is the table styles if you do have a certain color scheme or theme that you're going for you can go ahead and change the colors that you're using here or you can even drop down the table and choose something different mine is going to stay green because it was already green before we also have our table style options this is going to allow you to include new pieces such as headers totals different columns and more the biggest one is going to be the addition of a total row if you want excel to go ahead and calculate a total for you it can do that very quickly without you having to enter in any additional functions or formulas let's also say that's not the data i was looking for i didn't want a total but rather maybe an average if that's the case you can click on the output that excel gives you here and it will produce a small downwards pointing arrow right next to it and if we click on that i can change it from sum to average and get a different type of total we can also move over to properties here and we'll see that we have the ability to name our table this is really going to come in handy when we start looking at those advanced functions later on there are many different functions where you have to select a range of data or you have to indicate some kind of range of data instead of typing in the cell names every time or having to highlight every time you can type in the name of a table instead to save you some time so i do suggest naming your tables lastly in the tools area you can also remove duplicates if you have extras in your table or you can convert it back to a range when you convert something to a range you lose all of the table extras that we got when we indicated it as a table so if you more so want the design of a table but not all the extra buttons that come with it you can choose to convert it back to a range and our data will look just like this so we'll go ahead and move on from creating the table and we'll talk about conditional formatting of our table i'll go ahead and highlight this area here and we'll stick within the home tab under styles we'll get access to something called conditional formatting which is going to allow us to add more visuals to our data so we can pull useful insights now i'm going to add a couple of different things here and it may look a little bit chaotic at first but we'll use that to our advantage and it'll make sense in just a second so first we can use the highlight cell rules option so maybe i want to highlight cells that are greater than a certain number maybe i have a sales goal we could type in that number here and say that i want excel to fill it in with green if we meet that goal we'll choose ok and see those highlights we'll continue we can do the same with top bottom rules if i want the top percent or my top 10 items we can add data bars these will put physical bars inside of our cells so we can quickly see which cells have the larger and smaller numbers you'll get something very similar with color scales except it will highlight every cell and will give you a different shade of color depending on the number compared to other numbers and the last is icon sets you can include an icon or multiple different icons that do something very similar now when we add all of these you might be looking at this and saying this is very colorful and very hard to read let's go ahead and clear some things so we can make this make a little bit more sense the first piece we need to take into consideration is that our headers total and left hand column do not need to use these conditional formatting items so we can highlight those and when i'm clicking other cells we use the control key we can move back up to conditional formatting and we can move to clear rules i can either clear rules from the entire sheet or from my selected cells when i clear just from the cells i selected we can already see that my data is much more accurate but it's still kind of hard to read so let's remove a couple of more things but this time i'd really like to not remove everything i'd still like to keep a couple of pieces so instead of clear rules we'll choose manage rules and i can see the four rules that i've applied we'll keep the data bar and the icon sets we'll choose the ones we don't want and delete the rules and then choose ok and now our table is much easier to read and we have some additional insights that could give us some quicker information the next type of table we'll go ahead and move into is a pivot table now pivot tables are typically for larger amounts of data but you can really use them with any kind of data set that you would like so we have something a little bit bigger here and we'll start this off just the same way that we started off a normal table we'll go to the insert tab and still under tables we'll have two primary areas that we can choose from the first is recommended pivot tables this will help give us some insight into what a pivot table really is just like its name says the table pivots or changes depending on what you're looking for so you can indicate the exact information you're trying to pull from your table so instead of you having to create something entirely new from hand you can have excel help you out so if i wanted to see the sum of the price by the make we can make a table for that we could do the count of the mileage by the make some of the mileage by the color or anything else i might be looking for but it's easy to say that we look through the recommended pivot tables and i don't find anything that is what i'm looking for that solves my problem so let's go ahead and create one of our own instead and we'll choose pivot table from here once again we need to make sure that the indicated range is correct so take a look at your dotted highlight or the numbers indicated here and make sure that everything lines up then we can choose where we want this pivot table to go do i want it to go into a new worksheet or do i want to put it into one of my existing worksheets for now to keep it simple we'll go ahead and create a new one and we'll choose ok that's going to pop up this area for us here and we get plenty of new tools to go ahead and work with here on the right side is going to be the primary place we'll be working in here i have all of the categories that i had in my original table i can use this data to create something new these categories can be placed into these four different areas down here and we'll start to build a couple of things we'll throw a few pieces in here and see what it'll go ahead and create for us if you would like you can also choose the check box next to the left of your category there are default places that excel will go ahead and put certain things for you so if you needed to help decide where things go you can choose the check box let's go ahead and put our color in the columns and we'll put our models in the rows let's say i'm trying to find on average how much certain colors of my models cost if that's the case we'll want to put cost into the values that's going to fill out the middle piece of my data here so we can already start to see something coming together and i'm pulling in different insights from the data that i had before but already looking at this we can tell that something might not be quite right if we look into values we see that it says the sum of the cost well i wasn't looking for the sum in this case i want the average so we can click on that drop down and choose value field settings from here i can pick exactly what i'm looking for and we'll choose average okay and the data changes now i can see something a little bit more accurate we also have the filters choice so if i want to cut this down even more we can add a filter maybe i want to filter by the make we'll go ahead and drag the make in there we'll look at the drop down and we see that we have a few makes that we can choose from if i want to select multiple items we can choose this checkbox here maybe i'm really only interested at the moment and looking into three different makes we'll choose ok and that will limit my data for me so there are many different ways that our pivot table can change with the information that we are looking for just like our regular table from before we also get a couple of contextual tabs and the first one is the pivot table analyze under pivot table you'll once again see the option to name the table very similar used to normal tables and i always suggest naming your objects the active field is what we have selected here so we see the average cost which we were just changing and another place that i can see that value field settings in filter we have a couple of additional ways that i can filter my data instead of just using the filter area i can also include a slicer we'll simply choose the make one just because we kind of already know what that one looks like and we'll choose ok and that creates a slicer that looks like this slicers also get their own contextual tab and you can give them a name and a color and you can change their size we see the three that we had selected before and if we want to multi-select we can choose this button here add some more or we can clear them by choosing the clear button i'll delete the slicer for now we also have a data area here which is a little bit more advanced so you do want to be careful with this one if you are familiar with hooking up excel to different applications such as a sql server or an access database you can do those things from here and start to pull in that automatic data we also have some additional tools if you want to look at your recommended ones again you can press that here or we can even add a pivot chart so if we want to get even more visual we can add a chart and we have plenty of different choices here on the left of different charts that we can add we'll go with the clustered column and the default style and choose ok that will enter in the info for me here and i can change the color the average of the cost the make the model and more last piece for our pivot table is that this also does come with a design tab so once again if you have a theme or a color that you need you can pick that from here we have more headers and column headers row and column and you can also change your layout so if you want it to appear in a certain way you can alter all of that from here including subtotals and that is going to be our brief overview on pivot tables once again just as a quick reminder if you do have any questions on these subjects don't be afraid to let us know if you're seeing this for the first time and you need to see something again we'll have some time for that at the end or the recording will also be available to you so let's go ahead and move into my personal favorite part which is our advanced functions and we'll start with a few lookup functions there's also a couple of different ways that we can find the functions that are available inside of excel the first one is going to be next to your formula bar you have an fx button right here this allows you to insert a function so i can type a description of what i'm looking for or i could even search by category so we have tons of different options here or you can also choose to move into the formulas tab and from here you have access to your function library you can also search by category from here but for today's demonstration i will go ahead and type these in by hand manually so that that way you can see what the process looks like every function is going to follow the same pattern which is going to be equals whatever function you want to use in all caps an opening parenthesis your required arguments and a closing parenthesis right now that might sound like a lot but don't worry we'll be typing that a fair bit through today's presentation and you'll get used to it very quickly so we'll start with vlookup this allows me to look up information in a vertical sense so in this situation i have set up i'm trying to find out how much money these three people owe me so we'll type in our function we'll say equals v lookup opening parenthesis now the great thing going back a little bit is that excel does try to help you so if you vaguely remember what something starts with or how it begins you can start to type that in here and excel will prompt you so we'll say lookup opening parentheses and it will do the same with its arguments what it requires it will tell you what it's looking for so the first piece we have bolded here is the lookup value what am i looking for now there's a couple of different ways i can insert this information the first is to simply type it we could write down norman's name this is considered a constant because it doesn't change which technically is probably not the best choice for us right now if i want to autofill something which i'll show you in a bit you do need to use cell references instead of constants a cell reference is when you indicate the name of a cell and you essentially are telling excel i want you to do this problem with whatever's inside of that cell so we can simply click on norman cell and it will know to do it with norman's name so in there it puts cell f2 to move on to your next argument you'll type a comma and it will highlight the next section for you now it needs the table array which table am i trying to look up this information in this goes back to what we discussed before if i had created a table and named it i could type in the table's name here same with the pivot table but for now we're using a fairly small table so we can just highlight it instead and i'll click and drag to highlight this information and that is considered a range so we see the c2 colon d6 which essentially translates to c2 through d6 we'll type our column or a comma again and the next one is the column index number the reason it asks this one is because we highlighted multiple different columns i need to tell excel what information it i wanted to give back to me if we said column one it wouldn't be very useful because i already know norman's name and column one only consists of names but if i put column two that would be useful because it would then tell me how much money the woman owes me so we'll put the number two the last argument is technically optional and we can tell excel what kind of match we're wanting it to give us you can give it true for an approximate match so just something that's close enough or false for an exact match in this case i do want an exact answer so we'll go ahead and type in false we'll close the parentheses press enter and it comes back with 70. if we double check it norman does owe me 70. now i'll show you how to fill this information which is another reason why we need the cell reference if i hover in the bottom right hand corner i'm going to get this for four pronged black plus sign here so if we click and hold on that and drag it down it will go ahead and autofill in that information for me essentially every time i told excel to look to the cell to its left and give me the answer we can confirm that emma owes me forty dollars and rey owes me 25. we'll continue the next one is hlookup very similar to vlookup but instead in the horizontal sense so we'll double click in here input equals hlookup lookup value we still want to find out norman's information comma will highlight the table array now if you are highlighting you do want to be careful on how you highlight if i highlight from chi down to 25 the names are going to be row one and the money is going to be row two but if i highlight from a hundred dollars to ray the money will be row one and the names will be row two so we'll go from name to number and then that becomes useful when we hit the row index or the column index if you're using vlookup and we know that we want the money so we're still looking for row 2 and we still want an exact match we'll close that press enter and norman still comes back as 70. if we drag that over we get 40 and 25. next we'll move on to index this is going to allow me to know the index number or where exactly something is in a set of data so let's say i want to know what's in row 3 column 2. we could enter in that information with index we'll say equals index opening parenthesis what array am i looking in it's still this one over here which row do we want we want technically row 2 in this case i said row 3 because it's in row 3 but we didn't highlight row one so we do need to take in consideration that it's the row in which we have highlighted that array in put column two close that press enter and it comes back as 250 because once again we're looking just in this area not in the area of the whole worksheet so row 2 column 2 250. next is match match is almost the opposite of index so instead of me knowing which column in row something is in i know what i'm looking for and i want excel to tell me where it is so we'll type in equals match opening parenthesis our lookup value is chi and the lookup array in this case it did that a little funny hold on there i put a period by accident our lookup array is going to be just this not the money in this case match is only going to bring back the row number for you not the column number so we need to indicate one column only especially because in my lookup value i didn't indicate two values i didn't put a name and money i just put a name so we'll do that and then for our last optional choice we also once again get to choose what kind of match i'm looking for but instead of a true false choice here i get three different choices this is because you could be matching with a number instead of a name like i am so i could be looking for something that's less than a number the exact match or greater than a number to get the exact match we'll choose zero close that press enter and it comes back with one chi is in row 1. the last one for lookup values is going to be the today function which lucky for us doesn't require any arguments it just does it on its own so put equals today open close press enter and it comes back with today's date but it's very possible that you type this in and you don't get back the nice formatting that i did if that's the case i'll show you how to fix it we'll go ahead and click on the cell that doesn't quite look right and then we'll right click on it we'll go down not that one there hit the wrong one by accident instead we'll go down and choose format cells just to show you that one more time format cells right here and we'll get this box which allows me to choose different cell types most of the time you're working with general which allows you to put almost anything into a cell you can also limit it to just a number you might want to indicate it as currency that way if you need a particular symbol on the end of something like the dollar sign you can have that automatically there's accounting standards same with date and time you can indicate it as a percentage fraction scientific notation limit it to only text and then there's also special for things like zip codes or social security numbers and custom if you need to make one of your own but for now we'll stick to date and that format and choose ok the next part allows us to do math with today's date so we'll say equals today open close minus this date sure and we technically get the right answer but i'm more so looking for a number than a date so we'll do that again we'll right-click format cells general okay and we get how many days ago that date was now we'll go ahead and move on from lookup into conditional and the first one we'll work with is if if is only going to do something if a certain criteria is met so we'll say equals if opening parentheses and i need to give excel a logical test what is it testing for i'll tell it to check the cell to its left if that cell is less than 40 then do something that's our test we'll put comma we have a value to print if this comes back as true when i want excel to print something i need to put it in quotation marks we'll say to print the word less if that comes back is true i have one more i can also tell it what to do if it comes back as false if i don't indicate these excel will simply print true or false but in this case let's tell it to print more we'll close that press enter and we get less if we drag that down we'll also get more and then we have ifs which is like a more advanced version of if and allows you to use multiple if statements inside of one so we'll type this in we'll say equals ifs opening parentheses and we still have our logical test except we can do a lot of different ones here each set that i have in here will come in twos so we have the logical test and then what to do if that comes back is true and then we keep repeating that pattern so here's our test if this cell equals the word chi which we'll put in quotations comma not the word boss that's our first set our first pair we'll continue if the cell to your left has the name isaac print out the word friend if that cell says emma print out sister if it says norman if it says rey print out brother please press enter first one comes back is boss but all the others also come back as their indicated places friend sister brother etc the next one is if error what do you want excel to do if it runs into an error so we'll say equals if error opening parenthesis or actually let's just start with doing it normally to see the difference we'll say equals this cell divided by this cell and we'll press enter we'll drag that down and we get two errors trying to divide by a value and dividing by zero now let's do if error put if error and we'll put in the same math that we did before then for our second argument we'll tell it what to do when it bumps into that error i'll show you two different examples the first one you could do is two empty quotation marks if you just want it to be blank so we'll close that press enter drag that down we get five three and two blanks we'll do it one more time we'll say if error this cell divided by this cell print out the word error something not quite as eye-catching doesn't take up as much room we'll drag that down and we get the two cleaner looking errors the last piece for conditional is going to be and and or the funny thing about these two is that they do take the exact same arguments but print out something different so in this first situation let's say in order for a person to get the job they need to pass interview 1 and interview 2. let's go ahead and calculate who passed we'll say equals and and we're going to give excel two logical tests we essentially want to show it what the right answer is supposed to look like so we'll say this cell for it to be deemed as correct or true needs to have the word yes in it same with this cell we'll close that press enter and it comes back as true if we drag it down we get some different answers isaac does not get the job because he didn't do either of them emma doesn't either because she didn't pass interview one but she did pass interview two norman gets the job because he passed both but ray does not because he did not pass interview two so let's change the requirements of our interview let's say i'll give someone the job if they pass interview 1 or interview 2. we'll make it a little bit easier so we'll say equals or opening parentheses we still need to show it what the correct answer is supposed to look like this cell will still only come back as true if it has the word yes in it same with this cell it just needs to be one or the other instead of both this time so we'll close that press enter drag it down i think it's still false because he didn't pass either but now emma comes back as true because she passed interview 2 and so does rey because he passed interview 1 and that is how we use and and or for our last bit for functions i'll show you a different couple of different ways that you can use count we'll touch on a few extra things after that and then move into our q a so we'll start with normal count count is a little bit different from what we've used so far because it only slightly cares what's inside the cell it just counts the cells that have something inside of them so you know how many full cells you have so we'll say equals count opening parentheses and we'll highlight the area that we wanted to count we'll close that press enter and it comes back as 19. we have 19 full cells so let's go ahead and try that again but with a different column count we'll do our letters column and this one will strangely come back as zero that isn't necessarily what we wanted because it does have something inside of the cell this comes back to what i said before that count only slightly cares what's inside of the cell count will only count if it has a number inside of the cell so we'll see that one more time we'll do mix close and that one only comes back with seven but let's say i do want to count the letters that's something that's important if that's the case you'll instead want to use count a so we'll type that one in count a opening parenthesis and we'll go ahead and try numbers one more time that one still comes back as 19. so count a counts both numbers and letters we'll try our letters column again 19 just like we expected we'll do mix 15 much larger than what we got before our two last ones are going to be count blank and count if the opposite of the counts we've done so far count blank will count the blank spaces and i do mean truly blank spaces even if it has a space character inside of it that is still considered something even though we can't see it so we'll say count blank opening parentheses then we'll highlight the mix column that comes back as four we have four blank spaces here then we have countif only count if a certain criteria is met say countif opening parentheses and we'll use the letters range this time now we have a second argument after our comma which is the criteria i only wanted to count if it finds the letter a so we'll put a in quotation marks we'll close that press enter and we have seven a's in our letters column and that is going to be some of the last functions that we are going to cover for today so to make sure we still have plenty of time for questions i'll touch on just a couple of more things and then we'll move into that q a the first piece is going to be in the review tab if you are sending out your excel workbook to other people you want to make sure you check the accessibility first that way your document is as inclusive as possible it'll go ahead and check for things like alternative text screen reader order and color contrast if you do share it out with someone you may want to leave comments that way you have a way of communication with them so maybe i wanted to highlight this cell in particular and make a comment on it we'll choose new comment that will pop up here and i also have the ability to at mention someone from here but i could type in a note and send it with the airplane icon now we could comment on it or we could edit this i could press the ellipsis to delete it or even mark it as resolved meaning it's completed but it still technically exists so if i need to come back and review it later i can do that we'll mark it as resolved then we'll see under protect you can protect your worksheet and your workbook protecting the worksheet does protect certain cells and i'll show you how to indicate those let's say i do want it to be the entire worksheet if that's the case we can press the arrow between our rows and columns and that will highlight everything if i right click anywhere on my cells we can go back to format cells and choose protection from here if i want to make sure these cells are safe we'll have the locked checkbox turned on that way when i actually choose to protect it those will be the ones that lock you can also protect the entire workbook if you would prefer that way people are not adding sheets or deleting sheets or any of that fancy stuff we can also add a password to things which we can do once we save it in our onedrive in our cloud or sharepoint we could choose share from up here and we'll get this share box here we first can choose who the link works for so anyone with the link people in your company you can lock it down to people who currently have access or you can give it to very specific people you can either allow them to edit or not and if they can't you can also block them from downloading it here you can set an expiration date and a password if it's someone in your company you can go ahead and type in their name here or if you needed to send it to someone else you can type in an email address you can also quickly change your options from the drop down here of whether they can edit or view you can add an additional message on as to why you're sending this out and then you can choose to send it or if you prefer you can copy the link now do keep in mind when you share this you are sharing the master copy you are giving these people access to the original document and they can edit it at the same time as you so you can work together and you'll see where they are in the document and what they're currently doing if you don't want them to make changes to the original you can choose to send a copy instead i believe we have one very last piece here and then we can answer any questions we might have you also have the file tab available to you which shows you this backstage view you can print your excel workbook which you do want to be careful on your settings you want to make sure you are printing the exact cells or sheets that you want to use that way you're not printing too little or too much and those are going to be the last pieces that we'll go ahead and cover so we'll start to go ahead and dive into that q a but very quickly just in case you need to leave or anything to that degree i want to go ahead and say thank you all for joining us i do hope i was able to teach you something new and i do hope everyone has a wonderful day so we'll dive into those questions real quick thank you so much holly that presentation was very clear and so helpful to to all of us here we don't have any questions right now in the chat so i'll give it just another minute or so just to make sure that we don't miss anything while that's coming through and i will go ahead and i'm going to share a different screen just for our survey form for anybody that's attending that wants to provide some feedback on today's session and in addition in that survey we're going to have a couple of options to vote on classes for next spring so if you have ideas or suggestions there's a poll in there where you can give us your feedback because we do want to make sure that the content we're planning is what you're needing and desiring and will help you grow in it and excel in your careers so i'm double checking our questions real quick holly i'm not seeing anything i think it was really clear and i appreciate how thorough you went through those sharing options because that's a critical tool in using in all of microsoft products and one of the things that i appreciate so much is that consistency whether it's excel or powerpoint or word whatever we're working in that those share options and the edit options are consistent across the board so whatever it is you're wanting to collaborate on you can use any of those options to get that feedback and to collaborate with your department i'm going to go ahead and put the survey screen up full screen for you all feel free to take a screenshot use it you know that little qr code we're going to also have a link in the chat it's posted there now if you want to click on the link directly rather than scanning and putting that qr code you're welcome to do so if that's easier but go ahead share your feedback thank you all for joining us today we do appreciate you coming to these sessions and just remember if you have any further questions we're here to help you