Sample Excel Files

DV0076- Show List of All Matching Items - Select Region name from a data validation drop down list. Worksheet table shows list of employees from the selected region, using INDEX/MATCH formulas.
Format: xlsx Macros: No Size: 59 kb
Download: datavalmatchingitems.zip

DV0075 - Data Validation Combo Dependent Merged -- Double-click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. List in City column is dependent on selection in Region column, using INDIRECT and SUBSTITUTE formula. City column is merged.
Format: xlsm Macros: Yes Size: 78 kb
Download: datavalcombomergedepend.zip

DV0074 -Drop Down Shows Visible Items Only - In employee list, add X in rows where person is on vacation. Filter the list to hide the X rows. Data validation drop down shows available employees only. 
Format: xlsx  Macros: No  Size: 63kb 
Download: datavalvisiblerowsonly.zip

DV0073 -Data Validation Combobox - Select Next Item-- Click a button on the worksheet, and a macro runs, to select the next item in the Combobox list. If the last item is currently selected, the next selection is the first item in the list. 
Format: xlsm  Macros: Yes  Size: 69kb 
Download: datavalcombonextvalue.zip

DV0072 -Data Validation Combobox - Add/Sort -- Click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. If a new item is entered, you will be asked if you want to add it to the existing list. Similar to DV0062, but lists with multiple columns will be sorted correctly. 
Format: xlsm  Macros: Yes  Size: 33kb 
Download: datavalcombo_addsortmulticol.zip

DV0071 - Time Entry With Hour and Minute Lists
Enter production time, then select hour and minutes for defect time. Must not be greater than production time, and drop down lists control the valid times.
Format: xlsx  Macros: No  Size: 18kb 
Download: timeentrylimit.zip
Instructions: Excel Data Validation - Select Hours and Minutes

DV0062 - Data Validation Click Combobox - Add New Items -- Click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. If a new item is entered, you will be asked if you want to add it to the existing list. DataValCombobox_AddSort_Multi.zip 28kb  12-Dec-13 

DV0061 - Assign Players Each Inning -- Select a player from a dropdown list in this Excel template, and that name disappears from the other dropdowns for that inning. Excel 2010/2013: DataValPlayerInnings2013.zip 22kb  08-Dec-13 

DV0060 - Dependent List From Row Items -- Select an employee name from the first drop down, second drop down shows all skills for that employee. ListsDependentRow.zip 32 kb   24-Sep-13 

DV0059 - Add New Items to Validation List -- In a workbook with multiple data validation lists in Excel tables, type a new value in a cell that contains data validation, and confirm that you want to add it to the drop down. It's added to the appropriate source list, and the list is sorted; a macro automates the list updates. DataValListAddSort_Tables.zip 32 kb   24-Sep-13 

DV0058 - Limit Budget Entries with Data Validation -- Limit the total amount that is entered in an Excel budget worksheet, by using data validation to check the total. Create a custom message that appears when the amount goes over budget. You can also see the unbudgeted amount that is still available, and adjust the entries if required. DataVal_BudgetTotal.zip 12 kb  25-Jul-12 

DV0057 - Data Validation Combobox Codes -- Double-click a cell that contains a data validation list, and a combobox appears, showing a list of descriptions. Select a descriptions, and that descriptions numeric code is entered in the cell. Lists are stored in named ranges on a separate sheet. Excel 2007/2010 format; macros must be enabled. DataValComboboxCodes.zip 30 kb  02-May-12 

DV0056 - Dynamic Data Validation -- With this data validation technique from AlexJ, users can see a drop down list with just the top projects, or all projects. A macro cleans up the selection cell, if necessary. AlexJ_DynamicDataVal.zip 16 kb  16-Feb-11 

DV0055 - Dependent Data Validation Click Combobox -- Click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. List in second column is dependent on selection in first column. DataValComboClickDepend.zip 18 kb  25-Feb-10 

DV0054 - Create Dependent Lists With INDEX -- As an alternative to using INDIRECT for creating a dynamic formula to define a range, Roger Govier shows how you can use the non-volatile INDEX function. In this example, just 4 dynamic range names are used. Three are used to create the basic framework of the method. The fourth permits any number of subsidiary lists to be created to act as Dependent dropdown lists from the entry in the previous column, without having to define individual names for each list. Detailed Instructions  RJG_Universal_DD_Dependent.zip 21kb 18-May-09 

DV0053 - Different Drop Downs from One Source -- Instead of using a different source for each data validation list, AlexJ has devised a simple way to use the same source for all the lists. For details see Contextures Blog article Different Excel Drop Downs from One Source. APJ_Universal_DD.zip 25kb  22-Feb-09 
Roger Govier has created an Excel template with another method of using one formula to show different drop downs. RJG_Universal_DD.zip 39kb  22-Feb-09 

DV0052 - Show or Hide User Tips -- Excel template from AlexJ uses data validation to show messages for users on a worksheet. Users can choose from a drop down list to show or hide the messages. For details see Contextures Blog article Show or Hide User Tips in Excel. APJ_ToolTips.zip 9kb  11-Feb-09 

DV0051 - Assign Players Each Inning -- Select a player from a dropdown list in this Excel template, and that name disappears from the other dropdowns for that inning. DataValPlayerInnings.zip 3kb  12-May-08 

DV0050 - Data Validation Lookup -- Select a level from a dropdown list in this Excel template, then enter a minimum and maximum value in adjacent columns. The values are constrained by the limits set in a lookup table on another worksheet. DataValMinMax.zip 3kb 01-Jul-07 

DV0049 - ClipArt Selection -- Select a clipart item from a data validation dropdown list, and that picture appears in the adjacent cell. Uses Worksheet_Calculate event code. Excel template from Bernie Deitrick. ClipArtEvent.zip 30kb   03-Jun-07 

DV0048 - Dynamic Dependent Dropdowns from Unsorted List-- Select Yes or No from the first dropdown list in this Excel template, and the dependent cell's validation list shows only items for that selection. Dependent lists are created from an unsorted master list, where items are marked as Yes or No. DataValDynamicUnsorted.zip 8kb 23-Mar-07

DV0047 - Hide Previously Used Items in Dependent Lists -- Limit the choices in a Dependent Data Validation list, hiding items that have been previously selected in this Excel template. DataValHiddenDepend.zip 4kb 10-Feb-07 

DV0046 - Add Headings for Navigation -- Add letter headings in a long list of data validation items, to make it easier for users to navigate the list. Excel template from Roger Govier. DataVal_Headings.zip 15kb  13-Dec-06  

DV0045 - Hide Matching Columns -- Select a date from a dropdown list in this Excel template, and columns with matching date in the heading are marked. Click a button to hide marked or unmarked columns. HideMarkedCols.zip 10kb  25-Nov-06  Updated 05-Dec-06 

DV0044 - Dependent Cell List Multiple Offset-- Select a name from the first dropdown list, and the dependent cell's validation list shows only products for that name. Select a product, and the third column's validation list shows only the items for that name and product. Based on a lookup table sorted by name and product. DataValMgrProdClass.zip 6kb  29-Sep-06  

