Excel Suggestions

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:

.ShowBorders = False
.ShowTab = False
.SizeHandles = xlVerticleOnly

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.

Posted in Uncategorized

6 thoughts on “Excel Suggestions

  1. hummmm,
    I must be missing something Dick, could you not just use the same recored set to populate 2 diffrent tables?

    One problem with the cache set up, partiucally for “extrenal data”, which could be very large is the purging – try using a pivot is Access with a “resonable”
    size data set – worthless!!!


    the answer in this particualr case might be more manageable PT, which i think 12 might offer – but i guess not!

    I think the worksheet thing might be achivable with API’s – Steven Bullen’s Form Fun removes the caption from a VBA form, so i guess this could work for a window, and the z order is just the window index, and if the “base sheet” was maximised (even turned to a background type sheet) some of it might be possible… but yeah it’s a lot of work and over head, wold be mch better if it was defult.

  2. While we are on a wish list bent: why are central tendency functions limited to Average; Stdev; Count; Min; Max; etc. in PivotTables, SubTotal(), and StatusBar? Or is there a way (particularly in PivotTables) to do Median(); AveDev(); GeoMean(); etc? Is there a way to break into PT’s object model and add additional functions?

  3. Dick,

    As You know I’m not with the Redmonds people but I thought I would add my 2 cents here ;)

    External Data Cache:

    I can understand the sentence of what You raise but from my point of view I would say that it exist better server side solutions lika OLAP Cubes, stored procedures as well as persistent recordsets then to increase the size and also make the Data Cache more transparent.

    Although it will never be perfect the all in all is that the clients should not be “exhausted” with an unnessary overhead.

    BTW, in VSTO we can access and manipulate the ActionPane rather smooth in my opinion and it offer some great options when it comes to “order” specific data etc.

    Ross – Thanks for the heads up on the MSFT KB-article in Your blog :)

    Kind regards,

  4. doco – functions appear to be limited to those which satisfy f(f(x),…,f(z)) == f(x,…,z). My best guess it is because having generic functions would make pivots much more expensive in memory and cpu resources.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.