Idiot-Proof Forms in Excel - Part 1 - Formatting

here I'll show you how to make bulletproof forms in Excel that even your coworker can't mess up and I'm gonna show you lots of neat little features so you can make your own version of it so let me show you what we have here this is actually a regular worksheet in Excel a regular spreadsheet you can see down here it's called form and this is just a regular interface tweaked quite a bit so the first cool thing is that here in our user form you can only select the inputs you can't click anywhere out here it's not going to select the cells or let you do anything with them now you can use the arrow keys to navigate between them enter or tab so let's do some sample data here Thanos he's got an ID now let me show you what start date this is where it gets a little interesting let's say you enter the wrong date hit enter please enter a valid date after January 1st 2001 so that's one of the cool things I'll show you how to make custom data validation error messages that tell the user what they did wrong instead of just saying that they did something wrong so I need to fix the input it's got to be after 2001 so let's make it 1-1 2010 here I have a cool drop-down menu where we can select a department and the cool thing about this menu when I show you how to make it is it will automatically update so you can add new entries to the list where the the source data is and it's going to populate this drop down menu without you having to do any extra calculations and the method I show you is super super simple we're not going to deal with any offset or crazy functions or formulas now let's go down to sector say sector has a kind of a confusing format for entry so what I want here is to tell the user what it is you can see the little pop-up comment format a dash number a dash 1 no problem but if I do it wrong let's say there here's an example of the generic error message that doesn't really help you with that validation either way it's not gonna let you input that so you have to do it in the correct format let's now show you a cool little format two and two when you enter a phone number automatically formatted like a phone number in your format this is the u.s. phone number format right here and it's really cool now when we hit submit we a little success message box and this is where the tutorial will end once we get to this point and you will have to take this and then put the data into your own worksheet and I showed you how to do that in other tutorials and I'll show you how to do that in more tutorials in the future as well but here we're gonna focus on this lovely little interface and how to make your own now let me show you when they put an error so let's say that they didn't input anything for Department and they didn't imput anything for ID well I don't necessarily want a red message or an error box to pop up here by default because maybe the user is not finished entering data and I hate it when I'm filling out a form and it gives me error messages when I'm not done filling it out so when we click Submit it'll first tell me how many errors there are two errors and it'll highlight the errors so the errors are only highlighted after we click the submit button then when we enter the proper stuff the errors disappear so basically we have taken a boring spreadsheet and turned it into a rather useful and helpful form that's gonna make it so that the data that's input into Excel is much cleaner and it's going to vastly reduce input errors with data now I'm gonna break this tutorial up into three sections the first one we're gonna create this lovely little interface I'm going to show you the tricks of how to get it to look like this the next one will cover how to do the data validation and custom conditional formatting it's a bit interesting how I do that to make this special interface and then after that we'll cover the macro and how to protect the worksheet so that your co-workers can't mess it up before we start check the video description and click the link to teach Excel so you can download the files for the tutorial and follow along and make sure to subscribe and accept notifications so you can see all the new tutorials alright so will you believe it this is what we start with now let's transform this into a really cool form the first thing that we're gonna do is create the outline of the form and I'm going to assume that the spreadsheet that has this form will have nothing else on it that will allow us to make a much nicer looking form so the first thing you always want to do is have one empty column at least and one empty row at least before anything you're going to use for your form then after that you want to make two little tiny columns so select two columns like this smush them down it doesn't have to be exact it doesn't matter no one's gonna notice the actual size when you're done with this then take two rows up here and smush those down a little bit usually you don't need to do it too much there and now these are going to serve as the walls for our form so everything is going to be contained within that so we can think of this right here is our first column and then what you always want to have this is where our labels will go you always want to have a spacer column because that allows you to increase and decrease the space between where you input the data and where you add a label for the data so here this will be input or sorry this will be label and this will be input and you want your space or column right in between then at the end you can have one extra space or column if you want and you can have two or you need two little wall columns right here so basically to make things look pretty in Excel you need to have these spacer columns set up let's go ahead and delete these because I just put them there for you now think about the data that you want for the user I'm gonna go with name and also in between each item here so a name next one will be ID you want to have a space or row so you may notice that the theme here is spacers spacers because without them you have to shift all of your data just to get something to look decent so name ID then start state department sector and phone now we make this a little bigger I'm gonna go ahead and make these guys bold right now I like to keep form formatting relatively simple the most important thing is actually the spacing in the background colors now let's go ahead and create a little button you want to create everything here that's going to go inside your box inside your form before you do the formatting that makes it look like a box it's a lot more difficult to change the dimensions of the box and is just to create it correctly the first time so we go to insert this is gonna be our submit button go to insert illustrations and Excel 2016 go to shapes and then we're gonna do rounded rectangle you can choose any one of these do a rectangle if you want but we want it to look like a button will put this button right here and then without typing anything or without hitting anything clicking anything we just hit submit we just type submit' and we can hit the Escape key click that go to the Home tab and we want to Center it horizontally and vertically and add bold now when you click the button you can go to the format tab and you can format this guy to your heart's content you can make it match any theme that you need or do all sorts of jazzy stuff but I find for most forms it doesn't matter let's just make a nice little blue button okay now we can actually go ahead and create the outline of our form so let's select where our form will be select the little tiny space or column does the outside and go down as low as you want I'm gonna go down a bit here then we can go ahead and give it a boarder let's give it a thick outside border if you don't like the borders here click more borders and you've got tons of options here so let's go with thick border now let's go to the bucket got to change the background color you can change it to whatever color you want but subtle is always best if you're dealing with a form subtle is best so choose one of these top colors up here I'm gonna choose the one on the far left and now very important let's go ahead to this column over here where our inputs will be and in the same row as our labels I'm just hitting the ctrl key and clicking them so we can format them together then go back up here to the bucket and click white or the same color as the background color or whatever you want your input color to be and now we're starting to get something that looks like a nice little form I actually do not want column G here so I'm going to delete it much easier to delete columns then add them notice when I delete it everything looks okay I go ahead and add one right here where are you insert it's just all kinds of not cool and for the most part that is your form right there you can adjust the settings a little bit more or as much as you want let's say I want to make this spacer columns a little smaller I want to pull my button down here a little bit just make things a little bit tighter but the formatting is pretty much there and you can see just how simple it is just a few tricks to make the box and do all of that and now the final touch the cherry on top is to remove all the crap that makes this look like a spreadsheet so you go to the View tab in the show box or a show group uncheck headings formula bar and gridlines now we can zoom in and we can double click any one of these labels up here to get the ribbon menu to close now we have a nice giant form and that's all for this tutorial check back for the next tutorial where I will show you how to make this a bit more interactive we're gonna validate what the user puts in here and we're gonna set up some of the tricks that will tie together all at the end to make this more interactive form I hope you liked the tutorial if it was don't forget to give it a thumbs up and make sure to subscribe and accept notifications so you can see all the new tutorials

Channel: TeachExcel

Views: 91,070

Rating: 4.9266667 out of 5

Keywords: excel forms, training excel, microsoft excel, microsoft office, excel help, data anlysis in excel, teachexcel, ms office, ms excel, make better spreadsheets

Id: 9j8mAJGFWPg

Length: 11min 17sec (677 seconds)

Published: Tue Oct 23 2018