DV0043 - Data Validation Combobox With Entry Check -- Double-click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. When the combobox loses focus, the entry is validated. DataValComboCheck.zip 15 kb   29-Jun-06   Updated 11-Oct-06  

DV0042 - Limit Data Validation Selection -- If a selection has been made in a dependent cell in this Excel template, the data validation list is limited to the current selection. DataValListLimit.zip 3 kb   26-May-06 

DV0041 - Reset Data Validation Cells -- Macro to clear data valiation cells, and if cell has a data validation list, select the first item from the list in this Excel template. DataValReset.zip 10 kb   21-May-06 

DV0040 - Assign Qualified Employees to Single Task Per Day-- Lists of employees qualified for each task are created. Names are removed from data validation dropdown list once they've been assigned to any daily task in this Excel template. DataValDealers.zip 10 kb    15-Apr-06

DV0039 - Seating Plan -- Use data validation and shapes linked to cells, to plan table seating arrangement. SeatPlan.zip 6kb   17-Mar-06  Instructions NOTE: Newer version here

DV0038 - Flexible Item List -- Use formulas to automatically add new unique items to a data validation dropdown list. Excel template from Ron Coderre. DataValFlexList.zip 7kb   18-Feb-06 

DV0037 - Dependent Lists Country City-- Select a country from the first dropdown list in this Excel template, and the dependent cell's validation list shows only the cities in that country. To prevent invalid data, after a city is selected, the country dropdown shows only that city's country. DataValCountryCity.zip 6kb 10-Oct-05 

DV0036 - Dependent Cell List Offset-- Select a region from the first dropdown list, and the dependent cell's validation list shows only the customers in that region. Based on a lookup table sorted by Region. DataValRegionCust_2003.zip 7kb  31-Mar-05  Detailed Instructions 10-Oct-05 

DV0035 - Dynamic Chart -- Select Start and End dates from Data Validation lists; chart updates automatically to display sales for selected time period. ChartDateAuto.zip 5kb  Detailed Instructions 17-Jun-05 

DV0034 - Input Message in Textbox -- Select a cell that contains a data validation input title or message, and a textbox appears. Control the size, position and formatting of the message.  DataValInputMsgAlt.zip 11 kb 25-Apr-05   Detailed Instructions 05-Jun-05  Works on protected sheet. updated 08-May-06

DV0033 - Dependent ShipTo Location-- Select a customer, then select a shipping location from the dependent dropdown. VLookup formulas return the shipping address for the selected location. DataValShipTo.zip 4 kb 19-Apr-05 

DV0032 - Dependent Data Validation Combobox -- Double-click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. List in second column is dependent on selection in first column. DataVal_Combo_Depend.zip 12 kb  12-Apr-05 

DV0031 - Create Chart from Current Data -- Select a value from a data validation dropdown, and an event procedure captures the current data, and creates a scatter chart. DataValChartCreate.zip 12kb  07-Apr-05

DV0030 - Dependent Cell List Lookup-- Select an item from the first dropdown list, and the dependent cell's validation list shows only the accessories available for that item. Based on a lookup table where applicable accessories are marked. DataValLookupList.zip 7kb 

DV0029 - Dependent Cell Examples-- Two samples of code altering dependent cells. Source lists on separate sheet.
1) Selection from two lists must be different, or cell is cleared
2) Select from either list, and adjacent cell is filled in. Based on code from Jason Morin. DataValCode02.zip 12kb  updated 24-Apr-06

DV0028 - Data Validation Combobox Named Lists -- Double-click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. Lists are stored in named ranges on a separate sheet. Macros must be enabled. DataValComboboxSheet.zip 13 kb updated 15-Jan-07    View detailed Instructions

DV0027 - Update Dependent Cell -- Selection from first list runs event code that selects the first item from a dependent list in an adjacent cell. Based on code from Jason Morin. UpdateDependent.zip 9kb  20-Feb-05   updated 21-Dec-11

DV0026 - Variable Validation -- Selection from the first list runs event code that changes the validation in adjacent cell. DataValYesNoClear.zip 11kb 16-Jan-05

DV0025 - Data Validation Spinner -- Use a spin button control to select the next or previous item from a data validation list. One example uses a macro, the other example uses a formula DataValSpinner.zip 30kb

DV0024 - Cooking Time Planner -- Plan your meal preparation, using Data Validation to enter food items. Formulas calculate the cooking schedule, based on the target meal time. DinnerPlanner.zip 13kb  updated 08-Oct-06  

DV0023 - Limit Selection List -- If you have a lengthy list, it's hard to find an item in the data validation dropdown. Use an Advanced Filter to create a short list, and select from that. Event code creates the short list. DataValSelectName.zip 13kb

DV0022 - Update Validation Selections -- If you change an item in a data validation source list, the worksheet may show previously selected items. Event code can update the worksheet when you update the source list.
Multiple lists: datavalupdatemulti.zip 68kb   22-May-18
One list: DataValUpdate.zip 10kb  Updated 11-Sep-07

DV0021 - Update Multiple Validation Lists -- In a workbook with multiple data validation lists, type a new value in a cell that contains data validation, and it's automatically added to the appropriate source list, and the list is sorted; a macro automates the list updates. DataValListAddSort_Multi.zip 12 kb   Detailed Instructions  Updated 10-Apr-10 

DV0020 - Data Validation Combobox-- Double-click on a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, and autocomplete can be enabled. Macros must be enabled. DataValCombobox.zip 16 kb  Detailed Instructions updated 15-Jan-07

DV0019 - Default to First Value-- Data validation limits values that can be entered; event macro enters the default value for the selected option. DataValYesNoRates.zip 8 kb

DV0018 - Happy Face Gauge-- Data validation limits values that can be entered; event macro adjusts the curve. Separate worksheets with code for Excel 2003 and Excel 2007. Also see sample file UF0050 Download file: HappyFace.zip 8 kb

DV0017 - Select Multiple Items from Dropdown List-- Select multiple items from a dropdown list; an event macro stores selections in adjacent cell, or in same cell. DataValMultiSelect.zip 18kb  updated 16-Jan-14  Instructions

DV0016 - Assign Employees to Single Task Per Day-- Names are removed from data validation dropdown list once they've been assigned to a daily task. DataValDailyList.zip 6 kb  

DV0015 - Create Dependent List for Selected Column -- The first dropdown list is based on column headings. The second list contains unique items from the selected column, sorted in descending order. DataValListSort.zip 11 kb

DV0014 - Combine Multiple Lists into One -- A data validation list from a worksheet must come from contiguous cells in a single column or row. This example uses formulas to combine three dynamic lists into one master list. DataValMultiLists.zip 3 kb

DV0013 - Cross Dependent Validation Lists -- Selection from the first list controls the items available in the dependent cells. Selections in the dependent lists control the dropdown items in the first list. DataValYesNoDepend.zip 3 kb

