Excel vs. Tableau: See your data differently
Technology experts in the accounting profession have been predicting the demise of Microsoft Excel for years. In 2015, for example, the JofA asked the participants in its annual technology roundtable what they saw as the future of the spreadsheet. The first answer given was a product named Tableau. (See “Beyond Spreadsheets: Technology Round Table, Part 1,” JofA, April/May 2015.)
Fast–forward five years, and Excel is still going strong. That’s despite Tableau being a really cool tool — so cool, in fact, that we proposed writing a JofA article about it. The response came with a series of questions: How does Tableau compare to Excel? What can Tableau do that Excel can’t? Could Tableau replace Excel for accountants, and, if so, why hasn’t it yet?
This article answers these questions.
The first thing to understand about Tableau is that it is a data visualization program. Data visualization programs empower CPAs and other users to render the results of analyses in sophisticated, easy–to–create charts that highlight many more aspects, or “views,” of the data than plain tables. Provided with a plethora of new visual insights, users can look to develop business intelligence.
The second thing to understand about Tableau is that it’s not a spreadsheet replacement. Excel can handle a number of tasks that Tableau either can’t perform well or can’t handle at all. The reverse holds true as well.
To help you understand the capabilities, limitations, and best use cases for Tableau, this article explores its differences from (and similarities with) Excel and also walks you through a simple use case with live data to demonstrate the power of its data visualization capabilities.
Before we jump into the details, it’s important to note that Tableau is far from the only data visualization application available to CPAs. The market includes numerous competitors (see the sidebar “Spreadsheet and Visualization Applications,” at bottom of page), though Tableau and Microsoft Power BI are probably the two biggest names (see the accompanying article, “Power BI: An Analytical View“).
As mentioned, Tableau boasts a number of capabilities that Excel cannot match, while Excel shines in some scenarios that Tableau either can’t handle well or at all. Let’s look first at the strengths of each application in the table “Strengths of Excel vs. Tableau.”
Strengths of Excel vs. Tableau
Excel
Tableau
For decades, Excel has been the primary tool accountants use to summarize or chart data. Tableau also handles these tasks well. For someone who’s familiar with Excel, it may seem easier to use Excel, but a user can learn to perform these tasks just as easily, if not more easily, in Tableau. Since this article’s main purpose is to compare and contrast Excel and Tableau and note the differences, we elaborate more on differences than on similarities, but the many tasks the applications do equally well include:
While Excel and Tableau handle these tasks equally well, as the amount of data continues to increase in the accounting world, it may be advantageous for accountants to use Tableau due to its ability to handle large amounts of data (discussed below).
Before we jump into Tableau’s advantages over Excel, let’s cover a couple of tasks that Excel handles better.
Creating or revising a dataset
Excel offers great flexibility when creating or revising a dataset. In Excel, users can either import an existing dataset into a worksheet or build a dataset manually. They can then add or delete as many records to the worksheet as needed.
This simple task can be done by manually adding data or importing and appending additional records to the original data. For relatively small datasets, Excel offers simplicity and flexibility in creating and editing data. On the other hand, Tableau is not designed for ad hoc data management such as creating a dataset from scratch. (See the sidebar “What Data Can Be Imported Into Tableau” to see what types of file formats Tableau can accommodate.)
Customizing calculations, performing scenario analyses, and automating repetitive calculations
Perhaps Excel’s strongest capability is that users can create complex, recursive calculations in cells using values or formulas referencing other cells. This trait allows users to develop an almost unlimited type of small analytical “programs” within worksheets, each capable of providing critical information.
Take, for example, a loan amortization table. To develop a simple, flexible amortization table, a user allows for a small number of input cells where users can enter the desired interest rate, loan term, and loan amount, and then can determine the payment due using the PMT function. Once the payment is determined, users can create formulas in a schedule that calculates the interest paid each period, the repayment amount, and the loan’s new carrying value each period for the duration of the loan. The whole process is simple, quick, and flexible, and results in important information. Excel’s ability to accommodate ad hoc analyses of business needs by allowing customized calculations with preprogrammed functions and cell references is indispensable, and this simply cannot be done in Tableau.
Overall, Tableau is superior to Excel in allowing the dynamic exploration of data and interactive analyses. This section focuses on data filtering and data visualization, while the following section covers interactive dashboards.
Data filtering
Tableau can filter a single worksheet, as Excel does, and it can also filter multiple worksheets with any number of filters a user may need. It is almost effortless to do this since Tableau has a visual interface that makes it simple to set, change, or delete filters.
Filters can be set in individual worksheets in Tableau as well, so they can be applied locally to individual worksheets or globally to multiple worksheets, and global filters can work in conjunction with local filters.
Data visualization
Both Excel and Tableau allow users to create nice–looking, basic charts and graphs, but the process is simpler in Tableau. Tableau can create advanced charts by merely dragging and dropping fields. Once created, the chart or graph can be changed to a different visualization with one click. Excel does not rival Tableau in this respect. Further, Tableau users can click on areas of the chart to easily explore different levels of detail.
What does Tableau look like in action? To showcase the program’s ability to handle large amounts of data, we obtained occupational data collected by the government (see the sidebar “Data Source Citation”) and opted to analyze the geographical distribution of accountant salaries. A similar process could be used on company sales or revenue, but we wanted to walk through a relatively simple example using live data.
We can start by filtering the dataset for “Accountants and Auditors.” This filter separates accountant and auditor salaries from the larger dataset. To set this filter globally, select the Filters link in the Data Source page, then select the Occupation Title field and keep the “Accountants and Auditors” category. In the screenshot “Edit Data Source Filters,” we show the result that appears after following these steps. After we click OK, all other occupations are filtered out and only “Accountants and Auditors” is analyzed in all worksheets.
To create a chart, drag the Area Name field from the list of fields to the Rows line in the worksheet, and then drag the Mean Annual Salary field to the canvas. Next, click Sort. The screenshot “Accountants’ Average Salaries by Metro Area — Sorted” illustrates the result of this new chart sorted in descending order by salary.
Accountants’ average salaries by metro area — sorted
From this basic chart, it takes only one click to change to a table, a map, a column chart, a bubble map, and so forth. The different visualizations are that simple to create. Even better, the chart is dynamic, meaning that it can be explored for further insight. For example, a user can scroll through the bar chart to see the distribution of specific areas and the salaries, and can customize color, size, label, and detail to facilitate further analysis. The screenshot “Customized Bar Chart” shows the same chart with line widths customized to reflect total employment of each location. Note that although San Rafael, Calif., has the highest average accountant salary, its bar is much thinner than the bar for New York–Jersey City–White Plains, which has much higher total employment.
Customized bar chart
Next, to view the result as a map, you can simply click on the map icon to produce the map shown in the screenshot “Map of Accountants’ Salaries.”
Map of accountants’ salaries
Next, if you want to find where salaries are higher than $70,000, simply drag and drop salary onto the filter box in the worksheet. The screenshot “Map With 2 Filters Applied” shows the results of applying a filter for accountants’ salaries for all metropolitan areas in the continental United States where the mean salary is greater than or equal to $70,000.
Map with 2 filters applied
With the visual interface, we can easily change the condition to see just the metro areas where the average salary is greater than or equal to $75,000 by sliding the Mean annual salary bar located to the right of the map. Results change instantly to reflect the new condition. Further, to include an additional condition in the analysis, simply drag and drop another field on the filter box. The screenshot “Map With Mean Salary Slider” shows the results of U.S. metro areas where average salary is at least $75,000 and then total accountant employment is greater than or equal to 5,000.
Map with mean salary slider
Handling large amounts of data
Tableau can handle virtually any amount of data depending on your computer’s hardware. There is no limit to how many records Tableau can load. On a computer that has only 8 GB of RAM, we were able to load and analyze a dataset with more than 25 million records in Tableau. Excel, on the other hand, is limited to 1,048,576 rows.
Creating interactive dashboards
While filters offer one way to analyze data dynamically, interactive dashboards take it one step further. Interactive dashboards are visualizations involving multiple views of data items to help users analyze data. For example, if you wanted to analyze accountants’ salaries by geographical location, you could create a dashboard showing a geographic map of salary by city and a sorted bar chart showing the highest to lowest salaries. Excel can build dashboards, but it is not nearly as straightforward or as powerful as the dashboard feature in Tableau. The screenshot “Interactive Dashboard With Filter Slide Bars” shows an example Tableau dashboard highlighting average accountant salaries for the continental United States with a map, a bar chart, and sliders, allowing the user to define parameters for visualizing both salaries and employment.
Interactive dashboard with filter slide bars
By moving the Total Employment slide bar to the right, the user could easily update both the map and bar chart to display only metro areas where total accountant employment was, say, 1,000 or more accountants, and find the average accountant salary of those areas.
More importantly, Tableau dashboards allow users to zoom in on specific parameters to examine more granular detail. Suppose, for example, that a user wants to examine accountant employment and salaries in New York state and determine what average salaries are in each metro area with accountant employment of at least 1,000. By zooming in on New York state and adjusting the Total Employment slide bar, users can observe the results shown in the screenshot “Average Salary by New York Metro Areas Employing at Least 1,000 Accountants.” Users could then compare metro area salaries by observing the color code on the map or by looking at the related bar chart on the dashboard.
The ability to zoom in or out so easily allows users to aggregate or disaggregate data across multiple dimensions, which generates business intelligence.
Average salary by New York metro areas employing at least 1,000 accountants
In their never–ending quest for better ways to work with data and display information, accountants are likely to find that spreadsheet software such as Excel and data visualization software such as Tableau are both necessary tools. Excel is suitable for customized sequential calculations; Tableau is a powerful tool for analyzing large, complex datasets through a user–friendly interface. As a result, accountants would be well advised to have strong working knowledge of both Excel and Tableau (or another data visualization program) to continue to serve their clients and employers effectively.
Spreadsheet and visualization applications
This article focuses on the principal differences between two classes of software: spreadsheets and data visualization programs. A detailed comparison of products within each class is beyond the scope of this article. Although we use Excel and Tableau as the primary examples, the principles we discuss apply more broadly to spreadsheets and data visualization programs in general. Here are some names to know:
Other types of visualization software — flowcharting software, for example — are beyond the scope of this article.
What data can be imported into Tableau
Tableau is designed to incorporate data organized in fields and records; a column is a field, and a row is a record. The data can be in various file formats that follow the same field-record structure, including Excel files, text files such as CSV (comma-separated values) files and tab-delimited files, and PDF files. The data can also come from direct connections to database servers such as SQL. An advantage of live data connections is that the analytics automatically update with the data.
The data items can be qualitative or quantitative; a qualitative field is called a dimension, and a quantitative field is called a measure in Tableau. Further, a dimension can be assigned a geographic role to represent countries, states, metropolitan areas, or ZIP codes.
Data source citation
The data used in this article is publicly available from the U.S. Department of Labor’s Bureau of Labor Statistics’ (BLS) Occupation Employment Statistics website; specifically, metropolitan and nonmetropolitan area data. In this article, we are not concerned with the specific results or insights in this dataset but are using this dataset to demonstrate Tableau’s features. In the portion of the data that we use, each record identifies one occupation for each area; for example, one record contains fields about accountants in the New York-Jersey City-White Plains, NY-NJ area, and the fields include total employment, mean hourly wage, and mean annual salary of accountants in this area.
About the authors
Kevin Pan, Ph.D., is an assistant professor in the Brock School of Business, and Alan Blankley, CPA, Ph.D., is a professor in and chair of the Department of Accounting, both at Samford University in Birmingham, Ala.
To comment on this article or to suggest an idea for another article, contact Jeff Drew, a JofA senior editor, at Jeff.Drew@aicpa-cima.com or 919-402-4056.
AICPA resources
Articles
CPE self-study
Conferences
For more information or to make a purchase or register, go to aicpastore.com or call the Institute at 888-777-7077.
Videos
IMTA Section and CITP credential
The Information Management and Technology Assurance (IMTA) Section supports AICPA members who provide services in the areas of information security and cyber risk, privacy and IT risk management, business intelligence, and emerging technologies. CPAs may also pursue the Certified Information Technology Professional (CITP) credential, which demonstrates an individual has the expertise to advise organizations on how to maximize IT to manage their business. Access to IMTA tools and resources is included with AICPA membership. To learn more, visit aicpa.org/IMTA and aicpa.org/CITP.
Research & References of Excel vs. Tableau: See your data differently|A&C Accounting And Tax Services
Source
0 Comments