|
Home > Sample Files Sample Excel FilesFree Excel workbooks that you can download, to get insights on how functions, macros, and other features work. Some of these files contain VBA code, so enable macros if you want to test those spreadsheets.
Author: Debra Dalgleish |
Data ValidationDV0078- Drop Down with Latest Items at TopUse the SORTBY function (Microsoft Excel 365) to create drop down list with latest items at top, others below that, A-Z.
Product drop down on Orders sheet shows latest used product names at the top, with remaining products below that, listed alphabetically. Set the number of top products by typing a number on the Lists sheet.
DV0077- Add Headings in Drop Down ListAdd single-letter headings in a long list of items, so it's easier to navigate in the list.
The workbook has buttons to run macros that add the letter headings, or remove them.
After the letter headings are added, type a letter in a cell, then click the drop down arrow. The data validation list opens at the letter you typed, so it's easy to find what you need.
|
DV0076- Show List of All Matching ItemsSelect Region name from a data validation drop down list. Worksheet table shows list of employees from the selected region, using INDEX/MATCH formulas.
This video shows the steps for setting up the list of all matching items. |
 |
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. |
 |
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. |
 |
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. |
 |
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. |
 |
DV0071 - Time Entry
With Hour and Minute Lists |
 |
DV0070 - Option Buttons
Control Drop Down List |
 |
DV0069 - Excel Project
Task Tracker |
 |
DV0068 - Show Data
Validation Input Message in TextBox |
 |
DV0067 - Select Multiple Items in ComboBox
or ListBox |
 |
DV0066 - Data Validation - Contains Text |
|
DV0065 - Data Validation Click Combobox
- Named Range |
 |
DV0064 - Dependent Lists Clear Cells |
 |
DV0063 - Dynamic List With Blank Cells |
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. Excel File and Instructions: Limit Budget Amounts |
|
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. |
|
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 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 |
|
DV0035 - Dynamic Chart -- Select
Start and End dates from Data Validation lists; chart updates automatically
to display sales for selected time period 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. Works on
protected sheet |
|
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. 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 |
|
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. |
|
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. 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. To see how this technique works, watch this short video. It shows the data validation first, and then the combo box is added. |
|
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 Excel 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.
To see how this technique works, and a few multiple selection examples, watch this short video 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 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 22 kb Instruction page: Change Product Name to Code |
|
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  |
FiltersThese sample files show special techniques using Excel's AutoFilters and Advanced Filters. Some of the sample files contain macros, so enable macros when you open the files, if you want to test the macros. NOTE: For the complete list of Filter sample files, go to the Sample Excel Files - Filters page. FL0032 - Colour Filter HeadingsIn this workbook, code runs automatically when you filter one of the columns. The macro colours the heading cell with bright yellow fill, in the filtered column. The original heading colours are stored on a different worksheet in the file. When the filter is cleared, the macro automatically runs again, and applies the original colour from the stored colours.
|
FL0031 - Filter Data in Multi-Select CellsIn a formatted Excel Table, use this technique to filter cells that have multiple items entered. In the sample file, there are multiple weekday names in the WorkDays column. Above the table, click on a Slicer, to filter the WorkDays column for any rows that contain the selected weekday name. In the sample file, code runs automatically, to allow multiple selection, and and to filter the WorkDays column.
FL0030 - Filter Data to Multiple SheetsClick buttons to run macros that filters the original data to different sheets, based on criteria. The first macro extract data for specific areas, that are listed on one of the worksheets, The next macro sends that data to other sheets, based on the status that you enter for each item in the extracted list
More Filter Sample FilesFor the complete list of Filter sample files, go to the Sample Excel Files - Filters page. Here are some of the sample files you'll find there:
And many more Excel Filter sample files on the Sample Excel Files - Filters page |
UserForms, VBA, Add-Ins
 |