DV0012 - Update Validation List -- type a new value in a cell that contains data validation, and it's automatically added to the source list, and the list is sorted; a macro automates the list updates. DataValListAddSort.zip 11 kb

DV0011 - Dynamic Validation List -- shows customers with start and end dates that include selected date, macro automates the list creation. DataValDateRange.zip 12 kb

DV0010 - Model Pricing Scenario -- uses data validation to create dropdown lists, Scenarios to store variables, macro automates scenario display. ScenarioParts.zip 13 kb

DV0009 - Purchase Order -- uses data validation to create dropdown lists, VLookups to return values from named ranges on different sheets. VLookupNamedRange.zip 7 kb

DV0008 - Data Validation Checklist -- uses data validation to create dropdown lists, with only checked items appearing in the list. DataValDynaChk.zip 4 kb 

DV0007 - Chart Selected Date Range -- uses data validation to create dropdown lists, and dynamic named ranges to plot the selected date range.   ChartDateRange.zip 12 kb 03-May-09
NOTE: See version in Charts section: CH0013

DV0006 - Machine Capacity -- uses data validation to create dropdown lists, and the VLookup and Match functions to extract information from a table. MachineCapacity.zip 3 kb 

DV0005 - Data Validation "Columns" -- Data Validation dropdown displays product name and ID; an event procedure changes the selection to product name. (XL2000 +) DataValNameID.zip 9 kb; (XL97) DataValNameID97.zip 11 kb

DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb DataValCode2007.zip 22 kb

DV0003 - Order Form -- Select items from the dependent Data Validation lists; a VLookup formula extracts the unit price. DataValOrder.zip 11 kb

DV0002 - Invoice for Selected Number -- uses Data Validation and VLookup to extract details for an invoice DataValVlookup.zip 8 kb

DV0001 - Assign Employees -- ensure that each employee is only assigned once per day
AssignEmp.zip 12 kb

FL0025 - Filter Excel 2010 Table With Slicers
With this technique from AlexJ, you can use Slicers with a formatted table in Excel 2010.

Format: xlsm  Macros: Yes  Size: 28kb 
Download: alexj_TableSlicers.zip

FL0024 - Shorten Data Validation List -- Shorten a Data Validation list by using Advanced Filter to extract a list of names that start with selected letter; file contains macros to automate list updates and filter. See details. AdvFilterSelectOrders.zip 07-May-10

FL0023 - Movies Database -- uses Data Validation and Advanced Filter to extract a list of movies for selected category or actor; file contains macros to automate list updates and filter. Movies database details. MoviesDatabase.zip 24 kb 05-Sep-09 updated 28-Jan-10

FL0022 - FilterClick Colour -- Click on a cell in the filter range to filter that column by the selected value. Click on a column heading to remove that column's filter. Heading cells on filtered columns are coloured. Turn FilterClick feature off to perform other activities, such as sorting. Updated to work on Excel 2003 List feature and Excel 2007 tables feature.  FilterClickColour.zip 15kb  24-Oct-08     FilterClickColour_2007.zip 33kb   FilterClickColour_2003.zip 29kb  28-Nov-08 

FL0021 - FilterClick -- Click on a cell in the filter range to filter that column by the selected value. Click on a column heading to remove that column's filter. Turn FilterClick feature off to perform other activities, such as sorting. FilterClick.zip 14kb   19-Oct-08  

FL0020 - Fast Filter -- Type criteria in the row above an AutoFilter, instead of selecting from the dropdown lists. The cells with criteria change colour, so you can easily see which columns have a filter applied, and see what the criteria are. Event code runs when a change is made on the worksheet. Excel template from Roger Govier. Fastfilter.zip 32kb   10-Mar-08  

FL0019 - Filter Latest Date for Customer -- Select a customer, and a macro runs an advanced filter to extract the latest sale record for that customer. AdvFilterLatestDate.zip 12kb   

FL0018 - Highlight Filtered Headings in List -- In an Excel 2003 List, User Defined Function, and conditional formatting, highlight column headings where filters are applied. FilterHighlightList.zip 15kb   

FL0017 - Filter All Columns -- Enter a number on the worksheet, and the list filters to show only the records that contain that number in one of the five columns. AdvFilterStateNum.zip 11 kb   01-Dec-06

FL0016 - Move Items to Order Form -- Select items from a filtered list, and event code moves the item to an order form. ProductsListOrder.zip 17 kb  04-Apr-06

FL0015 - Filtered Chart Title -- Chart data and chart title change when an AutoFilter is applied to the source data. FilterChartTitle.zip 13 kb  04-Oct-05

FL0014 - Colour Filter Headings -- Event code colours the filtered column headings, when an AutoFilter is applied. FilterColour.zip 9 kb  15-Apr-05 updated 07-Dec-09

FL0013 - Create New Sheets from Filtered List -- uses Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.zip 14 kb   24-Sep-04

FL0012 - Map Based Filter -- uses code to extract regional data for the selected map shape. FilterMap.zip 11 kb

FL0011 - Extract Random Records -- uses code to extract a random set of records from a database. Based on code from Ross Connell. ExtractRandom.zip 18 kb

FL0010 - Summarize Budget Details -- uses Advanced Filter to summarize budget details for selected month, or full year; macro automates the filter. AdvFilterBudget.zip 11 kb

FL0009 - Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- creates a list of unique items, creates a sheet for each item, then replaces old data with current. AdvFilterCity.zip 15 kb 

FL0008- Filter Rows for Text String -- uses Worksheet Change event, and Advanced Filter, to display rows which contain specifiec text string and/or region. AdvFilterSearchWord.zip 9kb  

FL0007 - Highlight AutoFilter Headings -- User Defined Function, and conditional formatting, highlight column headings where filters are applied. FilterHighlight.zip 15kb 

FL0006 - Filter for Date Range -- uses Data Validation and Advanced Filter to extract a list of orders in selected date range; file contains a macro which automates the filter (XL2000 & XL2002 only) FilterAdvDateRange.zip 14kb 

FL0005 - Phone List for Selected Name -- uses Data Validation and an Advanced Filter to extract a list of customers with the selected Last Name; file contains a macro which automates the filter (XL2000 and XL2002 only) PhoneList.zip 13 kb 

FL0004 - Create New Sheets from List -- uses an Advanced Filter to create separate sheet of orders for each sales rep; macro automates the filter. AdvFilterRep.zip 12 kb

FL0003 - Product List with Chart -- uses Advanced Filter to extract a list of products by Category; dynamic chart displays the results; file contains a macro which automates the filter ProductsListChart.zip 14 kb

FL0002 - Product List by Week Number -- uses Advanced Filter to extract a list of products for the selected week number; file contains a macro which automates the filter ProductsListWeekNum.zip 11 kb

FL0001 - Product List by Category -- uses Data Validation and Advanced Filter to extract a list of products for selected category; file contains a macro to automate the filter. The zipped file is in xlsm format --ProductsList.zip 71 kb

UF0027 - Hide Sheet When Cell Changes -- Select a country name from a drop down list, to hide or show a worksheet. hidesheetselcountry.zip 23 kb  05-Jun-14

