Some Stuff ANSYS Users Should Know about Excel

Categories:
Tags: , , ,

imageWhat is the software tool that we numerical simulation types use almost as much as ANSYS products, maybe even more?  Most of you will answer Microsoft Excel.  We all use it almost every day for a variety of things. Every time I see someone doing something sophisticated with Excel, I learn something new, a tool I can use to be more efficient. 

For this week’s The Focus posting, I will be sharing some stuff in Excel, tips, and tricks, that ANSYS users should find useful.  I am using Microsoft Excel 2010, and the assumption is that the reader is a good user of Excel, maybe not an expert, but good.  I have tried to pick things that have a direct impact on user efficiency.  You may already know some or even most of these things, but hopefully, you will find some of it useful.  If you have something to share, please add it to the comments.

Take the Time to Setup Tables

I love tables.  I’m always getting made fun of because I always convert what I’m working on into tables.  Why are they so great? 

  • They auto-format
  • They have filtering built in
  • You can refer to the table, columns, rows, and cells in equations with names rather than ranges
  • When you add a formula in a column, it automatically copies it to the whole table (my favorite)
  • It does automatic totals, averages, etc…

Making a table is easy:

  1. Select the columns you want in your table
    1. It is a good idea to have the headers defined
  2. Go to the Insert Tab
  3. Click on Table
image

That give you:

image

Click on the downward-facing triangle icons to filter.  Use the options in the Table Tools > Design tab to set the name, remove duplicates, turn on the total row, and change the basic formatting (color).  Once you have played with these for a while, you will find you can not live without them, and people will ask you why you use tables so often.

Concatenation

One of the ways that we use Excel is to convert some sort of text data in row/column form into a command, mostly MAPDL commands.  A key to this is the ability to concatenate text strings and the values of cells.  I’ve even seen someone write a NASTRAN to ANSYS translator in Excel.

To do so you create a formula (start with =) and string together the text you want with ampersands: &

As an example, if we want to add a column to the table we used above to create N commands, we simply click on any of the cells in the empty column next to our table and enter:

=”n, “&[@N]&”, “&[@X]&”, “&[@[Y ]]&”, “&[@Z]

Because we are using a table, the command uses the column reference [@name] from the tables rather than cells.  In a non table the command would look like:

=”n, “&$A6&”, “&$B6&”, “&$C6&”, “&$D6

Either way, you are stringing the values in your cells together with text to make a command:

image

That column can be pasted into a text file, an ANSYS Mechanical code snippet window, or saved to a file.

Text to Columns

After tables, the next most useful feature in Excel for the analyst is the ability to convert the text in a column into multiple columns. This is a lot like the text import window that opens up when you open a text file, but it can be used at any time on any column in your spreadsheet.  To use it, simply select the column you want to convert:

image

Then go to the Data tab and click on “Text to Columns”

image

This will bring up the wizard that steps you through the process:

image

If you are working with a NASTRAN type input file, formatted with fixed columns, you can chose “Fixed Width” here. If not, choose delimited.  Click next.

For fixed, you get a ruler that you can drag the column lines back and forth on till you get what you want. Pretty simple.

For delimited, you get the delimiter screen.  Specify your delimiter here.  In the example, we will use a comma. But it can be spaces, tabs, or any other character. When you specify the delimiter, it shows you how Excel will break it up. 

image

I usually click finish here because the next screen is formatting and I usually play with that once I have the data in Excel.

That is it. Very simple.

image

One thing to note, it converts to columns by overwriting columns to the right. So if you have data in those columns, you should insert enough blank columns before you use this command, so you don’t overwrite anything.

Names

Usually you refer to a cell or a range of cells with the old LetterNumber syntax: A3, B7:NN2145, etc…  That can be a real pain to deal with and it really doesn’t tell you what the data in that range is.  A better way to deal with chunks of information, or critical cells, is to use names. 

Creating names is very easy.  The simplest is to click on the cell or cells you want to name and then type in the name you want in the input box in the upper left corner:

image

Now, if you want to know the max value of those numbers, you can use the formula =max(MyData)

image

If I have a lot of constants I want to define, I can use the “Create from Selection” tool in the Formulas tab:

image

This command brings up a dialog box and you can tell Excel where to grab your names from. Three or Four clicks and you have named parameters instead of cell locations.  This is very useful if you have a group of key parameters you want to use in your calculations.  Now when you look at your formulas, the descriptive name of the parameters are there rather than a reference.

image

Use the Name Manager in the same Formulas tab to view, edit, and delete your names.

Dynamic Range

A related trick for Excel is creating dynamic ranges. What do you do when you name a range and then the amount of data in that range changes? You have to redefine your range.  Nope, you don’t. You can define the range using a formula that changes as the length of the column, or row, changes. 

The name can be defined for a column as: =OFFSET(startCell,0,0,COUNTA(column)-1)

