In order to effectively analyze data, custom number formatting can be applied to your Microsoft® Excel® spreadsheet. Custom number formatting is used to easily identify values based on a set criteria. In a large Excel spreadsheet, you can easily highlight all negative or positive values by using custom number formatting.
In this example we are going to display all negative values in a different colour.
You are welcome to download the workbook to practice.
Applies To: Microsoft® Excel® 2010, 2013 and 2016.
1. Select the range of cells which need to be formatted. For this exercise, we’ll use cells B1:B8 in the workbook.
2. Hold down the Ctrl and 1 keys simultaneously or right click, and select the Format Cells
3. Click on the Custom option under the Category section.
4. Select the # ##0; [Red] – # ###0 option from the Type list . The two parts are separated with a semicolon (;) to control positive and negative numbers only. Values to the left of the semicolon represent positive values and values which are to the right of the semicolon, represent negative values.
5. To remove the negative sign from the value displayed, the number format code must be adjusted by removing the sign from the format code as follows
With “– “
# ##0; [Red] – # ###0
Without “– “
# ##0; [Red] # ###0
All negatives values are highlighted in red, making it easy to work with figures that have variances. This will save you time of manually selecting all the negatives values and increase productivity.
The post 5 Easy steps to display positive or negative values in Excel using custom number formatting appeared first on Sage Intelligence.
Powered by WPeMatico
