Excel doesn't change your numbers when you format them. It changes how they look. That distinction matters more than most people realize — and it's the single biggest reason spreadsheets misbehave.
Type 0.5 in a cell, apply Percentage, and the cell shows 50%. The actual value? Still 0.5. Type 44927 with Date formatting and you'll see January 1, 2023. Same number, different mask. Once you internalize that idea, every weird Excel display starts to make sense.
This guide walks through every built-in number format Excel offers — General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text — plus the custom format language that powers everything from phone numbers to color-coded budgets. You'll see when to use the Home tab shortcuts versus the full Format Cells dialog, why TEXT() sometimes beats formatting, and how to build custom codes that handle negatives, zeros, and conditional colors.
Worth knowing: most users learn three or four shortcuts and stop. That's a mistake. The Format Cells dialog hides genuinely useful tools — millions shown as "M", phone numbers that auto-add parentheses, dates that show only the weekday. None of these need formulas. They're built into excel already.
Before you start, pin one fact: formatting affects display only. If you need the actual stored value to change — strip decimals, convert a date to text, round to thousands — you need a excel formulas function, not a format code. We'll cover that distinction throughout.
Quick orientation: Excel ships with twelve built-in number format categories. Most people use only three. The other nine are where the real time savings hide. By the end of this guide you'll know which one to grab for any situation — from a 16-digit credit card field to a dashboard tile that needs to read "$2.4M" instead of "2400000".
Excel stores every value as either a number, a date serial (which is also a number), or text. Number formatting tells Excel how to display that stored value — it never changes the underlying data. Open Format Cells (Ctrl+1) to see all twelve built-in categories, or use the Home tab dropdown for one-click formats like Currency, Percentage, and Date.
For real transformations — converting a number to text, joining values with a label, locking a display format inside a formula — use the TEXT() function instead. It returns a text string that always renders the way you specified, even when copied or referenced elsewhere.
Two paths reach the same destination — but they're not equal. The Home tab gives you Currency, Percent, and Comma styles with a single click. Fast, but limited. The Format Cells dialog (Ctrl+1) opens twelve categories, every decimal option, negative-number styling, and the full custom code language. Memorize Ctrl+1. It's the most useful Excel shortcut almost nobody talks about.
Quick story: a client once spent twenty minutes typing dollar signs and commas into a quote sheet by hand. Selecting the whole column and hitting Ctrl+Shift+$ would've taken two seconds. The Home tab shortcuts cover roughly 80% of daily needs: Ctrl+Shift+1 for Number, Ctrl+Shift+4 for Currency, Ctrl+Shift+5 for Percent, Ctrl+Shift+# for short Date, Ctrl+Shift+@ for time, Ctrl+Shift+! for the comma thousand-separator.
Anything beyond the basics needs the dialog. Phone numbers, scientific notation, fractions like 3/8 instead of 0.375, dates that show only the weekday name, currencies that aren't the dollar — all of those live inside Format Cells. The dialog also previews your code against an actual sample cell value, which saves trial and error. Click around it once. The categories on the left, options in the middle, sample preview at the top — that's your reference desk for everything number-related in Excel.
Beside the Number Format dropdown sits a tiny gallery: Accounting, Percent, Comma. Click Accounting and Excel applies the locale currency symbol, aligns it to the left edge of the cell, and pads negatives with parentheses. That's a different beast from regular Currency — Currency floats the symbol next to the value, Accounting locks it to the left. Most finance teams prefer Accounting; it makes columns of numbers line up cleanly down the page.
Custom codes are where Excel's number formatting goes from useful to genuinely impressive. The grammar takes ten minutes to learn and then unlocks a thousand use cases. Every custom code has up to four sections separated by semicolons: positive;negative;zero;text. You can omit later sections — most codes only define one or two.
The two key digit placeholders are 0 and #. The zero forces a digit to appear. The hash shows a digit only if one exists. So 0.00 displays 5 as 5.00, but #.## displays 5 as just 5. Add a comma between digits like #,##0 to get thousand separators. Place a comma at the very end of the code — #,##0, — and Excel divides the display by 1,000 (think in thousands). Two trailing commas? Display in millions.
Colors and conditions live inside square brackets. [Red], [Blue], [Green] all work, plus six others. Place them at the start of a section: [Green]#,##0;[Red](#,##0) shows positives green and negatives red in parentheses. Add conditional logic the same way: [Red][<0]#,##0;[Green][>=1000]#,##0;0 applies different formats based on the value itself. Two thresholds max per code — Excel parses them strictly left to right.
Default reading order: section 1 handles positives, section 2 handles negatives, section 3 handles zeros, section 4 handles text input. Skip a section and Excel uses section 1 for it. Use three semicolons with nothing else (;;;) and the cell shows blank no matter what value lives there — a quick hide trick that beats white font color or hidden columns.
General is the default applied to every cell until you change it. Numbers display exactly as typed up to about 11 digits, then Excel switches to scientific notation. Decimals show as many places as you entered. Text aligns left, numbers align right — that's how you spot whether Excel sees a value as text or as a true number. Avoid General for any column you'll do math on. The auto-switch to scientific notation surprises people when account numbers or zip codes suddenly appear as 1.23E+12.
The Number format gives you control over decimal places (0-30), thousand separators, and how negative values display. Four negative styles: -1234.10, red -1234.10, (1234.10) in black, or red (1234.10). For most data tables, set 2 decimals with thousand separator. For inventory counts or quantities, set 0 decimals.
Currency places a currency symbol immediately to the left of the number — $1,234.56. Twenty-plus symbols are available in the dropdown, from the dollar to the euro, yen, pound, and the Swiss franc. The symbol moves with the number when widths change, which can make column alignment messy in financial reports. Use Accounting instead for ledgers.
Accounting locks the currency symbol to the left edge of the cell and the number to the right edge. Zeros display as a centered dash (-) instead of $0.00. Negatives go in parentheses. This format is what auditors, controllers, and bookkeepers reach for. Shortcut: Ctrl+Shift+$ on most keyboard layouts.
Date formats convert serial numbers (days since January 1, 1900) into readable dates. Short Date shows 1/15/2026, Long Date shows Thursday, January 15, 2026. Custom codes unlock anything: mmm-yy for Jan-26, dddd for the weekday name only, mm/dd/yyyy hh:mm for date-time stamps. Remember: dates are stored as plain numbers, so you can do arithmetic on them — subtract two dates to get days between.
Time represents fractions of a 24-hour day. Noon = 0.5, 6 PM = 0.75. Built-in formats include 13:30:55, 1:30 PM, and elapsed time codes like [h]:mm:ss which show hours past 24 (useful for timesheets totaling more than one day's work).
Percentage multiplies the stored value by 100 for display only — 0.5 shows as 50%. Type a value into an already-formatted percent cell and Excel respects the format: 20 becomes 20%, not 2000%. Adjust decimal places via the dialog or the toolbar buttons (.0 ←→ .00 increase/decrease decimals).
Fraction displays decimals as fractions — 0.5 becomes 1/2, 0.375 becomes 3/8. Choose denominator precision: one digit (halves), two digits (sixteenths), or fixed denominators (eighths, tenths, hundredths). Useful for engineering tolerances and recipe scaling.
Scientific shows numbers in exponential notation: 1.23E+05 means 1.23 × 10^5 = 123,000. Common in physics, chemistry, and astronomy data. Set decimal precision via the dialog. The auto-switch to scientific notation for huge numbers under General formatting catches everyone at least once.
Text tells Excel to treat the cell content as literal text, not as a number — even if you typed digits. Leading zeros stay (0042 stays 0042), formulas don't evaluate (=A1+B1 stays as that string), and long account numbers don't get scientific-notation mangled. Apply Text format BEFORE typing or pasting, not after — converting an existing number cell to Text doesn't retroactively un-mangle it.
Here's where it gets interesting. Custom format codes solve display problems that would otherwise need helper columns and TEXT() formulas. Below are recipes I've used (and reused) for years — copy them straight into the Format Cells dialog's Custom category. Each one took me longer to discover than it should have. Steal them, save them as Cell Styles, and you'll reach for the same five or six codes again and again.
Type 5551234567 into a cell and Excel shows 5551234567. Apply the custom format (###) ###-#### and the same cell now displays (555) 123-4567. The stored value never changes — sort, filter, and lookup operations still work on the digits. Looking for the official Microsoft Excel approach to phone-number storage? The same logic applies for the microsoft excel mobile app and Excel Online.
SSN format: 000-00-0000. ZIP+4: 00000-0000. Both use zero placeholders because you want leading zeros preserved — a ZIP starting with 0 (Massachusetts, New Jersey) would lose that zero under General formatting. Type 02134 with General and Excel saves 2134. Apply Text format or use a custom 00000 code and the leading zero stays visible.
Standard accounting display: #,##0.00;[Red](#,##0.00);-. Positives show with two decimals and commas, negatives appear in red parentheses, zeros display as a single dash. This single code does what most people achieve with conditional formatting plus a custom number format combined.
Display thousands: #,##0,"K" turns 24500 into 25K. Display millions: #,##0,,"M" turns 1,250,000 into 1M. Two trailing commas divide by a million for display. Useful for dashboards where exact pennies don't matter and column width is tight. The actual stored value stays exactly what you typed — only the display rounds.
Show only the weekday: dddd. Show short month plus year: mmm-yy. Show a full timestamp: yyyy-mm-dd hh:mm:ss. Combine with text using quotes: "Week ending "ddd, mmm d. Want fiscal-year labels? Try "FY "yy which appends FY to a 2-digit year.
Format Painter copies formatting (including number formats) from one cell to another. Select a formatted cell, click the paintbrush icon on the Home tab, then drag across the target cells. Done. Double-click the paintbrush instead of single-click and it stays active — paste the format into multiple non-contiguous ranges. Press Esc to release.
Shortcut: there isn't a built-in keyboard shortcut for Format Painter in Excel, which surprises people. Workaround? Use Ctrl+C to copy, then Alt+E+S+T (Paste Special > Formats) or Ctrl+Alt+V then T then Enter. Three keys instead of clicking the icon.
Paste Special with the Formats option does the same job and supports keyboard navigation. For repeated work — applying the same format down a column every Monday — record a macro or save a custom cell style. The Cell Styles gallery on the Home tab lets you save any combination of font, color, border, AND number format under a single name.
Cell formatting changes display. TEXT() returns a string with that display permanently baked in. They look identical on screen but behave differently in formulas. When you reference a formatted cell in a SUM or VLOOKUP, Excel sees the underlying number. When you reference a TEXT() result, Excel sees a string — which can break math.
TEXT() wins when you need to join formatted values into a sentence: ="Total: "&TEXT(B5,"$#,##0") produces Total: $1,234 as a single string. Without TEXT(), the concatenation drops formatting and shows Total: 1234. The function takes two arguments — a value and a format code. Format codes match the custom number format syntax exactly. Want to learn the broader function family? The excel text function guide walks through every variant.
Use cell formatting when the value will be used in further math — sums, averages, sorts, filters. Use TEXT() when you need a string for concatenation, file naming, custom labels in chart titles, or report headers built from formulas. Many dashboards combine both: format the cells for display, then use TEXT() inside helper columns to build report-ready labels elsewhere.
True conditional formatting (Home tab > Conditional Formatting) applies formatting based on cell values relative to other cells or thresholds. It can change number formats, fill colors, fonts, and add data bars or icon sets. This is different from the [Red][<0] custom code trick — that's a static rule baked into one cell. Conditional formatting evaluates dynamically against the whole range. For deeper coverage of conditional rules and color rules, check the how to highlight duplicates in excel walkthrough.
Common conditional number format uses: highlight cells over a budget threshold in red, color the top 10 sales reps green, apply a data bar showing relative size of revenue values, or display traffic-light icons (red/yellow/green) for KPI ranges. Each rule sits in a manager you access via Conditional Formatting > Manage Rules.
The most flexible setups combine both. Use Conditional Formatting to highlight cells meeting a condition (fill color red if > threshold), and use a custom number format on the same cells to display the value as needed (millions, thousands, decimals). Excel applies both layers, and they don't conflict — formatting affects appearance, conditional formatting overlays on top.
Click a single cell, drag across a range, or click a column header. You can apply the same format to multiple non-contiguous ranges by holding Ctrl while clicking.
Press Ctrl+1. Or right-click and choose Format Cells. The dialog opens directly to the Number tab — exactly where you need it.
Choose from General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, or Custom. Each shows a sample preview of your data.
Adjust decimals, separator, negative style, currency symbol, or date pattern. For Custom, type your code in the Type box. The Sample area updates live.
The format applies immediately. Test it by typing a new value into one of the cells. The format persists when you save the workbook.
To apply the same format to other cells later, click a formatted cell, hit the paintbrush, then drag across the target. Double-click the brush to lock it active.
Three errors come up almost weekly in support forums. First: applying Currency or Number format to a column already containing text values. Nothing happens. The cells still show as text because Excel respects existing data types — you have to convert text to numbers first (Data > Text to Columns, finish wizard, or use VALUE()).
Second mistake: typing a leading zero into a General-formatted cell. Excel strips the zero. Phone numbers, account IDs, ZIP codes all suffer. Fix: format the column as Text before pasting or typing. Or apply a custom format like 00000 to force leading zeros on display. Note: if you need the actual value to start with a zero (for string operations elsewhere), Text format is the only correct choice.
Type a 16-digit credit card or invoice number into a General cell and Excel converts it to scientific notation: 1.23457E+15. Worse, the trailing digits get rounded to zero — credit card 1234567890123456 becomes 1234567890123450. Fix: format the column as Text BEFORE pasting. After-the-fact formatting doesn't restore the lost digits.
Type 3/4 in a General cell. Excel sees that as a date (March 4) and stores serial 45720. You wanted the fraction three-fourths. Two fixes: format as Text before typing, or prefix with an apostrophe ('3/4) which forces text entry without changing the cell format. The apostrophe doesn't display — it's a marker only.
Veterans keep a small worksheet of go-to format codes. Mine has columns for: format name (e.g., "Phone US"), sample input (5551234567), format code ((###) ###-####), and sample output ((555) 123-4567). Five minutes spent building this reference saves hours of trial and error later.
Common entries: Phone US (10 digits, parens-dash format), SSN (000-00-0000), ZIP+4 (00000-0000), Money K (#,##0,"K"), Money M (#,##0,,"M"), Negative Red Parens (#,##0;[Red](#,##0);0), Date Friendly (dddd, mmm d), Time HMS ([h]:mm:ss), Percent 1dp (0.0%), Fraction Eighths (# ?/8).
Better than a reference sheet — save formats as named Cell Styles. Home tab > Cell Styles > New Cell Style. Give it a name ("Phone US"), check only the Number Format box, and the style remembers your custom code. Apply with two clicks anywhere in the workbook. Cell styles travel with the workbook — colleagues opening it get the same styles instantly.
Number formatting is the foundation of clean, professional Excel work. Master the dozen built-in formats and you handle 80% of daily needs in seconds. Learn the custom code language and you solve display problems most people brute-force with formulas and helper columns. Combine with TEXT() and Conditional Formatting and your spreadsheets start to look like dashboards instead of data dumps.
The next step? Pick three custom codes from this guide — phone numbers, large-value K/M abbreviations, red-parenthesis negatives — and use them on your current workbook today. Speed gains compound. Once these become muscle memory, every spreadsheet you touch will look more polished and behave more predictably. Need to brush up on the underlying functions that often pair with formatting? The how to use excel guide covers the wider toolkit.