Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 76 ยท Visualization and Storytelling

Spreadsheets and BI Tools in the Workflow

Learn where spreadsheets and BI tools fit next to Python: map Excel formulas to pandas, prepare tidy tables for dashboards, plan hand-offs and keep one source of truth.

A data scientist who looks down on spreadsheets will have a hard career. Most of the people who need your results work in Excel. Their managers look at dashboards in Power BI, Tableau or Looker. The numbers that drive a company often start life in a spreadsheet and end up on a dashboard, and Python sits somewhere in the middle. Knowing what each tool is good at, and how to hand work between them, makes you more useful and your analyses more likely to be used.

This lesson is about the workflow: which tool for which job, how Excel ideas translate to pandas, how to prepare data so that a BI tool can use it, and how to avoid the classic disaster of "three different numbers for the same thing".

Who uses what

Tool Strength Weakness
Spreadsheet (Excel, Google Sheets) Instant, visible, everybody knows it. Great for small data, quick checks, manual inputs, budgets and what-if models. Hard to repeat, review and test; breaks silently; limited to about a million rows; mistakes hide inside cell formulas.
BI tool (Power BI, Tableau, Looker, Metabase) Interactive dashboards with filters, scheduled refresh, sharing and permissions; business users can explore without code. Complex logic becomes hard to maintain; limited statistics; each tool has its own formula language.
Python (pandas, scikit-learn) Repeatable, versionable, testable; handles large data, statistics, modelling and automation. Needs programming skills; not where most business readers want to look.
SQL and the warehouse Single place for cleaned, shared data; fast aggregation. Not a presentation tool.

None of these wins everywhere. The practical question is: who is the audience, and how often will this be repeated?

  • A one-off question for one person: a spreadsheet or a notebook, whichever is faster.
  • A recurring report for many readers: a dashboard fed by a database table, so that it refreshes itself.
  • An analysis with statistics or a model: Python, with the results handed over as a table or chart.
  • A business rule that finance edits each month (targets, prices, exchange rates): a spreadsheet, as an input that your pipeline reads.

A typical flow

flowchart LR
    S[("Source systems<br/>databases, apps, files")] --> W["Warehouse tables<br/>(SQL)"]
    W --> P["Python:<br/>clean, model, forecast"]
    P --> T["Result tables<br/>(tidy, documented)"]
    T --> B["BI dashboard<br/>(Power BI, Tableau)"]
    T --> X["Excel report<br/>for finance"]
    I["Spreadsheet inputs<br/>(targets, mappings)"] --> P

Python and SQL prepare and analyse; spreadsheets and dashboards present and collect inputs.

The most important piece is the box in the middle: a result table with a stable shape. If your Python job writes the same columns every night, any dashboard or report can read it and refresh automatically.

Excel ideas in pandas

If you already know Excel, you know most of pandas. The mapping below helps you read and replace spreadsheet logic. All of it is repeatable code instead of cell formulas.

In Excel In pandas
SUMIFS, COUNTIFS, AVERAGEIFS df.groupby("col")["x"].sum() or boolean filtering
VLOOKUP, XLOOKUP df.merge(lookup, on="key", how="left")
IF(cond, a, b) np.where(cond, a, b)
Pivot table df.pivot_table(index=..., columns=..., values=..., aggfunc=...)
Filter and sort df[df["x"] > 10].sort_values("x")
Remove duplicates df.drop_duplicates()
Text to columns df["col"].str.split(",", expand=True)
Fill down df["col"].ffill()

Here are four of them side by side. The sales table, the price list and the result are all small enough to check by eye:

