Tag Archives: Refactoring

BumbleBee: an Excel refactoring Add-In

There is a new version of the BumbleBee that contains new refactorings. Get it here for Office 2010 and up (Alternative 1, Alternative 2).
A spreadsheet with examples where the refactorings can be applied on can be downloaded here.

Two years ago, we presented the first version of the BumbleBee Excel refactoring Add-In.  With this version, you could rewrite formulas into other using a specialized formula transformation language. This version adds refactorings which were not possible to create using the transformation language. A full overview of the refactorings and implementation can be found in this thesis. All refactorings are accessible from the “refactor” context menu (which appears when you right click on a cell) this Add-In adds.

Furthermore this version is now based on XLParser, which means that it can now handle virtually all of your formulas and also works if you reference other sheets or files.

Extract Formula allows you to extract an expressions from one or more cells into other cell(s). Example applications are splitting a formula in two, or to extract a magic number in the whole spreadsheet into a unique cell.

Inline Formula is the opposite of Extract Formula and will replace all references to a cell with the formula that cell contains.

Introduce Cell Name is a enhancement of the Excel “Define Name” functionality. You can give a cell or range a name with this functionality, and all references to exactly that cell or range will be replaced by the new name.

Group References is a small refactoring which helps you clean up formulas. In function where argument order does not matter (such as SUM or COUNT) it will merge references into ranges where possible and order them alphabetically. For example =SUM(F1,F2,A1,A2,A3) will become =SUM(A1:A3,F1:F2) after applying this refactoring.

Change + to SUM(IF) will transform additions into SUMs, for example =A1 + A2 + A3 + A4 will become =SUM(A1:A4) (it actually works for * and & too). Furthermore if everything you sum is in a single column and there is a column which contains a unique value for every row you sum, it will convert a + or SUM into a SUMIF.