Excel Howtos

Show Decimal Points if needed [Quick Tip]

Last updated on September 5, 2012

Chandoo

Sometimes you want to turnoff decimal points if the value after point is 0. Mireya, Chandoo.org member had one such situation. She writes:

I am a complete beginner in excel, how can I keep the zeros when I am working with decimals and remove them when are not required, ie

Thanks for your kind help.

Easy way: Use General Formatting

The default cell formatting in Excel is General. When you set a cell’s formatting to General, you are telling Excel,

Don’t bother me. Just figure it out.

And being a good Samaritan, Excel shows decimal point if there is something after it, else omits it.

See the demo aside to understand this.

What if your numbers are results of a calculation?

It doesn’t matter. General formatting takes good care of the cells. It shows and hides decimal point depending on the result of your formulas.

What if you want something fancy like accounting format, but turn off decimal values

Now you are talking. The General Formatting option shows numbers as typed (or calculated). So 124578395 would look like 124578395 instead of $ 12,45,78,395.

So how do you show $1,245 and $1,246.34?

Aside: You should always show decimal points if some values have them and others don’t. The below technique is useful when data is a result of calculation. For example: In a dynamic KPI report, for certain KPIs you may want to show decimal points, and omit for others.

To show decimal point if there is something after it

Just follow below steps:

Select the cell(s) where you want this formatting.
Go to Conditional Formatting > New rule from home ribbon.
Select rule type as “Use a formula…”
Check if there is a value after decimal point using a formula like =Mod(A1,1)>0
Click the format button
Go to “Number” tab and Apply formatting with 2 decimal places.
Click OK & You are done!

Now, if the cell has a decimal value, it shows, otherwise the decimal point is omitted.

Related: Conditional formatting Basics

Do you deal with such situations when formatting numbers?

Often when making reports (or dashboards), I have a cell where any data can go, based on user selection. In such cases, I use conditional formatting to define how it looks based on the data. Sometimes, I also use TEXT formula to format the data. This is more suitable when data is displayed in a text box rather than a cell.

What about you? Do you face situations like this? How often you rely on General formatting? Please share your experience and tips using comments.

More on Number formatting in Excel

Understanding how Excel formats numbers (and other values) can save you lots of time when you are designing dashboards, reports or workbooks that need to presented. Check out below articles to get few more tips.

Share this tip with your colleagues

27 Comments
Ask a question or say something...
Tagged under custom cell formatting, Custom Number Format, Learn Excel, Microsoft Excel Conditional Formatting, number formatting, quick tip, screencasts
Category: Excel Howtos

Want an AWESOME
Excel Class?

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.

Rebekah S

Reporting Analyst

FREE Goodies for you...

Related Tips

Excel Challenges

26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”

Stef@n says:

July 29, 2011 at 11:48 am

first solution for AND
The two numbers are in A1 and B1
= SUBSTITUTE (SUBSTITUTE (A1+B1*9*9, 9, 1), 8, 0)
regards
Stef@n

Reply
Stef@n says:

July 29, 2011 at 11:50 am

next solution for OR
=1*SUBSTITUTE (A1+A2;2;1)

regards
Stef@n

Reply
Stef@n says:

July 29, 2011 at 11:52 am

last solution for XOR
=1*SUBSTITUTE (A1+A2;2;0)

regards
Stef@n

Reply
Irvine says:

July 29, 2011 at 12:26 pm

Or you could make use of the VBA logical operators!

Define the following as custom functions
Public Function BITXOR(x As Long, y As Long)
BITXOR = x Xor y
End Function

Public Function BITAND(x As Long, y As Long)
BITAND = x And y
End Function

Public Function BITOR(x As Long, y As Long)
BITOR = x Or y
End Function

and then use them such:
A B =BITOR(A,B) =BITAND(A,B) =BITXOR(A,B)
0101 0100 0101 0100 0001

Reply
Stef@n says:

July 29, 2011 at 12:46 pm

an another solution for AND
=1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

note:
the binary numbers are in A1 and A2 !
regards
Stef@n

Reply
Shair says:

July 29, 2011 at 2:22 pm

I was obviously playing hooky at the beach during the bit-wise math lesson – you lost me at “Understanding bit-wise operations” 🙂

Reply
Anup Agarwal says:

July 30, 2011 at 8:46 am

After looking at the above solutions, I find my solution silly, but still:

For the following formulae,
Row 1: headers,
Row 2: OR
Row 3: AND
Row 4: XOR

Column 1: Input 1
Column 2: Input 2
Column 3: Result

OR
{=SUM(IF(MID(A2,ROW(OFFSET($A$1,0,0,LEN(A2),1)),1)+MID(B2,ROW(OFFSET($A$1,0,0,LEN(B2),1)),1)>0,1,0)*10^(LEN(A2)-ROW(OFFSET($A$1,0,0,LEN(B2),1))))}

AND
{=SUM(IF(MID(A3,ROW(OFFSET($A$1,0,0,LEN(A3),1)),1)+MID(B3,ROW(OFFSET($A$1,0,0,LEN(B3),1)),1)=2,1,0)*10^(LEN(A3)-ROW(OFFSET($A$1,0,0,LEN(B3),1))))}

XOR
{=SUM(IF(MID(A4,ROW(OFFSET($A$1,0,0,LEN(A4),1)),1)+MID(B4,ROW(OFFSET($A$1,0,0,LEN(B4),1)),1)=1,1,0)*10^(LEN(A4)-ROW(OFFSET($A$1,0,0,LEN(B4),1))))}

Reply
Hui... says:

July 31, 2011 at 11:57 pm

