Sample Excel Files
Free Excel workbooks that you can download, to see how functions, macros, and other features work. Some files contain VBA code, so enable macros if you want to test those.
Data Validation
 |
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. 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 Click the option button for a region, and the data validation drop down shows list of colors for the selected region. . Format: xlsx Macros: No Size: 22kb Download: optionbuttonselect.zip Instructions: Option button setup |
 |
DV0069 - Excel Project
Task Tracker List project tasks, estimated time, person assigned, date completed, actual time, and other task information. Summarize task data in pivot tables, to see total times. Format: xlsx Macros: No Size: 23kb Download: projecttasklist.zip Instructions: Track Project Tasks in Excel |
 |
DV0068 - Show Data
Validation Input Message in TextBox Click a cell that contains a data validation list, and an ActiveX text box appears on the worksheet. It can show a long message about the cell's data validation. Use the scroll bars, if necessary, to see more of the message. Format: xlsm Macros: Yes Size: 52kb Download: datavalinputmsgactivex.zip Instructions: Code is different, but set up similar to Form Control text box |
 |
DV0067 - Select Multiple Items in ComboBox
or ListBox Click a cell that contains a data validation list, and a form appears. Click items in the ListBox, or type in the ComboBox and click Add. Then, click OK, to add all selected items to the cell. Lookup lists are in named ranges on different sheet. Format: xlsm Macros: Yes Size: 61kb Download: datavallistboxcombo.zip Instructions: N/A |
 |
DV0066 - Data Validation - Contains Text Type one or more letters in column B. Then, click the cell to the right, that contains a data validation list, and the drop down list will only show the product names that contain the letters you typed. Format: xlsm Macros: Yes Size: 22kb Download: datavalidationcontains.zip Instructions: Show Drop Down List With Specific Letters |
|
DV0065 - Data Validation Click Combobox
- Named Range 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. Lookup lists are in named ranges on different sheet. Format: xlsm Macros: Yes Size: 48kb Download: datavalcomboboxsheetclick.zip Instructions: N/A |
 |
DV0064 - Dependent Lists Clear Cells Select a region from the first dropdown list, and dependent cell's validation list shows only customers in that region. Customer cell is cleared when Region is selected, to prevent invalid Customer names. Format: xlsm Macros: Yes Size: 24kb Download: datavaldependclear.zip Instructions: Click here |
 |
DV0063 - Dynamic List With Blank Cells If a list contains blank cells, the usual formula for creating a dynamic named range does not work. Use formulas to create a second list, without the blanks, and base the dynamic range on that. Format: xlsx Macros: No Size: 13kb Download: dynamiclistblankcells.zip Instructions: Click here |
| 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 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. 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. 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 To see how this technique works, watch this short video. It shows the data validattion 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 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 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 |
Filters
 |
FL0032 - Colour Filter Headings Event code colours the filtered column headings, when an AutoFilter is applied. Heading colours stored on different sheet, applied when filter is cleared Format: xlsm Macros: Yes Download: filtercolour_headformat.zip |
 |
FL0031 - Filter Data in Multi-Select Cells For cells with multiple items entered, use a Slicer to filter for a specific item. Event code allows multiple selection and runs the filter macro Format: xlsm Macros: Yes Download: datavalmultiselectfilter.zip |
 |
FL0030 - Filter Data to Multiple Sheets Click buttons to run macros that filters the original data to different sheets, based on criteria. Extract data for specific areas, then send area data to other sheets, based on the status of each item. Format: xlsm Macros: Yes Download: advancedfiltermultisheets.zip |
 |
FL0029 - Colour Filter Headings in Excel Table Event code colours the filtered column headings, in a named Excel table, when a column filter is applied. NOTE: When the macro changes the heading colour, the UNDO list is cleared. Format: xlsm Macros: Yes Download: filtercolourheadings.zip |
 |
FL0028 - Clear Filters One at a Time After applying filters to an Excel Table, click the Clear Next Filter button to clear the filters one at a time, from right to left. Click the Reset button to set the Clear Next column number back to its original column count. Format: xlsm Macros: Yes Download: clearnextfiltermacro.zip |
 |
FL0027 - Set Filter Criteria With Slicers
Use Slicers to select criteria for an Advanced Filter. Slicers are connected to a pivot table that is based on the source data. In the extract range, select the headings for the fields that you want in the output. Format: xlsm Macros: Yes Download: advfiltercriteriaslicers.zip |
 |
FL0026 - Save Filter Settings in Custom
Views To make it easier to reapply filters, you can save settings in a Custom View. Show the selected view's name on the main sheet, based on a list of view names.. Format: xlsx Macros: No Size: 16kb Download: customviewsnames.zip Instructions: Save Excel Settings in Custom Views |
 |
FL0025 - Filter Excel 2010 Table With Slicers
|
|
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 |
UserForms, VBA, Add-Ins
 |
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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 download Format: xlsm Macros: Yes Size: 765kb Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 download Format: xlsm Macros: Yes Size: 780kb Download: 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. Instructions: Excel Sheet Lister Add-in Format: xlam Macros: Yes Size: 26kb Download: ctxsheetlister.zip |
| 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. Instructions: Macro to Copy Selected Items in Excel Table Format: xlsm Macros: Yes Size: 95kb Download: selectedrowsvisiblecopy.zip |
|
 |
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 Download: 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. Format: xlsm Macros: Yes Size: 68kb Download: selectsheettype.zip |
 |
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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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. 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 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 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 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. 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. 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
-- select tee off time, and mark 4 player names for that start time. Click a button, to put player names into schedule excelgolfteetimes.zip Go
to instructions page. |
Conditional Formatting
 |
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: xlsx Macros: Yes Size: 62kb Download: 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 Macros: No Size: 177kb Download: 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 Download: 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 Download: halloweenpumpkin.zip |
 |
CF0008 - Expiry Date Warning Highlight expiry dates that are in 30 days or less. Summary sheet shows total item count, and count of items expiring soon. Format: xlsx Macros: No Size: 58kb Download: expirydatewarning.zip |
 |
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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 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: xlss Macros: No Size: 87kb Download: 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 Download: 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 Download: 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 Download: 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 download 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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 Download: pivotmulticellsheet.zip |
 |
PT0037 - Compare Years in Pivot Chart In a pivot table, group dates by year and month, to create a chart that compares data year over year. Format: xlsx Macros: No Size: 44kb Download: pivotchartyearmonth.zip |
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 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 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
 |
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. |
 |
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 Download: 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 Download: 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 Download: 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 Download: pricelistscompare.zip INDEX/MATCH info: Click here |
 |
FN0040 - Option Button Choose Scores Use the CHOOSE function to assign a score for each option number. Format: xlsx Macros: No Size: 18kb Download: optionbuttonchoose.zip Option button info: Click here |
 |
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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 Download: 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 7 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 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
|
Unblock Files
2016-Jul - 28: After a recent Microsoft update, downloaded files might not work correctly, unless you unblock them, or store them in a Trusted Location.
Before you unzip the downloaded 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.
______________________
Sample Excel Files
______________________
Last updated: October 9, 2020 12:00 PM
Contextures RSS Feed
NOTE: No help is available for the sample Excel templates. If you have comments or suggestions, please contact ddalgleish@contextures.com
These sample Excel templates are provided "as is" for the sole purpose of illustrating Excel techniques. The authors do not warrant that the example Excel templates will meet your requirements or that the operation of the example Excel templates will be error free.