Excel Tips

Tips from the Finance Trenches: A Better Way to Use Static Reports for Cross-Functional Data Analysis

For a holistic view of your business, it is critical to integrate cross-functional business data. However, Finance and Business Intelligence professionals often find themselves in situations where they have to work with static crosstab reports that are not easy to manipulate. Furthermore, it can be challenging to use static reports to connect data with other functional datasets, such as Marketing, Manufacturing or Sales data.

In this latest Tip from the Finance Trenches, we’ll walk you through a simple fix to that problem. This tip helps you convert static reports into a flat file, which can then be integrated with other datasets in Microsoft Excel or Microsoft Access databases.

The following example shows a sample report – this will be our starting point for reference.

Now, follow these steps to convert this cross-tab report to a flat file so you can integrate it with additional cross-functional data sets.

Step 1: Insert a new column (in our example, after the “Customer” column) and aggregate all non-numerical column values in one column with comma separator (“,”), as shown below. Then drag that formula to the last row of the report.

Step 2: Highlight the new column and all the numerical data fields and create a pivot table.

Step 3: Right-click on Pivot Table Option> Display> Classic Pivot Table; and right-click on “Values” to move the values to rows.


Step 4:
Copy All and Paste Values into a new Excel sheet.

Step 5: Locate and fill in the empty cells. Choose Find & Select > Go to Special > Select “Blanks.” This will select all the blank cells. In the Formula Bar, type in cell reference above blank cell and hit CTRL+Enter. All blank cells will be filled with respective values.


Step 6:
Insert columns after aggregated name values column. Select the Data tab and choose the “Text to Columns” button from the top menu. The “Convert Text to Columns Wizard” will appear. Check “comma” as your delimiter.

And you’re done! Your Crosstab Report has been converted into a Dynamic List format and you can connect the dataset with other datasets via PowerPivot or MS Access for further cross-functional analysis.

Want more Excel tips and shortcuts like this one? Check out more Tips from the Trenches, all with easy illustrated step-by-step instructions. Our most popular tips include:

8020 Consulting provides expertise across a broad range of complex financial topics, from Fortune 50 companies to middle market and venture-backed firms. Contact us if have a financial issue you would like to discuss.

financial system selection checklist

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Financial Planning & Analysis

Risks in a Rising Rate Environment

In today’s economic environment, macroeconomic rates – inflation, Fed Funds, interest rates, Prime and LIBOR, for example – are increasing across the board, pushing production costs higher and straining the wallets of consumers. For companies, one of the most obvious consequences is the higher interest that they will need to pay to service their debt…. View Article

July 6, 2022Danny Lee

M&A Due Diligence & Transactions

M&A Sell Side Transaction Support: Keys to Success

I was recently deployed into the sell side of an M&A transaction involving a high-growth company in the entertainment sector. This role, like other transaction support roles I have performed, involved a lot of granular-level data analysis and modeling. But the function of an M&A sell-side transaction support professional is always consistent: Collaborate with various… View Article

June 22, 2022Chris Routhe

Financial Planning & Analysis

Five Signs You Need to Update Your Headcount Management

For most companies, headcount is one of the most important assets, and more often than not, it is also the single largest expense. Proper management of headcount is essential to achieving a company’s growth objectives or cost savings targets. Given the vital importance of headcount to the success of any business, one would expect that the… View Article

June 8, 2022Chris Kirk

See All

Back to Insights