UF0026 - Prevent Invalid Entries in UserForm -- Change a combo box setting, to prevent invalid entries in the UserForm. PartLocDBComboVal.zip 22 kb  13-Jun-11 Instructions

UF0025 - Change Marked Text to Superscript -- There's no Ribbon command or QAT command for Superscript. Instead, mark the text with red font, then run a macro to change red text to Superscript. superscriptmacro.zip   18 kb  21-Jan-14

UF0024 - Click Check Box to Run Macro -- Click a check box, and the current date is entered on the worksheet. Clear the check box, and the date cell is also cleared. checkboxtodolist.zip   26 kb  11-Jan-14

UF0023 - Send Email with PDF Attachment -- Send emails to each address on a worksheet, with a PDF attachment created from report worksheet. emailtestfile.zip   38 kb  06-Nov-13

UF0022 - Worksheet Data Entry Form Multiple Areas -- Enter data on a worksheet, in multiple data entry areas. Add new records, view and update existing records. FormSheetEditMulti.zip   32 kb  04-May-13

UF0021 - Scroll Items Up and Down in List -- Sample code from Jim Cone. Select a list on the worksheet, and click arrows to scroll the list items up or down. custom_scroll_ area_jc.zip 24 kb  17-Apr-13

UF0020 - Parts Database With Dependent Combo Boxes -- UserForm with comboboxes for data entry, with database on a hidden worksheet. Part combo box depends on selection made in Part Category combo box. Pivot table summarizes the inventory PartLocDBComboRibbonDepend.zip 48 kb  06-Mar-13 Instructions

UF0019 - Formula Info List -- Code creates a list of formulas on each worksheet, by inserting a new sheet for each list. Remove formula list sheets by running the cleanup macro. FormulaInfo.zip 36kb  26-Sep-12 

UF0018 - Excel VBA Code Documenter -- Sample code from Professor Lee Townsend creates a list of VBA procedures in a workbook, and shows which procedures call others. Also lists worksheet shapes and connected macros. ExcelVBACodeDoc.zip 88kb  28-Jun-12 

UF0017 - Parts Database with Updateable Comboboxes -- UserForm with comboboxes for data entry, with database on a hidden worksheet. Add new parts to worksheet list, while entering data in the UserForm. Pivot table summarizes the inventory. Excel 2010/2007: PartLocDBComboAdd.zip 43 kb  07-May-12  Excel 2003: PartLocDBComboAdd2003.zip 33 kb  08-May-12   UserForm instructions

UF0016 - Excel Calorie Counter With Recipe Calculator-- Keep track of daily calories, protein, and other nutrients, and store the data on a separate sheet. Calculate nutrients per serving in your favourite recipes, and add those to the Food List. See the summary in a pivot table. ExcelCalorieCounter2007Recipe.zip 128kb  12-Mar-12 
Instruction Page: Excel Calorie Counter

UF0015 - Excel Calorie Counter -- Keep track of daily calories, and store the data on a separate sheet. See the summary in a pivot table. ExcelCalorieCounter2007.zip 60kb  06-Jan-10  Instruction Page: Excel Calorie Counter

UF0014 - MultiPage Excel UserForm -- Add Help buttons to a worksheet, and open a UserForm to a specific page, to show relevant help. UserFormMultiPageHelp.zip 36kb  06-Feb-12 

UF0013 - Show Personalized Message -- Show an Excel message box that includes the network username. ExcelMessageUserName.zip 36kb  16-Dec-11 

UF0012 - Add Sheet When File Opens -- Automatically add a new worksheet with the month name, when the Excel file opens at the start of the month. ExcelVBA_AddMonthSheet.zip 31kb  10-Apr-11 

UF0011 - Excel Advent Calendar -- Click a numbered square and a macro reveals a Christmas picture each day. For details see Excel Advent Calendar article on Contextures Blog. AdventCalendar2007.zip 355kb  02-Dec-09 

UF0010 - Create a Table of Contents with Hyperlinks -- Code creates a Table of Contents sheet. The first instance of each heading is added to the TOC sheet, with a hyperlink to the cell where that heading is located. For details see Contextures Blog article Create a Table of Contents in Excel for a Price List. HyperlinkCode.zip 19kb  27-May-09 

UF0009 - Hide Outline Rows With Keyboard Shortcut -- Sample code from Sam uses keyboard shortcut to run macros that show or hide outline rows on a worksheet.
Download:   outlinesample_sam.zip 21kb  04-Feb-09 
Instructions: From comment on Contextures Blog Hide Excel Rows With Outlining.

UF0008 - Hide Rows With Outlining -- Excel template from AlexJ uses outlining and VBA to hide rows on a worksheet. Users can click buttons to show or hide specific sections. Admin toolbar assists with worksheet setup.
Download:   apj_panecontrol_v2.zip 21kb  04-Feb-09 
Instructions: Contextures Blog Hide Excel Rows With Outlining.

UF0007 - Create a Table of Contents -- File contains two methods for navigating in a large workbook, from reader comments on the Contextures Blog article Create a Table of Contents in Excel. Run code to create a list of worksheet names, with hyperlinks to those sheets (sample from Andrew). Or use the keyboard shortcut Ctrl+q to create a popup menu or window with a list of sheet name. (sample from Sam). Create_TOC_Code.zip 16kb  18-Dec-08 

UF0006 - Monthly Workbook Creator -- Click a button, and the code in this file creates a workbook for each month of the year, with a sheet for each day. Excel template from Roger Govier. CreateMthlyWkbks.zip 26kb  08-Dec-08 

UF0005 - Music Playlist Creator -- Click a button, and the code in this file creates a playlist of music from a selected folder, and places it on your desktop for easy access. Excel template from Dave Peterson. M3U_Creator.zip 17kb  28-Dec-06

UF0004 - Worksheet Navigator Toolbar -- This add-in creates a floating toolbar, that you can open in any workbook, and creates a list of sheets in that workbook. View Instructions  NavToolbar.zip 11 kb  21-Dec-05

UF0003 - Parts Database with Comboboxes -- UserForm with comboboxes for data entry, with database on a hidden worksheet. Pivot table summarizes the inventory PartLocDBCombo.zip 21 kb  06-Mar-05   View instructions 23-Jan-06

UF0002 - Parts Database -- simple example of creating a UserForm for data entry, with the database on a hidden worksheet. View instructions. PartLocDB.zip 14 kb   

UF0001 - Assign TeeOff Times -- uses an Advanced Filter to schedule selected players in specific time slot; file contains a macro which automates the filter GolfTeeOff.zip 11 kb
This version has a UserForm in which to enter data: GolfTeeOffForm.zip 16 kb updated 19-Dec-2012
Also available -- Golf Score Template

CF0005 - Highlight Employee Hire Date Anniversaries -- Set a date range and highlight upcoming anniversary dates for employees, based on hire date. EmpAnnivHighlight.zip 11kb    07-Jan-14