UF0055 - Transaction Search Edit UserForm Click button to open UserForm. Add new transactions, search for existing records to view or edit Instructions: N/A Format: xlsm Macros: Yes Size: 87kb Excel File: userformtransactionsearch.zip |
 |
UF0054 - Macro Lists All Sheets With Info Create a list of sheets in the active workbook, with details on cells used, and sheet contents Instructions: In Workbook Format: xlsm Macros: Yes Size: 85kb Excel File: excelsheetlistmacro.zip |
 |
UF0053 - Create PowerPoint Slides from Excel List Create a list of names in Excel table. In PowerPoint, run macro to put those names on individual slides, based on master slide. Use all names or test for criteria. Instructions: In PowerPoint file Format: Excel xlsl, PPT pptm Macros: Excel No, PPT Yes Size: 98kb Excel File: pptslidesfromxl_fldr.zip |
 |
UF0052 - List and Close All Workbooks At the end of the day, use this workbook to list and close all the open workbooks, except this macro file. The next day, open this macro file, then click a button to open all the files listed on the worksheet. Instructions: In the workbook Format: xlsm Macros: Yes Size: 64kb Excel File: listcloseallworkbooks.zip |
 |
UF0051 - UserForm Controls Demo This sample has a simple UserForm that shows how check box, option buttons, and listbox controls work. Instructions: See UserForm with Comboboxes page for general instructions Format: xlsm Macros: Yes Size: 68kb Excel File: userform_check_opt_list.zip |
 |
UF0050 - Happy Face Gauge Colour Data validation limits values that can be entered; event macro adjusts smile's curve, and colour of face. Another sheet has circle shape- colour and "pie slice" size change. Also see sample file DV0018 Instructions: In the workbook Format: xlsm Macros: Yes Size: 78kb Excel File: happyfacecolour.zip |
 |
UF0049 - Show Specific Sheets Multi Selector To see how this technique works, watch this short video |
 |
UF0048 - List All Files in Folder with Dates Click a button to run macros that list files in a specific folder. A) List all files, with name, size and date created. B) List all Excel files with name, size, with creation dates from Windows and Excel. . Instructions: In the workbook Format: xlsm Macros: Yes Size: 72kb Excel File: folderfileslist.zip |
 |
UF0047 - Click Button to Capture Data Click a worksheet button, and a macro captures the current date and time, name entered on worksheet, and number from button. Instructions: In the workbook Format: xlsm Macros: Yes Size: 70kb Excel File: buttonclickdata.zip |
 |
UF0046 - Run Macro based on Worksheet Data Enter quantity, and button text changes, based on lookup table. If 41 or over, caption "See Discounted Price", ShowPriceDisc runs. Different caption and macro for 40 or below. Instructions: In the workbook Format: xlsm Macros: Yes Size: 65kb Excel File: buttoncaptionmacrolookup.zip |
 |
UF0045 - Personalized Error Message Show a personalized error message with user name from network or application, when budget total is above the limit. Instructions: In the workbook Format: xlsm Macros: Yes Size: 69kb Excel File: errormessagepersonal.zip |
 |
UF0044 - List Folder File Counts Enter a folder path in the green cell, then click the button. Macro adds new sheet to workbook, with list of subfolders in the main folder, and file counts for all folders. Instructions: In the workbook Format: xlsm Macros: Yes Size: 64kb Excel File: folderlistmacro.zip |
 |
UF0043 - Better Hyperlinks for Excel Sheets Use hyperlinks and a background macro, to overcome limitations of normal Excel hyperlinks. Warning for hidden sheets, go chart sheets, and don't change the selected cell. Zipped folder has workbook and test files - keep all in one folder, for testing. Contributed by J. Woolley NOTE: Before you unzip the folder, unblock the zipped file. Instructions: PDF file with instructions in zipped file Format: xlsm Macros: Yes Size: 765kb Excel File: hyperlinksheetname_jw.zip |
 |
