Add a Dropdown Filter That Drives an Excel Dashboard

Readers of a dashboard want to ask their own questions — this region, that product — without editing formulas. In Excel you would add a slicer. Slicers cannot be created from Python: they live in parts of the workbook format that neither openpyxl nor XlsxWriter writes. A dropdown cell built with data validation, with formulas and charts that follow it, gives readers the same control, and it works in every Excel version and in LibreOffice. This guide, part of Building Multi-Sheet Excel Dashboards, builds that filter step by step and checks each selection by recalculating the workbook.

Prerequisites

pip install openpyxl

Monthly revenue by region in a table on its own sheet:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.table import Table

ROWS = [("Jul", "North", 100), ("Jul", "South", 80), ("Aug", "North", 120), ("Aug", "South", 90),
        ("Aug", "East", 40), ("Sep", "North", 140), ("Sep", "South", 70), ("Sep", "East", 55)]
wb = Workbook()
dash = wb.active
dash.title = "Dashboard"
data = wb.create_sheet("Data")
data.append(["Month", "Region", "Revenue"])
for row in ROWS:
    data.append(row)
data.add_table(Table(displayName="Sales", ref=f"A1:C{data.max_row}"))

Step 1 — Build the option list from the data

Generate the dropdown's options from the data itself, with "All" first, on a hidden sheet, and name the range. The list then never falls out of step with the data:

lists = wb.create_sheet("Lists")
regions = ["All"] + sorted({region for _, region, _ in ROWS})
for i, region in enumerate(regions, start=1):
    lists.cell(i, 1, region)
lists.sheet_state = "hidden"
wb.defined_names["RegionList"] = DefinedName("RegionList", attr_text=f"Lists!$A$1:$A${len(regions)}")
print(regions)                                    # ['All', 'East', 'North', 'South']

A list typed straight into the validation rule — formula1='"All,East,North,South"' — also works, but it is limited to 255 characters, cannot be sorted or checked with a formula, and has to be rebuilt inside the rule. A named range on a hidden sheet avoids all three.

Step 2 — The dropdown cell

A DataValidation of type list restricts the cell to the named range and shows the dropdown arrow. An error message explains what went wrong when someone types a value instead:

dash["A1"] = "Region"
dash["B1"] = "All"
picker = DataValidation(type="list", formula1="=RegionList", allow_blank=False,
                        showErrorMessage=True, errorTitle="Region",
                        error="Choose a region from the list.")
dash.add_data_validation(picker)
picker.add("B1")

Give the cell a distinct look — a light fill and a border — so readers recognise it as the control, and place it at the top left of the dashboard, where people look first. An input message (promptTitle and prompt on the validation) can add a short hint that appears when the cell is selected. Dropdowns and their options are covered in more detail in Add Dropdown Data Validation to Excel with openpyxl.

Step 3 — Formulas that follow the selection, including All

Each figure is a SUMIFS whose region criterion comes from the dropdown. "All" is handled by swapping in the wildcard *, which matches any text:

dash["A3"], dash["B3"] = "Month", "Revenue"
for row, month in enumerate(["Jul", "Aug", "Sep"], start=4):
    dash[f"A{row}"] = month
    dash[f"B{row}"] = f'=SUMIFS(Sales[Revenue], Sales[Month], A{row}, Sales[Region], IF($B$1="All","*",$B$1))'
dash["A7"], dash["B7"] = "Total", "=SUM(B4:B6)"
wb.save("filtered_dashboard.xlsx")

Recalculating the workbook in LibreOffice with each selection gives the expected figures: All shows 180, 250 and 265 for July to September with a total of 695; North shows 100, 120 and 140; East shows 0, 40 and 55, because East had no sales in July. The wildcard matches text only — if the filtered column holds numbers, such as a year, use two formulas selected with IF, or SUMPRODUCT with an OR condition, instead of "*".

Step 4 — A chart that follows the dropdown

Point the chart at the formula cells, not at the data table. Every change of selection recalculates the formulas, and the chart redraws from them:

chart = BarChart()
chart.x_axis.delete = False  # Excel hides axes unless this is set
chart.y_axis.delete = False
chart.title = "Revenue by month"
chart.add_data(Reference(dash, min_col=2, min_row=3, max_row=6), titles_from_data=True)
chart.set_categories(Reference(dash, min_col=1, min_row=4, max_row=6))
chart.legend = None
chart.height, chart.width = 7, 12
dash.add_chart(chart, "D1")
wb.save("filtered_dashboard.xlsx")
print(len(dash._charts))                          # 1

A title that names the selection helps readers — put a formula such as ="Revenue: "&B1 in the cell above the chart, since chart titles written by openpyxl are static text. Charts on dashboards are covered in Build a Dashboard Sheet with Charts from Multiple Tabs.

Step 5 — A second filter

