Types of Formatting in Spreadsheets: A Complete Guide

Why Formatting Matters More Than You Think

A spreadsheet full of raw, unformatted data is like a book with no punctuation, no paragraphs, and no chapters. The information might all be there, but good luck making sense of it.

Formatting does three things. First, it makes data readable. A column of numbers like 1234567.89 means something very different when formatted as $1,234,567.89 versus 1,234,567.89 kg. Second, it prevents errors. When dates look like dates and phone numbers look like phone numbers, people are less likely to misinterpret or misenter data. Third, it communicates meaning. Conditional formatting that highlights overdue items in red tells a story that raw data simply doesn't.

This guide covers every major type of formatting you can apply in spreadsheets, with practical examples focused on Google Sheets. We'll also look at how AI can help automate formatting decisions when you're dealing with messy or inconsistent data.

Number Formatting

Number formatting is the most common type and probably the one that causes the most confusion. The same underlying value can look completely different depending on the format applied.

General Number Formats

Decimal Places

You control how many decimal places to show. Financial data typically uses 2 decimal places. Scientific data might need 4 or more. Whole number counts (like item quantities) should show 0 decimal places.

In Google Sheets, you can adjust this through Format > Number > Custom number format or by using the increase/decrease decimal buttons in the toolbar.

Negative Number Formatting

There are several conventions for displaying negative numbers:

• Minus sign: -1,234.50 (most common)
• Parentheses: (1,234.50) (common in accounting)
• Red text: 1,234.50 in red (visual emphasis)
• Red with parentheses: (1,234.50) in red (accounting with visual emphasis)

In Google Sheets custom number format: #,##0.00;(#,##0.00) shows positive numbers normally and negatives in parentheses.

Leading Zeros

Phone numbers, ZIP codes, and ID numbers often need leading zeros. If you enter 01234 in a number-formatted cell, Google Sheets strips it to 1234. To preserve leading zeros:

• Format the column as Plain text before entering data
• Use a custom format like 00000 for 5-digit codes
• Prefix with an apostrophe: '01234 (forces text treatment)

This is one of the most common formatting mistakes in spreadsheets, and it can cause real problems when dealing with ZIP codes or part numbers.

Text Formatting

Font and Style

The basics that everyone knows:

• Bold for headers and emphasis
• Italic for secondary emphasis or notes
• Strikethrough for completed or deprecated items
• Underline for headers (though this is less common in spreadsheets than in documents)

Font Size

A good rule of thumb for spreadsheets:

• Headers: 12-14pt
• Body data: 10-11pt
• Notes or secondary text: 8-9pt

Going bigger than 14pt for headers usually wastes space. Going smaller than 8pt makes data hard to read on screen and impossible to read when printed.

Text Alignment

Google Sheets usually auto-aligns text left and numbers right, which is the correct default. Override this only when you have a specific reason.

Text Wrapping

Long text in a cell can either:

• Overflow into adjacent empty cells (default)
• Wrap within the cell, increasing row height
• Clip at the cell boundary, hiding overflow text

For data entry sheets, wrapping is usually best because it shows all content without requiring manual column resizing. For dashboard-style sheets, clipping keeps rows uniform.

Set this in Google Sheets via Format > Wrapping.

Text Rotation

You can rotate text in header cells to save horizontal space. This is particularly useful when you have many narrow columns with long header names. In Google Sheets, use Format > Rotation to set the angle.

Date and Time Formatting

Dates are a constant source of formatting headaches, partly because different regions use different conventions.

Common Date Formats

Time Formats

Date-Time Combined

For timestamps, the ISO format YYYY-MM-DD HH:MM:SS is the clearest and least ambiguous. In Google Sheets, you can set this with the custom format: yyyy-mm-dd hh:mm:ss.

Duration Formatting

Elapsed time (like "3 hours 45 minutes") needs different formatting than clock time. Google Sheets has a built-in Duration format, or you can use custom formats like [h]:mm:ss where the brackets allow hours to exceed 24 (useful for project tracking).

The Date Ambiguity Problem

Is 03/04/2026 March 4th or April 3rd? Depends on where you are. This ambiguity has caused real business problems. Best practice: use YYYY-MM-DD format or spell out the month name (Apr 3, 2026) to eliminate confusion, especially in shared sheets with international collaborators.

Conditional Formatting

Conditional formatting changes a cell's appearance based on its value. It's one of the most powerful formatting features in any spreadsheet tool because it turns data into visual information.

Color Scales

Apply a gradient from one color to another based on cell values. Common uses:

• Revenue numbers: Green (high) to red (low)
• Performance scores: Red (poor) to green (excellent)
• Temperature data: Blue (cold) to red (hot)

In Google Sheets: Format > Conditional formatting > Color scale

Single Condition Rules

Highlight cells that meet a specific condition:

