With a product name like PowerExcel…
We gladly raise users’ expectations of what they can do beyond the “everyday” capabilities of their familiar spreadsheet.
One area of particular interest, and indeed where PowerExcel shows its strength very nicely, is how it stacks up against PivotTables.
Excel’s Pivot Table Functionality and Familiarity
Excel offers fantastic functionalities—one notably being the PivotTable. For those of us who have been accountants for over 30 years, the PivotTable has been a go-to tool to manage extensive data.
A large set or range of data—provided it is formatted and looks like a proper database table—can provide an underlying data set for a nifty PivotTable, which users can slice and dice at will.
By now many Excel users have used PivotTables and even if they don’t consider themselves pros, they have a familiarity with how PivotTables work and the concepts and capabilities they employ.
Indeed, concepts like “field” (or dimension, in OLAP lingo) and stacking/reordering data via drag-and-drop have become second nature (though these concepts and capabilities have existed at least since the emergence of OLAP/multidimensional database products).
In sum, that is what a PivotTable is, a quick and intuitive way to visually organize underlying data (functioning as the “database” for the PivotTable) and then render it in a familiar Excel format. You know, with data along rows and columns, and the ability to use filters.
PivotTables will also handle math: for example, it auto-creates sums of the logical groupings of data from the underlying data set.
Along with the concepts and capabilities, there’s the look and feel of a PivotTable: I believe it’s fair to say that most good Excel users would recognize the use of a sidebar, and, working with it, defining rows and columns, and filtering, in order to create new views of the data.
It is highly intuitive and powerful and, even if intimidating at first, can be a great tool for organizing and analysing/reporting on those formatted data sets.
Taking Cues from PivotTables—and Becoming Collaborative
By design, we’ve kept PowerExcel’s functionality similar to Excel’s PivotTable. This approach allows users to quickly adopt our technology, which is crucial when rolling out new enterprise-level software.
And this notion of being enterprise-level is key: while PivotTables aid single user productivity, they are not usable for collaborative work. They exist, essentially, as work areas within individual spreadsheets.
Like all such spreadsheet files, they are potentially sharable via OneDrive (for what we consider, at best, a kind of “co-authoring” exercise). But these spreadsheets are not collaborative in the respect that each user can work independently on a shared model.
How it Works in PowerExcel
In PowerExcel, when creating a New Slice, a sidebar pops up that is similar to what you see in a PivotTable.
Then, choose a database, and a Cube
(Important note: a PowerExcel Slice can access any number of modelled data sets, whether local or Cloud-based, and can create views of a multidimensional report according to default settings.
Following that, I can double-click to open a user interface for filtering. The syntax is simple. I select what I want to filter on and move it from left to right. I can use drag-and-drop or shortcuts and check the checkboxes to for further fileting.
It’s the same functionality as a PivotTable but with some enhancements: for one, there’s greater flexibility for stacking dimensions and format customization, and I can easily insert rows and columns. All this additional formatting in PowerExcel works seamlessly with its filtering capabilities.
Overcoming PivotTable Challenges
In Excel’s PivotTable there is an issue with sorting: Excel relies on custom lists or alphabetical sorting, which can be different from what a user actually wants. If you have to turn off grand totals or need to define Q1 and gross profit calculations manually… Let’s just say, it can get tricky.
In PowerExcel, we solve the calculation issue easily. Calculate subtotals and grand totals directly in the central database, which Excel can’t do. Indeed, there is a rich formula language in this modeling environment, which works very much the way and Excel user thinks—virtually any calculation logic you want to create for a Member(s) within a Dimension can be accommodated, with the results showing in the PowerExcel interface.
Furthermore, the “resource burden” of calculating formulas is offloaded from Excel (where it occurs in a PivotTable) and into a more efficient database model (for more information on the modeling environment—employing OLAP cube technology—see https://paristech.com/products/olation/)
Write-Back Capabilities—Accessing the World of Planning
Because PowerExcel works in tandem with an optimized underlying database (a data-modeling environment that allows for “write-back”) it expands its enterprise usage beyond reporting/analytics and into the realms of budget and forecast planning.
This is a very rich topic and deserves further in-depth exploration. It is worth noting for now, briefly, two things:
(1) for all the capabilities provided by PivotTables, you can not easily type a number into a cell (practically second nature for spreadsheet users!) and get results. In PowerExcel, you can.
And, (2) with “write back” enabled, and because of it’s collaborative nature, PowerExcel is deployed as a front end for the sophisticated planning applications—measuring performance “against plan” involving 100s of users, literally across continents.
In summary, PowerExcel technology, for all that it resembles a PivotTable, addresses many of the shortcomings of that capability within Excel, as well as provides a powerful tool for enterprise-level usage.
We encourage you to try our free PowerExcel version. If your business needs more dimensions or members than the freemium offers, reach out to me on LinkedIn or look on our website. We’ve got paid versions of our product, and we’ve got great consultants with years of experience that can help you and bring productivity gains within your company.