CF0004 - Conditional Formatting in Filtered List -- A coloured border separates dates in a list, and the conditional formatting formula works even if some rows are hidden. CondFormatDateFiltered.zip 12kb    17-Jul-12

CF0003 - Conditional Formatting for Currency Symbol -- With the conditional formatting options in Excel 2010, you can change the number format, to show a specific currency for the country that's selected. CondFormatNumbers.zip 20kb    23-Jun-10

CF0002 - Highlight Amounts Over Limit -- A formula totals hours worked. Conditional formatting highlights hours over regulated limit. Pivot table totals weekly hours. DriverLimits.zip 7 kb    13-Jan-05

CF0001 - Highlight Column Headings -- To guide users, highlight columns headings when an item is selected from a data validation dropdown list. Cond_Format_Headings.zip 9kb 

ED0003 - RSS Feeds in Excel -- Use XML Maps to show RSS feeds in Excel. This sample file has feeds for Contextures website and Contextures Blog. rss_contextures.zip 23kb  Updated 25-Mar-13 

ED0002 - PivotPlay PLUS Add-in -- Facilitates changes to the connection strings and queries that extract the external source data that pivot tables use. Created by Ron Coderre. PivotPlayPLUS.zip 88kb  Updated 15-Mar-08    Detailed Instructions

ED0001 - Query Master -- Makes it easy for users who are knowledgable in SQL (but not VBA) to create a catalog of up to 10 queries that can be run on demand. Excel template from Ron Coderre. QueryMaster.zip 61kb  10-Jun-06   Updated 07-Aug-06   

CH0010 - Show High and Low in Clustered Stacked Chart -- Compare high and low scores for wins and losses. Calculate differences between high and low, to create data for chart. winlosschart.zip 17kb 18-Oct-14

CH0009 - How to Make a Pie Chart -- Set up data and create a simple pie chart that is easy to read. piechartsetup.zip 23kb 18-Nov-13

CH0008 - Show Target Range on Line Chart -- Show sales quantity in a line chart, with target range shown in the background. chartwithtargetrange.zip 14kb 30-Oct-13

CH0007 - Show Sparklines for Hidden Data -- Macro changes all sparklines on the active sheet, so they will show data, even if rows and columns are hidden. Excel 2010 or later. SparklinesHide.zip 26kb 18-Nov-12  

CH0006 - Word Usage Chart -- Select words from drop down list and chart shows how frequently they were used in speeches, by each political party. WordUsageChart.zip 15kb 09-Nov-12  

CH0005 - Dot Plot Stock Chart -- In cell charts show position of last stock price, compared to Low and High prices. The file is in Excel 2010/2007 format, with no macros. DotPlotStockChart.zip 19kb 16-Sep-12 

CH0004 - Gantt Chart -- Simple task list and Gantt chart, uses the WORKDAY formula to calculate days. The file is in Excel 2007 format, with no macros. ExcelGanttChart.zip 15kb 11-May-11  

CH0003 - Insert Pictures from Folder -- ShowFilePicsDemo demonstrates how to insert picture files (bmp, gif, jpg, etc) directly from a network or web folder into an Excel sheet by selecting an item from a cell data validation drop-down list. Created by Ron Coderre. ShowFilePicsDemo.zip 27kb 20-Sep-07  

CH0002 - Show or Hide Chart -- Using named ranges and a linked picture, show or hide a chart based on the selection from a drop down list. Instructions and Video.
There are 3 versions of the sample file:
1) Excel 2003 - ChartRangeShow.zip 6 kb 21-Jan-07 
2) Excel 2010 (2 item drop down) -  ChartRangeShow2010.zip 21kb 09-Apr-12
2) Excel 2010 (multi item drop down) -  ChartRangeMulti.zip 26kb 27-Jul-15

CH0001 - Pareto Plotter -- Enter your categories and their values, then click a button. The program adjusts the chart columns in descending order and plots the cumulative total line (adjusting sizes so the line meets the upper right corner of the first column and the upper right corner of the chart area. Excel template from Ron Coderre. ParetoPlotter.zip 23 kb 12-Aug-06   

PT0036 - Show Text in Pivot Table Values Area -- Use conditional formatting and custom number formats to show text instead of numbers, in pivot table value area.
a. pivottablevaluestext.zip - no macros 24 kb   13-Apr-14  
b. pivottablevaluestextmacro.zip - macro by Prof. Lee Townsend 29-Jan-2019

PT0035 - Pivot Table Shows Customers With No Purchases -- Change pivot table layout or settings, to focus on customers who have not bought specific products pivotcustomerproducts.zip 33 kb   12-Jan-14  

PT0034 - Pivot Table With Fake Hyperlinks -- Hyperlinks can't be added to a pivot table, so this sample uses VBA and formatting to create fake hyperlinks pivothyperlinkvba.zip 21 kb   31-Oct-13  

PT0033 - Pivot Table or Excel Table from Multiple Files -- Select two or more files which have lists in an identical structure, and the code in this workbook will automatically create a pivot table or Excel table from all the data. Based on a template from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz. Details in blog post Create a Pivot Table from Multiple Files. KL_Files_Table.zip 62 kb   12-Sep-13  

PT0032 - Change Pivot Table Date Range With Scroll Bar -- Click the scroll bar to change the ending date for a pivot table report. Set the number of months to be included. This technique from AlexJ uses one line of programming, to refresh the pivot table. alexj_MonthScrollBar.zip 41 kb 27-Feb-13  

PT0031 - Change All Pivot Charts With One Filter -- Change the filter at the top of the sheet, and all the connected pivot charts change. This technique from AlexJ uses Excel 2010 slicers, and no programming. AlexJ_MasterReportFilter_v01.zip 93 kb 12-Dec-12  

PT0030 - Pivot Table Filter Markers -- In Excel 2003, there are no markers on a pivot table, to show which fields have been filtered. In this sample file from AlexJ, a symbol appears above those fields, to help you identify them. For details see the Contextures Blog article Add Filter Markers in Excel Pivot Table. AlexJ_PivotFilter.zip 23 kb  19-Nov-10 

PT0029 - Change Pivot Table Fields on Specific Sheets -- Change any page field in a pivot table, and the same selections are made in all other pivot tables that contain that page field. Specify which worksheets to change, and which pivot tables and pivot fields to ignore. Uses Slicers, if version is Excel 2010 or later. Sample code from Jeff Weir. PivotMultiPagesChange_JW.zip 45 kb 27-Aug-12   Updated 21-Jun-13

PT0028 - Change Specific Page Fields with Multiple Selection Settings -- Create a list of page fields that should be automatically changed. Change any of the specified page fields in a pivot table, and the same selections are made in all other pivot tables that contain that page field. Also changes the "Multiple Item Selection" settings to match changed page fields. PivotMultiPagesChangeSet2010.zip 47 kb 15-Jul-12 

PT0027 - Change All Page Fields - All Sheets or Active Sheet -- This sample file has 3 variations on the "Change All Page Fields" code, which also changes the "Multiple Item Selection" settings to match changed page fields. For Excel 2007 and Excel 2010 only.
1) Change any page field in a pivot table, and all matching page fields, on all sheets, are changed.
2) Change any page field in a pivot table, and all matching page fields, on the active sheet only, are changed.
3) Change a specific page field in a pivot table, and that page field, on the active sheet only, is changed. PivotMultiPagesChangeAllVar.zip 37 kb 11-Apr-12 

