Hacker News Reader

Comment by towawy | Hacker News Reader

Microsoft Excel. Manipulating tens or hundreds of thousands of rows, including cross-referencing across tables, is just so satisfying and much faster than doing it with a DB or code. Alternative spreadsheet software do some things better, but they don't come close as a complete package, I don't regret paying for it myself for the first time in my life. It such a life saver in a pinch.

Obligatory Spolsky intro to Excel: https://www.youtube.com/watch?v=0nbkaYsR94c

View on HN

Excel gets my vote for one of the top 10 software programs ever. It's crazy how useful it is.

Excel, the second best program for any task. ;0)

The really impressive part is that it’s Actually the second best program for every task.

As a Linux user, using Excel via the web interface or via a virtual machine is not so satisfying nor much faster than alternatives.

Yeah, as someone who doesn't pay for MS, I'm basically using a combination of Calc and Gnumeric, because G can open larger files (handled larger files than Excel in fact), but Calc has more features, and it's more similar to Excel which I was used to. ONLYOFFICE's sheets can also be handy, if MS Excel compatibility is wanted.

Try: visidata on a sqlite file.

Crazy useful.

So useful I like it despite how it always goes out of its way to waste my time by trying to misrepresent a number of different types of strings and numbers as American dates :-|

Dear god, I would use Excel so much more if I could get it to stop mutating my data.

January, February, Maruary

100% in agreement on Excel. Even when coding in Python I frequently save an intermediate file as xlsx to explore/debug, or even load into Tableau for viz.

You should learn R and dplyr ;)

Tell us more - in the context of them being replacements for Excel for his use case.

I love R but can't use it at work :(

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.

Note that different flavours of R have very different performance. 'Base R' is quite slow. But R + data.table is blinding fast. Power Query perforamnce is awful, even compared to base R. Some benchmarks of these plus other data wrangling software (including my own product) at:

https://www.easydatatransform.com/data_wrangling_etl_tools.h...

"Manipulating hundreds of thousands of rows" is exactly where R, dplyr and data.table are great at. I do that on a daily basis.

110% agreement on Excel

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.

I'm curious, how did you establish the Postgres connection?

I used ODBC [1] out of the big list of options [2] which gets a bad rep but worked for my use-cases.

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...

[3] https://www.devart.com/excel-addins/postgresql/

[4] https://jdbc.postgresql.org/

> The ability create a relational database in Excel with vlookups and hlookups,

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.

Took them long enough to add it.

I got to play around with Tableau when I was helping my wife in a collage programming course and though I don't have a current use to justify the significant cost, I must say that the tool was amazingly flexible and easy to use. I'd highly recommend it.

Pssh... adults use JMP ;-): https://www.jmp.com/en_us/software/data-analysis-software.ht...

Trivia: JMP stands for "John's Macintosh Project".

Also, the entire semiconductor industry depends on JMP just like the entire pharmaceutical industry depends on Minitab.

And we go full circle. I learned pandas in 2012 so I don't have to deal with Excel or JMP scripting.

Still use Excel for quick stuff but otherwise it's pandas for me.

The JMP buy page doesn't even give you a price. How expensive is it?

I work at a shop where a few people are JMP users. I think it's a problem when a proprietary app is only available to a handful of users at a site -- they tend to get a lot of work dumped on them, and nobody can use their stuff without going back to them. It's a different experience when a site has a site license.

This isn't specific to JMP of course. A good thing about Excel is that businesses are OK with just paying for "everybody" to have it.

Also, people use Excel for more than analysis. It's also a crude database and platform for creating small "apps" that do things, that are easy to share with others.

Yeah, just yesterday. Hacky output from `nslookup`, not ideal but it’s all we had. Tens of thousands of rows. No IDE with regex capabilities available.

Excel to the rescue! Took me about five minutes to extract exactly what I needed.

If you can use dig output, the next time you need to do something like this you might find 'jc' helpful: https://github.com/kellyjonbrazil/jc

Government laptop. No can use anything that isn’t whitelisted.

This is the beauty of Excel. It’s a powerful text manipulation tool that everyone has.

where can I input regex to excel? I really need that, last time I copied table to nopad++ then copy paste it back.

I didn’t. I pasted the output and munged it with Excel’s trusty text functions.

so in this time, Excel isn't so good...

> "No IDE with regex capabilities available."

If it's Windows there should be PowerShell ISE installed.

I know I should learn it but, I dunno, I just really dislike the PowerShell syntax. I get what they were going for with the Verb-Noun thing but it just reminds me of AppleScript and I can’t stand it.

You don't have to learn or use PowerShell to paste something into the ISE editor and go to 'Edit -> Replace in Script' and do a regex search/replace.

Ahaaa cool tip, that will be useful one day. Thank you.

Often useful CLI tools are linked on HN (like in this thread). And probably if you take the time to learn them or if you integrate them into your automation they are 100% useful.

But, given the problem “I have some unfortunately formatted data which I need to analyze”, there is no better solution than sanitizing it in VS Code and then analyzing it in Excel.

And for me excel wastes 100 hours a month.

I don't have a Windows machine, and I'm having mixed feeling about Excel for Mac — is it as powerful as the Windows version? Are they exactly the same or are there big difference in functionality? The web / Office360 version doesn't seem much better, either though.

Native Excel on macos isn’t bad. I’m on an M1 and it’s slow to start initially, but once loaded it’s fine. Better than any web version.

Reply on news.ycombinator.com