Excel Tip: Excel F9 (Calculate) Function Key

Most Excel users are aware that turning off calculation allows much faster saving of large workbooks (save often, avoid tears). Excel, by default, calculates before saving, but that becomes very slow with large workbooks. We can save time by using F9 to calculate manually once edits are complete. F9 can be used to debug or evaluate a formula.

In the example below, the user has typed =TRANSPOSE(A2:A13)&”,” and pressed F9 to evaluate the result, a comma delimited string:

excel1.jpg

We would usually press Esc immediately after, otherwise Excel will insert the evaluation into the formula as a literal, which we don’t usually want.

But wait, what if we actually want a string containing all those codes? Don’t press Esc! Just delete both curly braces, and insert “CONCATENATE(“ between the equal sign & the first double quotes, and add a closing parenthesis at formula end. Before pressing enter, delete the final comma after W7.

excel2.jpg

Press Enter, and the cell now contains a string: “E3,S2,J8,X4,E9,N3,U7,U3,E7,E5,R1,W7”, a comma delimited string containing the code in each cell. We did this without having to type each cell followed by a comma in the formula! We can now copy that and paste it as a string value anywhere we wish.

 
1
Kudos
 
1
Kudos

Now read this

BDD – Working with Scenarios

Behaviour Driven Development (BDD) or Specification by Example is the process of writing acceptance criteria as tests, using the defined format of Scenarios. Some teams take this further using a tool such as Cucumber to automate the... Continue →