Excel pivot tables are very useful. Most of the time it's using them to produce summary management reports such as monthly profit and loss, monthly sales and margins, etc. This is one way to use them, as a means of creating standard reports much more quickly than you do now.
But there's another way of using pivot tables, not just as a presentation tool for standard reports but as a wonderful 'analyser' - a way of digging around among tens of thousands of transactions and 'slicing and dicing' them to find out what's going on in your business.

Even quite a modest-sized business may generate 20,000 or more transactions on the computer each month. People sometimes print these out as audit trails or transaction reports. But leafing through pages of print-out rarely tells you anything since you can't see the wood for the trees.
However, if you simply export those 20,000 transactions into Excel and put them into a pivot table, you can summarise them and get the answer onto one screen in just a few seconds.

Consider those 20,000 transaction records. Each will hold the date of the transaction, the customer or supplier, the value, the details of what it was for, the quantity, the price, the job number, and more. Each column represents a data field, a piece of information held about this transaction on the computer.
And you can summarise this data in any way you want - literally. On the right of the Layout screen of a pivot table is a list of all the field names in each record. You can enter any combination of these fields into any of the 4 layout dimensions (Row, Column, Page, Data).

Drop any combination of fields into the pivot table Layout and Excel will calculate the resulting report for you in seconds. Therein lies the tremendous educational value of pivot tables. You start to learn what data you will need in order to create a particular report.
At the beginning it's not at all intuitive how a certain combination of fields will produce a certain report. The only way really is to suck it and see. Just experiment by dropping different fields into different areas, press the button and see the results.
When you get a report that looks good, print off a sample page, then write onto it the data fields dropped into the 4 areas. Build up a list of reports and gradually learn that if you want to create a report that showed ABC, you will require data fields XYZ to create it.

• Reconciliations

  Pivot tables are really useful for digging around in reconciliations. A classic example is where the Aged Debtors Report in the Sales Ledger disagrees with Debtors Control in the GL. Just drop all the Debtors Control transactions into a pivot table, analyse by transaction type, and there's that nominal journal that someone's analysed to Debtors Control.
  Or the bank reconciliation on the computer doesn't tie up with the bank statement. Export all the bank account transactions into Excel and summarise them by bank statement page number. Where the totals on the report start to disagree with the totals on the bank statements, that's where the error is.

• Credit Notes

  Credit notes should always be scrutinised because a credit note indicates that something has gone wrong. Why didn't we get it right first time?
  Use a pivot table to summarise the value of credit notes by customer over the months. Are we regularly giving a lot credits to one customer? If so, why? Or is there one particular person authorising them? Then, instead of SUM to see value, use the COUNT function to see the number of credit notes. One company found that a supplier was giving them dozens of credit notes each month for just £3.50 each. It wasn't a lot of money, so they told him to roll them all up into a single credit at the end of each month and save the both of them a lot of admin.

  You don't know what you are looking for But of course, when you first start using pivot tables to examine your data, you probably don't have much idea what you are looking for. So any message needs to hit you in the eye. There is no perception without contrast. In other words, a figure on its own often doesn't tell you anything; you have to see it next to other figures before it becomes significant.

• Month on month or week on week trends

  This is one of the problems with the traditional layout of reports that shows This Month's Actuals vs Budget, and YTD Actuals vs Budget. It doesn't give you any idea of the trend. Did the figures go up or down from the previous month?
  Pivot tables allow you to Group the data into any size buckets you want - break it down into month on month totals, week on week, quarter on quarter, whichever you want. Often, just doing this alone will reveal important anomalies or trends that the traditional report layout does not.