Webbo3 Data Analysis Bootcamp · Excel Module · Lesson 3

Excel Formatting: Number Formats, Fonts, Alignment, Borders, Cell Styles, and Conditional Formatting

A hands-on lesson covering how to format cells for clarity and professionalism, from basic number formatting and font styling to custom formats and conditional formatting rules.

Data without formatting is just numbers in a grid. Formatting is what turns raw data into information that people can read, understand, and act upon. A column of dates stored as serial numbers means nothing to a manager. The same column formatted as "14 March 2026" is instantly meaningful. A profit margin of 0.1547 is abstract. Formatted as 15.47% with green font, it communicates success. This lesson covers every formatting tool in Excel that a data analyst needs: number formats, fonts and alignment, borders and fills, cell styles, themes, and conditional formatting. Master these, and every spreadsheet you touch will look professional. Ignore them, and even the most brilliant analysis will be dismissed because it looks amateur.

1. Number Formatting

Excel offers twelve built-in number format categories, and the difference between choosing the right one and the wrong one is the difference between a report that looks polished and one that confuses its readers. You apply number formats by selecting cells and pressing Ctrl + 1 to open the Format Cells dialog, or by using the quick buttons on the Home tab.

General. This is the default. Excel displays numbers exactly as you type them unless the column is too narrow, in which case it may switch to scientific notation. General has no thousand separators, no fixed decimal places, and no currency symbols. It is fine for raw data, but you should almost never leave final reports in General format.

Number. This format lets you specify decimal places and whether to use a thousand separator. Select your cells, press Ctrl + 1, choose Number, and set Decimal places to 2. The number 45000 becomes 45,000.00. You can also choose how negative numbers display: with a minus sign, in red, in parentheses, or in red parentheses. For financial reports, parentheses are the standard because they are easier to spot at a glance than a thin minus sign.

Currency. Currency adds a currency symbol directly beside the number, for example ₦45,000.00. The symbol hugs the number, and negative values can show with a minus sign or in red. Apply it quickly with Ctrl + Shift + $. Currency is ideal for individual values, price lists, and invoices where each cell stands alone.

Accounting. Accounting also displays monetary values, but it aligns currency symbols and decimal points in a vertical column. The symbol sits at the left edge of the cell while the number aligns to the right. Negative numbers show in brackets, and zeros display as dashes. This is the format used in balance sheets, income statements, and any financial table where vertical alignment matters more than compactness. Apply it from the Home tab by clicking the small dollar sign icon in the Number group.

Percentage. The Percentage format multiplies the cell value by 100 and adds a percent sign. A cell containing 0.1547 becomes 15.47%. You control the number of decimal places. This is essential for margins, growth rates, and survey results. The quick shortcut is Ctrl + Shift + %. A common mistake is typing 15 into a cell and then applying Percentage format, which turns it into 1500%. The correct workflow is to type the decimal form 0.15 and then apply Percentage, or type 15% directly and let Excel handle the conversion.

Date and Time. Excel stores dates as serial numbers and times as fractions of a day. The Date format controls how that serial number appears. Choose Short Date for 14/03/2026, Long Date for 14 March 2026, or a custom format like dd-mmm-yyyy for 14-Mar-2026. Time formats display hours, minutes, and seconds. If you subtract one time from another, format the result as a time or as a number with decimal places to see the duration in hours. A critical detail: if two cells both contain dates but one is formatted as text, subtraction will fail. Always verify that dates are stored as numbers by checking their alignment. Real dates align right. Text dates align left.

Fraction and Scientific. Fraction format displays decimals as fractions, useful for measurements in construction or manufacturing. Scientific format uses exponential notation, for example 1.23E+10, which is standard for very large or very small numbers in engineering and scientific work.

Text. The Text format forces Excel to treat the cell content as plain text, even if it looks like a number or date. Use this for phone numbers with leading zeros, postal codes, product SKUs, and any identifier that should never be used in calculations. Once a cell is formatted as Text, formulas will not calculate inside it until you convert the format back to General or Number.

2. Font Formatting

Font choices affect readability and professionalism more than most people realize. The Home tab's Font group gives you complete control.

Font family and size. For data analysis work, stick to clean sans-serif fonts like Calibri, Arial, or Segoe UI. These render clearly at small sizes and remain legible when printed. Use 11pt for body data, 12pt for headers, and 14pt or 16pt for dashboard titles. Avoid decorative fonts like Comic Sans or Papyrus in professional work. They undermine credibility instantly.

Bold, italic, and underline. Use bold for headers, totals, and key metrics that you want the eye to catch first. Use italic sparingly, for annotations or secondary labels. Use underline for hyperlinks or to separate sections, but avoid double underlining except in accounting totals where it signifies a final sum. The shortcuts are Ctrl + B for bold, Ctrl + I for italic, and Ctrl + U for underline.

