Yesterday I have learned this cool excel charting trick and I cant wait to share it with you all.
The problem: I have too many charts & want to show one based on selection
You have made 3 charts to show your company performance in the last 8 years. But you don’t want to clutter the project report with all of them. You would rather want to show one chart and let user choose to see the any of the other two, like this:
The Solution: Use INDIRECT() and a nifty image hack
- First, create your charts in a separate worksheet like this (remember you need to create all 3 charts first)
- Once the charts are created adjust the width and heights of 3 cells and place one chart in each like above.
- Now, go back to the sheet where you want to control the display, and define a new named range. Lets call it getChart. You can define new named ranges from menu > insert > name > define. You will see a dialog box like this (right):
- In the “Refers to:” area we will now write an INDIRECT() spreadsheet formula to refer to one of the 3 cells where charts are placed. A sample formula is below:
IF('View them here'!$C$2="Sales",INDIRECT("'Place your charts here'!F11"),IF('View them here'!$C$2="Expenses",INDIRECT("'Place your charts here'!F12"),INDIRECT("'Place your charts here'!f13"))) - The above formula assumes, you are going to control chart display thru cell C2 in the sheet ‘view them here’
- Now adjust a cell’s size in this spread sheet to be big enough so that we can fit the selected chart.
- Go to Menu > Insert > Picture > From File and insert any picture. This is just for a placeholder purpose, so any picture would do, including that of your cat’s. 🙂
- Finally, select the image and go to formula bar and type
=getChart(or whatever name you gave to the named range), like this:
- Change the value in C2 and see the magic.
Now, go back to the sheet where you want to control the display, and define a new named range. Lets call it getChart. You can define new named ranges from menu > insert > name > define. You will see a dialog box like this (right):
How this hack works?
In excel you can assign named ranges to images inserted in the sheet. So when you adjusted the cell sizes in the sheet with charts and created indirect references through INDIRECT() formula and used it in the named range, excel fetched the content of the cell (the chart) and replaced your cat’s picture with that. This powerful little trick can help you make interactive dashboards within little space.
Pretty cool, eh?
Download and see in action
Here is a link to the downloadable conditional chart display workbook. I have tested this in Excel 2003, but I guess it should work the same way in most of the modern versions of excel. Feel free to drop a comment if you see this not working in a particular version.
191 Responses to “Select & Show One Chart from Many”
Chandoo,
you don't have to insert a picture to create an object for 'getChart'. Excel has a tool for this called Camera Object. It is not available via the Excel menus (and it is not documented in the help files). You first have to add the icon to one of your toolbars. Right-click on one of your toolbars and select customize and the commands tab. From tools, select Camera, and drag it to the toolbar. Clicking on the camera object takes a snapshot of the cell range you selected. You can then replace the cell range (the camera is linked to) by your range name 'getChart'.
The Camera Object is a great tool for creating dashboards. Charley Kyd over at http://www.exceluser.com provides a lot of information and tutorials about this technique.
Chandoo,
i just tried this on Excel 2010 and it's saying Reference not valid.
please advise.
amc
@AMC
Can you advise the whereabouts it is falling over?
Chandoo,
just to detail the issue:
1. when i click on the picture and place =getChart on the formula
it is saying that "Reference not valid" error.
amc
i'm not sure because using INDIRECT() usually means referring to a cell but when i use that it says the "Reference not valid error".
please help.
Chandoo,
just to detail the issue:
Hi, I also experience the same thing - reference not valid, when trying to input the =getchart onto the image.
Hi, I got the same thing too. With the reference not valid. But i found a way to fix it. Use the same formula, but remove "Indirect"
Example:
IF('View them here'!$C$2="Sales",("'Place your charts here'!F11"),IF('View them here'!$C$2="Expenses",("'Place your charts here'!F12"),("'Place your charts here'!f13")))
This should work, that's what i did to get it working
It is my pleasure to know your website. Thanks. Sam RYU
[...] again, I rely on Chandoo for inspiration for a charting tutorial. In Select and Show One Chart from Many he shows how to let a user choose which of several charts to show, in an interactive display. He [...]
Chandoo -
Nice post, nice little trick. Internally it works just like the Camera Tool, except the camera tool by default applies a white background and black border to the shape containing the picture.
If you're showing a picture that may change dynamically, it's the only way to go. The problem is that it sucks down resources, and using it a dozen times on a fancy dashboard report may result in a sluggish interface. Since it's only chart data that's changing, there's an easier way to make this work, and I've gone ahead and posted it on my blog in Display One Chart Dynamically and Interactively.
Thanks as always for the inspiration.
@Jon
Nicely done. (I was thinking about the same approach, but using offset formula and named ranges so that the source data range could change and the graph would update automatically.)
@Chandoo
There seems to be a problem with dynamic pictures when it comes time to printing - that is pictures created with the camera tool or with [Copy Picture] which have a live formula behind them (kind of like what you have there). I can't recall the exact details of the print problem - except the the layout gets screwed up - but it goes away when you remove the formula from the picture. My workaround has been to write a small piece of vba code which sets the formula for the picture (forcing the image to change) and then deleting the formula from the picture again to leave it 'static'. I am sure you can imagine how to write that code from the change in a dropdown selection! (in fact you have already written it, I expect)
@Alex
I used to always use offsets and named ranges, and scoff at such simple approaches as the one I posted about. But as I mature, I've learned that reliability and maintainability trump elegance every time.
The problem with Camera Tool objects is that sometimes the formatting of the object shown in the dynamic picture may be completely trashed. This sometimes seems to bee caused by one of my favorite culprits, that is, a printer driver. I don't recall the details, but I worked with another expert to try to fix such problems a couple of years ago, and updating the printer driver made the problem go away.
Jon,
I have used the name approach to show my charts in my software, however after closing down the software and reopening most of charts will not show unless I go the sheet the charts are on and click on a cell. Once it is working it works great but I need it to to work without having to click on the chart sheet.
Any idea's?
Jon, a bit late, definately something Chandoo could do with adding to his templates!
Private Sub Workbook_Open()
Calculate
End Sub
Seems to do the trick...
Ooops, Henry even!
@Robert: thanks for pointing the camera tool. Fortunately I am aware of this little feature in excel. Only that I didnt try that route.
@Sam RYU: Awesome, I am happy you enjoy this site 🙂
@Jon: Thanks, I really cherish this comment and I mean it.
When I first used it I have realized the sluggishness and poor response immediately. But I thought it was due to several other programs running or something like that. Looks like excel might be trying to constantly update the image formula (the way it does normal formulas) and this is a costly operation.
I have seen your post and I like the idea of keeping chart manipulations to formulas alone. I use that technique all the time 🙂
@Alex: even though I didnt test printing, I saw print previews in Excel 2003 and they are looking fine. But as Jon already mentioned I might be having right drivers.
I agree that writing formula once and leaving the image intact is a good solution. But I didnt want to complicate the tutorial that much as it already has enough steps to confuse readers 😀
Also, do you think by changing the formulas to circular reference formulas might help, that way, we only update the formula when the original indirect reference changes from the new one... ?
I didnt test it though as it might take forever to tweak the circular formulas... and they are even more difficult to explain to others.
Chandoo -
Circular references certainly have their place, but not in a workbook designed for general purpose use. People don't know what the error means, they become alarmed, and they don't use the workbook, because they think it's broken.
Whenever I've tried using circular references on purpose, even following someone else's cookbook approach, I get tangled up. They are remarkably hard to develop, debug, and use reliably.
@Jon: Agree... circular formulas are like recursion or multi-threading or reg-ex. You need different perspective to understand and extract the full power of them.
I have used them in the deal or no deal excel game (http://chandoo.org/wp/2008/10/03/download-excel-deal-or-no-deal-game-play/) posted here a while back and they were necessary as the only other option is to use vba.
But like you, i try to avoid using them in any sheets that are shared with others.
By the way, it's not necessary to use a single cell for the linked picture trick. You can use any contiguous rectangular range.
A very nifty trick. I'll start using it today!
I think one key advantageof this technique over a single dynamic chart is that you can easy switch chart types.
Oh Mr Pointy Head Dilbert! - this is just a brilliant BRILLIANT tutorial. Many thanks indeed. I will put your tutorial into practice as soon as possible. Bye for now Mr Chandooooo.
denise
[...] link [...]
Hi Chandoo..
This is simply great as usual. However, i need ur help to resolve one issue. Its that I have around 23 charts to display in a particular place but "IF" command can be used limited times (i guess around 7 or 12).. can we use anyother formula instead of "IF" or do you have any other solution??
Cheers
@Chandoo--- it is fundoo idia...
@John--- thanx for giving hint of camera..
I had used one more tool-- the horizontal scroll bar for changing the slides -- like in power point...
Enjoyed !
Waseem -
Put your list of items to choose from in a column (e.g., E1:E23). Use this range in the list source of the data validation for the cell containing the selected chart. Use this for the Refers To formula for getChart:
=OFFSET('Place your charts here'!F10,MATCH('View them here'!$C$2,'View them here'!$E$1:$E$23,0))
@Denise, @Justin, @Ketan: Thank you 🙂
@Waseem: As Jon suggested you can use offset and match() to solve this.
another way I can think of is:
instead of naming the charts in data validation as Sales, Expenses and Profits, you can name them as 1 sales, 2 expenses 3 profits.
Then you can extract the number using left() and find() and feed this to indirect().
Let me know if you still having difficulty making this work.
Hi Chandoo,
Firstly thanks for this fantastic chart, you offered to use, However,I have the same issue as Waseem mentioned above, i have almost 15-16 charts to show but while entering the formula in "refers to" area. this is restricted to limeted character, which means i can not use more 4 charts.i also tried to "OFFSET" formula but this will show the entire charts in a single time.
Need your kind attention, please help me out.
Thanks!
@ Jon RE: Offsets and Named Ranges
I'll concede the simplicity/maintainability factor.
Unfortunately, I find myself in the mode of developing tools for users who won't understand even the most simple mechanisms. I am trying to develop functionality which is to some degree "user-proof", so things like dynamic extensible ranges are crucial. Guess I'm stuck in that mindset.
Hi,
A little of topic, but, thinking about dashboards / KPI reports you could use indirect to reference images or drawing objects, or even create the plot matrix thingy (from last week I think) using smileys or some other icon set.
Good for corporate division logos on standard reporting format spreadsheets too.
@ Chandoo & Waseem==
Use one counter cell and offset command. Using these, one can enjoy 'n'th no. of slides...
Try it ! Gud Luk !
@Justin: You are absolutely right. Actually that is the first thing that came to my mind when I learned this trick. I will probably write another tutorial on how you can use this image hack in dashboards.
@Ketan: Thanks for sharing the idea with us. 🙂
[...] blog runs down the details of this nifty Excel trickery, which uses a formula and an image to conditionally show or hide the chart—neat stuff sure to impress the [...]
[...] uses a formula and an image to conditionally show or hide the c&#...—neat stuff sure to impress [...]
Chandoo great post really helpful. Do you have a youtube type video tutorial for this as it is a rather complex trick?
[...] blog runs down the details of this nifty Excel trickery, which uses a formula and an image to conditionally show or hide the chart—neat stuff sure to impress the [...]
@Mountain: Thank you so much for the words. I am sorry, but I do not have any video tutorial to make this simpler to understand. Did you try downloading the workbook provided? It might help answer few questions you may have.
Also, feel free to ask your doubts, we have a small but very passionate community here who would like to help each other to learn and become successful.
Welcome to PHD blog 🙂
[...] blog runs down the details of this nifty Excel trickery, which uses a formula and an image to conditionally show or hide the chart—neat stuff sure to impress the [...]
Any instructions on how this should work in Excel 2007? I've fumbled along but I can't get it to work. I've even downloaded the example spreadsheet which works in 2007 but I still cannot create one. A couple of things:
- There seems to be some data validation in the example spreadsheet but no mention of it in the instructions.
- For some reason, I cannot access the formula bar when I have the picture selected in my work. I can in the example spreadsheet which I find odd.
I really want this to work since I have several spreadsheet that this would be perfect for.
@Brodiemac: Welcome...
creating a data validation list in excel is simple. You can find more about creating one here:
http://chandoo.org/wp/2008/08/07/excel-add-drop-down-list/
Also, you can edit the data validation in the downloaded excel by selecting the dropdown list cell and going to menu > data > validation
and adding your own criteria like operating margins etc.
Also, did you try saving your workbook in excel 2003 formats and then accessing the formula bar?
Unfortunately I donot have excel 2007 to replicate your problem. Hence I request any of the readers to volunteer and clarify Brodiemac's doubts.
Brodiemac,
with Excel 2007 use Insert|Object instead of Insert|Picture. Everything else works as Chandoo described in the post with Excel 2007 as well.
I cannot name a picture in Excel 2007. brodiemac mentioned that but I am having the problem too. With a picture selected I cannot type in the formula bar.
Is there any way to do this without saving the file as a 2003 document?
Nevermind, insert object did it, thanks
@Chandoo: "Unfortunately I do not have excel 2007 to replicate your problem."
Unfortunately?? I've spent about a day and a half this week trying to make a chart that works fine in Excel 2003 work at all in 2007. It's been a process of:
1. Hmm, that doesn't work.
2. Let's try this instead.
3. Good, now it works, but...
4. This doesn't work either.
5 Go to 2.
Among the problems, if you're following t home:
1. In Excel 2007, XY charts don't work very well on charts with line chart date axes.
2. In Excel 2007, ActiveSheet.Shapes(ActiveSheet.Shapes.Count) isn't the last shape you've added.
3. Some complicated Excel 2003 charts lose their custom (linked to cells) data labels when opened in 2007.
4. In a complicated Excel 2003 chart, which has two 4-point area chart series to highlight a background range, and an XY series with custom markers (pasted shapes) and more then 4 points, only the first four points appear with their custom markers, though the code that applies the markers does not fail, and the data labels for the marker-less points do in fact appear.
And those were just the issues from this morning.
@Jon.. you can hear me laughing out loud if you are somewhere near Seattle... 😀
In “Unfortunately I do not have excel 2007 to replicate your problem.” , it is unfortunate that I couldn't help him.. not because I don't have excel 2007. I am actually scared of even trying it after all these posts and remarks from you guys 😀
@Robert: Awesome... thanks for the quick comment.
@Brodiemac: did you try it?
@Robert "with Excel 2007 use Insert|Object instead of Insert|Picture. Everything else works as Chandoo described in the post with Excel 2007 as well."
That worked, thanks.
@Jon Peltier "=OFFSET(’Place your charts here’!F10,MATCH(’View them here’!$C$2,’View them here’!$E$1:$E$23,0))"
I still can't get this to work. Excel complains that it's missing the Cols parameter.
Chandoo, don't be scared of 2007. I really like it a lot. The menu structure takes a few hours to get used to but now that I know it as well as previous versions, I can actually get things done much faster now. Trying to follow instructions written for older version can be a bit confusing though (this page, case in point).
Hi Mr PHD,
I attempted to make some changes to strive for a solution which is more "elegant"... please check your mailbox.
However, it's still a neat trick - naming charts/images as a range...
brodiemac -
Try this. I posted without testing. If I'd tested, I'd havee caught the eproblem in two seconds and fixed it in three.
=OFFSET(’Place your charts here’!F10,MATCH(’View them here’!$C$2,’View them here’!$E$1:$E$23,0),0)
BTW, I'm not at all scared of 2007, but it frustrates the you-know-what out of me. It will seem to be working fine, and I'll forget that it's a different animal than 2003. Then one day I'll have three or four hours when nothing goes as expected, nothing goes the way it used to, things are upside down and backwards.
Chandoo - I'm far from Seattle, at the other end of route I-90 almost in Boston. But I'll fill in the sound of laughter.
[...] Long long standing ovation to Pointy Haired Dilbert for the cute little Excel image hack permitting display of a chart selected from a set of charts, and a nod to Lifehacker for passing the tip along. Coincidentally, I’d been itching to roll [...]
@brodiemac: "Chandoo, don’t be scared of 2007. I really like it a lot..."
I am actually purchasing it this thanksgiving. Hopefully I will be using it when I go back to India by Christmas.
@Frederick: Thank you so much taking time to prepare this. I have uploaded it here:
http://chandoo.org/wp/wp-content/uploads/2008/11/select-chart-to-display-offset.xls
@all: Please feel free to download Frederick's version and see if it helps your situation.
[...] Select & Show One Chart from Many (38 comments) [...]
Firstly thanks Chandoo for such a great site.
Trying to create a dashboard from multiple data sources. Had problems with the charts as pictures resizing on print preview. Anyone with a workaround or suggestion as to how to overcome?
Thanks.
J.
@Jim: Welcome to PHD and thanks for the comments.
Can you explain what kind of resize problems you are having. Ideally, printing should be more or less same as what you have laid out on the spreadsheet. May be you can post some screenshots so that we can understand what problem you are having.
Hi Team,
I'm trying to call more than 4 charts by using the If condistion in reference, but it's not working. Is their any alternative way for call more charts
Hi Team,
Is their any alternative way to call more than 4 charts.
Thanks
Venku
@Venkateswarlu : the IF() formula should work all the way up to 7 conditions (thus 8 possible values) There might be some other reason why your chart is failing. Did you define the named range properly?
Also, you can try using CHOOSE() formula instead of IF().
Using Excel 2007, I need a chart/graph that has the following - looks like an upside down T:
x- axis, numbered from left to right (both sides positive - no negative; represents the number of drops of a solution added to an unknown buffer; left side 10 to 0 is NaOH; right side 0 to 10 is HCL) 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10; y-axis (pH) starting at 0 and going up to 14 (no negative values). I can graph a single titration curve on a graph but don't know how to do both and label them as directed (the upside down T). I am told that this is possible but the professor will not demonstrate it.
Thanks.
@Michael: Welcome to PHD and thanks for your comments.
You can format both negative and positive numbers as positive numbers using this custom formatting code:
0;0select the horizontal axis and right click and select format axis. in the number tab, select "custom" as category and in the format code write "0;0" to get the desired effect.
Visit this post learn more about number formats: http://chandoo.org/wp/2008/06/16/formatting-numbers-in-excel-few-tips/
[...] technique is much simpler than dynamic charts using drop-down lists and INDEX formula idea presented earlier. All you need to do [...]
Hi Chandoo!
I love this page... very interesting stuff!! And great job of explaining things...even I got through it alright 😉
So, like a few others, I have quite a few graphs to show (13). I've gotten everything to work smoothly for a a portion of the graphs, but now that I've added more graphs, I realize that I've reached my "If" limit.
It seems like the offset thing would work well for simple graphs, but my graphs are damn complicated (3D and all sorts of funky aside from that) so I think it would take months to get all the different characteristics of the graphs to show up correctly.
Could you help me work out the one idea you had: "instead of naming the charts in data validation as Sales, Expenses and Profits, you can name them as 1 sales, 2 expenses 3 profits.
Then you can extract the number using left() and find() and feed this to indirect()." Maybe that would be able to work for me? Any help/explanation is much appreciated! Or maybe you have another idea?
~eq
@Earthquakes: Thank you so much and welcome 🙂
When you have too many charts to show, it might help to use INDEX(), OFFSET() and even use form controls as they are more elegant and robust to manage. Peltier provides an excellent tutorial describing this technique here: http://peltiertech.com/WordPress/2008/11/06/display-one-chart-dynamically-and-interactively/
Another option is to try renaming the data validation cell values from sales, expenses etc. to 1. sales, 2. expenses etc. and then use left() and find() to parse out the number and then pass this to INDIRECT().
So, instead of
IF('View them here'!$C$2="Sales",INDIRECT("'Place your charts here'!F11"),IF('View them here'!$C$2="Expenses",INDIRECT("'Place your charts here'!F12"),INDIRECT("'Place your charts here'!f13")))We can have
INDIRECT("'Place your charts here'!F"&10+parsed-number&""))I havent checked this function, but I assume it should work. Let me know if you face some difficulty. I will try to help you 🙂
Dear Chandoo,
Thank you for this trick. Actually this helped me in different way. I look for some ideas to make some thing like a photo database. Beacuse I'm handling so many styles details (garments) in my office. I had prepared a workbok in Excel. Second woksheet using as a databse. First sheet contais a page like report. I'm using Vlookup() to display the deatils. When I enter Style No in the cell it will display balance deatils. But I didn't had the ideatto display the picture of the styles with the details. Now it is possible. Thank you again.
Best Regards,
Maikkil Mathew
@Maikkil Mathew : Welcome to PHD and I am so happy you have found a nifty use of this trick. Thank you for sharing with us all. You have demonstrated the robustness of excel and how it can be used creatively.
Dear Chandoo:
I am also having problems with printing the dynamic chart. In my case, I either want the chart to be seen or not seen depending if I choose a certain population.
I have screen shots: the original with the chart sized appropriately, the print screen menu, and the print screen look (which when I go back to my spreadsheet the graph is also changed as well). How do I send them to you?
~fh*
@Fieldhockeystar: Welcome to PHD and thank you for asking your question.
Printing seems to be a problem when images have formulas attached to them. Earlier, AlexJ faced the same problem (4th comment from top) and it seems to be either print drivers or something unknown to be blamed.
You said image size is changing. Did you use a different image size when nothing needs to be shown?
Btw, you can upload your screencaps to either flickr or photobucket and leave the urls in comments. That way everyone can see your screenshots and someone might be able to help you 🙂
Dear Chandoo,
Vlookup() not working with this trick so I add one more column to the Database Eg: like this
----- A-------B------------C-------------D------------------E--------
-1--ID----Name----Phone No.----Photo-----------Photoref
-2--11----Rose----2345324------@@@-----=cell("Address",D1)
-----------------------------------------------------------------------------
Photo column contains photos and Photoref Column contains formula "=cell("address",D1)" for first Row and copy the same in entire column.
With the help of Vlookup() displaying all other details in first sheet. If enter IDNo. in the cell A1 it will show Name and Phone No.
one hidden / white text cell in any corner (here H10) contain formula ="'sheet2'!" & VLOOKUP(Sheet1!$a$1,range,5,FALSE) . Range is the name of range contains data in sheet2.
Now this cell (H10) shows ----- 'sheet2'!D1--- like this. It will change with the change of the ID No. in A1
Name a this formula "=INDIRECT(Sheet1!$H$10)" as "GetPic"
Now as you did, insert and select a picture and type in formula bar "=getpic"
Now the Name, Phone no and Photo will change with the IDNo in A1
I think this will help somebody looking for a photo database in Excel like me. And I think now this way will handle thousands of charts with the help of Chart ID in another column.
Best rgards,
Maikkil Mathew
@Maikkil: several thanks to you for sharing your trick with all of us. I will try this myself and will share with readers if possible. I am very happy to have readers like you 🙂
Hi Chandoo:
Ok - I've uploaded four photos: original, print preview menu, print preview, and then after print preview to showing the "scrunching" of the chart.
Any suggestions?
Thanks
~fh*
@fieldhockeystar: Thanks for taking the trouble to upload the images. I have seen them and it looks weird. Often when you switch between views excel (happens in word too) incorrectly displays few objects. Although I think this is not a consistent behavior. The important question here, is your image dimensions are changing too, or just the layout? If it is the later, then you can clean it up running formulas F9 or scrolling down (not sure if they work, as I couldnt replicate this problem)
I believe this problem is something that originates from printing / preview functionality than from our little image trick. I may be wrong....
I am sorry, but I cant really help you at this point.. If you find a solution, let us know, and as usual we will give you a donut... 🙂
Dear Chandoo,
I'm very happy to hear that you are trying this yourself and share with others. Because I couldn't upload all details such as screen shot of the worksheet etc. If you provide your email then We can give the details also. You don't need to thank me, because this is your idea I used in diferent way thats all friend.
Thanks and Best Regards,
Maikkil Mathew
[...] says you have to learn only one thing a day? So, learn how to display one chart from many, prepare a matrix chart instead of data tables or make an incell bullet [...]
Hello,
Thank you for this information. I have more than 3 charts to show so having problems in the refer to section (formula too long) - not sure how to do index() and offset(), can you help?
Instead of having to select an option from the pull down menu (expenses, sales, profits), is it possible to have a link do the same task? For example, In my spreadsheet I have a list of Departments. I want to be able to click on a department from that list and have it display the appropriate chart. Is this possible?
Ryan -
You could do this a few ways. I might choose to use a listbox instead of a dropdown, so all of the options are visible without having to pull down the list. Otherwiase it works the same.
Instead of using official links, you could use a Worksheet_SelectionChange event procedure to detect when a cell was selected, and recreate the charts with that cell's data.
Dear Chandoo, I could not manage to implement this lession properly. Could you please teach me in a simple way. I am sorry I couldnot implement it.
@Manik: Unfortunately, this tutorial assumes some basic understanding of formulas and excel tools. May be you want to download the file and see it for yourself. Also try building dynamic charts using data filters: http://chandoo.org/wp/2009/02/12/make-a-dynamic-chart-using-data-filters/
[...] alternative to camera tool is to use the image and indirect references technique we have learned in conditionally hide or show charts [...]
[...] technique you like better? This one or the Dynamic Charts using INDEX() function, Use Data Filters as Chart Filters tips? One issue I can think of with this technique is that, [...]
Hi,
I using your 'Select & Show One Chart from Many' chart and experience a problem when adding more charts.
The indirect worksheet formula doesn't allow me to ad more into the 'refer to'line - Is there a restriction in data entering?
as example:
=IF('View them here'!$C$2="Sales",INDIRECT("'Place your charts here'!F11"),IF('View them here'!$C$2="Expenses",INDIRECT("'Place your charts here'!F12"),IF('View them here'!$C$2="MFG",INDIRECT("'Place your charts here'!F14"),INDIRECT("'Place your charts here'!f13")))
can you help me
Martin
@Martin: Can you tell us a bit more on what problem you are facing when you use more conditions ?
Also, you may want to check a simpler and easier method to get dynamic charts here: http://chandoo.org/wp/2009/05/19/dynamic-charts-in-excel/
I have done everything to get select & show to work, but I can't get the graph to show up. The formula is correct, but it wont let me put anything in the formula bar when I select my picture. I'm using excl 2007.
Thanks for any help
@Glenn... Welcome to PHD and thanks for asking a question.
You should use insert > object instead of insert > picture in excel 2007. Everything else should work pretty much the same. Let me know if you face some difficulty.
Also, there is another simpler way to do this. I suggest you check this post too: http://chandoo.org/wp/2009/05/19/dynamic-charts-in-excel/
I reaaly do appreciate the info. If I had read all the previous info I would have seen the same thing you wrote to someone else. That work great.
I have about 33 graphs. Will I be able to get all this in 1 formula?
Thanks again the help
@Glenn: You are welcome. 33 Graphs.. hmm.. I guess you can use the CHOOSE() function (http://chandoo.org/excel-formulas/choose.html ). Better thing would be to use the data-filter based chart filter technique (linked in the above comment)
Chandoo,
Very nice technique. However, I can't seem to get it to work. I am trying to use this technique to build a "Progress Bar" - with 4 different images (one for each 25% increment). I want the image to change based on the value selected from a drop down field, like your version (Not Started = image 1; Just Started = image 2; In Progress = image 3; Completed = image 4). The images are GIF images. Will that work? Or, does it have to be an Excel Chart?
I was able to replace one of your charts in your demo with one of my images, and it worked. Just not in the workbook that I created. Are there any settings in Excel, that you are aware of, that would prevent this from working?
Scratch that. I figured it out. I was missing an "=" sign in the INDIRECT formula. Great technique! This helped me a lot!
Thank you for the great post. I used this technique on my KPI report but I have run into one problem. I have excel 2003 and every time I try and print excel crashes. If I remove the GetChart formulas from the picture then the file prints with no problems at all. I do not believe that it is the printer driver because either printing to a PDF writer or doing print preview both crash excel. I saw that this was mentioned above in a couple posts but did not see a straight forward solutions. Any help or ideas would be appreciated.
[...] Dynamic charts using INDEX() formula and Camera tool [...]
I absolutely enjoy the tips - tried Excel "select-chart-to-display" and could not get it to work in Excel 2007. does it work? as I cannot access the fx box to type in =getChart? Please assist. ta
[...] Auf die Idee zu diesem Trick bin ich beim Lesen eines Beitrags des vorzüglichen Excel-Blogs pointy haired dilbert gekommen. Vielen Dank, chandoo! Dieser Beitrag [...]
Hi,
While digging around in ExcelForum I came across this alternative.
http://www.excelforum.com/excel-general/713781-display-an-image-based-on-a-cells-value.html
Seems to work a treat, is possibly easier for the intermediate user to understand.
When the referenced cell contains a picture of a large chart you can add 'mini charts'.
Not sure what the processor overhead would be for a graphic intensive dashboard or what the print looks like yet though.
Hi Chandoo,
I downloaded your excellent 'conditional chart display workbook', everything works well apart from when i go to print preview, the image moves almost off the page. I've tried to adjust the properties of the image to each different option such as 'move and size with cells' etc. Nothing I’ve tried resolves this problem. Any ideas?
John
Thanks sooooo much Chandoo. I was desparately looking for it becuase I have to finalize a dashboard. Before coming here I was trying some other techniques with the help of VBA but this trick is very cool.
Thanks so much man and keep up the good work.
I'm using Win 7 Pro and MS Office 2007 Pro - and I can not get your suggested solution to work. Following your instructions all goes reasonably well until I get to the point where you say select the "cat" picture you've inserted and type in the formula bar. The formula bar does not permit me to select it and therefore I can't type in the formula =GetChart. Also tried the Camera options discussed and no success with that either. Any suggestions?
All I'm trying to do is Hide or Show a Chart based on a selection of an Option Button. I've been able to figure out a VBA Macro to hide and unhide the Chart but I stumbled onto your solution which I'm hopeful I can make work. Any help would be appreciated.
@Doug.. refer to Robert's comment on November 13, 2008 - http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/#comment-27613
"with Excel 2007 use Insert|Object instead of Insert|Picture. Everything else works as Chandoo described in the post with Excel 2007 as well."
So use insert - object and draw a cat (well, just draw a rectangle). and it should work.
Good News and Bad News
Good News - I can Insert | Object and I can now put the =getChart in the formula bar
Bad News - My chart does not replace the Inserted Object
PS on my previous post - I've NOT been able to figure out a VBA Macro ... Thus the reason I'm trying to get your solution to work.
Thanks for your help.
@Doug... ok.. here is how you can proceed..
Link the option button to a cell (usually changes the cell value between true and false). Assuming the lined cell is J10
now, assuming the chart is in cells A1:B2 and cells C1:D2 are empty (as in no borders, no gridlines, no fill colors, nothing...)
first select cells A1:B2 and insert a camera snapshot.
Now, define a named range like "ShowHideChart" and assign the formula like this =if(J10,Indirect("a1:b2"),indirect("c1:d2"))
And assign the named range ShowHideChart to the snapshot output now.
This should work...
Chandoo... No success with the latest instructions. I was finally able to get a Macro to work, so I'm abandoning your solution for this assignment. I like the potential that your solution offers. I look forward to trying it again on a future project assignment.
Thank you for your original idea and for the help you've provided over the last couple of day. I very much appreciate your efforts and time.
Ciao
Hi Chandoo
Will this work in excel 2007
Chandoo - I'm not sure I understand what "this" is in your question.
If you're refering to your INDIRECT() image hack, then yes it works in MS Excel 2007 (at least a downloaded version of your example file above works when Saved As an .xlxs format).
If you're asking if the macro I wrote works in 2007, then the answer is yes as well. The macro was written using 2007.
If you're asking what the macro is here's what finally worked for me:
Sub HideChart()
'check the value of N11 which is controlled by 2 Option Buttons, if value is 2 then unhide the charts
If Worksheets("Model").Range("N11").Value = 2 Then
'turn Manual Calculation On
Application.Calculation = xlCalculationManual
'unhide or show the following Charts
Worksheets("Model").ChartObjects("Chart 109").Visible = True
Worksheets("Model").ChartObjects("Chart 1").Visible = True
Worksheets("Model").ChartObjects("Chart 20").Visible = True
'and hide-don't show the following picture
ActiveSheet.Pictures("Picture 9").Visible = False
'check the value of N11 which is controlled by 2 Option Buttons, if value is 1 or anything other than 2 then hide the charts
ElseIf Worksheets("Model").Range("N11").Value 2 Then
'turn Auto Calculation ON
Application.Calculation = xlCalculationAutomatic
'hide-don't show the following Charts
Worksheets("Model").ChartObjects("Chart 109").Visible = False
Worksheets("Model").ChartObjects("Chart 1").Visible = False
Worksheets("Model").ChartObjects("Chart 20").Visible = False
'and unhide or show the following picture
ActiveSheet.Pictures("Picture 9").Visible = True
End If
End Sub
Hopefully one of the above answered your question. If not, let me know and I'll try again.
@Doug.. I think the new Chandoo is referring to the technique in this post. But, thank you so much for sharing the macro with us. 🙂
@Chandoo ... yes, this works in excel 2007.
So I used this on Excel 2007, thanks to the comments got it working, but had one side effect - a couple of my charts come through looking... grainy or choppy.
These are column charts and the originals look fine, but when I use this method to display them on another sheet, the columns have some kind of graphical twitch - but it doesn't impact the rest of the chart, just the columns (and the colors in the legend.)
The smaller of my charts don't seem to have this problem - wider columns, though I'm not sure that's the reason.
I wondered if this had something to do with the object I chose as an initial placeholder. As an aside, does it matter what object that is at all? Will changing it affect the file size? I noticed a significant size increase after implementing this trick and if I can redo it better to save file size, I'd like to do so.
Wayne -
Excel 2007 does an unsatisfactory job rendering pictures of charts. It's the same if you copy a chart as a picture, paste a chart into PowerPoint or Word as a picture, or export a chart as an image file. They are grainy, alternate gridlines appear different (one is crisp, the next is faint and defocused), and edges are foggy.
Jon -
Thank you. At least I know it wasn't me doing something wrong.
Hi Chandoo,
Thanks so much for this really helpful technique. I want to display my charts using this technique. However i have one complexity, instead of selecting form just one drop down list, i want to use 2 drop down lists. E.g. In the first one i want to select the region, e.g. Sri Lanka and the inthe 2nd drop down i want to select what to show E.g. Sales, Volume, EBIT etc.
I tried If AND formula with indirect, but doesnt seem to work.
Any thoughts on how i can do this.
Thanks Again.
Bijal
hey,
I have 17 graphs on one sheet.. i want to view these graphs one by one.. selecting it from drop down list... can u plss help me.. 🙂
@Bijal: You can do the following:
I suggest that you use data filters to solve this problem. It is very simple that way.
http://chandoo.org/wp/2009/02/12/make-a-dynamic-chart-using-data-filters/
@Veena: Assuming your charts are all placed in cells A1:A17, one chart in each cell
you can use my technique with slight difference
1. create camera snapshot of A1 and place it anywhere
2. Use a drop-down to select the chart you want (link it to cell B1)
3. Create a named range "selectedChart" and assign it to =OFFSET($A$1,B1,0,1,1)
4. Now assign Sheet1!selectedChart to the snapshot
5. Whenever you change the drop-down, the chart should be updated.
Let me know if you face any issues implementing this.
thanks for the great tutorial.
i got the same question like Veena.
18 graphs to be selected by a dropdown. graphs are on a seperate sheet.
i made the camera shot, the quality is not good thoug... anyhow i have problems to proceed. i would be glad if you could help please
thanks
@Tim.. Thank you for the comments. I am happy you liked this.
You should use INDEX / OFFSET to dynamically set the chart's source data. That way you can easily show one series or another. Jon Pletier shows the basic technique behind this here: http://peltiertech.com/WordPress/2008/11/06/display-one-chart-dynamically-and-interactively/
when i am printing the sheet that is attached here as a down loadable attachment, it is not getting printed, can anyone help me out in printing the charts in this way!!
I am using excel 2007 and am having trouble with the placeholder picture I inserted. When I try to type "getChart", it wont let me type in the formula bar. Any tips?
Thanks for your help!
I do not know what m i doing wrong but i can not replicate the formulaes in 2007...Tried many times but what cud i be missing...The picture link is not created...the refer to does not indicates to the relevant cell...
@ DV, image problem, Doug
.
This method is a rather finiky in its execution but it does work
.
Try the following few ideas that may help you:
.
1. Change your sheet names to have no spaces
.
2. Don't use the word "Chart" as part of a Named Formula use "Cht" or something else.
refer: http://chandoo.org/forums/topic/any-fans-of-walkenbach-2003-charts-out-there
.
3. You have to use single and double quotes very carefully
eg: I have changed the sheet name to 1 and shifted the charts to A5..A7 so try:
=IF('1'!$C$2="Sales",INDIRECT("'1'!A5"),IF('1'!$C$2="Expense",INDIRECT("'1'!A6"),INDIRECT("'1'!A7")))
.
4. Don't just copy and paste from here as the text can sometimes come in incorrectly, even though a " may look like a " it may not be a ", so retype all the " and ' characters
.
5. If you change your Worksheet name after getting it to work remember that the 3 Indirect functions in the above have hard coded names and they must be renamed manually
How can you do this same trick using a line chart with multiple series? For example, create a line chart with multiple series sales and profits or all three.
Thanks for the great tip. Once implemented it works fine until i save my charts. (i have 3). After i save the workbook and i go to select the charts from the drop down list only the blank cells behind them are shown. I have to go and click on each chart to "activate" them if you will for the drop down list to start displaying them again when selected from the drop down list. Has anyone else run into this issue?
[...] Select & show one chart from many [...]
Thanks for this tutorial, it seems like just what I need, but I am having some problems implementing it in 2010 (does it work for 2010?). I have created 3 pictures with the camera and mapped them to getCht, getCht2 and getCht3. GetCht works perfectly. The other two only work some of the time. Sometimes it will compress more cells than the one I selected into the window.
Here are my getCht formulas:
getCht: =IF(Report!$AE$9="Sales",INDIRECT("'Charts'!$B$2"),IF(Report!$AE$9="Cancel",INDIRECT("'Charts'!$B$3"),IF(Report!$AE$9="Fulfillment",INDIRECT("'Charts'!$B$4"),IF(Report!$AE$9="activation",INDIRECT("'Charts'!$B$5"),IF(Report!$AE$9="support",INDIRECT("'Charts'!$B$6"),IF(Report!$AE$9="CSAT",INDIRECT("'Charts'!$B$7"),IF(Report!$AE$9="Cost",INDIRECT("'Charts'!$B$8"),IF(Report!$AE$9="usage",INDIRECT("'Charts'!$B$9")))))))))
getCht2:
=IF(Report!$AE$9="Sales",INDIRECT("'Charts'!$c$2"),IF(Report!$AE$9="Cancel",INDIRECT("'Charts'!$c$3"),IF(Report!$AE$9="Fulfillment",INDIRECT("'Charts'!$c$4"),IF(Report!$AE$9="activation",INDIRECT("'Charts'!$c$5"),IF(Report!$AE$9="support",INDIRECT("'Charts'!$c$6"),IF(Report!$AE$9="CSAT",INDIRECT("'Charts'!$c$7"),IF(Report!$AE$9="Cost",INDIRECT("'Charts'!$c$8"),IF(Report!$AE$9="usage",INDIRECT("'Charts'!$c$9")))))))))
getcht3
=IF(Report!$AE$9="Sales",INDIRECT("'Charts'!$D$2"),IF(Report!$AE$9="Cancel",INDIRECT("'Charts'!$D$3"),IF(Report!$AE$9="Fulfillment",INDIRECT("'Charts'!$D$4"),IF(Report!$AE$9="activation",INDIRECT("'Charts'!$D$5"),IF(Report!$AE$9="support",INDIRECT("'Charts'!$D$6"),IF(Report!$AE$9="CSAT",INDIRECT("'Charts'!$D$7"),IF(Report!$AE$9="Cost",INDIRECT("'Charts'!$D$8"),IF(Report!$AE$9="usage",INDIRECT("'Charts'!$D$9")))))))))
When I select "Sales" both the 2nd and 3rd charts show cells C2:E2 instead of just C2. Same thing happens with "Cancel". When I select "Usage" then getCht2 works fine but GetCht3 doesn't. Everything else works as intended.
Any help would be GREATLY appreciated.
Hi
I’m using Win XP & MS Office 2007 Pro – and I can not get your suggested solution to work. Following your instructions all goes reasonably well until I get to the point where you say select the a picture you’ve inserted and type in the formula bar. The formula bar does not permit me to select it and therefore I can’t type in the formula =GetChart. Also tried the Camera options discussed and no success with that either. Any suggestions? I have 4 Graphs, let me know the indirect syntax is correct or not?
=IF(View!$G$2="Severity 1",INDIRECT(" 'SeverityTrend'!B5"),IF(View!$G$2="Severity 2",INDIRECT("'SeverityTrend'!B6"),IF(View!$G$2="Severity 3",INDIRECT("'SeverityTrend'!B7"),IF(View!$G$2="Service Request",INDIRECT("'SeverityTrend'!B8")))))
Will this work if the cells are merged cells?
[...] basic technique behind this is similar to the one we discussed in show one chart from many article. Since, It would be 1800 word long if I describe the process, I decided to make a short video (18 [...]
hi chandoo,
great work with the site....very helpful. i have been following this for some time and i was able to learn a lot from here.
but with the getchart thingy i am facing the same problem as Ravi Raj... i am not able to type anything in the formula bar of the picture... so i tried using a shape instead of picture but it is returning an error on the formula...same thing happens if i use another chart instead of a picture..please help
regards,
N Ramakanth
Hi Chandoo,
Great post! Thanks for all of your sharing, which makes me know more about chart besides building basic ones as to Excel help.
Furthermore, I've done the above example by another way, using CHOOSE and MATCH. I will creat another two columns containing the chart data, which link to the dynamic chart. Then these two columns use the syntax of CHOOSE and MATCH, which links to the raw data of sales, expenses and profit. Also, I've added draw down list as you mentioned above. (Sorry, I can not attach my homework here, so just explain in words here) Then such can also works!
Thanks again for your tricks.
Hi Chandoo
I got an issue with "Select & Show One Chart from Many" so I tried to figure it out my self and went through your instructions but yet I couldnt. Then I thought to leave a comment, when I saw the date of first comment I was posted, I got little afraid.. whether I can continue on this subject. But fortunately this discussion seems still going on.
I'm using excel 2007, I have done same chart using "IF" function and "Choose" function. Fortunately, both the way it is working for me. But the issue I'm having here is when I close my excel and re-open it doesn't work. However, when I open Name Manager , click on the formula given (from name range) and close it works again. Pls advice me how to tackle this.
Thanks in advance
Thnx for the site..... really helped me a lot.
I am trying to use this function but as mentioned by Mr. N Ramakanth & Mr. Ravi Raj, i am unable to type in formula bar after selecting the image.
Thanks
Hi Chandoo
I am using Excel 2000.
I have 3 dynamic charts (dynamic range) on a seperate sheet "in" seperate single cells.
On the main worksheet I use option buttons to choose from the charts.
I did everything like is said, define name, insert pic, type =getChart, click on the option buttons and nothing happens. The charts work fine on the worksheet.
my formula in the define name:
=if(Calculation!$D$3=1;INDIRECT(Munka1!$B$3);IF(Calculation!$D$3=2;INDIRECT(Munka1!$B$4);INDIRECT(Munka1!$B$5)))
where D3 is linked to the option buttons, and equals 1,2 or 3.
Any suggestions?
thank you
@Balint
A few things
I am assuming you are using a non-english version of excel as the ; in the If should be , in English versions
.
The contents of the Indirect requiring to have " around them.
.
Insert a JPG from outside excel to add your formula to
.
I would change the If statement to a Choose like
=CHOOSE(Calculation!$D$3,INDIRECT("Munka1!$B$3"),INDIRECT("Munka1!$B$4"),INDIRECT("Munka1!$B$5"))
.
or
=CHOOSE(Calculation!$D$3;INDIRECT("Munka1!$B$3");INDIRECT("Munka1!$B$4");INDIRECT("Munka1!$B$5"))
as your language requires
.
Select the newly imported JPG and then in the Formula Bar enter =getChart,
.
But it is working for me with the above items checked
@Hui
Thank you very much for the fast answer. Yes, I am using a non-english version and this is what got me in trouble.
In my language If() needs ; and not ,
A worksheet reference doesn't need the ' only ! at the end
BUT
The INDIRECT does need the " around them.
Now it works fine even with the IF().
Thanks again!
Can you have two sets of Option buttons on one worksheet?
Option button 1, 2, 3 linked to one cell and 4, 5, 6 linked to an other.
I would like to have a dashboard with two chart 'boxes' where you can select 3 charts for the first box (just like in the post), and an other set of 3 for the second box.
@Balint
Yes
Simply add a Group Box Form Control to the sheet and drag it over the 3 Option Box's you want linked
Then add 3 more Option Box and another Group Box to them
@Hui
oh, that's it?
Pretty simple indeed 🙂
Thank you!
I had problems when using 2010. It did not allow me to name the picture.
I downloaded your example and copied the picture and pasted it into my excel sheet. After excel asked me if I want to import the picture, Wow!! It displayed my charts!!!
I am not sure if I was experiencing the same problem as the others on this page, but if I have, it seems that this would be the fix.
Hi,
my name is Anita and I visited this Website few days ago. I learned so many new things from here. Now, I am trying to create a chart "Select & Show One Chart from Many"but not able to do this. Below is the link for this post from your website.
http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/
Whenever I try to define the name in cell nothing happens.. can someone help me??? I use excel 2007.
best regards,
Anita
Had problems for naming picture in Excel 07, but working fine in Excel 10
[...] Med Google vid min sida fann jag lösningen ”Select and Show one Chart from many” Källa här – en tabell där data ändras genom val i en rullgardinsmeny. Jag försökte förstå mig på [...]
Hi Chandoo,
I applied your trick and it has worked out well. The only problem I could see is the update of the original chart in the single cell we defined. Every time new data was updated, the original chart in the single cell did not update automatically unless you have to click on this original chart. Any idea how to have it update automatically when new data added? Thanks
Hi Chandoo,
From Comment (4), November 6,2008, by AlexJ :
".........My workaround has been to write a small piece of vba code which sets the formula for the picture (forcing the image to change) and then deleting the formula from the picture again to leave it ‘static’. I am sure you can imagine how to write that code from the change in a dropdown selection! (in fact you have already written it, I expect)......."
I've been trying for weeks to write such a code with no success. It would certainly help overcome the sluggishness caused by assigning a formula to the image. When I manually delete the formula, the selected chart stays and the sluggishness goes away completely.But without the formula no other chart can be selected.
Any help will be greatly appreciated.
Hey Chandoo - excellent solution! I was tearing my hair out trying to setup Excel templates to mimic an arrangement with dropdowns in our Word templates (that way our people only need to learn one new step and not different arrangements between Word and Excel). When I came across your 'trick', I was so relieved! However, I had one sticking point...I couldn't get the picture naming step to work. I'd even set up the arrangement in line with your steps, which worked fine. Tried all sorts of workarounds to try to get the picture to hold the =getChart name, until it finally dawned on me that in my 'cleverness' I had included an extra cell in my dropdown list so that a instruction of 'Select Branch' was displayed as the title of dropdown list...but, of course, there was no corresponding graphic to display, as 'Select Branch' (the title) wasn't mentioned in the Name formula! So simple!! All I needed to do was to change the dropdown to an entry that had a graphic (I'm displaying graphics rather than charts), then apply the =getChart function (or, in my case, =SelectBranch) and that way the graphic would hold the function. From that point, I could choose whatever graphic I liked via the dropdown list and, when I set the dropdown list to show 'Select Branch', the graphic displayed is the last one that had been displayed prior to my choosing 'Select Branch' in the dropdown Hope this makes sense? I got there in the end! For anybody else who might think it's clever to include the instruction for using the dropdown, make sure you choose an entry from the dropdown that has a corresponding graphic/chart BEFORE applying the =getChart function to the graphic. Thanks again for your excellent advice, Chandoo!
Dear Chandoo et al,
Thank you for this fabulous technique! I am using 2007 and got the Insert>Object trick to work for an IF,INDIRECT formula but ran into the 7 Chart limit. I also had trouble getting all of my charts look the same size so I had to change tactics.
Using the sample spreadsheet submitted by Fredrick 3 years ago (Nov. 14, 2008!) I copied his technique of only using one chart and having the data dynamically linked using OFFSET from a table of figures. The last piece of the puzzle to getting things working was using MATCH to determine where in the table I wanted data. I can now choose from 32 different charts with exactly the same formatting and size!
Here's how I did it:
1) Dashboard worksheet: Choose the cell where you want the drop down. Use Data Validation and enter the range of cells from your Data Worksheet with Chart Name choices.
2) Data Worksheet: Use the Match formula in a cell to compare the data in the Dashboard dropdown with the data from your table and return a number (either the row or column number that matches your choice).
3) Data Worksheet: Use the Offset formula to return the information from the row(or column) specified by the Match formula cell.
4) Dashboard worksheet: Create a chart that uses the information specified in the Offset Formula on the Data Worksheet.
4 steps to yummy charting goodness!
Dear Dave-O
your posting seems interesting, would you mind uploading your work sheet? I have a problem with "select & show one from many", and I wounder whether your idea can serve my purpose.
Thank you,
Hey Chandoo,
I can see you are popular with your Excel's problem solving ability. I have similar case but in my case I have 20 series in one line graph. When all the lines appear in one graph, it is difficult to see which is which. I'm thinking of just delete the data but that would be troublesome if I mess up. I wonder if there is a way to select the line you need to view for that particular moment. I may need to up 3 to 5 line a time. Thanks in advance.
[...] Show one chart from many – Dynamic Charts in Excel [...]
Hello'
i am using excel '07, i cant find define name dialog box in menu>insert>name>define, kindly please show me how to do this, thanks and good day.
@Dennis
Its on the Formulas Ribbon, about half way along
Hi Chandoo, and other Excel Gurus!
Excellent tutorial thank you.
The Excel problem I am trying to solve is to develop a P3M3 (Portfolio, Program, Project) Dashboard for my Executive.
My Department has between 20-50 projects at any one time.
Therefore the project level dynamic graphs need to be able to be selected from a group of 20-50 drop down items.
Is there a less memory intensive way of doing this other than using the camera tool hack? For example using named ranges or forumlas such as OFFSET & MATCH etc?
A further complication is I need two different types of chart.
1) The Chandoo Milestone chart (sourced from the Project Dashboard report - which by the way is awesome) and
2) A dynamic line series chart (which the user can select lines they want to look at. (this second chart will be an Earned Value Management chart). There are may series in these charts and so it useful to only look at one or two series at a time so as not to clutter the graph.
I know there is a way of achieving these goals, I just need to find ihow and who best person to find the answer is, which I think is this forum!
If you need me to pay let me know the cost!
Kind regards
Geoff
Canberra, Australia.
Dumb question -- how do I change the pick list (Sales | Expenses | Profits)?
Never mind -- data validation, got it.
I keep getting "Reference is not valid" when I select the image and type the define name on the formula bar.
can you help please
@Lene:
Have you solved the “Not valid” error?
I’m receiving same error, utilizing same values and parameters as the sample mentions.
PS.- I’m utilizing XLS 2010.
1. Thank you Chandoo! you are a true genious
2. @ Lene - I had the same problem when I tried it. I solved it and this for me was the key - INDIRECT("'Sheet19'!A31") using the correct inverted commas and quotation marks.
I am using Excel 2010.
Hope that helps.
its not work
Hi all,
This tool is really great. We use this in a template which is automatically filled with data. This data then updates the tables we want to display via these drop down options but unfortunately only a white image is being displayed when opening the saved file after the update.
The only solution we’ve found so far is to click into each chart manually and then return to the drop down sections. This works but is not really the idea since this is supposed to be a fully automated process in the end.
Can anyone tell us how to automatically update and view the charts?
We would very much appreciate your help.
A & C
Chandoo,
I am not able to enter a formula for the image as given in your point
"Finally, select the image and go to formula bar and type
=getChart(or whatever name you gave to the named range), like this: "I am using Excel 2007
Mandeep
Hi Mandeep,
In MS 2007 we cannot enter a formula for the image as we can do in 2003 or 2010, what we need to do is very simple when we are using 2007 for this trick. Before you goto the step mentioned above in comment u need to follow the below steps.
1) Insert the picture as mentioned on this website( but make sure that picture is within cell).
2) Copy the cell where u store your picture.
3) GoTo Paste option on ribbon and hover you mouse on As Picture and then select Paste Picture as Link.
4) As you paste, will see the formula on formula bar then you edit the formula and follow the remaining steps as mentioned on this website.
MINKU
Hi Minku
Thanks for the help it worked.
Mandeep
I tried this and was way impressed. You can hide the chart sheet and it still works... try that with the camera tool, and it goes blank.
Hi All,
While practising i am stuck when we type (getchart) in formula bar, its not allowing me to do so, can any one help on this.
I am using excel 2007. I cant find the insert>name option in the menu. I simply changed the name on the cell besides the formula bar. So this problem was solved. But there is another issue. When i insert a picture and select it, I can't type anything in the formula bar. It is not getting selected and hence I am not able to perform the second last step..
Please help. I am in dire need of this help.
In MS 2007 we cannot enter a formula for the image as we can do in 2003 or 2010, what we need to do is very simple when we are using 2007 for this trick. Before you goto the step mentioned above in comment u need to follow the below steps.
1) Insert the picture as mentioned on this website( but make sure that picture is within cell).
2) Copy the cell where u store your picture.
3) GoTo Paste option on ribbon and hover you mouse on As Picture and then select Paste Picture as Link.
4) As you paste, will see the formula on formula bar then you edit the formula and follow the remaining steps as mentioned on this website.
Dear Chandoo
I am also facing same problem as Mandeep Mehta
Regards
Shrinivas
In MS 2007 we cannot enter a formula for the image as we can do in 2003 or 2010, what we need to do is very simple when we are using 2007 for this trick. Before you goto the step mentioned above in comment u need to follow the below steps. 1) Insert the picture as mentioned on this website( but make sure that picture is within cell). 2) Copy the cell where u store your picture. 3) GoTo Paste option on ribbon and hover you mouse on As Picture and then select Paste Picture as Link. 4) As you paste, will see the formula on formula bar then you edit the formula and follow the remaining steps as mentioned on this website.
MINKU
I have gotten this indirect trick to work, and it is great. However I am trying to have notes over the image that is changing, specifically text boxes with arrows pointing to part of the drawing, and the picture I have the indirect formula for cannot be made transparent or be moved behind the text boxes. I pulled up the selection and visibility toolbar and there is a divider between my image and regular text boxes that I cannot move the text box up past (to bring it too the front) or my image down past (to send it to the back).
Any ideas of how to get labels in front of my changing image?
Note: if I use a form control label I can put it in front of the image, but that is not ideal because it is difficult to change how the label looks, and I cannot use arrows with my labels.
Thanks!
Nathan
U can try the same trick Arrow lable and link with same validation.
Use Arrow Label instead of chart.
This may help u.....
It was a foolish idea. Assigning a name to a picture is neither posiible in2003 nor any version. Stop this nonsence job and try any realy creative thing.
Another foolish thing is to containing a picture in a cell. Watch carefully that a picture or object does"nt belongs to any cell reference or cell address. Then how it can be contained in a particular cell and hence it is also not possible to control through a any formula. Tohe possible thing is that a picture can be assingned a macro
How do I assign a new graph to a cell? As is done with the sales graph to F11.
Thanks
I keep getting "Reference is not valid" when I try to use =getChart on the image.
I really love this idea. It was a bit fiddling around in Excel 2007, but it works alright with an object instead of a picture. Thanks for the help.
But it does make my sheet much slower. Does anyone else encountered this problem?
First of all I would like to thank you both Chandoo and Jon for your amazing work and wonderful websites! I was a complete newbie with excel 3 weeks ago, now I still suck, but I feel like I am improving everyday!
Anyhoo, for this internship I am doing, I need to create a dashboard that would help analysing sales data. It has multiples variables interacting: products, clients, regions,year,months,markets,etc..)
I would like all of it contained in one or two dashboard. I made quite my way already, headaches after headaches to sort data, think of a good way to expose it and of course, make it happen on excel.
Now to gain some space on the dashboard, I'm using comboboxes, for example :
Sales per Product with column graphs for year 2008 for example.
So I want to put the amount of products sold by quantity in one graph, and the Sales volume in the other. I would like 2 different graphs because the scale is very different (pricey products!) for the two variables.
I tried Chandoo’s trick with the INDIRECT function + graphs . It’s great, but the final result makes a graph that feels like the quality of the “picture” taken has been deteriorated(any way to fix that?) . Plus as Jon said, it might use too much resources.
So here is my question : How to be able to switch between completely different graphs with a listbox?
Sorry for the long text and the bad English!
Kalda
PS: I’m on Excel 2007
Very useful tip! I use this in combination of a dashboard.
hi,
i am using above but when i am selecting sales, expenses, profits it showing cell value=0, not the charts..
Hello, I have accomplished getting the graph to show up under the "view them here" tab but there is not a drop down, not sure what I am doing wrong..help please!!!!!!!!!!!!!!!!!!!!
@Brittany
Can you post your file?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
[...] PS: This is a revised version of almost 5 year old article – Select & show one chart from many. [...]
I have a list of 20 chart (graphs) in excel 2007. I would like the user to select from a list (maybe from a drop down) and view the chart that he wishes to see. Or a group of 4 charts at a time.
How do I do it ?? Please help.
This works great but when using a line chart the chart looks really aliased. Any way to fix that?
Hi
When I select image and go to formula bar, it is inactive therefore,I can't write in formula bar.
Thank
Hi Nik
Even I have the same prob... when I select the image and go to the formula bar its inactive... did you find the solution to this... plz let me also know... thanks
Hi Chandoo,
First of thanks for letting me know on the in direct charts,
But i could find a problem which is not functioning in my Excel 2013. It is popping out an error 'Reference is not valid'
I submit below the formula for refer to cell:
=IF(GetCharts!$A$5="Number of Programs Authored ",INDIRECT("'chartsdown'!F11"),IF(GetCharts!$A$6="Number of Programs Reviewed",INDIRECT("'chartsdown'!F12"),IF(GetCharts!$A$8="Average Turn Around Time for Program Authoring",INDIRECT("'chartsdown'!F13"),INDIRECT("'chartsdown'!F14"))))
If you could please hep on this error......
Thanks a lot in advance
Hi Chandoo,
Loving your site which I have discovered recently. I haven't read all the comments above, so apologies if this has been mentioned before. Nothing complicated about my method.
I created a named range "METRIC" to use a list (Revenue, COS, Expenses etc) in my data table. On my Display sheet (cell B2), I created list in cell using data validation > List > =Metric.
I have a calculation sheet which then links to Display Cell B2 and with the match and offset function summarise my data for my graph. I then create the line graph based on this calc data, cut & paste graph into Display sheet, format and voila!
Simple to do... Hope this makes sense.
Hello Chandoo and Folks,
Thanks for such a good idea !
I use Excel 2010 and when I try to assign the name to the picture, it says that the 'reference is not valid'. I have tried to change the quotes, insert as a object, and all other options provided in the comments section.
Can anyone help to get the reference right ?
Best,
Vinoth
[…] Jadi, kalau klik salah satu menu, maka chartnya akan berubah sesuai dengan data dari menu yang dipilih. Untuk caranya bisa disimak disini […]
Why using two worksheets? It can be done in a single worksheet.
One more thing. I recommend using LOOKUP function instead of NESTED IF's.
Hi Chandoo,
This is the situation, so we have an excel spreadsheet with a chart that calcuates the choosen sales team (eg. Smith team; Doe Team; etc) total sales stats for all stats entered until the current date (stats are entered on a daily basis by an assigned employee) feeding off a pivot table inbeded in the same sheet using the V Look-up function. We need the smith team for example to only see their stats and not Doe's Team's stats. And visa versa for Doe's Team, we do not want them to see Smith's Teams stats and so on, when selecting their chart from the drop down menu.
Is there a way to:
1. assign each chart to the applicable team?
2. Hiding the other 2 charts so that the team wanting to see their
sales stats can only see their stats and not the other teams
stats. And visa versa for the other 2 teams stats?
Thanks so much in advance for your help Chandoo.
My apologies Mehmet. We use 3 charts on one worksheet that feed from a pivot table embeded into the same worksheet. We use 3 separate charts obviously because when the sales team chooses to see their stats, the following is shown in each chart: 1. Sales & Activitie Stats 2. Activity Stats. 3. Percentage of Sales vs YTD sales.
This gives them a summary of all aspects of our sales stats. Again our thing is we do not want the teams to see each other's stats other than their own.
I may have missed this suggestion in the blog, but the complex INDIRECT formula can be replaced by
=INDIRECT('View them here'!$C$2)
...if all cells whereever a chart exists (they can exist anywhere in the workbook) are given a range name and then listing all those range names for the drop down list.
Further, charts don't have to be put into single monster sized cells if worksheet gridlines are turned off but the range of cells covered by the chart has to be included in each range name.
Finally, as an alternative to the drop down list, I believe option buttons are a more pleasing approach that provide the dual advantages of only one click instead of two and all chart options are easily visible before selected.
Hi Chandoo,
Thanks for the above tip. Additionally, I have another requirement that needs to show charts based on selection and where the selection can be multiple. The need for multiple selection is to have different charts for comparison at the same time. Is it possible without using vba? It would be great if I can get some tip on that. Thanks.
Hi Chandoo,
I need a help. I want to create a price chart of different stocks with buy and sell levels indicated. For example: suppose there is one stock A, the duration of the chart for this stock is from Jan 1, 2015 to July 31, 2015. I bought this stock on Jan 1, 2015 and then increased the quantity on Feb 26, 2015 and March 30, 2015. I exited from this stock on June 30, 2015. Now I want to show "a blinking green arrow or a blinking sign B" for buy in charts on date feb26 and March 30. And a blinking red signal or a blinking S sign for the date where I sold ie June 30. Now this is for one stock. If I have 100 stocks and chart is dynamically adjusted for those hundred stock with a drop down. Can i highlight those buy and sell sign for all those stocks just by selecting a stock in dropdown?? Is it possible?
This was a happy find today. Thank you for clear instructions. It worked great even with overlapping pie charts I had grouped together.
Hi Chandoo,
Wonderful trick and tutorial. I tried to implement it on a worksheet that I embed and share online via OneDrive. I can get it to work using the Excel 2016 PC application if I omit the INDIRECT() functions, but the online embedded version only shows a blank target cell no matter what I've tried. Is this your experience as well, and might you know an alternative way to selectively show or blank charts that works in Excel Online?
Thanks in advance for any help!
Wow, that is amazing! Thanks for the clear instructions.