Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 2

Formatting Cells for Readability

Recommended resources

Hello! Welcome to your second lesson.

In our first session, you mastered the keyboard shortcuts for navigating Excel efficiently. That speed is the foundation. Now, we'll focus on the presentation of your data. An MIS Executive's report must be clear and instantly understandable, and that comes down to effective formatting.

Today's learning outcome is to apply cell formatting (number formats, borders, colors) to improve data readability. We'll move beyond the default Excel look to create professional, clean, and easy-to-interpret reports, similar to what you might build for tracking recruitment pipelines or cybersecurity incidents.

Excel Recruitment Tracker with Enhanced Formatting
A well-formatted recruitment tracker. Notice the clear headers, use of color to separate sections, and clean alignment. This is the level of professionalism we are aiming for.

1. Building a Clean Visual Structure

Before we even touch the numbers, let's make the overall sheet easier to read. A cluttered, messy spreadsheet is difficult to trust. We'll focus on creating a clean layout using consistent sizing, simple colors, and strategic borders.

The following video provides some excellent, easy-to-implement tips for creating a "beautiful" spreadsheet.

14 Excel Formatting Tips (Make Excel BEAUTIFUL!)

This video, '14 Excel Formatting Tips' by Tiger Spreadsheet Solutions, demonstrates how to transform a standard spreadsheet into a professional-looking report. We'll focus on the core principles of structure and aesthetics.

Please watch the following segments: Consistent Sizing and Fonts (02:13 - 04:39): Pay attention to setting consistent column widths and the impact of changing the font. Simple Color Scheme (07:45 - 09:30): Note the strategy of using one main color with different shades for headers and data. Clean Borders and Gridlines (11:36 - 12:52): Observe how using only horizontal borders and removing gridlines creates a much cleaner look. Line Styles for Emphasis (12:52 - 13:40): See how a slightly heavier border under the header row adds professional structure.

Beyond those video tips, here are two non-negotiable rules for professional report formatting. While they may seem small, they separate amateur work from expert-level work.

7 Pro Tips for Cleaner Excel Reports

The article '7 Pro Tips for Cleaner Excel Reports' from Excel Campus explains two critical formatting habits. We'll focus on how to handle headers correctly.

Read the following sections: #2 Header Wrapping: Focus on the 'Using Wrap Text' part, specifically the instruction to use Alt + Enter for manual line breaks. #4 Centering Headers: Read this section carefully to understand why 'Center Across Selection' is superior to the common 'Merge & Center' button. This is crucial because 'Merge & Center' can break sorting, filtering, and other data tools you'll rely on later.

Key Structural Formatting Habits:

  • Font and Size: Choose a single, clean font like Arial and use it consistently.
  • Headers: Make headers stand out with a bold font, a dark fill color, and light-colored text.
  • Borders: Avoid the "All Borders" button. Use horizontal borders only for the data body and a thicker bottom border for the header row.
  • Gridlines: Turn them off for a clean, report-like finish (View > Gridlines).
  • Titles: Never use "Merge & Center." Always use Center Across Selection (accessible from the Format Cells dialog: Ctrl + 1 > Alignment > Horizontal).

2. Formatting Numbers for Clarity and Calculation

How you format numbers is critical. A number can represent money, a date, a percentage, or a count. Displaying it correctly prevents confusion and ensures your data can still be used in calculations.

Standard Number Formats

Excel has built-in formats for most common data types.

How To Format Your Excel Spreadsheet: 10 Tips for Finance ...

This article, 'How To Format Your Excel Spreadsheet' from Vena Solutions, gives a great overview of the standard number formats and their use cases.

Read section 1. Format Numbers Based on Data Type. Pay close attention to the table that lists different number formats (Number, Currency, Date, Percentage, etc.) and their use cases. Also, read the comparison of Currency vs. Accounting formats in the '7 Pro Tips' article (resource_id LINK, section '1. Currency vs. Accounting Number Format'). The Accounting format's alignment is generally preferred for reports.

The key difference for MIS reports is Currency vs. Accounting format.

  • Currency: $ symbol is next to the number ($5.00).
  • Accounting: $ symbol is aligned to the left edge of the cell, and numbers are aligned to the right. This makes columns of figures much easier to read. Zeros are shown as a dash -. For reports, always prefer the Accounting format.
