• Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • More
    • Blog
    • Download Area
    • Excel Video Tutorials
Home » How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel

How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel

October 13, 2015 by Barbara

postitive negative

Most Excel users would be used to working with the concept of displaying negative numbers in a worksheet with a preceding – sign in front of the number, something a bit like this below, where I have an example of monthly sales and the diference month on month of those sales figures. The negative differences are easy to see.

positive number formatting

So, how about making the positive numbers just as identifiable with a + sign in front of the numbers. It is really easy in Excel to create these formats using the custom number formatting feature in Excel.

There are varying monthly performances; the negatives are obvious so let’s go ahead and make the positive ones just as obvious.

Word of note….. If we apply only the positive formatting to the data range in the above example we will wipe the original formatting of the negative values, (not exactly what we want), we therefore need to also take into account negative values and ensure we re- apply formatting for them as well. This is just as easy to do at the same time as applying the postive conditional formatting.

  • Select the range of cells you want to format.
  • Home tab
  • Number Group
  • More number formats
  • Custom

Or hit CTRL+1 to open the format cells dialog box

positive number formatting1

  • Select Custom
  • Type +0;-0;0
  • Hit Ok

positive number formatting2

Here is the same set of data with the new formatting- what do you think?

positive number formatting3

 

Both the positive and the negative are appopriately formatted (+0;-0)along with the blank cell reference having no formatting at all. This is the last ;0 part of the formatting instructions to Excel.

Have you ever used this type of formatting?. Do you think it makes a difference to the the impact of the data at all?

More Excel Tips

excel-formulas-ad-5

How To Compress Picture Sizes In Excel

How To Change The Number Of Recently Used Files Excel Displays

How To Print An Entire Excel Workbook At Once

Control how many last used Files Excel displays for you

Prevent user printing an Excel workbook

Easily find your most often used Excel workbooks

 

 

 

 

 

Filed Under: Blog, Excel Tip Category, Excel Tips& Tricks Tagged With: Excel, Excel Tips, excel tutorials

Comments

  1. Hank says

    May 22, 2017 at 10:12 pm

    Hi,

    I found 2 problems>
    1) formatting +0;-0 works fine but the :0 at the end is not accepted by Ecel 365
    2) The number 0 is formatted as +0 , which does not exist.

  2. Fidel Benitez Corrales says

    October 20, 2017 at 11:57 am

    Thanks a lot for this tip, you have saved me a lot off time

    • Barbara says

      October 20, 2017 at 7:55 pm

      Hi Glad It helped!

      Thanks Barbara

Trackbacks

  1. Formula Friday - Splitting Text And Number In Excel says:
    May 27, 2016 at 8:27 pm

    […] How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel […]

  2. Excel Tip - How To Count Unique Items In Excel Pivot Table says:
    June 28, 2016 at 5:24 pm

    […] How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel […]

  3. Formula Friday - View All Of Your Calculated Fields And Items Contained In Your Excel Pivot Table says:
    August 19, 2016 at 6:10 pm

    […] How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel […]

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar

Copyright © 2023 · Enterprise Pro Theme on Genesis Framework · WordPress · Log in

Popular Pages

  • Home

More Info

Want FREE Excel Tips?

Click on my FREE eBook, its my bonus for joining thousands of others who receive my 3 xFREE Excel tips every month in my Excel Newsletter. Join Us!.

Excel Jobs

Excel Charting

Self Expanding Charts
One Click Charts
Create Quick Dynamic Charts
Easy Combination Charts

Search

Navigation

  • Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • Top Menu

Social

  • Twitter
  • Facebook
© howtoexcelatexcel.com. All rights reserved.
  • Privacy Policy