Formatting Basics in Excel

BM101.03

⌨️ Keyboard Shortcuts:

  • Ctrl + 1: Format Cells.
  • Ctrl + B/I/U: Bold, Italic, Underline.

⏰ Estimated Learning Time: 40 Minutes

Objective

  • Learn how to format cells to enhance readability and professionalism.
  • Understand how to apply text formatting, number formatting, and conditional formatting.
  • Practice using alignment, borders, and styles effectively.

Fun Fact 🧮

  • Did you know that the first Excel version capable of conditional formatting was released in 1997? It helps users visualize data patterns and trends with just a few clicks!

Key Concepts

  • Text Formatting:
    • Change font style, size, and color.
    • Use Ctrl + B/I/U for bold, italic, and underline respectively.
  • Number Formatting:
    • Adjust numbers to display as currency, percentages, or dates.
    • Example: Use Ctrl + Shift + $ for currency, Ctrl + Shift + % for percentages.
  • Alignment and Wrapping:
    • Align text horizontally and vertically within cells.
    • Use Wrap Text to ensure all content is visible without resizing the cell.
  • Borders and Shading:
    • Add borders to define table structures.
    • Use fill colors to highlight important cells or sections.
  • Conditional Formatting:
    • Highlight cells automatically based on their values (e.g., sales > $1,000).
    • Example: Use a color scale to visually represent data trends.

Lesson Steps

  • Open a Blank Workbook:
    • Create a new workbook and enter a small dataset.
  • Apply Text Formatting:
    • Select the header row and make the text bold.
    • Change the font size of the header row to 14 and apply a background color.
  • Format Numbers:
    • Highlight the cells containing numeric data and format them as currency.
    • Use percentage formatting for data like growth rates.
  • Add Borders:
    • Select the entire table and apply borders to all cells.
  • Use Conditional Formatting:
    • Highlight cells with values greater than $1,000 using Conditional Formatting.
    • Add a color scale to visualize the range of values.

Common Errors

  • Text Not Wrapping Properly: Ensure Wrap Text is enabled.
  • Conditional Formatting Not Applying: Double-check the conditions and ensure the range is correctly selected.
  • Borders Missing: Verify that you have selected the correct range before applying borders.

Exercise

Scenario: You are preparing a monthly budget sheet. Perform the following steps:

Enter the following data into a worksheet:

Category Amount
Rent 1200
Groceries 300
Utilities 150
Transportation 100
Entertainment 200
  • Apply Formatting:
    • Bold the headers and increase their font size to 14.
    • Format the "Amount" column as currency.
    • Add a border around the entire table.
  • Use Conditional Formatting:
    • Highlight amounts greater than $500 with a light red fill.
    • Apply a green color scale to the "Amount" column.

    Quiz

    Question 1: Which shortcut opens the Format Cells menu?




    Question 2: What does Conditional Formatting allow you to do?




    Question 3: How can you apply currency formatting quickly?




    Solutions

    • Question 1: The shortcut to open the Format Cells menu is Ctrl + 1.
    • Question 2: Conditional Formatting allows you to automatically format cells based on their values.
    • Question 3: The shortcut to apply currency formatting is Ctrl + Shift + $.
    Video Block
    Double-click here to add a video by URL or embed code. Learn more