Start Wrangling

Speed up your data preparation with Trifacta

Free Sign Up
Trifacta Ranked #1 in Data Preparation Market Study

Dresner Advisory Services study reviews and ranks 24 vendors

Get the Report
Schedule a Demo

Unpivot Excel Command: Fading Function in Financial Services & Beyond

February 15, 2017

Pivot and Unpivot Excel Tools Are Powerful and Easy

Do you recall before you learned how to unpivot excel at work? Remember the heady days when you were just learning about the awesome capacity of pivot tables in Excel?  You were on a data expedition: no longer just a windward sailor trying to stay afloat, but a captain finally navigating your own ship.

Regardless of where you sat in your organization—a newly minted management consultant, investment banking analyst, or marketing associate—when you began using Excel pivot tables, your decision-making (and your life) became easier.

The moment you learned how to unpivot excel was even better. Now you could move information from data entry to data analysis more easily. You could also change data back into tabular data from your pivot-table cross-tabs to analyze the dataset in aggregate. Excel unpivot meant even better decisions.

In other words, you upped your business intelligence game big time. And with Trifacta, you can do even more with your data.

The Move Away from Excel Unpivot and Pivot Functions

At Trifacta, there is  love for a good pivot table. Excel unpivot and pivot are by all accounts the most powerful functions in Excel. But dealing in gigabytes worth of data with Excel can be unwieldy or even prohibitive. That’s why Trifacta has built a product that offers familiar pivot-table functionality, but with a superior visual interface and intelligent interactions. This means you get the multi-faceted view of the pivot table, without the unintuitive process. It also means you get the aggregation power of the Excel unpivot without the awkward practice of having to make multiple tables.

As data lineage has become more important,  we have seen a move away from Excel, as the application makes it virtually impossible to track all of the transformations that have been made, when, and by whom. With Trifacta, every change is clearly listed at the right of your screen at all times.

An area where Trifacta sees the ubiquitous use of Excel dissipating most is in financial services and banking. The financial services industry historically has relied mostly on Excel as a basis for informing industry trends and critical calculations, like the Allowance for Loan and Lease Losses (ALLL). One of the most significant quarterly financial statement estimates, the ALLL, shows how much a bank has in reserve to offset bad debts, and it has an enormous impact on earnings and capital. ALLL is also a calculation that has been derived almost exclusively using Excel. In a December 2015 Mainstreet Technologies survey, over 64% of the financial institution respondents said they still use Excel, and 66% said they are seeking other tech solutions ahead of the FASB CECL release that rolls out in 2020.

Excel became institutionalized because it’s powerful and relatively easy. However, finance and other sectors are realizing that Excel has more than just dataset size limitations. It is estimated that almost 90% of spreadsheets have errors, made worse by the lack of controls as spreadsheets pass between coworkers. In fact, it was a relatively simple Excel cut and paste formula error in 2012 that caused JP Morgan to miscalculate a fund’s VaR risk profile to the tune of a $2B loss.

Above size limitations, nowhere is tracking and data lineage more important than in the financial services sector, where scrutinous audits and regulatory reporting rule. Trifacta makes tracking data simple, enabling real-time inventories of key data.

How Trifacta Transformed the Unpivot Excel Transform

While Trifacta offers a predictive and visually pleasing way to execute a pivot transform, here we will focus on the Unpivot Excel command. This function is used most often when moving data from data-entry stage to analysis stage (e.g. normalizing data). Data entry is easiest when one assigns one row for each key (First Name) and one column for a data point (Hours Worked). This format makes for easy recording of data; however, aggregation and summation become far more difficult.

Trifacta reforms the data by merging one or more columns into key and value columns. Keys are the names of input columns, and value columns are the cell values from the source. Rows of data are duplicated, once for each input column. The unpivot column can be applied to multiple columns in the same transform. All columns are un-pivoted into the same key and value columns. With data arranged this way, new aggregations can be made and new insights found. Trifacta’s solution for unpivot Excel function woes is truly for anyone who needs to organize their data efficiently and accurately.

Beyond the actual function of a pivot or unpivot Excel command, Trifacta has the built in functionality to export directly to Tableau. Trifacta was built with Tableau users in mind: we know pivot/unpivot is an essential function, and we’ve made it easier and quicker to see your results, without employing the messiness of Excel.

To learn more about data wrangling, try out our free cloud product, Trifacta Wrangler!

Related Posts

Three Instances When Excel Breaks Down with Big Data

Excel launched in 1985, when companies dealt in megabytes and the concept of regularly crunching terabytes or... more

  |  October 28, 2016

The Cost of Expired Data (And What to Do About It)

Your business gathers data from multiple sources en masse to derive insights. All of this information is... more

  |  June 17, 2016

Best Practices for Developing a Master Data Management Strategy

Master data management strategy (MDM) refers to the governing procedures for entering, aggregating,... more

  |  October 18, 2016