As specified in the TM1 Users Guide:
Active Forms let you view and update live TM1 cube data directly in Excel whenever you are connected to the TM1 server on which the cube data resides. Active Forms retain the ability to expand and collapse row dimension consolidations in a TM1 view while allowing you to use native Excel features and functions to create complex reports.
Another place where drill-through works—other than the TM1 Cube Viewer and a sliced worksheet—is an Active Form. You can create your Active Form, using either of the following methods:
The Active Form is created in a new empty Excel worksheet and of course, right-clicking on your drill rule area definition will result in TM1 drilling the related data into a new worksheet within your workbook.