Font color and fill color. The Font Color button, the letter A with a colored underline, lets you change text color. The Fill Color button, the paint bucket, changes the background of the cell. Use dark text on light backgrounds for body data. Use light text on dark backgrounds only for headers or KPI cards. Never use red and green together without additional cues, because approximately 8 percent of men and 0.5 percent of women have color vision deficiency and cannot distinguish them. If you must use red for negative and green for positive, add a minus sign or an arrow icon as a secondary indicator.

Strikethrough and subscript/superscript. Strikethrough is useful for showing deprecated values or completed tasks without deleting them. Superscript is standard for exponents in formulas, and subscript is used in chemical notation. These live in the Format Cells dialog under the Font tab, or you can add them to your Quick Access Toolbar for faster access.

3. Alignment and Text Control

How text sits inside a cell affects scanability, especially in wide tables where the eye must travel horizontally.

Horizontal alignment. Text aligns left by default. Numbers align right by default. This is not arbitrary. Left-aligned text is easier to read because the eye starts at a consistent position. Right-aligned numbers are easier to compare because their decimal points line up vertically. Do not override these defaults without reason. Center alignment works for headers and short labels, but centering long text in data cells makes scanning difficult. For financial tables where currency symbols must align, use Accounting format rather than centering currency symbols manually.

Vertical alignment. By default, text sits at the bottom of a cell. For rows with varying heights, middle alignment often looks cleaner, especially when combined with wrapped text. Select your range, press Ctrl + 1, go to the Alignment tab, and set Vertical to Middle.

Text wrapping and shrinking. When a cell contains more text than its width allows, you have three options. Wrap Text breaks the text into multiple lines within the same cell, increasing the row height automatically. This is the best choice for notes, addresses, and descriptions. Shrink to Fit reduces the font size until the text fits in the cell width. This is dangerous for printed reports because the text may become illegibly small. Merge Cells combines multiple cells into one wide cell. Avoid merging in data tables because it breaks sorting, filtering, and formula references. Only merge cells in presentation layers like dashboards where the data is static.

Text orientation and indentation. You can rotate text to display headers at an angle, which saves horizontal space in wide tables. The Orientation setting in the Alignment tab lets you rotate text from 90 degrees vertical to any custom angle. Indentation pushes text inward from the cell edge, which is useful for showing hierarchy in outlines or subcategories. Each click of the Increase Indent button adds one level of indentation.

Alt + Enter for line breaks. To insert a manual line break inside a single cell while typing, press Alt + Enter. This is useful for multi-line headers or for separating items in a single cell without merging. Pressing Enter alone moves to the next cell down and ends your entry.

4. Borders and Fill Colors

Borders separate data visually. Fill colors group related data. Both must be used with restraint. A sheet with borders on every cell looks like a prison grid. A sheet with random fill colors looks like a child's drawing. The goal is structure, not decoration.

Border styles and placement. Select a range, press Ctrl + 1, and go to the Border tab. You can apply borders to the bottom, top, left, right, or outline of a selection. For data tables, use thin gray bottom borders between rows instead of full gridlines. This creates clean horizontal separation without the visual weight of a full grid. For totals and subtotals, use a medium-weight top border with a single bottom border to create the accounting double-line effect that signifies a final sum. The Border dropdown on the Home tab offers quick presets: Bottom Border, Top Border, All Borders, and Outside Borders. Learn the shortcuts: Alt + H + B opens the border menu from the keyboard.

Fill colors for grouping. Use light, subtle fills to group related rows or columns. For example, shade every alternate row in a very light gray to improve readability. This is called zebra striping, and Excel can do it automatically through conditional formatting, which you will learn next. For manual grouping, use pale blues, greens, or yellows at low saturation. Never use bright red or neon green as a fill color behind black text. It strains the eye and looks unprofessional. A good rule: if the fill color is darker than 15 percent intensity, it is probably too strong.

Removing formatting. To strip all formatting, borders, fills, and font changes from a selection, go to Home → Editing → Clear → Clear Formats. This leaves the data intact but returns the cells to their default appearance. To remove only borders, select the range and choose No Border from the Border dropdown. To remove only fill color, select No Fill from the Fill Color dropdown.

5. Cell Styles and Themes

Cell Styles and Themes are Excel's built-in systems for applying consistent formatting across an entire workbook. They are underused by beginners and indispensable for professionals.

