SOLIDWORKS Design Tables 4 Tips to “Excel” your Designs

Excel Design Tables in SOLIDWORKS

SOLIDWORKS design tables can be incredibly powerful tools for automating and creating configurations. Even the most basic design tables have the potential to save designers hours or even days of tedious modeling. As you probably know, a design table in SOLIDWORKS is just an embedded Microsoft Excel table. This is important because it means that you can utilize all Excel functionality while entering and controlling your parameters. In this post, I’m going to show you a few ways that you can utilize the power of Microsoft Excel functions in your design tables.

Concatenate

The first function I’m going to talk about is CONCAT or CONCATENATE. The function CONCATENATE allows you to combine the values from multiple cells into one, singular cell. You might be asking yourself “Why would this be useful in a design table?” Well, in SOLIDWORKS, you can configure more than just dimensions or features. In fact, there are more than 30 configurable parameters that you can control, one of them being Custom Properties. An awesome use of the CONCATENATE function in Excel would be to create a dynamic description of your parts based on the configured parameters. Defining these parameters in this manner allows you to automate the description and name of your configurations. It also reduces the risk of errors or typos. The images below show a simple example of what this may look like inside of Excel.

Excel Function for Dynamic Description
Results of the Dynamic Description

If Statements

Another powerful function inside excel is the IF Function. The IF function allows you to run a logical test (A1 > 1, A1 = 1, etc.) and it returns a specific value if it is true and a different one if it’s false. I created two examples of when you might use this in a design table to show some of its capabilities.

For the first example, we are driving the diameter of a hole based off the length of the part. We want the diameter to equal 0.5 when the length is less than 24. If the length is not less than 24, then we want the diameter to be 1. We’ll use an IF statement to achieve this. Below is what this would look like in Excel, along with a breakdown of the logic used.

BREAKDOWN OF THE LOGIC:

IF the “Length” < 24

THEN the “Diameter” = 0.5

ELSEIF the “Diameter” = 1

Excel function showing one IF Statement

For our second example, we want to drive the spacing of our patterned holes based on a logical test like the previous example, but this time we want to have three potential outcomes. See below for a better visualization of our design guidelines.

There are many ways of approaching this in Excel, but I find the easiest way is to use two IF Functions; one “nested” inside of the other. Doing this will allow us to check for more than two conditions, which is exactly what we need. Below is what this may look like inside of Excel along with the breakdown of the logic I used.

BREAKDOWN OF OUR LOGIC

IF the “Length” < 20

THEN the “Spacing” = 5 * “Diameter”

ELSEIF the “Length” < 30

THEN the “Spacing” = 8 * “Diameter”

ELSEIF the “Spacing” = 10 * “Diameter”

Excel Function showing a “nested” IF statement

These two examples are just the beginning of what IF statements and logical functions can achieve. If you plan to utilize logical conditions similar to the ones I used or even more complex, I recommend you familiarize yourself with the other logic functions that Excel offers (AND, OR, etc.). This will allow you to capture your design intent more efficiently and open the possibilities for what you can create with your design tables.

Data Validation

The next Excel functionality that you can utilize is Data Validation. Sometimes, the parameters that you are configuring need to be restricted to a specific list of values. For example, if you are using the standard custom property EngineeringApproval, you may want to restrict the value of the cell to be one of your Engineering Managers. Data Validation allows you to restrict the value of a cell by creating a convenient dropdown list of the allowable values. For this example, we will configure a Custom Property called “Paint Color”. We want to restrict this value to the available paints we have in house. We already have a table of each available color, along with its RGB values. We’ll simply copy this data range into the design table.

Partial view of the table of RGB values

To create the drop down, we will select the cells that we’d like to restrict and then choose data validation.

Steps for launching Data Validation

Then, the Data Validation window appears. Here, we want to choose to List from the Drop down, and we want to select the range of colors as our source.

Inputs for the Data Validation Window

Now, the cells from the list show up in a selectable, dropdown format. You also will not be able to enter values that are not on this list.

Selectable Drop-Down List for Paint Color
Error Message if input value is not part of the Drop-Down list

Data Validation is a useful way to make your design tables fool proof when creating your configurations and can be used for any list of data. Another example not shown here would be to restrict dimensions to match materials that you have available.

VLOOKUP

The last Excel function that I am going to mention is the VLOOKUP function. This function is very useful for retrieving values from a table. The VLOOKUP function requires the following 3 major inputs:

  1. The value that you want to look for in the FIRST column of a table. It’s important that this value is in the first column.
  2. The table that you are retrieving values from.
  3. Which column number, counted from left to right, is the value that you want to retrieve.

Let’s look at our example. We are going to use the VLOOKUP function to configure the color applied to each configuration. Before we can do this though, we will have to convert our RGB values into the 32-bit integer value that SOLIDWORKS uses. This is done with a simple conversion that can be found on the SOLIDWORKS help page. I’ve added this as a 5th column in our table.

Example list to use VLOOKUP to find correct Color Integer

Now, we are ready to add our VLOOKUP Function. Our inputs will be as follows:

  1. C3 – The value of our Paint Color Property
  2. Colors!A2:E141 – The table of RGB Values for our available colors, including the 32-bit integer column
  3. 5 – The column that holds the integer value

Below is what this looks like in Excel.

Using VLOOKUP Inside of an Excel Table to Drive Designs in SOLIDWORKS

The VLOOKUP function is probably one of the most powerful functions in Excel, but it can be daunting for those unfamiliar with Excel. Hopefully, the description and example helped demystify the VLOOKUP function.

I did not show every single Excel function that you can use in your design tables, but I hope that the ones I highlighted showcase the versatility and power that design tables can have. If you want to learn more about the capabilities within SOLIDWORKS, check out our Resource Library for more posts!