How to Show 12 or 24 Months in an Excel Table Based on a Drop-Down
Question details
The user wants to dynamically hide or show rows in an Excel table representing 13 to 24 months, depending on whether "12" or "24" is selected in a drop-down list.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic report or dashboard where the time horizon (12 or 24 months) can be instantly toggled via a drop-down menu.
- Observed behavior
- The user needs rows containing months 13 through 24 to automatically disappear from the table when "12" is selected, preferably causing the charts below the data to move up.
Ensure your drop-down list is properly set up in a specific cell (e.g., H1) using Data Validation, and verify your data table is structured with month identifiers (1 to 24) in a clear, continuous column.
Use a Worksheet Event Macro to Automatically Hide Rows
A VBA macro can automatically hide rows 13-24 when "12" is selected from the drop-down, providing a seamless and automated experience that shifts content below the table upward.
This method requires using a Worksheet_Change event. The code listens for a change in your specific drop-down cell and automatically adjusts the row visibility. Note that VBA does not work in Excel for the Web.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left, double-click the name of the worksheet that contains your drop-down list (e.g., Sheet1).
In the code window, paste the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$1" Then Range("18:29").EntireRow.Hidden = (Target.Value = 12) End If End Sub. Adjust "$H$1" to your drop-down cell and "18:29" to the actual rows containing months 13-24.
Close the VBA Editor. Go to File > Save As, and save your workbook as an Excel Macro-Enabled Workbook (.xlsm).
Use the FILTER Function for a Dynamic Display
If you prefer a code-free approach and do not want to hide rows in your original data, use the FILTER function to generate a new, dynamic table based on the drop-down selection.
Apply Regular Table Filters
A simpler alternative is to use built-in Excel table filters to manually show months less than or equal to the desired value.
Easily Create Dynamic Tables and Use Macros in WPS Spreadsheet
WPS Spreadsheet offers full support for advanced data manipulation, including VBA macros and dynamic array functions like FILTER. You can effortlessly manage your 12-month and 24-month reports within a familiar, lightweight, and highly compatible interface.
- 1. Open your Workbook in WPS Spreadsheet: Launch WPS Office and open your existing .xlsx or .xlsm file containing the data and drop-down list.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab and click on 'VBA Editor' to seamlessly insert your Worksheet_Change macro code.
- 3. Utilize Dynamic Functions: Alternatively, type the =FILTER() function into any cell to dynamically extract and display your 12 or 24 months of data without altering the original table.
Frequently Asked Questions
Why is my VBA macro not running when I change the drop-down value?
Ensure that macros are enabled in your Trust Center settings. Additionally, verify that the macro is placed in the specific Worksheet module (not a standard module or ThisWorkbook) and that the Target.Address matches your drop-down cell exactly, formatted as an absolute reference (e.g., "$H$1").
Can I use the FILTER function in older versions of Excel?
The FILTER function is a dynamic array function available only in Microsoft 365, Excel 2021, and modern WPS Office versions. If you are using an older version, you must use VBA macros, manual table filters, or complex INDEX/MATCH formulas instead.
Will hiding rows using VBA affect charts placed below the table?
Yes, hiding rows will typically shift elements located below those rows upward. To ensure this happens smoothly, right-click your chart, select 'Format Chart Area', go to 'Properties', and verify that the setting 'Move and size with cells' or 'Move but don't size with cells' is selected.