UF0042 - Show and Hide Sheet Groups Click a main sheet tab to show its related sheets, and hide other sheets. Instructions: In the workbook Format: xlsm Macros: Yes Size: 74kb Excel File: sheetgroupshow.zip |
 |
UF0041 - Enter Golf Scores Click button to open the input form. Type single-digit score, and next hole's box is automatically selected. Click OK to add scores to worksheet. Instructions: In the workbook Format: xlsm Macros: Yes Size: 70kb Excel File: golfscoreuserform.zip |
 |
UF0040 - Add Items to Related Table Enter company name and number of passes in table one, and a row for each pass is added in table two. Instructions: In the workbook Format: xlsm Macros: Yes Size: 65kb Excel File: companypasses.zip |
 |
UF0039 - Create a Tartan Pattern Store tartan setts, and build a tartan pattern from the stored setts, or create new setts. Tartan tab appears on Excel Ribbon when file opens. Contributed by John Marshall Instructions: In the workbook Format: xlsm Macros: Yes Size: 143kb Excel File: tartanmakermacro.zip |
 |
UF0038 - Hyperlinks Run Command Files Use hyperlinks and a background macro, to run command or script files from Excel, with a single click. Zipped folder has workbook and test files - keep all in one folder, for testing. Contributed by J. Woolley NOTE: Before you unzip the folder, unblock the zipped file. Instructions: PDF file with instructions in zipped file Format: xlsm Macros: Yes Size: 780kb Excel File: hyperlinkruncommand_jw.zip |
 |
UF0037 - Sheet Lister Add-in Popup list of sheets in the active workbook. Sort by sheet order or alphabetical order. Click a sheet name to go to that sheet. This UserForm is modeless, so leave it open while you work in Excel. Excel File and Instructions: Excel Sheet Lister Add-in |
| UF0036 - Copy Selected Rows in Table In a filtered table, select cells in one or more rows, then run the macro. The entire row for each selection is copied, and pasted at the end of the table, or pasted below the last row in the selection. Hidden rows are not copied, just the visible rows. NOTE: If columns are also hidden, the data will not be pasted correctly - unhide columns before using this macro. Excel File and Instructions: Macro to Copy Selected Items in Excel Table |
 |
UF0035 - Show Sheets With Selected Tab Colour Select a sheet type from the drop down list, and only sheets with matching tab colour are visible. Choose (All) to see all the worksheets. Format: xlsm Macros: Yes Size: 86kb Excel File: selectsheettabcolor.zip |
 |
UF0034 - Show Specific Sheets Select a sheet type from the drop down list, and only sheets with that text in their name are visible. Choose ALL to see all the worksheets. Excel File and Instructions: Show Specific Worksheets |
 |
UF0033 - Insert a Section Heading To make it easier to insert rows on a worksheet, use a macro. This file has an Admin sheet, where a formatted heading section (3 rows) is stored. To insert a section heading on the Data Entry sheet, select a cell in the row where the new section should start. Then, click the button, and confirm that you want to insert the heading. The Data Entry sheet is protected, and the code unprotects, then reprotects the sheet. Format: xlsm Macros: Yes Size: 64kb Excel File: insertnewsection.zip |
 |
UF0032 - UserForm With Macro Buttons Instead of putting macro buttons in a frozen section at the top of a worksheet, put them in a UserForm. With Worksheet_Open code, the form will open automatically when the file opens. The ShowModal setting is False, so the form can stay open while you edit the worksheet. Position the form anywhere on the screen, so it is easy to access while you work, or during a presentation. Format: xlsm Macros: Yes Size: 68kb Excel File: userformbuttons.zip |
 |
UF0031 - Excel Advent Calendar 2015 Click a numbered square and a macro reveals a Christmas picture each day. There is a simple formula on each window, instead of a number -- create your own formulas, or change to numbers, if you prefer. Format: xlsm Macros: Yes Size: 188kb Excel File: adventcalendar_2015.zip |
 |
