Using Excel filters on an Excel BOM with thumbnails    

SOLIDWORKS 2019 introduced a new option to include a thumbnail of each component when you export a Bill Of Materials to Microsoft Excel. I noticed that a lot of SOLIDWORKS customers are using this Excel BOM to group their components with the filter functionality in Excel. Unfortunately this did not work as expected, because the thumbnails don’t move along with the filter action – they have a fixed position. In this tech blog I want to tell you more about a solution to use the filter functionality together with the new thumbnail option.

Firstly, create an Excel BOM with thumbnails:

  1. Right-click a BOM in SOLIDWORKS and click Save As.
  2. In the dialog box:
    1. For Save as type, select one of the following:
      • Excel 2007 (*.xlsx)
      • Excel (*.xls)
    2. Select Thumbnails.
    3. Browse to a folder, enter a file name, and click Save.

The next step is to create a filter in Microsoft Excel to group components based on part number. In this example this will mimic the difference between purchase parts and make parts. And of course I want to ensure that the thumbnails move along.

Follow the next steps to achieve the desired result:

  1. Open the Excel BOM with Microsoft Excel.
  2. Go to the Review tab in the Ribbon and click Unshare Workbook.
    This makes it possible to edit the thumbnails.
  1. Press F5. This will open the Go To dialog box.
  2. Select Special in the Go To dialog box.
  1. In the Go To Special dialog box, select Objects and click OK.
    This will automatically select all the thumbnails in the Excel BOM.
  1. Right click one of the thumbnails and select Size and Properties.
  2. The Format Picture menu appears. Go to Properties and select Move but don’t size with cells.

    This ensures that the thumbnails will be connected to the part number after applying the filter action.
  1. Select all columns of the Excel BOM (column A, B, C, D and E in the first image).
  2. Go to the Data tab in the Ribbon and select Filter.
  3. Now the Number column can be sorted by selecting Sort A to Z.


  1. The thumbnails move along with the sort action.


SOLIDWORKS 2019 introduced a great way to communicate your bill of materials with other people inside or outside your organisation. The visualization with thumbnails introduces more clarity for everyone. But on the other side a lot of users struggle with the filter tool in Microsoft Excel to sort their BOM along with the thumbnails. I hope this tech blog clarified this workflow, so your BOM output is better then before.


Author: Martijn Visser, Elite Application Engineer, CAD2M

CAD2M is certified reseller of SOLIDWORKS, SolidCAM, DriveWorks and our private label dddrop 3D printer. The CAD2M approach integrates this range of products into an all-in-one solution that covers the complete product development process. Take the full advantage of working in 3D with our advice, training and expertise. For more information, visit