Python SUMIFS, VLOOKUP, IF and a pivot table in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two cautions when you translate. First, how="left" in the merge matters: like VLOOKUP, it keeps every sales row, and unmatched products simply get NaN for the price (check with df["price"].isna().sum(); a typo in a product name would show up there, whereas a spreadsheet shows #N/A cell by cell). Second, VLOOKUP returns the first match, while merge returns all matches, so duplicates in the lookup table multiply rows. Check prices["product"].is_unique before you merge.

The full lessons for the spreadsheet side are in the Excel course: Pivot Table Basics, VLOOKUP and XLOOKUP.

Preparing data for a BI tool

Dashboards work best on tidy tables: one row per observation, one column per variable, one clearly typed value per cell. A wide report with months as columns is easy for a human to read but awkward for a dashboard, because the tool wants to filter and group by month, which has to be a column. The fix is melt, the reverse of a pivot (see Reshaping):

Python Wide report to tidy table, with dashboard-friendly columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Checklist for a table you hand to a dashboard or a colleague:

  • One header row, no merged cells, no totals mixed into the data, no blank rows. Add totals in the tool, not in the table.
  • Stable column names and types. Renaming revenue to Revenue (EUR) next month breaks every chart built on it. Use snake_case and put units in a data dictionary.
  • Real dates and numbers, not text such as "1,234.5 EUR" or "Q1-25".
  • ISO dates (2025-03-31) in files, so no one reads 04/05/2025 as April or May by mistake.
  • A key and a grain. Say what one row means ("one row per region per month") and make that key unique; test it with tidy.duplicated(["region", "month"]).any().
  • Dimensions and facts. Keep descriptive attributes (customer name, country) in one table and measurements (orders, amounts) in another, joined by keys. BI tools model data this way (a star schema).
  • Parquet or a database table instead of CSV for large or typed data, because CSV loses types.

Hand-offs without chaos

Most problems at the boundary between tools are not technical; they are about trust.

One source of truth. If an analyst computes "active customers" in Excel, the dashboard computes it differently, and Python gives a third answer, the meeting turns into an argument about numbers. Define each metric once, preferably in the warehouse or in a shared, version-controlled code module, and have every tool read it. Write down the definition ("a customer is active if they ordered in the last 90 days").

Spreadsheets as inputs need guarding. If people maintain a mapping table or targets in Excel, read the file in code and validate it: required columns exist, no blank keys, no duplicates, numbers are numbers. Fail loudly, with a clear message, instead of silently producing wrong totals.

Keep a refresh story. A dashboard is only as fresh as its last data load. Show a "data as of" date on every page. Know how the refresh is scheduled and who is told if it fails.

Do not paste values into a spreadsheet by hand. Every manual step is a place for errors and a task nobody remembers. Write results to a file or table through code (df.to_excel(...), see Working with Excel Files), and re-run it.

Mind permissions and privacy. Dashboards are shared widely. Remove personal data you do not need, aggregate small groups so that individuals cannot be identified, and use the tool's row-level security if different people may see different data.

Know the tool's limits. Excel can silently convert gene names or product codes into dates, drop leading zeros and round long numbers. Format sensitive columns as text before importing, and prefer CSV with explicit types when precision matters.

Which tool for which job

  • Explore a new dataset quickly: a notebook (and a spreadsheet if the file is small).
  • Check someone else's numbers: the spreadsheet they used, then reproduce in code.
  • Explain a result to executives: a clear chart in a slide, or a dashboard if they will return to it.
  • Let colleagues slice the data themselves: a BI dashboard on top of a clean table.
  • Statistical tests, models, forecasts: Python (then publish the output table to BI).
  • Budgets, plans, one-off what-if tables: a spreadsheet.

A good analyst moves between them without ego. If you want to learn the other side, see the Excel course and the Power BI introduction; the Tableau track is also being added to this site. The principles for charts themselves are in Visualization Principles.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A finance team edits monthly targets every month. Where should this input live?

    People who own the numbers should edit them where they are comfortable. Your code should read the file and check it before using it.
  2. 2. Which Excel function is most like df.merge(lookup, on='key', how='left')?

    Both bring matching columns from a lookup table into the main table. Remember merge returns all matches, so check that the lookup keys are unique.
  3. 3. Why melt a wide report with one column per month into a tidy table?

    A dashboard groups by columns. If the months are column headers, the tool cannot treat time as a dimension.
  4. 4. Three reports show different values for 'active customers'. What is the root fix?

    Different definitions cause different numbers. One shared, documented definition gives one answer.
  5. 5. What should a column hand-off table avoid?

    Merged cells, embedded totals and numbers stored as text all break automated tools.

Summary

  • Spreadsheets, BI tools, Python and SQL each have a role: choose by audience and by how often the work repeats.
  • A stable, tidy result table in the middle lets Python feed dashboards and reports automatically.
  • Excel concepts map directly to pandas: SUMIFS to groupby, VLOOKUP to merge, IF to np.where, pivot tables to pivot_table.
  • Prepare tables for BI with one header row, real dates and numbers, stable names, a unique key and tidy shape (melt).
  • Define each metric once, validate spreadsheet inputs, show data freshness, avoid manual copying and protect privacy.

Next: Descriptive Statistics in Practice begins the statistics chapter.