I have learned more from my mistakes than from my successes.
Sir Humphry Davy (1778-1829)
Monthly Archives: May 2015
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
appli
And then press Ctrl + Space Bar to have Excel finish the word Application.
Related Posts
Excel VBA Command to Clear the Clipboard
Stop users pasting after the macro is run
When using copy and paste in a macro it is a good idea to clear the clipboard at the end of the macro. If you don’t, the user could use paste to paste the last thing you had copied in the macro.
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.
One does not know – cannot know – the best that is in one.
Friedrich Wilhelm Nietzsche (1844-1900)
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.
Positives
=COUNTIF(A:A,">0")
Negatives
=COUNTIF(A:A,"<0")
Zeroes
There may be zeroes as well. The formula to count them is
=COUNTIF(A:A,0)
Generating Random Text Entries in Excel
Create random first and last name combinations
In my previous blog post I discussed generating random numbers in Excel. What about generating random text? E.g. generating random names for testing or training purposes.
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
All our talents increase in the using, and every faculty, both good and bad, strengthen by exercise.
Anne Bronte (1820-1849)
Footprints in the sands of time are not made by sitting down.
Proverb
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.
Two Random Functions in Excel
They both create random numbers
Excel has had the RAND function for a long time. In Excel 2007 a new function was added. Called RANDBETWEEN it made it easier to create random numbers.
The least expensive education is to learn from the mistakes of others.
Anonymous
Story Telling with Data website by Cole Nussbaumer
May 5th 2015
May 5th 2015
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.
Seth Godin
May 5th 2015
May 5th 2015
Some good insights from Seth Godin about how to display a message and get your important point(s) across.
Excel’s Smart Art
Microsoft boosted Excel’s graphic engine and increased the chart formatting options and it also added Smart Art. Smart Art includes built-in graphics that represent different types of processes.
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)
http://www.healthdataviz.com/
1st May 2015
1st May 2015
Often times you need to identify what NOT to put in your dashboard.
““Business, like life, is all about how you make people feel. It’s that simple, and it’s that hard.” – Danny Meyer (via @JaysonGaignard)”
@tferriss
2:06 PM - 30 Apr 2015
2:06 PM - 30 Apr 2015
Good charts allow you to sell the data and the findings.