Excel Incident Management Template with Severity Scale
In this incident management template, color and text are used to define severity. Similarly, number formats define the meaning of your data (e.g., \$, %, date).

Custom Number Formats

Sometimes, the standard formats aren't enough. You might want to display "5 days" or "100 units" in a cell, but still be able to use the number 5 or 100 in a formula. This is where Custom Number Formats are essential.

This next video is a fantastic guide to this powerful feature.

Why you SHOULD be USING Custom Number Formatting in Excel

Leila Gharani's video, 'Why you SHOULD be USING Custom Number Formatting in Excel,' explains the logic behind custom formats. This is a skill that will significantly elevate the quality of your MIS reports.

Watch the following clips carefully: The Rule (02:22 - 03:25): This explains the fundamental structure: POSITIVE;NEGATIVE;ZERO;TEXT. 0 vs. # (03:35 - 05:33): Understand the difference between the fixed placeholder (0) and the variable placeholder (#). This is key for things like fixed-length IDs. Adding Text (05:33 - 07:54): See how to add descriptive text (like 'returns' or 'total') to a number without converting the cell to text, keeping it ready for calculations.

Key Custom Format Codes:

  • 0: A digit placeholder that shows insignificant zeros. Use it for fixed lengths (e.g., 0000 for ID 0012).
  • #: A digit placeholder that does not show insignificant zeros.
  • "text": Displays the text inside the quotes.
  • ,: A thousands separator. Placing a comma at the end of the format code divides the number by 1000 (e.g., #,K would show 12,000 as "12K").

Hands-On Practice

Let's apply these techniques to a small incident report dataset.

  1. Open a new Excel sheet.
  2. Copy and paste the data below starting in cell B2.
INCIDENT RESPONSE TIME REPORT
ID	Date	Category	Owner	Resolution Time (Hrs)	Cost
INC001	45168	Data Leak	Ana	4	5250
INC002	45169	Phishing	Carlos	2	0
INC003	45169	Malware	Priya	24	15000
INC004	45170	Phishing	Ana	8	1200
INC005	45171	Data Leak	Priya	0	0

(Note: Excel stores dates as numbers; 45168 is the number for October 1, 2023. We will fix this with formatting.)

Your Task (use keyboard shortcuts where possible!):

  1. Title: Select the range B2:G2. Open the Format Cells dialog (Ctrl + 1), go to the Alignment tab, and set the Horizontal dropdown to Center Across Selection. Make the title font size 14 and bold.
  2. Headers: Select the header row (B3:G3).
    • Apply a dark blue fill color and a white, bold font.
    • Set the vertical and horizontal alignment to Center.
  3. Dates: Select the date column data (C4:C8). Apply the Short Date format.
  4. Resolution Time: You want this to read "X Hrs" but remain a number.
    • Select the data (F4:F8). Open Format Cells (Ctrl + 1).
    • Go to Custom. In the "Type" box, enter 0" Hrs"
    • Notice how "0 Hrs" is displayed in the cells F6 and F8, but the formula bar still shows the number 0.
  5. Cost: This is a financial value. Select the cost data (G4:G8) and apply the Accounting number format. Notice the alignment of the $ and the dash for the zero values.
  6. Borders & Gridlines:
    • Select the entire table (B3:G8). Apply a Thick Bottom Border to the header row only.
    • Select the data rows (B4:G8). Apply a standard Bottom Border to all of them.
    • Finally, go to the View tab and uncheck Gridlines.

Your report should now look clean, professional, and be much easier to read.

Summary of Key Takeaways

  • Structure First: A clean layout (consistent fonts, simple colors, good alignment) is the foundation of a readable report.
  • Professional Habits: Always use Center Across Selection instead of Merge & Center, and prefer the Accounting format for financial data.
  • Format for Meaning: Use Number Formatting to make it clear if a value is a date, currency, or percentage.
  • Custom Formats are a Superpower: They allow you to add text and units to numbers (" Hrs", " K") while keeping them as actual numbers for formulas and charts.

Next Lesson

You've now learned to navigate efficiently and format your data professionally. In the next lesson, we'll dive into tools that help you manage large datasets: Use Find & Replace and Go To Special to quickly locate and modify data. These are essential skills for the data cleaning and preparation phase of any MIS task.