7.1 Borders

Borders are lines drawn on the edges of cells — top, bottom, left, right, or diagonal. They are the primary structural tool for making tables readable on screen and in print. Unlike gridlines (which are a display aid and do not print by default), borders are part of the cell formatting and always print.

Applying Borders from the Ribbon

1. Select the cell(s) where you want borders
2. Home → Font group → click the dropdown arrow ▼ next to the Borders button (the square with lines icon)
3. A gallery of preset border options appears:

Advanced Borders via Format Cells Dialog

For full control over line style, colour, and which specific edges have borders:

1. Select the cell(s) → Ctrl+1 → Border tab
2. In the Style panel on the left: click a line style (thin solid, thick solid, dashed, dotted, double, etc.)
3. In the Color dropdown: choose the border colour
4. Apply the border by clicking the buttons in the Presets section (None, Outline, Inside) — or — click individual edges in the Border preview diagram to toggle each edge on/off
5. The preview updates in real time — click OK when satisfied

Drawing Borders with the Border Draw Tool

1. Home → Font → Borders dropdown → Draw Border or Draw Border Grid
2. The cursor becomes a pencil ✏
3. Click and drag across cells to draw borders freehand
4. To change line style or colour before drawing: Borders dropdown → Line Style or Line Color
5. To erase borders: Borders dropdown → Erase Border → click/drag over borders to remove them
6. Press Esc to exit drawing mode

Professional Border Conventions

7.2 Number Formatting

Number formatting controls how a value displays without changing the underlying value that Excel stores and uses in calculations. The number 1500.5 can display as 1500.5, R 1,500.50, 150050%, 15 Jan 1904, or countless other formats — but Excel always stores and calculates with the same underlying value.

Applying Number Formats from the Ribbon

1. Select the cell(s)
2. Home → Number group → click the Number Format dropdown (shows the current format, e.g., "General")
3. Choose from the list — or click More Number Formats… for the full Format Cells dialog

The Standard Number Formats

Quick Format Buttons in the Number Group

7.3 Custom Number Formats

When the built-in formats do not meet your needs, you can create a Custom Number Format using format codes. Custom formats are one of Excel's most powerful and underused features.

Accessing Custom Formats

1. Select the cell(s) → Ctrl+1 → Number tab → select Custom at the bottom of the Category list
2. In the Type field, type your custom format code
3. The Sample preview shows how the active cell value will display
4. Click OK

Custom Format Code Building Blocks

Four-Part Custom Formats (Positive; Negative; Zero; Text)

A custom format can have up to four sections separated by semicolons, each controlling how a specific type of value displays:

Practical Custom Format Examples for SA Offices

7.4 Cell Styles

Cell Styles are pre-built, named combinations of formatting — font, size, colour, border, number format, and alignment — that you can apply with a single click. They ensure consistency across a workbook and make mass formatting changes easy: update the style definition and every cell using that style updates automatically.

Applying a Built-In Cell Style

1. Select the cell(s)
2. Home → Styles group → Cell Styles dropdown (click the small arrow to expand the full gallery)
3. Styles are grouped into categories:

Creating a Custom Cell Style

1. Format a cell exactly as you want the style to look (font, colour, border, number format, alignment)
2. Home → Cell Styles → New Cell Style…
3. Type a style name (e.g., "Corp Header", "Corp Total", "Input Cell")
4. Tick which formatting categories to include in the style (Number, Alignment, Font, Border, Fill, Protection)
5. Click OK
6. The style appears in the Cell Styles gallery under a "Custom" section at the top

Modifying an Existing Cell Style

1. Home → Cell Styles → right-click the style name → Modify…
2. Click Format… to open Format Cells and change the formatting
3. Click OK
4. Every cell in the workbook that uses this style automatically updates to the new formatting

Merging Styles from Another Workbook

1. Open both the source workbook (with the styles you want) and the target workbook
2. In the target workbook: Home → Cell Styles → Merge Styles…
3. Select the source workbook from the list → click OK
4. The styles from the source workbook are copied into the target workbook

7.5 Themes — Workbook-Wide Formatting

A Theme is a coordinated set of colours, fonts, and effects that applies to the entire workbook at once. Changing the theme instantly updates all themed colours, fonts, and chart styles throughout the workbook — making it easy to match corporate branding or change the look of a report in seconds.

Applying a Theme

1. Page Layout tab → Themes group → Themes dropdown
2. Hover over any theme to preview it on the current sheet in real time
3. Click to apply — all theme-based colours, fonts, and chart formatting update throughout the workbook

Theme Components

Creating and Saving a Custom Corporate Theme

