Shrink Large Excel Datasets With Pivot Tables

When you need to analyze last year’s sales data and it’s almost 1200, scrolling row by row doesn’t work. This is where Excel pivot tables for large datasets shine. You can turn a big sheet into a few clear summaries in minutes, without changing your original data.

Below is the same build, step by step, using the exact fields shown (Locale, Amount (USD), Date, Description), and the same quick moves that keep the results readable.

Build the pivot table once, then swap views fast

The goal is simple: “We’re not gonna do that manually by scrolling. We’re gonna use a pivot table.” Start by inserting the pivot table, then use the field list on the right to build each view.

1) Insert a PivotTable on a new sheet

1. Click any cell inside your dataset. As said in the walkthrough, “Select a cell from anywhere inside of our data set.”
2. Go to Insert and choose PivotTable.
3. In the dialog, Excel will select the full range for you. Choose New Worksheet so it’s clean and separate.
4. Click OK. Excel inserts a new sheet with a blank pivot table.
5. Look to the right for the builder area (the PivotTable Fields pane). This is where “we’re going to build this pivot table.”

That right-side pane is your control center. You’ll use it to drop fields into Rows, Columns, and Values to answer different questions without redoing the setup.

View 1 and View 2: sales by country, then sales by month

You can answer common sales questions by swapping which field sits in Rows while keeping the dollar amount in Values. This is a big reason Excel pivot tables for large datasets save so much time.

2) Show sales totals by country (Locale)

1. In the field list, select Locale. Excel places it in Rows.
2. Select Amount (USD). Excel places it in Values and totals it.
3. You now have “a pivot table with our country and our amount.”

This view is the fastest way to see which countries drive revenue. It’s also a quick check for gaps, like a country that has far less total than you expected.

3) Switch to month totals using Date

1. Uncheck Locale to remove the country view.
2. Keep Amount (USD) in Values.
3. Add Date (Excel will group it). You may see multiple time levels show up.
4. As noted, “It puts days and months.” Remove Days.
5. Also remove Date (the full date level) so “it just gives us the month and the dollar amount.”

This month view is the clean version you want for trends. It keeps the table short and readable, which matters when you’re working with Excel pivot tables for large datasets and don’t want a long list of daily entries.

View 3 and View 4: top-selling items, sorted, then split by country

Once you understand totals by country and month, the next question is usually product performance. The transcript’s flow is: “Now we wanna see how many we’ve sold of each item,” then sort it, then break it down by country.

4) Show sales by item (Description)

1. Remove the month setup from Rows (take out the date or month grouping you added).
2. Keep Amount (USD) in Values.
3. Add Description to Rows.
4. You’ll now see “our sales data for each item.”

At this point, the list is usually alphabetical because it’s based on the Description field. That’s fine for finding an item, but not for spotting the real top sellers.

5) Sort by dollar amount (Largest to Smallest)

1. Click any cell that contains a dollar amount in the pivot table.
2. Right-click, choose Sort, then select Largest to Smallest.
3. As stated, “Then it’s gonna put them in the order of the most sold.”

This step changes what you notice first. Alphabetical sorting hides what matters. Sorting by Amount pushes your highest revenue items to the top.

6) Break item sales down by country (Locale in Columns)

1. Add Locale again. Excel will “automatically put it in the rows,” which can get hard to read.
2. Grab Locale in the field layout and drag it from Rows to Columns.
3. Now you have “the item that was sold, and then it’s broke down by country.”

This final layout is a simple matrix: items down the left, countries across the top, and dollar totals in the middle. It’s one of the most useful patterns for Excel pivot tables for large datasets because it shows both product mix and regional differences in a single view.

Conclusion

Twelve hundred rows can feel like a wall of numbers, but a pivot table turns it into a few clear answers. Use Rows to pick the lens (country, month, item), keep Amount (USD) in Values, then sort “Largest to Smallest” to surface what matters. When you’re ready for deeper comparisons, drag Locale to Columns and keep the table readable. Keep practicing with Excel pivot tables for large datasets, and the next big report won’t slow you down.

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.