Data Formatting in Excel

Excel's 2025 Data formatting tools, with dynamic arrays and AI suggestions, transform raw data into clear, professional insights. Master these techniques to save time and enhance spreadsheet readability. Explore essential tools and best practices to make your data stand out.

Types of Data Formatting in Excel

Excel 2025 offers a variety of formatting options to meet diverse needs. Below are the key types, with practical examples and step-by-step instructions.

Number Formatting

Number formatting controls how numerical data appears, such as decimals, percentages, or currencies.

• Examples: Display numbers as currency (e.g., $1,234.56), percentages (e.g., 75.00%), or custom formats like millions (e.g., 1.23 M).
• How to Apply:
  1. Select the cells (e.g., A1:A10).
  2. Go to Home > Number Format dropdown or press Ctrl+1 to open the Format Cells dialog.
  3. Choose a category (e.g., Currency, Percentage) or select Custom for formats like 0.00 "M" for millions.

Text Formatting

Text formatting enhances readability and emphasizes key data.

• Tools: Font style, size, bold, italic, underline, and font color.
• How to Apply:
  1. Select the cells.
  2. Go to Home > Font group or right-click and select Format Cells.
  3. Adjust font (e.g., Calibri), size (e.g., 12), or apply bold/italic via Ctrl+B/Ctrl+I.

Date and Time Formatting

Date and time formatting ensures dates and times align with user preferences or regional standards.

• Examples: Display dates as 15-Mar-2025 or 03/15/25, and times as 2:30 PM or 14:30.
• How to Apply:
  1. Select the cells.
  2. Go to Home > Number Format > Short Date or Long Date, or use Ctrl+1 for custom formats like dd-mmm-yyyy.

Conditional Formatting

Conditional formatting automatically applies formats based on rules, ideal for data analysis.

• Examples: Highlight cells above a threshold, apply color scales, or use icon sets.
• How to Apply:
  1. Select the range (e.g., B2:B100).
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose a rule type (e.g., "Format only cells that contain") or use a formula (e.g., =B2>10000 to highlight sales over $10,000).
  4. Set a format (e.g., green fill) and click OK.

Cell Styles and Themes

Cell styles and themes ensure consistent formatting across workbooks.

• Examples: Apply predefined styles like "Heading 1" or use themes for uniform fonts and colors.
• How to Apply:
  1. Select the cells.
  2. Go to Home > Cell Styles or Page Layout > Themes to select a style or theme.

Custom Formatting

Custom formatting allows unique formats tailored to specific needs.

• Examples: Format negative numbers in red (#,##0;[Red]-#,##0) or phone numbers as (###) ###-####.
• How to Apply:
  1. Select the cells.
  2. Press Ctrl+1, select Custom, and enter the format code.

Borders and Fill

Borders and fill add structure and visual distinction to cells.

• Examples: Use thick borders for tables or fill colors to highlight key data.
• How to Apply:
  1. Select the cells.
  2. Go to Home > Borders or Fill Color in the Font group, or use the dropdown for border styles.

Table Formatting

Formatting data as an Excel Table improves organization and interactivity.

• Benefits: Automatic filtering, sorting, and dynamic formatting that adjusts as data grows.
• How to Apply:
  1. Select the data range.
  2. Go to Insert > Table or press Ctrl+T.
  3. Choose a table style from Table Design > Table Styles.

How to Apply Formatting in Excel?

The following are the steps to apply the formatting:

1. Select the range of cells on which formatting needs to be done.
2. Select the suitable formatting tool from the toolbox(ribbon).
3. Formatting can be observed on the sheet.

Formatting tools that work on the text and cell appearance can be found in Home Tab [ Font Group ] in the ribbon.

Let's discuss some formatting tools in this article. 

• Font: It changes the writing style of the data.

• Font Size: It changes the size of the data, can make it appear big or short.

• Increase/Decrease Font Size: It does the same work as by Font Size i.e. adjusts the size of the font accordingly. However, it gets handy as in only one click, the size is getting changed.

• Bold and Italic: Bold thickens the letter and makes it appear darker than before while Italic, italicize the letters. Both are useful when some data needs to be differentiated from the rest.

• Underline: This feature marks a line at the foot of the data.

There is a drop-down button on the right of the underline tool, which has two options: Underline and Double Underline. Underline is the same as the Underline tool and Double Underline marks two lines below the text selected.

• Top and Double Bottom Border: This feature creates the border as per selection from the drop-down button around the selected cells. This is majorly used to differentiate some cells from the drop-down and create tables in large sheets.

The drop-down in the right of the tool has many options in which we can create the borders around the cells.

• Fill Colour: As the name suggests, it fills the colour in the cell making it distinct from the rest. Excel offers a pool of colours from which the choice can be made.

• Font Colour: It changes the colour of the font and makes it appear better. Correct colour choices can be made from a plethora of options available.

The differences can be observed between using the formatting tools and by default:

Cell B4 is formatted. It has a different font style, size, double underline, Bold and Italic font, different colours, and a thick box border around it whereas cell B7 has default settings.

Best Practices for Effective Formatting

• Accessibility: Choose high-contrast colors (e.g., avoid red-green for colorblind users) and use fonts like Calibri or Arial for readability.
• Consistency: Apply Cell Styles or Themes to maintain uniformity across large workbooks.
• Performance: Limit excessive conditional formatting in large datasets to avoid slowdowns; use formulas sparingly.
• Readability: Use text wrapping for long entries and alternate row colors in tables for clarity.
• Collaboration: In Microsoft 365, use comments with formatted text (e.g., bold or colored) to enhance communication in shared files.

Troubleshooting Common Formatting Issues

• Formatting Not Applying: Check for cell protection (Review > Unprotect Sheet) or merged cells.
• Performance Issues: Reduce conditional formatting rules or apply them to smaller ranges.
• Inconsistent Formatting: When copying data from external sources, use Paste Special > Values and reapply formatting.
• Cloud Sync Errors: Ensure a stable internet connection for real-time formatting updates in Microsoft 365.