excel 2007 assignments for students

> Sample Files

Free 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:

Use 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 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.

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 - -- 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.
: xlsm : Yes : 78 kb
:

DV0074 - - 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. 
: xlsx  : No  : 63kb 

DV0073 - -- 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. 
: xlsm  : Yes  : 69kb
:

DV0072 - -- 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 -
Enter production time, then select hour and minutes for defect time. Must not be greater than production time, and drop down lists control the valid times.
:

DV0070 -
Click the option button for a region, and the data validation drop down shows list of colors for the selected region.
:

DV0069 -
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.
:

DV0068 -
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.
: xlsm  : Yes  : 52kb 
: Code is different, but set up similar to

DV0067 -
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.
: xlsm  : Yes  : 61kb 
:

DV0066 -
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.
:

DV0065 -
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.
: xlsm  : Yes  : 48kb 

: N/A

DV0064 -
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.
: xlsm  : Yes  : 24kb 

:

DV0063 -
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.
: xlsx  : No  : 13kb 

:

DV0062 - -- 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.  

DV0061 - -- 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:  

DV0060 - -- Select an employee name from the first drop down, second drop down shows all skills for that employee.    

DV0059 - -- 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.    

DV0058 - -- 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.

:

DV0057 - -- 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.  

DV0056 - -- 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.  

DV0055 - -- 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.  

DV0054 - -- 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 - -- 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 .  
Roger Govier has created an Excel template with another method of using one formula to show different drop downs.  

DV0052 - -- 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 .  

DV0051 - -- Select a player from a dropdown list in this Excel template, and that name disappears from the other dropdowns for that inning.  

DV0050 - -- 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.  

DV0049 - -- 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.  

DV0048 - -- 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.

DV0047 - -- Limit the choices in a Dependent Data Validation list, hiding items that have been previously selected in this Excel template.  

DV0046 - -- 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.   

DV0045 - -- 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.  

DV0044 - -- 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.   

DV0043 - -- 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. Updated 11-Oct-06  

DV0042 - -- If a selection has been made in a dependent cell in this Excel template, the data validation list is limited to the current selection.  

DV0041 - -- 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.  

DV0040 - -- 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.

DV0039 - -- Use data validation and shapes linked to cells, to plan table seating arrangement. NOTE:

DV0038 - -- Use formulas to automatically add new unique items to a data validation dropdown list. Excel template from Ron Coderre.  

DV0037 - -- 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.  

DV0036 - -- 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 - -- Select Start and End dates from Data Validation lists; chart updates automatically to display sales for selected time period
Excel File and Instructions:

DV0034 - -- 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 - -- Select a customer, then select a shipping location from the dependent dropdown. VLookup formulas return the shipping address for the selected location.  

DV0032 - -- 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.  

DV0031 - -- Select a value from a data validation dropdown, and an event procedure captures the current data, and creates a scatter chart.

DV0030 - -- 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.  

DV0029 - -- Two samples of code altering dependent cells. Source lists on separate sheet.
1) Selection from two lists must be different, or cell is cleared
2) Select from either list, and adjacent cell is filled in. Based on code from Jason Morin.

DV0028 - -- 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 - -- 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.

DV0026 - -- Selection from the first list runs event code that changes the validation in adjacent cell.

DV0025 - -- 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

DV0024 - -- Plan your meal preparation, using Data Validation to enter food items. Formulas calculate the cooking schedule, based on the target meal time.   

DV0023 - -- 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.

DV0022 - -- If you change an item in a data validation source list, the worksheet may show previously selected items. Event code can update the worksheet when you update the source list.
Multiple lists:
One list:

DV0021 - -- 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.
Excel File and Instructions:

DV0020 - -- 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.
Excel File and Instructions:

To see how this technique works, watch this short video. It shows the data validation first, and then the combo box is added.

DV0019 - -- Data validation limits values that can be entered; event macro enters the default value for the selected option.

DV0018 - -- 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  Excel file:

DV0017 - -- 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 - -- Names are removed from data validation dropdown list once they've been assigned to a daily task.   

DV0015 -- The first dropdown list is based on column headings. The second list contains unique items from the selected column, sorted in descending order.

DV0014 - -- 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. 3 kb

DV0013 s -- 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.

DV0012 -- 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.

DV0011 -- shows customers with start and end dates that include selected date, macro automates the list creation.

