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.
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.
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.
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.
About Maurice Cherian
Maurice Cherian is Technical Support Engineer at GoEngineer. He was introduced to CAD while working for the Navy. Maurice has over seventeen years of Software Training and overall CAD experience. He has been using SOLIDWORKS since 2008 and is a certified expert and instructor. He provides SOLIDWORKS training and Tech Support. Maurice is active in the SOLIDWORKS User Group Community in San Diego, presenting and hosting multiple times over the last eleven years.
Get the latest articles delivered daily to your inbox, unsubscribe at any time.