Why formatting improves readability in Excel - Business Case Study

Scenario Mode

👤 Your Role: You are an office assistant responsible for preparing reports.

📋 Request: Your manager wants a clear and easy-to-read sales report to share with the team.

📊 Data: You have a table with sales data including Product, Region, Units Sold, and Revenue.

🎯 Deliverable: Create a formatted sales report that highlights key data and improves readability.

Sample Data

Product	Region	Units Sold	Revenue
Notebook	East	120	2400
Pen	West	200	1000
Binder	East	150	2250
Pen	North	180	900
Notebook	South	100	2000
Binder	West	130	1950
Pen	South	170	850
Notebook	North	110	2200

1

Step 1: Select the header row (Product, Region, Units Sold, Revenue) and make the text bold.

Use Excel ribbon: Home > Font > Bold (or press Ctrl+B).

Expected Result

Headers appear bold, making them stand out from data rows.

2

Step 2: Apply a background color to the header row to separate it visually from the data.

Select header row, then Home > Fill Color > Choose a light color (e.g., light gray).

Expected Result

Header row has a light background color improving visibility.

3

Step 3: Format the Revenue column as currency to show dollar signs and two decimals.

Select Revenue cells, then Home > Number Format > Currency.

Expected Result

Revenue values show as $2,400.00, $1,000.00, etc., making money amounts clear.

4

Step 4: Adjust column widths to fit the content so no data is cut off.

Double-click the right edge of each column header to auto-fit.

Expected Result

All data is fully visible without extra space.

5

Step 5: Add borders around all cells to separate data clearly.

Select entire table, then Home > Borders > All Borders.

Expected Result

Each cell is outlined, making the table easier to read.

6

Step 6: Apply conditional formatting to Units Sold column to highlight values above 150.

Select Units Sold cells, Home > Conditional Formatting > Highlight Cell Rules > Greater Than > Enter 150 > Choose light green fill.

Expected Result

Units Sold values above 150 are highlighted in light green, drawing attention to high sales.

Final Result

-------------------------------------------------
| Product  | Region | Units Sold |   Revenue    |
-------------------------------------------------
| Notebook | East   |    120    |  $2,400.00   |
| Pen      | West   |   200*    |  $1,000.00   |
| Binder   | East   |   150     |  $2,250.00   |
| Pen      | North  |   180*    |   $900.00    |
| Notebook | South  |   100     |  $2,000.00   |
| Binder   | West   |   130     |  $1,950.00   |
| Pen      | South  |   170*    |   $850.00    |
| Notebook | North  |   110     |  $2,200.00   |
-------------------------------------------------
* Units Sold highlighted for values > 150

✓Bold headers and background color make the table easy to scan.

✓Currency format clarifies money amounts in Revenue.

✓Borders separate data clearly for better reading.

✓Conditional formatting highlights high sales in Units Sold.

Bonus Challenge

Create a total row at the bottom that sums Units Sold and Revenue with bold formatting.

Show Hint

Use SUM formula for Units Sold and Revenue columns, then apply bold font to the total row.

Practice

1. Why is formatting important in a spreadsheet?
Formatting means changing how data looks without changing the data itself.

easy

A. It makes the spreadsheet run faster.

B. It helps you see important data quickly and clearly.

C. It deletes unnecessary data automatically.

D. It changes the actual numbers in the cells.

Solution

1. Step 1: Understand what formatting does

   Formatting changes only the appearance of data, not the data itself.

2. Step 2: Identify the benefit of formatting

   Formatting helps highlight or organize data so you can find important information faster.

3. Final Answer:

   It helps you see important data quickly and clearly. -> Option B

4. Quick Check:

   Formatting improves clarity = D [OK]

Hint: Formatting changes look, not data, to highlight info [OK]

Common Mistakes:

• Thinking formatting changes data values
• Believing formatting deletes data
• Assuming formatting speeds up calculations

2. Which of these is the correct way to make text bold in Excel?

easy

A. Select the cell and press Ctrl + B

B. Type =BOLD(A1) in a cell

C. Right-click and choose 'Delete'

D. Change the cell color to red

Solution

1. Step 1: Recall Excel shortcuts for formatting

   Ctrl + B is the standard shortcut to make selected text bold.

2. Step 2: Check other options for correctness

   =BOLD(A1) is not a valid formula; deleting removes data; changing color does not bold text.

3. Final Answer:

   Select the cell and press Ctrl + B -> Option A

4. Quick Check:

   Ctrl + B = Bold text [OK]

Hint: Use Ctrl + B to quickly bold selected cells [OK]

Common Mistakes:

• Trying to use a formula to bold text
• Confusing deleting with formatting
• Changing color instead of font style

3. Look at this data with and without formatting:

Without formatting:
A1: 1000
A2: 2000
A3: 3000

With formatting (Number format: Currency):
A1: $1,000.00
A2: $2,000.00
A3: $3,000.00

What is the main benefit of applying the currency format here?

medium

A. It adds extra calculations to the cells.

B. It changes the actual values to dollars.

C. It makes the numbers easier to read and understand as money.

D. It deletes the decimal places permanently.

Solution

1. Step 1: Understand what number formatting does

   Number formatting changes how numbers look, like adding dollar signs and commas, but does not change the value.

2. Step 2: Identify the benefit of currency format

   Currency format helps users quickly see that numbers represent money, improving understanding.

3. Final Answer:

   It makes the numbers easier to read and understand as money. -> Option C

4. Quick Check:

   Currency format improves readability = A [OK]

Hint: Currency format shows money clearly without changing values [OK]

Common Mistakes:

• Thinking formatting changes the actual number
• Believing formatting adds calculations
• Assuming decimals are removed permanently

4. You want to highlight cells with values above 100 using conditional formatting, but it doesn't work. What could be the problem?

medium

A. You applied conditional formatting to text cells instead of numbers.

B. You typed the formula =A1>100 in the conditional formatting rule.

C. You used bold font instead of color fill.

D. You saved the file before applying formatting.

Solution

1. Step 1: Check the data type of cells

   Conditional formatting with >100 works only on numeric cells, not text.

2. Step 2: Understand why formatting fails

   If cells contain text, the condition >100 is ignored, so no highlight appears.

3. Final Answer:

   You applied conditional formatting to text cells instead of numbers. -> Option A

4. Quick Check:

   Conditional formatting needs numbers = B [OK]

Hint: Ensure cells are numbers, not text, for conditional formatting [OK]

Common Mistakes:

• Using wrong formula syntax
• Confusing font style with conditional formatting
• Thinking saving affects formatting

5. You have a sales report with thousands of rows. You want to make it easier to find the top 10 sales quickly. Which formatting method should you use?

hard

A. Change all font colors to blue.

B. Manually bold the top 10 sales values.

C. Delete rows with sales below average.

D. Apply conditional formatting with a color scale to highlight higher sales.

Solution

1. Step 1: Identify the best formatting for large data

   Conditional formatting with color scales automatically highlights values based on size, helping spot top sales easily.

2. Step 2: Compare other options

   Manually bolding is slow and error-prone; changing all font colors doesn't highlight top values; deleting data loses information.

3. Final Answer:

   Apply conditional formatting with a color scale to highlight higher sales. -> Option D

4. Quick Check:

   Color scale highlights top values = C [OK]

Hint: Use color scales to spot top numbers fast [OK]

Common Mistakes:

• Trying to format manually for large data
• Changing all colors without focus
• Deleting data instead of highlighting