A second dropdown works the same way; each SUMIFS simply gains another criterion pair. Suppose the data had a product column — the formula for a region and product filter, both with "All", becomes:

def filtered_sum(month_cell, region_cell="$B$1", product_cell="$B$2"):
    return (f'=SUMIFS(Sales[Revenue], Sales[Month], {month_cell}, '
            f'Sales[Region], IF({region_cell}="All","*",{region_cell}), '
            f'Sales[Product], IF({product_cell}="All","*",{product_cell}))')

print(filtered_sum("A4"))

Keeping formula construction in a function means the dashboard's cells all follow one pattern, and adding a third filter is a one-line change. Put all the filter cells together at the top of the dashboard, labelled, so readers see at a glance what the figures are filtered by.

Step 6 — Test every selection

A filter has as many states as it has options, and each should be checked. Compute the expected figures for every option with pandas, then set the dropdown to each option in a copy of the workbook, recalculate it and compare:

import subprocess

import pandas as pd
from openpyxl import load_workbook

frame = pd.DataFrame(ROWS, columns=["Month", "Region", "Revenue"])
expected = {}
for option in regions:
    chosen = frame if option == "All" else frame[frame["Region"] == option]
    by_month = chosen.groupby("Month")["Revenue"].sum()
    expected[option] = [int(by_month.get(m, 0)) for m in ["Jul", "Aug", "Sep"]]
print(expected["East"])                            # [0, 40, 55]

def rendered_figures(option):
    wb_copy = load_workbook("filtered_dashboard.xlsx")
    wb_copy["Dashboard"]["B1"] = option
    wb_copy.save(f"check_{option}.xlsx")
    subprocess.run(["soffice", "--headless", "--convert-to",
                    "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true", f"check_{option}.xlsx"],
                   check=True, capture_output=True, timeout=120)
    lines = open(f"check_{option}.csv").read().splitlines()
    return [int(line.split(",")[1]) for line in lines[3:6]]

for option in regions:
    assert rendered_figures(option) == expected[option], option
print("all selections match")

The pandas side is an independent calculation of the same question, so agreement for every option is strong evidence that the criteria, the wildcard and the cell references are right. Run it whenever the dashboard's layout or formulas change; four recalculations take a few seconds.

With XlsxWriter instead of openpyxl, the dropdown is one call — worksheet.data_validation("B1", {"validate": "list", "source": "=RegionList"}) — and the formulas and chart are written the same way; remember to store the formula results for readers that do not recalculate.

Common pitfalls

• Typing in the dropdown cell. Without showErrorMessage=True, a typo like "Nroth" is accepted and every figure shows 0. Keep the error message on.
• Lists that go stale. Regenerate the hidden list on every run from the data; a hard-coded list misses new regions.
• Protecting the dashboard but not the dropdown. If the dashboard sheet is protected, unlock the dropdown cell first, or readers cannot change the selection at all. Locking cells while leaving inputs editable is covered in Lock Cells and Protect a Sheet with openpyxl.
• A default that hides data. Opening on a specific region makes the dashboard look incomplete to readers who do not notice the filter. Default to "All".
• Wildcards in the data. A region literally called "North*" would match more than itself. Such values are rare, but clean them if they occur.

Conclusion

Native slicers are out of reach from Python, but a data validation dropdown gives readers the same control. Build the options from the data on a hidden sheet with "All" first, name the range, restrict the dropdown cell to it with an error message, write SUMIFS criteria as IF($B$1="All","*",$B$1), and point charts at the formula cells. Add further filters as extra criterion pairs, and check each selection by recalculating the workbook.

Frequently asked questions

Can openpyxl or XlsxWriter create a real Excel slicer? No. Slicers are stored in parts of the file format that neither library writes. A data validation dropdown that drives formulas gives readers the same kind of control and works in every Excel version and in LibreOffice.

How do I add an All option to the filter? Put "All" at the top of the list and write the criterion as IF($B$1="All","*",$B$1). The asterisk is a wildcard that matches any text, so SUMIFS includes every region when All is selected.

Why does the dropdown list come from a separate sheet? A list typed directly into the validation rule is limited to 255 characters and must be edited in the rule. A range on a hidden sheet, referenced through a name, can be long, is easy to regenerate from the data and is easy to check.

Will the chart update when the selection changes? Yes, if the chart's series refer to the formula cells that follow the dropdown rather than to the raw data. Changing the selection recalculates the formulas, and the chart redraws from them.

Related

• Up one level: Building Multi-Sheet Excel Dashboards — dashboards across sheets.
• Link Dashboard Cells to Data Sheets with Formulas — the formulas behind the figures.
• AVERAGEIFS, MAXIFS and MINIFS Equivalents in pandas — the conditional aggregates in pandas.
• Add a Native Excel Pivot Table with Python — when readers need Excel's own pivot tools.