Excel Magic Trick #184: Setup Database in Excel

[splinter328]

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.

[ExcelIsFun]

@splinter328 , I am glad that the video and my mistakes help!!

[jabunk21]

TY my fella

[junseobchoi]

nice cool tips thx :D

[ExcelIsFun]

@junseobchoi , u r welcome!

[JRHartly1984]

What if you have millions of data sets which you need to sort according to a particular field?

[eddimull]

Hahaha - I laughed at "Radcoolinator"

[ExcelIsFun]

Cool!!!

Laughing and Excel go together well!

[goodtimegwyn]

THANK YOU, I HAVE TO TEACH THIS TO MY SEN PUPILS, AND WAS TOTALLY INEPT AT EXCEL MYSELF. THE TRICK IS TO KEEP ONE STEP AHEAD OF THE PUPILS, AND SINCE I RARELY USE THIS PROGRAM - I FORGET. SO THANKS.

[ExcelIsFun]

You are welcome!!

[sali8816]

Thank you so much for this trick. Could you please share any trick on How to create a leave record of employees which contains their Annual Leave, Casual Leaves & Sick Leaves.. please give me your email address so that I can share some email directly with you... Your co-operation will be highly appreciated.

[sali8816]

Thank you soo much for this trick. Could you please share any trick on How to create a leave record of empolyees which contains their Annual Leave, Casual Leaves & Sick Leaves.. please give me your email address so that I can share some email directly with you... Your co-operation will be highly appreciated...

Regards,

Haider Ali

[dowesun]

Very helpful and straightforward. Your enthusiasm is a plus too =) Thanks a bunch!

[ExcelIsFun]

You are welcome!

[bhasher03]

this helps me ..simple but useful thank you

[ExcelIsFun]

You are weclome!

[sali8816]

@ExcelIsFun Hi, Could you please share any trick on How to create a leave record of employees which contains their annual , Causal and sick leaves.

[testing4echo]

I"m using Miscrosoft Office for Mac, 28 and unfortunately my prompts are different :(

[ExcelIsFun]

I am sorry about that - but I have not used a MAC since 1995 - back when the business world forced me to switch.

Macs are better, but in the business world... :(

[testing4echo]

@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.

[ExcelIsFun]

I am happy that it helps!

[testing4echo]

Thank you!!!

[ExcelIsFun]

You are welcome!

[kahonski]

Is there a function in excel 2007 to automatically add a product code to a row, like a product id? I tried the Row function but it changes every time I delete older records in the list. Thanks and great videos!

[tcullen1493]

Hello, I like the way you treat the subject and make it so informative.

I wish I could find one that deals with converting XL97 linked data bases using the database wizard to my XL2010 version. I have 600 records created using formulae and the wizard in 97 but no equivalent in 2010. Do I stay with XL97?

[ExcelIsFun]

I do not know the answer to that one. However someone at this amazing Excel question site might know:

mrexcel[dot]com/forum

Let me know how it works out. Or better yet send me the link to your post via YouTube e-mail.

[ExcelIsFun]

Ctrl + L does it is 2003.

Or; Data menu, List, Create List.

[ExcelIsFun]

What's not working?

[PookieDoRight]

Love your videos! I have a drop down list of team numbers. I want to be able to choose a team and have all team members associated with that team number populate. Can you help?

[ExcelIsFun]

Sure, search for this video and then watch:

Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down

[PookieDoRight]

Love your videos! I have a drop down list of team numbers. I want to be able to choose a team and have all team members associated with that team number populate. Can you help?

[tauhinu0519]

Great video - thanks

Can you tell me - if one of my columns is emails - how do I merge that email database to my email and then broadcast to my customers. I'm currently cutting and pasting - and its painful!!!!!!

[ExcelIsFun]

Probably it would involve a mail merge. I do not have a mail merge video, but perhaps I will do one soon...

[MrJy93]

How do you make your column names float down the data base so you know where data belongs? I mean how do you keep it straight when you can no longer see the column names?

Thanks.

[ExcelIsFun]

In Excel 2007 and 2010, when you scroll down, the column headers automatically change to reflect field names. In earlier versions you had to use Freeze Pane. See this video:

Excel Magic Trick 496: Attendance Sheet with Freeze Pane, IF & SUM functions, Custom Date Formatting

[snomom713]

Hi not sure what it is called but I want to know how to be able to put a item # in a cell & have it put a description of that item in the next cell & then the price in another cell. Is this possible 7 what is it called?

[ExcelIsFun]

Use VLOOKUP. See these video titles:

Excel Magic Trick 334: VLOOKUP & Data Validation for Invoice

Highline Excel Class 07: VLOOKUP function formula 7 Examples

[caballero4321]

Hi,

how can i create a data filter in one sheet which also filters data in a different sheet??

please help me on this...

thank you in advance...

[ExcelIsFun]

Serach for:

Excel Advanced Filter Tricks

And click on the link for Playlists (24 videos).

[DjinnMstr]

6:52 lol

[ExcelIsFun]

I guess it is sort of funny! :) Lucky there is usually a way to un-do in Excel!

