Better Than Excel: Use These R Shiny Packages Instead

Excel has its limitations regarding advanced statistics and calculations, quality and version control, user experience, and scalability. Switching to a more sophisticated data analysis tool or dashboard is often a great solution to these limitations. What's more, transferring your Excel sheet to an R Shiny app can be the easiest way to create an enterprise-ready dashboard. In this post, we present 8 Shiny alternatives for the table-like data that are better than Excel. <blockquote>Can't decide between Tableau and R Shiny? <a href="https://appsilon.com/tableau-vs-r-shiny/">Our detailed comparison might simplify the decision</a>.</blockquote> You’re probably aware of Shiny’s cool interactive <a href="https://shiny.rstudio.com/gallery/" target="_blank" rel="noopener noreferrer">plot and charts features</a> that are well ahead of what you can do in Excel. What may still prevent you from switching from Excel to a more advanced Shiny dashboard is the fear of missing the beloved Excel functionalities to work with table-like data. Don’t worry! It is super easy to implement and extend them using Shiny. The most commonly used table widgets in Shiny are <a href="https://rstudio.github.io/DT/" target="_blank" rel="noopener noreferrer"><b>DT</b></a> and <a href="http://jrowen.github.io/rhandsontable/" target="_blank" rel="noopener noreferrer"><b>rhandsontable</b></a>. We'll first take a deep dive into their features but also look at some other packages strictly dedicated to helping with popular spreadsheets tasks. <em><strong>Updated</strong>: March 29, 2022.</em> Table of contents: <ol><li><a href="#anchor-1" target="_blank" rel="noopener noreferrer">Editable tables</a></li><li><a href="#anchor-2" target="_blank" rel="noopener noreferrer">Conditional formatting</a></li><li><a href="#anchor-3" target="_blank" rel="noopener noreferrer">Sorting and filtering</a></li><li><a href="#anchor-4" target="_blank" rel="noopener noreferrer">Drag &amp; drop pivot tables</a></li><li><a href="#anchor-5" target="_blank" rel="noopener noreferrer">Reacting to selection</a></li><li><a href="#anchor-6" target="_blank" rel="noopener noreferrer">Expandable rows</a></li><li><a href="#anchor-7">Data visualization</a></li><li><a href="#anchor-8">Connecting to external data sources</a></li></ol> <hr /> <h2 id="anchor-1">1. Editable tables</h2> A basic reason for using a spreadsheet is the simplicity of data manipulation. Displaying data is not always enough. Content may require spell checking, fixing, and adding rows or columns. The closest solution to what can be found in spreadsheets is <a href="http://jrowen.github.io/rhandsontable/"><b>rhandsontable</b></a>. It contains all of the cool spreadsheet features like autocomplete, selecting a value from a list, adding rows/columns, sparklines in cells, freezing, comments, input validation, read-only mode, and so on. <script src="https://gist.github.com/darioappsilon/ca43b0bdd6c0178ad44b3bda7ae63111.js"></script> <img class="size-full wp-image-12378" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b022e6dde585f524535b5c_gif1-rhandsontable-1.gif" alt="Image 1 - Source: rhandsontable on GitHub" width="480" height="274" /> Image 1 - Source: <a href="https://jrowen.github.io/rhandsontable/" target="_blank" rel="noopener">rhandsontable on GitHub</a> A similar solution is available in <a href="https://swechhya.github.io/excelR/"><b>excelR</b></a>. The package is worth testing as it contains many interesting solutions such as radio selection inside tables and multiple well-known Excel functions like <i>SUM</i> presented below. The package also allows users to easily manipulate cells with actions like resizing, merging, or switching row/column positions. Nested headers are also useful as they can organize your data. Implementing the solution in Shiny is easy and intuitive with well-known <i>render/output </i>dedicated functions. There are two downsides with <b>excelR</b> at the moment -- cloning formulas between columns, and calculation approximations, which do not work as one would expect. <script src="https://gist.github.com/darioappsilon/72851839e7173fd0cf2e471031f398f5.js"></script> <img class="size-full wp-image-12380" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b022e8855531924257b20b_gif2-1.gif" alt="Image 2 - Source: excelR on GitHub" width="480" height="104" /> Image 2 - Source: e<a href="https://swechhya.github.io/excelR/" target="_blank" rel="noopener">xcelR on GitHub</a> The <a href="https://rstudio.github.io/DT/"><b>DT</b></a> package has a lot of great features and is a great option when heavy data editing is not the main goal. And as you can see in the gif below, tables implemented with <b>DT</b> look really nice. It has less functionality than <b>rhandsontable</b> though, basically just allowing the user to replace the values inside cells when double-clicked, without validating the values typed in. Some columns may be restricted to be read-only. <script src="https://gist.github.com/darioappsilon/81d3c27577d5136f5b4796cbed7cf5a6.js"></script> <img class="size-full wp-image-12382" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b022e9df1312180459e7f1_gif3-DT-1.gif" alt="Image 3 - Source: DT on GitHub" width="480" height="212" /> Image 3 - Source: <a href="https://rstudio.github.io/DT/" target="_blank" rel="noopener">DT on GitHub</a> A possible workaround for <b>DT</b>’s limited editing functionality is a more advanced <a href="https://github.com/jbryer/DTedit"><b>DTedit</b></a> package. It comes with a pleasing interface (modal dialog) for editing single table rows as well as buttons to add, delete, or copy data. The package is currently only available on GitHub, but we will keep our fingers crossed for its expansion and increase in popularity. <script src="https://gist.github.com/darioappsilon/f3c54959e89f19eb56ff7ebcd0891f39.js"></script> <img class="size-full wp-image-12384" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b022ebc51a6e88355a383a_gif4-DT-1.gif" alt="Image 4 - Source: DTedit on GitHub" width="480" height="314" /> Image 4 - Source: <a href="https://github.com/jbryer/DTedit" target="_blank" rel="noopener">DTedit on GitHub</a> <h2 id="anchor-2">2. Conditional formatting</h2> Conditional formatting is a super useful tool for getting a quick overview when dealing with tons of values. Both <b>rhandsontable</b> and <b>DT</b> allow users to format cells according to their values. If your highest priority for your application is beautiful data presentation, then the package <a href="https://github.com/renkun-ken/formattable"><b>formattable</b></a> is worth checking out. The formatting interface is more user-friendly than in <b>rhandsontable</b> and it is based on R functions, not pure javascript code. Besides working on tables, it also contains the functionality of formatting R vectors which might be useful when presenting results of pure R analysis. <script src="https://gist.github.com/darioappsilon/b3245721eba13e289ad61100f0c55d10.js"></script> <img class="size-full wp-image-12376" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b022eec497a144bd9e6675_formattable-1.webp" alt="Image 5 - Source: formattable on GitHub" width="975" height="350" /> Image 5 - Source: <a href="https://github.com/renkun-ken/formattable" target="_blank" rel="noopener">formattable on GitHub</a> <h2 id="anchor-3">3. Sorting and filtering</h2> Sorting and filtering are also crucial when examining a huge dataset. In the <b>rhandsontable</b> package, sorting columns can be enabled by a single parameter, however filtering is not implemented inside the feature and may require adding some extra Shiny components. On the other hand, in <b>DT</b>, column sorting is available by default as well as global search. Enabling column filtering is as easy as adding a single parameter (filter = top/bottom, depending on where the filters should be placed).  <script src="https://gist.github.com/darioappsilon/9dc819d06f605403e1cb9c9719a8152f.js"></script> <img class="size-full wp-image-12386" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b022ee77066e9c82befe17_gif6-1.gif" alt="Image 6 - Source: DT on GitHub" width="480" height="258" /> Image 6 - Source: <a href="https://rstudio.github.io/DT/" target="_blank" rel="noopener">DT on GitHub</a> <h2 id="anchor-4">4. Drag & drop pivot tables</h2> Excel users love pivot tables. Allowing the users to create their own stories based on data is an excellent feature - sometimes the valuable info is only generated when looking at the data from the right angle. For a plug-and-play pivot table, we recommend using the <a href="https://github.com/smartinsightsfromdata/rpivotTable"><b>rpivotTable</b></a> package. As you can see in the gif, it is super easy to produce tables and manipulate the aggregation variables with drag & drop. You can also filter by specific values and/or choose which variable should be calculated based on the selected function - like presented below <i>sum </i>and <i>sum as a percentage of the total</i>. Quickly switching from table to different types of (interactive!) charts is a great bonus. <script src="https://gist.github.com/darioappsilon/4b788971d8cb79e06a175c0e45a313c5.js"></script> <img class="size-full wp-image-12388" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b022f06dd6718f6c1ee737_gif7_rpivot-table-1.gif" alt="Image 7 - Source: rpivotTable on GitHub" width="480" height="244" /> Image 7 - Source: <a href="https://github.com/smartinsightsfromdata/rpivotTable" target="_blank" rel="noopener">rpivotTable on GitHub</a> If you would like to combine pivoting with other features, a combination of <a href="https://github.com/ayayron/shinydnd"><b>shinydnd</b></a> and <a href="https://rstudio.github.io/DT/"><b>DT</b></a> Custom Table Containers as well as data manipulation is needed. Nevertheless, the results can be amazing. Maybe we’ll get into that in a future post. Let us know in the comments if this is interesting to you. <h2 id="anchor-5">5. Reacting to selection</h2> The usual scenario in dashboards applications is reacting to user selection and continuing to work on a selected element. When it needs to be a key feature of the application, then the <a href="https://rstudio.github.io/DT/shiny.html"><b>DT</b></a> package is a great choice. It is easy to implement the logic for reacting to user cell/row/column selection.    The sky's the limit! Options range from custom edit data tools to going deep into nested tables. Or as presented in the gif below, you see the graph dynamically reacts to user selection in the table. <script src="https://gist.github.com/darioappsilon/e897bf4f3c423db81a42a7985d35d2f8.js"></script> <img class="size-full wp-image-12390" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b022f145854de233d331dc_gif8-dt-react-on-selection-1.gif" alt="Image 8 - Source: DT in Shiny on GitHub" width="480" height="268" /> Image 8 - Source: <a href="https://rstudio.github.io/DT/shiny.html" target="_blank" rel="noopener">DT in Shiny on GitHub</a> <h2 id="anchor-6">6. Expandable rows</h2> Expandable rows are a nice, extra feature that allows you to hide (in an elegant way) additional information and bring the crucial part to the top. This is another feature that does not exist in spreadsheets. Expandable rows are also useful in presenting database-like structures with one-to-many relations. It requires a little javascript magic in <b>DT</b> for now, but the various examples (including child rows) are easy to follow. <script src="https://gist.github.com/darioappsilon/53dc646af4943755e7c452957276042d.js"></script> <img class="size-full wp-image-12392" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b022f35b956c19cd8f34b4_gif9-expandable-rows-dt-1.gif" alt="Image 9 - Source: Extending DT child row example" width="480" height="330" /> Image 9 - Source: <a href="https://www.r-bloggers.com/2018/04/extending-dt-child-rows-example/" target="_blank" rel="noopener">Extending DT child row example</a> <h2 id="anchor-7">7. Data visualization</h2> Let's face the facts - looking at endless tables day in and day out is no fun, especially for business users. What's easier on the eyes is the visual representation of these tables. The good news is - R offers more visualization packages than we can list. The de-facto standard package is <a href="https://ggplot2.tidyverse.org/"><strong>ggplot2</strong></a>, and it's used to produce publication-ready charts that are better than Excel. The example below shows you how to use this package to visualize a scatter plot of MPG vs. HP from the Mtcars dataset, and also how to style and add captions to the plot: <script src="https://gist.github.com/darioappsilon/2dce4489a85eb39a9ed3b10ada7577fc.js"></script> <img class="size-full wp-image-12370" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b2706ea7d7937753091779_10.webp" alt="Image 10 - Data visualization example" width="2652" height="1598" /> Image 10 - Data visualization example <blockquote>Are your data visualizations an eyesore? <a href="https://appsilon.com/ggplot-scatter-plots/">Our complete guide to stunning scatter plots in R can help</a>.</blockquote> <h2 id="anchor-8">8. Connecting to external data sources</h2> You won't always have the luxury of working with local data. More often than not, you'll have to connect to a database of some sort, or even fetch the data from a REST API. The example below shows you how to do the latter - it connects to a dummy <a href="https://gorest.co.in/">REST API</a>, pulls the data by making an HTTP GET request with <a href="https://cran.r-project.org/web/packages/httr/vignettes/quickstart.html"><strong>httr</strong></a> package, and converts data from JSON to data.frame - all of that in five lines of code: <script src="https://gist.github.com/darioappsilon/a41df8b420b3c2a65fea1c4e2c6cd993.js"></script> <img class="size-full wp-image-12372" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b270baa7d7937753095b81_11.webp" alt="Image 11 - REST API communication example" width="1894" height="758" /> Image 11 - REST API communication example <hr /> <h2>Summary of Better Than Excel - Smooth transition to R Shiny</h2> You may have felt that if you switched from Excel to Shiny, you would be limited in the table data feature set. We hope you can see that Shiny offers a comparable feature set to Excel as well as exciting new possibilities! <ul><li>When to stick with Excel and when it's time for a change? Take a look at Filip Stachura’s article "Excel Is Obsolete" which addresses this question from the architectural point of view. </li><li>Curious about making the leap from Excel to R Shiny, but you don't have any experience with R? Read How to Switch from Excel to R Shiny: First Steps. </li><li>Are you considering using R Shiny for your enterprise solution? These 6 lessons in Shiny app development are a great way to prepare your business for digital transformation.</li></ul>