Скачать или смотреть Excel - Charting Positive and Negative in Different Colors - Excel - Episode 1323

Microsoft Excel Tutorial: Charts with different colors for positive and negative.

Welcome back to another exciting episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head-to-head to solve your Excel challenges. In this episode, we tackle a reader's question about creating a chart with positive and negative values displayed in different colors. This challenge was brought to us by a reader named John, who wanted to know how to make the positive columns green and the negative columns red.

As a contributing editor for cfo.com, I receive a lot of interesting questions from readers. When John's question came in, I thought it would be a piece of cake to solve. I quickly fired up Excel 2010 and used the "Invert if negative" feature to change the colors of the columns. However, when I tried the same solution in Excel 2007, I realized that the feature was not available. This led me to explore other ways to achieve the desired result.

I came up with a solution using two secret series, one for the positive values and one for the negative values. This involved using the IF function to check if the value was above or below a certain threshold and displaying the value or an N/A if it didn't meet the criteria. Then, I created a stacked column chart from these series and formatted the colors accordingly. However, my solution was not as elegant as I had hoped.

That's when Kelly, a reader of cfo.com, wrote in to suggest a better way to solve the problem. She pointed me to one of Mike's videos where he showed a different approach using the "Invert if negative" feature in Excel 2010. I immediately reached out to Mike and suggested we do a dueling podcast to showcase both our solutions. Mike's solution involved using a formula to create labels for the legend and using the overlap feature to ensure the correct column was on top. We both agreed that our solutions had their own advantages and it was great to see different ways to solve the same problem.

In the end, we hope this episode has provided you with some valuable insights into charting positive and negative values in different colors. We always love hearing from our viewers, so if you have any Excel challenges or suggestions for future episodes, please leave them in the comments below. Don't forget to subscribe to our channels for more exciting Excel content. Until next time, this is Bill Jelen and Mike Girvin signing off. See you in the next episode of Dueling Excel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Table of Contents:
(00:00) Discussion about a reader's question on creating a chart with different colors for positive and negative columns
(01:10) Alternative method for creating the chart in Excel 2007 or earlier versions
(03:23) Adjusting the scale and major units for the chart
(04:23) Introduction to Mike's solution for the chart
(05:18) Creating labels for the chart using formulas
(06:12) Using the TEXT function to format values in the chart
(07:01) Adjusting the chart elements and using the overlap trick
(07:56) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Adding labels to Excel chart
Changing chart series overlap in Excel
Conditional formatting in Excel chart
Creating a chart with positive and negative columns
Creating a stacked column chart in Excel
Customizing colors in Excel chart
Excel 2010 chart formatting
Excel chart tips and tricks
Formatting axis in Excel chart
Formatting legend in Excel chart
Inverting colors in Excel chart
Using IF function in Excel chart


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...