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.
I converted the entire range of data to an Excel Table. I named the table, "tblData"
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
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.
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.
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.
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 inThe Formula - Long Form
I wrote a formula using Excel Tables and Structured ReferencesNot 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.
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. :)
ReplyDeleteI 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.
It's a pleasure to have you comment here, Dick - I'm a big fan of your work.
DeleteAny 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.
Nice post, thanks for sharing
ReplyDelete