DV0010 -- uses data validation to create dropdown lists, Scenarios to store variables, macro automates scenario display.

DV0009 -- uses data validation to create dropdown lists, VLookups to return values from named ranges on different sheets.

DV0008 -- uses data validation to create dropdown lists, with only checked items appearing in the list.  

DV0007 -- uses data validation to create dropdown lists, and dynamic named ranges to plot the selected date range.   
: See version in Charts section:

DV0006 -- uses data validation to create dropdown lists, and the VLookup and Match functions to extract information from a table.  

DV0005 -- Data Validation dropdown displays product name and ID; an event procedure changes the selection to product name. (XL2000 +) ; (XL97)

DV0004 -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. Instruction page:

DV0003 -- Select items from the dependent Data Validation lists; a VLookup formula extracts the unit price.

DV0002 -- uses Data Validation and VLookup to extract details for an invoice

These 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.

: For the complete list of Filter sample files, go to page.

In 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.

In 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 -

Click 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

For the complete list of Filter sample files, go to page.

Here are some of the sample files you'll find there:

And many more Excel Filter sample files on page

UF0049 -
Select a sheet type on one of the 3 Selector sheets, and only sheets with that text in their name are visible. Choose ALL to see all the worksheets. For single Selector sheet version, see
: In the workbook
: xlsm  : Yes  : 76kb 

To see how this technique works, watch this short video

UF0027 - -- Select a country name from a drop down list, to hide or show a worksheet. 05-Jun-14

UF0026 - -- Change a combo box setting, to prevent invalid entries in the UserForm. 13-Jun-11

UF0025 - -- 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. 21-Jan-14

UF0024 - -- 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 emails to each address on a worksheet, with a PDF attachment created from report worksheet. 06-Nov-13    :

UF0022 - -- Enter data on a worksheet, in multiple data entry areas. Add new records, view and update existing records. 04-May-13

UF0021 - -- Sample code from Jim Cone. Select a list on the worksheet, and click arrows to scroll the list items up or down.

UF0020 - -- 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. :

UF0019 - -- 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.  

UF0018 - -- 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.  

UF0017 - -- 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:   Excel 2003:   

UF0016 - -- 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.  
:

UF0015 - -- Keep track of daily calories, and store the data on a separate sheet. See the summary in a pivot table. :

UF0014 - -- Add Help buttons to a worksheet, and open a UserForm to a specific page, to show relevant help.  

UF0013 - -- Show an Excel message box that includes the network username.  

UF0012 - -- Automatically add a new worksheet with the month name, when the Excel file opens at the start of the month.  

UF0011 - -- Click a numbered square and a macro reveals a Christmas picture each day. For details see article on Contextures Blog.  

UF0010 - -- 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 .  

UF0009 - -- Sample code from Sam uses keyboard shortcut to run macros that show or hide outline rows on a worksheet.
:     : From comment on Contextures Blog .

UF0008 - -- 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.
:     : Contextures Blog .

UF0007 - -- File contains two methods for navigating in a large workbook, from reader comments on the Contextures Blog article . 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).  

UF0006 - -- 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.  

UF0005 - -- 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.

UF0004 - -- This add-in creates a floating toolbar, that you can open in any workbook, and creates a list of sheets in that workbook. :

UF0003 - -- UserForm with comboboxes for data entry, with database on a hidden worksheet. Pivot table summarizes the inventory. :

UF0002 - -- simple example of creating a UserForm for data entry, with the database on a hidden worksheet. :

UF0001 - -- select tee off time, and mark 4 player names for that start time. Click a button, to put player names into schedule. : .
Also see this golf page, with its sample file:

CF0014 -
Choose country name from drop down list. Conditional formatting shows flag for selected country.
: xlsx  : No  : 142kb 

Excel 365 - uses spill functions (for older versions, )

CF0008 -
Highlight expiry dates that are in 30 days or less. Summary sheet shows total item count, and count of items expiring soon.

: xlsx  : No  : 58kb 

This video shows the steps for formatting cells, based in the value in another cell.

CF0005 - -- Set a date range and highlight upcoming anniversary dates for employees, based on hire date.

CF0004 - -- A coloured border separates dates in a list, and the conditional formatting formula works even if some rows are hidden.

CF0003 - -- 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.

CF0002 - -- A formula totals hours worked. Conditional formatting highlights hours over regulated limit. Pivot table totals weekly hours.

CF0001 - -- To guide users, highlight columns headings when an item is selected from a data validation dropdown list.  

