How to Format Cells in Excel

Formatting is what separates a spreadsheet that’s technically correct from one that’s actually easy to read and present. I’ve seen perfectly accurate spreadsheets get misunderstood simply because numbers weren’t formatted as currency, or dates displayed inconsistently. In this guide, I’ll walk through everything I do to format cells properly in Excel — from number formats to borders, fonts, and conditional formatting.

What Cell Formatting Actually Does

Formatting changes how data looks, not what it actually is. A cell containing the number 1000 can display as “1,000,” “$1,000.00,” or “1000%” depending on formatting — but the underlying value Excel uses in calculations stays 1000 unless you actually change the number itself. Understanding this distinction is key to avoiding confusion later.

Accessing the Format Cells Dialog

There are three ways I open the main formatting window:

1. Right-click a cell (or selected range) and choose Format Cells.
2. Select a cell and press Ctrl + 1.
3. Go to the Home tab and click the small arrow in the bottom-right corner of the Number group.

All three open the same Format Cells dialog with tabs for Number, Alignment, Font, Border, Fill, and Protection.

Number Formatting

The Number tab is where I spend most of my formatting time. Common categories include:

• General – Excel’s default, no specific formatting applied.
• Number – lets me set decimal places and thousand separators, e.g., 1,234.56.
• Currency – adds a currency symbol (like $ or PKR) with proper decimal alignment.
• Accounting – similar to Currency but aligns symbols and decimals in a column for cleaner financial statements.
• Percentage – multiplies the value by 100 and adds a % sign, e.g., 0.25 becomes 25%.
• Date – multiple formats like DD/MM/YYYY, MM-DD-YY, or long forms like “15 August 2026.”
• Time – displays hours, minutes, and seconds in various formats.
• Fraction – shows decimal values as fractions, like 0.5 becomes 1/2.
• Text – forces Excel to treat the entry as plain text, even if it looks numeric (useful for things like ID numbers with leading zeros).
• Custom – lets me build my own format code for very specific display needs.

Step-by-Step: Applying Currency Formatting

1. Select the range of cells.
2. Press Ctrl + 1.
3. Click Currency under Category.
4. Choose the currency symbol and set decimal places (I usually keep 2 for money).
5. Click OK.

Step-by-Step: Using Custom Number Formats

Custom formats give me full control. For example, if I want numbers to always show “kg” after the value:

1. Open Format Cells > Number > Custom.
2. In the “Type” box, enter: 0 "kg"
3. Click OK.

Now 25 displays as “25 kg” while the underlying value stays a pure number I can still calculate with.

Font Formatting

Under the Font tab, I can change:

• Font style (like Calibri, Arial)
• Size
• Bold, Italic, Underline
• Color
• Strikethrough (useful for marking completed or cancelled items)

I usually keep these accessible directly from the Home tab ribbon rather than opening the full dialog, since they’re used so frequently.

Alignment Formatting

The Alignment tab controls:

• Horizontal alignment (left, center, right)
• Vertical alignment (top, middle, bottom)
• Text wrapping (so long text shows on multiple lines within one cell instead of spilling over)
• Text rotation (useful for angled column headers in tight tables)
• Merge cells (combining multiple cells into one, often used for titles)

I use Wrap Text constantly for cells containing longer notes or descriptions, so the row height adjusts automatically instead of the text getting cut off.

Borders and Fill

Under Border, I can add lines around cells — useful for separating sections or creating a clean table look manually. Under Fill, I set background colors, which I often use for:

• Highlighting headers with a distinct color.
• Color-coding categories (like red for overdue, green for completed).
• Alternating row shading for readability in long tables (though I usually use Table styles for this instead, since they handle it automatically).

Conditional Formatting (Dynamic Formatting Based on Values)

This is where formatting becomes genuinely powerful. Instead of manually coloring cells, I let Excel apply formatting automatically based on rules:

