More Calendars for Excel

[29 April 2013]

Dates seem to cause people problems. Ask five people to enter a date into Excel and you'll get dates entered five different ways. Excel does its best to understand dates and recognises many different date formats. It automatically switches the European d/m/y format to the US m/d/y format when a file is opened in the appropriate environment. But any developer will tell you that dates can be a problem. For me the answer is to help people enter dates correctly by providing some sort of date picker. Some time ago I published my Build a Pop-up Calendar for Excel tutorial which was written for Excel 2003. It has been the most popular of all my tutorials but it isn't suitable for everyone. It relies on an ActiveX control that Microsoft doesn't include in every version of Microsoft Office. Also, there are "issues" with some ActiveX controls on 64-bit systems. When Microsoft dropped that particular control from Office 2010 I published an updated version for Excel 2003/2007 and followed it up with a series of video demos.

Continuing the updates, I have just published an updated version of my tutorial Put a Permanently Open Calendar on a Worksheet which used the same ActiveX control, and also added a new tutorial Put a Date Picker Calendar on an Excel Worksheet which uses a different (hopefully more widely available!) ActiveX control. So, if you are having trouble with dates in Excel, or just want to make life easier for yourself and your colleagues, take a look and see if any of my ideas might suit you.

An Age Old Problem

[29 April 2013]

Continuing the mammoth task of updating my web site I have just published a revised version of my tutorial Working Out a Person's Age in Excel. As well as offering a couple of solutions to the task of calculating a person's age in years from a their date of birth, this tutorial offers an insight into using nested IF functions. I have updated the screenshots for the current the versions of Excel and added a note about Microsoft's guilty secret, the DATEDIF() function.

More Free Handouts

[27 April 2013]

As I add new tutorials and update old ones I will no longer be adding a printer-friendly version of the tutorial. Instead, I am including a copy of the tutorial in my handout-style PDF format. These handouts are completely free to download and you can print them out or view them on a computer, tablet or smartphone. Look for the link on the tutorial's page or see a full list of available handouts on my Free Courseware page.

Teachers and trainers are welcome to use my handouts for courseware but please observe my copyright and do not remove my identification from the pages. If you would like to help support this site (yes is costs money to run!) and would like to make a donation, that would be very much appreciated. You'll find a link to PayPal further down this page.

Build an Audit Trail for Access

[23 April 2013]