ED0003 - -- Use XML Maps to show RSS feeds in Excel. This sample file has feeds for Contextures website and Contextures Blog.  

ED0002 - -- Facilitates changes to the connection strings and queries that extract the external source data that pivot tables use. Created by Ron Coderre.     

ED0001 - -- 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.    

DB0001 - -- 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 .

: Go to , for more free download files

CH0010 - -- Compare high and low scores for wins and losses. Calculate differences between high and low, to create data for chart.

CH0009 - -- Set up data and create a simple pie chart that is easy to read.

This video show the steps for making a pie chart in Excel.

CH0008 - -- Show sales quantity in a line chart, with target range shown in the background.

CH0007 - -- Macro changes all sparklines on the active sheet, so they will show data, even if rows and columns are hidden. Excel 2010 or later.   

CH0006 - -- Select words from drop down list and chart shows how frequently they were used in speeches, by each political party.   

CH0005 - -- 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.  

CH0004 - -- 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: Sample File:   

CH0003 - -- 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.   

CH0002 - -- Using named ranges and a linked picture, show or hide a chart based on the selection from a drop down list. .
There are 3 versions of the sample file:
1) Excel 2003 -  
2) Excel 2010 (2 item drop down) -  
2) Excel 2010 (multi item drop down) -  

CH0001 - -- 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.    

PT0037 -
In a pivot table, group dates by year and month, to create a chart that compares data year over year.

: xlsx  : No  : 44kb 

This video shows the steps for setting up a pivot chart with separate lines for each year.

PT0036 - -- Use conditional formatting and custom number formats to show text instead of numbers, in pivot table value area.
a. - no macros   
b. - macro by

PT0035 - -- Change pivot table layout or settings, to focus on customers who have not bought specific products   

PT0034 - -- Hyperlinks can't be added to a pivot table, so this sample uses VBA and formatting to create fake hyperlinks   

PT0033 - -- 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 .   

PT0032 - -- 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.   

PT0031 - -- 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.   

PT0030 - -- 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 .  

PT0029 - -- 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.

PT0028 - -- 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.  

PT0027 - -- 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.
1) Change any page field in a pivot table, and all matching page fields, on all sheets, are changed.
2) Change any page field in a pivot table, and all matching page fields, on the active sheet only, are changed.
3) Change a specific page field in a pivot table, and that page field, on the active sheet only, is changed.  

: In this sample file, change specific page field in any pivot table, and that page field is changed in all pivot tables in the workbook.  

PT0026 - -- 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.  

PT0025 - -- 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.  

PT0024 - -- 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.  
Plug and Play version:  

PT0023A - -- 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.   Also see updated version - - Excel Budget Workbook

PT0023 - -- Excel template from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz. Details in blog post . Uses code to automatically create a pivot table from multiple sheets in a workbook.   

PT0022 - -- -- 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    .   

PT0021 - -- 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-    Excel 2007-  

PT0020 - -- Facilitates changes to the connection strings and queries that extract the external source data that pivot tables use. Created by Ron Coderre.     

PT0019 - -- 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.   

PT0018 - -- Copy your credit card export csv data into this workbook, and view summary reports by store and expense category.   

PT0017 - -- 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.   

PT0016 - -- 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.   

PT0015 - -- 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.

PT0014 - -- Enter start and end dates on the worksheet, and update the pivot table, to show matching items.

PT0013 - -- Select an item from a dropdown list on the worksheet, and event code refreshes the pivot table, to show matching items.

PT0012 - -- 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.

PT0011 - s -- 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.

PT0010 - -- 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.

PT0009 - -- Select a question from the dropdown list, and view survey results for that question.

PT0008 - -- 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.   
Excel 2000 and older versions

PT0007 - -- 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.  

PT0006 - -- Use a spinner from the Control tool box to select the next item in the page field. 17 kb  Updated to skip hidden items and to show (All).  

PT0005 - -- Modify captions in a pivot table, and the matching data in the pivot table source is changed.    

PT0004 - -- 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.

PT0003 - -- Change page field selection in main pivot table, and same selection is made in related pivot tables in this Excel template.

PT0002 - -- Mark items on a worksheet list, and display those items in the pivot table in this Excel template.

PT0001 - -- record hours worked; Pivot Table report and chart

FN0073 - -- 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.
: xlsx  : No  : 59 kb 
: Notes in Workbook

