John Stokdyk
Share this content

Tell Bill Gates to fix Excel bug, urges 'Pivot King'

Tell Bill Gates to fix Excel bug, urges 'Pivot...

AccountingWEB's Excel Topic Expert David Carter has warned of a potentially lethal bug in Microsoft's Excel spreadsheet program and urged community members to tell Bill Gates to fix it.

"Now that the danger of the Y2K bug has passed, it's time to tackle the AZ bug," said Carter, the self-styled "Pivot King".

The problem occurs when users use Excel's AZ icon with a single column highlighted. Although data in that column will be sorted into alphabetical order, the other columns remain as they were - potentially corrupting the user's database.

"This is a Grade-A fault and quite lethal," said Carter. "One day you will need to do an AZ Sort and will click on the column heading without thinking. You won't realise what has happened until later, and if in the intervening time you have saved the workbook, your database is corrupted beyond repair.

"The long-standing flaw is highlighted in David Carter's latest AccountingWEB tutorial on auditing and financial reporting with pivot tables.

"If there was just one tutorial I would like an accountant to see in order to understand the sheer power of Excel and pivot tables, this would be it," he added.

The follow-up to last month's Sage Line 50 tutorial provides pivot-table ready accounting records (trial balance, debtors control summary, bank reconciliation, daybook analysis) and generates financial reports from them.

The AZ bug adds a bit of danger to the exercise. Carter advises users they can either train their subconscious minds always to click on a particular row, say Row 10, before clicking the AZ icon. Or they can send an email to Microsoft's chief software architect Bill Gates urging him to fix the glitch.

"If a thousand British accountants all complain politely about it to Mr Gates at the same time, maybe we'll get some action," said Carter.

Bill Gates can be reached at the email address [email protected].

David Carter's pivot table tutorials are among the most popular items on AccountingWEB. They include demonstrations on how pivot tables can be used for budgeting, and sales analysis. Sage users can also download and try Carter's SagePivot data extraction program.


Please login or register to join the discussion.

13th May 2005 16:11

This was written 5 years ago. For the record, the problem is sorted in Excel 2003, which now presents you with a warning before doing the Sort.

It just shows, maybe Microsoft do listen to us after all!

Thanks (0)
By admin
03rd Apr 2000 19:56

Pivot Tables - the Real Problem
Mr da Silva has made three important points. Firstly, that users will always prefer an Excel-based solution because that is what they are familiar with. Second, that by enabling Excel to be loaded onto an Intranet and read and manipulated by anyone with a browser, reports can easily be distributed to managers around the organisation.

So far, so good. His third point, however, is the problem area…..”Pointing the P/T to the accounting package's data tables means 'live' data - all you have to do is hit Refresh Data and wait about 30 seconds.“

This is crucial. Once the design of a report has been agreed, managers want to be able to run it themselves without having to call in the P/T expert every time. In theory they simply have to click the Refresh button to bring the latest data into the pivot table. However, in practice this only works if the data held in the ERP/Accounts package’s data tables is immediately suitable for analysis. Usually it’s not, because in most packages the data tables are optimised for transaction processing (i.e many, small, “relational” files) rather than for data analysis (i.e. one, large, “flat” file).

Companies like Cognos understand this and - at a price - will adapt the data files. But Microsoft do not, and if you use P/T’s on a system whose data tables are not “Analysis ready”, every time a manager wants to run a report he first has to call in the P/T expert to modify the data. It’s terribly frustrating. To that extent, I agree with Dennis. All Microsoft has on offer at the moment is a product. It’s only the specialist suppliers such as Cognos and Business Objects who have a working solution.

Microsoft must persuade the package vendors to output their data in a standard, Analysis-ready, format which can be loaded without modification into a P/T or SQL Server. It’s not difficult – just a matter of writing a couple of reports.

Thanks (0)
By admin
31st Mar 2000 15:38

Excel pivot tables central to Microsoft's information strategy
Taking Mr Veacock’s point about discussing what Excel CAN do for the accountant or analyst rather than what it can’t, Dennis claims that Excel pivot tables “cannot provide clients with *real* solutions that deliver long term value thoughout the enterprise however large or small”.

Dennis is about as wrong as it is possible to be. In fact, Microsoft have designated pivot tables as being THE standard analysis tool for end users, whatever the size of the task . In you have Excel 2000, select Data – Get External Data – Create New Query and in the “Open New Source” box you will see a new option – “OLAP cube”. OLAP may not mean much to readers now but it is of enormous significance. In our pivot table tutorials we have been calculating pivot tables as we go along. But when you get to big corporate databases with millions of records, doing it “on the fly” like this just takes too long. The answer is to PRE-CALCULATE the monthly totals via a specialist OLAP (“On-line Analytical Processing”) server such as that available on the recently released version 7 of Microsoft’s SQL Server database.

