Excel Tutorials...

• Put a Date Picker Calendar on an Excel Worksheet
  Written for Excel 2007/2010 this tutorial aims to provide an alternative to the previous calendar tools which used the now discontinued mscal.ocx ActiveX control. Using the new Date and Time Picker control it shows how to add a handy date insertion tool to a worksheet without the need for any VBA coding, and how with a little VBA code it can be transformed into a powerful and useful addition to a worksheet.
• Put a Permanently Open Calendar on an Excel Worksheet
  Some of my most popular tutorials involve creating calendars. This one shows you how to place an ActiveX calendar control directly on to an Excel worksheet so that it is permanently visible. [Revised and updated for Excel 2007]
• Working Out a Person's Age - An Introduction to Nested IF Statements
  What might at first sight appear to be a simple task... you know someone's date of birth - how old are they? ...turns out to be quite a job. Here you will learn how to build a basic formula, how dates work, and how to get heavy with nested IF statements. [Revised and updated for Excel 2010]

More Excel Tutorials from my VBA Section...

• A Pop-up Calendar for Excel (Excel 2007/2010 version)
  This version of my popular tutorial has been updated for Excel 2007 and 2010. In addition to walking you through the steps of building a pop-up calendar it introduces VBA UserForms and tells you everything you need to know to write the associated VBA code. It also shows you how to build the Pop-up Calendar as an Excel Add-In for safe and easy distribution to other users. Ready-made examples of the files are available for free download.
• Writing Your First VBA Function
  This tutorial takes you step-by-step through creating a User Defined Function (UDF) in Excel and shows you some more useful examples. A good starting point for VBA.
• More Custom Functions for Access and Excel
  If you're short of ideas for Custom Functions take a look at these. This tutorial shows you how to build and use RandomNumbers, FindSaturday, EOMonth and RemoveSpaces functions that can be used in both Excel and Access. You will also see examples of If Statements and Case Statements, and some built-in functions that you might not have discovered yet.
• An Improved Custom Random Numbers Function
  Just when you think you've thought of everything, you realise you need something else! With only minor adjustments to the original, this function allows you to specify a degree of precision (number of decimal places).
• Build an Excel Add-In
  The Add-In is an ideal tool for storing and distributing your custom functions in Excel. This tutorial is ideal for beginners, taking you step-by-step through writing a custom function, then creating and installing an Add-In. If you are already comfortable with VBA function writing, you can jump straight to the Add-In section.
• How to Use Your Excel Add-In Functions in VBA
  "I built an Add-In for my custom functions. Why why can't I use them in my workbook code?" Add-Ins are intended primarily to add extra functionality to your workbooks. If you want to be able to use their functions in your VBA code you have to set a reference to the Add-In. This article explains how.
• A Pop-up Calendar for Excel (Excel 97/2000/2002/2003 version)
  Entering dates correctly seems to be a problem for so many people. This tutorial shows how to create a pop-up calendar using the Microsoft ActiveX Calendar Control. Choosing a date from the calendar enters it into the active cell on the worksheet. The tutorial includes automatically assigning a keyboard shortcut, and customizing shortcut menus so that the user can right-click on a cell and ask to insert a date. There's also a ready-made Calendar Add-In to download.

Excel Video Tutorials from my Excel Video Tips Section...

• Excel Spreadsheet Techniques #1
  This is the first part of a two-part tutorial illustrating a range of techniques useful for creating all kinds of spreadsheets in Excel. Starting with basic data entry and formatting this part goes on to build a simple interactive financial model. It includes formulas that use percentages. It uses the Autofill tool to simplify the entry of text and calculations, and illustrates the significance of absolute references in formulas. Finally, a drop-down list is added to create a simple interactive tool.
• Excel Spreadsheet Techniques #2
  The second part of the tutorial enhances the model created in part one with the addition of a chart. It shows how to select the data for the chart and illustrates the importance of choosing the right type of chart. You learn how Excel's tools make it easy to create professional looking charts and customize them to get them looking just the way you want.
• Excel Pivot Tables #1
  The first of a three-part tutorial introducing Pivot Tables in Excel 2007/2010. It demonstrates how to select data and summarize it by using a Pivot Table, how to rearrange and filter the table and how to "drill down" to display the source data underlying any value in the table.
• Excel Pivot Tables #2
  Part two of three tutorials introducing Pivot Tables in Excel 2007/2010. It demonstrates how to format numbers and text in a pivot table, add or remove grand totals and use the Design tools to select a color scheme.
• Excel Pivot Tables #3
  Part three of three tutorials introducing Pivot Tables in Excel 2007/2010. Demonstrates how to group and ungroup fields in the table and add subtotals to the groups, how to rearrange rows, and how to specify how data is summarized.
• Analyzing Business Data with Excel - The SUMIF Function
  This video explains how to useExcel functions (SUMIF, AVERAGEIF, COUNTIF) to analyze business data and includes some useful, timesaving tricks to help you make sense of your data. It demonstrates how to use these functions creatively with the aid of some of Excel's other features such as the Autofill tool (to quickly enter multiple calculations) and the Remove Duplicates and Data Validation tools to add a useful dropdown list.
• Analysing Business Data with Excel - The Subtotal Tool
  A useful way to summarize data is to sort it then add subtotals to each group. Done manually, this can be very time consuming. The data used in this example requires subtotals to be calculated for 13 offices each of which has 8 departments, a total of 112 separate subtotals (one for each department in each office and another for each office as a whole). The Subtotal tool can achieve this with just a couple of mouse clicks and allows you to display the data with its subtotals or just the subtotals themselves. And it is just as easy to remove the subtotals when you are finished. This video shows you how to do it.
• Excel's Business Tools - What-if Analysis
  This video 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

Excel VBA Video Tutorials from my VBA Video Tips Section...

• Build a Pop-up Calendar for Excel - Part 1
  The first of three videos that accompany my popular tutorial A Pop-up Calendar for Excel and is an excellent introduction to working with UserForms in VBA. The entry of dates is always a headache for people who use spreadsheets or databases. How should the date be written and in what format and what was the date of the third Thursday in October last year anyway? The answer is my pop-up calendar. Use a keyboard shortcut or right-click on a cell to ask for the calendar and all you have to do is pick a date. This short introductory video shows you what the calendar will look like and what it can do. [03:36]
• Build a Pop-up Calendar for Excel - Part 2
  In this second video I show you how to create a UserForm in Excel's Visual Basic Editor. I add a command button and the calendar itself and write the VBA code to make it work, as well as a macro to open the calendar in Excel. [26:55]
• Build a Pop-up Calendar for Excel - Part 3
  In the final video I enhance the calendar by writing some code to have it synchronise with an existing date, then add code to the workbook that defines a keyboard shortcut and creates a new item on the cell's right-click menu. Finally I convert the project file into an Excel Add-In for safe and easy distribution. [30:40]

Excel Questions from my Mailbag Section...

• I Don't Have a Copy of the mscal.ocx Calendar Control

• How Can I Make my Excel Calendar Appear Automatically When the User Enters a Cell?

• My Excel Calendar Keeps Making Extra "Insert Date" Entries

• How can I add up just the red numbers on my worksheet?

• I've got US format dates and I can't change them with Format Cells