Advanced Excel for Data Analysts: Formulas, Pivot Tables and Dashboards

Introduction

Many beginner data analysts overlook Excel in favor of Python and SQL. This is a mistake. In corporate environments, business leaders use spreadsheets to inspect transaction details, run scenarios, and track KPIs. A skilled analyst understands when to query in SQL and when to use Excel for high-speed local analysis.

Essential Formulas Every Analyst Needs

Below are the core functions that form the backbone of advanced exploratory analysis:

1. XLOOKUP (The Modern VLOOKUP)

Unlike standard VLOOKUP, XLOOKUP can look to the left, defaults to exact match, and does not break when you insert columns. It matches customer IDs to customer details like this:

        SQL / PYTHON QUERY
        
          
          Copy
        
      
=XLOOKUP(A2, Customers!A:A, Customers!B:B, "Not Found")

2. SUMIFS & COUNTIFS (Multi-criteria Aggregation)

Use SUMIFS to calculate total revenue filtered by region and product category:

        SQL / PYTHON QUERY
        
          
          Copy
        
      
=SUMIFS(Orders!C:C, Orders!D:D, "North", Orders!E:E, "Electronics")

3. INDEX / MATCH (Dynamic Array Lookups)

For nested index searches that need to be highly robust and compatible across Excel versions, combine INDEX and MATCH:

        SQL / PYTHON QUERY
        
          
          Copy
        
      
=INDEX(PriceGrid, MATCH(Product_ID, ProductGrid, 0), MATCH(Month, MonthGrid, 0))

Pivot Tables: The Exploration Engine

Pivot tables are one of the fastest ways to summarize raw spreadsheets. Within seconds, you can drag a list of 50,000 transactions and group them by month, region, or product category. Analysts use them to identify anomalies, double-check data totals before loading to databases, and build initial reporting views.

Designing Clean Dashboards

A professional Excel dashboard should not look like a random block of colorful tables. Use these design guidelines:

• Muted colors: Use a unified corporate palette (like dark blues, slate grays, and single accent colors) instead of bright primary colors.
• Clear visual hierarchy: Place high-level KPIs at the top, trends below, and detailed tables at the bottom.
• Hide gridlines: Go to the View tab and uncheck “Gridlines” to instantly make your spreadsheet look like a clean custom application.

Next Steps

At SBS, we teach Advanced Excel models, lookup techniques, financial reporting dashboards, and how to connect spreadsheets directly with SQL databases.