16 ms·

Why do we use R rather than Excel?

241 points by edent 5y ago

Dumblydorr 5y ago
I use R and Excel daily. Excels graphs are simpler to make than a customized GG plot, but an ice cream sandwich is easier than a sundae with whipped cream and sauce and a cherry. Different levels of difficulty and customization for different applications.
fatline 5y ago
The author could have mentioned testing. The reason I use python scripts instead of spreadsheets for important calculations is that I can unit test the logic extensively and be sure that the code works as expected. This also makes sure I don't break stuff when adding functionalities / refactoring code. I would never ever use Excel for something important, unless it's completely trivial (e.g., sum/average values of a column). I assume the same applies to R vs spreadhseets, of course.
throwaway879 5y ago
Worse Is Better™
prionassembly 5y ago
Excel files are shareable and everyone's able to inspect them. (drops mike)
fractal618 5y ago
Use R when getting paid by the hour. Excel when you have dinner plans.
foreigner 5y ago
Wow those reasons don't sound very compelling to me! (I'm a programmer but don't know R.)
- verelo 5y ago
  Honestly i struggle with it. My partner recently wrote an R package (phd biology) and I’m a developer. I had done a bit of R but nothing substantial. By the end of the project i couldn’t think of a compelling reason not to use any other language. The R tooling is clumsy, the language isn’t great…the built in graphs are ok? It feels like the reason it’s popular is mostly just people not wanting to update skills because they’re in a tangential field where this is a solution so it’s not a priority to change.
  - bonoboTP 5y ago
    There are also a lot of niche libraries for various statistical tests and analyses that are used for scientific studies. Especially applied scientists who wouldn't necessarily be able to reimplement the math and are not very familiar with programming rely on it as a workhorse. Things like variants of anova, regressions etc. are often just a single line in R, specify the data, the interaction terms etc and you get a nicely formatted output with everything calculated for you, like goodness of fit, p-values etc. There's a long tail of functionality in there that's hard to match even with the scipy stack or other statistical packages in python. Plotting is also much less of a hassle than with matplotlib. R is more of a free and de-bloated SPSS than an alternative to scipy or excel.
  - JimBlackwood 5y ago
    I use R for work a lot, maybe to give some reasons to use R: 1. The language itself isn’t great, but the tidyverse packages are amazing. There’s really not a reason to use R if you’re not going to use tidyverse. 2. It’s integration to make quick webapps, books, Markdown reports, blogs. Is amazing. In a team where we process a lot in Excel, it’s so convenient I can just make a quick webapp that does that task a lot quicker for everyone to use.
    - vharuck 5y ago
      >There’s really not a reason to use R if you’re not going to use tidyverse. I feel the same way, except for the data.table package.
Mustangdudu69 5y ago
https://coincircle.com/l/I3VxB2MqAi https://coincircle.com/l/I3VxB2MqAi
corty 5y ago
Big big reason: R doesn't alter data without being asked to, Excel does so, silently, sneakily. Anything important should not be done in Excel. https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam... https://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates https://stackoverflow.com/questions/165042/stop-excel-from-a...
- bradleyjg 5y ago
  > Anything important should not be done in Excel. Hold on a second while I go shut down the global economy for two years so we can teach everyone finance person how to program.
  - shakow 5y ago
    Would not necessarily be a bad idea. https://theconversation.com/economists-an-excel-error-and-the-misguided-push-for-austerity-13584 https://theconversation.com/economists-an-excel-error-and-th... Not that this is Excel's fault, but researchers should definitely either seriously learn how to use computer stuff, or just don't. A person can't drive on the highway without a license, the same rigor should be applied here, especially in academic circles.
  - manishsharan 5y ago
    This. Most finance people who live by Excel have ninja level skills with Excel. I have had to unpack logic from some excel sheets into webapps and they had so much sophisticated programming embedded into it. And it is so much easier intuitive to use Solver for linear equations in Excel than any web application. Regarding the graphing ability , R may have more power but plotting the graphs in Excel is so much more WYSIWYG.
  - belter 5y ago
    Do not shutdown the global economy. Just maybe move to tools with less issues around process governance: "The 7 Biggest Excel Mistakes of All Time" https://www.teampay.co/insights/biggest-excel-mistakes-of-all-time/ https://www.teampay.co/insights/biggest-excel-mistakes-of-al... "The financial fails and business risks of spreadsheets" https://www.webexpenses.com/2020/10/financial-fails-business-risks-spreadsheets/ https://www.webexpenses.com/2020/10/financial-fails-business... "Nightmare on spreadsheet: take Excel use seriously" https://www.icaew.com/insights/viewpoints-on-the-news/2020/oct-2020/nightmare-on-spreadsheet-take-excel-use-seriously https://www.icaew.com/insights/viewpoints-on-the-news/2020/o... "Excel – The Dirty Secret" https://tax.thomsonreuters.co.uk/blog/excel-the-dirty-secret/ https://tax.thomsonreuters.co.uk/blog/excel-the-dirty-secret... "8 Challenges When Using Excel For Accounting" https://www.senacea.co.uk/post/excel-for-accounting-challenges https://www.senacea.co.uk/post/excel-for-accounting-challeng...
npilk 5y ago
For small datasets and simple charts, I'm not sure there are any compelling reasons to use R instead of Excel (unless you're already more comfortable with it). Plus, Excel is great for beginners - most people wouldn't need a class to draw a basic chart. The more complex the analysis, the more reasons there are to get out of Excel.
- enriquto 5y ago
  The "spreadsheet" interface is now universal and not limited to excel. You have a completely equivalent thing with gnumeric, or libereoffice's calc, for example. This interface is really great for kids, for example to compute averages and simple formulas, before they learn "serious" programming. I was taught how to use spreadsheets in school and it is easily one of the most useful things that I learned there.
  - npilk 5y ago
    Fair - I was using "Excel" when to your point I meant "a spreadsheet".
radiator 5y ago
With millions of users using Excel, I am not convinced that there are no solutions to the problems of Repeatability, Batch Processing, Extensibility as mentioned in the article.
supahfly_remix 5y ago
Saw CSV mentioned in the article but didn't see it mention that R can read Excel files directly. So, you can use the spreadsheet to capture data and process it in R.
- supahfly_remix 5y ago
  Oops. meant this for my comment below.
supahfly_remix 5y ago
This also provides a good transition path in case you'd like to experiment with R but have existing spreadsheets.
Dwolb 5y ago
Whenever I hear a process is run on excel I do a quick mental calculation of the relative risk of process defect severity, the time it takes to manually do the task in excel, and the investment + maintenance costs associated with automating. When running any process in excel after enough times there will be an error. It just depends on whether that error will cost you a lot of money or not. However, there is also a cost to automating these processes that goes beyond the initial investment. Someone needs to make sure the input data stays clean and coordinate any system-wide changes to the interfaces. This is a new thing that can break whenever system-wide changes get made so data governance becomes a priority.
inthewoods 5y ago
Some compelling reasons - I think most of them can be addressed in Excel, but the reality is most people don't know how Excel really works or how to do anything complex in it. Having said that, as your need to batch process numbers goes up, R and other solutions have more value in my opinion. I'm particularly interested in "half-way" solutions - something between R and Excel. I've been looking at https://www.causal.app/ https://www.causal.app/ - no affiliation but I find their approach similar to a Mac app I like called Numi.
dav43 5y ago
Currently watching a health care department in a c.8m person city implement powerBI. To me, similar reasons surface to why it's a disaster waiting to happen. Formulae hidden away in non document DAX columns. Its horrendous. Its the worst thing to happen to "BI", "Data analysis" that I've seen. Given me a clearly defined single R markdown document that's easy to follow. If you can't follow the Markdown document, you shouldn't be running analysis in the first place.
cmsonger 5y ago
IMO this really asks the question: Why is there not a code view for an excel spreadsheet? I get that some of the basic operations probably create expressions that are too wordy / very "specific data" intensive. That is, if you took the first step and just did your best to create that code view it would have a lot of stuff conditional on specific things. But it's the next step that gets interesting. Now that you've got it, in what ways can the visual UI change to have the code view create tighter expressions. Now that you've got this view, how can it become super handy for doing things that today are clunky? IMO there's an interesting "no-code" path in there somewhere and there's also an interesting "make spreadsheet re-use more powerful." Or maybe not, what do I look like, an Excel engineer? Ha!
- jmkni 5y ago
  > Why is there not a code view for an excel spreadsheet? Isn't that VBA?
  - cmsonger 5y ago
    Well, I'm not a VBA / excel expert -- but my neophyte view is that VBA is used to add code to excel, which is great -- but it's not the same as every single spread sheet is automatically creating a "this VBA is the equivalent of your spread sheet." Again, not an expert. I'd expect I've never written one line of VBA. (Yay for me!)
    - deleted 5y ago
      [deleted]
- Hjfrf 5y ago
  You might want to try out Power Query (get & transform). Step-by-step repeatable transformations where the UI records steps and writes code in the background. Almost exactly what you're talking about, and comes out-of-the-box in the last few versions.
  - cmsonger 5y ago
