Can Excel emulate a database?

[JenniferMurphy]

I have a number of "items" that I want to rate and track. One example is food. The minisheet below illustrates a simplified version of the sheet I am currnetly using to track vrious types of food that I buy.

The table on the left (TblRtgs) contains a record with a rating (0-100) for each time I try any of these food products. The table on the right (TblProds) contains a record for each product with the average of the ratings.

This works Ok, but I'd like to be able to do more, such as track the prices, the stores where the items were purchased, and calculate moving averages.

Is what I have the best way to do this? Is there a better way that will provide more options? Or should I move it all to a dataase?

Product comparison, Mr Excel.xlsx
	B	C	D	E	F	G	H	I	J
4	Date	Product	Type	Rating		Product	Type	Number	Avg Rtg
5	7/20/23	D	Soup	65		E	Bacon	1	85
6	7/18/23	B	Bacon	72		B	Bacon	2	75
7	6/09/23	E	Bacon	85		C	Cereal	3	90
8	4/10/23	C	Cereal	91		A	Soup	2	87
9	1/30/23	B	Bacon	77		D	Soup	2	63
10	12/14/22	A	Soup	85
11	9/14/22	A	Soup	89
12	7/07/22	C	Cereal	88
13	6/08/22	D	Soup	60
14	4/16/22	C	Cereal	92
Mr Excel

Cell Formulas
Range	Formula
I5:I9	I5	=COUNTIFS(TblRtgs[Product],[@Product])
J5:J9	J5	=AVERAGEIFS(TblRtgs[Rating],TblRtgs[Product],[@Product])
D5:D14	D5	=XLOOKUP([@Product],TblProds[Product],TblProds[Type])

Product comparison, Mr Excel.xlsx
	M
11
Mr Excel

Thanks

 

[Legacy 143009]

It is contraversial. In my opinon it is a very subjective issue. One objective fact is, Excel is not a database and never should be used as a database.

But in practice, if you would have not more than, per say, 10.000 records then why not to use?

I know reputable R&D companies using an Excel file as product tree.

In my opinion, you are on the right path. If it fits your needs then it is unnecessary to invest for a much more complicated system.

If you would have 1.000.000 million records in the future and the file starts to become bulky, then you can look for database solutions. There are many ways to migrate Excel tables to SQL.

 

[JenniferMurphy]

It is contraversial. In my opinon it is a very subjective issue. One objective fact is, Excel is not a database and never should be used as a database.

But in practice, if you would have not more than, per say, 10.000 records then why not to use?

I know reputable R&D companies using an Excel file as product tree.

In my opinion, you are on the right path. If it fits your needs then it is unnecessary to invest for a much more complicated system.

If you would have 1.000.000 million records in the future and the file starts to become bulky, then you can look for database solutions. There are many ways to migrate Excel tables to SQL.

Thanks. I'll never have more than a few hundred records, let alone 10,000. It's not the number of records that concerns me, but the existence of multiple values and sub-values and the queries I might want. In the food example, for instance, one "item", such as Chicken Noodle Soup, might have several brands (Campbell's, Progresso, ???) and be purchased at several different stores at different prices. For frozen dinners, I might want to keep track of whether I cooked them in the oven or microwave and for how long. Setting all that up in Excel seems like it might be an endless pain in the *ss. In Access, once I get the tables and the relationships set up, all kinds of derivative data would be easier to obtain.

 

[Legacy 143009]

Thanks. I'll never have more than a few hundred records, let alone 10,000. It's not the number of records that concerns me, but the existence of multiple values and sub-values and the queries I might want. In the food example, for instance, one "item", such as Chicken Noodle Soup, might have several brands (Campbell's, Progresso, ???) and be purchased at several different stores at different prices. For frozen dinners, I might want to keep track of whether I cooked them in the oven or microwave and for how long. Setting all that up in Excel seems like it might be an endless pain in the *ss. In Access, once I get the tables and the relationships set up, all kinds of derivative data would be easier to obtain.

You are right. Access is the right tool for the job. I thought that we are speaking about Excel in particular. You are describing exactly a data table structure.
When I was running my own company I ran into same situation. I wanted to track the tickets taht I sold. I created a clients table, then a purchases table, etc. I first learned how to do this in Access. Then I transferred all the tables SQL and I wrote my own CRM application.
Short answer, yess.. Your data model exactly looks like thing that must be archieved in Access.