Like most developers I have a large collection of prototypes, demo files and samples that I have built up over the years so when a friend (thanks John!) called me a few day ago to ask if I had any suggestions for keeping an Audit Trail I rummaged around and found an example I had built way back in Access 2003. I had thought about publishing it here as a tutorial but had never got around to it. Well, at last, with a few tweaks and updates here it is. Access is commonly used in a multi-user environment so it can be important to keep track of who did what and when they did it. Based on VBA code, the Audit Trail tool can be run from any Access form and can keep track of changes to the data in the form of edits, additions or deletions. The details are all stored in a single table. The Audit Trail tool is suitable for Access 2003, 2007 and 2010 (I haven't tested it on Access 2013 yet but I can't think of a reason why it wouldn't work there too!). It's really easy to build an implement, and I have included a couple of sample files and a downloadable PDF version of the tutorial too. So if you've found yourself asking "Who did that?" then take a look at my new tutorial: Build an Audit Trail for Access.

Grrrr!

[23 April 2013]

I'm slowly migrating my web site from Microsoft FrontPage 2003, a program which although scorned by many, has served me well for many years, to Microsoft Expression Web 4. In the process of converting pages I've noted that phantom HTML code is being generated at the foot of some of the pages. I don't know why this is happening. I delete the code, save the page, and it just comes back. It's driving me nuts and causing me a lot of extra work having to rebuild pages from scratch. So, in the meantime, I apologise for any untidy-looking pages - please bear with me!

Happy New Year (Who He?)

[9 January 2013]

If you are a regular visitor to this site you will be used to seeing a picture of a handsome bearded fellow dressed in a blue check shirt (his favourite!) up there at the top of the page. He was maybe around 50 years old, carrying a little too much weight and losing his hair. It has been replaced by a portrait of an older gentleman, smartly dressed with shirt and tie, also bearded but greyer and thinner on top, could be the other chap's father. The year turned and I decided it was time for a change. Of course they are both me. I can't remember when the first picture was taken but it has been there a while and I thought it was time for people to see me as I really am, now in my 60s but still alive (just) and still working (unfortunately). One day, and it happens to us all, I looked in the mirror and saw my father looking back at me. He is slowly being replaced by my grandfather. But, apart from the aches and pains, growing old isn't so bad and I plan to be here for quite a while. If you want a reminder of the younger (less handsome) me, point your mouse at my picture. Happy New Year!

Learn About What-if Analysis with Excel

[13 November 2012]

What-if analysis is an important part of business management and planning. Excel has some excellent tools to help you forecast what might happen if circumstances change and help you predict what effect this might have on your business. My latest video tutorial Excel's Business Tools - What-if Analysis uses the simple example of a cash loan to demonstrate some of these tools. It makes use of Excel's PMT Function to calculate repayments on a loan, then shows how you can use the Goal Seek tool to manipulate variables such as the amount borrowed, repayment period and interest rate to arrive at a desired result. You will see how to use Data Tables to display a large number of calculations employing one or two variables and finally use Conditional Formatting to present the results in an easily understandable way. I've also included a new PDF handout to accompany the video, illustrated with colour screenshots, describing the tools used.

You can find this tutorial and all my other Excel videos in my Excel Video Tips section.

New Pop-up Calendar Videos

[26 September 2012]

Many people are reluctant to get to grips with VBA and in particular VBA UserForms because they think it's going to be difficult, or maybe they just don't know where to start. My Pop-up Calendar for Excel tutorial is a great place to start. Not only do I take you through all the tasks step-by-step but I explain what you are doing, why you are doing it, and how everything works. Now I have added a set of three videos demonstrating all the steps in the tutorial. You can find them in my new VBA Video Tips section. Follow the tutorial and you will learn how to build a useful VBA UserForm. You'll write some VBA code and finally create an Excel Add-In so that you can distribute your work safely and easily to your colleagues.

New Pop-up Calendar Tutorial

[18 September 2012]

When Excel 2010 came along Microsoft dropped support for the mscal.ocx ActiveX calendar control that was at the heart of my popular Pop-up Calendar tutorial. Fortunately they replaced it with the similar MonthView control and I have finally got around to writing a completely revised and updated version of the tutorial for Excel 2007 and Excel 2010. Although the basic coding has not changed much the interface of the newer versions of Excel is sufficiently different from those that went before (remember Excel 2003 anyone?) that a complete rewrite of the tutorial was required. If you need to update your Pop-up Calendar, or if you are new to VBA and want a fun and useful project to get you started, take a look at my new Pop-up Calendar for Excel tutorial. If you are still using Excel 2003 (or earler) the original tutorial is still available - you'll find a link to it on the new tutorial page.

I have expanded this edition of the tutorial to include instructions for creating a Pop-up Calendar Add-In for Excel 2007/2010, and if you are too busy (I was going to add "too stupid" but one shouldn't insult one's customers) or just can't be bothered to build one for yourself then fear not, I have created ready-made files you can download.

I plan to add an accompanying video tutorial to accompany the Pop-up Calendar project so you can see just how easy this stuff is. After all, as I frequently tell my students, if I can do this it must be easy as I'm pretty stupid as anyone who knows me will confirm.

As always... watch this space. Or better still, subscribe to my news feed at http://www.fontstuff.com/rss/rss.xml

New Video Tips

[4 September 2012]

I've been experimenting with video tutorials for a while and I've finally got around to publishing some of my work. Starting with Excel I have published the first collection of videos on my new Excel Video Tips page. I'm hoping to provide something for everyone so you'll hopefully find something of interest there. Many of the examples are those I use when training so, if you have attended one of my classes and need some revision, or want to see the way I teach, you should take a look. The first upload includes a two-part introduction to creating spreadsheets, a three-part guide to the basics of Pivot Tables, and an in-depth look at using functions to get sense out of your business data.

My videos are hosted on YouTube so you can view them on my web site or visit YouTube and check out my YouTube Video Tips Channel for all my latest uploads. All are recorded at high resolution so they look great on your desktop, laptop or tablet PC. You can even view them on your Smartphone using the browser or the YouTube app.

The videos all have a sound commentary (by me!) and I realise that you might not have an audio facility on your computer or might be hearing-impaired, or maybe you just don't like my English accent. So I also plan to add complementary Viewlets where this is applicable. A Viewlet is a step-by-step animation, compiled from screenshots, which is viewed as a movie but contains text annotations. You might like to watch the video, then work through the exercise yourself at your own pace using the Viewlet as a guide.

Work is still at an early stage and I still have much to do on the video pages here so there are going to be some changes in the next couple of weeks. I've got lots of ideas for topics and will be working on some Access videos too. So, watch this space!

Pip pip!