1. Customise the Colors (entering your corporate hex codes) and Fonts (your corporate fonts)
2. Page Layout → Themes → Save Current Theme…
3. Name it (e.g., "Skailit Corporate") → Save
4. The theme appears in the Custom section of the Themes gallery and can be applied to any workbook
5. Share the .thmx file with colleagues — they place it in their Document Themes folder to access it

Theme Colours vs Standard Colours

7.6 Conditional Formatting — Introduction

Conditional Formatting automatically applies formatting to cells based on their value or content — highlighting cells that meet specific criteria without any manual action. It is covered in full in Module 24; this section introduces the concept and the most commonly used options.

Accessing Conditional Formatting

• Select the cell(s) → Home → Styles group → Conditional Formatting

Highlight Cell Rules

The most frequently used conditional formatting — highlights cells based on their value:

1. Home → Conditional Formatting → Highlight Cells Rules
2. Choose a rule type:

Top/Bottom Rules

• Home → Conditional Formatting → Top/Bottom Rules
• Highlight: Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average
• These rules automatically identify the relative performers in a dataset — useful for dashboards

Managing Conditional Formatting Rules

• Home → Conditional Formatting → Manage Rules… → shows all rules applied to the current selection or sheet. Edit, delete, or reorder rules here.
• Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells or Clear Rules from Entire Sheet

7.7 Format as Table — Instant Professional Formatting

Format as Table (also called "Excel Table") converts a data range into a structured Excel Table with automatic formatting, filtering, sorting, and formula capabilities built in. It is the fastest way to make a data range look professional while also gaining powerful analytical features. Excel Tables are covered in full in Module 9; this section covers the formatting aspect.

Applying Table Formatting

1. Click anywhere inside your data range
2. Home → Styles group → Format as Table
3. A gallery of table styles appears in three sections: Light, Medium, Dark
4. Click any style to apply it
5. A dialog asks: "Where is the data for your table?" and offers to include/exclude headers
6. Confirm the range → click OK

What Table Formatting Provides

• Header row: formatted distinctly (coloured background, white bold text)
• Alternating row shading: applied automatically — every other row has a lighter shade of the table colour
• Filter dropdowns: AutoFilter arrows appear on every header column automatically
• Automatic expansion: when you type in the row immediately below the table, the table and its formatting extend automatically to include the new row
• Total row: Table Design → tick Total Row → a Total row appears at the bottom of the table with dropdown options for SUM, AVERAGE, COUNT, MAX, MIN, etc. per column
• Banded rows/columns: Table Design → tick/untick Banded Rows or Banded Columns

Modifying the Table Style

1. Click anywhere in the table → the Table Design contextual tab appears
2. Table Design → Table Styles group → hover to preview → click a new style to apply
3. Or create a custom table style: Table Design → Table Styles → New Table Style…

Removing Table Formatting Without Losing Structure

• Table Design → Table Styles → scroll to the very beginning of the gallery → select None (clear formatting but keep the table structure)
• To completely convert back to a regular range: Table Design → Tools group → Convert to Range

7.8 Row & Column Formatting

Setting Default Row Height for the Entire Sheet

1. Click the Select All button (top-left corner) to select the entire sheet
2. Home → Cells → Format → Row Height…
3. Type the height in points (default is typically 15 or 20 pt) → OK
4. All rows on the sheet now have this height — useful for creating a uniform, professional look across the entire spreadsheet

Setting Default Column Width for the Entire Sheet

1. Click Select All → Home → Format → Default Width…
2. Type the width in characters → OK

AutoFit All Columns and Rows at Once

1. Click Select All (Ctrl+A)
2. Home → Format → AutoFit Column Width
3. Then Home → Format → AutoFit Row Height
4. Every row and column on the sheet resizes to fit its content — a fast way to clean up a messy imported dataset

7.9 Quick Self-Check

Q1: A financial report shows a grand total row. What two types of border formatting are standard accounting convention for a grand total, and how do you apply them?

✓ The accounting convention for a grand total uses: (1) a single thin top border to indicate "sum of the values above" and (2) a double bottom border (Bottom Double Border) to signal the final figure. Apply by selecting the total row cells → Home → Font → Borders dropdown → Bottom Double Border for the double underline. For the top border: Borders dropdown → Top and Bottom Border — or use Format Cells (Ctrl+1) → Border tab → set the top border to a thin solid line and the bottom to a double line precisely.

Q2: A cell contains the value 0.15 and you want it to display as 15.00%. What number format do you apply, and what would happen if you entered the number 15 and then applied the same format?

✓ Apply the Percentage format with 2 decimal places: select the cell → Home → Number → Percentage (%) → then click Increase Decimal twice to get 2 decimal places (or Ctrl+1 → Number → Percentage → set Decimal places: 2). The value 0.15 displays as 15.00%. If you entered 15 and applied the same format, it would display as 1500.00% because Percentage format multiplies by 100 — 15 × 100 = 1500. The correct practice is to always store percentages as their decimal equivalent (0.15 for 15%) and let the Percentage format handle the display.

Q3: You want to create a custom number format that shows Rand amounts as "R 1,500.00" for positive values, "(R 250.00)" in red for negative values, and a dash for zero. Write the custom format code.

✓ The four-section custom format: "R "#,##0.00;[Red]"(R "#,##0.00")";"—". The first section (before the first semicolon) formats positive values as R 1,500.00. The second section formats negative values in red with brackets around them. The third section replaces zero with a dash. Apply via Ctrl+1 → Number tab → Custom → paste the code into the Type field → OK. This is the standard accounting/financial display format used in SA corporate reporting.

Q4: Your company wants all spreadsheets to use a specific shade of dark green (#1A4D1A) as the corporate colour and Calibri as the corporate font. How do you set this up so it can be applied to any workbook with one click?

✓ Create a custom theme: (1) Page Layout → Themes → Colors → Customize Colors → update the relevant colour slots with #1A4D1A → name the colour set "Corp Colors" → Save. (2) Page Layout → Themes → Fonts → Customize Fonts → set both Heading and Body fonts to Calibri → name "Corp Fonts" → Save. (3) Page Layout → Themes → Save Current Theme → name "Corp Theme" → Save. Share the .thmx file with colleagues. Anyone who places this file in their Document Themes folder can apply it via the Themes gallery with one click, and all theme-aware colours and fonts update throughout the workbook.

Q5: You have a sales spreadsheet with revenue figures in column C (C2:C200). You want all cells where the revenue is below R10,000 to automatically highlight in red. How do you set this up?

✓ Select C2:C200 → Home → Conditional Formatting → Highlight Cells Rules → Less Than → type 10000 in the value field → in the format dropdown, choose "Red Fill with Dark Red Text" (or choose "Custom Format…" to create a specific red background) → click OK. Any cell in C2:C200 where the value is less than 10000 immediately displays with a red highlight. The formatting is dynamic — if a value rises above R10,000, the red formatting disappears automatically without any manual intervention.

Q6: What is the difference between using the "Total" Cell Style and applying bold + border formatting manually to a total row?

✓ The "Total" Cell Style is a defined, named style that applies a complete set of coordinated formatting (bold font, top and double bottom border, specific size) in one click. The key advantage: if you later modify the "Total" Cell Style definition (right-click style → Modify), every cell using that style throughout the entire workbook updates simultaneously. Manual bold+border formatting is unlinked — you must find and reformat each total row individually if the design changes. For workbooks with many total rows, Cell Styles provide major maintenance advantages. Additionally, Cell Styles from one workbook can be merged into another workbook via Home → Cell Styles → Merge Styles, making it easy to apply a consistent look across multiple files.

✓ Module 7 Complete — You Have Learned:

• Borders — 8 preset border options (All Borders, Outside, Bottom Double, etc.); Format Cells Border tab (line style, colour, individual edges, preview diagram); Border Draw tool (pencil cursor); professional SA financial reporting border conventions (header thick bottom, subtotal single, grand total double)
• Number formatting — formatting displays without changing the value; 10 standard formats (General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text); quick format buttons (Accounting $, Percent %, Comma, Increase/Decrease Decimal); keyboard shortcuts (Ctrl+Shift+$, %, #, @, ~)
• Percentage formatting caution — enter 0.15 to display 15%, not 15
• Custom number formats — format code building blocks (0, #, ., ",", %, "text", @, date/time codes, colour codes [Red][Green][Blue]); four-section format (Positive;Negative;Zero;Text); 9 practical SA office custom format examples including R currency, leading zeros, date formats, conditional colour
• Cell Styles — 5 built-in style categories (Good/Bad/Neutral, Data and Model, Titles and Headings, Themed, Number Format); creating custom styles; modifying styles (all cells update automatically); merging styles from another workbook
• Themes — changing the full workbook theme; the 3 theme components (Colors, Fonts, Effects) and how to customise each independently; creating and saving a corporate theme (.thmx); Theme Colors vs Standard Colors (theme colours change with theme; hex colours stay fixed)
• Conditional Formatting introduction — Highlight Cells Rules (Greater Than, Less Than, Between, Equal To, Text Contains, Date Occurring, Duplicate Values); Top/Bottom Rules (Top 10, Bottom 10, Above/Below Average); Manage Rules; Clear Rules
• Format as Table — applying from the gallery; what it provides (styled header, alternating shading, filter dropdowns, auto-expansion, Total Row); modifying the table style; Table Design tab; removing style (None) vs converting to range
• Row and column formatting — setting default row height for entire sheet; default column width; AutoFit all columns and rows simultaneously (Select All + Format)