HomeНаука и техникаRelated VideosMore From: ExcelIsFun

Excel Magic Trick #184: Setup Database in Excel

1658 ratings | 952928 views
See how to create a simple database in Excel using the List or Table feature. A simple database can be created in Excel using the Excel 2003 "List" feature or the Excel 2007 "Table" feature. Fields names must be in first row (no blanks). Records are in rows (no blanks). Other data in the sheet cannot be next to the Table/List (at least one blank row or column between other data and the Table/List Keyboard shortcuts: Excel 2003 List: Ctrl + L. Excel 2007 Table: Ctrl + T. The ranges are dynamic: formulas, pivot tables, charts will all automatically update
Html code for embedding videos on your blog
Text Comments (157)
Yas Hussein (5 months ago)
Seriously !! a database?.. This is only a data without any base
1smendez (5 months ago)
I'm watching in 2018, too. Better late than never LOL
NEED TO BUDGET (6 months ago)
This is not what I was expecting. Doesn’t seem like a database to me. Thanks for your time
Miguel Barajas (7 months ago)
wow a 4:3 aspect ratio...
Igor Fomenko (8 months ago)
Thanks, much better than access at first glance
thank for the lesson sir. I wonder if in 2003 list can be treat like a table for accesing sql query. Is there a way to assign a name to that list?
Chua Hello (9 months ago)
This ain't database .
Hana Kazumi (10 months ago)
I prefer to use ms word just to do simple data like this
Maskmello (11 months ago)
who is watching this in 2018?? I am here.... LOL
wboni001 (4 months ago)
I'm here 2019 and it does not work anymore. Unfortunately. Off to me youtube searching lol
Kirandeep Choudhry (5 months ago)
No, men some of his video are very good just not make fun of anyone plz don't mind and don't reply plz
周眉宁 (8 months ago)
me too .lol
streeteats (1 year ago)
Thanks for the easy to follow, useful lesson.
ExcelIsFun (1 year ago)
You are welcome!
Paul S (1 year ago)
Really helpful. Thank you. I'm glad that you kept in the mistakes and how you corrected them. That was very useful. It's the type of mistake anyone could make but you went straight to the fix and made it look so easy. Great stuff.
GAURAV shukla (1 year ago)
GAURAV shukla (1 year ago)
love you
PAIN - (1 year ago)
fuck u
Fejz Rexhepi (1 year ago)
can anyone tell me how numbers are held, eg 300 workers in 8 columns with and in 300 rows how to collect and verify on two pages of the same? not personally but collectively, thank you for your help and understanding
Spaclintor (1 year ago)
This is not a database. That's a table. This is also why they call it table. In a database you can have multiple tables and define relations.
Julian Gonzales (1 year ago)
sioux radcoolinator lol what
Londynka 7 (1 year ago)
Unfortunately this already too advanced for me. I need to start from basics :(
michael Evans (1 year ago)
your lessons are cool..thank you
Natasha Allison (1 year ago)
Do you have any suggestions on how to create a table in bookings reservations? Thanks!
mjuni francis (1 year ago)
Jose Lima (1 year ago)
I need to create an overtime excel table, for my associates, but I just don't know how to do it.  Can someone help me out?
Jose Lima (1 year ago)
Please, help me to create a table in excel to rotate my associates as overtime is available.  Someone can help me guide this table?  Thanks so very much.
Michael Jordan (1 year ago)
Thank you
Becky Kosher (1 year ago)
Simple, easy explanation! Thanks
i could not see your explanation as the table was covered by an Amazon ad which i could not delete
Hannah Hill (2 years ago)
Under special I do not have the options for zip codes or phone numbers. I am using Office 365 2016 so they may have changed it.
E Dunlap (2 years ago)
Does this work with the current edition of Excel?
E Dunlap (2 years ago)
Thanks! I do this at work all. the. time. And I need all the shortcuts I can get!
ExcelIsFun (2 years ago)
Yes : )
MJ Jackson (2 years ago)
@2:35 What if there isn't an option for Special?
OldManWilkins (2 years ago)
What i would like is for the list to be hidden and have a search box only then display what was searched, but Im guessing thats not possible with excel correct? ..
OldManWilkins (2 years ago)
Thanks I will look into that.
Pet My Bear (2 years ago)
You can lock and hide a sheet and use data validation and VLOOKUPs to search for relevant data.
Jet lag (3 years ago)
I am trying to make a column like 100a 100b 101a 101b 102a 102b etc... any clue on how to make it? thanks
Bar D (1 year ago)
need to use programming
Marie Schleimer (3 years ago)
I have made the table the same way you did the only issue is at the end you press "tab" to create a new input row in the table that keeps the same formatting. I didn't make this part work. Any chance to re-explain that part. I am using excel 2016 on windows.
John Mama (3 years ago)
that great. I like it, it too usefull
brently flowers (3 years ago)
Thank you for teaching me how to create a data base. JESUS CHRIST and GOD bless you and everyone else in existence. Thank you .
Cabo Pedregal Hotel (3 years ago)
I was hoping to find how to create a database for hotel reservations. I don't need an entire wiz/bang set-up. I'm just looking to track # of guests in hotel so maids can predict how many ladies we need to clean rooms... a projected number based on reservations. Do you have a video that would help me with that? I didn't want to look through 184 of them lol. Appreciate any thoughts on the matter.
NEED TO BUDGET (6 months ago)
Did you ever get this answered/solved?
Bar D (1 year ago)
Open excel file > when a guest enters increase a cell number +1 > when a guest leaves decrease the cell number -1 > now you know how many guests there are in your hotel at any point in time
afzal jahangir (3 years ago)
ripudaman singh narang (2 years ago)
I'm also facing the same problem
Dan Lazar (3 years ago)
How can be added the new line on top of the other instead below?
Dan Cacovean (3 years ago)
great stuff!!!
james bong (4 years ago)
use a pop filter on your mic
Riteish Kotian (4 years ago)
I'm facing problem with the audio. I tried watching other random videos on youtube and the audio worked just fine. Please help. I using chrome as my browser.
Leandro Protacio (4 years ago)
hi, i noticed that there are drop down arrows on you database, what are they for? and if you don't mind telling me how to make them work... thanks so much, you've been a lot of help...
Jalal Hindieh (4 years ago)
That is called filter and they will appear automatically on the headers once you choose to turn the data into table, however, if you have data on a sheet and you want to activate a filter on them, just select the data and press (ALT then A Then T) after each other and you shall apply the filter, be aware that the first row of the selected data will be your filter/table header :) 
Shailesh Prashar (4 years ago)
not able to edit phone number .here country code +255 ..try on your excel sheet and kindly assist???
Valarie Hayes (4 years ago)
Im setting up my bank log and your video was SO helpful, however, can you please tell me how to add in the autosum into this table for my balance?  Thanks so much! 
Need For Excel (5 years ago)
I was just revisiting your videos that I used to watch when I was starting off with Excel.. These tricks that you'v taught are just amazing.. Cheers!
ExcelIsFun (5 years ago)
Glad the videos help!
gmshadowtraders (5 years ago)
All those excel shortcuts are really very impressive. Do you know the total number of shortcuts in 2007? It's got to be in the hundreds. It would be really useful to get a full list of them. Great work! Subscribed.
Adolfo Hernandez (5 years ago)
Hello, I've been looking on your videos (there's a ton) but I can't find the one that explains how to insert formulas inside databases. In one of your videos you are using a database where when you enter a name there's an arrow in each cell where you can choose which name is entered in the cell trough a drop down. Also another column is filled (I suppose with a vlookup) with the city corresponding to the person what was entered. Which from your videos explain how to do all of that? Thanks a lot for your videos, they are AWESOME, you are the boss!
MNY (5 years ago)
than you very match it's really help but can you tell me pleas how to fix the qty for every type
ExcelIsFun (5 years ago)
Try VLOOKUP. Here is the video title to search for: VLOOKUP Function Beginner to Advanced 26 Examples: How To Use Excel VLOOKUP Function 
ExcelIsFun (5 years ago)
Glad you like them!
WayneTai (5 years ago)
easily the best excel videos tutorials here on youtube.
iamowe1992 (5 years ago)
I need to have the user enter information into a worksheet on another workbook (I already know how to do 3-D references), but I need for each customer that inputs their information after they are done it will jump to the next line on the table for the next user to enter their information.
John O'Loughlin (5 years ago)
I am using Excel V-2007, the Option to set phone numbers is only available when Locale is set to US? Is there any way to have these options when Locale is set to UK? Thanks, John.
Nzai Mei (5 years ago)
Hi, sir hope you will me with my assignment, my teacher assigned me to do a simple data identifier that will identify what types of data you've entered, for example if i type in cell 4 "apple" it will automatically identify in cell 5 that apple is a "fruits". please help me, have a nice day
ExcelIsFun (6 years ago)
I am not sure why - try this Excel question site: mrexccel [dot] com/forum
Simon Hughes (6 years ago)
Ctrl +1 opens Format Cells dialog box, "Special" is in the Number tab between text and custom and this is where you formatted to accomodate phone numbers. My version has no entries at all.
ExcelIsFun (6 years ago)
What dialog box are you trying to open? I do not know what this means: "Ctrl + 1 > Special > no options". I can't find a "Special" in the Format Cells Dialog box.
Simon Hughes (6 years ago)
Typo - sorry about that, I meant Ctrl + 1 > Special > no options.
ExcelIsFun (6 years ago)
What dialog box are you trying to open? Ctrl + F1 is the toggle to show and hide the Ribbon. Ctrl + 1 opens the Format Cells dialog box. Alt + F1 creates the default chart on the active sheet. F5 opens GoTo dialog box, which has a Special button.
Simon Hughes (6 years ago)
Just tried the special dialog box (Control +F1 > Special) and there are no entries; any idea why this is? I am using Excel 2010
Sabra A (6 years ago)
i have microsoft excel 2007. it does not have that special features of phone. It does but it does not put brackets on the phone numbers. and also about Ctrl+Shift+4 does not help. got the currency sign only on the first row, and it does not automatically format for the second row!
Chris Wisselo (6 years ago)
=IF(AND(MASTER!$E223="Active",MASTER!$F223="Eligible"),MASTER!$B223,"") This formula checks two statuses on the master sheet: If the employee is "active" and in the group 'sales". If both arguments return a TRUE, the name of the employee is transported to the sheet. Hope this helps! Chris
yusak santoso (6 years ago)
Hi Sir, can we fill the data base by multiple user? So, if in the office, we have Admin 1, Admin 2, Manager, Sales, etc.. Can they fill this data base together online? Or is there any software to do that? Please your kindly advice. Thx a lot.
Sir, How to make Statement. Opening Balance/ Debit Amount / Credit Amount / Closing Balance. in Excel 2007 or 2010. Sir I really want to know. Thanks
Alex Miller (6 years ago)
You can actually use an Excel style database product like Ragic, if you want to work on it online and need real, database-like functions.
ExcelIsFun (6 years ago)
No template.
streetfleet (6 years ago)
helo sir do you have inventory template for spare parts inventory?
Jane Taylor (6 years ago)
Hey there, how come when I go into the 'format cells' 'special' area, the 'type:' area is blank ie: I have no phone number option here. How can I add this in??? Thanks.
ExcelIsFun (6 years ago)
For back and forth dialog to get Excel solutions, try posting question to THE best Excel question site: mrexcel [dot] com/forum
Mary Jane Flowers (6 years ago)
I need help....anyway you can tell me what video to watch to get help I have a master spread sheet that I would like to have information moved to another page if it is equal to a certain value/text. I know how to extract the info but I would rather them be linked. Anyway to do this?
doksan (6 years ago)
Hello, i am new to excel. Can you please tell me if i can do these? 300 employees (their id numbers), i want to assign their pictures. One empty excel sheet just the bar for you to type the id number then press or click enter, picture will pop up, type another id, another picture pop up Can you teach me or tell me how do i search this feature on the YouTube or Google? Thank you very much
Zulema Rodriguez (6 years ago)
I have seen many of your videos and ALL of them were very helpful! I was wondering if any of these fields could be exported onto a form in order to print out, but be able to pick out the rows to be exported?
ExcelIsFun (6 years ago)
I do not have a trick for that. Try THE best Excel question site: mrexcel [dot] com/forum
joshithegreat (6 years ago)
I saw many of your wonderful tricks in excel. But can't find the one that I am looking for . Could you please refer me the video showing how to over come conversion of "2877E-0300 " to "2.877E-297" . Data exported from a report program into excel and gets converted. Is any trick to overcome that. Any help is appreciable.Thanks
ExcelIsFun (6 years ago)
I am glad that it helped!
akila yaro (6 years ago)
ExcelIsFun (6 years ago)
Yes, you can just click in cell below table and start typing - then a new record will be added. Same for columns too.
ExcelIsFun (6 years ago)
You are welcome!
Anissa Alexander (6 years ago)
That was pretty. I am going to use it to keep track of my school teams information! Thanks.
ExcelIsFun (6 years ago)
Cool! I am glad that you like them!
Dinesh Kumar Takyar (6 years ago)
Excellent! Your videos truly give the feeling 'Excel is fun!'
ExcelIsFun (6 years ago)
I am glad that you like it!
Love Fleurette (6 years ago)
i tried ctrl shift 4 and it didn't work but I am using windows 2010
Alimon Pito (7 years ago)
sir,can i link my excel sheet with mysql table??
ExcelIsFun (7 years ago)
I am glad that the videos help!
garyoptica (7 years ago)
The best Excel teacher in the world :-)
newnoona (7 years ago)
@ExcelIsFun Wow, as simple as that. It worked, of course :) Thank you for the prompt response.
ExcelIsFun (7 years ago)
@newnoona , instead of Enter, use Alt + Enter.
newnoona (7 years ago)
This is great, thank you. Question: is there a way to make a cell display multiple rows? I have to enter a database with a lot of words in it basically, and I imagine like word docs, if I press "enter" it will move me a row down inside the same cell. But what it does is it takes me down one cell. Is there a way around this? Thanks.
ExcelIsFun (7 years ago)
@splinter328 , I am glad that the video and my mistakes help!!
Daniel N Rob (7 years ago)
Great tutorial you saved me from hours of headaches! Dunno if anyone mentioned the fact that you made mistakes during your tutorial (and then quickly corrected them) but I appreciate that! You showed how to find out what was done wrong and how to fix it.
JR Neto (7 years ago)
TY my fella
ExcelIsFun (7 years ago)
@junseobchoi , u r welcome!
Junseob Choi (7 years ago)
nice cool tips thx :D
JRHartly1984 (7 years ago)
What if you have millions of data sets which you need to sort according to a particular field?
ExcelIsFun (8 years ago)
Cool!!! Laughing and Excel go together well!
eddimull (8 years ago)
Hahaha - I laughed at "Radcoolinator"
ExcelIsFun (8 years ago)
You are welcome!!
ExcelIsFun (8 years ago)
You are welcome!
dowesun (8 years ago)
Very helpful and straightforward. Your enthusiasm is a plus too =) Thanks a bunch!
ExcelIsFun (8 years ago)
You are weclome!
dar steffan (8 years ago)
this helps me ..simple but useful thank you
ExcelIsFun (8 years ago)
I am happy that it helps!
testing4echo (8 years ago)
@ExcelIsFun So true. You're very kind to reply. I still use PCs for business (and to serve as IT support for my non-techie family members, lol)- so it's still good to know these things.Thanks for your videos.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.