By Luke Sisley, Senior Business Intelligence Analyst, CSL
Microsoft Excel has now been around for close to 4 decades. In that time added functions have extended its capabilities far beyond basic spreadsheeting. Despite the availability of newer, more sophisticated data analytics and visualisation tools like Power BI and Tableau, Excel remains popular because of its ease of use, flexibility, and ubiquity. Many businesses have already invested heavily in Excel and have existing spreadsheets, macros, and add-ins that are crucial to their business processes.
However, self-service data analytics and visualisation platforms like Power BI and Tableau are gaining popularity. They offer a more complete “service” approach to BI. These tools allow users to easily connect to various data sources, combine and transform data, and create interactive visualisations and reports, with sophisticated sharing options.
At CSL we support many small and large businesses in developing their BI processes and systems. A common question we get at asked is: How do we integrate these different tools in our business? For businesses aiming to build a strong data culture, it is crucial to have the right software in place.
In this short article, we will address some key considerations around why, how and whether you should transition your reporting from Excel into a more advanced platform like Tableau or Power BI.
What are the differences?
Before we jump into the topic at hand, we need a quick overview of the options available. Software like Power BI and Tableau are essentially data visualisation software, but they offer a far more complete solution to BI when compared to a spreadsheet program such as Excel. Let’s take a closer look at some of the key differences:
Data modelling and size
While Excel allows a basic level of data modelling via Power Pivot, as well as a range of connections, data used in the connections is stored in the file itself, with a capacity of 2GB. Power Pivot in Excel is also more rudimentary than its application in Power BI, only supporting basic modelling functions and one-to-many or one-to-one relationships between tables.
This can be contrasted with the advanced data modelling functions in Power BI and Tableau, where we have a range of live connections or extract types that allow us to source data in different ways, depending on which one is most suitable for the job at hand. These tools can also analyse far more data than an Excel document. This means that these tools are superior for BI functions that require the analysis of millions of rows, or data from many sources.
Visualisations and interactivity
Every experienced analyst knows how important the look and feel of any report or dashboard is. Excel does again allow for simple dashboard and report design, and does include a range of charts, but its visualisation capabilities pale in comparison to proper visualisation tools. Overall, software such as Power BI and Tableau present data in a more appealing way. Both applications feature dashboard-building functions, while this has to be done manually in Excel. Both also feature mobile customisation options, allowing authors to edit layouts for mobile users – a feature that is absent in Excel. Adding to this, Power BI also allows users to import custom visualisations from the marketplace.
While visualisation software does visualisation software make your reports look more appealing, there are considerations around the level of interactivity. An un-protected Excel sheet will allow users to work with the data, producing their own calculations and outputs. Pivot tables and charts also grant a high level of interactivity. Excel’s level of interactivity is enhanced by the fact that a lot of people know how to work with Pivot tables or perform basic formulas.
On the other hand, Power BI and Tableau use user roles, which can limit the level of interaction with a report or dashboard. While it is possible to grant users the ability to click through, alter, or filter the visuals being displayed, it is not possible for users to edit and add their own outputs or analysis.
A major difference is sharing options; Power BI for example has sophisticated sharing options, including cloud features that allow users to collaborate and share dashboards in a more advanced way. Additional features include alerts, custom views for different users. Sharing is managed via user roles, allowing your business to designate how each user will be permitted to use the software.
This more structured approach to sharing can be contrasted to Excel, where sharing is very simple – just send your document or store it in a shared location. Microsoft 365 does allow for web-based collaboration on an Excel document in real time, which is great for team working.
Realistically, the key difference here comes down to the different usage models for each type of software – Tableau and Power BI are designed to allow a skilled set of authors to produce and share reports and dashboards with viewers – who can perform basic manipulation of visuals, and set up custom alerts and views. Excel and Google sheets on the other hand do not have a baked-in structured usage model.
Which one should we use?
The choice in whether to use a spreadsheet or a visualisation software depends on a number of factors, including:
Where it is more common to find people with skills in spreadsheet programs such as Excel, Power BI development skills are rarer. Some also find it harder to learn, since there are fewer visual elements. Power BI uses DAX for calculations, which many users feel is more difficult to master than Excel formulas. Nevertheless, if your organisation plans on making the most out of Power BI or Tableau, then you need access to advanced development skills.
When deciding whether to transition to a more sophisticated software, it is important to consider the technical aspects. To get the most out of tools such as Tableau and Power BI, you will need to ensure that you have the right systems in place to maximise the automation of your report production. While these programs can integrate with simple spreadsheets, you will want to ensure that most of their data is sourced through connections to databases.
Pro versions of Power BI and Tableau have a cost attached, and you will want to invest in the professional versions if you plan on seriously integrating these tools into your business. Excel on the other hand is part of the standard MS suite and will be readily available for most business users.
Overall, many businesses work with us to transition existing Excel deliverables that are required on a regular basis, into either Power BI or Tableau. This does have some implications around the systems you have to store and query data, the competencies required, and the costs. However, there are a multitude of benefits, including having a slicker, more efficient BI function that can provide self-service analytics across your business.
If you have questions about how to best integrate dashboarding and visualisation tools into your business, please feel free to get in touch. We have worked with small and large companies in many industries, and can find the right solution to match your needs and budget.