Oliver Couzens writes: “I have an Excel 2007 spreadsheet that includes columns for dates and amounts of sales. How can I automatically create monthly totals?”

If your dates are in column A and your sales amounts in column B, enter =A2 in cell C2. Click the Number format down arrow and custom format this cell mmm. Under the Font tab in this box, change the font colour to white, assuming you are using a white background. Drag this entry down the column. Give your columns the headings Date, Sales and Month.

Switch to the Data tab. Click anywhere in the data range and click the Subtotal icon at the right of this ribbon. Make the Subtotal dialogue box read “At each change in Month, Use function Sum, Add subtotal to Sales, Summary below data,”.

Under the Home tab, click the down arrow at the right of the Styles group. At the bottom of the Styles box choose, New Cell Style. For the Style name enter “RowLevel_2” and format as Currency, Bold font, and a colour.

Highlight the complete table. Under the Data tab, click the little arrow beside the Outline icon. In the Settings dialogue box check the Summary rows below detail box and click the Apply Styles button. At the top left are three small numbers, 1, 2 and 3. Click 1 for a Grand Total. Click 2 for your monthly totals. Click 3 to show the full data sheet.

In earlier versions
As the majority of our readers don’t have Office 2007 yet, here’s a way of creating monthly totals in earlier versions.

On the Format menu choose Style. For the Style name enter RowLevel_2 and format with Accounting, Bold font and a colour.

If your dates are in column A and your sales amounts in column B, enter =A2 in cell C2. Custom format this cell mmm. Using the Font colour tool, change the font colour for this cell to white, assuming you are using a white background. Drag this entry down the column.

Give your columns the headings Date, Sales and Month. Hold down the Shift key and click on the first and last cells in your data range to highlight it. Choose Subtotals on the Data menu. Make the Subtotal dialogue box read At each change in Month, Use function Sum, Add subtotal to Sales, Summary below data.

Choose Group & Outlines and then Settings on the Data menu. Check the ‘Summary rows below detail’ box and click the Apply Styles button. You should now have a display as in screen 2. At top left are three small numbers 1, 2 and 3. Click 1 for a Grand Total. Click 2 for your monthly totals. Click 3 to show the full data sheet.

Personalising Excel 2007
Anyone coming to Excel for the first time with version 12 should quickly find their way around, as it is intuitive. But those who have been using Excel for years and then upgraded to Excel 2007 can find it very frustrating. Ironically, those few people swapping from Lotus 1-2-3 get more help as version 12 still, under Excel Options, allows for transition formula entry and evaluation.

One saviour for those set in Excel’s earlier ways is the new Quick Access toolbar. By default it comes with Save, Undo and Redo buttons, but you can right-click on any button on any of the Ribbon tabs, choose Add to Quick Access Toolbar and another of your frequently used tools is immediately available.