How do I make my Excel equations pretty?

January 13, 2008 5:31 PM

Is there a way to format equations in Excel so they are more readable?

I'm creating a series of Excel-based utilties, many of which contain complex equations. They are hard for me to read and edit, and I find myself cutting and pasting the equations into Microsoft Word so that I can indent them, as I would code in any other programming language. Is there a better way to do this?

I am using Mac OS X, but if a Windows-based solution is available I'd work with that. Either a feature of Excel, add-in, or third-party program would be helpful.

posted by bchaplin to Computers & Internet (10 answers total)

I use Excel on Windows, and I use a utility called MathLook for Excel from UTS Systems. The program is $99. It renders Excel formulas into proper math notation. It’s very useful for double checking formulas.
posted by Jasper Friendly Bear at 5:47 PM on January 13, 2008

Jasper Friendly Bear reminds me of something I have found useful in the past on complex formulas, which is naming ranges and cells, which would allow you to change =C8*C11/(1-1+C11)^-C10) to =Loan*rate/(1-1+rate)^-term). There's no fancy typography, but I still find it helpful.
posted by grouse at 5:54 PM on January 13, 2008 [1 favorite]

I second Grouse's recommendation.

Something else I do is to insert extra rows and columns to calculate "intermediate results". This can help with error checking (make sure certain components are non-negative, non-zero, etc.) and help simply the "construction" of overall results.

This can be done on other sheets or just out of the print area.
posted by KevCed at 6:52 PM on January 13, 2008

Naming helps, for sure.

Also, you can insert line breaks in your formulas using alt+enter (in Windows, at least). Dunno how to do idents though.
posted by mullacc at 7:49 PM on January 13, 2008

I like to keep my intermediate results on a separate sheet within the workbook, and name them.
posted by flabdablet at 9:54 PM on January 13, 2008

Break things down into smaller functions as much as possible. If you have monster formulas in single cells they're a bitch to troubleshoot.
posted by dhartung at 11:31 PM on January 13, 2008

Using Names is helpful but there are drawbacks if other people will use the spreadsheet as well as inserting and deleting rows and columns can reek havoc on named ranges. Plus, some functions react to named cells and ranges inconsistently, especially with array and table-based formulas.

Also, you can always add comments to cells and paste the formula there with some nominal formatting as a reference.
posted by michswiss at 1:41 AM on January 14, 2008

write a custom function, then you can format the code/formulas neatly in the VBA editor
posted by Lanark at 11:00 AM on January 14, 2008

Response by poster: Thanks, all! Lanark, the custom function idea is intriguing, but new to me. Once a workbook contains custom functions, can anyone with the standard Excel application still open and use it? Would the VBA editor be required to modify that function in the future?
posted by bchaplin at 2:22 PM on January 14, 2008

Yes the custom function is stored in the workbook like a macro so anyone can run it.

One catch is that in Office 2008 for Mac, support for VBA has been dropped
posted by Lanark at 2:32 AM on January 27, 2008