Cell Styles. Go to Home → Styles → Cell Styles. Excel provides predefined styles for Normal, Bad, Good, Neutral, Heading 1, Heading 2, Title, Total, and more. When you apply a style, you apply a complete package of font, border, fill, and number format in one click. The real power is that styles are linked. If you modify a style's definition, every cell using that style updates automatically. To create your own style, format a cell exactly how you want it, select it, then click Cell Styles → New Cell Style. Give it a name like "KPI Card" or "Table Header." Now you can apply that exact formatting to any cell with one click. If your company brand colors change, modify the style once and every cell updates.

Themes. A theme controls the color palette, font pairing, and effects used across the entire workbook. Go to Page Layout → Themes to choose from Excel's built-in themes. Each theme defines two fonts: one for headings and one for body text, and a set of twelve coordinated colors. When you apply a theme, all styled cells, charts, and shapes update to match the new palette. This is how large organizations maintain brand consistency across dozens of reports. If your company has a brand guideline with specific hex colors, you can create a custom theme by clicking Customize under Colors and entering your exact brand values.

Merging styles from other workbooks. If a colleague has created a brilliant set of cell styles, you can import them into your workbook. Open both workbooks, go to Home → Cell Styles → Merge Styles, and select the source workbook. This copies all custom styles into your current file. It saves you from recreating formatting from scratch.

6. Format Painter

Format Painter is one of Excel's most time-saving tools. It copies every formatting attribute from one cell and applies it to another, or to an entire range, with a single click.

Single use. Select the cell whose formatting you want to copy. Click the Format Painter button on the Home tab, or press Ctrl + Shift + C. The cursor changes to a paintbrush. Click the target cell or drag across a range to apply the formatting. The paintbrush disappears after one use.

Multiple uses. If you need to apply the same formatting to several non-contiguous areas, double-click the Format Painter button instead of single-clicking. The paintbrush stays active until you press Escape or click the Format Painter button again. This is ideal for formatting multiple tables on the same sheet with identical styling.

What gets copied. Format Painter copies number format, font, font size, font color, fill color, borders, and alignment. It does not copy formulas, values, or conditional formatting rules. To copy conditional formatting, use Paste Special → Formats, or copy the cell and use the Paste Options menu to select Formatting.

7. Conditional Formatting

Conditional formatting is the most powerful formatting tool in Excel. It applies formatting automatically based on the cell's value or a formula you define. Unlike static formatting, it updates dynamically when the data changes. A sales figure that drops below target turns red automatically. A project that is overdue gets a warning icon. This is how you build self-updating reports.

Highlight Cell Rules. Go to Home → Conditional Formatting → Highlight Cell Rules. Choose Greater Than, Less Than, Between, Equal To, Text That Contains, A Date Occurring, or Duplicate Values. For example, select a column of sales figures, choose Greater Than, enter 100000, and pick a green fill. Every cell above one hundred thousand naira turns green instantly. If a value later drops below that threshold, the green fill disappears automatically.

Top/Bottom Rules. These highlight the top 10 items, bottom 10 items, top 10 percent, or values above or below the average. Select your data, choose Top 10 Items, and Excel highlights the highest values. Change the number from 10 to 3 if you only want the top three. This is perfect for ranking reports and performance dashboards.

Data Bars, Color Scales, and Icon Sets. These are visual formatting rules that do not require you to pick specific thresholds manually. Data Bars draw horizontal bars inside each cell, with longer bars representing larger values. Color Scales apply a gradient from one color to another, for example red to green, based on the relative value of each cell. Icon Sets display arrows, traffic lights, or flags to indicate high, medium, and low values. These are ideal for heat maps and quick-scan dashboards. Use them sparingly. A table with data bars, color scales, and icon sets all at once is overwhelming. Choose one visual layer per table.

Creating a custom rule with formulas. For advanced control, use New Rule → Use a formula to determine which cells to format. This lets you write a logical formula that returns TRUE or FALSE. For example, to highlight an entire row when the status column contains "Overdue," select the entire data range, create a new rule, and enter =$D1="Overdue" where column D is the status column. The dollar sign before D locks the column reference so each row checks its own status value. When any cell in column D says Overdue, the entire corresponding row highlights. This is how you build status-driven reports.

Managing and clearing rules. To see all conditional formatting rules on a sheet, go to Conditional Formatting → Manage Rules. You can edit, delete, or reorder rules here. Rules are evaluated from top to bottom, and if two rules conflict, the one higher in the list wins. To remove all conditional formatting from a selection, choose Clear Rules → Clear Rules from Selected Cells. To remove every rule on the sheet, choose Clear Rules from Entire Sheet.

A practical warning. Conditional formatting can slow down large workbooks because Excel recalculates the formatting every time the sheet changes. On datasets with tens of thousands of rows, complex color scale rules or formula-based rules may cause noticeable lag. If this happens, consider converting the conditional formatting to static formatting after your analysis is complete. Copy the formatted cells, then use Paste Special → Values and Number Formats to freeze the appearance without the live rules.

