Create an Excel Pro Forma Template for Technical Support Effortlessly

Excel pro forma template for Technical Support

in this video you learn how to make a data entry form in Excel like this where you can add values checkboxes drop downs and even popups depending on your answer once you click submit all of the results will get collected in a new sheet like this there's really just four main steps to this firstly is formatting the Excel sheet to look like a form secondly is creating a new sheet to host all our results thirdly is automating the whole process and finally will protect the submission form so other people can't break it starting with the formatting and over here you can see there's the Excel file which right now just has a few entries of the things we would like people to write let's suppose that this is for a big Tech conference and we want to monitor who's going to be coming and you can download this exact same start file if you want to follow along in the video description all right so over here let's try to format this a bit nicer so this top part I'm going to make it in dark blue and I'm just going to pick a custom color for me that's going to be 073 673 but feel free to pick a different one if you want then I'm just going to choose a white here let's suppose that we're working with Microsoft for this conference so maybe we can add their logo for that we can go over to insert pictures and place over cells we wanted from this device and you can download it obviously from the internet if you want to find their logo as well I'm just going to put it over here and resize it logo's looking good and now we want to work on these parts just so it's more obvious for people to know where exactly they need to put their first name or their last name so for this we're going to color it in a light gray like this one over here and let's also add borders all around so it looks something like this now that we have the formatting for one we can just copy paste it across all of the other areas that we think we need this so the entry time will omit that one for now sandwich drink and dietary restriction S as well for the entry time we would like to create some kind of checkbox that says morning or evening so we can choose one or the other and for this we'll head over to the developer tab if you can't find it just go to any other tab right click on this top part here and go to customize the ribbon from here you'll see this popup and you want to go over to where it says developer and take on that on the right hand side and click on okay once you do that you should be able to see it and from here we want to go to insert and we'll click on this dot right here called the option button and we want to add two of these so one is going to be for the morning option let's say and another for the evening so we can just call this one morning and let me right click on that so we can select it and I'm going to stretch it out a bit and just duplicate it with crl d then we're going to have the evening option as well nice so you can see we have a morning and an evening let me quickly reformat these just by selecting both of them with the control key and I'm going to align them to the top so over here I'm going to go to align to the top nice so we now have the checkbox and the next step is going to be creating a drop down for the dietary restrictions we don't really want people saying it depends or something like that so just a yes or no for that we can head over to the data tab over here and click on this tick mark which is the data validation what do we want well we want to create a list that simply says yes comma or no so those are the two options separate them with a comma there and click on okay now you'll see we get this drop down and we have the yes option or the no option as well we can copy the same format to the this one and this one for sandwich and drink as well that said with these restrictions if it's a yes we need to know exactly what their allergy or their restriction is so right below it we could add some kind of condition that says is that if hit the Tab Key there this says yes so if this is equals to in quotations yes closing quotations comma then we want it to say some kind of message we can put that in quotations and say something like please contact info@ ms.com with details closing quotations comma and if it's false meaning it doesn't say yes then we don't want it to say anything so we'll close those quotations close up parenthesis and hit enter now we could change this to a red color and let's say we also want to italicize it and make it a bit smaller it looks something like that and so now if I change this to no there is obviously nothing that shows up but if there is an allergy or something like that then they need to give further details here finally we're just missing the submit button which we'll work on later but before that we should get rid of some of these grid lines which we can do by going to the view Tab and clicking on grid lines also let's separate this form a bit more from the rest of the Excel file which we can do just by selecting it let me add a bit of margin on the bottom for the actual submit button and what we want to do here is head over to the Home tab add a border and for everything surrounding it maybe we should change the color to something like a gray like so same thing up over here on this side as well and all the way on the bottom too to this way control shift down control shift right there and I'm just going to add a gray Bard there and you can see there that it's a lot more obvious where the submission form is that's part one done and secondly we have the results table for this we're going to create a new sheet let's go ahead and name this first one something like the form hit enter and the second one is going to be the data hit enter again and over here what we want to do is first we're going to list out all of the different entries that we have available so the first one is the first name then we have the last name and so forth so let me fast forward that so you can see I've listed all of the headers down here and right next to them we just want to link their answers so this one is the first name then we've got the last name and so forth I'm going to fast forward that too for the entry time over here it gets a bit more complicated that's because the answer is not that clear instead it's a checkbox so what we want to do is select on any of these checkboxes right click and go to format control here when checked we want to link a specific cell which is going to be the one right next to the answer itself so over here to the side now I'm going to click on okay you'll notice that we get a one there and when it's evening and I click on that it switches to a two so the idea is that we can create a simple if statement hit the if and then if this figure over here is equals to A2 comma then it's going to be an evening that's being ticked right now comma and if not it's going to be a morning that's being ticked close those quotations close a parenthesis and hit enter so right now it's evening but I switch this and it's going to be number one meaning morning this side is looking great and don't worry that it's right below the form as we'll hide this later for now we're just going to select all of these headers contrl C and we're going to paste them over here to the side but we don't just want to paste them instead we want to go to paste special we'll paste them as values and more importantly we want to transpose them which is going to switch them from going vertically to going horizontally click on okay you can see what that looks like there I'm also going to change the colors to the blue and the white font as well and to space this out better I'm going to go to alt hoi that's the same thing as is going to format and auto fitting the column width there so these two Tabs are looking good and we're going to create a third one that's going to be called something like roll hit enter there and I'm just going to zoom it in a bit more you'll learn why we need this one later when we automate this moving to the third part where everything is going to come alive through automation we're going to be using macros which are essentially a pre-recorded set of steps that we're going to tell Excel to memorize so we can click on a button it's going to do it automatically every time and don't worry we're not going to be programming or anything like that for this so let's take a look and I'm back on this first form sheet you can see I've added some very basic values feel free to do the same and to get started we'll head over to the developer tab and here we're going to click on record macro in this popup let's go ahead and name it whatever we want let me just call it automate for now it's fine in this workbook and I'm going to click on okay now you can see it's already recording up here and we can get started with the steps the first one is going to be to take all of the answers that we currently have from down here contrl C to copy those and we're going to go over to the raw tab here we want to paste them across to the side so we're going to do right click go to paste special and in here we want to paste these as values and we also want to transpose them click on okay there you can see we have all the answers well aligned we're going to hit on contrl C and we'll head over to the data tab once we're over here as our first entry we're going to want to go to the Home tab and all the way to the right we'll go to insert insert copied cells and this is a really important step we want to hit on shift cells down click on okay there and so whenever we get a new entry it's not going to delete the previous one instead it's it's just going to take it one down now we can go back to the form tab here and we want to delete all of the current entries so we can just select those and delete them like so and same thing over here to the side once we've done that we can just go back to the start here go to the developer tab and turn off that recording awesome so we've just recorded this whole sequence but now we need to tell Excel how exactly to run it and for this we're going to create a button on the bottom so so under the developer tab again go to insert and select a button we're just going to extend that over here towards the bottom and now you'll see that you'll get a button like this that it asks where to assign this macro to it's the automate one which I spelled incorrectly here we can just select that and click on okay so now whenever we click on this button it's going to automate this whole process but first let me rename this to something like submit and now we can just add a few values let me fast forward that and once the values are all in we can just click on submit there and if we go over to the data tab you'll see that we have his whole entry as well so we have bills here and mics as well which we added earlier that's gone one down going back to the form sheet over here one thing we want to do is hide this whole area and we can do that very simply by going to this color and matching it to the background color like so now no one's going to suspect there's anything below awesome the final step for us would be to protect this sheet and to save it in a special Excel format but before we do that if you're impressed with this XL macro this is really just the tip of the iceberg in terms of what macros and more specifically VBA can do in Excel if you want to learn how to automate even further check out our Excel VBA and macros for business automation course if you're looking to automate data analysis time TKS financial reporting and spreadsheet formatting learning VBA is going to be a GameChanger for your office productivity in the course you learn fundamentals such as object properties methods and variables once you get a f for the basics we'll introduce more Dynamic features like conditional statements looping functions and data arrays with this knowledge you'll be able to perform a whole string of common spreadsheet tasks like autogen generating pivot tables formatting charts building interactive input boxes and more so if you want to save hours of time in your future tedious office tasks check out the link in the description below moving on to part four and right now with the form over here people can change this from first name to something like hello and all of a sudden the whole form doesn't make sense anymore so to prevent that from happening first I'll press contrl Z to go back and we want to protect these cells so we want to select all of the ones in Gray right now so it's going to be all of these over here and once we have this selected we want to go over under home format first untick on this lock cell and secondly go back in there and click on protect sheet now I can add a password in here let me just quickly do that and click on okay I'm just going to enter the password so we can proceed and and as the first name if someone wants to come in and change it to let's say Nike then you'll notice they're not able to do so however if they want to add something like their first name I'm just going to put bill in here hit enter you'll see they're able to add that and they're even able to click on submit and the data fully comes in here obviously there's zeros right now because we didn't add any data inside one thing I did want to note is that I haven't been able to unprotect these for the entry time because they're a bit awkward in that they're not a cell but rather they're some kind of a button it's not that easy to take them on or off with the protected cells option so if you wanted to work around this you could just create a drop down for the morning and the evening instead of creating these two checkboxes let me know down in the comments if you have a solution for this one because we've been working with macros we want to save this as a macro enabled workbook so we can go to file save as the shortcut is just going to F12 and from here I'm going to change this from an Excel workbook to an Excel macro enabled workbook and we're all good to go speaking of automation if you want to learn how to automate invoices in Excel check out this video over here or take our Excel VBA and macros course over here hit the like and that subscribe and I'll catch you in the next one