FN0072 - -- 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!
: xlsx  : No  : 54 kb 
:

FN0071 - -- Students can check their answers, when learning trigonometry. Enter 3 variables, completed dimensions are returned in Output area. Sample file created by Peter Atherton.
: xlsx  : No  : 123 kb 
: In Workbook

FN0070 - -- Print out this Excel Function word seach game, and use it as an ice breaker at your next meeting. Everyone loves Excel games!
: xlsx  : No  : 64 kb 
: In Workbook

FN0069 - -- For . 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
: xlsx  : No  : 62 kb 
: In Workbook

FN0068 - -- The RAND function is used to produce sets of 3 unique Bingo cards in this Excel template
: xlsx  : No  : 68 kb 
:

FN0067 - -- Examples show how to use Excel's new spill functions (Microsoft 365), on the worksheet, and in VBA. Thanks to Dermot Balson
: xlsm  : Yes  : 103 kb 
:

FN0066 - -- Enter criteria, and select headings for the columns you want in the filter results. Uses Excel's new dynamic array functions (Microsoft 365).
: xlsx  : No  : 78 kb 
: Notes in file

FN0065 - -- Practise the multiplication tables, one row at a time, or fill in the entire table. Turn error checking on or off.
: xlsx  : No  : 60 kb 
: Notes in file

FN0064 - -- Enter a hex code and see the related emoji, or select an emoji and see its code and description. Thanks to
: xlsx  : No  : 161 kb 
: Notes in file

FN0063 - -- Compare two lists to find new items, using the COUNTIF function.
: xlsx  : No  : 59 kb 
: Notes in file

FN0062 - -- 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.
: xlsx  : No  : 63 kb 
: Notes in file

This video shows the steps for showing or hiding the borders in the weekly planner template.

FN0061 - -- First, set up a translation table, with your messages in English and French. Then, select English or French from drop down list, and workbook text, buttons, and messages show that language. Numbered notes link to lookup table.
: xlsm  : Yes  : 75 kb 
:

FN0060
Use the IF function or conditional formatting with a custom number format, to show your own custom hash errors in Excel. If amount is over budget, cell shows #OVER!
: xlsx  : No  : 55 kb 
: Notes in file

FN0059 -
Press F9 to select random items from 3 lists, to create a scenario. Third list depends on selection in first list.
: xlsx  : No  : 21 kb 
: Notes in file

FN0058 -
Based on a maintenance issue start time, calculate the shift when the issue occurred
: xlsx  : No  : 56kb 
: In the workbook

FN0057 -
Select letters (A, B, C, D) to answer survey questions. Formulas calculate total survey score, and show description based on total
: xlsx  : No  : 58kb 
: In the workbook

FN0056 -
Use the INDIRECT function, with the INDEX and MATCH functions to student grades for each subject. AVERAGE calculates the overall grade
: xlsx  : No  : 64kb 
: In the workbook

FN0055 -
Use INDEX and MATCH functions to find distance from city to city, during a vacation trip, based on a mileage lookup chart for Florida cities
: xlsx  : No  : 55kb 
:

FN0054 -
Use the FIND, LEN and other functions to split and IPv4 address into parts. Then use DEC2HEX function to convert to hex codes for an IPv6 address
: xlsx  : No  : 58kb 
:

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

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

FN0051 -
Select a courier name and country from drop down lists. Weights and rates for those selections are shown in the table. Uses INDIRECT and INDEX functions to pull data from named ranges.
: xlsx  : No  : 55kb  : N/A
: (FILTER function)
:

FN0050 -
A summary of the favourite Excel functions mentioned in Jon Wittwer's survey of Excel experts. List of functions, and pivot table summary of the counts.
: xlsx  : No  : 66kb 
: N/A

FN0049 -
Product pricing table has a list of price changes, with dates. Invoice table pulls the applicable product price, based on selected product and invoice date.
: xlsx  : No  : 54kb 
: N/A

FN0048 -
Enter a list of holiday weeks, regular pickup day, and the type of waste that is collected each week, then print the schedule.
: xlsx  : No  : 62kb 
:

FN0047 -
Count specific codes in a range of cells, where some cells contain multiple codes. For example, count "JK" only where another code is "JKA".
: xlsx  : No  : 56kb 

FN0046 -
Select a language from the drop down list, and the TEXT function uses the correct code to show the weekday name -- "ddd" for English and "jjj" for French..
: xlsx  : No  : 55kb 

