MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Format as a Façade

November 14, 2019 - by Bill Jelen

Excel Format as a Façade. Photo Credit:

Excel is amazing at storing one number and presenting another number. Choose any cell and select Currency format. Excel adds a dollar sign and a comma and presents the number, rounded to two decimal places. In the figure below, cell D2 actually contains 6.42452514. Thankfully, the built-in custom number format presents the results in an easy-to-read format.

Quantity in column B is formatted with no decimal places and a comma for a thousands separator. Revenue in column C adds two decimal places. Average Price in column D has a currency symbol and two decimal places.

The custom number format code in D2 is $#,##0.00. In this code, 0s are required digits. Any #s are optional digits.

However, formatting codes can be far more complex. The code above has one format. That format is applied to every value in the cell. If you provide a code with two formats, the first format is for non-negative numbers, and the second format is for negative numbers. You separate the formats with semicolons. If you provide a code with three formats, the first is for positive, then negative, then zero. If you provide a code with four formats, they are used for positive, negative, zero, and text.

continue reading »

More Reading

Excel Fill in a Flash. Photo Credit: lee junda at

Excel 2019: Fill in a Flash »

November 13, 2019 - by Bill Jelen

Excel 2013 added a new data-cleansing tool called Flash Fill.

Excel Perform Sentiment Analysis in Excel. Photo Credit: Justin Bashore at

Excel 2019: Perform Sentiment Analysis in Excel »

November 11, 2019 - by Bill Jelen

It is easy to quantify survey data when it is multiple choice: You use a pivot table to figure out the percentage for each answer. But what about free-form text answers? These are hard to process if you have hundreds or thousands of them.

read more articles »

IMA Data Analytics Leveraging Excel

Featured Products

Excel Subtotals Straight to the Point

Excel Subtotals Straight to the Point »

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.

Excel Dynamic Arrays Straight to the Point

Excel Dynamic Arrays Straight to the Point »

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.

Excel 2019 Business Basics & Beyond

Excel 2019 Business Basics & Beyond »

Are you tired of tracking financial elements of your business manually, do you have to prepare staffing schedules by hand, or do you simply want to know how to get more information from your business data? Learn how to harness your business data and put it to use for you.

The Absolute Guide to Dashboarding & Reporting with Power BI

The Absolute Guide to Dashboarding & Reporting with Power BI »

Learn Power BI Dashboarding Techniques by Watching A Pro!

MrExcel LX – The Holy Grail of Excel Tips

MrExcel LX – The Holy Grail of Excel Tips »

A book for people who use Excel 40+ hours per week. Illustrated in full color.

Excel JavaScript UDFs Straight to the Point

Excel JavaScript UDFs Straight to the Point »

JavaScript custom functions - UDFs can be used like any other native functions or UDFs in Excel. This book shows the process of creating JavaScript UDFs in Excel Developer Preview for Office Insider program subscribers.

see more products »