Here are a couple of suggestions for the Excel team that have been rattling around in my head.

External Data Cache – They should structure external data similar to the way they do pivot tables. Each pivot table is tied to a pivot cache, and there can be more than one pivot table to a pivot cache. This allows you to have multiple pivot tables based on the same underlying data without having to repeat the data. You could, for instance, show sales by quarter and sales person and elsewhere show sales by product class and region. You don’t need to create two links to the sales journal. Rather, the sales journal data is stored in a cache and each pivot table gets its data from the same place.

The genesis of this suggestion is my difficulty creating financial statements from an external data table. Now, I’ll create an external data table from a trial balance and use array formulas to organize the data like a financial statement. That works until you add a new account or until an account has a zero balance. I have to manually add a line for new accounts (it’s actually account classes that group similar accounts together), and copy the formulas down. For accounts that are zero, I have to hide the row or delete it altogether. This isn’t a major pain, but it seems like it should be easier. I’m quite certain I’m not the first accountant to use Excel to create financial statements.

I want to create an external data cache that holds a recordset. For example, I could define an SQL statement that brought in all non-zero trial balance accounts. Then I can define several external data tables that have a CommandText property. In that property, I would define another SQL statement that uses the recordset in the cache as its table. I could make an ed table for current assets, property plant and equipment, long term assets, current liabilities, long-term etc. etc. Each of these tables would insert and delete rows as necessary when refreshed. I would also have to be able to add a header and footer and define formats like font, bold, indent.

Why not just use pivot tables? Well, as much as I like pivot tables as data analysis tools, I’m not such a fan of their presentation abilities. Or should I say my ability to manipulate them for presentation. Adding calculated fields and maintaining formatting, like column widths, is a pain is the arse.

In addition, this method could reduce workbook size.

Undocked Sheets – Why is it that worksheets within a workbook are so rigidly docked? You can look at two different worksheets in a workbook by creating a new window, but I want more. I want free-floating, dockable/undockable, modal/modeless worksheets. They wouldn’t be able to be moved outside the workbook container, but within that container they could be sized, positioned, and stacked in any fashion. They would have a zOrder property that would take the place of their physical position in the workbook. Code like Sheet2.Previous returns the sheet that’s to the left of Sheet2. But in the new world order of worksheets, it would refer to the sheet whose zOrder was Sheet2.zOrder – 1.

You could stack worksheets on top of each other. Perhaps the main worksheet would be docked normally with tabs on the bottom and maximized to fit the workbook container. Maybe the worksheet is a tax return and certain cells have a command button next to them that let you add up some numbers to be put in that cell. The command button would invoke the .BringToFront property of the calculation worksheet. The calc worksheet has some properties set so that you can hardly tell it’s a worksheet:

But it works like a worksheet, with cells, access to the calculation engine, and such. The user can enter numbers in the unprotected cells and click the PasteDetails button you put on the sheet. The button builds a formula in the calling cell to add up the numbers entered and invokes the .SendToBack method so the little calculator goes away.

Think about how easy dashboard applications would be to build. You could define the Top, Left, Width, and Height properties of various panes to create a collage of sheets. You can also set the .CanGrow and .CanShrink properties so that all the dynamic data is displayed. Setting .SizeHandles = xlNeither and .TitleBar.Visible = False (so the user can’t move it) ensures that the dashboard looks the way you intended.

So there they are. If anyone from Microsoft is reading, get started on these right away and keep me posted with your progress.