Excel, as we all know, is often referred to as an “electronic spreadsheet”. It was designed to take the place of the green accounting ledger paper consisting of a space to write comments followed by little green “squares (cells)” where a single number or letter was printed using either a pen or a pencil.
Excel’s “spreadsheet” appearance, unfortunately, is an electronic illusion. Remember how I compared Excel to a typewriter? Well, maybe Excel should be called the “new age” typewriter. Excel takes “WYSIWYG” to mesmerizing heights!
Excel appears on your computer screen as the “squares” of the accounting ledger (Note: each cell can hold 255 characters as opposed to 1 character). While the data entries seem to be made on a “ledger (spreadsheet)” they’re really typed into the Formula Bar (the white text bar sitting above the electronic “ledger”). Data gets “dressed-up” on the spreadsheet – not the Formula bar.
Excel processes data entry visually in 3 steps (from the floor up not the roof down)
- data is entered in the Formula Bar, but it appears to be entered in a spreadsheet cell
- a numeric format is applied to the entry (either a number or the default General format)
- your computer screen displays the formatted, customized data entry.
The Formula Bar entry remains unaltered, but the worksheet value appearance is “dressed up” (formatted) in some fashion. This “dressed up” view is both the “bird’s eye view” and the location where your data is “massaged/modified” format wise. Conversely, your data in the Formula Bar just sits there as entered.
Type 1000 into any cell. Excel internally sees 1000.00 not 1000 – 1000 followed by decimal values! Excel does this because of the way it is programmed to store dates and times.
If your cell contains a time format, like “h:mm:ss”, something visually odd happens in Excel. You enter 1000 in the Formula Bar, but it is somehow replaced with “9/26/1902 12:00:00 AM”. Then, because a time format is selected, 1000 in the spreadsheet view changes to “0:00:00”. Remember, appearances in Excel can be deceiving – which is why it isn’t a “WYSIWYG” typewriter!
And didn’t I just say original cell entries always remain unchanged?
So why does the time/date format appear to replace 1000 in the Formula Bar with “9/26/1902 12:00:00 AM”? This switch is conjured up, as just mentioned, by Excel’s internal date/time programming.
Though you see a Date and Time value in the Formula Bar, the original value – 1000- still lies underneath this date/time string. Otherwise, Excel wouldn’t show that date and time value.
Ah, appearances in Excel are deceptive, don’t you agree? Date and Time formats, along with the “birds eye view” exemplify this axiom. This is the only time the Formula Bar will not display the original value.
Date and Time formatted value are the only formats which seem to violate the Formula Bar original entry rule, but they don’t – they sit on top of your data, they don’t replace it. Mystifying, isn’t it?
Here’s why date/time formats do this.
The beginning of time in Excel was arbitrarily chosen to be 12 AM, January 1, 1900. Excel stores dates and time values as decimal serial numbers. The day component is expressed by the whole number before the decimal and the time component expressed by numbers after the decimal. A single second is equal to approximately 0.00001157407 seconds (1 day divided by 86,400 seconds per day).
In other words, Excel’s programming recognizes numbers before the decimal as dates and values (“0.00” in this scenario) after the decimal as time – the ‘decimal” portion of a 24-hour day. For example.125 would represent 3:00:00 AM in the morning. Or .78 translates to 6:43.12 PM.
So, when 1000 was typed into the Formula Bar, Excel saw 1000.00 as 1000 full days after the ‘beginning of time” without any time elapsing. 1000, as a value, still sits in the Formula Bar but is hidden underneath the “9/26/1902 12:00:00 AM” value. Excel can’t show a formatted value if the value is absent from a cell!
If you change the format to “m/d” – a date format- the Formula Bar would now show “9/26/1900” and your spreadsheet would display “9/26” because the year symbols (“yyyy”) weren’t included in the date only format. Change the date format to a currency format (sans zeros). 1000 now is visible in the Formula Bar unfettered-where it sat all along-but the “bird’s eye view” has now morphed into “$1000”!
Figure 6 – Value Appearing in Formula Bar versus Cell
“New” custom formats (both in appearance and number formatting) are created when a cell’s appearance is customized so that’s its active format is “unique” from any other cells in the entire file.
Alert: Excel counts Custom formats (e.g. fill, borders, fonts, etc.) and Conditional Formatting formats as entirely separate and distinct formats though the same formatting can be applied to a cell using either method.
They are considered distinct formats because the manually added format is performed on the “fly” (i.e. you decide when and how to change the cells format). However, Conditional Formatting is dependent upon true/false conditions (rules) that control when the format is activated. More memory is required to run Conditional Formats than it does to manually apply custom formats because of the true/false trigger logic.
Be aware that adding different fills, borders, fonts, conditional formats, etc. to your file will increase the file’s size and can cause file “bloating”. Too many custom formats in a file will also eventually slow down Excel’s calculation time as well.
In summary, both custom & Conditional formats both will consume a lot of computer resources. Number formats, on the other hand, normally consume much less.
Excel 2016 is the first version, unlike previous Excel versions, that doesn’t have a custom format limit and won’t freeze or “crash” like previous versions. Earlier Excel versions do have a custom format limit – between 4,000 to 64,000 custom formats – depending upon the Excel version. You can find this limit for your Excel version on the web. It is a good idea to review that documentation for your version to understand your Excel version limitations, if any.
So, if your files are calculating or updating slowly, reducing the number of custom formats and number of conditional formats might help speed them up – assuming you don’t have numerous array formulas lurking elsewhere on your spreadsheets (but that’s entirely different bedtime story).
All unformatted cells, by default, apply the “General” format. Translation: the cell entry appears as typed (“WYSIWYG”).To change the cell “clothes“ use the number formats provided in the “Number” submenu (on the Home Ribbon) or the Categories on Number tab in the Format Cells tool (dialog box).