Creating a relational database in Excel

This is a broad question about how to approach the structure of a relational database in Excel. Are there any good resources where I can read / learn from. I'm after creating a database like Access would do with a relationship map, but using Excel. The relationship map in Access is very visual and easy to work with. Is there something within Excel that performs a similar task ?

I'm fairly confident with the nuts and bolts of Excel formulas and pivot tables especially, but I get lost when trying to link data. So, I end up doing a lot of duplication of effort.

Hope someone can help. Merry Christmas all.

Excel is not a database tool. My advice would be to use the right tool for the job: Access.

if you insist on using Excel (and it can be done), then you are going to need to learn a lot of new tricks to make it mimic what Access could do for you behind the scenes.

Thank you Ali, you helped me out on my last post about slicers back in February btw..

I agree with what you say about Access and would love to exploit the power of Access. However, I work in a collaborative environment where spreadsheets are shared minute by minute by colleagues who don't have the skills to understand Access nor do they want to. Using Excel is more accessible to the team.

Thank you again..

Mmm. Not sure I follow your logic here. The whole point about Access is that you create a very user-friendly interface for colleagues to use to add data to your database and to extract the reports they need. They don't need to understand what's going on behind the scenes.

Can you explain the extent of the database functionality that you are wanting to achieve?

So, what I've got is a beast of a set of spreadsheets for a large fit-out project in Europe. I have the following data sets.

List of RoomIds, Names (450 rooms of all types) and Floors (1 to 13)
List of TagIds i.e. Fixtures and Fittings (250 different ones) which include specification information, supplier names
A room schedule which is a list of TagIds allocated to individual RoomId
A manufacturing report which lists the individual components necessary for each TagId along with delivery dates, specification information etc..

Its worked well in so far as I've been able to create pivot tables to summarise the data. But, somehow its all a bit cumbersome and takes me so long to manage it. I'm probably not explaining myself well.

I think I probably need to accept that the spreadsheet I've got works ok. Stop trying to improve it as there is only 2 months left of the project to go before it finishes. Stick with it... Keep it maintained until its all over.

It is certainly not worth the amount of time it might need to streamline it for the time you have left to use it!

Given the description, I would ALWAYS go with a proper database for this sort of thing.

Did you notice that this thread is over three years old?

Thank you Entone, I much appreciate the time you took to reply. I never did simplify this particular project spreadsheet. However, not long afterwards I was introduced to Power Query which was a game changer for me.