Pivot Tables & Charts, VBA Macros

Currency format Pivot fields with one click [Friday VBA]

Last updated on October 7, 2016

Chandoo

Anyone who has made a pivot table and their grandma knows that formatting them is a pain. Let’s recap the steps to apply one of the most common formats – currency format.

Right click on any value field
Go to Value field settings
Click on “Number Format” button
Choose Currency format
Close the boxes, one after another

Unless you get paid per click, you wont be happy with all those clicks.

Wouldn’t it be cool to just click once and apply most common format to your pivot fields?

Of course you can. Just add oneClickCurrency macro to your personal macros workbook. And then add this to your Home ribbon as a custom button and you have a one click format option for any pivot.

oneClickCurrency Macro

So are you ready for the code? Its so tiny, you could type it faster than manually formatting the pivot fields yourself 😉


Sub oneClickCurrency()
    On Error GoTo GameOver
    Dim pName As String, pfName As String
    
    pName = ActiveCell.PivotTable.Name
    pfName = ActiveCell.PivotField.Name
    
    With ActiveSheet.PivotTables(pName).PivotFields(pfName)
        .NumberFormat = "$#,##0.00"
    End With
GameOver:
End Sub

When copy pasting this code to your personal macros workbook, change the $#,##0.00 format code to any other formatting you want to use. Here are a few more common ones.

Accounting format (negative values in brackets, no zeros): _($* #,##0.00_);_($* (#,##0.00);_($* “”-“”??_);_(@_)
Negative amounts in red color: $#,##0.00;[Red]$#,##0.00
Amounts with no decimals: $#,##0

So there you go. I just save you from a massive tax. Click tax that is.

George Costanzaesque macros for you

Short & fun, that is how I like my macros. Here are a few you should add to your personal macros workbook to save time & get more out of Excel.

How do you format your pivots?

For most of my work, I rely on Power Pivot, which allows you to set up format options when defining a measure. But whenever I use pivots, I end up paying click tax for the formatting. Hence the macro.

What about you? How do you format your pivots? You can customize the above macro to include additional steps that you often do (changing layouts etc.) Please share your techniques & thoughts in the comments section.

Share this tip with your colleagues

4 Comments
Ask a question or say something...
Tagged under downloads, Learn Excel, macros, Personal Macro Workbook, pivot tables, screencasts, VBA
Category: Pivot Tables & Charts, VBA Macros

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

Currency format Pivot fields with one click [Friday VBA]

Chandoo

oneClickCurrency Macro

George Costanzaesque macros for you

How do you format your pivots?

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