Convert PivotTables to Values, SUMIFS, GETPIVOTDATA or Cube Functions Automatically in Excel
Ever spent far too long wrestling a PivotTable to fit a presentation-ready format? If you’re a finance professional or consultant in Financial Due Diligence (FDD), Transaction Services, Commercial Due Diligence (CDD), or any data-heavy role, you know this pain.
PivotTables are excellent for quick analysis, but when it comes to custom formatting or complex calculations, they can become a roadblock. Copying and pasting values out of a PivotTable is tedious and error-prone.
This is exactly the problem Accelerate Excel, an advanced Excel add-in, was built to solve. In this article, we’ll show how Accelerate Excel’s Convert PivotTable tool can transform your PivotTables into nicely formatted normal cells in one click, saving you hours of manual work and letting you focus on other things.
Why Convert a PivotTable?
Converting a PivotTable (or the underlying data) into normal cells (whether as values or formulas) is a common step for professionals who need ultimate control over their Excel reports.
Here are the main reasons you might want to do this:
Easier Editing & Further Analysis: Once converted, you can reorganize rows, rename items, or add commentary without wrestling with PivotTable rules. This flexibility is especially handy for last-minute fixes before client reviews.
Polished Reporting: Static tables often look more refined and are simpler to integrate into Word or PowerPoint. Converting your PivotTable gives you full control over every subtotal and format, so you can deliver clear, professional-looking data.
Freezes Your View: PivotTables are great for dynamic analysis, but they can change quickly if someone adjusts a slicer or field. Converting them to regular cells fixes the data in place, ensuring no one accidentally changes the layout or numbers.
The Traditional Way: Manual PivotTable Conversion
If you’re using standard Excel without any add-ins, converting a PivotTable typically involves copying the PivotTable, then pasting as values and formats somewhere else.
This gets you the numbers and appearance, but everything becomes static – no formulas, no pivot functionality, and minimal structure. You then spend a lot of time rebuilding hierarchies or outlines, re-creating subtotals that disappeared, and reformatting to make it presentable.
For a small, simple PivotTable, that manual copy-paste might be okay. But if your PivotTable has hundreds of rows and multiple nested levels, like Region > Product Group > Product Category > Product Name, or a financial layout with Revenue, Gross Profit, EBITDA, Net Income, the process becomes a nightmare of cut-and-paste and formula writing.
In short, the native Excel approach works but is inefficient for anything beyond the most basic PivotTable. It eats up time you could be spending on analysis, and every manual step is another chance for errors to occur.
One-Click PivotTable Conversion with Accelerate Excel
Accelerate Excel’s Convert PivotTable tool turns hours of manual effort into a single click process. Instead of copying and reformatting data, you can instantly convert any PivotTable (including those built with Power Pivot) into a well organized, editable worksheet.
One Click Conversion: Select your PivotTable and click Convert. The tool automatically creates a new sheet with standard cells, preserving the original formatting and grouping with no manual edits required.
Formulas or Values: Convert data into static values for easy sharing, or choose flexible formulas like SUMIFS formulas, GETPIVOTDATA formulas, or even CUBEVALUE/CUBEMEMBER formulas for PivotTables that were using the Data Model (Power Pivot).
Preserved Hierarchy: All the structure in your original PivotTable is retained in the conversion. Row groupings (expand/collapse outlines), indentations for subcategories, subtotal rows, and grand totals are carried over.
You can collapse or expand sections just like you would in the pivot, because Accelerate Excel inserts Excel’s grouping/outlining to maintain that drill-down structure.
Quick Iterations: The conversion happens in seconds, even for large PivotTables. This quick turnaround enables faster analysis and refinement. Need to adjust something? Modify your PivotTable, convert again, and your updated output is ready immediately.
Use PivotTable Just to Convert Them: Many users have reported that even if they did not typically work with PivotTables, they now create them as a quick intermediary step. Setting up and converting a PivotTable has proven to be quicker than manually building a summary from raw data.
In short, a task that could take up hours now only takes seconds. Try converting your own PivotTables by downloading the free trial of Accelerate Excel.
How It Works (Step-by-Step)
Using the Convert PivotTable tool is straightforward. Here’s a quick overview of the process:
Select a PivotTable. Simply click any cell inside the PivotTable you want to convert (or select the entire PivotTable range).
Launch the Convert PivotTable tool. In the Accelerate Excel ribbon (which appears in Excel after you install the add-in), click on the Convert PivotTable tool. A dialog box will appear with several options (see image below).
Configure your settings (optional). In the Convert PivotTable dialog, you can tailor the output to your needs before running the conversion:
Choose the formatting logic for hierarchy levels (e.g. which levels should be treated as “Subtotal” rows with bold formatting, etc.).
Pick the aggregation function for any subtotals (SUM or SUBTOTAL).
Decide whether to include mapping information – i.e. have separate columns for each row field (hierarchy level) or combine them into one column. (This is similar to Excel’s “Show in Tabular Form” vs “Compact Form” settings for PivotTables.)
Opt to exclude repetitive subtotal rows if you want to remove redundant subtotal labels that repeat.
Specify if you want to create a separate ID column (handy if your pivot rows have codes/IDs and you want those in their own column next to descriptions).
Click Convert. Accelerate Excel will generate a new worksheet in your file with the converted PivotTable. Your original PivotTable remains untouched (so you can still use it or convert it again with different settings).
The new sheet will contain the values or formulas as selected, with all formatting and grouping applied automatically. Your PivotTable is now an independent, formatted table that you can modify freely. There’s no link between this output and the original PivotTable, which is great for creating final reports. (If you chose formulas like GETPIVOTDATA or CUBEVALUE, those will still pull from the pivot’s cache or data model.)
More Detailed Technical Descriptions
Accelerate Excel’s Convert Pivot dialog gives you granular control over how the PivotTable is transformed. In this screenshot, you can see options to format each hierarchy level, use SUM or SUBTOTAL, include mapping columns (tabular form), exclude repeated subtotals, and even create an ID column. These settings ensure the result is tailored to your reporting needs.
When using the conversion tool, you have plenty of options to fine-tune the result. Here are some key settings and what they do:
Formatting Logic per Level: You can define how each hierarchy level in the rows should be formatted. For example, maybe Level 1 (top level) entries should be bold and formatted as “Subtotal” rows, Levels 2-3 as regular detail, and the grand total as a distinct style.
The tool lets you set these preferences so that the output is nicely formatted with indents and bold subtotals where appropriate.
Include Mapping Information: If this option is enabled, the add-in will create separate columns for each row field in your PivotTable (similar to Excel’s Tabular form layout). That means if you have a multi-level row hierarchy (e.g. Country > Division > Product), each level gets its own column in the output, making it easy to filter or pivot the flattened data further.
If disabled, the row labels will appear in one single column (more like the compact PivotTable format where items are indented in one column).
Use SUM Instead of SUBTOTAL: By default, Accelerate Excel uses the SUBTOTAL function for any subtotal rows it creates (since SUBTOTAL can exclude other SUBTOTALs in ranges). However, you can choose plain SUM functions instead.
Exclude Repetitive Subtotal Rows: In some PivotTables, you might see repeated subtotal lines for the same category (for example, a “Total Expenses” line showing up multiple times under different parent categories).
Enabling this setting will remove duplicate subtotal labels so that each subtotal appears only once, making the report cleaner and less confusing.
Create a Separate ID Column: If your PivotTable’s rows include identifier codes (such as account IDs, product codes, or any sort of ID alongside a description), this setting will put those IDs in their own column. The result is a two-column display for that field – one column with the ID, and the next with the description. This is perfect for financial reports where you want to show, say, account numbers next to account names.
Flexible Output Formulas: As mentioned, you have a choice of outputting values or different formulas. The Convert PivotTable tool can output:
Static hardcoded values (just numbers and text, no formulas).
SUMIFS formulas pointing to the original data source (essentially re-creating the pivot calculation with SUMIFS).
GETPIVOTDATA formulas pointing to the PivotTable (these retrieve values from the pivot cache by field name).
CUBEVALUE/CUBEMEMBER formulas if the PivotTable is using the Data Model (Power Pivot).
This flexibility means you can choose a format that best suits the next steps in your workflow. If you need a standalone snapshot, go with values. If you want the numbers to update when data changes, choose one of the formula options. (And if you’re unfamiliar with Excel’s CUBE formulas, read this:Excel CUBE Formulas: A Must-Know for Data-Heavy Consulting Projects)
With these options, Accelerate Excel empowers you to get the output you need. You’re not stuck with a one-size-fits-all conversion. You decide the layout, formulas, and details, and the tool executes it.
More Examples
Convert a PivotTable to SUMIFS
Convert a PivotTable to CUBEVALUE and CUBEMEMBER Functions
What is Accelerate Excel?
Accelerate Excel is a productivity add-in for Excel (Windows desktop Excel) that packs a suite of tools to streamline repetitive and complex tasks.
It was built with finance professionals and consultants in mind. Instead of resorting to manual work or complicated VBA macros for certain tasks, Accelerate Excel provides ready-made solutions accessible from a custom ribbon inside Excel. Think of it as an Excel power-user’s Swiss Army knife: it adds dozens of features to Excel, from quick formatting shortcuts to advanced data manipulation utilities.
Who Benefits the Most
While almost any avid Excel user can find value in Accelerate Excel, it’s especially useful for:
Financial Due Diligence (FDD) teams: During M&A due diligence, analysts build complex databooks and Excel models combining data from various sources.
Finance departments handling special projects: M&A-related work, refinancing analyses, restructuring models, one-off strategic analyses. These work streams often require tailoring Excel beyond standard reports.
Transaction Services, Commercial Due Diligence, and Strategy Consultants: Advisors in transaction advisory and strategy roles often prepare data-heavy packs for clients, needing to aggregate and refactor data quickly.
Excel Power Users in any industry: If you’re an Excel guru known for building complex spreadsheets, you’ll appreciate how Accelerate Excel takes care of the mundane tasks (like cleaning up formats, tracing formulas, etc.) so you can focus on the complex logic. Even if you use only a few of its features, you’ll likely notice a boost in your productivity.
Why It’s Worth It
Even if you end up using only a handful of the features (say the PivotTable converter and a couple of formatting tools), the time you save on those tasks each week adds up tremendously. In fact, in our experience, professionals using Accelerate Excel in due diligence projects have reduced their Excel work by about 50% on average.
The cost of the add-in is minor compared to the value it delivers. A yearly subscription is about USD $72, that’s just $6 a month, which is negligible next to the hours of work it can save you. For anyone billing their time (or crunching on a deal timeline), the ROI is basically a no-brainer. Even for internal corporate teams, freeing up hours of manual work allows you to allocate time to more meaningful analysis and quality checks, improving overall output.
How to Get Accelerate Excel (Free Trial & Download)
Getting started with Accelerate Excel is easy, and you don’t need any special IT permissions in most cases. Here’s how to get the add-in and try it out:
Download the Installer: Go to Download on the Accelerate Excel website and click “Get Download Link.” Enter your email when prompted, and you’ll immediately receive an email with the download link for the add-in. (Don’t worry – we won’t spam you. We might send a follow-up for feedback, but that’s it. Your email is kept strictly for license purposes.)
Install the Add-In: Run the downloaded .msi installer. No admin rights are required to install, so even if you’re on a secure work computer, it should install without issues. The installer will add the Accelerate Excel add-in to your Excel.
Launch Excel: Open Excel and you’ll see a new Accelerate tab on the ribbon. The add-in comes with a free trial period (fully functional), so you can start using all the tools, including Convert PivotTable, right away.
Activate if You Love It: After the trial period, you can purchase a license key to continue using the full feature set. A license can be purchased securely via our website when you’re ready.
Upon purchasing, you’ll receive a key to be enterered into the add-in.
Stay Supported: Accelerate Excel is a locally-running Excel VSTO add-in (it runs within your Excel, on your PC). It doesn’t send your data anywhere – all your information stays on your machine. If you run into any security warnings during installation or first use, refer to our installation guide (which covers how to handle Excel add-in security settings) or reach out to our support team. We’re here to help make sure it works smoothly in your environment.
That’s all it takes. In just a few minutes, you can be up and running with Accelerate Excel and immediately take advantage of its time-saving features.
Work Faster with Accelerate Excel
Simplify your Excel work with the Accelerate ribbon comprising productivity tools for M&A Transaction/Deal Advisory, FDD and other Excel power users.
Conclusion
PivotTables are a fantastic tool for analysis, but they often are not the final destination for your data. When it is time to create a client-ready report or dive into custom calculations, you need the flexibility of a normal worksheet. Usually, that means spending valuable time manually converting and polishing PivotTable outputs or building summaries from raw data. With Accelerate Excel, you can speed up this process.
See the difference for yourself by starting with the free trial (no commitments). Once you see the time it saves on tasks like PivotTable conversion, you will not want to go back.
Download Accelerate Excel and let it lift some of the manual Excel work from you, one PivotTable at a time.