Create Excel Database and Data Entry Form. Excel Magic Trick 1690.

in excel we want to enter a record into a form and have it appear in our database now technically in excel this is not a database but for us it's a table where we can store [Music] data [Music] step one in creating a database in excel is to create the column header names also called field names and very importantly once we create them you want to highlight and we're going to add bold up here or use control b you always add some formatting to the column header or field names so that excel knows that these are different than the records that we're going to enter below now we want to enter our first record so we type the id and i'm going to use control enter because i want to enter that number and keep the cell selected because we want to add the correct id formatting to this first record here so as we add new records the formatting will automatically be copied down in the home ribbon tab number group we click the dialog launcher and in the format cells dialog box number tab we want special now we have 10 digits for the id in our company and if we select social security it almost looks correct what we've done by coming to special and selecting this is actually behind the scenes it wrote some code we can change the code by going down to custom and we can see that zeros and dashes are all it takes to create the correct custom number formatting we have four digits before the first dash so we type four zeros and then dash zero zero dash and the remaining four zeros click ok now that's a number formatting you can see up in the formula bar that the number is actually stored without the dashes i'm going to use the tab key and type the first name tab type the last name tab the higher date was 8 slash 2019 now when we enter dates like this and hit tab a certain number formatting is added if you want to see the full years you can come back up to dialog launcher for number and in the date we can select the date we want this looks like what we want now we click ok tab the starting salary 69 430 control enter and now instead of using the dialog launcher we can use the drop down which has the most common number formattings we'll select currency tab the phone number and when i control enter now i want to come to the dialog launcher and i'm going to try to find one under special and sure enough there's a phone number click ok tab to enter the email now when i enter this email it will automatically add a link if you don't like that you can right click and come down to remove hyperlink i'm going to keep it for this data set now all we've done is enter one record but the significance is we added all the correct number formattings so as we add new records that number formatting will be carried down now the way we do that is by converting this proper data set field names in the first row records and subsequent rows we convert it to an official excel table we can do that by selecting a single cell it doesn't matter which cell insert and there it is table you can also use the keyboard control t now my table has headers that's checked which we need the reason that the dialog box correctly checked it is because we added bold formatting to the field names at the top click ok now it adds some formatting you can change that up here in table designs that's a context-sensitive ribbon if i select outside the table it disappears click inside the table and there it is over in properties we definitely want to name this table object so i'm going to come up and call this employee table and enter once you have your excel table to add new records you can simply click directly below the last record type your data and use the tab key the records will automatically be added to the bottom of the table but this video is about adding an excel form and so we want to see how to do that now we'd like to add a data entry form so we can enter data conveniently into a form and the records will appear in the table the problem is this is an older feature that doesn't exist anywhere in the excel ribbon or in the quick access toolbar at the top now we want to move the quick access toolbar down below the ribbon so i come to the end click the drop down and select show below the ribbon now i want to customize this and add the form button so i click the drop down come down to more commands or in this case you can right click customize quick access toolbar in both cases it opens up our excel options and has the correct quick access toolbar selected on the left now by default it shows a list of popular commands and that's not what we want so in choose command from we select the drop down and we want all commands we could select commands not in ribbon but i like all commands because then you have the power to look through this list select an item add it to your quick access toolbar now the one we want is and i'm going to click somewhere in the list and type the letter f that jumps to the f's and we're going to look for form and sure enough there it is this is a beautiful feature and i don't know why they don't have it for us in the ribbon tab but nevertheless we click add by the way you can go through this list and select all your favorite items add them over here and then they're easy to access in the toolbar now we click ok and there's our form button anytime we want to enter records we simply click the form now we can click new so we'll enter the id and use the tab key the first name is gigi tab last name is fam tab the higher date and here's a cool thing in this dialog box if you know the keyboard shortcut for today's date it's ctrl semicolon it works right in this dialog box tab 71 000 tab we enter our telephone number tab notice we're entering it without any of the formatting that'll show up later and then the email now you can click new record and instantly look at that it's been added to our excel table with all of the correct formatting we can also when we get to the end of a record hit tab and because new is selected i can hit enter and when i'm done i click close and just like that the last record has been entered all right so when you create a table like this loosely called a database field names at the top add some formatting add the first record with the correct number formatting then ctrl t to convert it to an excel table and then add your form button to your quick access toolbar alright if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos to come from excel is fun and if you want to learn more cool tricks about excel here's some cool videos

Channel: ExcelIsFun

Views: 93,196

Rating: 4.940351 out of 5

Keywords: Excel, Highline College, Mike Girvin, excelisfun, excel is fun, excel fun, excel magic, Excel Formulas, Excel Functions, formulas and functions, Excel Magic Trick 1690, EMT, EMT 1690, Excel Magic Trick 184, Create Database in Excel, Data Form, Excel Form, Excel Table to store data, Step up Database in Excel, Customize Quick Access Toolbar, Add form button to toolbar, How to create data entry form, how to create database in Excel, Excel Database, Excel Form for database

Id: 0dJFkqzxlR4

Length: 8min 7sec (487 seconds)

Published: Mon Sep 14 2020