VBA AutoComplete

When you are typing code in the VBA code window you can press

Ctrl + Space Bar

to have Excel finish the word for you. Eg type


And then press Ctrl + Space Bar to have Excel finish the word Application.


Creativity Inc (Random House, 2014)

A brilliant book. Very accessible. It covers many aspects of encouraging and maintaining creativity in an organisation and it uses Pixar movies as an example.

It was amazing to hear how some of the Pixar movies changed as they were developed.

I listened to the audio book which was narrated by the author which always adds to the experience.

Ed Catmull worked closely with Steve Jobs and the book finishes with Ed’s thoughts on Steve.

Highly recommend, 10 / 10.

Counting Positives and Negatives

If column A contains positive and negative numbers, you may need to count how many of each. You can use the following two formulas.






There may be zeroes as well. The formula to count them is


Getting ahead in a difficult profession requires avid faith in yourself. You must be able to sustain yourself against staggering blows and unfair reversals.

Sophia Loren (1934 – )


All our talents increase in the using, and every faculty, both good and bad, strengthen by exercise.

Anne Bronte (1820-1849)

Inserting values that start with 0

To enter numbers that starts with a zero eg mobile telephone numbers, start with the single inverted comma ‘ then type the number and press Enter.

If you are going to enter a whole column of them, it may be easier to format the whole column as Text – see image below.

When you use the Text format you don’t need to use the inverted comma, just type the number.

Text format example

Calculation Warning

You have to be careful when turning off calculation in Excel. The calculation options are on the right of the Formulas ribbon tab – see image below.

When you turn off calculation (change it to Manual) it affects all the open files, not just the active one. The reasons you must be careful are

  • Most people don’t turn off calculation and hence always expect the sheets to automatically calculate, so they may rely on the results even though they might not have been updated.
  • When you save a file and calculation has been turned off, the file remembers the calculation state and when you or someone else opens the file it will turn off calculation which again causes the previous issue

When you turn off calculation always remember to turn it back on (change to Automatic) before you save the file.

Space Bar Tips

To select the current column or columns hold the Ctrl key down and press the Space Bar. (Remember tip – Ctrl + column both start with c)

To select the current row or rows hold the Shift key down and press the Space Bar.

Always dream and shoot higher than you know you can do. Don’t bother just to be better than your contemporaries or predecessors. Try to be better than yourself.

William Faulkner (1897–1962)