UF0030 - List All Files and Properties Enter a folder path and name, then click the button to create a list of all files, and their properties. Or, click button to create a list of properties and their names. Format: xlsm Macros: Yes Size: 30kb Excel File: folderfilesproperties.zip |
 |
UF0029 - Adjust Font Size For Printing
To ensure that the first four rows in a table print at a consistent size, a Workbook_BeforePrint macro increases the font size, if necessary, based on the zoom level. After printing, the font size is reset.. Format: xlsm Macros: Yes Size: 29kb Excel File: fontscaleforprinting.zip |
 |
UF0028 - Dependent Combo Boxes on Worksheet
Select a category from one combo box, list of related products appear in the dependent combo box. Product combo box is cleared when a different category is selected. Format: xlsm Macros: Yes Size: 29kb Excel File: comboclearlookup.zip |
|
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. |
|
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 Instructions and sample file: Email from Excel with PDF 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. Excel File and Instructions: UserForm with Dependent Comboboxes 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 UF0015 - Excel Calorie Counter -- Keep track of daily calories, and store the data on a separate sheet. See the summary in a pivot table. Excel File and Instructions: 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. |
|
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. 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. Excel File and Instructions: Sheet Selector Excel 2003 UF0003 - Parts Database with Comboboxes -- UserForm with comboboxes for data entry, with database on a hidden worksheet. Pivot table summarizes the inventory. Excel File and Instructions: UserForm with Comboboxes |
|
UF0002 - Parts Database -- simple example of creating a UserForm for data entry, with the database on a hidden worksheet. Excel File and Instructions: Create an Excel UserForm UF0001 - Assign TeeOff Times
-- select tee off time, and mark 4 player names for that start time. Click a button, to put player names into schedule. Excel File and Instructions: Golf Tee Off Times. |
Conditional Formatting
 |
CF0014 - Canada and USA Flags (Excel 365) |
|
CF0013 - Canada and USA Flags Choose country name from drop down list. Conditional formatting shows flag for selected country. Instructions: Contextures Blog Format: xlsx Macros: No Size: 159kb Excel File: flags_usa_cda.zip |
 |
CF0012 - Get Conditional Formatting Colour Info Double-click a cell with conditional formatting colour, to copy that colour to adjacent cell, and show the RGB numbers. Or, run macro for colour info in message. Format: xlsm Macros: Yes Size: 62kb Excel File: getcondformatcolour.zip |
 |
CF0011 - Advent Calendar - No Macros - Background Picture Simple Advent Calendar uses background picture, formulas and conditional formatting to show a different picture each day, from December 1 to 24. Format: xlsx file Macros: No Size: 177kb Excel File: adventcalendarbackground.zip |
 |
CF0010 - Advent Calendar - No Macros Simple Advent Calendar uses formulas and conditional formatting to show a different picture each day, from December 1 to 25. Format: xlsx Macros: No Size: 66kb Excel File: adventcalendar2018.zip |
 |
CF0009 - Halloween Pictures Click Spin Button to change eye colour in skull, and see worksheet message. Click check box to see scary or friendly pumpkin picture. Formulas change messages, no macros. Format: xlsx Macros: No Size: 206kb Excel File: halloweenpumpkin.zip |
 |
CF0008 - Expiry Date Warning This video shows the steps for formatting cells, based in the value in another cell. |
 |
CF0007 - Excel Valentine Cards Interactive card uses data validation and conditional formatting to colour cells in the shape of a heart. Also contains heart-shaped creature cards, made from Excel shapes. Format: xlsx Macros: No Size: 63kb Excel File: excelvalentinecards.zip |
 |
CF0006 - Show Selected Color Select a color name from a drop down list, adjacent cell shows the selected color. Data validation and conditional formatting are used in this technique. Format: xlsx Macros: No Size: 14kb Excel File: condformatselcolor.zip |
|
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 |
External Data
|
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
|
Dashboards
| DB0001 - Show Top 3 Plus Other Totals -- Link to a pivot table to show totals for the top 3 categories. Calculate the total for all other items, create dashboard chart with Top 3 and Other. Details in blog post. DashboardTopDates.zip 26 kb 23-Mar-11 |
Charts and Graphics
Chart Utilities -- Peltier Technical Services, Inc. Charting Utilities
 |
