How to Switch from Excel to R Shiny: First Steps

Estimated time:
time
min

If you're still using Excel or Google Sheets for business, you might already know that <a href="https://appsilon.com/excel-is-obsolete-here-are-the-top-2-alternatives-from-r-and-python/" target="_blank" rel="noopener noreferrer">Excel is obsolete</a> for many business use-cases. But how do you switch from Excel to <a href="http://appsilon.com/shiny" target="_blank" rel="noopener noreferrer">R Shiny</a> which is a much better alternative? Getting started is easier than you might think, especially if you're already an Excel power user. This article will walk you through a sample migration of an analytics tool built with Excel and Google Sheets to a dashboard built with R Shiny. We'll show you how to prepare the data for migration, create a simple R Shiny app, view/filter tables in R Shiny, modify a table using SQL, and also how to add interactive filters using the library ShinyWidgets. You don't need prior experience with R to follow this tutorial. <strong>Updated</strong>: April 12, 2022. <ul><li><a href="#excel-is-problematic">Why Excel is problematic for businesses</a></li><li><a href="#excel-alternatives">Alternatives to Excel: R and Python</a></li><li><a href="#migration">Migration from Excel to R: getting started</a></li><li><a href="#first-shiny-dashboard">Creating your first Shiny dashboard</a></li><li><a href="#migration-shiny-widgets">Migration: SQL and ShinyWidgets</a></li><li><a href="#learn-more">Learn more</a></li></ul> This article shouldn't take you more than 15 minutes to complete if you're following along with the code - The final Shiny dashboard has only 45 lines of well-formatted code!. You can download <a href="https://appsilon.com/wp-content/uploads/2020/09/j_h2.csv" target="_blank" rel="noopener noreferrer">the dataset we use</a> to try it yourself. <hr /> <h2 id="excel-is-problematic"><span data-preserver-spaces="true">Why Excel is a bad choice for businesses</span></h2> Excel has been an excellent tool for decades due to its relative intuitiveness and the WYSIWYG (What You See Is What You Get) user interface. Still, running an important business process in an Excel workbook in 2022 is a big mistake, as there are a variety of more stable and sophisticated tools readily available. Here are the reasons why Excel is a bad choice for businesses: <ol><li>Excel is prone to human errors.</li><li>It's not possible to fully version control Excel.</li><li>It's not safe to keep critical information in Excel.</li></ol> For more details on each point, read along. <span data-preserver-spaces="true"><strong>Excel is prone to human errors.</strong> Everyone with access to an Excel document can edit all of its features, which can lead to unintentional changes or the removal of crucial functions. As a result, you can easily end up with a broken spreadsheet whenever a user accidentally (or intentionally) changes a parameter. This is a serious issue because it is currently not possible to fully <strong>version control</strong> Excel. This means that you cannot effectively keep track of changes in an Excel spreadsheet and revert to a previous version if something breaks.</span> <span data-preserver-spaces="true">To put it simply -<strong> it is not safe to keep a company’s critical information in Excel workbooks</strong>, especially when it comes to business analytics<strong>.</strong> Excel can still be useful as an alternative tool to SQL-based solutions to create simple 'databases' as it is very intuitive, and anyone can contribute. However, in the long run, Excel is just too fragile to handle complex business analyses.</span> <blockquote><span data-preserver-spaces="true">Learn more: </span><a class="editor-rtfLink" href="https://appsilon.com/excel-is-obsolete-here-are-the-top-2-alternatives-from-r-and-python/" target="_blank" rel="noopener noreferrer"><span data-preserver-spaces="true">Excel is Obsolete: Here Are the Top 2 Alternatives from R and Python</span></a></blockquote> <h2 id="excel-alternatives"><span data-preserver-spaces="true">Alternatives to Excel: Python and R</span></h2> <span data-preserver-spaces="true">To effectively plan your next business move, you need access to features that Excel doesn't offer, such as integration with machine learning models. You also might need a scalable way of connecting to external data sources via APIs. Selecting a flexible and reliable tool is crucial, due to the variety of analytical approaches that a modern business needs to make decisions.</span> <span data-preserver-spaces="true">Therefore, it is necessary to move as many analytical operations as possible away from Excel. There are plenty of better-equipped tools for data analysis and data visualization, and they should not be too difficult to master for someone already proficient in Excel. </span> <span data-preserver-spaces="true">Python and R are two of the most popular beginner-friendly programming languages. Many non-IT people find R easier and more intuitive to use than Python. This is good news because R and R Shiny alone can cover the majority of business operations stored in Excel</span><span data-preserver-spaces="true"> and they really can open the door to modern, ground-breaking data analysis.</span> <blockquote><span data-preserver-spaces="true">Learn More: </span><a class="editor-rtfLink" href="https://appsilon.com/forget-about-excel-use-r-shiny-packages-instead/" target="_blank" rel="noopener noreferrer"><span data-preserver-spaces="true">Better than Excel: Use these R Shiny Packages Instead</span></a></blockquote> <h2 id="migration"><span data-preserver-spaces="true">Migration from Excel to R: getting started</span></h2> <span data-preserver-spaces="true">Every migration starts with proper data preparation. This step might take some time if a company has a lot of Excel workbooks. The most important thing is to extract original data from analyzed data, create tables, and save them in a CSV format. If you're just starting - start small. Pick one or two CSVs that move the needle for a start. In almost any use case, you don't have to start with a complete dataset. </span> <span data-preserver-spaces="true">Remember:</span> <ul><li><span data-preserver-spaces="true">When dealing with multiple sheets in a workbook, you need to combine them into one or divide them into different CSVs.</span></li><li><span data-preserver-spaces="true">When combining all sheets from the workbook into one table, make sure all sheets have the same number of columns and the same column names.</span></li></ul> <span data-preserver-spaces="true">It would make sense to switch from CSV to SQL in the future, but using CSV is not a dealbreaker in the beginning. </span><span data-preserver-spaces="true">Moreover, you’ve likely performed some useful and effective analytical operations within Excel. Don’t get rid of them just yet. By all means, analyze/verify these operations one more time, and describe their logic in detail (to recreate it later in R).</span> <h3><span data-preserver-spaces="true">Sample case: Job hunt analysis</span></h3> <span data-preserver-spaces="true">A couple of years back, one of our consultants was searching for a full-time job, and they started tracking application processes to see a more accurate picture of their career prospects - primarily to find out which companies/industries find a single profile interesting. Information about all applications sent in 2019 and 2020 were stored in two Excel sheets (original data) and one in Google Sheets (analysis). Let’s take a quick preview of this data.</span> Original data: <img class="size-full wp-image-12413" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e3a597077171c050be_original-data-source.webp" alt="Image 1 - Original source data" width="1814" height="1098" /> Image 1 - Original source data Analysis: <img class="size-full wp-image-12411" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e36cd8f7584c8364a9_original-analysis-dataset.gif" alt="Image 2 - Original analysis data" width="1528" height="580" /> Image 2 - Original analysis data <span data-preserver-spaces="true">As you can see in the screen recording above, the dashboard works fine. However, there are some issues we encountered while using and maintaining it:</span> <ul><li><span data-preserver-spaces="true">When sharing the analysis with others, it is impossible to filter data without granting editing rights to users.</span></li><li><span data-preserver-spaces="true">We can’t select multiple options in filters, e.g. we are not able to check the results for Poland and Spain simultaneously.</span></li><li><span data-preserver-spaces="true">We need to maintain four different tables and functions that show almost the same thing.</span></li></ul> <span data-preserver-spaces="true">Because of the reasons above, it makes sense to migrate this dashboard to R Shiny and see if these problems can be eliminated.</span> <h3><span data-preserver-spaces="true">Data preparation</span></h3> <span data-preserver-spaces="true">In this case, data preparation was fairly easy. Two sheets were merged into one, some values got replaced (YES -&gt; 1, NO -&gt; empty cell) using <em>Find and Replace</em>, and the data was saved to a CSV file:</span> <img class="size-full wp-image-12417" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e4afc0ff7c657b4856_saving-excel-workbook-as-csv.webp" alt="Image 3 - Saving Excel workbook as a CSV file" width="1482" height="592" /> Image 3 - Saving Excel workbook as a CSV file <span data-preserver-spaces="true">The next logical step is describing the features we need to migrate from Excel to R Shiny. The following table summarizes the steps pretty well:</span> <img class="size-full wp-image-12405" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e648c3845e4b4de96f_features-needed-to-migrate-from-excel-to-r-shiny.webp" alt="Image 4 - Features needed to migrate from Excel to R Shiny" width="1024" height="499" /> Image 4 - Features needed to migrate from Excel to R Shiny <h3><span data-preserver-spaces="true">Loading a dataset</span></h3> <span data-preserver-spaces="true">To start the migration process, we </span><a class="editor-rtfLink" href="https://rstudio.com/products/rstudio/download/" target="_blank" rel="noopener noreferrer"><span data-preserver-spaces="true">downloaded RStudio</span></a> (a free development environment for R from our partner <a href="https://appsilon.com/appsilon-data-science-is-now-an-rstudio-full-service-certified-partner/" target="_blank" rel="noopener noreferrer">RStudio, PBC</a>)<span data-preserver-spaces="true">, found the CSV file we wanted to use, and imported it into RStudio:</span> <img class="size-full wp-image-12409" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e73c5d0379a4fa2fa8_loading-a-dataset-in-rstudio.gif" alt="Image 5 - Loading a dataset in RStudio" width="1678" height="908" /> Image 5 - Loading a dataset in RStudio <span data-preserver-spaces="true">After a successful import, the file appeared in the <em>Global Environment</em>. R had no problem with recognizing the CSV table.</span> <blockquote>New to R or want to speed up your workflow? Check out our favorite <a href="https://appsilon.com/rstudio-shortcuts-and-tips/" target="_blank" rel="noopener noreferrer">RStudio Shortcuts and Tricks</a>.</blockquote> <h3 id="first-shiny-dashboard"><span data-preserver-spaces="true">Creating your first R Shiny dashboard</span></h3> <span data-preserver-spaces="true">Let’s start simple with something that remotely resembles the original dashboard. The main goal is to make a simple app that displays the source data and filters it by <em>Job Category</em>.</span> <span data-preserver-spaces="true">It’s important to understand the two main components of an R Shiny app - the <em>UI</em> (User Interface) and the <em>server</em>. UI is a graphic layout of an app - everything the user sees on a webpage. The </span><a class="editor-rtfLink" href="https://shiny.rstudio.com/articles/shiny-server.html" target="_blank" rel="noopener noreferrer"><span data-preserver-spaces="true">server</span></a><span data-preserver-spaces="true"> is the backend of an application. The app is stored on the computer that runs R in the form of a page that can be viewed in a web browser.</span> <blockquote><span data-preserver-spaces="true">If you are a beginner with R Shiny, get started with our </span><a class="editor-rtfLink" href="https://appsilon.com/video-tutorial-create-and-customize-a-simple-shiny-dashboard/" target="_blank" rel="noopener noreferrer"><span data-preserver-spaces="true">Video Tutorial to Create and Customize a Simple Shiny Dashboard</span></a></blockquote> <span data-preserver-spaces="true"><strong>Note:</strong> To share the R Shiny app with others, you either need to send them a copy of a script or host this page via an external web server.</span> <span data-preserver-spaces="true">To start, let’s use the most basic Shiny app template:</span> <script src="https://gist.github.com/darioappsilon/73f0bb6cedcbc73997679370a0759df4.js"></script> <h3><span data-preserver-spaces="true">Defining input and output</span></h3> <img class="size-full wp-image-12401" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e96738007ca3671aee_basic-r-shiny-app.webp" alt="Image 6 - Basic Shiny app" width="2808" height="1114" /> Image 6 - Basic Shiny app <span data-preserver-spaces="true">Input is everything the user can interact with on a website. To name a few: </span> <ul><li><span data-preserver-spaces="true">select boxes -  <code>selectInput()</code></span></li><li><span data-preserver-spaces="true">radio buttons -  <code>radioButtons()</code></span></li><li><span data-preserver-spaces="true">sliders -  <code>sliderInput()</code></span></li><li><span data-preserver-spaces="true">date ranges -  <code>dateRangeInput()</code></span></li><li><span data-preserver-spaces="true">passwords -  <code>passwordInput()</code></span></li></ul> <span data-preserver-spaces="true">Each input must have an <code>inputId</code> (local name, e.g. 'value'), and a <code>label</code> (a description that will be displayed in an app, e.g. 'Select value'). In addition, depending on the type of input, you can provide additional parameters that will specify/limit the actions a user can perform. For more on defining <code>input</code> and <code>output</code>, and other aspects of Shiny, read <a href="https://shiny.rstudio.com/tutorial/?utm_source=appsilon" target="_blank" rel="noopener noreferrer">RStudio's tutorial</a>.</span> <span data-preserver-spaces="true">In the first draft of the app, let’s create a reactive select box from which the user can choose any job category that appears in the dataset. Therefore, besides defining <code>inputId</code> and a label we need a list of choices for a dropdown list (<code>choices = TableName$ColumnName</code>):</span> <script src="https://gist.github.com/darioappsilon/4181dfe61d9502be0c63b3cbbd23b5b7.js"></script> <span data-preserver-spaces="true">On the other hand, <code>output</code> is the second argument in <code>fluidPage()</code>. In this case, it is the result of actions taken by the user in inputs. It can be displayed in the form of a:</span> <ul><li><span data-preserver-spaces="true">Graph - <code>plotOutput()</code>, </span></li><li><span data-preserver-spaces="true">Table - <code>tableOutput()</code>, </span></li><li><span data-preserver-spaces="true">Text - <code>textOutput()</code>, </span></li><li><span data-preserver-spaces="true">Image - <code>imageOutput()</code>, </span></li></ul> <span data-preserver-spaces="true">and so on. Just like input, the output needs to have an ID - <code>outputId</code>. We’ll display the results as a table, so let’s use the <code>tableOutput()</code> function and name our reactive output <code>jobhuntData</code>:</span> <script src="https://gist.github.com/darioappsilon/ee64db38814c76f1c5ddee0c031e8f6e.js"></script> <span data-preserver-spaces="true">Like many basic Shiny apps, our draft Shiny app is quite ugly by default. Let’s fix this with some elements: <code>titlePanel()</code>, <code>sidebarLayout()</code>, <code>sidebarPanel()</code>, and <code>mainPanel()</code>. </span> <span data-preserver-spaces="true">At this point, after adding all elements to a <code>fluidPage()</code> function, our code looks like this:</span> <script src="https://gist.github.com/darioappsilon/b5e4ad0bfa7ea3a88a7f4032c09d8159.js"></script> <img class="size-full wp-image-12403" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021eaf75d40a9af727a4e_dropdown-menu-in-r-shiny-app.webp" alt="Image 7 - Dropdown menu in R Shiny app" width="2502" height="1080" /> Image 7 - Dropdown menu in R Shiny app <span data-preserver-spaces="true">We can see the filter, but there is no table yet. This is because R Shiny does not know what kind of table we want to generate. Let’s introduce server requirements to address this.</span> <blockquote>Want to Make a Beautiful Shiny App Fast? <a href="https://appsilon.com/r-shiny-dashboard-templates/">Leverage Appsilon's R Shiny templates that are completely free</a>.</blockquote> <h3><span data-preserver-spaces="true">How to use Shiny Server</span></h3> <span data-preserver-spaces="true">To build the first draft of the app, we need to create a source for the <code>tableOutput()</code> function by using Render Functions:</span> <ul><li><span data-preserver-spaces="true"><code>renderImage()</code> to render an image, </span></li><li><span data-preserver-spaces="true"><code>renderPlot()</code> if you want to render a plot/graph, </span></li><li><span data-preserver-spaces="true"><code>renderText()</code> to display text</span></li></ul> <span data-preserver-spaces="true">and so on. </span> <span data-preserver-spaces="true">Below you can see how we assigned the <code>outputId</code> (<code>jobhuntData</code>) to a function that renders the desired output - in our case, <code>renderTable()</code> to render a table. Inside this function, we specified the data that we want to see in the table. Please mind that <code>input$jobcategory</code> refers to the Input Function from the UI, and it is always equal to the current value of the input (a value selected by a user).</span> <script src="https://gist.github.com/darioappsilon/a99cc125a0d6cbe7aedb623272ab6990.js"></script> <span data-preserver-spaces="true">The current version of the app does not look amazing, but we can see that the correct data is shown, and the server generates proper output according to the input provided by the user:</span> <img class="size-full wp-image-12415" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021ec22f94709a7bfaf08_reactive-r-shiny-app.gif" alt="Image 8 - Reactive R Shiny app" width="1414" height="788" /> Image 8 - Reactive R Shiny app <h3 id="migration-shiny-widgets"><span data-preserver-spaces="true">Migration: SQL and ShinyWidgets</span></h3> <span data-preserver-spaces="true">Now that we know how to create a basic dashboard in R Shiny, we are going to migrate other features from our original dashboard. First and foremost, we had to not only create filters for all columns but also aggregate/group data by <em>YEAR</em> and <em>COUNTRY</em>. There are several ways to modify the dataset in R, but we decided to do it using an <em>SQL SELECT</em> statement. SQL is another topic on its own, but we recommend that you learn the basics of SQL if you work with data on a daily (or even weekly) basis.</span> <span data-preserver-spaces="true">This is one of the SQL statements we used to create an aggregated view in Google Sheets:</span> <img class="size-full wp-image-12419" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021ec21b80e6ed3cf9d50_sql-statement-in-googlesheets.webp" alt="Image 9 - SQL statement used in Google Sheets" width="3316" height="74" /> Image 9 - SQL statement used in Google Sheets <span data-preserver-spaces="true">Below is the logic that we applied in R using the <code>sqldf</code> library. It enables us to see how many phone screenings, interviews, and offers we had each year in every country:</span> <script src="https://gist.github.com/darioappsilon/8070b1a45308939ec0bdd731ac7d35e0.js"></script> <span data-preserver-spaces="true">This is what the new table <code>aggregated_data</code> looks like:</span> <img class="size-full wp-image-12399" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021ed21b80e6ed3cf9d9e_aggregated-data-table.webp" alt="Image 10 - Aggregated data table" width="892" height="1024" /> Image 10 - Aggregated data table <span data-preserver-spaces="true">Adding multiple filters that are conditional can be a very difficult task, but the </span><a class="editor-rtfLink" href="https://www.rdocumentation.org/packages/shinyWidgets/versions/0.4.9" target="_blank" rel="noopener noreferrer"><span data-preserver-spaces="true">ShinyWidgets</span></a><span data-preserver-spaces="true"> library offers a perfect solution: </span><a class="editor-rtfLink" href="https://www.rdocumentation.org/packages/shinyWidgets/versions/0.4.9/topics/selectizeGroup-module" target="_blank" rel="noopener noreferrer"><span data-preserver-spaces="true">selectizeGroup-module</span></a><span data-preserver-spaces="true">. Having imported ShinyWidgets, we've replaced <code>selectInput()</code> with <code>selecticizeGroupUI()</code> and added one more function - <code>callModule()</code>. This way we have eliminated the possibility of choosing a combination that does not exist. Below you can see the entire solution:</span> <script src="https://gist.github.com/darioappsilon/93e8ead9c6b7ac2a6654a2b4c8b40d9c.js"></script> <img class="size-full wp-image-12407" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021ef6bb6e6a6629db0fb_final-shiny-app.gif" alt="Image 11 - Final Shiny app" width="1414" height="880" /> Image 11 - Final Shiny app <hr /> <h2><span data-preserver-spaces="true">Switching from Excel to R Shiny: key takeaways</span></h2> <span data-preserver-spaces="true">Working with a new tool like <a href="http://appsilon.com/shiny" target="_blank" rel="noopener noreferrer">R Shiny</a> can be intimidating at first, but in some ways, it can be even easier to learn and understand than Excel or Google Sheets. It is more flexible in terms of adding new features or modifying existing ones. Because we replaced four tables with one, the dashboard not only looks better than our Excel and Google Sheets tool - it is also much easier to use.</span> <span data-preserver-spaces="true">Moreover, we managed to create an app where the user is in complete control of the displayed data but does not have access to the backend. This means we do not need to worry about non-technical users making accidental changes to the source code or breaking the app. We can also apply version control and store the source code of the app on services like GitHub in a way that allows us to safely revert to previous versions. This way, anyone with whom I want to share my code can download it and make contributions in a controlled environment.</span> <blockquote>Interested in learning R Shiny further? <a href="https://appsilon.com/how-to-start-a-career-as-an-r-shiny-developer/">Here's how to start a career as an R Shiny developer</a>.</blockquote> If you need a PoC fast or an improvement on an existing concept, reach out to us. Appsilon is a global leader in R/Shiny development. And the Appsilon team plays an active role within the R/Shiny community, developing and maintaining open source packages and Shiny templates. From design to infrastructure, our <a href="https://shiny.tools/" target="_blank" rel="noopener noreferrer">Shiny tools</a> can be used to build efficient, <a href="https://appsilon.com/shiny/" target="_blank" rel="noopener noreferrer">beautiful Shiny applications</a> that scale. We’ve got the skillset to solve the most challenging of problems and we’re an <a href="https://appsilon.com/appsilon-is-an-rstudio-full-service-certified-partner/" target="_blank" rel="noopener noreferrer">RStudio Full Service Certified Partner</a>. <p style="text-align: center;"><em>This article was originally written by <a href="https://www.linkedin.com/in/zuzanna-maria-danowska/" target="_blank" rel="noopener noreferrer">Zuzanna Danowska</a> with further edits from Appsilon team members <a href="https://appsilon.com/author/marcin/" target="_blank" rel="noopener noreferrer">Marcin Dubel</a> and <a href="https://medium.com/@radecicdario" target="_blank" rel="noopener noreferrer">Dario Radečić</a></em><em>.</em></p>

Contact us!
Damian's Avatar
Damian Rodziewicz
Head of Sales
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Have questions or insights?
Engage with experts, share ideas and take your data journey to the next level!
Join Slack
Explore Possibilities

Take Your Business Further with Custom Data Solutions

Unlock the full potential of your enterprise with our data services, tailored to the unique needs of Fortune 500 companies. Elevate your strategy—connect with us today!

Talk to our Experts
shiny dashboards
r
data analytics
tutorials
rstudio
excel alternatives