How do I build a weekly pipeline report from a HubSpot export in Excel
Export your deals from HubSpot, turn them into an Excel table, add a week column, and build a PivotTable by stage and week that you refresh each Monday.
Smalt AI team ·
Export your deals from HubSpot with the properties you need, open the file in Excel and convert it to a table, then add a helper column that turns each close date into a week. Insert a PivotTable with stage and week as the fields and amount as the value. Each week, replace the data, refresh the pivot, and the report updates.
Export the deals from HubSpot
In HubSpot, go to CRM > Deals and open the view you report from, such as All Deals. In list view, choose Export at the top right. In board view, use Board options > Export view. You pick the format (CSV, XLS or XLSX), a file name and the header language, and HubSpot emails you a download link. HubSpot’s export guide covers each of these steps.
The default export includes only the properties shown in the current view, so use Customize to add the ones you need. I would start with deal stage, amount, close date, deal owner, pipeline and create date. You also need export permission on your account, and the download link expires after 30 days.
Choose XLSX if you plan to work in Excel only. HubSpot’s export page states that XLS keeps 256 columns and drops the rest, while XLSX allows up to 16,384. Large exports can arrive as a zip split into several files, so check that you have all the rows before you build anything.
Get the data into a clean table
A PivotTable is fussy about its source. Microsoft’s data preparation guidance asks for one header row, no merged cells, no blank rows or columns, and one data type per column. Microsoft also recommends converting the range to an Excel table, which you do with Ctrl+T.
If you exported a CSV, you can load it through Power Query instead of opening it directly. In Excel, choose Get Data > Text/CSV, then pick Load, or Transform Data to clean the file first. Microsoft’s Text/CSV connector page lets you set the file origin (character set) and the delimiter. It infers UTF-8 only if the file starts with a byte order mark, so if special characters in company names look garbled, check the file origin.
Data type detection can use the first 200 rows or the entire data set, or you can turn it off so every column is Text. The page warns that inference can be wrong, so check the types before loading.
The simplest weekly routine is a table you paste into.
Add a week column
Group a pivot by date and you may get months or days when you want weeks. Microsoft documents grouping through right-click > Group, with a start, an end and a step. Grouping by days with a step of 7 is an alternative, but a helper column keeps calendar weeks stable and starts on the day you choose.
Add a column called Week Start and enter this formula. It assumes the date column is named Close Date and weeks start on Monday:
=INT([@[Close Date]])-WEEKDAY([@[Close Date]],2)+1
Format the column as a date. The formula is not from Microsoft; adjust the column name to match your export. Deals with no close date will return an error or a nonsense value, so filter or fill those first.
Build the PivotTable
Click inside the table and choose Insert > PivotTable. Drag Deal Stage to Rows and Week Start to Columns, then drag Amount to Values. The same Microsoft page notes that PivotTables default numeric fields to SUM, but fall back to COUNT when Excel reads the values as text. If you see counts where you expected amounts, open Value Field Settings, switch to Sum, and fix the source column’s data type. Stage order sorts alphabetically in a pivot unless you reorder it by hand.
A second pivot with Deal Owner in Rows shows the pipeline by rep.
Refresh it every Monday
A PivotTable works from a cached snapshot, so it does not change when the table does. Microsoft’s refresh instructions say to use PivotTable Analyze > Refresh (Alt+F5 on Windows), right-click > Refresh, or Refresh All if you have several pivots.
- Export the deals from the same saved view.
- Clear the old data rows in the table, keeping one row so the table and the Week Start formula survive.
- Paste the new rows in, keeping the header row and the Week Start column.
- Refresh All.
- Compare the Amount total with HubSpot.
The same page says a table picks up new rows on refresh, while a plain range needs its source range changed. Refresh can also reset column widths, so open PivotTable Options and turn on “Autofit column widths on update” and “Preserve cell formatting on update”. Menu names differ slightly between Excel versions and on Mac.
Doing it with an AI coworker
If you would rather not repeat the export each week, Smalt is one optional way to automate it, and you should still check its numbers against your HubSpot view.
What to check before you rely on it
The export is a snapshot. If a rep updates a deal after you download the file, the report will not show it until the next export, so note the export date on the sheet.
Sum the Amount column and compare it with the deal total on the same HubSpot view. If they differ, a common cause is an export view with a filter you forgot, or a file split into a zip where you opened only part of it.
Watch the date column. A common cause of “Cannot group that selection” is blanks or text in it, and they will break the Week Start formula too.
Check the amount column for currency. If your deals can be in more than one currency, summing the Amount column would mix them. Look at which currency each deal uses before you trust a total.
Smalt is an AI coworker that takes jobs like this end to end and hands back the finished file.