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

Microsoft Excel 2019 VBA and Macros

Microsoft Excel 2019 VBA and Macros »

Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful.

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!

Supercharge Excel When You Learn to Write DAX For Power Pivot

Supercharge Excel When You Learn to Write DAX For Power Pivot »

Hands-on book to learn and master the DAX language!

Microsoft Excel 2019 Pivot Table Data Crunching

Microsoft Excel 2019 Pivot Table Data Crunching »

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.

Microsoft Excel 2019 Inside Out

Microsoft Excel 2019 Inside Out »

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.

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.

see more products »