4) Change specific page field in any pivot table, and that page field is changed in all pivot tables in the workbook. PivotChangeAllSpecField.zip 16 kb 27-Aug-12 

PT0026 - Pivot Table Slicer Detail -- With this pivot table tip from AlexJ, you can see the difference when drilling to details in a pivot tables that has been filtered with an Excel Slicer. If the filtered field is not in the pivot table layout, results might not be what you expect. AlexJ_DoubleClick_Slicer.zip 19 kb  21-Mar-12 

PT0025 - Change All Page Fields with Multiple Selection Settings -- Change any page field in a pivot table, and the same selections are made in all other pivot tables that contain the same page fields. Also changes the "Multiple Item Selection" settings to match changed page fields. PivotMultiPagesChangeAll2010.zip 40 kb 28-Dec-11 

PT0024 - Pivot Table from Multiple Sheets - ADO version -- Excel template from Excel MVP Kirill Lapin (KL). Uses code to automatically create a pivot table from multiple sheets in a workbook. Similar to sample file PT0023, but based on ADO, and is less prone to error in different versions of Excel. No manual refresh for pivot table. VBA_PT_NormalMultipleSheets EN 06.zip 21 kb   28-Dec-11 
Plug and Play version: VBA_PT_NormalMultipleSheets EN 07.zip 28 kb   28-Dec-11 

PT0023A - Student Budget Workbook -- Track and plan spending in the student budget from Bob Ryan, of Simply Learning Excel. Review cash and credit card spending in pivot tables that show monthly totals. Actual_Budget_Workbook_Basic.zip 16 kb   24-Aug-09  

PT0023 - Pivot Table from Multiple Sheets -- Excel template from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz. Details in blog post Create a Pivot Table from Multiple Sheets. Uses code to automatically create a pivot table from multiple sheets in a workbook. VBA.MULT_CONS_PIVOT_EN.zip 16 kb   24-Aug-09  

PT0022 - Filter Pivot Table for Date Range -- -- Enter start and end dates on the worksheet, and update the pivot table in this Excel template. Dates from that range are selected in the Report Filter Date field PivotCodeDateFilter2003.zip 20 kb   28-Jan-09   . PivotCodeDateFilter2007.zip 27 kb   28-Jan-09  

PT0021 - Change All Page Fields -- Change any page field in the main pivot table, and the same selections are made in all pivot tables that contain the same page fields. Excel 2002/03- PivotMultiPagesChangeAll.zip 17 kb 04-May-08   Excel 2007- PivotMultiPagesChangeAll2007.zip 17 kb 31-Jul-09 

PT0020 - PivotPlay PLUS Add-in -- Facilitates changes to the connection strings and queries that extract the external source data that pivot tables use. Created by Ron Coderre. PivotPlayPLUS.zip 88kb  Updated 15-Mar-08    Detailed Instructions

PT0019 - Task List Summary -- Enter standard tasks in a data entry form, and copy to a task list. Summarize by date in a pivot table, to track all on-going projects in this Excel template. TasksPivot.zip 15 kb   02-Mar-08  

PT0018 - Credit Card Transaction Tracker -- Copy downloaded csv data into this workbook, and view summary reports by store and expense category. CreditTrans.zip 14 kb   02-Jan-08  

PT0017 - Create Calculated Items and Fields -- A calculated field cannot check a row field's text, e.g. IF(Type="Yes",Qty*1,Qty*2). Create calculated items, and multiple pivot tables, to simulate conditional formulas. PivotItemCalc.zip 14 kb   03-Aug-07  

PT0016 - Change Page Fields With Cell Dropdown -- Select an item from a data validation dropdown, and all pivot tables in the workbook show that selection in the page field. Excel 2002 and later versions. PivotMultiPagesCellChange.zip 20 kb   15-Jun-07  

PT0015 - Change Multiple Different Page Fields -- Change either page field in the main pivot table, and the same selection is made in other pivot tables page fields (if available), where some field names are different. Excel 2002 and later versions. PivotMultiPagesDiffChange.zip 20 kb

PT0014 - Filter from Worksheet Date Range -- Enter start and end dates on the worksheet, and update the pivot table, to show matching items. PivotDateRange.zip 17kb   10-Mar-07

PT0013 - Filter From Worksheet Selection -- Select an item from a dropdown list on the worksheet, and event code refreshes the pivot table, to show matching items. PivotSelectDept.zip 9 kb   23-Feb-07

PT0012 - Hide Details to Show Last Date -- An array formula in the source data marks the latest record per customer. In the pivot table, hide details to see only the latest data. PivotLastShip.zip 9 kb   09-Sep-06

PT0011 - Remove Duplicate Pivot Caches -- Multiple pivot tables in a workbook may be based on the same data source, but use different pivot caches. A macro creates a list of pivot caches, checks for duplicate data sources, and eliminates duplicate caches. PivotCacheFix.zip 17 kb 02-Sep-06 Pivot Cache Info page

PT0010 - Print Top Items -- A worksheet contains two pivot tables, based on the same pivot cache. Display Top 10 items in PivotTable1. A macro in this Excel template prints a copy of PivotTable2 for each Top 10 item. PivotPrintTopItems.zip 16 kb   24-Feb-06

PT0009 - Survey Pivot Charts -- Select a question from the dropdown list, and view survey results for that question. PivotSurveyDepts.zip 16 kb   04-Jan-06; updated 27-Jul-13 

PT0008 - Change Multiple Page Fields -- Change either page field in main pivot table, and same selection is made in related pivot tables page fields. Excel 2002 and later versions. PivotMultiPagesChange.zip 17 kb   21-Oct-05  
Excel 2000 and older versions PivotMultiPagesChange2K.zip 15 kb   22-Oct-05

PT0007 - Multiple Consolidation Ranges -- Create a normal pivot table from multiple ranges, by using MS Query to join the data. One Excel template contains the pivot table, the other contains the source data tables. Contains macro to update connection. Zipped file contains 2 workbooks. PivotMultConsol.zip 18 kb 03-Oct-05 

PT0006 - Pivot Page Spinner -- Use a spinner from the Control tool box to select the next item in the page field. PivotPageSpinner.zip 17 kb  Updated to skip hidden items and to show (All). 18-Jul-05 

PT0005 - Change Pivot Source Data -- Modify captions in a pivot table, and the matching data in the pivot table source is changed.  PivotUpdateItemCode.zip 19 kb 24-May-05 

PT0004 - Hide Page Field Items -- Mark items on a worksheet list, and hide those items in the page field. Change page field selection in main pivot table, and same selection is made in related pivot tables. PivotMultiChangeMarked.zip 20 kb   15-Apr-05

