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.
37 Responses to “Quickly Change Formulas Using Find / Replace”
Chandoo,
this is a really cool stuff what I use quite often. In addtion this method also could be a good choice to switch the reference type of the formulas from relative to absolute or vice versa. (just simply replace the $ in the same way).
Andras
@Andras: you are right, we can use find / replace to change references, reference types etc. Now, only if they had regex in find/ replace, we could so much more 🙂
@Tony Rose: Thank you. This is very useful and powerful feature. I even use it for cleaning up data. While formulas are good, they are not the solution for every problem. Often when I need more powerful cleanup / changing, I copy paste the stuff to text editors like notepad++ and then use their find/replace to do the dirty task.
What if i have to change the formula from ='Analysis'!C1 to 'Analysis 1'!C1?
I tried doing it using Find /Replace but could't. Encountered some errors.
And is there a way to change this using VBA???
Hi,
Did you ever get a reply to this?
Thanks
Ollie
to make your life easier, suggest you to avoid (Space) in worksheet names whenever possible. Consider (underscore) instead.
As the first formula wouldn't have the single apostrophes (since there's no space) need to include that in replace. So, search for:
Analysis
and replace with:
'Analysis 1'
This could be the most useful tips I've seen in a while. I use this all the time and can instantly change 400 formulas with a few clicks. Like so many other functions in Excel, I don't know what I would do without this one.
Keep 'em coming!
[...] on formulas: 5 areas where mouse kicks keyboard’s butt | Edit formulas in bulk using Find / Replace | Excel Formulas Online [...]
THANKS BRO
You, sir, are a god among men...
This is really cool. Your just save me hours of work. Thanks.
Thanks so much for this fix! It saved me tons of work. I'm muddling my way through and this really helped!
Oh... My... God!
This tip just saved me about 2 hours every month! I can't believe how easy it is to use. Now, can somebody tell me who I should call to get a refund for the previous 100 hours I spent manually changing formulas cell by cell?
Thanks so much!
THANK YOU!!!
THANK YOU!!!!
You saved me hours, I had a sheet that has more than 500 formulas, and i needed to replace the year in all of them, you saved me hours
Awesome info on replacing cell addresses in formulas. I have never heard about Ctrl+` before. Thank you!
I have something inside a formula like:
=sum(A1, A2*10) all over I now need to get rid of the *10 {=sume(A1, A2)} I thought to use the find replace trick above but with a blank in the replace but it then outputs just zeros. I thought I could trick it by doing *1 but then it just turns into =*1) with none of my references. Does anyone have an idea how to do this?
The Ctrl+ trick is cool.
@T
Instead of replacing with a blank try replacing
*10)
with
)
Thank you! This literally will save me hours and hours of time, and that's without losing my sanity in the process!
I have Sheet(1), Sheet(2), Sheet(3), etc ... Sheet(100).
Then there's a summary tab where I want to recap information on all those different sheets. Is there anyway to create a formula on the Summary tab to get ='Sheet(1)'!B$29 copied down for all 100 sheets without having to change each sheet # within the formula by hand?
@Brigitte
If you have a list of the sheet names in A2:A100
In B2: =INDIRECT("'"&A2&"'!$B$29")
Copy down
or if you don't have a list of the sheets names you can make it up on the fly
=INDIRECT("'sheet("&ROW()-1&")'!$B$29")
Copy down
Thanks for the suggestion. However, I copied your formula right back to my file and it didn't work. So I did it another way. I put the tab/cell reference in one cell and then did an =INDIRECT() to capture that information.
K2="'Sheet("&L2&")'!B$29" which has a value of 'Sheet(1)'!B$29
B2=INDIRECT(K2) which now has a value of 40 (contents on Sheet(1).
Thank you!!!!
Thank you ..
Hi, Out of all the formulae, I wish to replace the formula which has generated 0 value with blank space? I am unable to do it with find and replace function,
Please suggest.
Thanks.
Chandoo, you literally just saved me about 2 hours of work. I had a document with a daily report in two formats. The second formate just linked to all the appropriate cells in the other format (different sheets). This was 180 references that needed to be changed and I had to make this for a 4 week period (aka 28 different sheets at 180 references to change per sheet).
Thanks so much.
I have tried this way and without using the Ctrl-` formula view
Either way, I am trying to do something simple, but it won't let me.
I have a bunch of cells with a simple math formula like
=-(0.5*20)
various values in each cell, multiplied by 20
I simply want to change the multiplier globally from 20 to 25. But when I tell it to find *20 and replace it with *25, it replaces the entire cell contents with *25, rather than just replacing the *20 portion of the cell contents.
Can anyone assist with this? Seems so simple, but Excel isn't letting me do it.
Search/Replace 20 or 20) with a cell Reference eg A1 or A1)
Then put the value 25 in A1
By using a * in the search it replaces all the text
how to find a specific cell's value in a column & replace replace it with another cell value i actually need a method to replace a data in ca column and replace with the value i have in a specific cell can i give a [ location ] of data to what i need to find and then give row or column range to where i need to find and the given value & then give a [ location ] of data to what i want to be replace with the find and replace by row & column range & than by specific criteria and than by specific location.
please help.
how to find a specific cell’s value in a column & replace replace it with another cell's value.
i actually need a method to find a specific cell's data in a column and replace it with the value i have in a specific cell.
can i give a [ location ] of data to what i need to find and then give row or column range from where i need to find the given value & then give a [ location ] of data to what i want to be replace with.
find and replace by row & column range & than by specific criteria and than by specific location.
please help.
how to find a specific cell’s value in a column & replace it with another cell’s value.
i actually need a method to find a specific cell’s data in a column and replace it with the value i have in a specific cell.
can i give a [ location ] of data to what i need to find and then give row or column range from where i need to find the given value & then give a [ location ] of data to what i want to be replace with.
"find and replace by row & column range & than by specific criteria and than by specific location."
in more than 100 sheets in entire workbook
please help.
This is a great tool, does anyone knows an easiest way??
I'm working with a system that has over 59000 references... so every time the replace all is activated. I lose an entire day.
i actually needs to find cell number "D12" in column "D" and replace with Cell Number "B8" for example
find what = Cell Number "D12" John McNamara
find Where = in Column "D"
Replace with = Cell Number "B8" Bieber D'Souza
Replace Range = Column "D"
In which Sheet = All Sheets in Work Book (more than 100 Sheets)
Note: in every Sheet Cells Number "D12" & "B8" containing Different Employ Name but the find rang and replace rang are same in every sheet and find what cell number and replace with cell number are same also.
please help!
thank you. saved lot of time.
Thank you from the bottom of my heart!
Hi, I am trying to figure out how to use RE to find and replace several values in a column. Using find and replace does not work because of the values I am working with. I have a column with hundreds of rows that have a description of several operating systems and other info, which looks like this: Windows Server 2008 R2 Member Server Security Technical Implementation Guide; Windows 2008 Member Server Security Technical Implementation Guide; Solaris 10 10 SPARC SECURITY TECHNICAL IMPLEMENTATION GUIDE; and Windows Windows 2003 Member Server Security Technical Implementation Guide.
I need to be able to find and replace (or basically curtail the descriptions) to be Windows 2008 R2; Windows 2008; Windows 2003; and Solaris 10. BUT when I run find and replace with just *2008*, it finds every instance, including the ones with R2 at the end. I need it to only change the ones with 2008 to Windows 2008 and the ones that have 2008 R2 to Windows 2008 R2. I know it is possible, but I have no clue on how to write a macro to do this.
Thanks for your help,
Gerard
Wickedly efficient workaround. Excel really is a powerhouse program, all you have to do is dig into it. Ctl ~ exposes the formulas, and Ctl H allows for the multi edit. Brilliant, Chandoo!