Jump to content


Photo

Excel as database


  • Please log in to reply
3 replies to this topic

#1 monq

monq

    Member

  • Members
  • PipPip
  • 11 posts

Posted 07 March 2015 - 10:00 AM

I like the way pivot makes its reports, collect/ manipulate data by CUBE Function.

1. Now because of this feature, can I use excel as a database?

2. How do I manage the database if there are many users who shares information and updates the same tables?

3. How do I protect the data from accidental deletion, corruption, etc. 

4. The problem I also see is the GUI interface. It's kind of tedious inputing data on a spreadsheet. What will I do to make inputing more easy?

 

I appreciate your help.

 

Thanks.

monq



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 07 March 2015 - 10:58 PM

Hi monq,

 

Although Excel is now able to create relationships between tables and extract data from multiple tables simultaneously, it still isn't really appropriate to use it as a database - mostly for the reasons you've listed.

 

To answer your individual points:

  1. I would not recommend using Excel as a database unless your requirements are very simple. If you need more than one table of data, Excel is probably not a good choice.
     
  2. Although Excel spreadsheets can be used by several simultaneous users, any conflicting changes must be resolved manually. For more on this, see the video lesson: Expert Skills Lesson 8-13 Share a workbook on a network.
     
  3. Excel is not capable of enforcing the integrity of data. This means it is very difficult to prevent users from deleting records that are related to other records and corrupting the database.
     
  4. Your users will be limited to entering data using the features available in Excel. These can be quite extensive, but still might not be ideal.

My recommendation would be not to try to use an Excel workbook as a database, as it was never designed for this purpose.  However, you can extract data from a database into an Excel workbook in order to analzye it and create reports.

 

You can see a walkthrough of connecting to an external database in the video lesson: Expert Skills Lesson 5-4 Use an external Pivot Table data source.

 

I hope this answers your questions, but please feel free to reply if anything is still unclear.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 monq

monq

    Member

  • Members
  • PipPip
  • 11 posts

Posted 08 March 2015 - 11:12 PM

Hi Jonathan, 

 

Thank you so much for your detailed reply.

 

Now I know Excel is not intended to handle database.

 

Just a follow-up question, I want to make data encoding simpler. Where can I find lesson/s creating a data entry form? I only know ALT--D-O function can make it appear. But are there other ways?

 

Thank you again,

monq



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 14 March 2015 - 05:14 PM

Hi monq,

 

You can add this feature to the Ribbon or Quick Access Toolbar by customizing it. In this case, you need the Form... command, which can be found in the Commands Not in the Ribbon category.

 

form.png

 

For more in-depth instructions on accessing 'secret' features like this one, see the video and e-book lesson: Expert Skills Lesson 4-16: Use Speak Cells to eliminate data entry errors.

 

Alternatively, you could create a data entry form within Excel and then record a macro to add the data to a table.  For more on this technique, see this thread.

 

I hope this information is useful, and please feel free to reply if I can be of any more assistance with this problem.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users