Microsoft Excel has been used for multiple decades and has been a powerful tool that is ingrained in a lot of businesses. With the rise of so many new business intelligence tools that offer things like self-service interactive graphics and other advanced features, people are questioning its role in the workplace. While new tools can provide different niche benefits, Excel still can provide great value when used for certain tasks.
Excel is good at taking raw data or comma-delimited data outputs and putting them in a format that allows for easy perusal of the data. It is great at housing small data sets and building custom tables out of the data. It is also easy to do custom data manipulation and create formulas and calculations that can be validated easily. For example, it is easy to build a loan amortization calculator in a worksheet using a quick built-in template that then allows for updating of a few variables to produce great financial analysis of a loan option. This task would be difficult to replicate in a tool like Tableau or PowerBI since they are focused more on business intelligence and data analytics instead of scenario building. Excel is also extremely flexible when it comes to visual formatting. It is easy to build complex reports with text, graphics, and tables that can then be directly exported to PDF for the generation of nice custom reports. With the use of coding in Visual Basic for Applications (VBA) and macros, Excel can also be used to automate a lot of data-scrubbing activities to improve raw datasets before further analysis or visualization.
While it may be good at doing some data and reporting tasks, it does take longer to create visualizations and analysis because typically the items are very static and the addition of more data requires updates to formulas and references that can get cumbersome. Excel also is not good at handling large data sets. A single tab can only hold a little over a million rows of data which causes major issues with big data projects. With large data sets, formulas and array calculations can also get complex and slow to perform. The connection of disparate datasets requires these lookup and indexing functions to work properly, so any complex project is likely to have limitations from these features. Most advanced visualization software, like PowerBI or Tableau, don’t edit any data as well, they simply read the data sources and then model the data for interaction. Excel on the other hand introduces a lot of data quality risk regarding the ability to mess with cells, sorting, and formulas that can create a lot of data and model risk. For Excel to unlock a lot of the more useful features, a user with advanced skills is needed to create some of the statistics and models where in software like PowerBI a beginner user can create model connections and start analyzing disparate datasets quickly.
While I prefer to build most of my reports within PowerBI, Excel is still a great tool for housing the source datasets I use that are too small to be housed in a database. It is easy to capture and record small data sets in Excel and then use PowerBI to visualize and explore the data further. It is also nice for building budgets, calculators, and other useful analyses that require data inputs to build scenarios. Excel has so many features that go beyond just data management and visualization that it will always have a place in the data toolbox, albeit a more niche one now that there are a lot of better data visualization and modeling software options.