Go into Excel 2000’s on-line help, and look up “OLAP”. There’s a good discussion, in which occurs the comment that “OLAP DATA CAN BE RETURNED TO EXCEL ONLY IN THE FORM OF A PIVOT TABLE OR PIVOT CHART REPORT”. So, if you’re one of the 90% of PC users on the planet who use Office, Microsoft have decided that the way you will view corporate data will be via Excel pivot tables. I rest my case. Dennis, my friend, stop talking about Excel pivot tables in this vein or you are going to end up making a fool of yourself.

Thanks (0)
By admin
29th Mar 2000 15:55

Reply to Dennis Howlett on budgetting in Excel
Dennis is suffering from so many delusions that a single reply cannot deal with them all. However, let’s knock one on the head – use of Excel for budgeting. Dennis, I agree with you entirely on the widespread misuse of spreadsheets. The reason is that the spreadsheet format, while ideal for the FINAL OUTPUT of a report, is quite unsuitable for storing SOURCE DATA. You can get away with using formulas as a form of sticky tape to consolidate a handful of worksheets, but as soon as the spreadsheet gets beyond a minimum size (say 3 or 4 subsidiary worksheets) it all becomes too unwieldy and difficult to change. It is irrelevant to talk about proper documentation, using audit tools etc. because the basic design is faulty and the whole thing needs to be scrapped and rebuilt using proper methods.

By “proper methods” I mean storing the source data in a structured format just like any other data file. With the source data properly structured you can now use a report writer on it to produce your reports. At heart this is what Excel’s pivot table wizard is - the simplest and cleanest report writer program around.

Dennis, you have rightly raised a number of problems with Excel, but the answer to them has been staring you in the face throughout this series. As my very first tutorial showed, budgeting in Excel with pivot tables avoids all the problems associated with the traditional method. Your pivot can be made up of 20,000 records and it will still be as solid as a rock because there’s not a single formula in it to go wrong.

Larger organisations will only get sensible budgetting procedures when they accept the principle that the source data cannot be held in the same format as the final output. Once they store the data in one source worksheet and output the budgets in another via P/T's, then all the problems you mention will disappear.

Thanks (0)
29th Mar 2000 11:27

what excel can and cannot do
I suggest that this forum be used for explaining what excel can do, for the reporting and management accountant. Comments about excel's bugs, deficiencies and non intuative ways of undertaking certain tasks are best taken up with the vendors of the program. Releases of patches to the program show that they do listen. The program is not wholly written for or by accountants ,even though we use it as a useful tool.

Thanks (0)
By admin
28th Mar 2000 21:24

Not a bug - and not in SR-1!
Adge Cutler is right: Sorting a column only, when selecting that column and clicking AZ is exactly what you would expect. Deleting the contents of your database after pressing "ctrl-a" then delete is also what you would expect. Both can trash a database and neither are a bug.

With reference to "ctrl-z" - this does not work after a save (a limitation, not a bug!).

Possible Solutions:
Save files in generations (suffix with V1, V2 etc) regularly to ensure you can "go back to" a decent version. You can acheive the same for single sheets by control dragging the sheet tab at the bottom of the screen.

Press Shift-ctrl-8 to ensure you get all the data before sorting, tab can then be used to select the column you want to sort by.

Replace the standard AZ (and ZA!) tools and replace them by ctrl dragging the Data menu sort option onto the toolbar (select tools customise to do this).

Never work with single copy data in excel. This includes merely opening a spreadsheet. Always have the option to restore option incase a real bug trashes valuable data.

NB is this a real bug (also not in SR-1):

=CELL("Filename") does not recalculate automatically (ie you have to press F9 to get the right answer even if calculation is set to automatic).

Thanks (0)
29th Mar 2000 00:59

Back to basics
The most common sort-tragedy is when users have failed to grasp the principles of CurrentRegion and create, for instance, blank columns to space out adjacent columns of data. Oooops! The resulting mess is not pretty and I've seen grown men cry after they have inadvertently sorted their database into a jumble! But that's because they didn't read 'Guidelines for creating a list on a worksheet' which they would have found in Excel Help under 'lists, creating'...

Thanks (0)
29th Mar 2000 01:30

PivotTable on Accounting Database
I know this contribution belongs in a PT thread somewhere ...

Just following up on David's comment on exporting accounting data for use in PivotTables. Rather than exporting, why not point the PT directly to the data tables in the underlying database?