CH0013 - Chart with Dynamic Date Range -- Select start and end dates from drop down lists, and the line chart updates automatically, to show the selected date range. Format: xlsx Macros: No Size:63kb Excel File: chartdaterangedynamic.zip |
 |
CH0012 - Seating Plan -- Select guest names from drop down list, names appear at selected table in seating plan. Based on data validation and donut charts. Format: xlsx Macros: No Size: 82kb Excel File: seatplancharts.zip Online: Google Sheets version |
 |
CH0011 - Chart Colour Based on Rank -- Macro changes the pie chart and bar chart colours, based on number in Rank column. Slice size and bar length are based on Order amount, colours are based on ranking of percentage invoiced. Format: xlsm Macros: Yes Size: 75kb Excel File: piechartcondformat.zip |
|
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 This video show the steps for making a pie chart in Excel. |
|
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. Instruction page: Excel Gantt Chart Sample File: 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. |
|
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
|
PivotTables
 |
PT0050 - Excel Budget Template -- Track and plan your spending in this budget template from Bob Ryan, founder of Simply Learning Excel. Review cash and credit card spending in pivot tables that show monthly totals. Format: xlsx Macros: No Size: 87kb Excel File: pivotsamples/actual_budget_workbook_br.zip |
 |
PT0049 - Slicer Shows Pivot Table Value Groups -- Click a Slicer, to quickly show and hide groups of values in a pivot table. Function slicer changes summary function and heading. Format: xlsm Macros: Yes Size: 112kb Excel File: slicervaluegroupshow.zip |
 |
PT0048 - Filter All Pivot Tables With One Slicer -- Select a region on the main Slicer, and all 3 pivot tables are filtered for that region. Pivot tables are based on 2 different data sources. Format: xlsm Macros: Yes Size: 83kb Excel File: multipivotsourceslicer.zip |
 |
PT0047 - Budget Report Selector -- Enter Forecast and Actual data for a budget, see the summary in a pivot table. Click the Slicer to change values in the Pivot Table report - choose the forecast, actual, YTD, Variance or Variance Percent. Format: xlsm Macros: Yes Size: 88kb Excel File: budgetselectreport.zip |
 |
PT0046 - Analyze Food Tracker Data in Excel -- Export data from the LoseIt app, and paste into this workbook. Pivot tables summarize the data. Format: xlsx Macros: No Size: 74kb Instructions and Excel file link: Excel Food Tracker Data Page |
 |
PT0045 - Hide Subtotals for Single Items -- Hide subtotals for sections with only one item, in simple pivot table. Conditional formatting rules check for "Total" in column B, and different data in row above. Custom pivot style adds border after each category. Format: xlsx Macros: No Size: 66kb Excel File: pivotsubtotalshide.zip |
 |
PT0044 - List Pivot Fields and Pivot Items
-- Adds sheet with list of all row, column, and report filter fields,
and their items. Visible items are marked "Y". Select cell
in pivot table, before running macro. You will be prompted for maximum
number of items per field to list without confirming. Format: xlsm Macros: Yes Size: 82kb Excel File: pivotlistallfilters.zip |
 |
PT0043 - Track and Summarize Activities
-- Record daily activities and summarize in a monthly report.
Sample shows student and parent activities at a school. Same technique
could be used for business activities. Format: xlsx Macros: No Size: 25kb Excel File: activitytrack.zip |
 |
PT0042 - Results from Pivot Table Survey
-- Pivot tables summarize data from the Pivot
Table Survey on my Contextures Blog, August 2015. Format: xlsx Macros: No Size: 43kb Excel File: pivottablesurvey.zip |
 |
