Jump to content


Photo

Bar charts and hidden columns

Bar charts hidden columns

  • Please log in to reply
6 replies to this topic

#1 BobP

BobP

    Member

  • Members
  • PipPip
  • 25 posts

Posted 01 September 2014 - 03:03 PM

Hi

 

I have a worksheet that contains students' test data with a column for their first name, a column for their surname and a column for their total mark out of 50.  I have worked out a way of producing a bar chart that colour codes various ranges to show their results, ie 0-25, 26-37, 38-50.  This involved producing three additional columns, one for each range so a student would have a mark in the appropriate column and nothing in the other two. Using a stacking bar chart for all three columns, the columns with no marks in therefore didn't show and I could colour code each series.  I hope that makes sense! I get a nice chart with individual blue, yellow and green bars.  Now the problem - I want to hide the three columns (AA, AB & AC) with the intermediate data as they are not relevant to the user.  I know how to do this from the Show data in hidden rows and columns but I have one other column (E) hidden on the sheet and when I check the Show hidden data this comes up on the bar chart as well.  Definitely not required.

 

Is there any way to restrict what is hidden and what shows?

 

Thanks

Bob

 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 05 September 2014 - 07:55 AM

Hi Bob,

 

It sounds as though your best option might be to open the Select Data Source dialog and change the values there.  Adjusting the Chart data range setting to only cover the cells you want to be charted should allow you to chart just the data you want.

 

For instructions on opening the Select Data Source dialog and changing the Chart data range, see the video lesson: Essential Skills Lesson 5-11 Change a chart’s source data.

 

I hope this helps, but feel free to reply if I can be of more assistance.  You can attach a copy of your workbook if you'd like me to take a look at it.  For instructions on attaching files, see this thread.


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 BobP

BobP

    Member

  • Members
  • PipPip
  • 25 posts

Posted 08 September 2014 - 09:43 AM

Hi

 

I have attached the relevant workbook. As it stands the data in columns C & D are concatenated in column E and then column E is hidden.

 

In order to apply different colours to the bars in the chart, I have produced three new columns AA to AC that sort the data from the total in column G into three ranges. The ranges are then plotted as separate series and different colours applied. The data itself in columns AA - AC are currently in a white font in order not to show. One of the series is plotted against a secondary axis to allow for the same scale at the right hand end of the chart but even when the worksheet is protected this is not ideal.

 

In essence the effect is what I want and will probably suffice for its purpose for use by teachers but it would be better if it could be done 'properly'.  Any help or comments most appreciated.

 

Kind regards

Bob

 

Attached File  Y3 Reading Results Analyser_Demo_v0.3_030914_testing.xlsm   101.84KB   231 downloads



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 08 September 2014 - 10:45 PM

Hi Bob,

 

I see the issue now - you're not just trying to set up different data series in a chart, but trying to do the equivalent of applying 'conditional formatting' to the chart so that the bars appear in different colours according to their value.

 

The approach you've taken is currently the only way to do this in Excel; there isn't a more 'proper' way to do this. You could achieve the same result by using a Pivot Table and Pivot Chart, but there's really no advantage to that approach in this situation.  Is there something specific that isn't ideal about the chart?

 

testresults.png

 

For other users trying to achieve the same effect, this is done by creating separate columns for each of the different colored bars you need and then placing the appropriate values in each column.  To filter the values into the correct columns, you might use either an IF formula or a Pivot Table with calculated fields.

 

For instructions on using the IF function, see: Expert Skills Lesson 3-5 Use the IF logic function.

For instructions on creating Pivot Tables with calculated fields, see: Expert Skills Lesson 5-17 Add a calculated field to a pivot table.

 

Once the data is filtered into columns, you can create a Stacked Column chart from the data.  This has the effect of creating a single bar for each data series, instead of separate bars for each column.

 

For instructions on creating charts, see: Essential Skills Lesson 5-2 Create a simple chart with two clicks.

 

Once the chart is created, you can modify the color of the bars to your liking.  For instructions on formatting chart elements, see: Essential Skills Lesson 5-7 Manually format a chart element.


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.


#5 BobP

BobP

    Member

  • Members
  • PipPip
  • 25 posts

Posted 09 September 2014 - 06:48 AM

Hi Jonathan

 

Thanks for the response.  I am happy with the chart as is but as you can see above I can't use the show hidden columns approach as you get the mess along the bottom just above 'Pupils'. I will have to stick to the method I have of formatting the extra data columns in white and perhaps move them further away.

 

Thanks for your hep as always

 

Kind regards

Bob



#6 Mike

Mike

    Advanced Member

  • Administrators
  • 182 posts

Posted 09 September 2014 - 10:14 AM

Dear Bob

 

Just to add to Jonathon's response.  I can see that you are using Excel 2010 and so will not have seen a different presentational solution to exactly the same problem using a new Excel 2013 feature.  You will find this lesson interesting (though you cannot use this solution with Excel 2010):

 

Lesson 5-19 Add data labels from a range

 

The approach described in this lesson annotates each bar with a label (rather than a color).  This may present a more flexible approach for the data you need to present:

 

Data.png

 

Graph.png


Mike Smart is the author of ten world best-selling Excel books. The books are available in printed form for for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Mike has also recorded over 850 video lessons (that you can watch online) for Excel 2007, Excel 2010 and Excel 2013.
Mike Smart is also part of the team that answers questions posted on the ExcelCentral.com forums.


#7 BobP

BobP

    Member

  • Members
  • PipPip
  • 25 posts

Posted 09 September 2014 - 03:42 PM

Hi Mike

 

That's for the extra information.  Unfortunately my role is to provide a version that works in Excel 2007 as well as Excel 2010 because our schools still have old versions!  The functionality is not backwards compatible but it is really interesting to know how the things move on. Maybe our schools will catch up eventually. Also my publishers have a thing about putting in colour for the teachers! I work within their limitations.

 

Thanks again as always.

 

Kind regards

Bob






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users