Show Negative Values As Zero/0 In Excel

We will figure out how could we display negative values in a field in excel as “0”.

For some reasons we don’t want to show the actual negative values in our report or presentation so we show them as “0” or “NA” etc.

Below is the sales data for previous and current year along with the percentage change in the numbers from previous number.


The formula that is used to calculate the percentage is   =(B2-A2)/A2, now we will just edit the formula with an if condition to make it display the value as “0” rather than the actual negative values.



We could replace the “0” with “NA” in case we wanted to display “NA” instead of negative values like below.



As you could see, the negative percentages are displayed as 0.

Hope this helped.

Share The Knowledge

Random Posts

  • Search Suggestion Drop Down In Excel

    This post demonstrates how to create a google like dynamic search suggestions drop down list in excel.It is very helpful […]

    Share The Knowledge
  • Excel Deselect Cells/All

    This post demonstrates how to deselect all the cells without changing the active cell in excel The features is available […]

    Share The Knowledge
  • Sort Comma Separated Values Within A Cell In Excel

    sort cell values in excel, excel sort comma separated words within a cell in excel, sorting within a cell in excel, sort comma separated texts inside a cell, sort string within a cell in excel, sort words within a cell in excel

    Share The Knowledge
  • Move/Copy Sheet To A Different/Same Workbook In Excel

    Let’s see how a sheet in an excel work book could be moved or copied to another excel workbook Step […]

    Share The Knowledge

Leave a Reply