Create your first dashboard from Excel

Objective

Turn the synthetic file into a reliable analytical table without altering the source evidence.

What the dataset represents

The Datos worksheet contains 180 operations from January to March 2026. Each row represents a sale; venta_id is the key, fecha fixes the time, región and canal describe the operation, and unidades, precio, descuento, coste and importe are quantities. The data contains no real names or personal data.

Guided procedure

1. Save a copy of the XLSX and do not edit the original CSV.
2. Check that there are ten unique headers and no titles or subtotals among the rows.
3. Confirm that venta_id is text, fecha is a date/time type, unidades is an integer and precio, descuento, coste and importe are numeric.
4. Select a transaction and manually verify that amount = units × price × (1 − discount).
5. Enable filters and look for blanks, repeated identifiers and unexpected categories.
6. Record 180 as the initial count and retain that control for the next lesson.

Common Error

Formatting text as a date does not always change its type. Sort chronologically and check that January, February and March follow the correct sequence. Do not insert a Total row inside the table either: it would contaminate filters, Power Query and pivot tables.

Visible Result

The table is filterable, with consistent types and one row per operation. The minimum check is 180 rows and no unnamed columns. If it does not match, return to the original copy before continuing.

Internal reference

content/mini-course/files/mini_dashboard_ventas.csv, version 1.0.0, reviewed 2026-08-15.

Objective

Calculate four indicators and a monthly and regional summary that reconcile with the source table.

Indicators

Sales sums amounts; units sums units; transactions counts venta_id; average order value divides sales by transactions. Do not use PROMEDIO on amounts as a substitute for the definition without first checking that each row represents one transaction.

Guided procedure

1. Create a control area outside the table and calculate sales with SUM (SUMA in Spanish Excel) over MiniVentas[importe].
2. Sum MiniVentas[unidades] and count MiniVentas[venta_id].
3. Divide sales by operations without rounding the intermediate calculation.
4. Insert a pivot table from MiniVentas: region in rows, date grouped by month in columns and amount summarised as Sum.
5. Enable grand totals and verify that they match the independent formula.
6. Temporarily filter to one region and check that the sum of the detail matches its subtotal.

Reference check

With the source unfiltered, the results are 113.181,75 € in sales, 630 units and 180 transactions. The leading region is Centro. These values help locate errors, not bypass the attempt.

Common mistakes

• Summarise importe by Count because Excel detected text.
• Sum discount percentages in the pivot table.
• Compare a filtered total with a formula that ignores the same filter.
• Round each row before summing and create an artificial difference.

Visible Result

The worksheet contains reconciled KPI and a month × region matrix. If the pivot total does not match, check the type, source range and aggregation before designing the chart.

Objective

Turn the validated summary into a readable page and write a useful conclusion without overstating what the case demonstrates.

Proposed design

Place sales, units, average transaction value and transactions at the top; below, use a line chart showing the three months and horizontal bars to compare regions. Keep one unit per axis, titles identifying the metric and enough text to avoid relying solely on colour.

Guided procedure

1. Copy only the reconciled results from the previous lesson.
2. Create the monthly line and order the axis by date, rather than alphabetically.
3. Create regional bars from highest to lowest; retain all regions.
4. Reduce borders and decorative elements that do not help comparison.
5. Check contrast, size, series names and readability at 200 % zoom.
6. Write an insight with a period, magnitude, comparison, possible action and limitation.

Insight model

“Between January and March 2026, Centro records 33.660,00 €, the highest cumulative regional sales in the dataset. Its channel and category mix should be reviewed before replicating actions; the case is synthetic and three months do not demonstrate causality or annual seasonality”.

Common mistakes

• Use a pie chart to compare four regions and three months simultaneously.
• Truncate the axis to dramatise a small difference.
• Present the leading region as the cause of the result without analysing composition.
• Hide the period or the synthetic nature of the data.

Result and next step

You now have a visible dashboard, a numerical check and a defensible conclusion. Exercise ADP-FREE-PR-01 checks sales, units, the leading region and chart choice. The full path adds SQL, Power Query, modelling, DAX, Power BI, security and four portfolio projects.