A highly valuable Excel skill is being able to create dynamic sub-lists, custom reports, or chart data from a larger master Excel list or database. Use these techniques to create dynamic sub-lists and custom reports that will match almost any report writer. Add a simple macro and you can automate work that would take hours.
The figure below shows the master list used in this dynamic list and custom report tutorial. It has unique headings across one row at the top and all rows have data to the bottom of the list. The rows do not have to be completely filled as long as all regions of the list have some filled cells that are edge to edge.
The master list is in one worksheet. The master list is where you will insert or delete rows (records) or edit existing data.
Extracted or sub-lists will be on separate worksheets. The extracted lists on separate worksheets are dynamic, so edits or changes in them will disappear when they are next updated. In this tutorial these sub-list worksheet tabs are named Region and Product.
Notice the Region worksheet above has a Criteria range in D5:D6. That controls which data is extracted below the headings in D8:F8.
The Region sub-list worksheet, shown above, contains a sub-list of data extracted for a specific criteria. It is setup with exactly the same headings as the master. (You can rearrange the extract columns by rearranging the headings.) The extracted data will appear below the headings.
Custom Excel Report Tip 1
Your sub-list or custom Excel report does not have to match the column layout of the original master list. You can delete or move headings in the sub-list to create the report you want. If a heading is missing that column of data is left out. If a heading is moved to a new position its matching data appears below it.
Custom Excel Report Tip 2
Use wildcards like * and ? in the criteria to specify special criteria. For example, a Product criteria of
will select any product name that begins with “Laser”.
Custom Excel Report Tip 3
You can have more than one heading and criteria in the Criteria range. For example, you could have Region and Product as criteria headings and West and Laser Heavy in the cells underneath those headings. Those criteria would extract all data that were both in the West region AND Laser Heavy products.
You can have as many sub-lists (custom Excel reports) and the criteria ranges that control them as you want. They can be on the same sheet as the master list or on separate worksheets. Just don’t put one above another or the one below will be erased when the one above updates.
To extract or refresh a dynamic sub-list follow these steps,
1. Enter a criteria in the Criteria region. For example, enter West in cell D6. Make sure you type the criteria exactly as it is in the master list.
If you leave a Criteria cell blank, all data will match so everything under that heading will be allowed. In this case because there is only one criteria heading everything from the master list will be retrieved.
2. Select a blank cell on the worksheet where you want the dynamic sub-list or Excel custom report. Make sure your cell selection does not contain or touch a filled area that Excel could mistake for a list.
Alert: You must start with a cell selected on the sheet you want to receive the extracted data. If you start on the master list worksheet, then Excel expects you to be creating a sub-list on the master list worksheet. You can do that, just be aware of what is expected.
3. On the Data tab in the ribbon, in the Sort & Filter group, select Advanced. The Advanced Filter dialog box appears. An Advanced Filter enables you to filter and copy a list.
4. Select the Copy to another location option button. This indicates you will copy from the List range to the Copy to range.
5. Click the List Range selection tool and switch to the worksheet containing the master list. Select all of the master list. In the example that is ‘Master List’!$D$5:$F$41. Press Enter to return to the dialog box.
If you have a very large master list, click in the top left corner and press Shift+Ctrl+Down arrow to select down. Continue holding the Shift+Ctrl and press Shift+Ctrl+Right arrow. The selection will travel to the bottom, then travel right while selecting.
A good solution for databases that expand or contract (most do) is to either select a lot of extra rows at the bottom to allow for later additions or to use a dynamic range name that automatically adjusts for the changing height of the master list.
6. Click in the Criteria range and select the criteria range for the sub-list, Region!$D$5:$D$6.
7. Click in the Copy to and select the headings for which you want data extracted, Region!$D$8:$F$8. Your dialog box should now look like this,
8. If your master list contains duplicate records, select the Unique Records Only check box. This happens most frequently if you are not extracting all columns in a database. If you do not extract the column that makes each row unique, for example, a part number, then you may end up with multiple identical records if this checkbox is not selected.
9. Click Ok to extract all data from the master list and fill it in below the headings on the Region sub-list.
Your extracted sub-list for the West region will look like this,
Any data below the headings will be deleted and replaced by the new sub-list data that matches the criteria.
While this works pretty well, Excel makes you reenter the range for the master list each time you run the Advanced Filter. It does this to force you to make sure that you have selected the entire master list. You can fix that using the technique that follows.
Make it Fool Proof to Select the Master Excel List
Correctly using range names in Excel is a valuable skill that separates beginners from power users. The MOST valuable range name skill is knowing how to create dynamic range names that expand and contract as a list changes size. If you use a dynamic range name that automatically adjusts as the master list size changes you can type the range name in the Advanced Filter range box or record a macro with that range name.
Once you start using dynamic range names you can stop worrying about the master list getting too long.
Alert: Do not use the range names Criteria and Extract. Excel reserves these range names for use with list filtering. Excel reassigns these names each time the Advanced Filter is used. Instead, create your range name with a structure like rngCriteriaRegion.
Create Custom Excel Reports and Dynamic Sub-Lists Using a Simple Recorded Excel Macro
Recording macros is an excellent way to step gently into the world of Visual Basic for Applications, the programming language inside Excel. You don’t have to be a programmer to record macros and they make your repetitive tasks far more efficient.
If you have not recorded or created a macro before you will need to add the Developer’s tab to the ribbon.
If you have created range names to use with your recorded macro, write down the range names before you begin recording.
To record a macro that updates the sub-list,
1. Enter criteria in the blank Criteria cell. (This criteria is not recorded by the macro.)
2. On the Developer tab in the ribbon, in the Code group, click Record Macro.
3. Enter a name for your macro. Use only letters with no spaces, for example, SubListRegion. Type a shortcut key if you wish. The shortcut key will overwrite Excel’s built-in shortcut keys. Click Ok.
Excel is now recording code that will reproduce what you do.
4. Start at step 2 in the instructions above and go to step 9. If you have previously created range names for the Criteria, Extract, or master list range, then type the range names in the Advanced Filter dialog box.
5. On the Developer tab in the ribbon, in the Code group, click Stop Macro.
You can now rerun this macro pressing the shortcut key you assigned, by selecting the macro name from the Macros tool on the Developer tab, or by clicking a button like that described in the next section.
Create a Fully Automated Awesome Dynamic Sub-List Excel Custom Report Generator!
If you create a lot of sub-lists or custom reports or dynamic charts using data extracted from a master list, then LEARN HOW TO DO THIS. You’ll save yourself a ton of time.
Here’s what I’ve done to create a fully automated dynamic sub-list Excel report generator on the Product page. And it’s ALL DONE WITH RECORDINGS. The Product example worksheet looks like this,
If you are going to record a macro I HIGHLY recommend assigning range names to the cell ranges you will need to record a sub-list update macro. I have assigned these range names,
rngCriteriaProduct on Product worksheet $D$5:$D$6
rngExtractProduct on Product worksheet $D$8:$F$8
rngMasterListStatic on Master List worksheet $D$5:$F$51
Notice that the rngMasterListStatic has an extra 10 blank rows at the end. These blank rows can be used if someone adds data to the bottom of the list. If they insert or delete rows through the middle of the list the range name will automatically adjust. (A more elegant way to solve this is to create a dynamic range name for the master list that adjusts as the master list changes size.)
The recorded macro on the Product worksheet is assigned to a button by right-clicking the button (or any artwork), choosing Assign Macro, and then selecting the macro you want to run.
Click the button and the Product sub-list updates to match the criteria you have entered. Here’s the recorded macro. You can see how the range names are easier to read than cell references.
' SubListProduct Macro
Range("rngMasterListStatic").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("rngCriteriaProduct"), CopyToRange:=Range( _
Automate the Advanced Filter Criteria Selection
Make it even easier for users to enter criteria (without typing mistakes) by creating a drop-down list that contains allowable criteria. Their selection from the drop-down list goes into the Criteria range.
Create Custom Reports Using the Advanced Filter
Rearrange columns in your extract list heading in any order you want. Excel’s extracted data fills in below the matching heading. So if you want a report with columns in a special order, put the headings in that order. Heading names must be spelled exactly as they appear in the master list.
Delete extract heading names of columns you do not want in your sub-list or custom report.
Create conditional criteria with AND by using multiple adjacent headings at the top of the criteria range.
Extract all data for a list heading by entering no criteria under that heading.
Don’t Put Data Below the Extract Range
Any data below the headings at the top of the extract range will be deleted. This includes formulas, old sub-lists, etc.
- 1 Make it Fool Proof to Select the Master Excel List
- 2 Create Custom Excel Reports and Dynamic Sub-Lists Using a Simple Recorded Excel Macro
- 3 Create a Fully Automated Awesome Dynamic Sub-List Excel Custom Report Generator!
- 4 Automate the Advanced Filter Criteria Selection
- 5 Create Custom Reports Using the Advanced Filter
- 6 Don’t Put Data Below the Extract Range