Streamline Your Workflow with an Invoice Excel Sheet for Teams

Invoice excel sheet for teams

Hi everyone, my name is Kevin. Today I want to  show you how you can create drop-down lists in   Microsoft Excel and as full disclosure before we  jump into this I work at Microsoft as a full-time   employee. My company requires me to say that  anytime I talk about our products. First off,   why would you want to create a drop-down list  in Microsoft Excel? Well, imagine that you set   up a spreadsheet and you're going to have other  people filling it out, perhaps you only want them   to fill out a cell with specific values and the  drop-down list is the perfect way to make sure   that people select from those specific values.  It's actually very easy to set up and I'm going   to show you step by step how to do it so you too  can create a drop-down list. All right. Well,   enough talk. Let's jump on the PC. Here I am on  my PC and I already have Microsoft Excel open.   This version of Excel comes with Office 365. It's  the latest and greatest. However, drop-down lists   have been available as part of Microsoft Excel for  a long time. Even if you have an older version,   you should be able to take advantage of this.  Before I show you how to create drop-down lists, I   want to show one thing that's been added recently  and that's a drop-down tutorial. If you click on   file and then home, you'll see a list of different  tutorials. Not only will it show you how to create   drop-down lists, but you could also learn how to  work with formulas. You get an introduction to   Excel, you can work with pivot tables, charting,  so they're all types of tutorials that are added   as part of Excel that will teach you how to use  it. For drop-down lists, this is an excellent   tutorial. Imagine that I run a grocery store and  we have all different types of food that we sell   within this grocery store. You can find things  like apples beef and bananas. What I want to do is   I want my staff to come in here and indicate what  department these different food items can be found   in. Now I could rely on my department members  to simply type in, well, you know, this is this   is produce or this is meat or this is bakery and  they could go through and type these in. However,   what would probably happen is you have a lot of  people working on a sheet. You'll have people   type in different things. Maybe they'll type it  in differently from others and then we'll have   all different types of values and I want to make  sure that the data that's entered is valid. So,   we list is perfect for doing this. So to create  a list what we're going to do is so I have my   table set up and I want people to come in here and  basically select a value from a list right here in   this cell. So, I'm going to select all these cells  here B2 through B13 and what I'm going to do now   is I want to make sure I click into the data pivot  on top. That's where we're going to be able to add   a drop-down list, and within here, there's an  option under data tools called data validation.   What you can also do is if you just want to get  there quickly up here and search you could simply   type in data validation and that'll also pop it  up. So that's another way to get to this. So,   let's go ahead and we'll click on data validation  and by default, we’ll be on this first tab called   settings. And now what I want to do is under here  it says allow and today anyone could go in there   and type in any value they want. So it's set to  any value. Like I said, I want to make sure that   people choose from a list. So, I'm going to go  down and select list right here. And what I'm   going to do now is it says well what is the source  of the list and so I could just type in items.   I could type in produce and then I'll type in  meats comma and then a space and then maybe I'll   type in bakery. So those are going to be my three  different departments. Of course, I could put more   in here, but I'm going to use that as my list.  So, what I'm going to do now is I'll click on OK   and you'll notice what happened now is whenever I  click into a cell, you'll see there's this little   drop-down arrow. And if I click on there now, you  have to choose from one of those three values. So,   I could say well this is produce and then the beef  is in meats and then let's see if I could find   some bread. That'll be in the bakery. So people  could go through now and rather than typing a   value they could choose from a list. So pretty  simple way to insert a fixed list. Now let's   get a little more advanced and I'm going to show  you how you can create a list that will update.   So what we're going to do now is here's another  here's another table where I have people who've   applied for a job. So I have application status  and I want my basically my interview team to go   in and put what the status of each interviewee is.  So what I'm going to do now is I want to create a   list just like I did on the previous tab, but  I want it based on a list on within one of my   sheets. So, what I'm going to do here is I'm going  to highlight these and now I'm going to go to   insert, So I'm going to go to the insert pivot and  what we're going to do now is I'm going to insert   a table. So I'm going to click on table and it has  headers because status list. That's my header. So   I'll say my table has headers and now I've created  a list here. So just like I did in the previous   example, I'm going to highlight all of these cells  and once again, I'll click on the data pivot. I'll   click on data validation and here I want to create  a list again. So I'm going to go and click on   list and now for source instead of typing in the  different things like say in process or submitted,   rather than doing that, I'm going to say  my source is on the sheet itself. So,   I'm going to click on this little thing and  that'll allow me to select my source. So, I'm   going to select these three because these are the  different Status items that I want to put within   here. So I'm going to select that as my source  and click OK. So now for Dave, if I click on this,   you'll see that I can select my item on a list  and that's coming from this table over here. Now   what I could do is I could also add another item  and perhaps I'll say completed or maybe we'll say   application status is accepted or I could say  rejected and so I can add different items on here,   and as I add those different items to the status  list, if I click on the drop-down, you'll notice   that they're automatically added to this list  right here. Now what I could do is this list   the list of options happens to be on the same  worksheet is where I want people to go through   and select an item from the drop-down list. What I  could also do is this list could be on a different   worksheet. So I could have another worksheet with  all the values for my lists and then the sheet   that people go in to fill out Is separate  from these items so you could do that too.   So that's, those are the basics of how to insert  a list whether you simply type in the items or   whether you're relying on a list within a sheet  for your drop-down list. Those are kind of the   two ways you can do it. I want to show a few extra  things that you could do to really get fancy here   and what we could do is I'm going to go back  to data validation and what you could use you   could also have an input message appear. Now so  this way when someone clicks into this cell you   show some instructions or some type of message.  So, I could say maybe the title is select an   option. Indicate the status of the application in  this cell from the drop-down. Something like that   and I'll go ahead and click on OK. Now what  will happen is as I click into this cell,   you'll see this message appears that says select  an option indicate the status and so I could   provide a little tool tip or a hints to people who  are filling out my sheet what they should do. So,   that's kind of one neat thing that I could do.  And for icing on the cake, I'm going to go back   to the data validation. Next what we're going to  do is we're going to click on this error alert   option. And now what you could do is if someone  enters invalid data, we could show a message So   maybe I'll show a warning that says please select  an option from the drop or this is the title. So   please select an option and then my error message.  Make sure to select an item from the drop-down   list. So I'm going to go ahead and select  that and so now if I come in here and just say   In progress, maybe I'll just say in progress  right now and I click Next I get this error   that says please select an option. Make sure  to select an item from the drop-down list   and then I could say continue and that way  it'll just inform me that this value should   not be entered and instead I should use the  drop-down list that's provided to me right here.   All right, well that's how you work  with drop-down lists in Microsoft Excel,   it's very easy to do. Hopefully you learned  how to do it, and if you have any spreadsheets   that you're setting up where you need drop-down  lists and you were able to do it successfully,   please give this video a thumbs up. If you want  to see more videos like this in the future,   hit that subscribe button. That way you'll get a  notification any time new videos new content come   out. And lastly, if there are any other topics  that you want to see me cover in the future,   leave a comment down below. I read them all and  I'll add them to my list of videos to create in   the future. All right. Well, that's all I had  for you today. I'll see you next time. Bye.