PT0003 - Change Page Field -- Change page field selection in main pivot table, and same selection is made in related pivot tables in this Excel template. PivotMultiChange.zip 17 kb   08-Apr-05

PT0002 - Pivot 97/2000 Show Marked Items -- Mark items on a worksheet list, and display those items in the pivot table in this Excel template. Pivot2000ShowMarked.zip 13 kb   02-Feb-05

PT0001 - Employee Time Tracking -- record hours worked; Pivot Table report and chart Time_Chart.zip 8 kb

FN0064 - Emoji Chart and List -- Enter a hex code and see the related emoji, or select an emoji and see its code and description. Thanks to Ken Puls from excelguru.ca
Format: xlsx  Macros: No 
Size: 161kb  Download: emojis_kenpuls.zip
Tutorial: Notes in file

FN0063 - Compare 2 Lists -- Compare two lists to find new items, using the COUNTIF function.
Format: xlsx  Macros: No 
Size: 59kb  Download: comparetwolists.zip
Tutorial: Notes in file

FN0062 - Weekly Planner Template -- Enter a start date, and formulas show selected week dates in planner sheet. Choose line spacing, adjust section headings, then print the weekly planner sheet.
Format: xlsx  Macros: No 
Size: 63kb  Download: excelweeklyplannertemplate.zip
Tutorial: Notes in file

FN0061 - Show Text and Buttons in Selected Language -- Select English or French from drop down list, and workbook text, buttons, and messages show that language. Numbered notes link to lookup table.
Format: xlsm  Macros: Yes 
Size: 75kb  Download: languageswitchbuttons.zip
Tutorial: Notes in file

FN0060 - Create Custom Hash Errors in Excel
Use the IF function or conditional formatting with a custom number format, to show your own custom hash errors in Excel. If amount is over budget, cell shows #OVER!
Format: xlsx  Macros: No 
Size: 55kb  Download: customhasherrors.zip
Tutorial: Notes in file

FN0059 -Create Random Scenarios with Dependent List
Press F9 to select random items from 3 lists, to create a scenario. Third list depends on selection in first list.
Format: xlsx  Macros: No 
Size: 21kb  Download: excelrandomscenariosdependent.zip
Tutorial: Notes in file

FN0058 -Work Shift Number Calculation
Based on a maintenance issue start time, calculate the shift when the issue occurred
Format: xlsx  Macros: No 
Size: 56kb  Download: workshiftlcalculate.zip
Instructions: In the workbook

FN0057 -Calculate Survey Scores from Letters
Select letters (A, B, C, D) to answer survey questions. Formulas calculate total survey score, and show description based on total
Format: xlsx  Macros: No 
Size: 58kb  Download: sumproductcodevalues.zip
Instructions: In the workbook

FN0056 -Student Grades Report
Use the INDIRECT function, with the INDEX and MATCH functions to student grades for each subject. AVERAGE calculates the overall grade
Format: xlsx  Macros: No 
Size: 64kb  Download: studentgradesreport.zip
Instructions: In the workbook

FN0055 -Total Travel Distance Mileage Chart
Use INDEX and MATCH functions to find distance from city to city, during a vacation trip, based on a mileage lookup chart for Florida cities
Format: xlsx  Macros: No 
Size: 55kb  Download: indexmatchmileagetrip.zip
Instructions: Contextures Blog

FN0054 -Convert IP Addresses IPv4 to IPv6
Use the FIND, LEN and other functions to split and IPv4 address into parts. Then use DEC2HEX function to convert to hex codes for an IPv6 address
Format: xlsx  Macros: No 
Size: 58kb  Download: ipaddressconvert.zip
Instructions: Contextures Blog

FN0053 - Check Item Count with SUBSTITUTE
Use the LEN, TRIM and SUBSTITUTE functions to calculate how many items are in a cell, separated by commas or space characters. Or, check that at least 3 items are in a cell, such as a street address, "123 Maple St".
Format: xlsx  Macros: No 
Size: 58kb  Download: substituteitemcount.zip
Instructions: In the workbook

FN0052 - Lookup Challenge Solutions
Try this lookup formula challenge from Contextures Excel Newsletter 20171017. Create a formula in column E, to get the correct customer code for each record in the imported data. There are hints to help you, and solutions on a different sheet.
Format: xlsx  Macros: No 
Size: 78kb  Download: lookupcodesolutions.zip
Instructions: In the workbook

FN0051 - Rates Lookup for Courier and Country
Select a courier name and country from drop down lists. Weights and rates for those selections are shown in the table. Uses INDIRECT and INDEX functions to pull data from named ranges.
Format: xlsx  Macros: No 
Size: 55kb  Download: selectedlistshow.zip
2003 Version: selectedlistshow2003.zip
Instructions: N/A

FN0050 - Favourite Functions List
A summary of the favourite Excel functions mentioned in Jon Wittwer's survey of Excel experts. List of functions, and pivot table summary of the counts.
Format: xlsx  Macros: No 
Size: 66kb  Download: functionfavsummary.zip
Instructions: N/A

FN0049 - Product Price Based on Date
Product pricing table has a list of price changes, with dates. Invoice table pulls the applicable product price, based on selected product and invoice date.
Format: xlsx  Macros: No 
Size: 54kb  Download: pricelookupdaterange.zip
Instructions: N/A

FN0048 - Waste Collection Schedule
Enter a list of holiday weeks, regular pickup day, and the type of waste that is collected each week, then print the schedule.
Format: xlsx  Macros: No 
Size: 62kb  Download: wastecollsched.zip
Instructions: Excel Garbage Collection Schedule

FN0047 - Count Specific Codes in a List
Count specific codes in a range of cells, where some cells contain multiple codes. For example, count "JK" only where another code is "JKA".
Format: xlsx  Macros: No 
Size: 56kb  Download: countifcodes.zip

FN0046 - Change Language for TEXT Function
Select a language from the drop down list, and the TEXT function uses the correct code to show the weekday name -- "ddd" for English and "jjj" for French..
Format: xlsx  Macros: No 
Size: 55kb  Download: languageselect.zip

FN0045 - Excel Bingo Card With Pictures
The RAND function is used to produce sets of 3 unique Bingo cards in this Excel template. The CHAR function converts the number to a characters, and the Webdings font is used on the bingo cards, to show the selected characters as pictures.
Based on the original Bingo cards file below, that uses numbers.
Format: xlsx  Macros: No 
Size: 30kb  Download: bingocardpictures.zip

FN0033 - Show Top 10 For Filtered Items -- Instead of the overall Top 10, show highest items based on current filters, using SUBTOTAL or AGGREGATE. top10subtotal.zip 04-Mar-14  

FN0032 - Which Excel Function Are You? -- Answer survey questions with option buttons, to find out which Excel function you are. whichexcelfunction.zip 02-Mar-14  

FN0031 - Find Last Code With INDEX and MATCH -- Enter category name and INDEX/MATCH formula finds the last product code in that category, from a sorted list. productcodelast.zip 03-Feb-14  

