+ Reply to Thread
Results 1 to 6 of 6

Need coloured background columns in chart for easy pattern recognition

  1. #1
    Registered User
    Join Date
    08-23-2011
    Location
    Toronto,Ontario
    MS-Off Ver
    Excel 2003
    Posts
    48

    Need coloured background columns in chart for easy pattern recognition

    I am making a chart to show the daily natural gas usage for an apartment building.

    The x axis grid lines are the days of the month in the format dd/mmm/yyyy.

    There are 2 lines across the x axis.

    One is for the daily temperatures from -25 to 0 degrees Celsius in 6 hour intervals.

    The other line is for the cubic meters of natural gas used over an 8 to 12 hour period during the day.

    The y axis range is from -25 to +55 which is OK for my 2 horizontal lines since both horizontal lines upper and lower limits fall within those parameters.

    The plot area is plain white with black gridlines. I want that simple. The 2 horizontal lines are red and green.

    On Saturday and Sunday the natural gas goes up to almost double the Monday to Friday daily amounts used.

    Therefore I would like to highlight these 2 days (Saturday and Sunday) where the line slopes up and then down by having a pastel colour in the 6th and 7th white plot area columns so it is easy to recognize the weekend on the chart.

    I have EXCEL 2003 but I will upgrade if I have to to get the desired effect because I might need this chart for a presentation and I want the pattern of increased weekend use very easy to see.

    Thanks in advance

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,436

    Re: Need coloured background columns in chart for easy pattern recognition

    You might be able to use additional series plotted as column chart to colour areas of the chart.

    If you need more help post an example file so we can see what chart type you are currently using and what data you have,
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    08-23-2011
    Location
    Toronto,Ontario
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Need coloured background columns in chart for easy pattern recognition

    Here is a chart showing the basic layout. Although there are fluctuations in the gas usage line there are other factors occurring in the building that cause the erratic daily gas usage that are not related to temperature alone.

    I hope this chart will be sufficient to get some assistance.

    Danny, from Ontario
    Attached Files Attached Files

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,436

    Re: Need coloured background columns in chart for easy pattern recognition

    You can create data for the 2 new series which plots Saturdays and Sundays.

    Add 2 new series to chart
    Move new series to secondary axis
    Change chart type of new series to Cluster Column
    Use Source Data to point new series and new data

    Format secondary horizontal and vertical axis.
    Format new series no gap/ 100 overlap. colour as required.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-23-2011
    Location
    Toronto,Ontario
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: Need coloured background columns in chart for easy pattern recognition

    Andy,

    Is there a way to make the y axis grid lines go through the background colours?

    Also, I opened the file with EXCEL 2010 on my laptop and the background gray colours don't fill the columns only and completely. They seem to be offset and shifted to the right taking up some of the adjoining column.

    A side note:
    I use Firefox and I couldn't download your file until I had the cookies turned on.

    Danny from Ontario

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,436

    Re: Need coloured background columns in chart for easy pattern recognition

    You need to extend the new data to Thu 19 2015, and adjust the ranges of the 2 series. That will then fill in the gaps correctly.

    Gridlines appear behind actual chart series.
    In 2003 you could use extra series to draw the gridlines using xy-scatter series.
    In 2010 you can adjust the transparency of the column fills so the gridlines are still visible.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. recognition of coloured squares
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 07:05 PM
  2. recognition of coloured squares
    By princecharlie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  3. recognition of coloured squares
    By princecharlie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  4. recognition of coloured squares
    By princecharlie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. [SOLVED] recognition of coloured squares
    By princecharlie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1