The ability create a relational database in Excel with vlookups and hlookups, then capture it all into a macro is amazing.
I've really enjoyed using Excel as a Postgres frontend, with a real Postgres DB instance handling data, and then using the report functionality to dump to Word.
While a pro reporting engine and cutting out MS Office altogether would be a better longterm solution, it is hard to beat for quick & dirty results.
Do yourself a favour and ditch vlookup and hlookup in favour of the recently introduced xlookup, which even obsoletes index/match !
I try to keep my exploratory joins out of Excel, but I admit that I often don't resist the immediacy of Excel's poor man's joins located right where I need them.
The commercial devart plugin [3] looks pretty neat too but I haven't used it yet
I've also tried the JDBC connectivity option too [4], but with some different use-cases in mind for Postgres (not about Excel)
[1] https://datacornering.com/how-to-connect-to-postgresql-datab...
[2] https://www.postgresql.org/download/products/2-drivers-and-i...
With just the tidyverse library (which includes dplyr), R can be very useful in a data analysis pipeline. It is great for data cleaning and aggregation, especially when a process needs to be done multiple times. It is much faster than excel/power query. I am an accountant in SaaS and spend a lot of my day waiting for excel/powerbi automations to refresh. Similar solutions in R/sql/python would be nearly instant. Also excel/powerbi automations are a bitch to troubleshoot, and are unnecessarily complex.
When following tidy principles, a framework designed by the tidyverse dev Hadley Wickham, R code can be very easy to interpret, similar to SQL. Additionally the R community has made libraries for everything, and I consider R a great general purpose language as well.
