Custom Formatting

Are You Struggling to Format Your Data Creatively in Excel?

Here’s a Step-by-Step Guide on Custom Formatting to Make Your Excel Sheets Stand Out!

In this guide, you’ll learn step-by-step tutorials to master Custom Formatting in Microsoft Excel. Whether you’re a beginner exploring the basics or a professional looking to create visually appealing and professional sheets, this tutorial is tailored to meet your needs.

To read our tutorials, like custom formatting in excel, do not forget to subscribe to our official Youtube Channel or Subscribe here: https://www.youtube.com/@hirdeshbhardwaj

Download the free workbook 👉 HERE and follow along.

Mastering Custom Formatting in Excel: A Complete Guide

Excel is a powerful tool, and one of its most underrated features is Custom Formatting. This feature allows you to change how your data looks without altering the actual data itself. Whether you’re preparing a professional report, presenting data to a client, or organizing your personal finances, custom formatting can transform plain data into something clear, elegant, and easy to understand.

In this comprehensive guide, we’ll take you through everything you need to know about custom formatting, from the basics to advanced techniques.

To join our Data Analytics using Excel VBA SQL and Power BI course, visit us at:

https://exceltraininggurgaon.in

What is Custom Formatting in Excel?

Custom formatting refers to the ability to create unique display formats for your data using format codes. These codes define how Excel interprets and presents numbers, text, dates, and more. For instance, you can:

• Add thousands separators to large numbers.
• Display negative values in red or parentheses.
• Customize dates and times to match regional formats.
• Append text or symbols to values for clarity (e.g., “Units: 10”).

What makes custom formatting so powerful is that it doesn’t change the underlying data. It simply modifies how the data appears to you or others.

How to Access Custom Formatting in Excel

Before we dive into examples, let’s first locate the custom formatting option in Excel:

1. Select the Cell(s): Choose the range of cells you want to format.
2. Open the Format Cells Dialog Box:
   • Right-click on the selected cells and click Format Cells from the context menu.
   • Or, use the keyboard shortcut Ctrl + 1 (Windows) or Cmd + 1 (Mac).
3. Navigate to the Custom Format Option:
   • In the Format Cells dialog box, go to the Number tab.
   • Select Custom from the list of categories.

This opens the Type field where you can input or modify custom format codes.

Decoding Custom Format Codes

Custom format codes in Excel consist of up to four sections, separated by semicolons (;):

1. Positive numbers format.
2. Negative numbers format.
3. Zero values format.
4. Text format.

For example:
#,##0.00;[Red]-#,##0.00;"Zero";@

• Positive numbers: Displayed with two decimal places and a thousands separator.
• Negative numbers: Displayed in red with a negative sign.
• Zero values: Displayed as the word “Zero.”
• Text values: Displayed as-is (@ represents text).

If fewer than four sections are defined, Excel uses the first format for missing sections.

Basic Custom Formatting Examples

1. Formatting Numbers

• Add Thousands Separator:
  Format Code: #,##0
  Example: 12345 → 12,345
• Display with Two Decimal Places:
  Format Code: #,##0.00
  Example: 12345 → 12,345.00
• Display Currency:
  Format Code: $#,##0.00
  Example: 12345 → $12,345.00

2. Formatting Percentages

• Convert to Percentage with Two Decimal Places:
  Format Code: 0.00%
  Example: 0.852 → 85.20%

3. Conditional Formatting with Colors

• Use Colors for Positive and Negative Numbers:
  Format Code: [Green]#,##0.00;[Red]-#,##0.00;[Blue]Zero
  Positive → Green, Negative → Red, Zero → Blue.

Custom Formatting for Dates and Times

Excel allows you to customize how dates and times are displayed using specific codes. These codes can be combined for greater flexibility.

Date Formatting Examples

• Day/Month/Year (Short Format):
  Format Code: DD/MM/YYYY
  Example: 22/01/2025
• Full Month Name, Day, and Year:
  Format Code: MMMM DD, YYYY
  Example: January 22, 2025
• Day of the Week, Month, and Year:
  Format Code: DDD, MM/DD/YY
  Example: Tue, 01/22/25

Time Formatting Examples

• 12-Hour Clock with AM/PM:
  Format Code: hh:mm AM/PM
  Example: 3:45 PM
• 24-Hour Clock with Seconds:
  Format Code: hh:mm:ss
  Example: 15:45:30

Advanced Custom Formatting Techniques

1. Append Text or Symbols

• Add Units to Numbers:
  Format Code: 0" Units"
  Example: 10 → 10 Units
• Display Measurements (e.g., kg):
  Format Code: 0" kg"
  Example: 45 → 45 kg

2. Add Leading Zeros

• Ensure a Fixed Number of Digits:
  Format Code:0000
  Example: 12 → 0012

3. Mask Sensitive Data (e.g., Phone Numbers)

• Phone Number Format:
  Format Code:(###) ###-####
  Example: 1234567890 → (123) 456-7890

4. Use Fractions

• Display as Fractions:
  Format Code:# ?/?
  Example: 0.75 → 3/4

Tips for Creating Effective Custom Formats

1. Preview Before Applying: Use the Preview Box in the Format Cells dialog to ensure your formatting looks correct.
2. Test with Sample Data: Experiment with different formats to see how they behave with varying data types.
3. Use Descriptive Colors: Add[Red],[Blue], or [Green] to make specific values stand out.
4. Keep a Reference List: For complex codes, maintain a list for future reference.
5. Layer Formatting: Combine multiple formatting techniques for advanced results.

Why Use Custom Formatting in Excel?

Custom formatting saves time and improves the presentation of your data. By using it effectively, you can:

• Enhance data readability.
• Highlight important values automatically.
• Create consistent formatting across your workbooks.

Conclusion

Custom formatting is a skill every Excel user should master. It provides endless possibilities to present data in a professional, organized, and visually appealing way. With the steps and examples outlined in this guide, you’re now equipped to explore and experiment with custom formats. Start creating your own formats today and see the difference it makes in your data presentations!

Download the exercise file from here 👇