8. Custom Number Formats

When Excel's built-in formats are not enough, you can build your own using a format code. Custom number formats are one of Excel's most underutilized power tools. They let you display numbers exactly how you want without changing the underlying value.

The four-section structure. A custom format code has up to four sections, separated by semicolons, in this order: Positive; Negative; Zero; Text. If you provide only one section, it applies to all numbers. If you provide two, the first applies to positive numbers and zeros, the second to negatives. Here is a practical example for financial variance reporting:

#,##0.00" F";#,##0.00" U";"-"

This displays positive numbers with an F for Favourable, negative numbers with a U for Unfavourable, and zeros as a dash. The underlying value remains a plain number, so formulas still work correctly. The text suffixes are purely visual.

Key placeholders. The 0 placeholder displays a digit always, padding with zeros if necessary. The # placeholder displays a digit only if it is significant, suppressing unnecessary zeros. The ? placeholder leaves space for insignificant zeros to align decimals vertically. The @ placeholder represents text values. The period is the decimal point. The comma is the thousands separator. A comma after a digit placeholder scales the number by a thousand, so #,##0.00, displays thousands as 45K.

Adding text and symbols. Enclose literal text in double quotes. To add a naira sign and the word NGN after numbers: #,##0.00" NGN". To display phone numbers with a specific pattern: 0000-000-0000. To show leading zeros for product codes: 000000 turns 42 into 000042.

Color codes. You can change font color within a custom format by adding a color name in square brackets at the start of a section: [Green]#,##0.00;[Red](#,##0.00);[Black]"-". This shows positive numbers in green, negative numbers in red inside parentheses, and zeros as black dashes. The available colors are Black, Blue, Cyan, Green, Magenta, Red, White, and Yellow.

Conditions in custom formats. You can apply different formats based on value ranges using conditions in square brackets: [<10][Red]0.00;[>=10][Green]0.00. This displays values below 10 in red and values 10 or above in green. For more complex conditions, use conditional formatting instead, but for simple numeric thresholds, custom format conditions are faster and do not slow down large workbooks.

Using AI to Move Faster in Excel Formatting

Every formatting decision in this lesson is a manual skill you need to understand, because you must recognize when formatting is wrong and fix it. But once you know the tools, AI can handle much of the repetitive application work, letting you focus on design decisions.

1. Use Copilot to apply consistent formatting across large datasets.
If you have a workbook with twenty sheets that all need the same header style, font, and number format, you can ask Copilot: "Apply the Accounting format with the naira symbol to all numeric columns in this workbook, and make the first row of every sheet bold with a dark blue fill and white text." Copilot can execute bulk formatting operations that would take half an hour of manual clicking. Always spot-check a few cells afterward to confirm the format was applied to the right ranges.

2. Generate custom number format codes with natural language.
Custom number formats are powerful but the syntax is cryptic. Instead of memorizing every placeholder, describe what you want: "Write an Excel custom number format that shows positive numbers in green with two decimal places and a thousands separator, negative numbers in red inside parentheses, and zeros as a dash." AI will generate [Green]#,##0.00;[Red](#,##0.00);"-". Copy it into the Format Cells dialog under Custom, and test it with a few sample values before applying it to your entire column.

3. Ask AI to design conditional formatting rules from business requirements.
When a manager says "Highlight any sales figure that is more than 10 percent below target," translate that into a formula-based conditional formatting rule with AI's help: "Write an Excel conditional formatting formula that highlights a cell in red if its value is more than 10% below the value in the adjacent target column." AI will suggest something like =B2 < A2 * 0.9 where column A is target and column B is actual. Apply it, test with values above and below the threshold, and confirm the highlighting behaves as expected.

4. Use AI to audit formatting consistency.
Before delivering a report, ask Copilot: "Check this workbook for inconsistent number formats, mixed fonts, unaligned headers, and cells that should be formatted as dates but are stored as text." AI can scan across sheets and flag inconsistencies that your eyes miss after hours of work. This is especially valuable when multiple people have edited the same file and each applied their own formatting preferences.

5. Verify every AI-generated format before trusting it.
AI might suggest a custom format that looks correct for positive numbers but mishandles negative values or displays zeros unexpectedly. Always test the full range of values your data contains: positive, negative, zero, very large, very small, and text. A format that works for 99 percent of your data but breaks on edge cases is worse than no format at all, because it creates false confidence. Test thoroughly, then deploy.

A habit worth building from this lesson onward: whenever you finish formatting a report, step back and ask whether a person seeing it for the first time could understand the story in ten seconds. If the answer is no, the formatting is not finished. AI can speed up the mechanics of applying formats, but the judgment of what makes data clear, credible, and compelling remains yours.

Next lesson: basic formulas, cell references, and named ranges.