Tag Archives: Excel add-in

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.

Presenting Aragorn: A Floating Prompt System for Spreadsheets and the AraSense Dependence Tracking Feature

aragorn

So what (who) is Aragorn?

We all know who Aragorn is! The mighty brave king of Men, Isildur’s heir, the skilled warrior, protector of the Fellowship of the Ring and the hobbits. Unfortunately we are not talking about him here. We are in the process of developing an Excel add-in tool, which quite similar to the real Aragorn, will help you protect yourself from orcs, goblins, and such nasty creatures (mistakes, errors, pitfalls) while you work on your spreadsheets.

What made us develop Aragorn?

We were trying to have a closer look at spreadsheet dependence tracing techniques. In my blog post I discussed some of our findings from a review of the research on this topic. We found that there was still enough need for good and reliable dependence tracing techniques which can be adequately and effectively adopted by the industry in a standardized and popular way. In 2013, in her dissertationDr. Felienne Hermans wrote about some responses from spreadsheet users –

Quote

We were particularly motivated by a scenario where you, as a spreadsheet user, have the task of coming up with some calculation or financial estimate after some data crunching in a fairly large complex spreadsheet which you have not created yourself. Imagine, to obtain the required results you need to change some parameter values, modify some cells, move data around here and there. However not knowing well the full design structure of the spreadsheet you definitely do not want your changes to affect the whole model so that the results you obtain turn out to be wrong! On the other hand perhaps it´s not even worth it for you to spend days in trying to understand the spreadsheet model as it may be for a one-off use in your case. This is exactly the type of scenario where a quick, real-time prompting system displaying cell dependency information can make things a lot easier. While this was our primary motivation, we also wanted to make sure that the tool was easy and lucrative enough for you. Therefore we also aimed to ensure that the following key features were there in our tool:

  1. Effortless: We did not want to burden you with additional effort of multiple mouse clicks or peering into the screen trying to distinguish between overlapping blue arrows which all seem to point towards the same cell or no-where.
  2. Real-time: We wanted our system to be a process that is part of using the spreadsheet itself and not a separate activity to be done at a different time.
  3. Instantaneous: We wanted our system to be quick and momentary so that you do not have to wait for the information.
  4. Specific: We did not want to burden you with more information than you really need; it’s not always necessary or worthwhile to understand the whole structure of a spreadsheet when all you need to know is whether a change in one or two cells are going to affect other cells.

So what have we come up with?

The core of Aragorn is the floating prompt system. With these localized text-boxes very close to active cells, we can display crucial information about the selected cell in a transient manner.

5

In addition to this we have developed the feature called AraSense particularly to cater the need of dependence tracking especially for scenarios like I described above. It is our answer to the questions raised by the users.

We all know how the real Aragorn is also an expert ranger with superior tracking skills. He could track the trail of hobbits when they almost gave up hope of finding them alive!

 

download
Aragorn tracking hobbits’ trail.

Similarly the AraSense feature is the tracking power of the Aragorn tool. It can pre-sense dependencies and thus its called AraSENSE. It can do a one-time analysis of an opened Excel workbook, after which it will display a list of dependent cells in the floating prompts for every cell you click on. If you are not trying to edit the cell, normally the prompt will disappear automatically after a delay. If you are trying to edit a cell, the prompt will persist next to it as long as you are editing the cell. Even if the dependent cells are in a different worksheet, they will be listed in the prompt. You can turn the feature on or off based on your preference. No warning is shown for cells that do not have any dependents. Thus when the feature is turned on, if there is no prompt for a cell, then you can safely modify its contents without worrying. And if there is a prompt then you know which are the dependent cells and on which sheet they are. Thus you can be cautious and forewarned about the changes you make. The AraSense feature is effortless, real-time, instantaneous and specific. It fulfills all the aims we had about the system we wanted to give you.

So how it works?

 

So what we would like from you?

We would like to hear your thoughts on Aragorn and AraSense.

If you are a professional who uses spreadsheet at work, then do you think this tool will be helpful for you?

If you are not a spreadsheet user, but interested about end-user computing and works with usability aspects then do you think our tool addresses some key usability aspects?

Whoever you are, if you have read this and found it worthwhile of your time, we would like to thank you and would really like your thoughts and opinions. Encouragements are obviously appreciated but we would also eagerly welcome criticisms and suggestions for improvements.