Auto Format Excel numbers in thousands / millions / billions - 2 TRICKS💡

Ever wish you could auto--format numbers in Excel to thousands / millions / billions as you type?
In this video, let me show you two tricks.

📺 More on Custom Cell Formatting:

🧑‍💻 Advanced Excel Course:

~

#excel #formatting

[veronical.c]

This comes so handy! I'm currently trying to learn as much Excel as possible before next year😊

[sarahc]

Perfect timing of getting your email about this video!! You're tips are SO helpful, thank you!

[MAH]

I like all the formats shown by you, it really helps me.

Thank you Chando

[kennyhammad]

"Your channel is exceptional - clear, informative, and truly educational. I find it incredibly rewarding to follow your work. I'd love to see a tutorial on creating a patient data entry system for a doctor's office without using VBA. This system should include patient information, services rendered, costs, total billing, payments, balances, diagnostics, and notes. The ability to easily modify formulas would be a huge asset. Your step-by-step approach to building formulas is captivating."

[arbazahmad]

Excellent... 2nd one is great👍.... thanks for sharing 🎉...

[Excelambda]

Great video!! Formatting stuff + new released regex formats are extremely technical. Will never know enough so every example helps a lot. 😉✌
For fun, quick lambda draft. Easy to add trillion format or even more, :
KMBT(a, [k])
where:
a: any array of numbers
k: if omitted, "-" format, if k=1 "(...)" format

=LAMBDA(a, [k],
LET(
t, {"K", "M", "B", "T"},
s, {3, 6, 9, 12},
r, REDUCE(
ABS(a),
s,
LAMBDA(v, i,
LET(
x, IFERROR(v / 10 ^ i, v),
IF(
x < 1000,
IF(x < 1, v, ROUND(x, 1) & INDEX(t, i / 3)),
v
)
)
)
),
IF(LEFT(a) = "-", IF(k, "(" & r & ")", "-" & r), r)
)
)

[Michael_Alaska]

Great explanation. Appreciate the sample file, importing a picture did not go as smooth as hoped. Why the extra three 9's in the first row of the table?

[Bhavik_Khatri]

The second approach appears to be more effective.

[aks]

Hi Chandoo, looks really easy formatting with this. Any such formula available to convert into Hundreds?

[syedarslanshakir]

Dear Chando, which desktop marker tool do you use? Specially the zoom function, because it is really nice.

[rajaanishetti]

Thank you so much for the video.
Converting negative numbers is awesome. I have a question in the lookup range, I used the range $J5$:$k11$ it's working but in the video You have taken the lookup range $J5$:$J11$, $k5$:$k11$ is there any specific reason?

[Amank]

Thank you so much for this interesting details

I am trying to put the same code in VBA Macro but it's giving me the Syntex error please help me

[stefankirst]

Thanks for the Video (all your videos, really)!
Have you experienced issues with the Text function if users with different language settings open the file? In my experience in a different context the Text function doesn´t seem to be the most robust.

[tibibara]

Hi Chandoo, I loved that tutorial, especially the Lookup solution, it's very-very PRO, and you gave me also an idea to do it portable with LAMBDA/LET.
2 Lambdas: AutoFormatNumbers, AutoFormatNumbersAccounting -> with [optional] decimal place argument. The default decimal place: 1
So it's enough to select the whole range of values and the DAA works properly: =AutoFormatNumbers(A1:A10, 3) - example with 3 decimal places.

AutoFormatNumbers=LAMBDA(values, [decimal],
LET(
lcolumn, VSTACK(
-REPT(9, 15),
-REPT(9, 9),
-REPT(9, 6),
-REPT(9, 3),
--(1 & REPT(0, 3)),
--(1 & REPT(0, 6)),
--(1 & REPT(0, 9))),
decp, IF(ISOMITTED(decimal), 1, decimal),
dp, REPT(0, decp),
ap, CHAR(34),
dot, CHAR(46),
emp, T(0),
b, "#, ###, , , .",
m, "#, ###, , .",
k, "#, ###, .",
bs, SUBSTITUTE(b, dot, emp),
ms, SUBSTITUTE(m, dot, emp),
ks, SUBSTITUTE(k, dot, emp),
bsc, IF(decp = 0, bs, b),
msc, IF(decp = 0, ms, m),
ksc, IF(decp = 0, ks, k),
rcolumn, VSTACK(
bsc & dp & ap & "B" & ap,
msc & dp & ap & "M" & ap,
ksc & dp & ap & "K" & ap,
"#, ##0",
ksc & dp & ap & "K" & ap,
msc & dp & ap & "M" & ap,
bsc & dp & ap & "B" & ap),
mylookup, LOOKUP(values, lcolumn, rcolumn),
myformat, TEXT(values, mylookup),
myformat))

AutoFormatNumbersAccounting=LAMBDA(values, [decimal],
LET(
lcolumn, VSTACK(
-REPT(9, 15),
-REPT(9, 9),
-REPT(9, 6),
-REPT(9, 3),
--(1 & REPT(0, 3)),
--(1 & REPT(0, 6)),
--(1 & REPT(0, 9))),
decp, IF(ISOMITTED(decimal), 1, decimal),
dp, REPT(0, decp),
ap, CHAR(34),
dot, CHAR(46),
emp, T(0),
b, "#, ###, , , .",
m, "#, ###, , .",
k, "#, ###, .",
pre, ";(",
bs, SUBSTITUTE(b, dot, emp),
ms, SUBSTITUTE(m, dot, emp),
ks, SUBSTITUTE(k, dot, emp),
bsc, IF(decp = 0, bs, b),
msc, IF(decp = 0, ms, m),
ksc, IF(decp = 0, ks, k),
rcolumn, VSTACK(
pre & bsc & dp & ap & "B" & ap & ")",
pre & msc & dp & ap & "M" & ap & ")",
pre & ksc & dp & ap & "K" & ap & ")",
"#, ##0;(#, ##0)",
ksc & dp & ap & "K" & ap,
msc & dp & ap & "M" & ap,
bsc & dp & ap & "B" & ap),
mylookup, LOOKUP(values, lcolumn, rcolumn),
myformat, TEXT(values, mylookup),
myformat))

created with AFE in Excel Labs add-in.

[bloggingbyasifali]

How many Power bi developers will be there in organisation? Startup, mid level and MNC?

[ericpolstra]

It appears that the limitation of the second approach is that it is now text and not a value. The first approach keeps it a value, meaning it can be used as an input to other formulas. Am I missing something?

[naeemsaleem-gb]

Chando sir tell us shortcut to open right click on the tab sheet below using keyboard

[Ayyappamanasu]

Hi chandoo sir, please support me to find unnecessary increase in the excel size

[swapneiltamhankar]

I have 52 columns in which the first 4 columns have data under the column header. And in remaining columns i might have data . It's not compulsory that each cell.under each column will have data. Some columns will blank, some columns will have 1 cell empty, in some columns 2 or more cells empty. How to write formula which filters all columns which has blank cells ?

[jerrydellasala]

One thing I don't like about your formats is that there are varied number of decimal points and the formats not followed by a letter are unaligned. I do like the LOOKUP solution but should be right aligned.!