Jump to content


Advanced Charts

  • Please log in to reply
2 replies to this topic

#1 Sherif129



  • Members
  • Pip
  • 2 posts

Posted 13 September 2015 - 09:32 PM

Hi there,


I sat my Expert exam the other day, but failed ultimately on the section 'Advanced Charts.' The question that got me really stuck was asking me produce a combo chart (never said what type though which was confusing as well, e.g stacked column etc), but you had to edit the series data. So for that I just went Design > Select Data. Seemed simple. But it was asking for the Legend Entries to be Duration and Country, and the horizontal label to be something such as Date, but ONLY data with the name matching "Strata" could be shown in the chart. Again, I had no idea how to do this, because the Name field (containing the "Strata" name along with others could not be added, so I assume some function needed to be added to each series, but I had no idea what to do. e.g I was thinking like IF the cell in the selected range is == to the name THEN use this cell in the series, but had no idea how to do this.

Any ideas?



EDIT: Forgot to add, simply filtering by hiding the rows where "Starta" was not present, was no possible due to the table of data having 299 entries, thus being somewhat time consuming in doing this method.

#2 Jonathan


    Forums Administrator

  • Administrators
  • 888 posts

Posted 14 September 2015 - 09:22 AM

Hi Sherif,


I think the solution is to use a filter. You should be able to very quickly and easily filter the data to only show "Strata" entries by using Excel's standard filtering features.

All you need to do is untick every box except for "strata", and you can do this in two clicks by first unticking "(Select All)" and then ticking "Strata".




You can see a video lesson showing more details of how to do this in: Expert Skills Lesson 1-3 Apply a simple filter to a range.


When you filter your data, any charts that are applied to it will automatically change to display only the filtered data. This is because charts do not display hidden rows by default.

You can see more about why this works in the video lesson: Essential Skills Lesson 5-15 Chart non-contiguous source data by hiding rows and columns.


I have attached an example workbook showing this in action:


Attached File  ChartFilteredData.xlsx   13.4KB   175 downloads



I hope this is the solution you were looking for, but please feel free to reply if you're still not sure about this.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

#3 Sherif129



  • Members
  • Pip
  • 2 posts

Posted 14 September 2015 - 09:26 AM

Hi Jonathan,


After consideration, I think this may be the solution - as simple as it sounds.


I was looking at the question in a much more complex way - probably due to my coding experience.


Many thanks.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users