FN0030 - Flexible AGGREGATE Function Selector -- Select function name and option from drop down list, and totals change to show AGGREGATE for those settings. aggregateselector.zip 05-Oct-13  

FN0029 - Show Text in Selected Language -- Select English or French from drop down list, and workbook text and graphics show that language. cerealboxlanguage.zip 29-Sep-13  

FN0028 - Use Check Box Result in Formula -- Add check boxes to the worksheet, and link to cells. Use cell results in worksheet formulas. CheckBoxFormulas.zip 06-July-13  

FN0027 - Print Marked Invoice Items -- Use INDEX and MATCH functions to show all selected items on a customer invoice. OrderFormMark.zip updated 26-June-13 Instructions

FN0026 - Get Travel Distance from Mileage Chart -- Use INDEX and MATCH functions to find distance between two cities. Conditional formatting highlights selected mileage in lookup table. indexmatchmileage.zip 08-May-13   Also see FN0055

FN0025 - Build Amazon Affiliate Links and HTML -- Use worksheet formulas to create links and HTML code for Amazon affiliate product links. AmazonLinksMaker.zip 11kb 13-Mar-13

FN0024 - DSUM With Excel Tables -- Use DSUM to calculate totals for a database that is a named Excel Table, for Excel 2007 and later. DSUM_ExcelTables.zip 28kb 14-Nov-12

FN0023 - Product Code Lookup in Date Range -- Find the promo code and discount rate for the selected product, based on the order date. PromoCodeLookup.zip 18kb 07-Nov-12

FN0022 - CONVERT Function Made Easy -- Convert amounts from one measurement unit to another, such as feet to metres. Excel VBA clears cells when new category is selected. ConvertFunctionExample.zip 23kb 13-Jul-12 Updated: 01-Aug-17 Instructions

FN0021 - Track Project Time -- Use Excel VBA to enter project task start and stop time, by double-clicking on a cell. Excel formulas total the time per task, and overall total time. On TimeStamp sheet, make a change in columns A:D, and username and date/time are added in columns E:F. UserName is a lookup from UserCode list. TimeTrackTotal.zip 20kb 26-Feb-12 Updated: 22-Nov-16

FN0020 - Track Driver Hours -- Enter maximum hours a driver can work in consecutive days. Conditional formatting highlights overtime hours. Pivot table totals the drivers' hours per calendar week.DriverLimits.zip 17kb 07-Apr-11

FN0019 - Split Points for Shared Rank -- If 2 or more players have same rank, they split points available for that rank, down to next occupied rank. For example, if 3 players share rank 1, they split total points for ranks 1, 2 and 3. RankPointsSplit.zip 16kb 11-Sep-10

FN0018 - Excel Weight Loss Tracker -- Calculate target weight (pounds or kilograms) based on BMI, record weekly weight, review results in dashboard. ExcelWeightTrackerLbKg.zip 20kb   04-Jan-10  Instructions

FN0017 - Create Model Numbers -- Automatically create model numbers in a list of items, either with a formula or event code. Model_Numbers.zip 9kb 04-Jan-09 

FN0016 - Treatment Calendar -- Enter a list of medication doses or injection sites, or another list. Click a button, to create a calendar with schedule of treatments. Excel template from Roger Govier. Treatment Calendar.zip 24kb 11-Dec-08

FN0015 - Named Range Pictures -- Select a weather condition name from dropdown list, and the matching picture appears. Uses dynamic ranges with Offset function. Excel template from Aaron Kinser. Weather_Pics.zip 99kb 30-Dec-07

FN0014 - Task List and Calendar -- Enter tasks and appointments in a worksheet list, and see items for selected week in a calendar layout. TaskCalendar.zip 23kb 15-Oct-06

FN0013 - Coloured Error Bars -- Calculate the difference between two scores and use coloured error bars to highlight the differences. ErrorBars.zip 4kb  03-Jul-06

FN0012 - Dynamic List -- uses Vlookup formula to create a dynamic list of completed items. VLookupCompleted.zip 10kb  22-Jul-05

FN0011 - Grant Distribution -- uses ranking to distribute available funds to applicants, based on request amount. GrantDistrib.zip 4 kb  05-Mar-05

FN0010 - Database Functions -- uses DSUM and DCOUNT to calculate totals in a database. DSUMSample.zip 11 kb  01-Mar-05

FN0009 - Print Unmarked Invoices -- uses VLookup to create an invoice; a macro prints unmarked items from the list, and marks as printed.
Excel 2007 and later: InvPrintMark.zip 30 kb  07-Feb-13 
Excel 2003:  InvPrintMark2003.zip 20 kb 

FN0008 - Invoice for Marked Item -- uses VLookup to create an invoice for the marked item in a list; a macro clears old marks. VLookupInvMark.zip 10 kb 

FN0007 - Extract Items with Formulas -- Extract items from a list to a new workbook, with formulas intact; contains a macro. Copy_To_Wkbk.zip 12 kb 

FN0006 - Extract List of Unique Items -- Use formulas to extract unique items from a list. Based on an example from Paul Cumbers. ExtractUnique.zip 5 kb 

FN0005 - Forecast vs Actual -- Enter forecast and actual amounts; summary sheet shows totals year to date; no macros - hyperlinks used for navigation. zipped file; BudgetYTD.zip 91 kb Instructions Page

FN0004 - ComboBox Selection -- uses a combo box and the Index function to extract an address from a list. ComboBoxAddress.zip 24 kb; 24-Apr-03; updated 27-Jul-13  

FN0003 - Bingo Cards -- The RAND function is used to produce sets of 3 unique Bingo cards in this Excel template. BingoCard.zip 7 kb
Also see the Bingo cards with pictures above.

FN0002 - Date Calculation -- enter today's date; create list of upcoming Wednesdays/Saturdays  NextWedSat.zip 6 kb

FN0001 - Daily Walking Record-- enter steps walked each day in this Excel template; set thresholds, keep track of days thresholds reached   WalkTrack.zip 8kb

SC0002 - Alternative to Scenario -- Excel Scenarios are limited to 32 changing cells. The method in this Excel template could use as many variables as columns. ScenarioAlt.zip 7 kb   19-Feb-04

SC0001 - Model Pricing Scenario -- In this Excel template data validation creates dropdown lists, Scenarios store variables, macro automates the scenario display. ScenarioParts.zip 12kb

CM0003 - Add Comments With Picture -- List the picture file names in column A, then run a macro to insert comments with those pictures in Column B. CommentPicCode.zip 25 kb  14-Jul-08

CM0002 - Display Comment Text -- Instead of popup comments, use programming to display comment description in a cell at the top of the worksheet, when a cell with comment code is selected. CommentLUText.zip 8 kb  20-May-06

CM0001 - Number and List Comments -- Use programming to number comments for printing. List numbered comments on a separate sheet. CommentsNumberPrint.zip 14 kb   22-Jan-06    Excel 2007 and later- CommentNumbersPrint2007.zip 19 kb   17-Aug-09