We probably don't need to tell you that you can easily map out large amounts of figures and other data in Excel. But did you know that you can also make entering that data a lot easier? All you have to do is 'build' an input form.
Tip 01: Why?
Why create a fill-in form when you can just type data directly into Excel? It's true that Excel is great for that, but if you've worked with the program before, you'll also have noticed that sometimes it's not very practical to have to click on cells first, accidentally click on the wrong cell and so on. . Searching for data in a large Excel document can also sometimes be difficult. A fill-in form can make all of these things a lot more efficient. Entering is a lot faster, you can be sure that you fill in everything in the same way, never forget a field and you can also easily search the entered data. So get started!
Tip 02: Add button
However useful such a form is, by default Excel does not show this option at all. To be able to use it, we will first have to add a button ourselves to the Quick Access Toolbar (the toolbar at the very top with, among other things, the option Autosave). Right click on any button in this menu and then click Customize Quick Access Toolbar. By default you see below Choose assignments, the option Popular assignments are selected. Click on this drop-down menu and then on All assignments. Now scroll until you see the option Form see and then click Add and finally on OK. A button has now been added to the Quick Access Toolbar, with which you can use a form.
Make sure Excel knows what to do with your values Tip 03: Organize the table
You can use the form to easily enter data in your Excel document. But of course Excel can't read your mind and really has no idea what exactly should be in the form. Fortunately, you don't need to be able to program to realize that; all you need to do is indicate what information your table/document will consist of. Suppose we are the head of an association (football, theatre, dance, you name it) and we have just had a successful open day where more than 200 people filled in a registration form. We want to enter these forms as efficiently as possible. That's what we're going to do with the (digital) form in Excel. For that we must first indicate from each elements this table of data will consist. In our case that is: First name, Last name, Date of birth, Telephone number, E-mail address and Desired start date. Of course, these headlines can be anything you can think of, this is just for our example.
Tip 04: Cell properties
An important next step is to define the cell properties. You can use the form we're about to create without defining it, but if you want the data you enter to be displayed correctly, it's important that you tell Excel what kind of data you're entering. In our case, we select the column First Name and Last name and right click on Cell properties. Then we choose Textbecause this is just plain text. Then we do the same for the column Date of birth but there we choose the property Date. That way we let Excel know exactly what kind of content is going to be in which column. By the way, what we call a column in this article can also just as well be a row, after all, you can also put headers in the first column instead of in the first row, it just depends on your preference.
Finally, we give the table an official table format, we do that by selecting the columns (or rows) and then clicking on Start and then on Format as table under the heading Styles. Of course you can decide for yourself what format you choose.
Tip 05: Create a form
Excel now knows exactly which elements our table consists of and how it should shape that content. That makes it very easy to start creating our form now. At least make it, it's a matter of pressing a button. Select all the headlines you just created and then click the button Form that you have added to the toolbar yourself Quick access. If you skipped the formatting step in tip 4 (after all, we said that in principle this is possible and allowed) then something appears that looks like an error message, but in reality the program simply offers you some tips and options. click on OK. If you have added a table format to the cells, Excel knows exactly what you expect from it and this message does not appear. A small menu will now appear and you will see that the input fields of that menu correspond to the headings you have created for your table. Don't worry if you've made a typo in a header: you can easily adjust this, after which your change will be immediately implemented in your form.
Tip 06: Fill in the form
You will now notice in seconds why filling out an Excel document using a form is so much more convenient. Next to the headers you have entered, you will see an input field in the window. You can enter the actual data there. When you're in a field and typed in the desired value, press the Tab key to quickly move to the next field. When you press the Enter key, the content is actually added to the form. This way you can enter your data really fast without worrying about accidentally overwriting the wrong cell. If you want to navigate even faster between the fields (for example if you don't necessarily want to fill in all the fields), you can also jump using the Alt key. Below your headers you will see dashes under specific letters: when you press that letter in combination with the Alt key, your cursor jumps directly to the corresponding field.
You can even generate error messages for incorrect input Tip 07: Find data
Each time you press Enter, the data you entered in the fields will be added to your Excel sheet. However, the form window is not only useful for entering data, you can also easily search your data with it. When you click in the form window Find previous or Find next, you can easily scroll through the values of your Excel document. However, if you are looking for a set of specific values, click the button Criteria and simply enter the criteria you want to search for (for example, all lines with a specific date of birth). when you're on now Find previous or Find next click, only the values that match the criteria you entered will be displayed. What you should be aware of is that the data you look up can then be changed very easily. Suppose you have found an entry, and you type something else over it, this text will be replaced in your Excel sheet as soon as you press Enter. So be a little careful. You can also delete data by simply clicking remove clicking when you have found a particular entry.
Tip 08: Limit Date
Let's say you're filling out those 200 forms, and you realize there's a certain date range where classes/activities can't start. Now if you think: I'll remember that, I'll make sure the start date isn't within that range, then you'll often be disappointed: most people are a star at overestimating their own memory and attentiveness. It is much more convenient to let Excel indicate that a certain date range is not allowed! You do this using data validation. Please close the form and select the entire column Desired start date. Now suppose that it is not possible to start between March 1, 2019 and March 16, 2019. Click in the ribbon now Facts at the cup Data Tools on Data Validation. In the pop-up window, click in the drop-down menu below To allow on Date. Choose under Given in front of Not in between and then fill in 1-3-2019 at Starting date and 16-3-2019 at End date. Then click (still within the pop-up) on the tab Error Message and fill in below Error Message the reason why this date is not allowed, for example: “Date not allowed, trainer on vacation”. When you now try to enter a date in the 'forbidden' range, you will get the entered error message.
Tip 09: Prefill
In the example we mentioned here, all fields are filled in using the form. But it is also possible that you want to enter a default value for a certain column. For example: the Desired start date must always be May 1, 2019. Let's assume an empty Excel sheet with only the headers of the table filled in. In (our case) in cell F2 (i.e. below Desired start date) we enter the following formula in the formula bar: =IF(A2"";"5/1/2019";""). In fact, it says here: as soon as something is entered in cell A2, place the value 1-5-2019 in the cell containing this formula (F2). Now click on the cell, and drag the small green square at the bottom right quite a bit down (within the same column), to also apply the formula to the rest of the cells in this column. When you enter data in your form, you will see that the field Desired start date can no longer be filled in. So the default is 1-5-2019.
Tip 10: Conditional data
Interesting about the trick we pulled in tip 9 is that you can also use this to use conditional data. For example, suppose new members born before 1995 join a specific group whose start date is May 1, but members born in or after 1995 join a group whose start date is June 1. To make things easier for us, we'll change the date of birth field to year of birth (it can also be extrapolated from an entire date, but that's much more complicated). Adjust the Cell properties of this column and choose Number instead of Date.
In cell F2 (Desired start date) we now enter the following formula =IF(C2>1994;"6/1/2019";"5/1/2019"). To summarize: here it says that if C2 (year of birth) contains a year that is greater than 1994 (so 1995 and higher), then the date June 1 must be shown in F2, and in all other cases June 1 must be shown. . That way you can really save yourself a lot of work.