Or for a row: =OFFSET(startCell,0,0,0,COUNTA(row)-1)

This may be the most time saving trick I know in Excel.

You put the formula into the “Define Name” dialog box found on the Formulas tab:

image

Now, no matter how long the column of data is, MyVals will always contain it.  A big time saver.

Relative Reference on Record Macro

How many times have you gotten data in Excel, or imported data into Excel, where you want to make a small change to every line. But you have several thousand lines. If you do a “Record Macro” that doesn’t work because you have to click down to the next line, then run the macro and repeat that over and over again. Wouldn’t it be great if you could simply record a macro with some sort of relative reference. 

For years (maybe decades) I didn’t know you could do that. There is an option under the Developer Tab called “Relative Reference.”  Click that before you record your macro and you are good to go.

image

As an example, take a look at this data. Nodal coordinates on one line, rotations on the second. 

image

I want to grab the rotations, paste them on the same line as the coordinates, delete the rotation line, then move to the next node.

Here is a video that shows the process:

That is all fine and dandy if you have a few dozen lines, but your fingers will get tired CTRL-e’ing that many times.  I quick fix is to go into the macro and add a simple loop.  First we use CountA() to see how many nodes we have, then we loop on that with a for statement:

Sub Macro3()

'

' Macro3 Macro

'

' Keyboard Shortcut: Ctrl+e

'

    cnt = Application.CountA(Range("a:a"))

    For i = 1 To cnt

    ActiveCell.Offset(1, 1).Range("A1:C1").Select

    Selection.Cut

    ActiveCell.Offset(-1, 3).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select

    Selection.Delete Shift:=xlUp

    ActiveCell.Select

    Next i

End Sub

Of course you could have done this with *VREAD’s in MAPDL, or python. But sometimes Excel is just faster.

Categories

Get Your Ansys Products & Support from the Engineers who Contribute to this Blog.

Technical Expertise to Enable your Additive Manufacturing Success.

PADT’s Pulse Newsletter

Keep up to date on what is going on at PADT by subscribing to our newsletter.


By submitting this form, you are consenting to receive marketing emails from: . You can revoke your consent to receive emails at any time by using the SafeUnsubscribe® link, found at the bottom of every email. Emails are serviced by Constant Contact

Share this post:

Upcoming Events

09/27/2023

2023 AZ Bio Awards

09/26/2023

Experience Stratasys Truck Tour - Houston

09/22/2023

AIAA Rocky Mountain Section Technical Symposium 2023

09/22/2023

Experience Stratasys Truck Tour - Dallas, TX

09/21/2023

Accelerating the Energy Transition through Simulation

09/20/2023

3D Printing vs. CNC Machining - Webinar

09/13/2023

Maxwell Updates in Ansys 2023 R2 - Webinar

09/12/2023

Sandia Science & Technology Park 25th Anniversary

09/12/2023

Experience Stratasys Truck Tour - Tempe, AZ

09/08/2023

26th Annual New Mexico Flying 40 Awards

09/08/2023

New Mexico Tech Summit

09/07/2023

New Mexico Tech Summit

08/30/2023

Structures Updates in Ansys 2023 R2 (1) - Mechanical, Post & Graphics

08/23/2023

Improved Injection Molding with Additive - Webinar

08/22/2023

SPIE Optics & Photonics Exhibition 2023

08/16/2023

Fluids Updates in Ansys 2023 R2 - Webinar

08/04/2023

Experience Stratasys Truck Tour - Salt Lake City, Utah

08/01/2023

Experience Stratasys Truck Tour - Denver Colorado

07/26/2023

Solving Supply Chain Issues with Additive - Webinar

07/25/2023

Arizona Tech Leadership Golf Tournament

07/24/2023

Arizona Tech CEO Leadership Retreat

07/19/2023

System Automation & Optimization Updates in Ansys 2023 R1 - Webinar

07/13/2023

2023 AEROSPACE, AVIATION, DEFENSE AND MANUFACTURING CONFERENCE

07/12/2023

Materials Updates in Ansys Granta 2023 R1 - Webinar

06/30/2023

Turbo Expo 2023

06/29/2023

Turbo Expo 2023

06/28/2023

Turbo Expo 2023

06/28/2023

Revolutionize Packaging Design with Additive - Webinar

06/27/2023

Turbo Expo 2023

06/27/2023

2023 E-MOBILITY AND CLEAN ENERGY SUMMIT

06/26/2023

Turbo Expo 2023

06/21/2023

Optics Updates in Ansys 2023 R1 - Webinar

06/07/2023

LS-DYNA Updates in Ansys 2023 R1 - Webinar

05/31/2023

Driving Automotive Innovation with Additive - Webinar

05/24/2023

Hill Air Force Base Tech Expo

05/24/2023

Structural Updates in Ansys 2023 R1 (3) – Structural Optimization & Ex

05/23/2023

