Converting Excel to Mathcad: Best Practices

April 4, 2006

10 Min Read
Converting Excel to Mathcad: Best Practices

Why convert your Excel worksheets into Mathcad worksheets?
You’ve already spent the time and energy in creating valuable math content and engineering work. Why not continue to benefit from your work as you embark on future projects?

By converting existing Excel worksheets to Mathcad, you can:

Extend the life of the math and engineering work on which you’ve already spent valuable time and resources.
Bring your computations into an environment designed for engineering math—one that captures mathematical content in natural math notation and incorporates units intelligence into every equation.
Give legs to what previously may have been viewed as a “black box” solution.
Leverage Mathcad’s whiteboard interface and documentation tools to allow these converted equations to be multi-purposed into other types of systems or used by other departments.

Consider the migration of Excel into Mathcad as a step-by-step process:

1. Identify the Excel sheet

  • Converting Excel to Mathcad is both a noble and painful effort. It pays to ensure the
    Excel worksheet you are targeting is not only one that is used today, but one that is also a good candidate for migration to Mathcad. How do you determine what constitutes a “good candidate”?

  • An Excel worksheet is a good candidate if it satisfies the following criteria:

  1. Used to do engineering math.

    • Difficult to follow mathematical workflow.

    • Difficult to visualize variables, equations and results.

    • Used as part of a design or verification process.

    • Has limited UI Controls and VBA macros. (Some are okay, but for the most part UI controls and VBA macros will remain the same in Mathcad, so where is the gain? See online Developer’s Reference for more information on VBA & UI controls

2. Take inventory of the worksheet and plan content mapping

  • Taking inventory is simply a review process of the existing worksheet.

Review process:

  • Understand if the worksheet has dependencies on other worksheets, perhaps in the workbook or on external data sets such as flat files or databases.

  • Understand what math functions are being used. To do this step, hit Control-` (tilda). The worksheet will display the entire math hidden in cells (Figure 1 to the right). View the routines and evaluate how Mathcad will handle these functions. Mathcad will most likely have a 1:1 mapping of all math functions(sin, cos, hlookup, vlookup, maxa, mina ...).

  • Understand the variables being used. If you use the dropdown Insert->Names->Define, you’ll get a dialog showing you all the variable names.(Figure 2) Look at the worksheet--see how and where they are being used. Mathcad works nicely with named variables. Understand the name space; you may want to use abbreviated names for some variables. Mathcad global variables may be needed if Excel variables are used before they are declared in a top/down structure.

  • Understand the dependencies on individual cells and tables from within the equations. Many equations will leverage tables through hlookup/vlookup. Others will

    simply pull out individual cell or table values. Knowing this will help you understand if Mathcad tables should be used or if individual variables will suffice.

  • Investigate any macros used in the Excel worksheet, and consider how those macros will be implemented in Mathcad.

  • Planning content mapping involves considering all types of content in the existing worksheet and determining the best method to implement it in the new Mathcad worksheet. Some content types are outlined below.

Content Mapping:

  • Math/Engineering Content – Calculations in the Excel worksheet should map directly into the whiteboard workspace. Some cases to pay specific attention to are:

    • Unit Conversions – Should be eliminated by leveraging Mathcad’s built-in units intelligence/converter

    • Iterative calculations - Can be implemented using Mathcad Programming loops, range variables, vector operations or parametric functions.

    • Lookup tables – Can be implemented using a data-matrix or one of many File I/O protocals

    • UI controls - Should carryover from Excel to Mathcad in a straightforward manner

  • Existing Datatables – There are a number of options to choose from when deciding upon a style of conversion for existing datatables. The major decisions are

    listed below:

    • Excel tables can be copied directly into Mathcad tables. This is manual labor, one table at a time but also allows for the original Excel sheet to be retired. (Figure 3)

    • Mathcad can read Excel files/ranges directly, resulting in a Mathcad matrix. This requires keeping the Excel sheets around. This option is very easy to implement.

    • Mathcad supports an Excel Component which allows interactive usage of both Mathcad and Excel. The original Excel worksheet becomes part of the Mathcad worksheet and is saved with the Mathcad worksheet. This option is very powerful, but heavy. You may need to use multiple instances of the Excel component.

  • File Metadata - Excel File Properties should map to Mathcad File Properties. Specific properties defined on the Excel sheet should be mapped to custom properties in Mathcad.

  • Cell Annotations – Comments on individual cells in the Excel sheet should be implemented in Mathcad in one of two ways, depending on the comment-type:

    • Verifiable Source - Comments indicating the verifiable source of a constant or equation should be implemented using region-level metadata. Right mouse click on the region to add these comments.

    • Contextual Information - Comments providing contextual information on the cell’s contents should be implemented in the worksheet whitespace using the documentation tool

3. Take advantage of Mathcad’s landscaping and formatting options

  • For the most part you are going to try and emulate the layout of the original Excel worksheet. Mathcad’s document interface makes this an easy process, and also provides much more flexibility than Excel’s rigid cell structure does. So have fun. Remember headers/footers for printing. Also, Mathcad supports all the font families and font faces of Excel, so you can produce an almost identical look and feel.

  • You also need to choose a layout - deciding whether or not to use hidden regions. One of the key benefits to moving Excel to Mathcad is our support for natural math notation and units. However, for some engineers, viewing the equation is only important when designing, reviewing, and debugging. Specifically, some users do not need to see the equation when they are computing. They need to be able to change the input parameters and view the results. You can hide whatever portion of the calculations you like in a collapsed area - the calculations will work as they did before, but it's at your discretion whether they will see how the work is done.



Sign up for the Design News Daily newsletter.

You May Also Like