I have mentioned before that blank cells in your data can affect Pivot Table defaults in Excel. They can also reduce the effectiveness of some keyboard and mouse shortcuts. The macro below populates blank cells in the selected range with zeros.
FREE Webinar Recording – Chapter 5 of My Book
The latest and fourth webinar for my book has just been uploaded.
It covers Chapter 5 and the topics include data cleansing and the Format as Table feature and getting external data.
VBA Window Tip
To open the VBA window press
Alt + F11
This shortcut actually toggles between the Excel window and the VBA window – but if the VBA window isn’t open, it will open it.
Believe you can and you’re halfway there.
Perfection is not attainable, but if we chase perfection we can catch excellence.
Windows has a built-in screen clipping feature, but I paid for an app that makes the process a lot easier and is great for manuals and documentation.
Stop Text Being Input
If you want to stop text being entered in a range you can use a Custom Data Validation formula.
Select the range and press in sequence, not held down, Alt a v v. This opens the Data Validation dialog.
From the Allow drop down select Custom (last entry).
In the Formula box enter the following formula
Replace A2 with the top left cell in your range. Make sure there are no $ signs in the reference.
Nice to get emails like this
If you think you are too small to make a difference, try sleeping with a mosquito.
Object variables are the variable types that have their names written in black (right hand side) when you define them – see example image below for Range, Worksheet and Workbook. These are the most common objects used. There are two important things to know about using object variables.
Close Excel Quickly
In the more recent versions of Excel, when you click the X in the top right corner, you only close the current file.
In the older versions this would have closed Excel down, and you could then save each file in sequence.
To get the same effect in the new Excel, hold the Shift key down whilst you click the X with the mouse.
If you want to save all the open files click here to see a blog post with a macro to do just that.
Lose an hour in the morning, and you will spend all day looking for it.
FREE Recording – Chart Tips and Tricks
I have posted the video and material links for my two most recent free Excel webinars – Chart Tips for Excel 2010 and 2013.
Attendee feedback scores were 86% for Excel 2010 and 91% for Excel 2013.Free Webinar Recordings and Materials
I was reading a magazine last week and a chart caught my eye. I thought I could improve it. I recreated it in Excel – its close to the original – see below – I didn’t quite match the column colour.
The great end of life is not knowledge but action.
T H Huxley (1825-1895)