• Cells greater than a threshold (e.g., overdue amounts > $10,000 in red)
• Cells containing specific text (e.g., "Urgent" highlighted in yellow)
• Empty cells (e.g., required fields that haven't been filled)
• Duplicate values (e.g., repeated email addresses highlighted)

Multi-Condition Rules

You can stack multiple conditional formatting rules on the same range. Google Sheets applies them in order, with the first matching rule taking priority.

Example for a project tracker:

1. If Status = "Overdue" -> Red background
2. If Status = "At Risk" -> Yellow background
3. If Status = "On Track" -> Green background
4. If Status = "Complete" -> Gray background, strikethrough text

Icon Sets (Limited in Google Sheets)

Excel has built-in icon sets (arrows, traffic lights, stars) for conditional formatting. Google Sheets doesn't support this natively, but you can simulate it with Unicode characters in helper columns:

=IF(A2>80, "🟢", IF(A2>50, "🟡", "🔴"))

Data Bars (Limited in Google Sheets)

Excel's data bars show a colored bar within each cell proportional to the value. Google Sheets has a SPARKLINE function that can simulate this:

=SPARKLINE(A2, {"charttype","bar"; "max",100; "color1","#4285F4"})

Conditional Formatting Best Practices

• Don't use too many colors. Three to four is usually the maximum before it becomes confusing rather than helpful.
• Be consistent. Red should always mean the same thing across your entire workbook.
• Use color-blind-friendly palettes. Avoid relying solely on red/green distinctions. Add a secondary indicator (bold, borders, icons) for accessibility.
• Document your rules. Add a legend or key so anyone looking at the sheet understands what the colors mean.

Cell Formatting

Borders

Borders define the visual structure of your spreadsheet. Use them to:

• Separate header rows from data
• Create distinct sections
• Outline summary or total rows
• Define input areas vs. calculated areas

Common border patterns:

• Bottom border on header rows
• Top and bottom borders on total/summary rows
• All borders for print-ready tables
• Outside borders only for clean, minimal sections

Avoid the "every cell has all borders" look. It makes sheets look cluttered and harder to scan.

Cell Colors and Backgrounds

Use background colors sparingly and consistently:

Merging Cells

Merging cells combines multiple cells into one. It's useful for section headers that span multiple columns. It's terrible for data cells because merged cells break sorting, filtering, and formulas.

Rule of thumb: Only merge cells in title rows or section headers. Never merge cells in data areas.

Freezing Rows and Columns

Not technically "formatting," but it affects how data is presented:

• Freeze the header row so it stays visible when scrolling down
• Freeze ID or name columns so they stay visible when scrolling right

In Google Sheets: View > Freeze > 1 row (or however many you need)

Custom Number Formats

Google Sheets and Excel both support custom number format strings. These give you complete control over how values display.

Format String Components

Useful Custom Formats

Thousands with "K":

#,##0.0,"K"

Displays 1500000 as 1,500.0K

Millions with "M":

#,##0.0,,"M"

Displays 1500000 as 1.5M

Phone numbers:

(###) ###-####

Displays 5551234567 as (555) 123-4567

Social Security Numbers:

###-##-####

Positive/Negative/Zero:

#,##0.00;[Red](#,##0.00);"-"

Shows negatives in red parentheses and zeros as a dash.

Using AI to Auto-Format Data with SheetAI

Formatting becomes a real challenge when you're working with inconsistent or messy data. Imagine receiving a CSV file where dates are in five different formats, phone numbers use three different conventions, and currency values sometimes include the symbol and sometimes don't.

Standardizing Date Formats

=SHEETAI("Convert this date to YYYY-MM-DD format regardless of the input format. Return only the formatted date. Input: " & A2)

This handles inputs like "March 3, 2026," "3/3/26," "03-Mar-2026," and "2026.03.03" and normalizes them all.

Standardizing Phone Numbers

=SHEETAI("Format this phone number as (XXX) XXX-XXXX for US numbers. If it's an international number, use +XX XXX XXX XXXX format. Return only the formatted number: " & A2)

Cleaning and Formatting Names

=SHEETAI("Format this name in proper case (First Last). Handle edge cases like McDonald, O'Brien, van der Berg correctly. Return only the formatted name: " & A2)

Detecting Appropriate Formats

When you're not sure what format to apply, AI can help:

=SHEETAI("What type of data is this? Respond with one of: currency, percentage, date, phone number, email, plain text, ID number. Data: " & A2)

You can then use this classification to apply the right format programmatically.

Formatting Addresses

=SHEETAI("Standardize this address into a consistent format with proper capitalization and abbreviations (St, Ave, Blvd, etc.): " & A2)

Generating Formatted Summaries

=SHEETAI("Format this raw financial data as a clean one-line summary with proper currency formatting and labels: Revenue " & A2 & " Expenses " & B2 & " Profit " & C2)

Formatting for Different Outputs

Different destinations require different formatting approaches:

For Screen Viewing

• Use conditional formatting liberally
• Freeze headers
• Use alternating row colors for readability
• Keep font size at 10-11pt

For Printing

• Add borders to define the table structure
• Include headers on every printed page (File > Print > Headers & Footers)
• Remove gridlines if using borders (File > Print > Formatting)
• Consider landscape orientation for wide tables

For PDF Export

• Same as printing considerations
• Check page breaks to ensure tables don't split awkwardly
• Include a title and date on the first page

For Presentation (Pasting into Slides)

• Bold headers, clear borders
• Larger font sizes (12-14pt minimum)
• High-contrast colors
• Minimal data, maximum clarity

Getting Started

Good formatting is invisible. When a spreadsheet is formatted well, people just understand the data without thinking about why. When it's formatted poorly, every number requires extra mental effort to interpret.

Start with the basics: proper number formats, consistent date formatting, and a clean header row. Add conditional formatting for data that needs to tell a story. Use custom formats when the built-in options don't fit your needs.

And when you're dealing with messy, inconsistent data that needs cleanup before formatting, let AI handle the standardization so you can focus on the analysis.

Try SheetAI free to bring AI-powered formatting and data cleanup to your Google Sheets.