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.
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”
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
– Any criteria that is unique to each formula may be placed in this section
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.
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.