PT0041 - Show Survey Results in Pivot Table
-- Pivot tables summarize data from the Excel
Version Survey on my Contextures Blog, July 2015. Format: xlsx Macros: No Size: 75kb Excel File: pivotsurveyresults.zip |
 |
PT0040 - Show Filtered Data Percent of Grand
Total -- Pivot table on hidden sheet shows all data. Pivot table
on main sheet is filtered. Summary table uses GetPivotData formula
to show filtered data as % of grand total. Format: xlsx Macros: No Size: 56kb Excel File: pivotfilteredpercentage.zip |
 |
PT0039 - Copy Source Data Number Formatting -- Select a cell in a pivot table and click button to run macro.
Number formatting from source data is applied to all fields in the
Values area. Format: xlsm Macros: Yes Size: 113kb Instructions: Format Pivot Table Numbers Excel File: pivotnumberformatsource.zip |
 |
PT0038 - Change Report Filters With Cell
Dropdown Same Sheet -- Select an item from a data validation dropdown,
and all pivot tables on that worksheet show that selection in the
page field. Excel 2002 and later versions. Format: xls Macros: Yes Size: 28kb Excel File: pivotmulticellsheet.zip |
 |
PT0037 - Compare Years in Pivot Chart This video shows the steps for setting up a pivot chart with separate lines for each year. |
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. |
|
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. 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 |
|
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 Also see updated version - PT0050 - Excel Budget Workbook 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 your credit card export 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 xls files and later
versions. PivotMultiPagesChange.zip
17 kb 21-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 |
Functions
 |
FN0073 - - Weekly Declutter Schedule -- How many items can you remove every day, to declutter your home or office? Running total per week, and over multiple weeks. You could use this for other plans too, such as minutes per day that you want to practise a new skill. |
 |
FN0072 - - Hey Scale Chart -- In a Hey Scale, count number of Ys, if someone types “Heyy” in text message. Excel formulas count Ys, VLOOKUP gets result in rating table. Create your own rating system! |
 |
FN0071 - - Triangle Calculator -- Students can check their answers, when learning trigonometry. Enter 3 variables, completed dimensions are returned in Output area. Sample file created by Peter Atherton. |
 |
FN0070 - Excel Function Word Search -- Print out this Excel Function word seach game, and use it as an ice breaker at your next meeting. Everyone loves Excel games! |
 |
FN0069 - STOCKHISTORY Function Min and Max -- For Excel 365. Select two properties to show for the selected stock symbol, along with the date. Min and Max values show for each property, and the event date |
 |
FN0068 - Bingo Card 1 to 100 -- The RAND function is used to produce sets of 3 unique Bingo cards
in this Excel template |
 |
FN0067 - Spill Formula Examples -- Examples show how to use Excel's new spill functions (Microsoft 365), on the worksheet, and in VBA. Thanks to Dermot Balson |
 |
FN0066 - Dynamic Filtered Lists -- Enter criteria, and select headings for the columns you want in the filter results. Uses Excel's new dynamic array functions (Microsoft 365). |
 |
FN0065 - Excel Multiplication Tables -- Practise the multiplication tables, one row at a time, or fill in the entire table. Turn error checking on or off. |
 |
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 |
 |
FN0063 - Compare 2 Lists -- Compare two lists to find new items, using the COUNTIF function. |
 |
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. This video shows the steps for showing or hiding the borders in the weekly planner template. |
 |
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. |
 |
FN0060 - Create Custom Hash Errors in Excel |
 |
FN0059 -Create Random Scenarios with Dependent List |
 |
FN0058 -Work Shift Number Calculation |
 |
FN0057 -Calculate Survey Scores from Letters |
 |
FN0056 -Student Grades Report |
 |
FN0055 -Total Travel Distance Mileage Chart |
 |
FN0054 -Convert IP Addresses IPv4 to IPv6 |
 |
FN0053 - Check Item Count with SUBSTITUTE |
 |
FN0052 - Lookup Challenge Solutions |
 |
