I was wondering, is it actually possible to normalize (1NF, 2NF, or 3NF) spreadsheets (even with the help of vba) or is that something that can only be achieved in a program like MS Access?
We don't believe in applications at work so a lot of things are done through spreadsheets. I've tried my best but of course there is still excessive repeating data.
I have heard of PowerQuery and PowerPivot however these are still alien to me.
One other thing, a lot of my spreadsheets have the same repetitive tables (ie: in Accounts Payable, the Lease, Prepaid, Projects spreadsheets all have the same GL_Account_No and VendorTable) so when we add/change a Vendor, then I have to change it across multiple spreadsheets. I figure I could just make a MasterTables spreadsheet that these other spreadsheets externally link to but I just don't like the idea of linking to spreadsheets (especially when end-users are ignorant and could mess things up). So I was wondering if there are "best practices" for things like this?
Yes, you can approximate normalization in Excel with tools like PowerQuery, PowerPivot, or master lookup tables, but it's like trying to move the contents of your house in a convertible instead of a moving truck. It can be done, but it's awkward, fragile, and easy to break. Excel just isn't designed for relational data. If you really need proper normalization with enforced relationships and easy updates, that's a job for Access.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Excel Forum.
