Excel Tips and Tricks / Business Reporting Question: We have just upgraded from MS Excel 2007 to MS Excel 2010. When applying conditional formatting in MS Excel 2007 we were unable to display negative data bars. Can this be done in MS Excel 2010?

Answer: Yes, by using conditional formatting

Why: To create data bars for negative & positive values

Applies To MS Excel 2010:

1. The screen shot given below has negative & positive stock in/out values.

 

2. Highlight the stock in/out values.

3. Select conditional formatting under the home tab.

4. Select data bars.

5. Select the data bar of your choice.

6. The data bars below will be displayed.

 

7. To customize your data bars.

8. Repeat steps 3&4 and then select More rules.

9. Refer to the screen shot below.

 

You can then customize the data bars, negative values and axis. Data bars for negative and positive values have been created. One is then able to easily analyze and interpret the data in Ms Excel 2010 by using conditional formatting and assigning rules to the conditional formatting.