FN0045 -
The RAND function is used to produce sets of 3 unique Bingo cards in this Excel template. The CHAR function converts the number to a characters, and the Webdings font is used on the bingo cards, to show the selected characters as pictures.
: .
: xlsx  : No  : 30kb 

FN0040 -
Use the CHOOSE function to assign a score for each option number.
: xlsx  : No  : 18kb 
:

This video shows how to pick answers with Excel Option buttons.

FN0033 - -- Instead of the overall Top 10, show highest items based on current filters, using SUBTOTAL or AGGREGATE.

FN0032 - -- Answer survey questions with option buttons, to find out which Excel function you are.

FN0031 - -- Enter category name and INDEX/MATCH formula finds the last product code in that category, from a sorted list.

FN0030 - -- Select function name and option from drop down list, and totals change to show AGGREGATE for those settings.

FN0029 - -- First, set up a translation table, with messages in English and French. Then, select English or French from drop down list, and workbook text and graphics show that language.
Sample File:
Instructions: .

FN0028 - -- Add check boxes to the worksheet, and link to cells. Use cell results in worksheet formulas.

FN0027 - -- Use INDEX and MATCH functions to show all selected items on a customer invoice.
updated 26-June-13

FN0026 - -- Use INDEX and MATCH functions to find distance between two cities. Conditional formatting highlights selected mileage in lookup table.   Also see

FN0025 - -- Use worksheet formulas to create links and HTML code for Amazon affiliate product links.

FN0024 - -- Use DSUM to calculate totals for a database that is a named Excel Table, for Excel 2007 and later.

FN0023 - -- Find the promo code and discount rate for the selected product, based on the order date.

FN0022 - -- Convert amounts from one measurement unit to another, such as feet to metres. Excel VBA clears cells when new category is selected.

FN0021 - -- 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.

FN0020 - -- Enter maximum hours a driver can work in consecutive days. Conditional formatting highlights overtime hours. Pivot table totals the drivers' hours per calendar week.

FN0019 - -- 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.

FN0018 - -- Calculate target weight (pounds or kilograms) based on BMI, record weekly weight, review results in dashboard.   

FN0017 - -- Automatically create model numbers in a list of items, either with a formula or event code.  

FN0016 - -- 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.

FN0015 - -- Select a weather condition name from dropdown list, and the matching picture appears. Uses dynamic ranges with Offset function. Excel template from Aaron Kinser.

FN0014 - -- Enter tasks and appointments in a worksheet list, and see items for selected week in a calendar layout.

FN0013 - -- Calculate the difference between two scores and use coloured error bars to highlight the differences.

FN0012 - -- uses Vlookup formula to create a dynamic list of completed items.

FN0011 - -- uses ranking to distribute available funds to applicants, based on request amount.

FN0010 - -- uses DSUM and DCOUNT to calculate totals in a database.

FN0009 - -- uses VLookup to create an invoice; a macro prints unmarked items from the list, and marks as printed.
Excel 2007 and later:  
Excel 2003:    

FN0008 - -- uses VLookup to create an invoice for the marked item in a list; a macro clears old marks.  

FN0007 - -- Extract items from a list to a new workbook, with formulas intact; contains a macro.  

FN0006 - -- Use formulas to extract unique items from a list. Based on an example from Paul Cumbers.  

FN0005 - -- Enter forecast and actual amounts; summary sheet shows totals year to date; no macros - hyperlinks used for navigation. zipped file;  

FN0004 - -- uses a combo box and the Index function to extract an address from a list. 24 kb;  

FN0003 - -- The RAND function is used to produce sets of 3 unique Bingo cards in this Excel template.
:
Also see the above.

FN0002 - -- enter today's date; create list of upcoming Wednesdays/Saturdays 

FN0001 - -- enter steps walked each day in this Excel template; set thresholds, keep track of days thresholds reached  

SC0002 - -- Excel Scenarios are limited to 32 changing cells. The method in this Excel template could use as many variables as columns.

SC0001 - -- In this Excel template data validation creates dropdown lists, Scenarios store variables, macro automates the scenario display.

CM0003 - -- List the picture file names in column A, then run a macro to insert comments with those pictures in Column B.

This video shows how to add a picture to an Excel comment, manually.

CM0002 - -- 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.

CM0001 - -- Use macros to number comments for printing. List numbered comments on a separate sheet.