Is there a good tutorial (book or video series) that can teach me how to make Excel behave like a database?

[JenniferMurphy]

I have a bunch of applications that seem like they are better suited for a database than a spreadsheet where I can normalize the data and queries and forms to manipulate and interrogate the data in pretty much unlimited ways. But several conversations I have read here suggest to me that Excel has features that I am not aware of that can come pretty close to impersonating, emulating, or actually being a database.

I am wondering if there is a really good book or video series that I can get that will teach me how to make Excel function like a database. I am not looking for something that is free.

I would also be interested in any consultants or tutors who could walk me through that process and critique my work.

Thanks for all tips and pointers.

 

[JenniferMurphy]

One of the tutorials on Power Pivot by ExcelIsFun describes how it is like MS-Access tables. So, when you start on the MS-Access path you'll have a head start. I agree with you that adding a little bit more of excel knowledge will serve you quicker than a brand new application. But, MS-Access can be a very powerful addition to your excel toolbox.

Ok. I'll check it out.

 

[awoohaw]

before power query it

Why would anyone want to link Access tables to Excel? What advantages does that offer?

And that brings up another question: Do either Access or Excel allow me to create a form that will run on my iPhone that will have R/W access to the data on my Win 11 laptop?

Excel (and ms-access) should be able to link to many different file structures and sources (SQL-SERVER,MySQL, SAS, Oracle - for some you may need to add drivers).
Importing data into a huge excel workbook would slow down excel. With the interfaces to other data sources you would only get the data you need for particular needs. And reduce file size.
Power Query can do this as well.

 

[Logit]

Jennifer :

The example I posted gives you the ability to search, edit existing records as well as enter or delete. If you are seeking a simpler design that only "enters" data, that too can be provided with much
less required code.

Keep in mind Access will require as much or as little code depending on what you desire the project to do.


Sample code :

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    'Col A = 1; Col B = 2; Col C = 3; Col D = 4; Col E = 5; Col F = 6; Col g = 7, etc. etc.
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Text
    ws.Cells(newRow, 2).Value = Me.txtSurname.Text
    
    'continue the remainder adhering to the pattern seen above.
    
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Simple database download : Internxt Drive – Private & Secure Cloud Storage

 

[JenniferMurphy]

Excel (and ms-access) should be able to link to many different file structures and sources (SQL-SERVER,MySQL, SAS, Oracle - for some you may need to add drivers).
Importing data into a huge excel workbook would slow down excel. With the interfaces to other data sources you would only get the data you need for particular needs. And reduce file size.
Power Query can do this as well.

I don't really have any "huge" data sets -- not by today's standards. A few thousand records would be very large for me.

But are you saying that keeping the entire "huge" "source" data in Access and then having Excel access only as much of that data as it needs for whatever it has been asked to do, could make Excel run faster?

And do you also mean that Power Query can do that with data that is stored in Excel? I would put the "huge" "source" data in one workbook and then use Power Query to access only some of it from a different workbook?

 

[awoohaw]

not sure if only a few thousand records would slow much down. I mean millions. hundreds of thousands.

yes to the 2nd question. and with Power Query those secondary workbooks where your large reference data is do not need to be open. The secondary data could even be csv or txt files.

 

[JenniferMurphy]

not sure if only a few thousand records would slow much down. I mean millions. hundreds of thousands.

yes to the 2nd question. and with Power Query those secondary workbooks where your large reference data is do not need to be open. The secondary data could even be csv or txt files.

Ok, thanks

 

[awoohaw]

Power query playlist:

https://youtube.com/playlist?list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK&si=Bq8h4qm7w8_qx64m

 

[Edgar_]

The advantages you expect to have by making Excel more capable will probably limit what you may want to do with your applications.

But Excel is super flexible, if you don't have large amounts of data, you can do a lot with it. There are workarounds for everything, that can be a good or a bad thing, it depends on who you ask, so let's evaluate that.

