It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.
Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.
Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.
This is the first in a series of webinars dedicated to macros. Future paid sessions this month will expand on the techniques taught in this session.
Macro Webinar Materials
Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.
How to get CREATIVE! by John Cleese
No slides needed, this was way back in 1991!
This is especially relevant these days because we get so little “think” and “alone” time.
Where it all began
A great talk by Dan Bricklin the co-developer of Visicalc – the first desktop spreadsheet.
He talks about how game changing spreadsheets were for the desktop computer.
Discover where he was when the idea came to him and how he visualised it.
Decisions he made back then are still with us today.
This is a great video on data visualisation in general.
It includes two great tips, with examples, to improve your visualisations.
Cole has a great
website as well.
Excel’s Fill Handle has a lot of cool tricks. This short video demonstrates a couple of them.
Blanks in Excel can cause a lot of issues, but there is one instance where you can skip them and speed up copying and pasting.
Here is the problem. You have a single column range. Each cell in the range needs to be given a unique range name. Doing this manually takes time, but there is a quick and easy method to do it.
Check out my follow up article and VIDEO on the ITBDigital website on how to convert a vertical bullet chart into a horizontal one.
Click here for full article and video.
For the original bullet chart post
These techniques are based on ones in the great book
Excel 2007 Dashboards and Reports For Dummies by Michael Alexander
Check out my July 2014 article on Bullet charts on the CPA Australia ITBDigital website – click here to see the article. The video is below.
Bullet charts were developed by Stephen Few – see his pdf on bullet charts
The technique is based on one used by Michael Alexander in his great book
Excel 2007 Dashboards and Reports for Dummies by Wiley.
When you copy a formula in Excel, any relative references (those without dollar signs) may change depending on where you paste the formula. If you would like to copy a formula and not have the relative references change you have two options.
The Format As Table feature has many useful features that are worth taking advantage of. The previous post listed them. The video of this blog is shown at the bottom of the post.
Changing a sheet name and deleting the hyperlink cell are two processes that can break Excel sheet hyperlinks. The video is at the bottom of the page.
If I print emails I typically only want the first page. To do that in Outlook takes a few clicks each time.
When dealing with data lists in Excel it is a common requirement to extract the unique entries from a field. Excel has a built-in feature that will create a unique list.
Line charts are frequently used in Excel but their default settings leave a lot to be desired. See the transformation of a standard line chart to a simpler and easier to read line chart.
A Column chart walks into a Bar chart … sorry, I couldn’t resist that. Column charts are one of the most popular and straightforward of Excel’s many chart types. Its close cousin is the Bar chart.
Excel 2007 introduced the new interface called the Ribbon. It’s a cross between a toolbar and a menu. It also has a Quick Access Toolbar (QAT) that many people don’t seem to be using.
Excel allows you to easily hide a group of sheets BUT frustratingly, it won’t let you unhide a group of sheets. You have to unhide them one sheet at a time.