FN0051 - Rates Lookup for Courier and Country |
 |
FN0050 - Favourite Functions List |
 |
FN0049 - Product Price Based on Date |
 |
FN0048 - Waste Collection Schedule |
 |
FN0047 - Count Specific Codes in a List |
 |
FN0046 - Change Language for TEXT Function |
 |
FN0045 - Excel Bingo Card With Pictures |
 |
FN0044 - Hyperlink Formulas for Worksheet
Navigation Create a list of worksheet names in a workbook. Then add HYPERLINK formulas to each sheet, to navigate to the next or previous sheet. No macros required -- the formulas do all the work. Format: xlsx Macros: No Size: 58kb Excel File: hyperlinknavigation.zip |
 |
FN0043 - Calculate Project End Date with
WORKDAY.INTL Enter project start date, and number of days, and formula calculates the end date. Specify the weekdays that are non-working, and list the non-working holidays. For Excel 2010 or later. Format: xlsx Macros: No Size: 19kb Excel File: excelworkdayintlfunction.zip |
 |
FN0042 - Activity Tracker Chart Keep track of your daily activity, and see the running total of days, and minutes of activity. Format: xlsx Macros: No Size: 11kb Excel File: movemberactivitychart2015.zip |
 |
FN0041 - Find Supplier With Lowest Price Use the INDEX and MATCH function to find product prices from 3 supplier lists. Highlight lowest price and show supplier name. Format: xlsx Macros: No Size: 21kb Excel File: pricelistscompare.zip INDEX/MATCH info: Click here |
 |
FN0040 - Option Button Choose Scores This video shows how to pick answers with Excel Option buttons. |
 |
FN0039 - Create Random Scenarios Press F9 to select random items from 3 lists, to create a scenario. Could be used to select names for groups, or protein and side dishes for a meal. Uses INDEX, RANDBETWEEN and COUNTA functions. Format: xlsx Macros: No Size: 17kb Excel File: ExcelRandomScenarios.zip Tutorial: Click here Also see: FN0059 - with dependent list |
|
FN0038 - Option Button Survey Scores Select options in the survey, and see a score for each option. Format: xlsx Macros: No Size: 18kb Excel File: optionbuttonscores.zip Option button info: Click here |
 |
FN0037 - Student Time Tracker Keep track of your lecture hours, and course work hours, to see what the weekly totals are. Format: xlsx Macros: No Size: 23kb Excel File: StudentTimeTracker.zip Instructions: Click here |
 |
FN0036 - Count Hotel Guests in Date Range Use SUMIFS and COUNTIFS to count hotel guests who stayed during a specific date range. Format: xlsx Macros: No Size: 22kb Excel File: guestdaterange.zip Instructions: Click here |
 |
FN0035 - Create Random Text with CHOOSE
Function Use CHOOSE with RANDBETWEEN to create random text in a sample file, to use for testing.. Format: xlsx Macros: No Size: 18kb Excel File: chooserandomtext.zip |
 |
FN0034 - Create IFRAME Code in Excel Enter variables such as URL, height and width, then copy IFRAME code to your web page. Format: xlsx Macros: No Size: 13kb Excel File: exceliframegenerator.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 04-Jan-10 Instructions and more weight trackers 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. |
|
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 64 kb |
|
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 |
Scenarios
|
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
|
Comments
|
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 This video shows how to add a picture to an Excel comment, manually. |
| CM0002 - Display Comment Text -- Instead of pop-up 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 macros to number comments for printing. List numbered comments on a separate sheet. 17-Aug-09
|
Unblock Files
Due to Windows security settings, files that you get from the internet might not work correctly, unless you unblock them, or store them in a Trusted Location.
Before you unzip the files, follow the steps below, to unblock the files
- In Windows Explorer, right-click on the zipped file
- In the pop up menu, click Properties
- Add a check mark in the Unblock box, near the bottom of the Properties window.
- Click OK, to close the Properties window.
