FN - Excel Multiplication Tables -- Practise the multiplication tables, one row at a time, or fill in the entire table. Turn error checking on or off. FN - 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. FN - 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. Numbered notes link to lookup table. If amount is over budget, cell shows OVER! Third list depends on selection in first list. Formulas calculate total survey score, and show description based on total Format : xlsx Macros : No Size : 58kb Excel File: sumproductcodevalues.
Or, check that at least 3 items are in a cell, such as a street address, " Maple St". 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. Weights and rates for those selections are shown in the table. List of functions, and pivot table summary of the counts. Invoice table pulls the applicable product price, based on selected product and invoice date. FN - Waste Collection Schedule Enter a list of holiday weeks, regular pickup day, and the type of waste that is collected each week, then print the schedule.
FN - Count Specific Codes in a List Count specific codes in a range of cells, where some cells contain multiple codes. The CHAR function converts the number to a characters, and the Webdings font is used on the bingo cards, to show the selected characters as pictures. Based on the original Bingo cards file below, that uses numbers.
Use cell results in worksheet formulas. Conditional formatting highlights selected mileage in lookup table. Excel VBA clears cells when new category is selected. Excel formulas total the time per task, and overall total time. UserName is a lookup from UserCode list. Conditional formatting highlights overtime hours.
Pivot table totals the drivers' hours per calendar week. FN - 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. FN - Create Model Numbers -- Automatically create model numbers in a list of items, either with a formula or event code.
FN - 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. Treatment Calendar. FN - 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. FN - Task List and Calendar -- Enter tasks and appointments in a worksheet list, and see items for selected week in a calendar layout.
FN - Coloured Error Bars -- Calculate the difference between two scores and use coloured error bars to highlight the differences. FN - Dynamic List -- uses Vlookup formula to create a dynamic list of completed items. FN - Grant Distribution -- uses ranking to distribute available funds to applicants, based on request amount.
FN - Print Unmarked Invoices -- uses VLookup to create an invoice; a macro prints unmarked items from the list, and marks as printed. Excel and later: InvPrintMark. FN - Invoice for Marked Item -- uses VLookup to create an invoice for the marked item in a list; a macro clears old marks. FN - Extract Items with Formulas -- Extract items from a list to a new workbook, with formulas intact; contains a macro.
Based on an example from Paul Cumbers. FN - Forecast vs Actual -- Enter forecast and actual amounts; summary sheet shows totals year to date; no macros - hyperlinks used for navigation. FN - ComboBox Selection -- uses a combo box and the Index function to extract an address from a list. FN - Daily Walking Record -- enter steps walked each day in this Excel template; set thresholds, keep track of days thresholds reached WalkTrack.
The method in this Excel template could use as many variables as columns. SC - Model Pricing Scenario -- In this Excel template data validation creates dropdown lists, Scenarios store variables, macro automates the scenario display. CM - Display Comment Text -- Instead of pop-up comments, use programming to display comment description in a cell at the top of the worksheet, when a cell with comment code is selected. List numbered comments on a separate sheet.
Due to Windows security settings, files that you get from the internet might not work correctly, unless you unblock them, or store them in a Trusted Location. If you have comments or suggestions, please contact [email protected] 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. Toggle navigation Home. Tips Files Products Newsletter Blog. Sample Excel Files Free Excel workbooks that you can get, to see how functions, macros, and other features work.
DV - 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.
DV - 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. It can show a long message about the cell's data validation. Use the scroll bars, if necessary, to see more of the message. Then, click OK, to add all selected items to the cell. Lookup lists are in named ranges on different sheet.
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. DV - 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. DV - 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. DV - 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. DV - 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. DV - Dependent List From Row Items -- Select an employee name from the first drop down, second drop down shows all skills for that employee. In PowerPoint, run macro to put those names on individual slides, based on master slide.
Use all names or test for criteria. UF - 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.
UF - 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. 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. UF - 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.
Different caption and macro for 40 or below. UF - Personalized Error Message Show a personalized error message with user name from network or application, when budget total is above the limit.
Macro adds new sheet to workbook, with list of subfolders in the main folder, and file counts for all folders. 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. Type single-digit score, and next hole's box is automatically selected. Click OK to add scores to worksheet. UF - 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. UF - 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. UF - Hyperlinks Run Command Files Use hyperlinks and a background macro, to run command or script files from Excel, with a single click.
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. 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. Choose All to see all the worksheets. UF - Show Specific Sheets Select a sheet type from the drop down list, and only sheets with that text in their name are visible.
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. 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.
There is a simple formula on each window, instead of a number -- create your own formulas, or change to numbers, if you prefer. UF - 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.
After printing, the font size is reset.. UF - 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. Or, run macro for colour info in message.
CF - 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 Click check box to see scary or friendly pumpkin picture. Formulas change messages, no macros. CF - 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. Hockey - Data Analysis : Click here to get the hockey data file, with pivot tables and pivot charts , analyzing the data.
Here are a few of my Contextures tutorials on working with data in Excel, organizing it in tables, and summarizing the data in pivot tables. For video-based lessons on working with Excel data, I recommend these highly-rated courses, offered by Mynda Treacy. These are affiliate links, and I will earn a small commission if you purchase a product through these links.
Toggle navigation Home. Tips Files Products Newsletter Blog. Excel Sample Data This page has Excel sample data that you can freely use for testing, Excel training and demos, and other educational purposes. More Sample Files. You can download it directly: Financial Sample Excel workbook. You can also download it from Power BI Desktop.
Select Try a sample dataset from the yellow welcome screen or from the blank canvas. If you've landed on this tutorial from the sample dataset dialog box, go ahead and choose Load data.
More questions? Try the Power BI Community. Skip to main content.
0コメント