Are Your Formulas All “Wrapped” Up?

The other day, there was a discussion on one of the LinkedIn Excel Groups regarding wrapping formulas in the formula bar. Today. I’ll take a look at wrapping and indenting formulas complete with screen shots and a workbook you may download at the end.

The Data

First, I’ll need some data. I use Dick Kusleika’s Random Data Generator to get some quick data. I selected the fields I needed to generate and voila! 20K records very quickly.

I converted the entire range of data to an Excel Table. I named the table, “tblData”

The Analysis

To analyze the data I went to another sheet and setup some criteria fields based on the Field Headers.
Think of this section as a Page Field in a Pivot table. You do not necessarily have to setup your analysis in this manner. It is just a structure I use as it makes sense to me.

“Page Field” – Any criteria that is common to all formulas may be left in his section

“Row Labels”
– Any criteria that is unique to each formula may be placed in this section

The Criteria

I entered all of the crieria in each cell that I am interested in

The Formula – Long Form

I wrote a formula using Excel Tables and Structured References

Not too bad. But it is a little long and hard to read. The image causes us to have to scroll left right on the blog post to see the whole thing so we lose clarity of what is on the left. I’ll “wrap” the formula in the formula bar to see if that clears things up.

The Formula – Wrapped Form

To “Wrap the formula in the formula bar, click before the text where you want to insert a break, then hold down the [ALT] key and press [ENTER]. I’m going to add a break for each Field/Criteria set so each set is on its own line in the formula bar.

Looking better. I’ll see what it looks like if I indent each line as well. To indent a line, use the space bar to mve the text to the right.

The Formula – Wrapped And Indented Form

I indented all lines after the first line to line up the name of the table.

I think this version is the easiest to read and understand.

The result

I drilled in to the data, In this instance, there was only 1 line item that met the criteria so validation of the formula results in this instance was easy.

Download a copy of the workbook

Let us know how you use wrap and indent in your formulas in the comments section below.

Advertisements

2 thoughts on “Are Your Formulas All “Wrapped” Up?

  1. dick kusleika

    I don't use indent and wrap. I really, really want to, but I always find that it ends up being more work than it's worth. Your example is a great one. Now I just need that to happen automatically. :)I think MS has done wonderful things with the formula bar (expandability, highlighted matching parentheses) but I also think there's more to be done. The formula bar a little IDE and should be treated as such. I don't know if that's better for the average user or just the nerds like us.

    Reply
  2. Jordan Goldmeier

    It's a pleasure to have you comment here, Dick – I'm a big fan of your work. Any advancements to the formula bar would be better for the average user, I would think. Many average users are afraid to expand their skills when they see the long and dizzying formulas we nerds use. Part of reading formulas is simply training (the more you do it – the better at it you get) – but, like you said, there's still much more that can be done to the formula bar to make it more approachable.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s