Everyone likes to be in control. Even my 2 year old daughter jumps with joy when she lays her hands on TV remote. She pushes the buttons and assumes it is working. It is another story that we rarely watch TV at home.
By adding an element of control, we can make our dashboard reports fun. Interactive elements like form controls, slicers etc. invite users to play with your dashboard, get involved and understand data by asking questions. That is why I recommend making dashboards interactive.
Today lets understand how you can make dashboards interactive.
There are 2 aspects to interactivity:
- What users see (controls, slicers etc.)
- How it works in background (formulas, pivots, tables etc.)
Section 1: Adding interactivity to your dashboards
There are many techniques to add interactivity to your dashboards. Lets look at each of them closely.
Using Data Validation to add drop-downs to a cell
This is the easiest way to get started. Using data validation feature in Excel, we can restrict only a set of values in a cell. When you do this, Excel shows a small drop down box (combo-box) inside the cell so that you can pick one of the possible values. Like this:
Demo of what you can do:
An example report show casing flu trends in US, various states & cities between 2003 – 2009. For more, click here.
Learn how to use data validation drop-downs:
- Adding data validation drop downs in Excel – Introduction & Examples
- Cascading Drop downs – load values in 2nd list depending first list
- Making data validation list dynamic
Example Dashboards with data validation drop downs
- Flu trends dashboard in Excel
- Visualizing Survey Results using Panel Charts
- Sales Analysis charts in Excel – lots of examples
- Personal Expense Trackers
- Sales Dashboards – lots of examples
- Excel Salary Survey – Dashboards – lots or examples
Using Form Controls to add interactivity
Almost all computer users are familiar with form controls. We see them every day – scroll bars, check boxes, option buttons, buttons – pretty much all programs in your computer are ripe with form controls. But do you know you can add the same controls to your Excel worksheet?
You can use these controls on worksheets to help select data. For example, drop-down boxes, list boxes, spinners, and scroll bars are useful for selecting items from a list. Option Buttons and Check Boxes allow selection of various options. Buttons allow execution of VBA code.
By adding a control to a worksheet and linking it to a cell, you can return a numeric value for the current position of the control. You can use that numeric value in conjunction with the Offset, Index or other worksheet functions to return values from lists.
Demo of what you can do:
[Watch the demo on our YouTube channel]
Learn how to use form controls
- Introduction to various form controls & Examples
- Using check boxes with charts – example & tutorial
- Using scroll bar control – simple mortgage payment calculator in Excel
Example dashboards using form controls
- KPI Dashboards using Excel
- Customer Service Dashboard in Excel
- Excel Salary Survey Dashboards – lots of examples
- Sales Dashboards in Excel – lots of examples
Using Slicers to add interactivity
Slicers, a new feature added in Excel 2010 can be used to add interactivity to your dashboards & reports. Slicers are like visual filters. So you can see all available options as small boxes and you can click which option you want.
Demo of Slicers in action:
Learn how to use Slicers
- Using slicers to make a dynamic dashboard in Excel
- Overview of slicers & other new features in Excel 2010
- Using slicers to select one of many scenarios in your models
Example Dashboards using Slicers
Using Click-able cells as interactive elements
With a few lines of VBA code, you can turn every cell in Excel in to a potential input option. When user clicks on a particular cell, you can treat that as interaction and modify your dashboard (or chart). This is a very powerful and intuitive way to use in dashboards. See below example.
Demo of what you can do:
Learn how to use click-able cells
Example dashboards using click-able cells type of interactivity
- Interactive sales chart in Excel
- Displaying product reviews on demand
- Grammy bump chart in Excel
- Customer service dashboard in Excel
- Excel Salary Survey Dashboards – lots of examples
Using Hyperlinks to add interactivity
Many of you know that you can type any text in a cell and press CTRL+K to convert it to a hyperlink to another part in your workbook. But Hyperlinks can trigger macros upon mouse hover. This is a powerful technique first mentioned by Jordan at OptionExplicitVBA.
By using this behavior, we can create an interactive report that gets updated upon mouse hover. See this demo:
Demo of what you can do:
Learn how to set up dynamic hyperlinks
- Interactive dashboards using Excel Hyperlinks – tutorial & explanation
- Video tutorial on Interactive hyperlinks
- Excel hyperlinks – basics, syntax & more
Example dashboards using interactive hyperlinks
- Excel Salary Survey Dashboards – lots of examples
- Periodic table of elements in Excel [Option Explicit VBA]
Using VBA / Macros to add interactivity
Of course, you can add active x or VBA events to add interactivity to your dashboards. This gives you lot of control on what you want and enables you to do more. That said, using VBA to provide interactivity requires that your audience must enable macros when they view your work.
There are many ways to add interactivity thru VBA. Some popular methods are,
- Adding buttons or assigning macros to drawing shapes, images
- Overlapping buttons or shapes on maps, floor plans etc. and driving events on click
- Using worksheet or active-x controls and adding events (like mouseover, click etc.)
Note: Both click-able cells & interactive hyperlinks also require VBA to be enabled. But the amount of code they require is quite less.
Demo of what you can do
Learn how to use VBA & Macros to add interactivity
- Introduction to VBA, Excel Macros
- Using VBA Macros to make a picture calendar
- Dynamic Pivot Chart using VBA Macros
Example Dashboards using VBA Macro based interactivity
- MLB Pitching Statistics Dashboard
- India’s world cup cricket victory in a dashboard
- Interactive Sales chart using Excel
- Sales analysis charts in Excel – multiple examples
- Excel Salary Survey dashboards – multiple examples
- Visualizing Roger Federer’s Wimbledon victory – Excel VBA Dashboard
Using Timelines to add interactivity [Excel 2013]
Starting Excel 2013, Microsoft is introducing a new feature called as Time lines. Timelines allow you to interactively select a range of dates. I have not yet written any articles on this feature. But here is a short demo on how they work:
Section 2: Behind interactivity – What you need to know in Excel
Now that you know various techniques for interactivity, lets understand various building blocks that help you get there.
Use tables to hold your data
One of the premises of interactivity is that your data can change. When this is the case, I suggest you to set up all your data in tables. Tables allow you to keep data that can grow (or shrink) and write formulas referring to whole range.
Learn how to use tables [Excel 2007 and above only]
Use INDEX formula
INDEX formula helps you extract a portion (single cell, range) from a list of values that you want to use for further calculations or charting. The syntax is simple.
INDEX(range of values, row, column)
Example: INDEX(A1:A10,5) returns A5
Note: Index returns a reference to A5, not the value itself. So you can use INDEX where ranges are expected. For ex. INDEX(A1:A10,5) : INDEX(A1:A10,9) same as A5:A9
Fore more on INDEX formula:
PS: You can also use OFFSET formula in this situations. Please keep in mind that OFFSET is volatile and hence can slow down your workbooks if you use it alot.
Use lookup formulas
Interactive dashboards require formulas that dynamically lookup a set of values among heaps and return them to charts, summaries etc. This is where lookup formulas come handy. Check out our LOOKUP page for comprehensive information on this.
Use SUMIFS, SUMPRODUCT
SUMIFS & SUMPRODUCT formulas will become your best friends when it comes to extracting summaries from mountains of data based on user interaction. Once you master these, you can analyze & visualize any amount of data with ease.
- Introduction to SUMIFS formula, examples & explanation
- Introduction to SUMPRODUCT formula, examples & explanation
- Formula Forensics 007 – Sumproduct
- Advanced SUMPRODUCT examples
- More on SUMPRODUCT, SUMIFS, COUNTIFS, SUMIF, Array formulas
Use Picture links
Picture links are live snapshots of ranges of cells. If you create a picture link from cells A1:D5, then although it looks like a picture, it is a live image of the cells A1:D5. So when the cells change, the picture gets updated too, thus creating interactive effect.
For more on picture links:
- Introduction to picture links – examples, information & uses
- Picture links in practice – example dashboards & charts
Use Pivot tables
Pivot tables can process large volumes of data and give you desired summaries with in split seconds. They are by nature not dynamic (if data or criteria changes, you need to refresh them). Starting Excel 2010, you can use Slicers to interactively update pivot tables (hence pivot charts) . Even in earlier versions, you can use simple macros to automatically refresh pivot tables whenever users modify a form control or do something else. This allows for powerful dashboard reporting all the while keeping your calculation engine light weight.
For more on pivot tables:
Use conditional formatting
Conditional formatting plays an important role in interactive dashboards by highlighted changed portions of worksheet. This further improves the interactive feel and guides users attention.
More on conditional formatting:
Do you make your dashboards interactive?
I love keeping my workbooks, models & dashboards interactive. Simple features like form controls, slicers can add a lot of wow factor to your workbooks.
What about you? Do you make interactive dashboards & charts? What are your favorite techniques? Please share using comments.
Now, if you excuse me, I will go and resolve a fight between my daughter and son. They both want remote control the TV even though it is switched off.
More on Dashboards: Check out Excel Dashboards page & resources for making dashboards page.
40 Responses to “Looking up when the data won’t co-operate (case study)”
Nice Trick.. Clever use of cell references
Here is a formula I tried to create:
=SUMPRODUCT(((NOT(ISERROR(SEARCH(L5,B4:H14))))*1),(B5:H15))
It takes care of Caveat #1 (can handle text), but Caveat #2 remains.
In situations like this, I will often use VBA to restructure the data (2 columns: dates and values) on to a new worksheet. I can then use this 'clean' source for data analysis (formula or pivot table).
=SUMPRODUCT(((NOT(ISERROR(SEARCH(L5,B4:H14))))*1),(B5:H15)) and complex formulae in general are all very well but when you come back to them in a few weeks / months time, it is not at all easy to see what they do and what the limitations are.
Hi Chandoo,
I had used this type of cell ref. various times while calculating average.
But for the situation here try below formula . Note this is an array formula and must be confirmed with Ctrl+Shift+Enter.
=SMALL(IF(MMULT((L5=B4:H14)*IF(ISNUMBER(B5:H15),B5:H15),{1;1;1;1;1;1;1}),MMULT((L5=B4:H14)*IF(ISNUMBER(B5:H15),B5:H15),{1;1;1;1;1;1;1})),1)
Regards,
Hi, I think Using SEARCH in here will create a problem say there is a text like SUN and another text SUNLIGHT both result will be added by SUMPRODUCT.
Regards,
Array option.
=SUM(IF(MOD(ROW(B4:B14),2)=MOD(ROW(B4),2),IF(B4:H14=L5,B5:H15)))
Regards
@Elias,
Nice approach.
Although not requested - the formula I suggested closes all possibilities.
Criteria: _____ Value to retrieve:
Date__________ Numeric
Date__________Textual
Textual________Numeric
Textual________Textual
While your formula copes with only the 3 first combinations.
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
@Michael,
Sorry but I don’t understand your point. I believe the challenge was to return the summary of a given date. What is your really volatile formula doing that mine is not?
Regards
@Elias,
I didn't say that the challenge differs from what you just mentioned/aimed to nor that your formula doesn't provide the requested result.
Please read my previous comment again and focus on the last combination (TEXT / TEXT).
I, myself, always try to provide a global Formula that is capable to handle all sorts of data.
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
@Michael,
I see your point, but you are missing the below points if you are trying to cover all sorts of data.
What happened if the lookup value does not exist?
Do you want the first, second, summary, concatenation of the values if the look value is repeated?
See they are too many possibilities to be cover with just one formula.
Regards
1) The range: B4:H15 was named: RNG.
2) The following Array Formula was "retrieved from my sleeve" and I hope it can be shorten.
3) The formula seems to take care of BOTH(!) caveats.
-------------------------------------------------------------------------------
=OFFSET(INDIRECT(ADDRESS(SMALL(IF(RNG=L5,ROW(RNG),""),1),MOD(SMALL(IF(RNG=L5,(ROW(RNG))+COLUMN(RNG)/10),1),1)*10)),1,)
-------------------------------------------------------------------------------
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
@Michael,
Check what happened with your result if you type 41927 in D5.
Regards
Correct. Didn't predict that.
Will find time to work something out.
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
@Elias,
Let's hope the following Array Formula "closes all open doors".
Again - it has nothing to do with your formula which works fine as long as the 3 first mentioned combinations are concerned.
-------------------------------------------------------------------------------
=INDEX(RNG,LARGE(IF(RNG=L5,MOD(ROW(RNG)-1,2)*(ROW(RNG)),""),1)-2,(MOD(SMALL(IF(RNG=L5,(ROW(RNG))+COLUMN(RNG)/10),1),1)*10)-1)
-------------------------------------------------------------------------------
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
Ok, if you insist. The following will cover all the scenarios you listed. However, I’ll never recommend/use such of formula.
Defined names:
rDat = $B$4:$H$15
rRow =ROW(rDat)-MIN(ROW(rDat))+1
rCol =COLUMN(rDat)-MIN(COLUMN(rDat))+1
rInc =MOD(rRow,2)=MOD(MIN(rRow),2)
L6=INDEX(rDat,MAX(IF(rInc,IF(rDat=L5,rRow)))+1,MAX(IF(rInc,IF(rDat=L5,rCol))))
Array Enter
Regards
@Michael,
unfortunately, your array formula still seems to return wrong results (eg 3-Nov).
If data are organized like in the example, ie. looks like a calendar, the INDEX formula seems quite simple:
=INDEX($B$4:$H$15,ROUNDDOWN((L5-B4)/7,0)*2+2,MOD((L5-B4),7)+1)
Yours is effectively the same as what I just came up with, and I believe this is the optimal answer to this particular problem.
My solution, before I saw yours:
=OFFSET(B5,QUOTIENT(L5-B4,7)*2,MOD(L5-B4,7))
OFFSET will work for an arbitrary list size, but INDEX might be easier to read.
QUOTIENT does the round and division in a single step.
If there's an improvement over Elias's solution then I for one can't see it.
Perhaps a non-CSE version which would also mean that only two references (B4:H14 and B5:H15), as opposed to three (B4, B4:B14 and and B5:H15), would require manually amending should the data range change, i.e.:
=SUMPRODUCT((ISEVEN(ROW(B4:H14)-MIN(ROW(B4:H14)))*(B4:H14=L5)*B5:H15))
I suppose we could make it a single, uniform range reference:
=SUMPRODUCT((ISEVEN(ROW(B4:H14)-MIN(ROW(B4:H14)))*(B4:H14=L5)*OFFSET(B4:H14,1,,,)))
which might be more appropriate should we e.g. wish to use a Defined Name for our range, i.e.:
=SUMPRODUCT((ISEVEN(ROW(Rng)-MIN(ROW(Rng)))*(Rng=L5)*OFFSET(Rng,1,,,)))
though whether that compensates for the extra, volatile function call is something to be debated.
Regards
I have tried something and then my Excel workbooks got shut down. Maybe that was too much?
Anyway here is what I've tried:
=SUMPRODUCT(INDEX(B5:H15;IF(ISEVEN(ROW(B5:H15));ROW(B5:B15)-ROW(B5)+1);{1\2\3\4\5\6\7}))
Guess that was wrong? Would this approach work anyway?
Looking forward to learn something from you Excel Experts.
Sorry, I haven't took notice of XOR LX's answer. I guess that's kind of what I was looking for.
@Michael Avidan
As it stands that is not a very rigorous construction.
You say "I, myself, always try to provide a global Formula that is capable to handle all sorts of data", which is a wonderful philosophy, but isn't it at least as important that we ensure that our formulas are independent of the row and column references of the data range in question, so that, should that range change, we do not have to re-work our solution?
What happens with your formula, for example, if RNG is instead re-located one row down, from B4:H15 to B5:H16?
When a formula is reliant upon the addition/subtraction of certain constants within the formula, which themselves are necessarily dependent upon the specific rows/columns in which the data lies at any given time (e.g. the -1 in MOD(ROW(RNG)-1,2)), then that formula is not a very flexible one.
Hence the reason for my choice of a slightly longer construction:
ROW(B4:H14)-MIN(ROW(B4:H14))
which ensures that this part of the calculation is not dependent upon the precise location of the data range within the worksheet, and so will give correct answers even if that range is re-located.
Regards
{=OFFSET(B4,MAX((B4:H15=L5)*ISODD(ROW(1:12))*ROW(1:12)),MAX((B4:H15=L5)*ISODD(ROW(1:12))*COLUMN(A:G))-1)}
Non-array formula:
=INDEX(B4:H15,SUMPRODUCT((B4:H15=L5)*(ROW(B4:H15)-ROW(B4)+1)*ISODD(ROW(B4:H15)-ROW(B4)+1))+1,SUMPRODUCT((B4:H15=L5)*(COLUMN(B4:H15)-COLUMN(B4)+1)*ISODD(ROW(B4:H15)-ROW(B4)+1)))
Using one range (B4:H15), one reference (B4), one lookup value (L5) and no INDIRECT or OFFSET.
My trial with defined names:
DateRange
=$B$4:$H$4,$B$6:$H$6,$B$8:$H$8,$B$10:$H$10,$B$12:$H$12,$B$14:$H$14
Position
=RANK('lookup problem'!$L$5,DateRange,1)
L6
=OFFSET(B4,ROUNDUP(Position/7,0)*2- 1,IF(MOD(Position,7)=0,6,MOD(Position,7)-1))
I'd probably just run with something like:
=SUMPRODUCT((B4:H14=L5)*(MOD(ROW(B4:H14),2)=MOD(ROW(B4),2))*B5:H15)
...which is basically the same as Elias' but without the IFs
The opposite of elegant but it works...
=INDEX(B4:H15,IFERROR(MATCH(L5,B4:B14,0),0)+IFERROR(MATCH(L5,C4:C14,0),0)+IFERROR(MATCH(L5,D4:D14,0),0)+IFERROR(MATCH(L5,E4:E14,0),0)+IFERROR(MATCH(L5,F4:F14,0),0)+IFERROR(MATCH(L5,G4:G14,0),0)+IFERROR(MATCH(L5,H4:H14,0),0)+1,IFERROR(MATCH(L5,B4:H4,0),0)+IFERROR(MATCH(L5,B6:H6,0),0)+IFERROR(MATCH(L5,B8:H8,0),0)+IFERROR(MATCH(L5,B10:H10,0),0)+IFERROR(MATCH(L5,B12:H12,0),0)+IFERROR(MATCH(L5,B14:H14,0),0))
=INDEX(B4:H15,
IFERROR(MATCH(L5,B4:B14,0),0)+
IFERROR(MATCH(L5,C4:C14,0),0)+
IFERROR(MATCH(L5,D4:D14,0),0)+
IFERROR(MATCH(L5,E4:E14,0),0)+
IFERROR(MATCH(L5,F4:F14,0),0)+
IFERROR(MATCH(L5,G4:G14,0),0)+
IFERROR(MATCH(L5,H4:H14,0),0)+1,
IFERROR(MATCH(L5,B4:H4,0),0)+
IFERROR(MATCH(L5,B6:H6,0),0)+
IFERROR(MATCH(L5,B8:H8,0),0)+
IFERROR(MATCH(L5,B10:H10,0),0)+
IFERROR(MATCH(L5,B12:H12,0),0)+
IFERROR(MATCH(L5,B14:H14,0),0))
Named Range
rownum = SUMPRODUCT(('lookup problem'!$B$4:$H$14='lookup problem'!$L$5)*ROW('lookup problem'!$B$4:$H$14)*ISEVEN(ROW('lookup problem'!$B$4:$H$14)))
Formula
=OFFSET($A$1,rownum,MATCH(L5,INDIRECT("$B"&rownum&":$H"&rownum),0))
How about SUM(IF(B4:H14=L5,B5:H15)) with array..it should work
Sorry, Chandoo, you can't find stuff this way in every possible scenario.
What if 2014-10-01 sales would equal 41.927 ? Which is serial number for 2014-10-15 ? SUMIF would fail to retrive correct answer. And your example data suggest that such number is possible in your table.
It's better not to search through dates and numbers at the same time.
If I'd solve a problem like this, it'd reformat table first so I get one column with dates and the other with numbers.
In this case, formula to form date column would be:
=INDIRECT(ADDRESS((INT((ROW()-4)/COUNT($B$4:$H$4))+1)*2+2;MOD(ROW()-4;COUNT($B$4:$H$4))+2;4;1))
and numbers would be the same formula with sight adjustment (+3 instead of +2 at the end of first argument):
=INDIRECT(ADDRESS((INT((ROW()-4)/COUNT($B$4:$H$4))+1)*2+3;MOD(ROW()-4;COUNT($B$4:$H$4))+2;4;1))
And now you got two columns that you can safely use for searching!
Oops, sorry, you actually mentioned that it doesn't work if number=date! I missed that part 🙁
={OFFSET(A1,SUM((B4:H14=L5)*ROW((B4:H14))),SUM((B4:H14=L5)*COLUMN((B4:H14)))-1)}
Works for all data... the solution I got for indirect looks little lengthy
I want to count last 20 records of a person, whose marks is greater than 2 and grade "manager". ....
Assume A1 has got names (James, John...etc...)
A2 "Manager"
A3 "2"
Someone please reply
I want to count last 20 records of a person, whose marks is greater than "2" and grade "Manager"
Assume A1 "geroge" A2 "Michael" A3 "George" etc...name can found anywhere in the rows
B1 "Manager" B2" clerk"
C1 "2" C2, "4"
please reply
Simplest I can come up with. No limitations for either 1 or 2. This does assume dates are an ordered list with 7 per row, and 2 rows per set. Assuming this is always true this will work for an arbitrary long list of dates.
=OFFSET(B5,QUOTIENT($L$5-$B$4,7)*2,MOD($L$5-$B$4,7))
@Marc,
Nice approach - however, as there are no "Negative Dates" - try:
=OFFSET(B5,INT(L5-B4)/7)*2,MOD(L5-B4,7))
——————————————————————————-
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
=OFFSET(B4,ROUNDUP((L5-41911+1)/7,0)*2-1,MOD(L5-41911,7))
B4 has been used as reference cell for OFFSET().
FOR ROWS:
ROUNDUP(....,0) gives the integer value of a division. In case of presence of a remainder, ROUNDUP will add 1 to the Quotient.
As opposed to ROUNDUP(), the INT() or QUOTIENT() functions eliminate the remainder.
41911 = 01-Sept-2014, the first date in the data.
*2 has been used because there are 2 columns per set of data.
/7 has been used because there are 7 columns per set of data.
For columns
MOD(L5-41911,7))
Vijaykumar Shetye,
Panaji, Goa, India
This is how i did it
{=INDEX(B4:H15, MAX((L5=B4:H15)*ROW(B4:H15))-2, MAX((L5=B4:H15)*COLUMN(B4:H15))-1 )}
Here's my solution:
=INDEX(B4:H15,MATCH(1,MMULT(--(B4:H15=L5),TRANSPOSE(COLUMN(B4:H15)^0)),0)+1,MATCH(1,MMULT(TRANSPOSE(--(B4:H15=L5)),ROW(B4:H15)^0),0))
Sorry, forgot to mention Ctrl Shift Enter is needed.