|Get Excel Training
Are you looking for Microsoft Excel
training for yourself, your colleagues or your staff? If you
like what you see here you can get personal training from me.
Find out more on my
Do you need someone to build you an
application in Excel? Do you need help with your Excel
spreadsheets or financial models? I can help. Find out about my
development and consultancy services on my
Put a Date Picker Calendar on an Excel
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
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
Working Out a Person's Age - An Introduction to
Nested IF Statements
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
"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
- 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
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
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
Formatting to present the results in an easily understandable
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
Excel Questions from my Mailbag Section...