[Snoofers2]

Hey i am just wondering if i have a large amount of data is it possible to have it set up so there is like a welcome screen with say an empty field that says for example school name which i can type the school name in and it will search my entire database for the said name and show me the results?

[ExcelIsFun]

If you just want to find, click in table and use Ctrl + F (Find) then you can go to each record in the table.

If you want to extract records with a criteria, go to my excelisfun channel and watch the video that shows how to serach for videos and playlists, then go to the playlist on exracting records (there are many cool ways to do it)

If you want a really fancy method, post a question to the Mr Excel Message Board and ask for some VBA code to do it.

[6243313]

anyway to search the data base if you have a long list

[ExcelIsFun]

Highlight list and use Ctrl + F to open Find

[tarbrushmonkey]

hello there, i am creating an ebay shop selling car parts but i need a databasem, to keep car infor when they come into the scrapyard. just wondering if its possible. i needd to search quickly aswell

[ExcelIsFun]

Dear tarbrushmonkey,

This video shows how. Here is another video title that may help:

Excel Magic Trick #130: 3 Dimensional Database in Excel

Otherwise, you sould just use a Database (like Access), which I do not have videos for.

--excelisfun

[beokuf]

This makes learning Excel fun!

[ExcelIsFun]

Dear beokuf,

EXCELlent!!!

--excelisfun

[adambooma]

hello hello,

i am in Australia and have been watching quite a few of your lessons. thanks so much for the help

cheers

adam

[ExcelIsFun]

Dear adambooma,

You are welcome!

--excelisfun

[beaniehoke1]

I watched your video number 184 on the "list" and "table" commands. Yes, these are dynamic. I liked the video. I also inserted a row in the middle of the table and the table automatically updated everything I had to give this video five stars.

[ExcelIsFun]

Dear beaniehoke1,

I am glad that the videos are helpful! I love the dynamic range aspect of tables too!

--excelisfun

[Mickoy88]

Great!!

[ExcelIsFun]

Dear Mickoy88,

I am glad that you like it!

--excelisfun

[rdlastfm]

thanx, is it possible to have an entering row always on the top of the table not vice versa?

[ExcelIsFun]

Dear rdlastfm,

I do not know how to do that.

--excelisfun

[CADwannabe]

Love it!

[ExcelIsFun]

Dear CADwannabe,

I am glad that it is helpful!

--excelisfun

[CADwannabe]

Thank you for what you do!!! You have made it easy for me to decide to continue to learn the Excel. I was following as stated in the video and even started at the last phone number to start B14, I get a red flag in the upper left hand corner and wants me to choose one option. I chose Update cell formula. How can I change this?

Thank you for ALL you do!

[ExcelIsFun]

Dear CADwannabe,

Can you send me your workbook at:

excelisfun at gmail

Then I can take a look.

--excelisfun

[planiolro]

First of all I want to thank you for your quick replay. As I saw the video(Excel Magic trick 36) I noticed as square brackets are used besides the tables column names there are other thinks that are displayed such as: #All, #Data, #Headers, #Totals, #This row. How can we use these things? Did you touch this subject in another video?

[planiolro]

You mentioned something about the advantages of tables when we use formulas in excel. I know that in one of your videos this subject was touched. Due to the fact that you have hundreds of videos (many thanks for that) I do not remember which video is(give me a hint). However one of your next videos can tackle this topic only! Thank you for your videos!

[ExcelIsFun]

Dear planiolro,

Watch these:

Excel 2007 Magic Trick #36: NEW TABLE FEATURE: Dynamic Range

and

Excel 2007 Magic Trick #36.5 (Part 2): Dynamic Range

you can also do dynamic range without the Table Feature (watch this one):

YTLE#105: Excel OFFSET for Dynamic Range in Function

--excelisfun

[ExcelIsFun]

Dear hlee200,

EXCELlent!!!!

--excelisfun

[hlee200]

Another great lesson.