ConfusedDog 5y ago
These reasons are valid of course. But Excel is installed on most of machines, R or Python isn’t. We all know how people like a “binary” that just runs everywhere… Overall, for serious data analysis work, I’d use R or Python, but Excel is popular enough to have its place, just need to know the risks.
- wuschel 5y ago
  How about other solutions e.g. deno+typescript?
edumucelli 5y ago
One important aspect is the numerical precision. For those in need of correctness, avoid Excel and other spreadsheets. There are several papers discussing the lack of precision of excel and the likes in the literature [1]. [1] https://www.jstatsoft.org/article/view/v034i04 https://www.jstatsoft.org/article/view/v034i04
mkl95 5y ago
A while ago, there was a job offer in my area to automate Excel tasks using Python. The thing was posted time after time by several recruitment agencies, for almost a year. Just thinking how expensive that process must have been answers the question.
- analog31 5y ago
  I've done this kind of conversion, not as a job, but on a case by case basis within my own work. An example would be taking a simple computation that I would share with people (mostly engineers) in the form of a spreadsheet, and instead turning it into a Python program, even replete with GUI. Here are some of the pitfalls that I've experienced. If I share a computation as a spreadsheet, people already know how to work with the GUI, which is actually quite sophisticated, even if it doesn't prevent you from screwing up. People could extend my spreadsheets, e.g., adding columns to do the same computation on multiple input sets, etc. They could easily extract the output in text format and paste it into something else. And so forth. A Python program with GUI created the expectation that I was writing commercial quality "software," and that if it wasn't 100% intuitive, I would hand-hold each colleague, and make changes on demand, including converters to multiple file formats (often, so they could put the data back into a spreadsheet). And of course I also had to help each person install Python on their computer. Of course I'm not a full fledged software developer, just a "scientific" programmer, and of course the lesson I learned is common knowledge: Writing and supporting real software is orders of magnitude more costly than just writing a one-off program to solve a problem, to the point where the conversion to "proper code" could be a net liability to the business. You have to assess whether it will actually add value. In one sibling comment, it sounds like the answer was yes, so I acknowledge that. The difference was not so much the technology, but the cultural expectations associated with Excel versus "software." Today, I do all of my work in Python, but when I share a simple computation with colleagues, I will often convert it back into Excel for them.
  - benhurmarcel 5y ago
    I’m doing this kind of task, and I tend to avoid any GUI and just have input and output files as simple text, CSV, or Excel spreadsheet. It seems to me that it’s the best way to have a satisfactory UX without too much headache.
underdeserver 5y ago
For non-programmers who do any kind of data processing, spreadsheet applications (Excel, Numbers, Google Sheets) are the closest thing to programming they have, and have a lot less overhead - just throw in the data, write a formula, you get results, no compilation or fidgeting with input and output and so on. The steps of an algorithm are reflected by cells that reference cells that reference cells. I've always thought that every highschooler should be taught how to use Excel properly, it really is a superpower in many contexts.
- deleted 5y ago
  [deleted]
