These examples show Excels features
such as VBA,
Advanced Filter, Dfunctions, Choose,
Index & Match and more!
you will need WinZip to open the Workbooks on this page. If you do not have WinZip
download it here
Some basic and advanced uses of Data Validation.
This is a zipped Excel Workbook that has six working examples using Excels Advanced Filter. It has three that use no formulas and three that do
This is a zipped Excel Workbook that shows how you can use Data Validation and the Dfunctions to extract data from a Table.
This is a zipped Excel Workbook that shows how you can use various formulas to extract information from a Table using LookUp formulas.
Think you need to know VBA to make things happen automatically, think again. Here are some clever uses of Option Buttons and Conditional Formatting.
Way to spice up your spreadsheet using a 3D effect on selected cell and the Office Assistant to guide you.
Use of a UserForm and ListBox to display a list of non empty sheets for printing.
This is a zipped Excel Workbook that shows how you can use VBA housed in the Worksheet_Change Event to automatically add data to a cells Data Validation list.
This is a zipped Excel Workbook that shows how you can Sum every second, third fourth or Nth cell, the right way
This Workbook shows how you can use the Worksheet_Calculate
Event and a Validation
list to have formulas automatically reference different Workbooks. The code has been set so it works in both Excel 97 and 2000. The reason it uses the Worksheet_Calculate
Event and not the Worksheet_Change
is because a Validation list will not fire the Worksheet_Change
Event in Excel 97.
This download contains four Workbooks. They should be all saved to the same folder on your hard drive. Then open "ChangingExternalFormulas
This workbook example shows how you can move a long column of data (10,000 rows in the example) at each page break
and place them across columns ready for printing. The code is also here.
This workbook demonstrates the use of dynamic ranges taken to a new level. It will create a dynamic range within another dynamic range, range or named range. For example you can actually have a dynamic range of all people whos name starts with the letter "H" within a list of names. If you are not familiar with dynamic ranges, click here
Here is how you can use Data Validation in 2 cells and have the List of one depend on the item chosen from the other, no code required! There is also a more advanced versions which makes use of VBA code.
This workbook shows how you can combine VLOOKUP, COUNTIF, Data Validation and Dynamic named ranges to create a very user friendly lookup table.
Uses two 'Type 2' InputBoxes that allow you to select two seperate ranges. The Values from the first range are then match and transfered to the second range.
Demonstrates how you can use a multi columned ListBox and transfer the user selection(s) to another table.
A user friendly way to ensure macros are enabled within your VBA Workbook. This method uses some simple VBA to hide all sheets if macros are disabled.
How to add a UserForm that prompts for a user name and password when the workbook opens.
A very quick and easy way to add a list to a UserForm ListBox that has all duplicates removed and is then sorted.
This workbook demonstrates how to create a Custom menu (with sub-menus) on Excel's Worksheet Menu Bar.
The custom menu is deleted when the Workbook is Deactivated
and re-created when it's Activated
The "Pick from list"
option you get when you right click in a cell or use Alt
is a very handy way to add to a list. Unfortunately it will only include text
, this Workbook contains a possible work-around using Validation
Very simple method to have a chart range expand and contract and have it instantly reflected in the chart. No VBA needed!
A simple but very handy use of Conditional Formatting to show all over due dates.
A very simple method to have a ComboBox (from Forms toolbar) change it's Input Range according to a users choice from some OptionButtons. No VBA needed!
Another very simple method to have a ComboBox (from Forms toolbar) change it's Input Range according to a users choice from another ComboBox. Again no VBA needed!
A very simple macro that swaps the content of two cells with each other. Cells can be contiguous or non-contiguous!
Simple use of a Pivot Table and a Dynamic named range to show the Top x occuring numbers in a list.
Example of how to use the one Procedure
to determine which Control button was clicked and run it's own procedure.
Very simple method of looking up the appropriate tax rate using VLOOKUP.
Here is a simple method anyone can use to let the user know that a Procedure is running. Often a progress bar is used, but this slows things down even more.
Think you need to use VBA for user interaction? If so this will prove you wrong, it's a very simple use of the CheckBox control from the Forms Toolbar combined with Conditional Formatting.
Here is the Microsoft Workbook example of Excels Solver feature.
Excels Chart Workbook.
This Workbook is priceless in more ways than one. It shows Functions, special features, ActiveX controls, VBA code for Databases and API code and more!
If you create Custom Command Bars, you will find this download very handy.
Excel Training ||
Employee Scheduling Software ||
Password Recovery and Access & Word etc||
Excel Recovery and Access & Word etc ||
Financial Software ||
Financial Calculators ||
Construction Estimating Software ||
Real Estate Investment Software ||
Time Management Software ||
Neural Network Software ||
Charting Software ||
Windows & Internet Software ||
Fonts, ActiveX, Labels and DLL's ||
Printing Software ||
Excel to HTML