Express courier. Direct.
Also overnight.
x25pronto Switzerland takes goods direct to the destination. Small loads go by car. Larger loads go by van or Van XL. Direct, express and overnight trips run Monday to Sunday when a slot is free. Free times are shown in the booking calendar. This keeps the plan clear.
Excel workshop: Pivot tables for shipping volume analysis
A step-by-step guide to analyze shipping data in Excel using Pivot tables, visual charts and automatic quantile functions. Useful for logistics and dispatch teams across Switzerland.
Quick answer
This workshop shows how to use Pivot tables to analyze shipping volumes, build PivotCharts and compute quantiles automatically in Excel. Start with a clean table (Date, Sender city, Receiver city, Customer, Shipment type, Weight, Volume, Status, Distance). Create a Pivot table to compare volumes by route, customer and period. Add PivotCharts and calculated fields, and use PERCENTILE.INC or QUARTILE.INC to get automated quantile measures.
1. Prepare your shipping dataset
Collect relevant fields in one worksheet table: shipment ID, date, sender city, receiver city, customer, shipment type (e.g. overnight), weight (kg), volume (m³) and status. Remove duplicates, fix date formats and ensure numeric fields are numbers. For Swiss reports include city names like Zurich, Bern and Basel or postal codes.
2. Build the Pivot table
Select the table and choose Insert > PivotTable. Put Date or Month in Rows, Customer or Route in Columns and use Count of Shipments or Sum of Volume in Values. Group date fields by month or quarter and add filters for shipment type or status to focus on approved, properly packed shipments.
3. Calculated fields and KPIs
Add calculated fields for KPIs such as average volume per shipment (Sum Volume / Count Shipments) or volume per kilometre. In PivotTable Tools, select Fields, Items & Sets > Calculated Field and enter formulas like =Volume/Count.
4. Charts and dashboard basics
Create PivotCharts (bar, line or stacked area) to show seasonal change. Combine several PivotTables on a dashboard sheet: monthly total volume, top-10 customers by volume and average volume per route. Use Slicers for interactive filters (customer, route, period).
5. Automatic quantile calculations
Use the raw table or a Pivot output as a table and compute quantiles with formulas. In recent Excel versions use PERCENTILE.INC(Range, k) or QUARTILE.INC(Range, quart). Use named table references (e.g. =PERCENTILE.INC(Table1[Volume],0.9)) for a 90% quantile. Modern functions like FILTER and UNIQUE can prepare ranges for targeted quantile checks.
6. Automation and data quality
Keep the raw data as an Excel Table (CTRL+T). Pivot tables will then expand with new rows. Use Power Query or simple VBA macros to automate cleansing steps. Check samples for missing dates or inconsistent location names, especially for night shifts and transfers between depots.
Frequently asked questions
How do I group shipments by month in a Pivot table?
Right-click a date in the Pivot table and choose 'Group'. Select Months or Quarters. Excel will aggregate rows by the chosen period.
Which formula gives the 90% quantile of shipping volume?
Use =PERCENTILE.INC(Table1[Volume],0.9) or =QUARTILE.INC(Table1[Volume],3) in modern Excel. Ensure the range name refers to the numeric volume column.
How do I refresh Pivot tables when new shipments are added?
If data is an Excel Table, use 'Refresh' in PivotTable Tools. For automatic refresh on open use a small VBA macro or refresh via Power Query.
Can I combine x25pronto shipment exports with this method?
Yes. Export shipping reports from your dispatch or ERP system to CSV/Excel with fields like date, route, customer and volume. Then follow the workshop steps to build analyses.