- iagovar 5y ago
  Knime is way more powerful, but it requires more learning too. It has nodes for programming (R & Python, and I'm pretty sure it has something with JAVA) your own transformation if you want to integrate complex stuff, but it's very capable out of the box. Excel has PowerQuery too, which is very nice but you hit the ceiling pretty easy. Knime eats a lot of data down the throat with modest PC, Excel really struggles with large datasets, not matter how you use or tune PowerQuery. I know here in HN people talk down visual programming, but I've done pretty heavy and complicated stuff with it. It would be way more complicated with pandas.
- tapia 5y ago
  I've always thought that every highschooler should be taught the basics of programming with an easy language like Python instead. That really is a superpower in a lot more contexts.
  - underdeserver 5y ago
    Yes, but often different contexts. Let's say my volunteer org wants to keep track of events, who volunteered in them, etc. and wants to give an award to the volunteer who gave the most hours. How do you do that in Python? Why would you?
    - 5y ago
akg_67 5y ago
There is no reason to use R when Excel can do the jobs. It is when Excel can’t handle the task at hand, R shines. I am well familiar with R, but for simple data manipulation, quick chart, pivot table, usually resort to a spreadsheet program. I maintain my Options Trading Journal in spreadsheet where I record all the Options trade I made, positions I hold, P&L, etc. I just can’t imagine doing that in R. R starts with CSV, Excel ends with CSV.
- lordnacho 5y ago
  How do you store your surface?
- jstx1 5y ago
  > There is no reason to use R when Excel can do the jobs. It is when Excel can’t handle the task at hand, R shines. I think the problem is more nuanced because Excel can do a lot. There is a large set of use cases and tasks that are possible in Excel but your team would be a lot more efficient and productive using a programming language instead. The trick is to recognise those tradeoffs and it it's not as simple as sticking to Excel as long as things are still possible there.
  - sweezyjeezy 5y ago
    Most data scientists I work with just sneer at Excel. Using a programming language is often using a sledgehammer to crack an egg though - sometimes you just want to look at the data in the csv, and I still see people doing this in R/Jupyter - just open it in a spreadsheet, trust me - the UI is better.
- sweezyjeezy 5y ago
  100% this - I encourage junior data scientists to get more familiar with Excel when they start - R/python are definitely more flexible, but if Excel can sort you out it's always the quickest way.
protomyth 5y ago
R isn't interactive or visual like Excel. Excel's learning curve is a lot less daunting. Also, Excel is ingrained in the accounting culture and well supported by peers. Plus Excel is everyone's default database and form software. None of the article's reasons matter to non-IT people and no IT department has the juice to override the CFO and accountants. Unlike, every other interactive tool that displeases programmers, Excel survives. If you want R to replace Excel you need to build an interactive front end that can do everything Excel can as easily as Excel. Sitting in a class is not and option.
- salamandersauce 5y ago
  No, but it's far easier to see what the hell an R script is doing versus an Excel file. And there's just a ton more that R can do that Excel can't. More powerful plotting options, more tools like Random Forests or mixed effect models. And has less issues with floating point. Excel is good for accounting tasks because that's what it and other spreadsheet software were originally designed for. Data science tasks are tacked on.
- azalemeth 5y ago
  > Plus Excel is everyone's default database and form software. This is one of many, many things that is terribly wrong with Excel. It is arguably well suited for neither of those applications. I will continue to do my statistics in R, and be very glad that I do not work somewhere with either CFOs or powerful accountants ;-).
- ineedasername 5y ago
  Most of what you said only applies when the tasks are accounting related. There's a lot more to Excel, and a lot more than that to R. And just like, in accounting, you're not going to get the CFO to choose R over Excel, in many circles involving data analysis and statistics you won't get to choose anything but R. Also you don't need an interactive front end for R any more than you need an interactive front end for any other programming language. The scope of use is too large to allow for a single GUI. There are some packages that provide a GUI for specific purposes, like rattle, which provides an interface for common data mining tasks and models. I'd you want a pretty good spreadsheet GUI for viewing and modifying data, you can use rhandsontable. Just a few examples. Replacing Excel with R isn't necessary and I think would miss the point & relative strengths of the different tools. Recognizing where each tool is better suited for your needs, and if R is worth the learning curve for your specific needs, is what is important.
icegreentea2 5y ago
Best 'ah ha' moment for why R (or python, matlab, or whatever) might be useful is to have someone do tedious work making a bunch of charts and formatting them in Excel. Oh, and histograms. Oh, or confidence intervals on your linear regression. Pivotcharts (and tables) help quite a bit for removing some of the tedium, but now suddenly it's interactive. Sometimes you want a format where BAM, everything is there laid out. There's nothing to misclick, all the tables and charts are there, you can just tell whoever, "look at the 3rd figure from the top on the 2nd tab". The other things that people usually laugh at excel about (aside from silently changing values... that's just baaaaad) is the gong-show of naming files and versioning. R and friends + CSVs by themselves don't fix that problem. They just make it somewhat easier to solve (as in they play with git better).
eatonphil 5y ago
This week I got a bit more familiar with R while adding it as a scripting language for a data IDE I'm working on (it already supported other languages). It is a very hard language and community to get into! The documentation is very sparse. Library documentation is published as PDF (I guess?) and also very sparse. The default `print` behavior is pretty hard to understand. It's 1-indexed and it took me a while to realize every time I think `array[1]` I should write `array[[1]]`. I can't tell the difference between `<-` and `=`. My guess is that it was probably a great language at some point but is way behind other numeric scripting languages like Julia or Matlab in terms of community attention and language ergonomics. I know it's highly used but other than legacy reasons I'm not sure why you'd want to learn it over Julia. I'm also curious to investigate how the aspects I'm critical of differ in Octave. Edit: totally fair, 1-indexing shouldn't have been a "critique". Lots of languages do that.
- Dumblydorr 5y ago
  Use the tidyverse and use the cheatsheets for dplyr and the book R for Data Science. If you're trying to use primarily base R, you'll be limited and hamstrung. Tidyverse is the modern framework of choice.
- mshron 5y ago
  I highly recommend anybody getting into R to skip the base language (which indeed is ancient and full of gotchas) and go straight for the Tidyverse[1]. You can always go back in and learn how to do things the old way later. Over the last decade, the R community has largely standardized around tools like dplyr, ggplot, tibble, purrr, and so on that make doing data science work way easier to reason about. Much more ergonomic. At my company we switched from using Python to using R for most analytical data science work because the Tidyverse tools make it so much easier to avoid bugs and weird join issues than you get in a more imperative programming environment. [1] https://www.tidyverse.org/ https://www.tidyverse.org/
  - jstx1 5y ago
    I would recommend the opposite - pick stuff from tidyverse (mostly dplyr and ggplot2) only if you need them. Knowing base R goes a long way on its own.
cabbagehead 5y ago
* R scales to big data, runs on servers * Excel is full of foot guns - e.g. errors in ranges are hidden unless you dig in * R you can write test suite and run it in CI * R integrates nicely with cloud services via reticulate * R’s Shiny is great for interactivity and deploys to the web * R and git support you to collaborate - PR reviews etc * automatable - run analysis in a pipeline when data arrives and publish results on the web * white box / transparency - who knows what Excel does exactly in some of its functions * data science - Excel doesn’t do machine learning, NLP
ad404b8a372f2b9 5y ago
Because R has a million scientific libraries to enable very specialized research workflows and excel doesn't.
WBrentWilliams 5y ago
For me, it comes down to the right tool for the job. Excel (strictly speaking, spreadsheets) is very powerful. It is a grid-based visual programing environment that has an interface simple enough to be groked by its original target audience: accountants and file clerks. What that means to me is that I use excel for "toys" (small data set analysis, rough charts) and ephemeral-yet-shared lists where it is easier to say "evening batch tab, line 3" to communicate which job needs to be updated than try to communicate the same thing in a written request or pull a database table report. I use R where I need more power: automated reports, automated statistics, and more complex analysis where the paradigm of textual code fits better than the paradigm of a 2D grid. I teach R (or python) when I want to give someone more reproduceable tools than a "magic" spreadsheet that, under the covers, is really a contraption held together with sticky tape and positive thinking. We can, and should, teach both. Ideally side-by-side with a constant stream of "why are you doing task X this way?" that is largely missing from formal education.
Zhyl 5y ago
If you need to do something once, use a GUI (Excel). If you need to do something ten times, use hotkeys and shortcuts. If you need to do something a hundred times, write a script (R). I usually use the command line as the example for why writing code and scripts are better than the more intuitive and lower-learning-curve GUIs. If I want to move a file from one folder to another then I just drag it across. Easy. If I want to move a thousand files from one folder to another, I will benefit from learning `CTRL-A` or shift-clicking (slightly more obscure than the 'intuitive' drag each file across individually or drag a large box around them all to select them). If I want to move a thousand files beginning with 'UTR-77' and ending with '.csv' then I would benefit from learning `mv UTR-77*.csv $folder`, but I could still do it manually if I didn't know that was an option. If I want to move a thousand files beginning with 'URT-77' to another folder at a moment's notice or at Thursday 1am, then the only options I really have are scripting. I almost feel like before people learn the 'basic' stuff as outlined at the beginning of the article, they should be shown some 'magic' that is only really possible with scripting so that it's clear from the outset why you wouldn't 'just use excel'.
- DantesKite 5y ago
  This is a great comment and the line of reasoning for work at different scales makes so much sense.
- danuker 5y ago
  > I want to move a thousand files beginning with 'UTR-77' and ending with '.csv' 1. Use a file manager with stable sorting [1] (I use Thunar which does this, but I suspect lots of file managers keep the sort order stable). 2. Sort by type. 3. Sort by name. 4. Select the first file named UTR-77. 5. Scroll to the last file, and Shift+Click it. 6. Cut then paste to your desired directory. [1] - https://en.wikipedia.org/wiki/Sorting_algorithm#Stability https://en.wikipedia.org/wiki/Sorting_algorithm#Stability
  - bicolao 5y ago
    > 5. Scroll to the last file, and Shift+Click it. With thousands of files, this won't be particularly fast or easy.
tomrod 5y ago
Unless you need the frontier of statistical methods and are unable to code them yourself, I highly recommend not engaging R. While Hadley Wickham has done amazing things to make the R language actually useful, Python and Julia are better for data applications. Also, bindings to the R language exist, obviating the need to be tied into it completely. Thus, even if you need the frontier, you have it available to you.
- padthai 5y ago
  R is first and foremost an interactive language, and the focus of the libraries, tooling, and community reflects that. I believe working in pandas/sklearn/jupyter ecosystem is much slower (>50%) if you are doing EDA and statistical modelling (ML) than tidyverse/tidymodels/rstudio. The exception is deep learning and adjacent fields (like computer vision). Of course Python is better at everything else, so adding an extra languages might or might be worth the hassle.
- tomrod 5y ago
  Just as a followup, when asked I also excoriate Python for its gap in typing for data applications. But that gap isn't as wide as the gulf between Python & Julia vs R
- dm319 5y ago
  I would say R has a beautiful functional approach which is very hard to replicate in python. It is also faster than Python and Julia for tabular/dataframe data. Ggplot is fantastic - I tried to recreate some relatively simple plots (scatterplot with a LOESS line of best fit coloured by group) and failed to do this in Python easily.
intrasight 5y ago
I think a fairer question today would be "Why do we use R rather than M?" https://www.howtoexcel.org/power-query/m-code/ https://www.howtoexcel.org/power-query/m-code/
- Closi 5y ago
  Absolutely! For power-users of Excel, this article reads as "the reason we don't use Excel is because we don't understand how to use it". For those who aren't as familiar with Excel, all the functionality the author is describing as missing in Excel is actually built into it out of the box - as a feature called Get & Transform (or 'PowerQuery'). You can load in a CSV, manipulate it, do batch processing, and all the steps are visible, editable, resequencable and deletable (showing you the M code behind it!). You can even handle hundreds of millions of records this way. For those who want to learn, I would wholeheartedly recommend this book: https://www.amazon.co.uk/Power-Pivot-Bi-Excel-2010-2016/dp/1615470395 https://www.amazon.co.uk/Power-Pivot-Bi-Excel-2010-2016/dp/1...
  - pjmlp 5y ago
    I think it comes from Excel hate and thinking Google sheets are good enough. Many people to this day aren't even aware cells can be named like proper variable names.
  - sitrucp 5y ago
    Second using Get & Transform (or 'PowerQuery'). Note it is also Power BI's data integration functionality. (Though was recently disappointed to learn it is not available on Mac.) While I use wide range of data integration and visualization tools, Excel/Power Query with pivot tables & charts is often my go-to for quick data analysis or exploration. Recently used it to retrieve Our World in Data Github hosted csv file data into an Excel file and then click refresh to get most recent data: https://009co.com/?p=1491 https://009co.com/?p=1491
__te__ 5y ago
When you are learning the basics, you are doing very basic tasks. R can do very basic tasks. MS Excel can do very basic tasks. If you never go beyond the basics, there is no compelling reason to use R (or any other programming language) over MS Excel. Programming languages come into their own when the tasks stop being basic, but the learning process usually goes through "Hello, World" first. I mean, if you're just going to print a line of text to the screen, why use Python? Just open an MS Word document. MS Word can even include variables and insert them dynamically!
Traster 5y ago
I think one of the things this is really missing is that Excel fundamentally supports 1 data structure - the table. What if your data isn't a table? What if your data is many tables? What if your data has 3 dimensions? It soon becomes incredibly difficult to map your actual problem into a set of tables. It's actually one of excel's great features - they so strongly constrained the way you represent data that it's easy to reason about.
erdos4d 5y ago
Mainly because they haven't heard of Python apparently.
o_m 5y ago
These are also good points against no-code
fghorow 5y ago
Debugability.
RobinL 5y ago
For me, the biggest reason to use a programming language instead of Excel is its capacity for abstraction, and hence ability to organise code and separate concerns. Attempting to solve a complex analytical problems in Excel is similar to a doctor trying to solve medical problems by reference only to atoms. Instead doctors use a variety of abstractions: organs, cells, enzyme, etc. to understand and explain a problem. By using a programming language, we can develop appropriate abstractions to solve our problem in a way which keeps a lid on complexity. I think this concept does make sense to an advanced Excel user, and can help explain the situations in which they may reach for a different tool. Having made some very complex Excel spreadsheets in the past, I think was aware it can become very difficult to develop them or generalise them further, even before I became a programmer.
aaronrobinson 5y ago
One big thing missing last I worked on Excel/VBA is the ability to write test cases.
dec0dedab0de 5y ago
I don't use R, and didn't read this, but I think the biggest problem with excel is that it hides the code. Figuring out which cell does what, and how, usually means clicking a million places trying to remember what is what It's the same reason I took the doors off my pantry and cabinets
ekianjo 5y ago
Much faster for much data processing workloads too. Like, significantly. And Excel has some stupid limitations like a maximum of a few millions lines.
aselino 5y ago
R is free. Excel is not. Excel encourages messy behavior, just like Jupyter Notebooks. Sometimes during EDA that might be ok. But anything rigorous shouldn’t be confined to just Excel. Or Jupyter imo.
dtnewman 5y ago
i think that part of the answer is that software engineers simply aren’t (usually) familiar with excel. It’s a remarkably complex and powerful program but if you never used it professionally you will never learn its full potential. Yes, it’s easier to do useful stuff as a beginner in excel vs a real programming language but to get really good at it takes time. You might think you know excel because you’ve used it in some of your classes, but to quote my first boss, if you don’t know how to use excel without ever touching a mouse then you aren’t even an intermediate user.
- Dumblydorr 5y ago
  Meh, if you're diving so deep into excel to learn all the shortcuts, why not just port the data into a scripting language which can do a lot more advanced things more easily and transparently.
croes 5y ago
Because in Excel 1900 is a leap year.
indymike 5y ago
The reason that we use R or Python or any programming language over applications is integration, capacity, performance or control of process. When I'm asked to replace a spreadsheet it is usually based on the sheet being to large to be performant or it is very buggy, and can't be relied on. I.e. CFO got crushed in a meeting because of a bad excel/googel sheets formula. Lately, I've been thinking about what the "Rust of spreadsheets" would be like... It's really a big problem now.
dan-robertson 5y ago
The article is a bit one sided. I think it ought to acknowledge the good things about excel. For example it lets you more directly interact with the data and see intermediate results.
hermitcrab 5y ago
R and Excel are not the only alternatives. There are also visual, node-based data transformation tools such as Easy Data Transform, Knime and Alteryx. They can do 95% of what R can do, but are much easier to get started with.
deleted 5y ago
[deleted]
henvic 5y ago
I had to "learn" R during my CS course, and I was really impressed by it. We used RStudio and Shiny to build this: https://henvic.shinyapps.io/accidents https://henvic.shinyapps.io/accidents https://github.com/henvic/accidents https://github.com/henvic/accidents
nomilk 5y ago
Another big one for me is speed. Almost all the popular wrangling libraries in R (tidyverse, data.table) use c++ under the hood and are lightning fast. Whereas excel can chug away for five minutes operating across a few tens or hundreds of thousands of rows; R can often do the same operations on a few millions or tens of millions of rows in a few seconds.
ohgreatwtf 5y ago
if you need to do something, use python. if you need to do it while using complex math, use R. if you need to do it in real-time, use c. if you need to do it reliably with memory constraints, use ada. if you need it to work under arbitrary and difficult conditions and continually without pausing for weeks or years on end, use rust. for everything else, there is javascript.
SubiculumCode 5y ago
The article completely misses the point of R. Go ahead. Try and run a mixed effects model with random slopes in Excel, and plot the random effects and the fixed effects in a spaghetti plot. Let me how that went.
RosanaAnaDana 5y ago
I don't think there is any excuse for doing analysis in excel at this point, and if any analyst came to me with either a) no coding skills or b) no interest in developing them, I'd laugh them out of the room. My BiL is this breed. He's a financial analyst. He's got a hilarious mental block about using any kind of coding language in his work to the point that he actually developed a web-scraping app in excel. Don't ask me how. But jimmeny-christmas, use the right too for the damn job.
TomJansen 5y ago
For a program which combines R with an Excel/SPSS interface I can recommend Jamovi [1] [1] https://www.jamovi.org/ https://www.jamovi.org/
iNane9000 5y ago
Also -Open source is better for serious work -Excel is notoriously buggy and shouldn’t be used for anything important, yet it is and the costs are enormous. Many articles on this.
- deleted 5y ago
  [deleted]
culebron21 5y ago
I would have been surprised and intrigued if the title read "Why we use R instead of Python". I've used Excel myself as a student in 2003-05, when R or Python weren't known. And now I sometimes teach Economics students, and they all work either in R or in Jupyter Notebooks. Many of them routinely do what was unthinkable -- process 100s of thousands of records, do geospatial queries and joins, etc.
pg_bot 5y ago
I would add testing, collaboration, and complex use cases. There's no good way to check if you've made a mistake in the logic of your excel spreadsheet. You can easily test your R code. If you need to work with multiple people excel is a nightmare. It works fine when one person needs to build something for themselves or just to show to someone else. When you get two or more people working together it's like sharing a keyboard with someone else. Excel can do a lot of things, but there is a limit to what it can do. R or Python is typically what you would reach for when you need to do something beyond what excel is capable of.
chrisgd 5y ago
Most ppl who use excel would benefit from Access and creating some repeatable processes. A lot of the sorting and ranking from excel could be done in linked tables and improve the repeatability
ISL 5y ago
I have more than once found myself answering that sort of question. I gave similar reasoning. In 2020, for a grassroots PPE-relief organization, however, I found that I had sometimes been mistaken. What our group managed to achieve by eschewing (eventually as a watchword) fancy tools and building our entire backend around Google Sheets was speed. Moreover, I learned along the way that simple database tools are sometimes more-efficient or faster than anything I'd have written myself. I had been blinded by the GUIs -- the (frequently correct) notion that GUIs are generally inferior in the long run to scripting/programming had blinded me to the very idea that perhaps another tool could be superior. As my career takes me in new directions, I'm presently reprising that experience, this time with SQL. Physicists rarely use it, so we have no idea what it can do. The syntax looks old/quirky/muddy, but the tools behind it are extremely powerful. If you're great at R, consider sitting down with someone whom you know is just crushing problems with Excel. I'm pretty sure you'll both learn something useful.
ogig 5y ago
I have some objections, as someone who doesn't use R but does quite a lot of Excel. The author seems to miss PowerQuery, data model, and PowerPivot functions. "Visibility: How do you see the code inside an Excel document? How do you tell exactly what is going on? You have to go clicking through cells, or reverse engineer what settings a graph has." PowerQuery it's a functional way of transforming data step after step. You can see the code/function of each step and it's very easy to reason about those functions. You can transform data to the format you want, and do calculations on it before it enters the spreadsheet. One of the main advantages is that it's easy! I've taught non programmers to reliably use this tool. "Repeatability: [...] With R, you just change read.csv("1.csv") to read.csv("2.csv") and the exact same calculations are run on two different data sets." Again, with PowerQuery this is doable, and a normal procedure on my day to day. You change the file parameter PowerQuery will use on step 1, and the rest of steps will follow. "Batch processing: Related to the above, you can read every CSV in a directory and produce a graph for each of them. You can read data from an API and run the same process on it that you did yesterday." You can use PowerQuery on folders, it can take a set of files and transform or aggregate them all at once. In my opinion Excel has gotten pretty powerful after data model and powerquery were added, I think around excel 2013. I barely use cell functions anymore; data model and pivot tables make for robust spreadsheets that are easy to reason about. I know a decent programmer can do most of it in many other ways, but the accessibility of Excel is amazing. The biggest defect Excel has for me at the moment is control change tracking. Wish I could git Excel changes.
derefr 5y ago
Is there a tool to transpile an Excel workbook to R source code? It seems like a plausible-enough thing to do. • Put all the static data first in one section, broken into tables with PDF’s word-boundary logic (the one that allows you to highlight text, despite it being a bunch of individually laid-out graphemes) • reverse-postorder (topological sort) the formula cells’ definitions, grouping them into “stanzas” by which “tables” of static data they’re transitively touching. The result would read a lot like the definition of an expert system in Prolog. Facts, then predicates.
techlatest_net 5y ago
Shameless plug, if you want to use R-Studio and R on AWS,GCP,Azure, we provide VMs with pre-installed R-Studio and popular R libraries which are far cheaper than the official R-Studio . Below are the links: AWS : https://aws.amazon.com/marketplace/pp/prodview-brc4ybuoee6he?qid=1626027247189 https://aws.amazon.com/marketplace/pp/prodview-brc4ybuoee6he... GCP : https://console.cloud.google.com/marketplace/product/techlatest-public/r-developer-suit https://console.cloud.google.com/marketplace/product/techlat... Azure : https://azuremarketplace.microsoft.com/en-us/marketplace/apps/techlatest.r-developer-suit?tab=Overview https://azuremarketplace.microsoft.com/en-us/marketplace/app... Support & Documentation : http://www.techlatest.net/support/r-studio-support/ http://www.techlatest.net/support/r-studio-support/
schwartzworld 5y ago
Reason I use scripting languages over excel: I don't want to pay for excel, or a machine that will run it.
- ogogmad 5y ago
  There are open-source spreadsheet programs, like LibreOffice Calc. I occasionally use it (Calc) to do things like taxes. I find it quite easy to use, personally, but YMMV.
supercanuck 5y ago
Excel doesn't scale. period. full stop.
beefield 5y ago
Only slightly tongue in cheek: Removing the ability to save workbooks would still allow people use excel where it shines while not allowing people and organizations shoot themselves in foot by using subpar processes for anything you actually need to be confident about.
coss 5y ago
One problem we have with excel is merging documents. I wish there was a good tool to use that worked well with merging.
deleted 5y ago
[deleted]
teleforce 5y ago
Beyond Spreadsheet with R book for those who are venturing into R from Excel: https://www.manning.com/books/beyond-spreadsheets-with-r https://www.manning.com/books/beyond-spreadsheets-with-r
Avalaxy 5y ago
As a counter point to his 'Repeatability' point: in Excel you can treat your workbook as a simple output for your data files. Let's say I have my data in a CSV file (like the example), I can just load the data from the CSV file and use Power Query to apply any transformations and calculations to this data. I could then simply swap out the CSV file for another CSV file, and all the transformations and calculations would be applied to it just the same. This is also how it works in Power BI, but I guess it's a bit of a different way of working than how most people are used to work.
Qem 5y ago
On the accuracy of statistical procedures in Microsoft Excel 2007: https://materias.df.uba.ar/l1aa2017c2/files/2017/08/excel2007.pdf https://materias.df.uba.ar/l1aa2017c2/files/2017/08/excel200...

241 points by edent 5y ago

I use R and Excel daily. Excels graphs are simpler to make than a customized GG plot, but an ice cream sandwich is easier than a sundae with whipped cream and sauce and a cherry. Different levels of difficulty and customization for different applications.

fatline 5y ago

The author could have mentioned testing. The reason I use python scripts instead of spreadsheets for important calculations is that I can unit test the logic extensively and be sure that the code works as expected. This also makes sure I don't break stuff when adding functionalities / refactoring code. I would never ever use Excel for something important, unless it's completely trivial (e.g., sum/average values of a column). I assume the same applies to R vs spreadhseets, of course.

throwaway879 5y ago

Worse Is Better™

prionassembly 5y ago

Excel files are shareable and everyone's able to inspect them. (drops mike)

fractal618 5y ago

Use R when getting paid by the hour. Excel when you have dinner plans.

foreigner 5y ago

Wow those reasons don't sound very compelling to me! (I'm a programmer but don't know R.)

verelo 5y ago
Honestly i struggle with it. My partner recently wrote an R package (phd biology) and I’m a developer. I had done a bit of R but nothing substantial. By the end of the project i couldn’t think of a compelling reason not to use any other language. The R tooling is clumsy, the language isn’t great…the built in graphs are ok? It feels like the reason it’s popular is mostly just people not wanting to update skills because they’re in a tangential field where this is a solution so it’s not a priority to change.
- bonoboTP 5y ago
  There are also a lot of niche libraries for various statistical tests and analyses that are used for scientific studies. Especially applied scientists who wouldn't necessarily be able to reimplement the math and are not very familiar with programming rely on it as a workhorse. Things like variants of anova, regressions etc. are often just a single line in R, specify the data, the interaction terms etc and you get a nicely formatted output with everything calculated for you, like goodness of fit, p-values etc. There's a long tail of functionality in there that's hard to match even with the scipy stack or other statistical packages in python. Plotting is also much less of a hassle than with matplotlib. R is more of a free and de-bloated SPSS than an alternative to scipy or excel.
- JimBlackwood 5y ago
  I use R for work a lot, maybe to give some reasons to use R: 1. The language itself isn’t great, but the tidyverse packages are amazing. There’s really not a reason to use R if you’re not going to use tidyverse. 2. It’s integration to make quick webapps, books, Markdown reports, blogs. Is amazing. In a team where we process a lot in Excel, it’s so convenient I can just make a quick webapp that does that task a lot quicker for everyone to use.
  - vharuck 5y ago
    >There’s really not a reason to use R if you’re not going to use tidyverse. I feel the same way, except for the data.table package.

Mustangdudu69 5y ago

https://coincircle.com/l/I3VxB2MqAi https://coincircle.com/l/I3VxB2MqAi

corty 5y ago

Big big reason: R doesn't alter data without being asked to, Excel does so, silently, sneakily. Anything important should not be done in Excel. https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam... https://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates https://stackoverflow.com/questions/165042/stop-excel-from-a...

bradleyjg 5y ago
> Anything important should not be done in Excel. Hold on a second while I go shut down the global economy for two years so we can teach everyone finance person how to program.
- shakow 5y ago
  Would not necessarily be a bad idea. https://theconversation.com/economists-an-excel-error-and-the-misguided-push-for-austerity-13584 https://theconversation.com/economists-an-excel-error-and-th... Not that this is Excel's fault, but researchers should definitely either seriously learn how to use computer stuff, or just don't. A person can't drive on the highway without a license, the same rigor should be applied here, especially in academic circles.
- manishsharan 5y ago
  This. Most finance people who live by Excel have ninja level skills with Excel. I have had to unpack logic from some excel sheets into webapps and they had so much sophisticated programming embedded into it. And it is so much easier intuitive to use Solver for linear equations in Excel than any web application. Regarding the graphing ability , R may have more power but plotting the graphs in Excel is so much more WYSIWYG.
- belter 5y ago
  Do not shutdown the global economy. Just maybe move to tools with less issues around process governance: "The 7 Biggest Excel Mistakes of All Time" https://www.teampay.co/insights/biggest-excel-mistakes-of-all-time/ https://www.teampay.co/insights/biggest-excel-mistakes-of-al... "The financial fails and business risks of spreadsheets" https://www.webexpenses.com/2020/10/financial-fails-business-risks-spreadsheets/ https://www.webexpenses.com/2020/10/financial-fails-business... "Nightmare on spreadsheet: take Excel use seriously" https://www.icaew.com/insights/viewpoints-on-the-news/2020/oct-2020/nightmare-on-spreadsheet-take-excel-use-seriously https://www.icaew.com/insights/viewpoints-on-the-news/2020/o... "Excel – The Dirty Secret" https://tax.thomsonreuters.co.uk/blog/excel-the-dirty-secret/ https://tax.thomsonreuters.co.uk/blog/excel-the-dirty-secret... "8 Challenges When Using Excel For Accounting" https://www.senacea.co.uk/post/excel-for-accounting-challenges https://www.senacea.co.uk/post/excel-for-accounting-challeng...

npilk 5y ago

For small datasets and simple charts, I'm not sure there are any compelling reasons to use R instead of Excel (unless you're already more comfortable with it). Plus, Excel is great for beginners - most people wouldn't need a class to draw a basic chart. The more complex the analysis, the more reasons there are to get out of Excel.

enriquto 5y ago
The "spreadsheet" interface is now universal and not limited to excel. You have a completely equivalent thing with gnumeric, or libereoffice's calc, for example. This interface is really great for kids, for example to compute averages and simple formulas, before they learn "serious" programming. I was taught how to use spreadsheets in school and it is easily one of the most useful things that I learned there.
- npilk 5y ago
  Fair - I was using "Excel" when to your point I meant "a spreadsheet".

radiator 5y ago

With millions of users using Excel, I am not convinced that there are no solutions to the problems of Repeatability, Batch Processing, Extensibility as mentioned in the article.

supahfly_remix 5y ago

Saw CSV mentioned in the article but didn't see it mention that R can read Excel files directly. So, you can use the spreadsheet to capture data and process it in R.

supahfly_remix 5y ago
Oops. meant this for my comment below.

supahfly_remix 5y ago

This also provides a good transition path in case you'd like to experiment with R but have existing spreadsheets.

Dwolb 5y ago

Whenever I hear a process is run on excel I do a quick mental calculation of the relative risk of process defect severity, the time it takes to manually do the task in excel, and the investment + maintenance costs associated with automating. When running any process in excel after enough times there will be an error. It just depends on whether that error will cost you a lot of money or not. However, there is also a cost to automating these processes that goes beyond the initial investment. Someone needs to make sure the input data stays clean and coordinate any system-wide changes to the interfaces. This is a new thing that can break whenever system-wide changes get made so data governance becomes a priority.

inthewoods 5y ago

Some compelling reasons - I think most of them can be addressed in Excel, but the reality is most people don't know how Excel really works or how to do anything complex in it. Having said that, as your need to batch process numbers goes up, R and other solutions have more value in my opinion. I'm particularly interested in "half-way" solutions - something between R and Excel. I've been looking at https://www.causal.app/ https://www.causal.app/ - no affiliation but I find their approach similar to a Mac app I like called Numi.

dav43 5y ago

Currently watching a health care department in a c.8m person city implement powerBI. To me, similar reasons surface to why it's a disaster waiting to happen. Formulae hidden away in non document DAX columns. Its horrendous. Its the worst thing to happen to "BI", "Data analysis" that I've seen. Given me a clearly defined single R markdown document that's easy to follow. If you can't follow the Markdown document, you shouldn't be running analysis in the first place.

cmsonger 5y ago

IMO this really asks the question: Why is there not a code view for an excel spreadsheet? I get that some of the basic operations probably create expressions that are too wordy / very "specific data" intensive. That is, if you took the first step and just did your best to create that code view it would have a lot of stuff conditional on specific things. But it's the next step that gets interesting. Now that you've got it, in what ways can the visual UI change to have the code view create tighter expressions. Now that you've got this view, how can it become super handy for doing things that today are clunky? IMO there's an interesting "no-code" path in there somewhere and there's also an interesting "make spreadsheet re-use more powerful." Or maybe not, what do I look like, an Excel engineer? Ha!

jmkni 5y ago
> Why is there not a code view for an excel spreadsheet? Isn't that VBA?
- cmsonger 5y ago
  Well, I'm not a VBA / excel expert -- but my neophyte view is that VBA is used to add code to excel, which is great -- but it's not the same as every single spread sheet is automatically creating a "this VBA is the equivalent of your spread sheet." Again, not an expert. I'd expect I've never written one line of VBA. (Yay for me!)
  - deleted 5y ago
    [deleted]
Hjfrf 5y ago
You might want to try out Power Query (get & transform). Step-by-step repeatable transformations where the UI records steps and writes code in the background. Almost exactly what you're talking about, and comes out-of-the-box in the last few versions.
- cmsonger 5y ago

ConfusedDog 5y ago

These reasons are valid of course. But Excel is installed on most of machines, R or Python isn’t. We all know how people like a “binary” that just runs everywhere… Overall, for serious data analysis work, I’d use R or Python, but Excel is popular enough to have its place, just need to know the risks.

wuschel 5y ago
How about other solutions e.g. deno+typescript?

edumucelli 5y ago

One important aspect is the numerical precision. For those in need of correctness, avoid Excel and other spreadsheets. There are several papers discussing the lack of precision of excel and the likes in the literature [1]. [1] https://www.jstatsoft.org/article/view/v034i04 https://www.jstatsoft.org/article/view/v034i04

mkl95 5y ago

A while ago, there was a job offer in my area to automate Excel tasks using Python. The thing was posted time after time by several recruitment agencies, for almost a year. Just thinking how expensive that process must have been answers the question.

analog31 5y ago
I've done this kind of conversion, not as a job, but on a case by case basis within my own work. An example would be taking a simple computation that I would share with people (mostly engineers) in the form of a spreadsheet, and instead turning it into a Python program, even replete with GUI. Here are some of the pitfalls that I've experienced. If I share a computation as a spreadsheet, people already know how to work with the GUI, which is actually quite sophisticated, even if it doesn't prevent you from screwing up. People could extend my spreadsheets, e.g., adding columns to do the same computation on multiple input sets, etc. They could easily extract the output in text format and paste it into something else. And so forth. A Python program with GUI created the expectation that I was writing commercial quality "software," and that if it wasn't 100% intuitive, I would hand-hold each colleague, and make changes on demand, including converters to multiple file formats (often, so they could put the data back into a spreadsheet). And of course I also had to help each person install Python on their computer. Of course I'm not a full fledged software developer, just a "scientific" programmer, and of course the lesson I learned is common knowledge: Writing and supporting real software is orders of magnitude more costly than just writing a one-off program to solve a problem, to the point where the conversion to "proper code" could be a net liability to the business. You have to assess whether it will actually add value. In one sibling comment, it sounds like the answer was yes, so I acknowledge that. The difference was not so much the technology, but the cultural expectations associated with Excel versus "software." Today, I do all of my work in Python, but when I share a simple computation with colleagues, I will often convert it back into Excel for them.
- benhurmarcel 5y ago
  I’m doing this kind of task, and I tend to avoid any GUI and just have input and output files as simple text, CSV, or Excel spreadsheet. It seems to me that it’s the best way to have a satisfactory UX without too much headache.

underdeserver 5y ago

For non-programmers who do any kind of data processing, spreadsheet applications (Excel, Numbers, Google Sheets) are the closest thing to programming they have, and have a lot less overhead - just throw in the data, write a formula, you get results, no compilation or fidgeting with input and output and so on. The steps of an algorithm are reflected by cells that reference cells that reference cells. I've always thought that every highschooler should be taught how to use Excel properly, it really is a superpower in many contexts.

deleted 5y ago
[deleted]
iagovar 5y ago
Knime is way more powerful, but it requires more learning too. It has nodes for programming (R & Python, and I'm pretty sure it has something with JAVA) your own transformation if you want to integrate complex stuff, but it's very capable out of the box. Excel has PowerQuery too, which is very nice but you hit the ceiling pretty easy. Knime eats a lot of data down the throat with modest PC, Excel really struggles with large datasets, not matter how you use or tune PowerQuery. I know here in HN people talk down visual programming, but I've done pretty heavy and complicated stuff with it. It would be way more complicated with pandas.
tapia 5y ago
I've always thought that every highschooler should be taught the basics of programming with an easy language like Python instead. That really is a superpower in a lot more contexts.
- underdeserver 5y ago
  Yes, but often different contexts. Let's say my volunteer org wants to keep track of events, who volunteered in them, etc. and wants to give an award to the volunteer who gave the most hours. How do you do that in Python? Why would you?
  - 5y ago

akg_67 5y ago

There is no reason to use R when Excel can do the jobs. It is when Excel can’t handle the task at hand, R shines. I am well familiar with R, but for simple data manipulation, quick chart, pivot table, usually resort to a spreadsheet program. I maintain my Options Trading Journal in spreadsheet where I record all the Options trade I made, positions I hold, P&L, etc. I just can’t imagine doing that in R. R starts with CSV, Excel ends with CSV.

lordnacho 5y ago
How do you store your surface?
jstx1 5y ago
> There is no reason to use R when Excel can do the jobs. It is when Excel can’t handle the task at hand, R shines. I think the problem is more nuanced because Excel can do a lot. There is a large set of use cases and tasks that are possible in Excel but your team would be a lot more efficient and productive using a programming language instead. The trick is to recognise those tradeoffs and it it's not as simple as sticking to Excel as long as things are still possible there.
- sweezyjeezy 5y ago
  Most data scientists I work with just sneer at Excel. Using a programming language is often using a sledgehammer to crack an egg though - sometimes you just want to look at the data in the csv, and I still see people doing this in R/Jupyter - just open it in a spreadsheet, trust me - the UI is better.
sweezyjeezy 5y ago
100% this - I encourage junior data scientists to get more familiar with Excel when they start - R/python are definitely more flexible, but if Excel can sort you out it's always the quickest way.

protomyth 5y ago

R isn't interactive or visual like Excel. Excel's learning curve is a lot less daunting. Also, Excel is ingrained in the accounting culture and well supported by peers. Plus Excel is everyone's default database and form software. None of the article's reasons matter to non-IT people and no IT department has the juice to override the CFO and accountants. Unlike, every other interactive tool that displeases programmers, Excel survives. If you want R to replace Excel you need to build an interactive front end that can do everything Excel can as easily as Excel. Sitting in a class is not and option.

salamandersauce 5y ago
No, but it's far easier to see what the hell an R script is doing versus an Excel file. And there's just a ton more that R can do that Excel can't. More powerful plotting options, more tools like Random Forests or mixed effect models. And has less issues with floating point. Excel is good for accounting tasks because that's what it and other spreadsheet software were originally designed for. Data science tasks are tacked on.
azalemeth 5y ago
> Plus Excel is everyone's default database and form software. This is one of many, many things that is terribly wrong with Excel. It is arguably well suited for neither of those applications. I will continue to do my statistics in R, and be very glad that I do not work somewhere with either CFOs or powerful accountants ;-).
ineedasername 5y ago
Most of what you said only applies when the tasks are accounting related. There's a lot more to Excel, and a lot more than that to R. And just like, in accounting, you're not going to get the CFO to choose R over Excel, in many circles involving data analysis and statistics you won't get to choose anything but R. Also you don't need an interactive front end for R any more than you need an interactive front end for any other programming language. The scope of use is too large to allow for a single GUI. There are some packages that provide a GUI for specific purposes, like rattle, which provides an interface for common data mining tasks and models. I'd you want a pretty good spreadsheet GUI for viewing and modifying data, you can use rhandsontable. Just a few examples. Replacing Excel with R isn't necessary and I think would miss the point & relative strengths of the different tools. Recognizing where each tool is better suited for your needs, and if R is worth the learning curve for your specific needs, is what is important.

icegreentea2 5y ago

Best 'ah ha' moment for why R (or python, matlab, or whatever) might be useful is to have someone do tedious work making a bunch of charts and formatting them in Excel. Oh, and histograms. Oh, or confidence intervals on your linear regression. Pivotcharts (and tables) help quite a bit for removing some of the tedium, but now suddenly it's interactive. Sometimes you want a format where BAM, everything is there laid out. There's nothing to misclick, all the tables and charts are there, you can just tell whoever, "look at the 3rd figure from the top on the 2nd tab". The other things that people usually laugh at excel about (aside from silently changing values... that's just baaaaad) is the gong-show of naming files and versioning. R and friends + CSVs by themselves don't fix that problem. They just make it somewhat easier to solve (as in they play with git better).

eatonphil 5y ago

This week I got a bit more familiar with R while adding it as a scripting language for a data IDE I'm working on (it already supported other languages). It is a very hard language and community to get into! The documentation is very sparse. Library documentation is published as PDF (I guess?) and also very sparse. The default `print` behavior is pretty hard to understand. It's 1-indexed and it took me a while to realize every time I think `array[1]` I should write `array[[1]]`. I can't tell the difference between `<-` and `=`. My guess is that it was probably a great language at some point but is way behind other numeric scripting languages like Julia or Matlab in terms of community attention and language ergonomics. I know it's highly used but other than legacy reasons I'm not sure why you'd want to learn it over Julia. I'm also curious to investigate how the aspects I'm critical of differ in Octave. Edit: totally fair, 1-indexing shouldn't have been a "critique". Lots of languages do that.

Dumblydorr 5y ago
Use the tidyverse and use the cheatsheets for dplyr and the book R for Data Science. If you're trying to use primarily base R, you'll be limited and hamstrung. Tidyverse is the modern framework of choice.
mshron 5y ago
I highly recommend anybody getting into R to skip the base language (which indeed is ancient and full of gotchas) and go straight for the Tidyverse[1]. You can always go back in and learn how to do things the old way later. Over the last decade, the R community has largely standardized around tools like dplyr, ggplot, tibble, purrr, and so on that make doing data science work way easier to reason about. Much more ergonomic. At my company we switched from using Python to using R for most analytical data science work because the Tidyverse tools make it so much easier to avoid bugs and weird join issues than you get in a more imperative programming environment. [1] https://www.tidyverse.org/ https://www.tidyverse.org/
- jstx1 5y ago
  I would recommend the opposite - pick stuff from tidyverse (mostly dplyr and ggplot2) only if you need them. Knowing base R goes a long way on its own.

cabbagehead 5y ago

* R scales to big data, runs on servers * Excel is full of foot guns - e.g. errors in ranges are hidden unless you dig in * R you can write test suite and run it in CI * R integrates nicely with cloud services via reticulate * R’s Shiny is great for interactivity and deploys to the web * R and git support you to collaborate - PR reviews etc * automatable - run analysis in a pipeline when data arrives and publish results on the web * white box / transparency - who knows what Excel does exactly in some of its functions * data science - Excel doesn’t do machine learning, NLP

ad404b8a372f2b9 5y ago

Because R has a million scientific libraries to enable very specialized research workflows and excel doesn't.

WBrentWilliams 5y ago

For me, it comes down to the right tool for the job. Excel (strictly speaking, spreadsheets) is very powerful. It is a grid-based visual programing environment that has an interface simple enough to be groked by its original target audience: accountants and file clerks. What that means to me is that I use excel for "toys" (small data set analysis, rough charts) and ephemeral-yet-shared lists where it is easier to say "evening batch tab, line 3" to communicate which job needs to be updated than try to communicate the same thing in a written request or pull a database table report. I use R where I need more power: automated reports, automated statistics, and more complex analysis where the paradigm of textual code fits better than the paradigm of a 2D grid. I teach R (or python) when I want to give someone more reproduceable tools than a "magic" spreadsheet that, under the covers, is really a contraption held together with sticky tape and positive thinking. We can, and should, teach both. Ideally side-by-side with a constant stream of "why are you doing task X this way?" that is largely missing from formal education.

Zhyl 5y ago

If you need to do something once, use a GUI (Excel). If you need to do something ten times, use hotkeys and shortcuts. If you need to do something a hundred times, write a script (R). I usually use the command line as the example for why writing code and scripts are better than the more intuitive and lower-learning-curve GUIs. If I want to move a file from one folder to another then I just drag it across. Easy. If I want to move a thousand files from one folder to another, I will benefit from learning `CTRL-A` or shift-clicking (slightly more obscure than the 'intuitive' drag each file across individually or drag a large box around them all to select them). If I want to move a thousand files beginning with 'UTR-77' and ending with '.csv' then I would benefit from learning `mv UTR-77*.csv $folder`, but I could still do it manually if I didn't know that was an option. If I want to move a thousand files beginning with 'URT-77' to another folder at a moment's notice or at Thursday 1am, then the only options I really have are scripting. I almost feel like before people learn the 'basic' stuff as outlined at the beginning of the article, they should be shown some 'magic' that is only really possible with scripting so that it's clear from the outset why you wouldn't 'just use excel'.

DantesKite 5y ago
This is a great comment and the line of reasoning for work at different scales makes so much sense.
danuker 5y ago
> I want to move a thousand files beginning with 'UTR-77' and ending with '.csv' 1. Use a file manager with stable sorting [1] (I use Thunar which does this, but I suspect lots of file managers keep the sort order stable). 2. Sort by type. 3. Sort by name. 4. Select the first file named UTR-77. 5. Scroll to the last file, and Shift+Click it. 6. Cut then paste to your desired directory. [1] - https://en.wikipedia.org/wiki/Sorting_algorithm#Stability https://en.wikipedia.org/wiki/Sorting_algorithm#Stability
- bicolao 5y ago
  > 5. Scroll to the last file, and Shift+Click it. With thousands of files, this won't be particularly fast or easy.

tomrod 5y ago

Unless you need the frontier of statistical methods and are unable to code them yourself, I highly recommend not engaging R. While Hadley Wickham has done amazing things to make the R language actually useful, Python and Julia are better for data applications. Also, bindings to the R language exist, obviating the need to be tied into it completely. Thus, even if you need the frontier, you have it available to you.

padthai 5y ago
R is first and foremost an interactive language, and the focus of the libraries, tooling, and community reflects that. I believe working in pandas/sklearn/jupyter ecosystem is much slower (>50%) if you are doing EDA and statistical modelling (ML) than tidyverse/tidymodels/rstudio. The exception is deep learning and adjacent fields (like computer vision). Of course Python is better at everything else, so adding an extra languages might or might be worth the hassle.
tomrod 5y ago
Just as a followup, when asked I also excoriate Python for its gap in typing for data applications. But that gap isn't as wide as the gulf between Python & Julia vs R
dm319 5y ago
I would say R has a beautiful functional approach which is very hard to replicate in python. It is also faster than Python and Julia for tabular/dataframe data. Ggplot is fantastic - I tried to recreate some relatively simple plots (scatterplot with a LOESS line of best fit coloured by group) and failed to do this in Python easily.

intrasight 5y ago

I think a fairer question today would be "Why do we use R rather than M?" https://www.howtoexcel.org/power-query/m-code/ https://www.howtoexcel.org/power-query/m-code/

Closi 5y ago
Absolutely! For power-users of Excel, this article reads as "the reason we don't use Excel is because we don't understand how to use it". For those who aren't as familiar with Excel, all the functionality the author is describing as missing in Excel is actually built into it out of the box - as a feature called Get & Transform (or 'PowerQuery'). You can load in a CSV, manipulate it, do batch processing, and all the steps are visible, editable, resequencable and deletable (showing you the M code behind it!). You can even handle hundreds of millions of records this way. For those who want to learn, I would wholeheartedly recommend this book: https://www.amazon.co.uk/Power-Pivot-Bi-Excel-2010-2016/dp/1615470395 https://www.amazon.co.uk/Power-Pivot-Bi-Excel-2010-2016/dp/1...
- pjmlp 5y ago
  I think it comes from Excel hate and thinking Google sheets are good enough. Many people to this day aren't even aware cells can be named like proper variable names.
- sitrucp 5y ago
  Second using Get & Transform (or 'PowerQuery'). Note it is also Power BI's data integration functionality. (Though was recently disappointed to learn it is not available on Mac.) While I use wide range of data integration and visualization tools, Excel/Power Query with pivot tables & charts is often my go-to for quick data analysis or exploration. Recently used it to retrieve Our World in Data Github hosted csv file data into an Excel file and then click refresh to get most recent data: https://009co.com/?p=1491 https://009co.com/?p=1491

__te__ 5y ago

When you are learning the basics, you are doing very basic tasks. R can do very basic tasks. MS Excel can do very basic tasks. If you never go beyond the basics, there is no compelling reason to use R (or any other programming language) over MS Excel. Programming languages come into their own when the tasks stop being basic, but the learning process usually goes through "Hello, World" first. I mean, if you're just going to print a line of text to the screen, why use Python? Just open an MS Word document. MS Word can even include variables and insert them dynamically!

Traster 5y ago

I think one of the things this is really missing is that Excel fundamentally supports 1 data structure - the table. What if your data isn't a table? What if your data is many tables? What if your data has 3 dimensions? It soon becomes incredibly difficult to map your actual problem into a set of tables. It's actually one of excel's great features - they so strongly constrained the way you represent data that it's easy to reason about.

erdos4d 5y ago

Mainly because they haven't heard of Python apparently.

o_m 5y ago

These are also good points against no-code

fghorow 5y ago

Debugability.

RobinL 5y ago

For me, the biggest reason to use a programming language instead of Excel is its capacity for abstraction, and hence ability to organise code and separate concerns. Attempting to solve a complex analytical problems in Excel is similar to a doctor trying to solve medical problems by reference only to atoms. Instead doctors use a variety of abstractions: organs, cells, enzyme, etc. to understand and explain a problem. By using a programming language, we can develop appropriate abstractions to solve our problem in a way which keeps a lid on complexity. I think this concept does make sense to an advanced Excel user, and can help explain the situations in which they may reach for a different tool. Having made some very complex Excel spreadsheets in the past, I think was aware it can become very difficult to develop them or generalise them further, even before I became a programmer.

aaronrobinson 5y ago

One big thing missing last I worked on Excel/VBA is the ability to write test cases.

dec0dedab0de 5y ago

I don't use R, and didn't read this, but I think the biggest problem with excel is that it hides the code. Figuring out which cell does what, and how, usually means clicking a million places trying to remember what is what It's the same reason I took the doors off my pantry and cabinets

ekianjo 5y ago

Much faster for much data processing workloads too. Like, significantly. And Excel has some stupid limitations like a maximum of a few millions lines.

aselino 5y ago

R is free. Excel is not. Excel encourages messy behavior, just like Jupyter Notebooks. Sometimes during EDA that might be ok. But anything rigorous shouldn’t be confined to just Excel. Or Jupyter imo.

dtnewman 5y ago

i think that part of the answer is that software engineers simply aren’t (usually) familiar with excel. It’s a remarkably complex and powerful program but if you never used it professionally you will never learn its full potential. Yes, it’s easier to do useful stuff as a beginner in excel vs a real programming language but to get really good at it takes time. You might think you know excel because you’ve used it in some of your classes, but to quote my first boss, if you don’t know how to use excel without ever touching a mouse then you aren’t even an intermediate user.

Dumblydorr 5y ago
Meh, if you're diving so deep into excel to learn all the shortcuts, why not just port the data into a scripting language which can do a lot more advanced things more easily and transparently.

croes 5y ago

Because in Excel 1900 is a leap year.

indymike 5y ago

The reason that we use R or Python or any programming language over applications is integration, capacity, performance or control of process. When I'm asked to replace a spreadsheet it is usually based on the sheet being to large to be performant or it is very buggy, and can't be relied on. I.e. CFO got crushed in a meeting because of a bad excel/googel sheets formula. Lately, I've been thinking about what the "Rust of spreadsheets" would be like... It's really a big problem now.

dan-robertson 5y ago

The article is a bit one sided. I think it ought to acknowledge the good things about excel. For example it lets you more directly interact with the data and see intermediate results.

hermitcrab 5y ago

R and Excel are not the only alternatives. There are also visual, node-based data transformation tools such as Easy Data Transform, Knime and Alteryx. They can do 95% of what R can do, but are much easier to get started with.

deleted 5y ago

[deleted]

henvic 5y ago

I had to "learn" R during my CS course, and I was really impressed by it. We used RStudio and Shiny to build this: https://henvic.shinyapps.io/accidents https://henvic.shinyapps.io/accidents https://github.com/henvic/accidents https://github.com/henvic/accidents

nomilk 5y ago

Another big one for me is speed. Almost all the popular wrangling libraries in R (tidyverse, data.table) use c++ under the hood and are lightning fast. Whereas excel can chug away for five minutes operating across a few tens or hundreds of thousands of rows; R can often do the same operations on a few millions or tens of millions of rows in a few seconds.

ohgreatwtf 5y ago

if you need to do something, use python. if you need to do it while using complex math, use R. if you need to do it in real-time, use c. if you need to do it reliably with memory constraints, use ada. if you need it to work under arbitrary and difficult conditions and continually without pausing for weeks or years on end, use rust. for everything else, there is javascript.

SubiculumCode 5y ago

The article completely misses the point of R. Go ahead. Try and run a mixed effects model with random slopes in Excel, and plot the random effects and the fixed effects in a spaghetti plot. Let me how that went.

RosanaAnaDana 5y ago

I don't think there is any excuse for doing analysis in excel at this point, and if any analyst came to me with either a) no coding skills or b) no interest in developing them, I'd laugh them out of the room. My BiL is this breed. He's a financial analyst. He's got a hilarious mental block about using any kind of coding language in his work to the point that he actually developed a web-scraping app in excel. Don't ask me how. But jimmeny-christmas, use the right too for the damn job.

TomJansen 5y ago

For a program which combines R with an Excel/SPSS interface I can recommend Jamovi [1] [1] https://www.jamovi.org/ https://www.jamovi.org/

iNane9000 5y ago

Also -Open source is better for serious work -Excel is notoriously buggy and shouldn’t be used for anything important, yet it is and the costs are enormous. Many articles on this.

deleted 5y ago
[deleted]

culebron21 5y ago

I would have been surprised and intrigued if the title read "Why we use R instead of Python". I've used Excel myself as a student in 2003-05, when R or Python weren't known. And now I sometimes teach Economics students, and they all work either in R or in Jupyter Notebooks. Many of them routinely do what was unthinkable -- process 100s of thousands of records, do geospatial queries and joins, etc.

pg_bot 5y ago

I would add testing, collaboration, and complex use cases. There's no good way to check if you've made a mistake in the logic of your excel spreadsheet. You can easily test your R code. If you need to work with multiple people excel is a nightmare. It works fine when one person needs to build something for themselves or just to show to someone else. When you get two or more people working together it's like sharing a keyboard with someone else. Excel can do a lot of things, but there is a limit to what it can do. R or Python is typically what you would reach for when you need to do something beyond what excel is capable of.

chrisgd 5y ago

Most ppl who use excel would benefit from Access and creating some repeatable processes. A lot of the sorting and ranking from excel could be done in linked tables and improve the repeatability

ISL 5y ago

I have more than once found myself answering that sort of question. I gave similar reasoning. In 2020, for a grassroots PPE-relief organization, however, I found that I had sometimes been mistaken. What our group managed to achieve by eschewing (eventually as a watchword) fancy tools and building our entire backend around Google Sheets was speed. Moreover, I learned along the way that simple database tools are sometimes more-efficient or faster than anything I'd have written myself. I had been blinded by the GUIs -- the (frequently correct) notion that GUIs are generally inferior in the long run to scripting/programming had blinded me to the very idea that perhaps another tool could be superior. As my career takes me in new directions, I'm presently reprising that experience, this time with SQL. Physicists rarely use it, so we have no idea what it can do. The syntax looks old/quirky/muddy, but the tools behind it are extremely powerful. If you're great at R, consider sitting down with someone whom you know is just crushing problems with Excel. I'm pretty sure you'll both learn something useful.

ogig 5y ago

I have some objections, as someone who doesn't use R but does quite a lot of Excel. The author seems to miss PowerQuery, data model, and PowerPivot functions. "Visibility: How do you see the code inside an Excel document? How do you tell exactly what is going on? You have to go clicking through cells, or reverse engineer what settings a graph has." PowerQuery it's a functional way of transforming data step after step. You can see the code/function of each step and it's very easy to reason about those functions. You can transform data to the format you want, and do calculations on it before it enters the spreadsheet. One of the main advantages is that it's easy! I've taught non programmers to reliably use this tool. "Repeatability: [...] With R, you just change read.csv("1.csv") to read.csv("2.csv") and the exact same calculations are run on two different data sets." Again, with PowerQuery this is doable, and a normal procedure on my day to day. You change the file parameter PowerQuery will use on step 1, and the rest of steps will follow. "Batch processing: Related to the above, you can read every CSV in a directory and produce a graph for each of them. You can read data from an API and run the same process on it that you did yesterday." You can use PowerQuery on folders, it can take a set of files and transform or aggregate them all at once. In my opinion Excel has gotten pretty powerful after data model and powerquery were added, I think around excel 2013. I barely use cell functions anymore; data model and pivot tables make for robust spreadsheets that are easy to reason about. I know a decent programmer can do most of it in many other ways, but the accessibility of Excel is amazing. The biggest defect Excel has for me at the moment is control change tracking. Wish I could git Excel changes.

derefr 5y ago

Is there a tool to transpile an Excel workbook to R source code? It seems like a plausible-enough thing to do. • Put all the static data first in one section, broken into tables with PDF’s word-boundary logic (the one that allows you to highlight text, despite it being a bunch of individually laid-out graphemes) • reverse-postorder (topological sort) the formula cells’ definitions, grouping them into “stanzas” by which “tables” of static data they’re transitively touching. The result would read a lot like the definition of an expert system in Prolog. Facts, then predicates.

techlatest_net 5y ago

Shameless plug, if you want to use R-Studio and R on AWS,GCP,Azure, we provide VMs with pre-installed R-Studio and popular R libraries which are far cheaper than the official R-Studio . Below are the links: AWS : https://aws.amazon.com/marketplace/pp/prodview-brc4ybuoee6he?qid=1626027247189 https://aws.amazon.com/marketplace/pp/prodview-brc4ybuoee6he... GCP : https://console.cloud.google.com/marketplace/product/techlatest-public/r-developer-suit https://console.cloud.google.com/marketplace/product/techlat... Azure : https://azuremarketplace.microsoft.com/en-us/marketplace/apps/techlatest.r-developer-suit?tab=Overview https://azuremarketplace.microsoft.com/en-us/marketplace/app... Support & Documentation : http://www.techlatest.net/support/r-studio-support/ http://www.techlatest.net/support/r-studio-support/

schwartzworld 5y ago

Reason I use scripting languages over excel: I don't want to pay for excel, or a machine that will run it.

ogogmad 5y ago
There are open-source spreadsheet programs, like LibreOffice Calc. I occasionally use it (Calc) to do things like taxes. I find it quite easy to use, personally, but YMMV.

supercanuck 5y ago

Excel doesn't scale. period. full stop.

beefield 5y ago

Only slightly tongue in cheek: Removing the ability to save workbooks would still allow people use excel where it shines while not allowing people and organizations shoot themselves in foot by using subpar processes for anything you actually need to be confident about.

coss 5y ago

One problem we have with excel is merging documents. I wish there was a good tool to use that worked well with merging.

deleted 5y ago

[deleted]

teleforce 5y ago

Beyond Spreadsheet with R book for those who are venturing into R from Excel: https://www.manning.com/books/beyond-spreadsheets-with-r https://www.manning.com/books/beyond-spreadsheets-with-r

Avalaxy 5y ago

As a counter point to his 'Repeatability' point: in Excel you can treat your workbook as a simple output for your data files. Let's say I have my data in a CSV file (like the example), I can just load the data from the CSV file and use Power Query to apply any transformations and calculations to this data. I could then simply swap out the CSV file for another CSV file, and all the transformations and calculations would be applied to it just the same. This is also how it works in Power BI, but I guess it's a bit of a different way of working than how most people are used to work.

Qem 5y ago

On the accuracy of statistical procedures in Microsoft Excel 2007: https://materias.df.uba.ar/l1aa2017c2/files/2017/08/excel2007.pdf https://materias.df.uba.ar/l1aa2017c2/files/2017/08/excel200...