Make SOLIDWORKS Inspection Reports Easier to Read with Conditional Formatting

In Excel, Conditional Formatting can be used to customize a SOLIDWORKS Inspection report template to highlight values that are within the determined specifications with a certain color (typically green). This formatting can also show values that fall out of the specified range in red or another color. 

Related >> SOLIDWORKS Inspection GD&T Fonts

As shown below on the left, the default formatting for the AS9102 template changes the font color of each cell in the Results column, which can be hard to read. To make your results a little more distinguishable, I prefer to have the entire cell background highlight to show whether my results are within my tolerance limits (shown below on the right report).

You can create a new template by adding the desired tokens or modify an existing template located in C:\ProgramData\SOLIDWORKS\SOLIDWORKS Inspection 20XX Add-in or Standalone)\Templates. 

Note: The template files are slightly different between the Inspection add-in and the Inspection Standalone version so make copies before editing the template files.  

Three rules will be added to show pass, fail, and blank results fields. 

In Excel, select the proper cell for the results then select the Home tab > Conditional Formatting > New Rule… to add a new rule. 

Select Format only cells that contain then select the two cells for the upper and lower limits and select between for the rule description. 

Note: This customized report example uses G9 and H9 for the Upper and Lower Limit Cells (yours might be different cells).

When you select a cell, the “$” defines an absolute cell reference. Meaning that if you move cells, it will always look for the same cell in the original location. When you remove one of the “$” it will create a mixed cell reference. If the row has the “$”, it will allow the row to adjust. 

And if the column has the “$”, it will allow the column to adjust. 

Remove the second “$” sign (marked above in a red square) in each of these two cells to make the rule works for all the rows that will be added below it for all the required characteristics. 

Select Format… to change the cell format. 

The example above shows the cell background will be filled in green if the inspection result value falls in between the upper and lower limits. 

These same steps can be followed above to make the failing results show a red cell background. The only difference will be that the rule description will be 'not between'. Still remove the second "$" in each results cell to get the formatting to work correctly as shown below. 

Blank Cell  

If you only use these two rules, you’ll notice that your template and report form will have cells automatically set to red because the value is not between the upper and lower limits.

You’ll need to add in a new rule for blank cells, so they don’t show up as out of specification until you input a value.

Select Format only cells that contain, select Blank from the dropdown list, and leave the Format set as No Format Set.  

If you don’t set up the rule for a blank value in a cell, all blank cells will show up with a red background. 

Rules are solved from Top to Bottom

To see all your existing rules, select a cell, then select Home > Conditional Formatting > Manage Rules. Move the blank rule up to the top using the arrows shown below.

If the blank rule is left at the bottom of the list, all blank values will have a red cell background.

Can Pass or Fail text be highlighted not just values?

These same rules can be applied to text string values, not just numbers. Pass/Fail or go/“no go” can also be used in the report with the background colors. The rules are shown below using “equal to” instead of between or “not between”. 

The rules above provide the results below.

Once you’ve added your conditional formatting rules, make sure you save this Excel document as an Excel template (.xlt, .xltx, or .xltm) since SOLIDWORKS Inspection only recognizes templates.

To learn more about customizing SOLIDWORKS Inspection Report Templates visit our video library.  

Expand Your SOLIDWORKS Skillset

Easily Save SOLIDWORKS Configurations as Separate Parts

Linking a Custom Cut List Property to a SOLIDWORKS Drawing Balloon

SOLIDWORKS Warning: A Journal File Could Not Be Created

How to Download SOLIDWORKS Block Libraries

SOLIDWORKS Thumbnail Previews