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:

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.

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.