How do I display negtive data lables in a bar chart, when the data in time format?

Thanks for this. I can see you are using Office 2016 , and my Excel 2010 does not have the option to SELECT RANGE. Can you suggest an alternative for 2010?

Edited since initially posted. Changed formula for date conversion to text.

My apologies for posting the reply for the incorrect Excel Version. I did not realize they are so different and I should have tested in xl2010 before replying.

It is a bit of a drawn out process to fix in xl2010 but I will try to explain. Refer to the screen shot below.

1. Set up a column with times in Text format using a formula as per the screen shot below. The formula to create the times in text format is as follows.  =TEXT(A2,"mm:ss")
2. Add the data labels to the chart. (If data labels already on the chart then select and delete them and add the labels again so we start clean).
3. Click one of the data labels to select all the visible labels.
4. Right click on one of the selected labels and select "Format data labels"
5. Select "Number" in the left column of the dialog box.
6. Ensure "Linked to source" is unchecked.
7. Select "Text" from the number category. (All data labels should now display but with numeric values)
8. Click on one of the already selected data labels and it should then be the only one selected.
9. Click in Formula bar and type an equals sign. (Must be in the Formula bar; not in the label).
10. Click on the cell with relevant text value for the label so it adds the cell address after the equals sign.
11. Repeat from Step 8 for remaining labels.

I cannot help you with the negative values on the X axis. However, if you want to display the times on the X axis in 5 min increments as I have done then the following procedure.

Anywhere in a blank area of the worksheet enter actual times in 3 different cells ie. (00:05:00,  00:10:00, 00:30:00)

Select the 3 cells and format them as numbers with 10 or more decimal points. (You should have numbers like 0.00347222222222222,  0.00694444444444444,   0.0208333333333333

Select the X axis and right click and select Format the axis.

Select "Fixed" for the Minimum, Maximum and Major units and enter the appropriate numbers in the adjacent fields. (Don't forget to include a negative sign in the Minimum field). When copying the numbers it is best to copy from the Formula bar but you will need to click back on the worksheet and select the X axis again to paste the values.

The Number format for the X axis should be Linked to Source.

Try the following:

• Ensure that the "Format Plot Area" dialog is visible at right of worksheet. If not, right click anywhere on the chart and select "Format Plot Area" (Last option)
• Left Click on one of the labels to select all of the labels and the dialog as per screen shot should display.
• "Label options" should be default but if not click the DropDown beside whatever is displayed where my screen shot displays "Label Options" and select "Series name Data labels" where "name" is the name of the series. (It should be the last option in the DropDown List).
• Click the icon that looks like a column chart (Under "Text Options")
• Check box against "Value from cells"
• Click Select Range and in the dialog select the range where data labels get their values (Values only excluding column headers)
• All other boxes under "Label contains" unchecked. (Optional but you can test what they do if checked because they display on the chart immediately they are checked.)
• I have circled the other options I used for testing.
• Click "Number" to open the Number format option.
• You can use the Number format Linked to Source data as per the Screen Shot or
• Note with the Number format that you can select a format from the Category or
• you can enter a Custom format in the "Format code" field and click "Add" to add the custom format.

A little extra information. Refer to the screen shot below. Note all in Minutes and Seconds Number format.

If the values with the negative numbers display as all #'s because Excel does not cope with displaying negative times then you can create an additional column with text values for your data labels.

Use the column of actual data to create the chart but when you get to the "Select range" for the Data labels, select the column with text data (The labels do not have to sourced from the same column as the chart series)

Formula to convert times to text values with mixture of negative and positive time values.

=IFERROR(TEXT(A2,"mm:ss"),TEXT(ABS(A2),"-mm:ss"))

The screen shot actually has negative times in column A where the #'s are displayed and column B has the times in text format with the leading negative sign for the negative times.

here it is. I am unable to post the data, due to confidentiality

Can you post an example of the data from which you are creating the chart and a copy of the chart. Use the Snipping Tool to post a copy of the chart. More info on Snipping tool at the following link. Suggest that you pin the Snipping tool to the Task bar.

https://support.microsoft.com/en-nz/help/13776/windows-use-snipping-tool-to-capture-screenshots

To post the screen shot created with the snipping tool use the "Insert image" icon in the post editor.