# Creating a candlestick chart with a horizontal line - can anyone help?

1. ## Creating a candlestick chart with a horizontal line - can anyone help?

Hi all,

I have the following time series and I'd like to create a visual chart of it. Please see attached Excel file.

1) I drew a horizontal blue line manually by hand on my charts. It's supposed to be the Open price at 09:31 (I bolded it out in the Excel file).

Is it possible to have Excel create this automatically?

2) Is it possible instead of a continous time plot at the X-axis to have Excel show only selected times...? For instance, 09:31, 10:00, 11:00, 12:00, 13:00, 14:00, 15:00 and 16:00?

I think this would help create a cleaner chart.

Thanks in advance for any help!

Best,

Elijah

2. ## Re: Creating a candlestick chart with a horizontal line - can anyone help?

I added a helper column G and placed the formula =\$C\$2 in G2 and copied it down. Then plotted a new series on the secondary axis and adjusted the min and max bounds of the secondary axis to match with that of primary axis.

See if that is what you were trying to achieve.

3. ## Re: Creating a candlestick chart with a horizontal line - can anyone help?

Great! Thanks a lot.

Do you have any idea if it's possible to also clean up my X-axis with only whole hours for time? Not very important, but I think I'd like it if possible without too much trouble.

It's embarrassing, but I couldn't quite figure out how you added that series to the chart, but it should be easy I think. Just didn't have time to Google it now as I'm headed out.

4. ## Re: Creating a candlestick chart with a horizontal line - can anyone help?

The attached file shows the whole hours in the X axis. A helper column (H) has been added to the data range. Cell H2 simply gets the time from cell B2 and is formatted [h]:mm
From H3 down the cells are populated using the formula: =IF(TIME(B3*24,0,0)<>TIME(B2*24,0,0),B3,"")
The formatting for H3 and down is: [h]
To add the X axis series
1) Select the X axis,
2) Right click,
3) Choose 'Select Data',
4) Under the 'Horizontal Axis Labels' select the 'Edit' button,
5) Paste the following in the 'Axis label range' window: ='Ark1'!\$H\$2:\$H\$391
Let us know if you have any questions.

5. ## Re: Creating a candlestick chart with a horizontal line - can anyone help?

Thanks a lot, JeteMc.

Exactly what I wanted. Thanks a lot!

7. ## Re: Creating a candlestick chart with a horizontal line - can anyone help?

You're Welcome and thank you for the feedback. As per AliGW's post, please mark the thread as 'Solved'. I hope that you have a blessed day.

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

#### 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