@Anup
Please don't consider your solution silly
Firstly, You are the 3rd person to submit an answer
Secondly, The best formula/function is the one that you know and understand.

Reply
Michael Azer says:

August 1, 2011 at 1:40 pm

I think I have a very tedious solution, which people won't have the patience to do except in small numbers.

I used the same problem setup as "Anup Agarwal"

AND =IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)

OR =IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)

=IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

Reply
Michael Azer says:

August 1, 2011 at 1:43 pm

Sorry my last post was totally messed up

AND
=IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)
OR
=IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)
XOR
=IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

Reply
Stephen says:

August 1, 2011 at 1:45 pm

@stefan,
I just couldn't get your solutions to work.
01010101010 + 01010101110 = 02020210120
what am i doing wrong?

Reply
Stephen says:

August 1, 2011 at 1:56 pm

@anup
...I got yours to work!

Reply
Rich says:

August 1, 2011 at 8:17 pm

@Stephen - I get the same, but Stef@an's second solution for AND does work (at least for the test cases I used)

Reply
Stef@n says:

August 2, 2011 at 9:09 am

@ Stephen / Rich
yes , you are right ! - only this works:

OR
=1*SUBSTITUTE (A1+A2;2;1)
XOR
=1*SUBSTITUTE (A1+A2;2;0)
AND
=1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

Reply
Michael Azer says:

August 3, 2011 at 1:36 pm

@Stef@n - You're answer is really smart, I never knew about the substitute function before. Great Work!

Reply
Stef@n says:

August 3, 2011 at 2:45 pm

Thx Michael 🙂
yes - it is simply easy 😉
if you add 1 and 1 - excel calculate 2
and then you have to substitute the 2 - new = 0 respectively 1

Reply
Rob says:

September 11, 2011 at 4:16 pm

Here is a good resource for people wanting to learn binary and hexadecimal.
http://justwebware.com/bitwise/bitwise.html

Reply
Kyle McGhee says:

January 29, 2012 at 3:57 pm

Three that weren't asked for:
NOT
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),1,0),3,1)

EQV
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),2,3),1,0),3,1)

IMP
=SUBSTITUTE(SUBSTITUTE(A1+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,3),1,0),3,1),0,1),2,0)

(was using Daniel Ferry's bitwise file to verify against)

Reply
Q says:

February 27, 2012 at 9:05 am

@ Kyle

Not only takes one parameter and inverts 0 -1 and 1-0

Took out the +A2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,3),1,0),3,1)

Reply
Richard Tallent says:

November 1, 2012 at 3:57 pm

Great solutions!

I'll add two:

NAND =1*SUBSTITUTE (A1+A2,2,0)

NOR=1*SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A1+A2,0,2),1,0),2,1)

Reply
John Fuller says:

August 29, 2014 at 4:15 pm

This will work for binary numbers of any size (although the text format mask will have to have as many zeroes as there are digits in the longest addend)

Assume binary #s are in C35 & C36, then add and format as text in C37:
=TEXT(C36+C35,"000000000000")
-sum- = 101112211112

AND - SUBSTITUTE 0s for 1s in -sum-, then sub 1s for 2s
=SUBSTITUTE(SUBSTITUTE(C37,"1","0"),"2","1")

OR - sub 1s for 2s in -sum-
=SUBSTITUTE(C37,"2","1")

XOR - sub 0s for 2s in -sum-
=SUBSTITUTE(C37,"2","0")

Reply
Shane says:

September 21, 2014 at 3:47 am

Just wandered by:

AND:

=SUBSTITUTE(A1+A2,1,0)/2

Reply
- xlpadawan says:
  
  September 22, 2014 at 2:22 pm
  
  Clever, Shane. I like that.
  
  Reply
How do I perform a XOR calucation of two binary numbers in excel 2007 - QuestionFocus says:

December 9, 2017 at 2:27 pm

[…] post http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html for examples using Sumproduct, and http://chandoo.org/wp/2011/07/29/bitwise-operations-in-excel/ for examples using Text […]

Reply
Ronald Vonk says:

December 6, 2018 at 12:00 pm

Hi Chandoo,

I am not (yet) really into bitwise calculation, but I am looking for a way to speed up my vba calculation with very big numbers. Would is ben convenient to use bitwise notation for this?

Best regards,

Ronald (the Netherlands)
p.s. love your country!

Reply
- Hui... says:
  
  December 7, 2018 at 4:25 am
  
  @Ronald
  
  I'd suggest asking this in the Chandoo.org Forums
  https://chandoo.org/forum/
  
  Attach a sample file with an example of some data and describe what you want to achieve
  
  Reply

Show Decimal Points if needed [Quick Tip]

Chandoo

Easy way: Use General Formatting

What if your numbers are results of a calculation?

What if you want something fancy like accounting format, but turn off decimal values

To show decimal point if there is something after it

Do you deal with such situations when formatting numbers?

More on Number formatting in Excel

Get FREE Excel + Power BI Tips

Welcome to Chandoo.org

Mandatory Trainings: Are we doing them? [Data Analysis Challenge – 002]

FREE Calendar & Planner Excel Template for 2026

Who is my boss’s boss? [Data Analytics Challenge – 001]

New Zealand GST Calculation with Excel [Free Template]

Make a Pivot from Another Pivot Table in Excel

Related Tips

Mandatory Trainings: Are we doing them? [Data Analysis Challenge – 002]

Who is my boss’s boss? [Data Analytics Challenge – 001]

New Zealand GST Calculation with Excel [Free Template]

Make a Pivot from Another Pivot Table in Excel

How to use XLOOKUP with two sheets?

26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”

Leave a Reply

Get FREE Excel & Power-BI Newsletter

Get Started

Online Classes

About

Downloads