Let's say you want to have many users access your workbook, two users are already a lot. You will need to save and close before the next user uses your data. How can you solve that? well, you can export your data to a database system and let Excel become just a front end that retrieves the required data only. That way, any user of the Excel front end will R/W up-to-date data whenever they see fit. Excel can do the reporting just for the user using the data. A database system could do everything though.

So let's say now that you want your data to be in good shape all the time. You want users to enter strings with string columns, numbers in number columns, numbers of a certain length in columns for that type, dates with dates, etc. Clever excel routines and constraints can be put in place. VBA can do wonders here, the worksheet change event can handle all that interaction too. You can add data validation in regular cells and data validation in ListObject cells as well as other kinds of measures. A database system could do everything though, with so much ease. Well, it depends, that will also require forms.

Now let's think for a moment that you expect your data to grow to huge proportions. Power *something* apps can help with that, they can interrogate your data and do some neat stuff with it. In Excel, you can have a lot of data stored, use those tools to get your results. Do keep in mind that sometimes a small amount of data is enough to crash a workbook, if the processing it requires is too much. A lot of Excel workbooks will have a bunch of cells reacting to all sorts of changes, so it's necessary to apply some clever design techniques here. A database system does not need to calculate everything though, you can choose what to calculate, only what's necessary.

How about security? does your data look like it could be sensitive information? Just apply some password and some VBA techniques to keep evil doers from peeking the file. OK, yes, there are a lot of things to remove passwords, bypass VBA protection, etc. You're very exposed in this regard. A database system may offer more security here. It depends on the system though. Don't expect Access to be more secure than Excel. It's not. SQL Server? now we're talking.

Is Excel capable? yes. Is Excel a golden hammer? for many, yes. Can a database system work better than Excel? sure... but it requires some more thought. In the end, what is best? I'd go for learning a database system.

So what if you want to see your data from anywhere, even an iPhone? a web application is better suited for that, but you can employ the central database technique here. You can write from Excel and retrieve the written data from Excel as well. Then, from the smartphone, using a cross-platform technology, you enter a website and the website communicates with the central database and retrieves your data and you'll be able to visualize it from the iPhone. That still requires you to write the web app.

Databases have their advantages over Excel sometimes. But Excel isn't useless either. It can handle smaller tasks fine. With some VBA tricks, it can even do a bit more. But if you're dealing with serious stuff, maybe think about using a database system, it will be harder, but you won't be as limited. Access is a pretty good alternative, if you learn it, it offers a similar user experience to Excel, so it's a great start for someone coming from an Excel background. The only drawbacks of Access are security, visuals and web deployment, you can't deploy an Access app to the internet (but there are workarounds, remote desktops, for example), but Access can communicate with web servers and you can still do a lot of web stuff from it.

 

[JenniferMurphy]

The advantages you expect to have by making Excel more capable will probably limit what you may want to do with your applications.

. . .

Wow. That's a lot to think about. At the moment, I am the only user of any of my data applications. However, I do plan to write an application that can be accessed by dozens of people in our neighborhood. I have it roughed out in Access. My plan was to export some of the resulting the data to a csv file that I could then import into Google Sheets. Then I could authorize the neighbors to view the data and enter their "selections".

 

[Edgar_]

My plan was to export some of the resulting the data to a csv file that I could then import into Google Sheets. Then I could authorize the neighbors to view the data and enter their "selections"

That could work.

For these kinds of scenarios, a web application is the way to go, since you would just give them a URL and they could enter the website, without installing anything, access the data and let them make their selections. Keep in mind that Google has their "Forms" service too. Google also has an online database that Excel can use via HTTP, which is Firebase, so that could also work. You could ask the GPT or me to write a simple app that can store their selections. There are a ton of options right now.

There is also AppSheets or something like that, which works with the Google Sheet API and I don't doubt there are more apps that can also communicate with the same service.