The fact that Plant 3D allows you to export information from your Data Manager to an Excel spreadsheet isn’t any kind of secret. This is something that many people are relieved to hear, because this allows us to make edits in Excel and then import the spreadsheet back into the Data Manager. Parts in the Spec Editor may also be exported to Excel. This feature has the potential to save a tremendous amount of time, but only if we can actually use Excel.
I have some experience in using Excel, and I’m willing to share some of what I know. We’ll start with some simple features that I find most useful.
First let’s go over how to get the size of our columns to fit the contents:
Notice that each cell is identified by the Letter along the top of the sheet and a Number along the left side of the sheet. If you select a letter or a number you will select that entire row or column, but if you select the triangle shape to the left of the letters and above the numbers as shown below you will select all cells in the sheet.
With all cells selected (using the method I just explained) in the Home tab from the Cells panel select Format. In the drop down menu select AutoFit Column Width.
If nothing else this will make it easier for you to be able to read over the information and start making the necessary changes.
For my next tip I’ll start with a blank sheet, and let’s say I want to fill the first column with all the same information. “Ball Valve” for example. I could just manually type Ball Valve in every row of that column… or I could do this trick:
Select the first cell and insert the value.
While the cell is selected hover your mouse over the small black box bottom right corner of the cell you just filled out, and you should see your cursor turn into a black cross shape.
Hold the left mouse button down and drag your mouse down to select all the cells that need this value.
Upon releasing your left click all selected cells should all have the same value as the original cell. This is called Auto Fill.
Here is what happens if you start by selecting two cells with different values:
The next topic that I’d like to mention is using filters. Before you can apply a filter you must “Unprotect” the sheet. To unprotect the sheet have the file open and select File be sure Info is highlighted and under Permissions select Unprotect for each sheet you wish to filter. You can also right-click on a worksheet tab and un-protect sheets.
Now you can apply filters in the Data tab from the Sort & Filter panel. Just use the Filter tool to create a generic filter that should be enough to work with for now, but know that advanced filters are available.
Notice that once the filter is applied row 1 now has drop down arrows. These will allow you to select how you want to sort the rows based off of the contents of one specific column. This also gives you the option of filtering out rows with a specific value for this column.
While the filter should work great for words, you may find that if you try sorting your components by size may stumble on some unexpected results.
After sorting Nominal Diameter 10 appears between 1.5 and 2. There is a perfectly good explanation for this: Notice the little green triangles in the corner of each cell. These triangles indicate that the cell consists of only a number, but is being treated as text. When Excel sorts numbers alphabetically the order will be based on the order of the individual characters and not the number’s value. To fix this highlight the cells you wish to convert to numbers and click on the yellow diamond that appears to the left of the selected cells.
Once you select the yellow diamond, select Convert to Number from the drop down menu. Now the numbers should sort numerically instead of alphabetically.
Most of you probably already know about functions in excel where you identify a specific cells and state how they interact to create a value.
For this example in A1 I will input this value:
I know it’s not the most practical function, but most of the basic concepts are used such as addition, multiplication, and an if statement. Regardless of how practical or impractical it is, here are the values I get if I Auto Fill this function for the values after A1 using these numbers:
Notice that the function uses cells from a relative location, meaning that as you use auto fill the new function will use cells with the same relative location. So A2 uses B2 and C2 and A3 uses B3 and C3, etc.
Another function that you might find useful is Concatenate. This will combine cells and text, stringing them together in the order you identify them.
For this example in A1 I will input this value:
=CONCATENATE(B1, ” “, C1)
Notice the the second text is a space, if I did not add that segment the text for A1 would be 4″Globe Valve with no space between 4″ and Globe.
These are only a few of the tools that Excel adds, making the ability to export and import from Plant 3D that much more valuable.