Posted by Datawatch on March 30, 2016

Microsoft Excel is one of the most widely used business intelligence and reporting tools. More than just a spreadsheet, Excel allows users to collect and integrate data for critical analysis and reporting. It’s easy to use, readily available as part of the Microsoft Office solution suite, and provides users with the ability to perform advanced analytic functions without the need of IT intervention.

Yet, despite its many benefits, there are some limitations to Excel that impede a user’s ability to quickly transition from data gathering to data analysis. Version control, data integrity, maintenance and security are just a few of the challenges that cause data analysts and auditors to spend more time preparing data instead of analyzing it. And since collecting data is only the first step in being able to deliver the right information to the right places at the right time, these challenges can cause difficulties if Excel is being used as the primary tool for performing data analysis. To further complicate things, most analysts don’t have direct access to various data sources across the organization and, as a result, require IT intervention to pull needed reports. This adds time to the data gathering process which can incur costs and delays in decision-making and detection of inefficiencies and fraud.

But there is hope! Many Excel users, having recognized the shortcomings of the software, have turned to self-service data preparation to augment Excel’s capabilities, and have experienced many time-saving and process benefits from this approach.

Understanding the data and what needs attention.
Excel allows users to preview a data table, but this is typically a very manual process requiring multiple spreadsheets to be open at once to see which table has the right data. Complex macros may also need to be created to hide multiple datasheets. With a data preparation solution, users are able to quickly see data tables before loading and determine the quality of data and what needs to be cleaned.

Accessing and preparing data trapped within reports, text files or PDFs.
The manual process of copying charts and data from within PDFs into Excel can take hours. Data brought over into one cell has to be manually parsed into different columns, and each table in a PDF needs to be manually copied and reformatted. By incorporating a data prep tool, users can automatically parse the document into rows and columns and have all of the tables brought into a standard format for further data preparation. Even multi-line reports can be intelligently captured into neat rows and columns.

Bringing in data from websites without the “noise.”
When trying to copy data from a website, the layout of the data does not always transfer cleanly, resulting in messy hyperlinks and missing information. Analysts can spend hours formatting just to get charts to merge with other data sets. Data preparation will allow the parsing of website data, stripping out unnecessary content like ads and graphics and bringing only the selected data into rows and columns.

Combining multiple data sets into one with ease.
VLOOKUPs and manual copy/pasting is required to join multiple spreadsheets in Excel, requiring analysts to select entire columns and rows in multiple data sets. With the right data preparation solution, analysts can easily join entire data sets or certain columns with the click of a button.

Identifying and incorporating missing data.
Excel users have to search for nulls and then replace them with one value, manually changing values for each row and column in the entire data set. Data prep will allow for the selection of the ditto function to fill blanks throughout the entire data set and the replacement or removal of data or nulls with the click of a button.

Masking data to ensure corporate or regulatory compliance.
With Excel, complex macros need to be created in order to hide sensitive data. The data is never actually removed from the spreadsheet but rather hidden, which could introduce security concerns. By using a data prep tool, users can securely mask sensitive data such as social security numbers or account IDs, allowing only authorized users to see this data.

Easily bringing together multiple data sets with similar row structures.
When you have multiple data sets with similar content in Excel and need to combine rows, there can be a lot of manual copying and pasting. A data preparation solution allows users to combine similar data sets simply by highlighting the two tables and selecting “append.” Similar data sets are recognized and automatically combined when new data is present.

Seeing specific changes made to reports.
When sharing files in Excel, it’s not possible to see the steps that were taken to clean the data, and in order to make changes, all prior work must be updated. Data prep allows users to see every step taken with the data and offers the ability to go back and make edits without redoing the entire process. This enables users to collaborate within the file by seeing each other’s work.

Reconciling reports with a summary view of changes.
It’s hard to see significant changes in large data-filled reports. Rules need to be set up to cover thresholds and require frequent updating as the data changes. With the right data preparation solution, users are provided with a full summary of the reports with any changes in the data, and the reports are automatically updated as new data is added.

Automating your data prep.
When users have new data, they typically have to manually redo all of the data preparation steps in Excel and create complex macros in order for reports to change on the fly. With a self-service tool, every step of the data preparation process is automatically captured so it can be reused and automated. Users can run a data prep process on a predetermined schedule or have it run automatically when new data becomes available.

From empowering business analysts and auditors to do more with fewer resources to increasing the quality of analysis output, data preparation helps to make Excel excellent. It allows data to be accessed and prepared in the right format, establishes trustworthy data, and enables detection of anomalies, fraud and trends across massive, complete data sets – all while minimizing direct IT involvement.

Product Tour