Introduction
Excel is a very important tool to interactively work with data in corporate environments. Thanks to its statistical capabilities and reproducibility R can be a powerful companion to leverage and automate Excel workflows.
- Excel for data science: Pros and Cons
- How to use R as a companion to Excel
- Learn about R packages to work with Excel files
Excel Pros and Cons
Excel still is the most important productivity application used in corporate environments to work with data. Spreadsheets can be valuable tools to setup interactive calculations and communicate results. Excel is available in nearly every corporate environment and does not require any extra IT-effort to install as compared to other tools.
However, using Excel for involved calculations and data science projects can be tricky. Most importantly, it mixes a spreadsheet user interface with data and calculations which makes it very hard to reproduce results. Creating automated reports based on Excel can therefore be very tricky. Excel can only handle 1 million rows in one spreadsheet and can become very slow for large data sets including many calculations. Last but not least, compared to other programming languages like R, it does not provide a large collection of statistical models and cannot be easily used for advanced analytics.
Excel vs. R Summary
Based on the arguments in the previous section we compare Excel vs. R based on the following criteria:
- Availability: Is the tool easily available in corporate environments?
- Interactivity: Is it easy to create interactive calculations and visualizations? In R: through Shiny.
- Reproducibility: Is it easy to reproduce results in the tool, e.g. using different/updated data sets?
- Large Datasets: Does the tool handle large data sets?
- Automation: Is it easy to automate calculations and reports?
- Advanced Analytics: Does the tool support advanced analytics?
The table below gives an indication how Excel and R perform based on the above criteria:
| Feature | Excel | R |
|---|---|---|
| Availability | ✅ ✅ | ✅ |
| Interactivity | ✅ ✅ | ✅ |
| Reproducibility | ❌ ❌ | ✅ |
| Large Datasets | ❌ | ✅ |
| Automation | ❌ | ✅ ✅ |
| Advanced Analytics | ❌ | ✅ ✅ |
Using R as a companion to Excel
Due to some drawbacks of MS Excel and its proprietary nature many data scientists have objections using it. R also had a rather awkward relationship with MS Excel throughout its history. Due to its open-source background the “best practice” approach interacting with Excel files has long been to export the contents to comma-separated files (CSV). However, this approach requires manual interventions and is far from practical.
Since most data science projects are interdisciplinary among different departments and stakeholders we recommend to use the best tool for the job—even if it requires to mix different tools. Currently, Excel can be integrated with R through the following workflows and packages:
- Reading from Excel: Read Excel files as data input for calculations in R. Use the readxl package thanks to its performance and ease-of-use.
- Writing Excel Files: Writing Excel files as results from R calculations/scripts. For large datasets the writexl package is recommended. If the Excel files need to be styled differently including charts, formulas, fonts, etc. we recommend using the openxlsx package.
- Hybrid: Excel can be directly integrated with R using the RExcel package which even allows to use cell formulas for R functions. However, it seems that the package is not being further developed and it requires all users to have the software installed.
Using one of the above R package integration we can have the best of both worlds and seamlessly integrate advanced statistical techniques into Excel sheets and workflows.
Coming up Readxl package…