How to Create Fillable Forms in Excel - Employee Engagement Survey Template

In this video, I'm going to show you how to  create a fillable form in Microsoft Excel.   In previous videos i've shown you  how to do this using Microsoft Word   but there are times where you may want to draw on  data from a large spreadsheet database and pull   that data into your form and in those cases  it's going to be easier to create your form   in Microsoft Excel. I'm going to show you how  to format your form so that when your users   open it up it's going to look like a form and not  necessarily like a typical spreadsheet. We're going   to go step by step in this video so i can show you  how to build your fillable form in Microsoft Excel.   All right so here is an example of the form  that we're going to build today and i'm going   to show you some of the features and how we're  going to design and build this. You'll notice   when i go to the print preview up here, I'm going  to click on print preview and you can see that   from a user's perspective it doesn't look like  a spreadsheet at all it looks like a normal page   with a form on it with three different questions.  All right so the way that we're gonna some of the   elements that we're gonna look at today when we  design this from scratch we have a drop down list   that we've inserted into our Excel form where we  can select either Full-Time or Part-Time as an   option on this form, it's a an employee  engagement form sample and if I notice the   text and the instructions if I select Part-Time  that the Part-Time instructions pull up so it's   a way using an IF formula that will pull in  different information into that cell based on   the user's selection from this drop-down list so  that's one feature we're gonna see how to do today   also over here we have check boxes that  users can check to make a selection in one   of the questions and then the other thing that  we're gonna look at today is how to use VLOOKUP  to pull in some information based on a drop down  list so down here if i have a drop down where i'm   asked who my manager is and i select from a  list of names i can select a name and then   have data automatically populate based off  of that name perhaps the title and department   is the example that i'm using in this today but  think about if you're creating a fax cover   sheet or other type of form you could  even have your vendors listed out and   based on selecting a vendor maybe that vendor's  address would pop up in the form so there are a   lot of ways that you're that you can use this  and i'm going to show you how to set that up   another thing that i'm going to show you  if you hang out until the end of the video   I'm going to show you how to hide tabs in  your Excel spreadsheet and in the workbook   and I'm going to show you two different ways  to do this a way to hide them and then a way   to make them very hidden so be sure and stay  till the end so that you see how to do that Okay so let's go ahead and start with a blank  spreadsheet and so from here the first thing   that we're going to do to start creating our form  in Excel is we're going to come up to page layout   select size and then we're going to select  the letter and if you notice very faintly   what happened there was this dotted line appeared  and this is my page gridline so when i'm designing   my form now I know what I need to stay within  that dotted line so that I can stay within an   eight and a half by eleven size piece of paper  so that it looks like a normal page for my form   and so if you scroll down you can see these very  faint lines here as well and that's the bottom   of your page so that's the framework that we're  going to work within because to design this form   we're going to be merging cells and hiding grid  lines and doing different things so we're going   to want to make sure that we have that eight and  a half by eleven size page selected all right so   the first thing we're going to do I'm going to  expand the top row because this is the row we know   we're going to want to put the title of our form  and we're building an Employee Engagement Survey   and then I know I have a line a row for some  instructions and then my first question is gonna   be let's double check here we're gonna  ask please select your employment status   so I'll go ahead and type that first question all right and so i'm gonna go ahead and start  formatting my columns i'm going to drag it over   so that this column will encompass enough  for some of my text here and my questions   and then i'm going to also expand my column c a  little bit for my drop down answer so notice how   the dotted line is still there but the the columns  um are moving based on my adjusting the the size   so now what i want to do is if this is somewhat  the framework that i'm going to work within   so now i can start formatting it a little  bit and what i'm going to do is i'm going to   close in the first column a little bit because  that's just where i want to number my questions   but i'm going to select all of the cells that are  in that top row and i'm going to go back to my   home tab and i'm going to select merge and center  then i'm going to align that over to the left   i'm going to increase the font size to make  it big and then i'm going to make it bold   and then i'm going to continue to use these  alignment buttons up here and align it into the   center of the text so then what i want to do is i  can either go up to insert and grab a picture or   logo from my computer or i can copy and paste if  i have it open in another document so that's what   i'm going to do i'm just going to copy my logo and  you can put your logo and what you can do is you   can make sure that it stays within your grid lines  there and then you can expand your column and   that way you can fit an image or a logo in the  top header so you've gotten your header set up and   um and some of your your formatting and framework  done all right so i'm gonna italicize this because   this is our first question and then the first  thing we're going to do is set up for a drop down   list and this and to insert a drop down box into  excel one of the ways to do this is to create a   table in your workbook on a tab that you're going  to ultimately hide and so what we're going to do   is we are going to set up our drop-down list items  and make it a table then we're going to insert the   drop-down form field into this cell so over here  on this sheet i have some text that i'm going to   use later on so i have it pasted here for ease but  i'm going to go ahead and create my drop-down list   for the status type which is i'm going to say full  time and part time and all i do is type the list   the whatever i want in my drop-down list i'm going  to just type it into different cells in a column   and then what i'm going to do is click on any  of those items that i create in the list and i'm   going to hit ctrl t and that is going to create  a table and i'll click ok and then what i can do   is i can say please select will be the instead of  it saying column one i can overwrite that and say   please select or select status and then because  now that this is a table it will be dynamic and so   if i ever add anything to this table uh the drop  down that i create on the other page on the other   sheet is going to automatically update based  on what i input here in this table so now that   we've created the table what we can do is come  back over here to sheet1 and we want to insert   the drop down list here so all we have to do is  go up to data under the data tools areas there's   a drop down here and you're going to select data  validation and that's going to open up a window   and allow you to select here you're going to drop  down and select list and then the source put your   cursor in the source area and then you're going  to come over to your your sheet 2 here where you   have your your table and you're going to select  that as your source and you're going to click ok   and so now you have a drop down over here in  excel so if somebody were to click on that cell   they would be able to select full time or part  time so that's how we insert the drop down list   now based on that drop down list we want certain  instructions to appear automatically so we'll make our instructions here and let's just say uh we  want to create a box here we can merge and center   we can left align it and put it in the center and  this is where we're going to use an if formula   and the reason i'm using an if formula here is  i want this to default to the full time text   however if someone selects part-time  from the drop-down i want it to display   a different set of text so the way to do that is  come here and insert in a formula you type equals   if and then we're going to select  the cell that we're referencing   so that enters C3 into the formula so  we're going to say if C3 equals part-time and i'm going to type it just the  way that it is displayed in the list then i want it to return if it's true what we want it to  return is some sort of part-time text and then we'll put a quote to end that and  then you put another comma and now the value if   false then we want it to return uh whatever  the full time text that we want it to return   so i'm just going to say full  time text for now but you can   type out or paste in into this formula between  those quote marks any type of text that you   want to display and so then i'm going to hit  enter i'm going to close my brackets and hit   enter so now it's defaulting to the full-time text  because there's nothing selected here however if i   select full-time it will default to the full-time  text but if i select part time it will pop up   with the part-time text so that's one way to use  the if formula that's a logical value um if if   a cell is returning something you um if it's  true you say this if it's false it says this so   that's how we're using that formula here for this  conditional drop down all right the next thing i'm   going to show you is how to insert those check  boxes into your form so the next question was   question number two and how long have you worked  for the company so how long have you worked here and what we want to do is i'm just formatting  it the same way as my other question   and then what i can do is select in the area where  i want to insert a checkbox and then i can come up   to my developer tab and if you're not sure how to  enable the developer tab be sure and check out one   of the videos on my channel how to enable it it's  the same way as you would enable it if you're in   microsoft word all right so you would enable your  developer tab and then you have a button over here   where you can insert certain controls and we  drop down from there and we can check um have   a checkbox form control and so um what we want  to do is draw that box wherever we want to have   the checkbox and so it places the box where we can  grab it and drag it and place it wherever we want   so from here we can place the check box here it  actually if you expand it it does say check box   so we um you can highlight that text and  delete that so it's a simple check box and   from there you have that you can copy and paste it  and if you want to insert more you can right click   i'm going to resize it back down so it's smaller  and i'm going to simply copy by hitting ctrl c   and i'm going to paste my other two check  boxes and then i can drag those and place them   wherever i want in my document so if i want them  to line up here all i have to do is select them   i can right click and then drag those over  here and those check boxes are nice because   you don't have to protect your document at all  for a user to check one of them to put a tick   in that box all they have to do is check the  box and it will select or deselect there that   way you have your check boxes that you can place  anywhere you need to in your excel document and so   for here our labels were um you know one to  two years so forth so you could fill in all   of your answer options for those check boxes in  those different cells and again if you need to   move those formatting around you can right click  and use your little arrows will make those micro   movements so you can adjust it and get it aligned  hopefully just right for you and i'm gonna just   move that over so it's in line all right the next  question the that we're going to set up is going   to use a VLOOKUP formula to look up information  from a large database if you have a large database   a big spreadsheet of information that you want  to pull pieces of information out of and return   data based on something that's selected from  a drop-down list this is how you're going to   do it so we're going to say question number  three is we want to lay say who is your manager and then based on what they select the manager's  title and maybe the manager's department will automatically populate so we're gonna  highlight this cell i'm going to come back   up to the home tab and highlight it because  that's where we're going to want to insert   our drop down list and so our drop down list is  simply going to be uh the list of names of our   from our database and so sheet3 over here  is a simple database that i've set up   as a table that we're going to turn into  a table so if you have a whole spreadsheet   what you're going to want to do is click  anywhere within that data and hit control t   and make it into a table and click ok and then  this turns your information into a table and   notice that um it did add extra columns and so  what i can do is replace that with the supervisor   title and department that we want um to be in  those headers and then i can delete that top row   so now we have our database that is in the format  of a table so now we can use it not only to create   the drop-down list but then also to use the  vlookup formula in those other cells where we   want to return the title and department depending  on what is selected again the other example that   i mentioned that you can use this for would be to  return vendor addresses or something like that in   your form that may be an invoice or a fax cover  sheet type of form that you're using this is   very helpful so then what i'm going to do is  come back over here and we're going to insert   our drop down list again we go up to data we go  up to the data tools the data validation area   and we're going to select the list value again  and in the source tab here we're going to come and   select just the first column of our database here  so i selected up here i selected a so it selects   the entire column as the source and i'm going to  click ok so now in my drop down i have three names   the three names that are in my database uh one  thing that's neat about uh the fact that you've   linked that source the entire column is now if i  ever go back and say i add or change something on   my database if i make a correction to someone's  name or replace a name or add a name i can add another name to the database and then when i come  back over here to my to my sheet and i select   down you'll notice it automatically inserted  whatever i added or updated into my drop down   list so another helpful feature of linking this  drop-down list to a table that might be hidden in   your document is that if you have multiple forms  and you're using the same drop-down then this is   a really nice way to make sure that all you have  to do is make that addition or change once and it   can update all of those fields in that drop-down  so it's dynamic that way which is very helpful   all right so now what we want to do is return  that manager's title and department based off   of what the user selects and in this case what  we're going to do is use a VLOOKUP formula   so you can come up to the formulas tab and if  you um have recently used vlookup you can find   it here you can type equals VLOOKUP or you can  search for it it is up here in your formulas tab   so what i like to do is um bring up the function  arguments table and then go from here so the   value that we're going to look up we're going  to select the cell that we want to reference   so it's going to look up based on whatever  is selected in that drop-down list   then it's going to return um the column and  row of information that we want it to from   our database so then the table array we're going  to put our cursor in the table array go back to   sheet 3 and we're going to select the entire  table and that's going to be our table array   the column index number is going to be which  column in our database that we want to return   so for title we know that's the second column  over number two b so we're going to put   number two the number two in the column index  and then range lookup we're going to put false   and that is because we want it to return the  actual value that's in that database so then we   click ok and now we have our vlookup formula there  and notice how what the way VLOOKUP works is if it   if there's if it deems it as an error meaning well  there's nothing in this cell to look up therefore   i have nothing to return to you therefore i'm  saying #NA and so if you um what we're gonna do is   wrap the vlookup formula in what we call an if  error and then if it's if error then we want it to   appear blank because it's not really an error it's  just that VLOOKUP isn't finding anything to put   there so it's putting hashtag in a we don't want  it to show hashtag a we just want it to show blank   so the way we do that is we come up here and  we say equals and then we say if error and then   we're going to put our VLOOKUP formula in keep  that in parentheses and then we're going to put   a comma and then we're going to put two  quotes with nothing in between the quotes   so the nothing in between the quotes means  that it's going to be blank and then we're   going to close the parentheses and hit enter  so now that's blank so we don't see anything   but you'll notice if we do select  from our drop down if we select a name   the title does pop up so we know that here  we can see that we have our VLOOKUP formula   that's wrapped around an if error to so that it  will hide the hashtag in a and we're going to   just basically copy this exact same formula  and so what you want to do is hit Control C   and then we want to um put our cursor hit  enter so we don't mess up that VLOOKUP formula   we select the cell that we want to also add  that formula into double click and paste and now   it what we want is column number three because  the third column column c was the column with   the department so we're going to put replace 2  with a 3 but keep the same formula for everything   else and hit enter so now when we make a selection  here um the title and department update based on   what we have in our database so you can try that  and then if for some reason nothing is selected   you can delete the information out of this and  it will show a blank so now before we finish up   a couple other things that i want to show you  how to do to make this easier to read and look at   so one of the things that i've done on the  original form that i showed you was i hid all   the grid lines so that it just looks like a blank  white piece of paper instead of having all of the   little grid lines that a normal spreadsheet  has when you log into excel so to do that   you simply come up to page layout and um check  the box where it's normally defaulted to view   under grid lines just uncheck it and then that  way your spreadsheet those gridlines don't show   up so when your user opens up the document it  just looks like a clean white piece of paper   kind of like microsoft word looks um the other  thing that i like to do is we know that our um   our margin line for our page is right here and so  i like to leave a little bit of some spacing here   just in case that i say i've put a border  around some of these columns and things   for example i want the header to have  a thick box border around it and maybe   these instructions i want to have a regular box  border um an outside border around that so i   squeeze in the last column over here and  then what i'm going to do is i'm going   to highlight the first row outside of that and  i'm going to hit ctrl shift and my right arrow   and when i do that it selects every single entire  column to the right of where of the column that i   highlighted and now what i'm going to do is right  click up here and hide all of that and so then i'm   going to come down and do the same thing at the  bottom of my page i'm going to select the row and   then i'm going to hit ctrl shift and my down arrow  that's going to select every single entire row   in the entire spreadsheet and then i can come over  here right click and say hide and the reason i've   done that is again to make it look like more of  a clean white piece of paper that your form is on   so that people don't get confused when they open  up an excel spreadsheet they just see it's a   massive spreadsheet with all these rows and  columns now they're seeing it's a nice formatted   page with your form on it all right and so the  other thing that you're going to want to do is   you're going to want to hide your other sheets in  the document so you don't want your users when you   email or you might email this form to them you  don't want them to be able to see these other   tables the other sheets that you put your tables  on now especially i would recommend that you never   include any table that has any confidential data  or information in it your data's tables that   you're pulling from should have information  that's publicly available just because   you don't want to send this off and have someone  unhide or find that data if it happens to have   confidential information in it so be sure to  be careful about that but all you have to do to   hide sheets is to right click on them and select  hide right and so that is an easy way that you've   hit you've hidden all those other spreadsheets so  that they aren't readily visible when you go up to   to see the spreadsheet when you  open it up so your users open it   but if a user is familiar with excel then they  know that all they have to do to unhide a sheet is   come down at the bottom click unhide and then they  can see that there are two other sheets down there   that can be unhidden so there is a way that you  can actually set these to be what's called very   hidden so i'll show you how to do that real  quick so let's go ahead and unhide these right   quick i'm going to right click again and unhide  and select sheet 3 and let's name these and to   name them all you have to do is double click  and so we're going to say this is the survey   sheet and we're going to say sheet 2 is called  table 1 and then this one will be our database all right so what i'm going to do now instead  of simply right clicking and selecting to hide   the other tabs what i want to do is come up  right click on any of the tabs and i can select   view code and this is going to open up your  vba window what we're going to want to do   if you you select the sheet that you're going  to want to hide and if you don't already have   your source properties so if you open this  up and you don't see anything down here   you simply hit f4 to display this window and your  properties window is going to display down here   and there you're going to see at the  very end something called visible   and you're going to have a drop down here  where you have the ability to make the sheet   very hidden okay so i'm going to select very  hidden on that one and then i'm going to come up   to sheet 3 and i'm going to also make sheet 3 very  hidden and i'm going to click save and then i'm   going to i can save my documents it's prompting  me to save it and so i'll go ahead and save it   and then i can actually close out of there and you  notice that those other sheets are hidden and when   i come down and right click there's nothing to  unhide so that's a great way to make sure that   any sheets you don't want to have visible make  them very hidden um the way to bring them back   is to remember to right click and go up to view  code simply select the table again and make them   visible or even just hidden and then you can right  click and unhide all right i know we covered a   lot of information in the video today what i'm  going to do is include a table of contents in   the description below this video that will allow  you to jump to the particular part of this video   that you may want to go back to and re-watch  also i've made this form available for purchase   as a download you can click on the link in the  description below the video to purchase this form   that you can use to customize for yourself and  just so that you don't have to start from scratch   if you found this video helpful be sure and  give it a thumbs up to like it you can click   the subscribe button to subscribe to my channel  and then click the bell to receive a notification   every time i post a new video visit my  website sharonsmithhr.com and thanks so   much for watching thanks for all my viewers with  all the great questions and comments if you have   any comments or questions be sure and leave  them below thanks and i'll see you next time

Channel: Sharon Smith

Views: 618,040

Keywords: how to create fillable forms in excel, creating forms in excel, formatting forms in excel, create drop down list in excel, create dropdown in excel, conditional text in excel, employee engagement survey, how to format a form in excel, microsoft excel tutorial, excel tutorial, excel lessons, create checkbox in excel, how to use vlookup in excel, how to use if function in excel, interactive forms in excel, link text to dropdown in excel, hide gridlines in excel, hide tabs

Id: _g7863PjNns

Length: 28min 24sec (1704 seconds)

Published: Tue Oct 20 2020