What separates the talented individual from the successful one is a lot of hard work and study; a constant process of honing.
Stephen King
Monthly Archives: March 2015
Increase decimals
To increase the number of decimal places press in sequence
Alt h 0 (Alt h zero)
To decrease the number of decimal places press in sequence
Alt h 9
Only those that dare to fail greatly can achieve greatly.
Robert F Kennedy (1925-1968)
Excel Hiding and Unhiding Error Fix
Sometimes when you hide or unhide, rows or columns, you can get an error message saying that Excel can’t move objects off the sheet. The solution is in the macro below.
We are all of us failures – at least the best of us are.
J M Barrie (1860-1937)
You cannot do a kindness too soon, for you never know how soon it will be too late.
Ralph Waldo Emerson (1803-1882)
Activating a sheet in VBA
Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.
Excel Options Dialog
To open the Excel Options dialog you can use, in sequence
Alt t o
Don’t hold down the keys, press one after the other.
To open the Advanced tab
Alt t o a
Avoiding danger is no safer in the long run than outright exposure. The fearful are caught as often as the bold.
Helen Keller (1880–1968)
Everything can be taken from a man, but one thing, the last of the human freedoms – to choose one’s attitude to any given set of circumstances, to choose one’s way.
Viktor Frankl, Nazi concentration camp survivor
Close Existing File
To close the existing file you can use
Ctrl + w
If you want to save and close you can use on after the other.
Ctrl + s
Ctrl + w
The s and w keys are close to each other, so it can be a quick way to save and close a file with one hand.
Useful Formatted Table shortcuts
These apply to a formatted table (Format As Table icon used from Home ribbon – see below)
Ctrl + a Selects the table data – no headings
Ctrl + + inserts a new blank row in the table
Ctrl + – (minus) deletes a row from the table
Ctrl + Space selects all the data in a column – no heading
Shift + Space selects all the data in a row
The shortcut below works in any table formatted or not
Ctrl + * to select the table (use the * on the numeric keypad)
It is very difficult to have a meaningful life without meaningful work.
Jim Collins
Running a Macro From the Quick Access Toolbar
The Quick Access Toolbar (QAT) can really speed up your work in Excel. You can even attach macros to the QAT.
Let us read and let us dance – two amusements that will never do any harm to the world.
Voltaire
Excel 2013 Power Programming with VBA (Wiley)
This is the third version of this book that I have bought. I own XL 2000, XL 2007 and now this one. It’s worth updating as new features are added in each Excel version.
I highly recommend this book if you have been using macros for a while and want to take them to another level. It is not a step by step instruction guide you need to read and absorb.
It demonstrates and explains many best-practice techniques but it can take time to digest the ideas. It has a companion website with the examples from the book.
(John Walkenbach is my favorite Excel author and I was honoured that he wrote a recommendation that appears on the back of my own book)
Copy As Picture
When copying between Excel and Word or PowerPoint, in some cases you may want to copy the range as a picture. This has the advantage of being easier to re-size and manipulate in Word or PowerPoint.
Select the range first and then use the drop down on the Copy icon on the Home ribbon – see below and select Copy as Picture
A dialog will display allowing you to specify the type of picture to create.
Click OK to accept the defaults
You can then paste into the other application and treat it like an image.
Your happiness ultimately comes from the way you work, not where you work.
Russell Simmons
Adding a Percentage to a Subtotalled List
Excel’s formulas are powerful. As an example we can create one formula that can be copied down to add a percentage calculation to a subtotalled list. This formula demonstrates a couple of useful techniques.
Obstacles are those frightful things you see when you take you eyes off your goal.
Henry Ford (1863 – 1947)