Guides

Case Study: How Big Is US E-commerce? Excel Analysis of Census Retail Data

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 7 October 2026 · Last updated: 7 October 2026

A planning manager at a US retailer asks a simple question before next year's budget: "Online keeps growing. How fast, and how much bigger is the holiday quarter?" The answer is in a free government spreadsheet. In the second quarter of 2026, e-commerce was 17.1% of all US retail sales (seasonally adjusted). From 2015 to 2025 online sales grew 14.0% a year, almost three times as fast as total retail (4.9%). And the fourth quarter alone brings about 30.4% of a year's e-commerce sales. This page shows how to get those numbers in Excel, step by step.

Download the Census Bureau's quarterly e-commerce time series, clean the label column into Year and Quarter with TRIM, MID, FIND and LEFT, sort it oldest first, then use SUMIFS for yearly totals, a growth formula for year-on-year change, a CAGR formula for long-term growth, and a PivotTable with Show Values As to see the share of each quarter. Four charts on one sheet make the dashboard.

Download the practice file (case-study-us-ecommerce-census.xlsx)

Excel file, 36 KB. It holds the trimmed real data and every formula used below. It has the two published sheets exactly as downloaded (Raw NSA, Raw SA), the cleaned table, the yearly analysis and the dashboard.

The business question

Budgets need two numbers: how fast the online channel grows, and how uneven it is across the year. A team that plans online staff, warehouse space and ad spend evenly across four quarters will be short in Q4 and idle in Q1. So the questions are:

1. How big is e-commerce compared with all retail, and is the share still rising?
2. How fast has it grown over ten years, compared with total retail?
3. How much of a year's online sales fall in the fourth quarter?

The data: source and licence

Step 1: Clean the file

Open the downloaded file and you will see a report, not a table: title rows, headers spread over four rows, the newest quarter at the top, labels with (p) and (r), "NA" in the first growth cells, and footnotes at the bottom. The Raw NSA sheet of the practice file shows it exactly as published.

1. Find the data rows: the first quarter label is in B9 ("2nd quarter 2026(p)") and the last in B115 ("4th quarter 1999"). That is 107 quarters.
2. On a new sheet called Clean, pull the label with =TRIM('Raw NSA'!B115), starting from the oldest row so the table runs oldest first.
3. Get the year: =VALUE(MID(A2,FIND("quarter",A2)+8,4)). FIND returns where the word quarter starts; the year starts 8 characters later.
4. Get the quarter number: =VALUE(LEFT(A2,1)).
5. Flag the status: =IF(ISNUMBER(SEARCH("(p)",A2)),"Preliminary",IF(ISNUMBER(SEARCH("(r)",A2)),"Revised","Final")).
6. Bring the two dollar columns and recalculate the share yourself: =F2/E2. The published percent is rounded to one decimal; your own division is exact.
7. Do the same for the seasonally adjusted sheet (columns H to J). Both sheets list the quarters in the same rows, which you can check by comparing the labels.
8. Add year-on-year growth for each quarter from the fifth row on: =F6/F2-1 compares a quarter with the same quarter one year earlier.
9. Check: 107 rows, years 1999 to 2026, and every year from 2000 to 2025 has exactly four quarters (=COUNTIFS(B:B,2010) gives 4).

Step 2: Explore with a PivotTable

1. Click inside the Clean table and choose Insert > PivotTable > New Worksheet.
2. Drag Year to Rows, Quarter to Columns and E-commerce NSA to Values.
3. Filter Year to 2000-2025 (untick 1999 and 2026, which are incomplete years).
4. Right-click a value, choose Show Values As > % of Row Total. Each row now shows how a year's online sales split across the four quarters.
5. Add Total retail NSA to Values and use Value Field Settings > Sum to compare the two channels by year.

The Q4 column stays near 30% every year (from 28.0% to 32.7%), while a flat year would give 25% per quarter. That one PivotTable already answers question 3.

Step 3: Yearly formulas, growth and CAGR

PivotTables are great for exploring; formulas are better for a report that must stay put. The By year sheet uses one SUMIFS per cell:

Total retail   =SUMIFS(Clean!$E$2:$E$108,Clean!$B$2:$B$108,$A2)
E-commerce     =SUMIFS(Clean!$F$2:$F$108,Clean!$B$2:$B$108,$A2)
Share          =C2/B2
Growth         =C3/C2-1
Q4 share       =SUMIFS(Clean!$F$2:$F$108,Clean!$B$2:$B$108,$A2,Clean!$C$2:$C$108,4)/C2
CAGR 2015-25   =(C27/C17)^(1/10)-1

CAGR (compound annual growth rate) is the steady yearly growth that would turn the 2015 value into the 2025 value: $331.5 billion became $1,233.7 billion, which is 14.0% a year. Total retail grew 4.9% a year over the same ten years.

Look at 2020: e-commerce jumped 42.1% in one year while total retail grew 3.0%. In 2021 total retail caught up (17.1%), and online growth has slowed since then, to 5.4% in 2025.

Step 4: Build the dashboard

1. Add a sheet called Dashboard, turn off gridlines (View > Gridlines) and put a title bar across the top.
2. Make six KPI cards with simple links such as ='By year'!J2, formatted as percentages.
3. Insert a line chart of the seasonally adjusted share (Clean!J1:J108) with the quarter labels as categories.
4. Insert a column chart of yearly e-commerce sales, a line chart comparing the two growth columns, and a column chart of the Q4 share.
5. Line the charts up in a 2 x 2 grid (hold Alt while dragging to snap to cells) and set Page Layout > Orientation > Landscape and Fit to 1 page.

The Dashboard sheet of the practice workbook (real data: U.S. Census Bureau).

Step 5: The recommendation

A good recommendation is short, uses the numbers and says what to do:

1. Plan for continued online growth, but not pandemic-era growth. E-commerce grew 14.0% a year over 2015-2025, but only 5.4% in 2025 (before inflation). A planning range of 5-10% a year matches the recent trend better than the ten-year average.
2. Staff and stock for Q4. Q4 brings about 30.4% of a year's online sales; in 2025 Q4 e-commerce was 21.8% higher than Q3 ($365.2 billion vs $299.7 billion). Book extra warehouse and support capacity from October.
3. Keep shifting budget online, step by step. The online share of US retail rose from 7.1% in 2015 to 16.4% in 2025 (not adjusted) and reached 17.1% in Q2 2026 (adjusted). It moves about one point a year, so a sudden switch is not needed.

What this data cannot tell you

• It is the whole US market, not your category or your company. A furniture store and a grocery store will see different numbers; the Census also publishes monthly retail data by kind of business if you need more detail.
• The dollars include inflation. To see real growth, divide by a price index such as the CPI.
• The newest quarter is preliminary and will be revised. Re-download the file before you present.