In this short video I demonstrate how to create range names quickly based on labels. Range names are a powerful formula feature. I also demonstrate their use.
There are no old roads to new directions.
attributed to Boston Consulting Group
Excel VBA Sort Sheet Tabs by Colour
Get your sheet tabs organised
Using colours on your sheets can help you navigate and organise your file. If you want to sort your sheets by colour you can use a macro to speed up the process.
Begin challenging your own assumptions. Your assumptions are your windows on the world. Scrub them off once in a while, or the light won’t come in.
Alan Alda – commencement speech – 1980
Excel File is Open VBA Function
Useful function
When working with files in VBA code, you may need to know if a file is already open. A function can return TRUE if a file is currently open and FALSE if it isn’t.
A good scare is worth more than good advice.
Proverb
Excel Month on Month Movement
A PivotTable solution
If you need to find the movement from the previous month a PivotTable can be your friend and do most of the work for you.
Discovery consists of seeing what everyone has seen and thinking what nobody has thought.
Albert Szent-Gyorgyi (1893-unknown)
General Format
The General format is Excel’s default format.
It doesn’t really do much. It can be handy to use though if Excel applies a format you don’t want, like a date or a percentage.
The keyboard shortcut to apply the General format is
Ctrl + Shift + ~
Be careful if you forget the Shift key as it will display formulas. If that happens, press it again (without the Shift key) and it will revert back to normal.
MIN and MAX and Dates in Excel
Automating the latest date
The MIN and MAX functions can provide easy ways to capture current dates.
The different between ordinary and extraordinary is that little extra.
Anonymous
One Minute to Excel #28 – Eight More Charts in a Minute
Fast mouse work to the rescue
OK in the last video I cheated and used Sparkline charts to create 8 charts in a minute.
This time I set myself a real challenge to create 8 real charts in a minute.
Its close – check out the video and learn a few useful techniques.
Navigation Columns in Excel
Great for long reports
When working in long spreadsheets it can be a good idea to use a navigation column to make it easier to move between the different sections.
It is the supreme art of the teacher to awaken joy in creative expression and knowledge.
Albert Einstein (1879-1855)
One Minute to Excel #27 – Eight Charts in a Minute
Sparklines to the rescue
Let’s see if I can create 8 charts in a minute – one for each state and territory in Australia
I cheat a little bit and use Sparkline charts. If you haven’t seen Sparklines before check out the video.
Switching Between a Line and a Column Chart
Keeping charts simple is a good rule to live by. Two of the simplest charts are a column chart and a line chart. People have their preferences. Here is a short macro that lets you switch between the two chart types.
One Minute to Excel #26 – Fix AutoSum’s Weakness
Get the right range every time
AutoSum’s cryptonite is a blank cell – it stops AutoSum in its tracks every time.
Here’s how you can avoid AutoSum’s blind spot.
Excel Power Pivot Limitations
It is not all bad
Power Pivots and the Data Model take pivot tables to another level or two, but there are a few limitations you need to be aware when you use Excel’s data model to create a pivot table.
Action may not bring happiness, but there is no happiness without action.
Benjamin Disraeli (1804-1881)
Stop Cafe Changing in Excel
A client has recently added a cafe to their budget and frustratingly Excel wants to put the accent above the e. Here’s how to stop that.
Knowledge of what is possible is the beginning of happiness.
George Santayanna (1863-1952)
Spanish-American philosopher and poet
Sheet Exists Function for Excel VBA
A function macro to help another macro
Often when you are working with Excel VBA you need to confirm if a sheet exists based on the tab name. This Function can be used to do that. It works with the active file (workbook).
We should not let our fears hold us back from pursuing our hopes.
John F Kennedy (1917-1963)
Excel and MYOB
Calculation issues
These two Apps don’t always play nicely together. MYOB has a bad habit of turning off calculation in its Excel files. Here’s how to fix it quickly.
If you add only a little to a little and do this often, soon that little will become great.
Ancient Greek poet Hesiod
Works And Days
Outlook macro to stop sending an email without an attachment [VIDEO]
Automatic attachment check via VBA
I don’t do many Outlook macros, but this one is really useful and I have used it for a long time. It looks at your outgoing email and checks to see if you have the word attach in it and then checks to see if you have an attachment. It warns you if you don’t.
A change in perspective is worth 80 IQ points.
Alan Kay– computer scientist
Talk at Creative Think seminar
Formatting Input Cells in Excel
Formatting input cells consistently is best practice
It is common to have a lot of input cells in an Excel file. It is best practice to use the same format for all the input cells. This makes it easier to identify input cells for you and the user. Here is a technique using Conditional Formats that may be useful.
“If you care too much about being praised, in the end you will not accomplish anything serious. Let the judgments of others be the consequence of your deeds, not their purpose.”
Leo Tolstoy
A Calendar of Wisdom
Excel Data Validation Blind Spot
Macro to fix it
One of the problems with Excel’s Data Validation is that it is possible to have an invalid entry in a data validation cell. This can be caused by Paste Special Values or linked drop downs that don’t update if an earlier drop down is changed. To easily identify invalid cells you can use a macro.