Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 'database' (Excel 2003)

    I have used Access to develop databases in the past, but now I need to use Excel. Where can I find information that will teach me how to use the 'database' functions in excel - how to set up, how to access the data, etc.

    I have been doing some searching and can't seem to find anything that covers the basics. Even in Excel Help... I must not be using the correct terminology or something...

    Any information would be helpful.

    Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 'database' (Excel 2003)

    This is a pretty broad topic. You might just try googling on something like:
    database excel tutorial

    An excel worksheet is akin to an Access Table. (though it can be less strict in structure).

    XL Pivot tables are like access crosstab queries.

    XL has "D-" functions for working with data tables, it also has features to filter (autofilter and advanced filter depending on your needs).

    If you have much more specific questions, we could provide more details in our answers.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 'database' (Excel 2003)

    If your question is really only dealing with the literal "database-functions" (DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP) in excel, you can see an example by looking in help under "daverage" (at least in XL2002 there is a detailed example, I presume XL2003 has something similar).

    Steve

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 'database' (Excel 2003)

    Steve
    I guess my question is vague because I'm not sure where to even begin. I think I'm supposed to begin with an excel worksheet for the data - like a table in Access, that the row headings are like the fields. Other than that, I don't know what options I have. I also don't know what anything is "called" so I don't know what to ask for in help. I will check out Pivot tables - those seem to be key to manipulating the data. I will also look at D* functions. I don't know how an excel database functions.. how to get data in - how to report it, etc. Evidently I can create input forms?? How will I indicate which record to change or that this is a new record - what's involved in adding records? That's why I was hoping there was some kind of basic 'how to' and 'features list'. Thanks for the start - I will take your advise and check out the internet (google) for information.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 'database' (Excel 2003)

    XL is a lot less "structured" than Access is and the user has more ability to add and change things that Access prevents.

    If you want a "real database" and can use access I would use access, it has built-in features to control data that you would have to code yourself in excel.

    XL is a much simpler database program than access. You can create forms in xl, but typically you give people access to the sheet (table) itself, which one tends to avoid in access.

    Look at the articles at Bristol University - UBIS - List of documents by category under spreadsheet. they have one on "Simple Database use"

    The "table" in XL is one size: 256 fields (columns) and 65536 records (rows) if you have no header label(s). You can add data to anyone of the entries. thus you can add new rocords, change records, add new columns just by selecting the cell and editing it.

    Steve

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 'database' (Excel 2003)

    Steve
    I would love to use Access but the 'powers that be' say it must be done in excel.
    All the information you've given me is new to me so thanks again for getting me started.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 'database' (Excel 2003)


  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 'database' (Excel 2003)

    Deb
    Hans should be able to cast more light on this; But I have reservations with the concept of expecting Excel to replace Access particularly when dealing with text fields. If my understanding is correct, there are text string limitations in excel that the user can apparently disregard for a while then after some operation Excel will truncate the text string.
    Somebody please tell me that I've got this wrong.
    Regards
    Don

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Excel 'database' (Excel 2003)

    Deb,

    2 years ago, I was teaching an Excel class and we were going to talk about Excel's DB capabilities. To get some ideas of Access vs Excel, I posted a question on here asking for comparative info with respect to building DBs. See <post#=355780>post 355780</post#>.

    If the powers to be say use Excel, you don't have much choice. But maybe some of the info will help.

    Fred

  10. #10
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 'database' (Excel 2003)

    Deb

    Just a couple other thoughts:

    If you are familiar with Access, you might try importing data from an Access table or query into an Excel table
    using Data>Get External Data.

    Data in an Access Table can also be manipulated (queried, deleted, updated etc) from inside Excel by using
    ADO commands via VBA in macros.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •