Excel: Filter on column merged downwards

Question

I have an excel spreadsheet with 3 columns. The first column is merged downwards to be the length of the other cells and contains a document name. The other 2 columns contain an id and some text respectively.

The spreadsheet ultimately will contain extracts from about 100 documents and I would like to be able to filter by document to see the document specific rows, however when I try to set up filtering, I can only return the first row per document name.

Is there any other way to accomplish this task? I tried using grouping however I didn't really understand it.

Answer 1

In Excel, merging multiple cells into one cell will keep the upper-left most data only. For example, if you merge the range A2:A6, only A2 will keep its value and A3:A6 will be blank. So, merged cells will be troublesome in your case. Instead, you can unmerge the cells in your first column, fill them with the related values (document names), and then use Filtering. Moreover, you can also use Pivot Tables (Insert > Pivot Table). Drag your first column's header (Document name) to Row Labels (in the Pivot Table Field List), and other column headers to Column Labels. You can change the design of your Pivot Table in whatever way you like.