CROSSTALK 2023: Emerging Opportunities for Advanced Manufacturing Smal

05/10/2023

Signal & Power Integrity Updates in Ansys 2023 R1 - Webinar

04/26/2023

Additive Manufacturing Updates in Ansys 2023 R1 - Webinar

04/20/2023

38th Space Symposium Arizona Space Industry

More Info

04/19/2023

38th Space Symposium
Arizona Space Industry

04/19/2023

Additive Aids for Manufacturing - Webinar

04/18/2023

38th Space Symposium
Arizona Space Industry

04/17/2023

38th Space Symposium

04/13/2023

Venture Madness 2023

04/12/2023

Fluid Meshing & GPU-Solver Updates in Ansys 2023 R1 - Webinar

03/29/2023

8th Thermal and Fluids Engineering Conference

03/29/2023

Structural Updates in Ansys 2023 R1 - Composites, Fracture & MAPDL

03/28/2023

8th Thermal and Fluids Engineering Conference

03/27/2023

8th Thermal and Fluids Engineering Conference

03/26/2023

8TH Thermal and Fluids Engineering Conference

03/24/2023

Arizona BioPreneur Conference | Spring 2023

03/22/2023

2023 Arizona MedTech Conference

03/22/2023

Optimize Jigs & Fixtures with Additive - Webinar

03/15/2023

3D Design Updates in Ansys 2023 R1 - Webinar

03/08/2023

Competitive Advantages of 1D/3D Coupled Simulation - Webinar

03/01/2023

High Frequency Updates in Ansys 2023 R1 - Webinar

02/22/2023

Additive Advantages in Aerospace - Webinar

02/15/2023

Structural Updates in Ansys 2023 R1 (1) - Webinar

02/09/2023

IME 2023: MD&M | WestPack | ATX | D&M | Plastek

02/08/2023

IME 2023 MD&M | WestPack | ATX | D&M | Plastek

02/07/2023

IME 2023 MD&M | WestPack | ATX | D&M | Plastek

01/27/2023

Arizona Photonics Days, 2023

01/26/2023

Arizona Photonics Days, 2023

01/26/2023

TIPE 3D Printing | 2023

01/26/2023

Venture Cafe Phoenix Talent Night - Job Fari

01/26/2023

VFS 2023 Autonomous/Electric VTOL Symposium

01/25/2023

Arizona Photonics Days, 2023

01/25/2023

Building A.M.- Utah: Kickoff!

01/25/2023

TIPE 3D Printing | 2023

01/25/2023

VFS 2023 Autonomous/Electric VTOL Symposium

01/24/2023

VFS 2023 Autonomous/Electric VTOL Symposium

01/24/2023

TIPE 3D Printing | 2023

01/18/2023

2023 AZ Tech Council Golf Tournament

12/21/2022

Simulation Best Practices for 5G Technology - Webinar

12/14/2022

Digital Twins Updates in Ansys 2022 R2 - Webinar

12/08/2022

Tech the Halls - AZ Tech Council Holiday Mixer

12/07/2022

Electric Vehicle and Other Infrastructure Update Panel

11/30/2022

SPEOS Updates in Ansys 2022 R2 - Webinar

11/23/2022

Simulation Best Practices for Electronics Reliability - Webinar

11/16/2022

Discovery Updates in Ansys 2022 R2

11/10/2022

VentureCafe Phoenix Panel: Venture Capital in AZ

11/08/2022

2022 GOVERNOR’S CELEBRATION OF INNOVATION AWARDS + TECH SHOWCASE

11/03/2022

VentureCafe Phoenix Panel: Angel Investment in AZ

11/02/2022

High & Low Frequency Electromagnetics Updates in Ansys 2022 R2

10/26/2022

Simulation Best Practices For Chip-Package-System Design & Development

10/20/2022

Nerdtoberfest 2022

10/19/2022

2022 Southern Arizona Tech + Business Expo

10/19/2022

LS-DYNA Updates in Ansys 2022 R2 - Webinar

10/17/2022

Experience Stratasys Truck Tour - Clearfield Utah

10/14/2022

ASU School of Manufacturing Systems and Networks - Formal Opening Cele

10/14/2022

Experience Stratasys Truck Tour - Midvale Utah

10/12/2022

Experience Stratasys Truck Tour - Littleton Colorado

10/06/2022

Fluids Updates in Ansys 2022 R2 - Webinar

10/05/2022

Experience Stratasys Truck Tour - Colorado Springs

09/29/2022

White Hat Life Science Investor Conference - 2022

09/28/2022

2022 AZBio Awards

09/28/2022

Simulation Best Practices for Rotating Machinery Design & Development

09/21/2022

ExperienceIT NM 2022

Search in PADT site

Contact Us

Most of our customers receive their support over the phone or via email. Customers who are close by can also set up a face-to-face appointment with one of our engineers.

For most locations, simply contact us: