Skip to content

Visuals & Dashboards

Visual Engineering & Conditional Formatting

Conditional formatting acts as a performance indicator rather than a tool for direct value comparisons. It updates dynamically based on performance criteria.

[Home Ribbon] ──> [Conditional Formatting]
                       ├── Data Bars (Gradient vs. Solid Fill)
                       ├── Color Scales (2-Color & 3-Color Midpoints)
                       └── Icon Sets (Directional Status Arrows)

Components

  • Data Bars: Visualizes value scales inside cells. Available as Gradient Fill (shaded transitions) or Solid Fill (bold, flat color blocks).

    • Hide Numbers: Go to More Rules > Check Show Bar Only.

    • Custom Limits:* Change minimum/maximum settings from "Automatic" to Number to establish absolute boundaries.

  • Color Scales: Highlights shifts from highest to lowest values using 2 or 3-color spectrums. Adjust the Midpoint settings via More Rules to accurately pinpoint median values.

  • Icon Sets: Highlights trend trajectories using directional arrows. Configure criteria rules using clear Numbers rather than generalized percentages.

  • Highlight Rules: Used to target duplicates separately within individual columns.

  • Target KPI Settings: Write your target variable into a standalone static cell, and reference that cell inside the Manage Rules manager to build responsive, dynamic benchmarks.

Professional Chart Engineering

Charts make data easy to read at a glance.

Chart Selection Framework

  Data Presentation Goal
         │
         ├──► Comparison (Categories) ──► Clustered Column (1 Variable) or Stacked Column (2+ Variables)
         │
         ├──► Trends (Time-Series)    ──► Line Chart or Area Chart
         │
         └──► Composition (Proportion)──► Pie Chart or Pie of Pie (Sub-categories)

Formatting Checklist * Clustered 2D Column: Best for single-variable direct metric comparisons. * Stacked 2D Column: Best for tracking multiple variables across a shared category. * 100% Stacked 2D Column: Best for displaying structural composition changes across a limited number of variables (2 to 4). * Line with Markers: Adds clear data points along trend lines to make reading complex data easier.

Executive Dashboard Architecture

Dashboards provide a visual overview of business performance to help identify strengths and areas for improvement.

The 8 Pillars of Dashboard Design

  1. Create the Environment: Hide raw data sheets. Set up two dedicated tabs: Pivots and Dashboard.
  2. Define Business Questions: Clearly list the questions your dashboard needs to answer before building.
  3. Build Clean Visuals: Create focused charts that simplify complex information.
  4. Align & Structure Layouts: Keep charts organized and consistently sized.
  5. Remove Visual Clutter: Clean up unnecessary lines, labels, or borders.
  6. Highlight Key Insights: Use clear labels and highlights to emphasize key data points.
  7. Add Interactive Slicers: Connect slicers across tables to let users filter data dynamically.
  8. Apply a Consistent Theme: Use a unified color palette to keep the design professional.

Step-by-Step Production Workflow

Environment Optimization

  • Go to the View Ribbon: Set a custom zoom level (e.g., 60%) for better overview visibility.
  • Uncheck Gridlines, Headings, and the Formula Bar.
  • Minimize the ribbon interface: Click the down arrow on the ribbon menu and select Show Tabs Only to maximize your screen space.

Data Preparation & Analysis

  • Paste your business questions directly into your Pivots worksheet.
  • Build a pivot table to answer each question. Copy and paste your pivot tables to keep them organized.
  • If data rows contain empty values, delete them from the source table and click Refresh All to clean up your pivot tables.
  • Fix column headers, auto-fit column widths, and format financial numbers as currency.

Visual Polish & Slicers

  • Create your charts, then cut (Ctrl + X) and paste (Ctrl + V) them into your Dashboard sheet.
  • Go to Format > Enable Snap to Grid for perfect chart alignment and sizing.
  • Remove default legends, vertical axes, and background borders (No Fill and No Line).
  • Select the chart title, type =, click the cell containing your written question, and press enter to link the title dynamically. Make titles bold (Ctrl + B).
  • Add Slicers or Timelines through PivotTable Analyze. Use Report Connections to link them to all relevant pivot models.
  • Add dividing lines using Insert > Illustrations > Shapes > Line.
  • Apply professional color palettes via Page Layout > Colors.

Case Study: E-Commerce Orders Dashboard

An end-to-end workflow for analyzing transactional e-commerce data.

1. Data Engineering Formulas

Before building pivot tables, fill any gaps in your source data table using these formulas:

  • Total Sales (if missing):
=PRODUCT([@[Unit Price]], [@[Order Quantity]])
  • Net Revenue After Discount (with integer rounding):
=ROUND([@[Gross Revenue]] - ([@[Gross Revenue]] * [Value @Discount]), 0)
  • Average Discount Value Given:
=[@[Gross Revenue]] - [@[Net Revenue After Discount]]

2. Layout Structure & Hierarchy

  • Top Section: High-level KPIs and summaries.
  • Middle Section: Column and bar charts for category comparisons.
  • Bottom Section: Line charts to show time-series and performance trends over time.

3. Visual Polish for KPIs

  • Select your chart bars, go to settings, and decrease the Gap Width to 15% to make them stand out.
  • Remove the default chart axes and add centered Data Labels. Increase the label font size to 55pt and set the text color to white for maximum contrast.
  • To create smooth, rounded borders on bar elements: Go to Format Chart Area > Line > Set width to 10pt and set the compound join type to Round.
  • For complex charts with large scale variations, switch the vertical axis settings to a Logarithmic Scale to keep all data points visible.