How to set the Sum function as the default in a PivotTable

Posted by Colin on 1 September 2018

Let's address a very important issue faced by many people that use Microsoft® Excel®. Sometimes when working with PivotTables, the Count function is set as the default instead of the Sum function. This can be frustrating as you then have to set each column value to Sum. Here's how to solve this issue.

The problem is caused by having blank cells in the PivotTable source data, and as a result, the values default to count. In order to rectify the problem, you have to replace the blank cells with zero values. Here is how:

1. Click on one of the values in the source worksheet.

2. Press F5

3. Click Special.

4. Select Blanks and then Select OK.


5. Enter 0 in one of the blank cells.

6. Press CTRL + Enter.

A great tip to macro for your Pivot Table source data tables! Running this tip will mean that your Pivot Tables will default to sum, when created.




Posted in: Top Tip  

NAB Superpay Closing

Posted by Colin on 20 July 2018

Are you using NAB SuperPay to process your superannuation in the SuperStream format? If so, we'd like to inform you that NAB will be decommissioning NAB SuperPay from 30 July 2018, and therefore from this date, you will have read-only access to this service.

To remain compliant with the ATO, you can move to another clearing house to submit your superannuation in a SuperStream format.

You can also pay your superannuation directly from your bank account on the day it's due. Plus, if you pay with AMEX, you get up to 50 days interest free, which can boost your cash flow!

Looking for other alternatives? Try Super Choice, ATO Small Business Clearing House (for 19 or less employees), or check with your company superfund to see if they also serve as a SuperStream clearing house. 

Posted in: News  

How to use Flash fill in Excel to change text

Posted on 25 June 2018
You know the drill. Someone has typed lots of data but not kept to the input rules and you need to fix up some stuff.

Just look at what they have done everything in lower case AND you only needed the first three letters of the month!














Flash fill to the rescue! Just give it an example of your translation and select flash fill from the data menu with it selected. Sorted. Time for a coffee 








Posted in: Top Tip  
< Previous | 1 | 2 | 3 | Next >