Change number format based on cell value | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★

★ About this video ★
You've built an amazing dynamic report where a user can select a business unit to view, and everything updates automatically - it's beautiful. The only issue is that Company A has sales of $30,000,000, Company B has sales of $30,000.

Displaying values in 1 decimal place millions makes sense for Company A. $30,000,000 becomes $30.0m. But it does not make sense for Company B. $30,000 become $0.0m - pretty useless.

The companies have different levels of significance. How can we format numbers so they work with either company?

We will look at two techniques in this post.

0:00 Introduction
0:33 TEXT Function
3:02 Conditional Formatting
5:50 Conclusion

What to know more about number formats?

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[ianl]

More Excel magic. Liked and subscribed. Thanks for your time and effort.

[IvanCortinas_ES]

As always, a great analysis of the case. Thank you very much for this complete example of formats.

[Al-Ahdal]

Excellent Approach for this particular issue. My First Comment!

[usmanahmad]

Please tell the pros and cons of applying TEXT function and applying Conditional Formatting. Thanks

[johnboffin]

Hello Sir, I am building a spreadsheet with 7 columns and 2500 rows. Column A is just a reference column, B is dates column which are fine, column C is with Validation drop down codes list for transaction, column D is of transaction details with drop down, tested and working all down to 2500 rows. Column E is payments and F is Income, G is balance (Balance column work as it should) now I need your input in column E and F, If I select from column C (Codes Drop Down List) for Sales, Refund, Finance or Cash Back The entry should only allow me the income column which is column F and if selected payment (Transaction) from C column (Codes) it should allow me to enter in column E "payments column". I have 4 source of income and payments I have about 44 or it may grow prior (My code list and details list grows as it should). I need help using validation (Custom) in these two columns E & F, so that the entry don't get switched between two column E & F. dependent from column C drop down, Your help will be appreciated and thanks a lot if you could help.. (I use Excel 2000)