1. Select your range.
2. Go to Home > Conditional Formatting.
3. Choose a rule type:
   • Highlight Cells Rules – like “Greater Than,” “Less Than,” “Between,” “Text that Contains.”
   • Top/Bottom Rules – highlight the top 10 items, bottom 10%, above average, etc.
   • Data Bars – shows an in-cell bar proportional to the value, great for quick visual comparison.
   • Color Scales – shades cells from one color to another based on value (like red for low, green for high).
   • Icon Sets – adds arrows, flags, or traffic-light icons based on value thresholds.
4. Set your specific condition and formatting style.
5. Click OK.

I use conditional formatting constantly for things like automatically flagging overdue dates in red, or highlighting sales figures below target.

Using Format Painter

If I’ve already formatted one cell exactly how I want and need to apply the same formatting elsewhere:

1. Click the formatted cell.
2. Go to Home > Format Painter (the paintbrush icon).
3. Click (or click-and-drag across) the cells you want to apply the same formatting to.

Double-clicking the Format Painter icon lets me apply it repeatedly to multiple separate areas instead of just once.

Practical Example

Say I’m building a monthly budget tracker:

• I format the “Amount” column as Currency with 2 decimal places.
• I format the “Date” column as DD-MMM-YYYY (e.g., 15-Aug-2026).
• I apply Conditional Formatting so any expense over 50,000 automatically highlights in red.
• I use Wrap Text on a “Notes” column so longer comments display fully.
• I bold and shade the header row for clarity.

The result looks organized and professional without me manually adjusting a single cell by hand after the initial setup.

Common Mistakes I See

• Confusing formatting with the actual value. Formatting a cell as Text doesn’t convert existing numbers into text automatically — you often need to re-enter the data after changing the format.
• Applying Currency/Percentage formatting to the wrong base value, like formatting “25” as a percentage and getting “2500%” instead of “25%” (percentages expect the underlying value to already be a decimal, like 0.25).
• Overusing colors and borders, making the sheet visually noisy instead of clearer.
• Forgetting that conditional formatting rules can overlap or conflict, producing unexpected results — check Home > Conditional Formatting > Manage Rules if something looks off.
• Manually formatting each cell instead of using Table styles, which handle borders, shading, and header formatting automatically and consistently.

Troubleshooting Tips

• If a percentage shows something like “2500%” instead of “25%,” check whether the underlying number is 25 instead of 0.25 — percentage formatting multiplies by 100.
• If dates display as serial numbers (like 45900) instead of an actual date, apply Date formatting from the Number tab.
• If conditional formatting doesn’t seem to apply, check Home > Conditional Formatting > Manage Rules to see if another rule is overriding it, or if the range selected is wrong.
• If numbers appear left-aligned instead of right-aligned, they’re likely stored as text — check the format and consider using =VALUE() to convert them to real numbers.

Real-World Use Cases

• Finance teams formatting currency and percentages consistently across financial statements.
• Project managers using conditional formatting to flag overdue tasks in red automatically.
• Teachers using color scales to visually show which students scored highest or lowest.
• Businesses formatting invoices with consistent fonts, borders, and currency symbols for a professional look.
• Data analysts using data bars and icon sets to make raw numeric tables easier to scan quickly.

Best Practices I Follow

• Format numbers according to what they represent — currency for money, percentage for rates, date for dates — rather than leaving everything as “General.”
• Use conditional formatting instead of manually coloring cells whenever the rule is based on a value threshold — it stays accurate even as data changes.
• Keep color use purposeful and limited; too many colors reduce readability rather than improving it.
• Use Table styles for consistent borders, shading, and header formatting instead of manually formatting every cell.
• Use Format Painter to keep formatting consistent across a sheet instead of repeating manual steps every time.

Formatting cells properly is a small habit that pays off constantly — it’s the difference between a spreadsheet someone has to squint at and one that communicates clearly the moment it’s opened. Once conditional formatting and consistent number formats become second nature, your spreadsheets start looking like finished reports rather than raw data dumps.