Data Formatting in Excel (With Examples & Shortcuts)

Types of Data Formatting in Excel

Excel offers six main ways to format data. Some change only appearance, while others add real functionality. Here is a breakdown of different types.

1. Number Formatting

It changes the presentation of a number, not the real value. For calculation, Excel use the true value.

• Example: 845.5 can become ₹845.50 or 845%
• Fix: Select cell(s), go to the Home tab, then the Number section
• For More Control: Press Ctrl + 1, open Format Cells, and set custom decimals, currency symbols, etc.

2. Date & Time Formatting

A date can be present in different formats like, 12/09/2026, 12-Sep-2026, and September 12, 2026. These all are identical dates.

• Fix: Select cell(s), press Ctrl + 1, open Format Cells, choose Date or Time, pick a built-in style or build a Custom one, then click OK
• Watch out: If Excel doesn't recognize your entry as a real date, it gets stored as text. Sorting and date math will misbehave when this happens.

3. Text & Font Formatting

This is purely cosmetic: font, size, color, bold or italic. It doesn't touch the underlying data.

• Where: Home tab, Font section
• Use for: readability, emphasis, visual hierarchy

4. Cell Formatting

Covers the container around the data rather than the data itself:

• Background color or fill
• Borders
• Alignment (left, center, right)
• Common use: color-coding headers, boxing in totals

5. Conditional Formatting

This tells Excel: if a cell meets a certain condition, format it a certain way, automatically.

• Examples:
  • Negative numbers turn red
  • Overdue dates get highlighted yellow
• How: Home tab, Conditional Formatting, pick a rule type, set your condition, choose a format
• Best for: large datasets where manually checking every row isn't practical

6. Table Formatting

Converts a plain range into a structured Excel Table, which is more than just a style upgrade.

• How: Select your data, go to Insert, click Table (or press Ctrl + T), confirm your range, then OK

Also read: Mastering Excel: A Comprehensive Guide to Boost Your Spreadsheet Skills

Data Formatting in Excel Formulas

Manual cell formatting works fine for static data. But once you're building dynamic reports or summary lines that pull from other cells, you need a formula to control the output, not just a formatting menu. That's what the TEXT function and its related tools are for.

1. The TEXT Function

TEXT() converts a number or date into a formatted string, right inside a formula.

=TEXT(value, format_code)

That last example is worth remembering. Zero-padding an ID or invoice number takes one formula instead of manual formatting.

2. Text Output Can't Be Calculated

Once a value passes through TEXT(), Excel treats it as text, not a number. It can't be summed, averaged, or used in further math.

Standard practice to avoid this:

• Keep raw numbers untouched in one column
• Do all calculations on those raw values
• Use a separate column or cell with TEXT() only for what gets displayed

One layer for Excel to calculate with, one layer for people to read.

3. Other Formatting Formulas

• ROUND(number, digits): rounds to a fixed number of decimals, changing the stored value itself
• TRUNC(number, digits): cuts off decimals without rounding (4.99 becomes 4, not 5)
• DAY() / MONTH() / YEAR(): extracts a single part of a date
• & operator or CONCAT(): joins TEXT() output with labels or other text

4. Cell Formatting vs. Formula Formatting

Use cell formatting when the question is how a cell should look.

Use TEXT() inside a formula when the question is how to build a label or sentence from values that change.

Most spreadsheets need both.

Must read: Top 60+ Advanced Excel Formulas List with Examples (2026)

Data Formatting in Excel Shortcuts

If you format data often, learning a few keyboard shortcuts can save you a good amount of time instead of clicking through menus every time.

1. Common Number Formatting Shortcuts

These work on whatever cell or range you've selected, and apply the format instantly:

2. Shortcut to Open Format Cells

Instead of right-clicking every time, just select your cells and press Ctrl + 1 (Cmd + 1 on Mac). This opens the Format Cells window directly, where you can pick number formats, date styles, borders, alignment, and more, all in one place.

3. Shortcut for Applying Date and Time Formats

Ctrl + Shift + 3 applies a quick date format to selected cells. There's no default shortcut for time in most Excel versions, so you'd typically go through Ctrl + 1, select Time, and pick the format you want. If you use a specific time format often, it's worth setting it up once and reusing that cell as a template.

4. Shortcut for Applying Borders and Other Cell Formats

To quickly add a border around selected cells, press Ctrl + Shift + 7. This applies an outline border to your selection. For more specific border styles, like only bottom borders or double lines, you'll still need to go through Format Cells or the Borders drop-down in the Home tab.

Other useful ones while formatting cells:

• Ctrl + B for bold
• Ctrl + I for italic
• Ctrl + U for underline

From your first line of Python to your first autonomous AI agent. This Professional Certificate Programme in Data Science & Agentic AI covers Python, SQL, BI, machine learning, and RAG. One programme, a complete skill set. Enroll now. 

Common Mistakes in Data Formatting in Excel

Even regular Excel users run into these. Most take seconds to fix once you spot the cause.

1. Numbers Stored as Text

Common after pasting data from a website or another file. The number looks normal, but Excel reads it as text, so SUM() skips it or throws an error. Look for a small green triangle in the corner of the cell; that's the warning sign.

Fix it two ways:

• Click the warning icon on the cell and choose "Convert to Number"
• Copy a cell containing just the number 1, select your data, then use Paste Special > Multiply

3. Too Many Colors and Borders

Color-coding everything backfires. It makes a sheet harder to scan, not easier.

Stick to two or three colors max, each with a clear purpose:

• One color for totals
• One color for cells needing attention

4. Inconsistent Decimal Places

A column mixing 0, 2, and 4 decimal places looks unprofessional, especially in financial sheets.

Fix: Select the entire column or range at once and apply one decimal setting, rather than formatting cells individually as you go.

5. Merging Cells Inside a Dataset

Merging looks fine for a header, but merging cells within your actual data causes real problems:

Better option: Use Center Across Selection (under Format Cells) instead of merging, when you want that centered look.

Also read: Supercharge Your Analysis with Statistical Functions in Microsoft Excel!

Conclusion

Data formatting in excel is not just about making a sheet look nice. It changes how easily you and others can read, trust, and use the data. A well formatted sheet catches errors faster, works better with formulas, and just looks more professional when you share it with someone else. 

Once you get comfortable with the basics, like number formats, date formats, and conditional formatting, it becomes second nature. Try applying a few of these tips to your next sheet, and you'll notice the difference right away. 

Have any questions about this topic? Book a free consultation call with our experts and get personalized guidance on the right learning path for you.