Excel to Insight Worksheets [Analysis with Insight]

One of the very pow­er­ful and commonly-used visu­al­iza­tions in Adobe Insight is the Work­sheet visu­al­iza­tion. It allows an ana­lyst to cre­ate ad hoc met­rics and fil­ters. Like a stan­dard spread­sheet, every cell can con­tain a sta­tic value or a for­mula — includ­ing ref­er­ences to other cells. In addi­tion, though, Insight Work­sheet cells can con­tain for­mu­las that define metrics.

Sev­eral times a month, clients ask me if they can bring in exter­nal data — from another Excel work­sheet for instance — quickly and eas­ily with­out retyping.

Some­times, it’s as sim­ple as bring­ing in a dozen cells of bench­mark data to write com­par­a­tive met­rics against.

Some­times, it’s more like 12 rows times 12 columns of bench­mark data.

Either way, it’s labor-intensive to re-type and intro­duces the risk of human error. Plus, it’s a pain if these val­ues change over time and need to be imported fre­quently (but not fre­quently or sig­nif­i­cantly enough to war­rant automat­ing them in as a log source into your dataset.)

A few Fri­days ago, I was talk­ing to Doug McCormick, one of our rock star (my term of affec­tion) Insight solu­tion con­sul­tants, and men­tioned this chal­lenge. I men­tioned it to him because he’s a whiz at Excel macros, and I fig­ured he might have an idea or want to even­tu­ally work on t.

By Mon­day, he had a solu­tion. I’m proud to intro­duce the “Excel to Insight” quick tool.

“Excel to Insight” will take data that you input into Excel (sta­tic val­ues, for­mu­las, and even met­ric for­mu­las that you’d like to write in Excel and bring across to Insight) and con­vert it into an Insight work­sheet in a .vw file that can be opened in Insight — effec­tively tak­ing your Excel data to an Insight work­sheet for you!

I do have to say that this isn’t part of the Insight solu­tion — it’s just a sup­ple­men­tary tool, pro­vided by Adobe Con­sult­ing, that might make life eas­ier for many Insight ana­lysts. As such, it’s pro­vided with­out war­ranty and is not sup­ported by Client­Care or avail­able as part of any default pack­age or instal­la­tion of Insight.

You’re feel free to grab it and use it as you need to, though. And if you have any feed­back or ideas for improve­ment upon it, feel free to let me know.

Here’s how it works:

First, unzip the pro­vided zip file so the root install folder “Insight_Excel-2-Insight” sits on the root C:\ drive (there are file references/links that need to ref­er­enced in the same location).

Then, open the Excel spread­sheet “Excel-to-Insight — Draft v1.xlsm”. Fol­low the instruc­tions on the first work­sheet to ensure you’ve enabled macros in a way that this tool can do its job.

Finally, plug the data you want into the allow­able cells (inside the red bor­der area) in the sec­ond work­sheet. Input the desired file­name for your .vw and click the but­ton at the top, and — presto! — you have a .vw file.

For the pur­poses of this demo, I filled in some ran­dom data in the tab as below. Then I put my desired out­put file­name in cell D10, then clicked the big “Cre­ate” but­ton in “Step 2″ at the top.

Once you have the .vw file, you can open it via the “Open” com­mand in an Insight work­space, and/or copy into your user Work folder in Insight and open from within the Insight client. Here’s mine:

Now I can use this data as a start­ing point to com­pare to met­rics from for­mu­las within Insight — updat­ing from my Insight dataset. I can also use these val­ues as the bench­marks for for­mu­las that will pop­u­late indi­ca­tor (true/false or up/down arrow) cells for alerting.

The tool has instruc­tions built in. And, yes, you can pre-write for­mu­las in Excel as well — just use a lead­ing sin­gle quote (‘) at the start of the formula.

Feel free to grab this, use it, and enjoy the effi­ciency of quickly & eas­ily bring­ing your Excel bench­mark data into a work­sheet in Insight.

Down­load the Excel to Insight tool from the Adobe Devel­oper Con­nec­tion. It’s avail­able in the Devel­oper Con­nec­tion as “Excel to Adobe Insight Work­sheet Tool.”