I have done this with several clients that use SunAcounts, the largest one where the PT is handling 250,000 records(!) with the results of 60 departments presented on just 1 Excel sheet which you can page-through (and even presented through IE4 browser).

Pointing the PT to the accounting package's data tables means 'live' data - all you have to do is hit Refresh Data and wait about 30 seconds.

Think of all the time you'll save!

Thanks (0)
28th Mar 2000 11:37

David Carter responds
Oh good, controversy. To take the positions of Mr Cutler and Dr Excel first, it is amazing how long people will go on defending the indefensible. To allow a user within two mouse clicks and without any warning message to corrupt their database beyond repair is just a piece of lousy design. It’s as simple as that. Even Microsoft themselves recognise the problem. If, after highlighting the column, you don’t click on AZ but from the menu select Data – Sort, Excel displays a “Sort Warning” message and tries to expand the selection to the whole database. At the very least Microsoft should display the Sort Warning with the AZ icon as well.

To take Mr de Silva’s point in answer to Dennis Howlett, Dennis has got this bee in his bonnet against Excel but Mr de Silva has got it absolutely right. Using a pivot table an accounting professional can get out virtually ANY management report within just a couple of minutes (clearly he must understand his data but this applies to the writing of any report). Excel is brilliant. Pivot tables are brilliant. There’s just this nasty AZ design fault to be sorted out.

I look forward to the day when any accountant can simply drop the transaction records our of his accounting package (Navision, Sun, Tetra, whatever) and produce management reports in a matter of minutes. But although the accounting vendors all claim that their packages will output to Excel, I have only found two that output in a format suitable for use in pivot tables. We need Microsoft’s backing to put pressure on the package vendors to output data in pivot friendly fashion, and hopefully the “AskBill” campaign will raise AccountingWEB’s profile at Microsoft U.S.A.

Thanks (0)
29th Mar 2000 10:31

Excel 2000 and IE5
It is a hard job convincing people who have already deployed Excel, and know how to use it properly, that they need to purchase another tool, such as PowerPlay, to realise their vision.
I'd like some feedback on whether (or when) PowerPlay can place a live slice 'n' dice model on the corporate intranet and the internet. An Excel PT (by design) integrates nicely with the IE5 web browser.
I agree Mr. Howlett's comments are valid if, as a company accountant, your work involves slicing and dicing the data BY YOURSELF. However, if your mission is to provide a channel by which your audience (perhaps scattered around the world) can study the 'views' THEY care about then the technology of IE/Excel is an easy method of delivering this. Hey look, it's free and most companies already have it!

Thanks (0)
By admin
25th Mar 2000 21:28

Surely the point is ...
I am confused about Dennis Howlett's (first) argument. In my experience, the power in the Pivot Table Report is that it takes UNDER A MINUTE to slice-and-dice your accounting and other data, wherever they may be, in all kinds of ways, to study trends etc. Hence its popularity with high-value people.

Thanks (0)
By Anonymous
27th Mar 2000 10:32

User problem, NOT Excel problem
Just because Mr Caster doesn't like the way the AZ Sort feature works does not mean it is a bug! I like the way it only sorts the column I select.

If I want it to sort a range of data based on one column, I just select all the data making sure the last cell selected is in the column I want to sort on. Then press the AZ Sort button.

It is certainly not lethal! I think Mr Caster is just annoyed because he messed up some data once by sorting, and saved it without checking the changes, and now he wants us all to blame Microsoft for it. Strange.

Thanks (0)
By admin
23rd Mar 2000 16:18

Not a bug
What do you excpect, when you are only selecting one column ... and anyway has the Pivot King not heard of Crtl+Z (undo).

Thanks (0)
By Anonymous
23rd Mar 2000 15:42

An aside to spreadsheet building
David Carter deserves full marks for effort in his campaign to promote pivot tables but I wonder how much a partner’s time is worth these days? If one spends say a day creating a reporting tool (little time I know but let’s be generous), then a conservative estimate has to put the time cost at anywhere between £800 and £1,120. Rather than contrasting DIY Excel reports to the cost of specialist products, I propose an entirely different approach by asking a different question: "What is the value of the information I will provide to my user community using this (or that) tool measured in terms of potential business lost or won in the time it takes to build the tool and/or provide the result?" This requires a different mindset but one I believe the modern accountant should consider. It's about understanding the value of information in the context of the whole. Does it really matter if the figures are 100%? Of course not, but it does matter that information provides strong pointers to trends not seen in the mass of data they represent.

Choose tools appropriate to the job because if as a profession we know the cost of everything and the value of nothing, then let’s not be surprised when barbarians in the shape of sales, marketing – take your pick - stampede the accounting edifice with cries of "Freedom from the tyranny of data."

Thanks (0)
Share this content