Tag Archives: excel

PivotTable Limits in Excel

Yesterday morning, I was creating a sweet pivot table to measure our development teams’ velocity over time. I had a track-everything spreadsheet that has all the User Stories and Tasks fromĀ our TFS Team Project. When I created my pivot table, I wasn’t getting all the values, though!

I did some quick Googling and quickly learned that Excel has limits to the amount of data that can be crunched in a pivot table. Who knew!?

I’m using Excel 2013, and I wasn’t able to find information about the exact limit. Earlier versions of Excel were limited to 32,000 fields across all rows and columns. The specification for Excel 2010 vaguely states that it’s “limited by available memory.” I’m not sure what that means, but I had 22 fields and over 4,000 rows, so I assumed that was my problem.

In order to test this theory, I created a new, smaller query with just the fields I needed for my pivot table. Sure enough, with the smaller data set, everything showed up. And I guess that’s the moral of this story: if you’re missing data from your pivot table, and you’ve got a pretty big data set, you’re probably exceeding the limits.

Advertisements

Excel Football Squares Pool Generator

The Super Bowl is right around the corner, and you know what that means: it’s time for some low-stakes office gambling! Yep, football squares. In case you aren’t familiar, the idea is that you blindly buy squares in a 10 by 10 grid. Once all the squares are sold, the rows and columns are randomly assigned values from 0 to 9. One team is assigned to the row values and the other to the column values. The last digit of each team’s score at the end of each quarter is then used to pick a square, and whoever owns that square wins some percentage of the proceeds.

What’s that, you say? Grids? Random numbers? This sounds like a job for Excel!

You can probably come up with a few different ways to do this, but I chose to generate two sets of ten random numbers. I then assign values to the rows and columns based on the row index of the 1st largest, 2nd largest, 3rd largest, etc. number in the list.

This is what my numbers grid looks like:

1 0.95708204 0 1 0.96026041 6
2 0.60117232 9 2 0.11468243 0
3 0.17740544 6 3 0.60526773 7
4 0.22298474 4 4 0.76106966 8
5 0.89427173 1 5 0.76476649 4
6 0.41933546 5 6 0.6840864 3
7 0.89535015 3 7 0.97857437 5
8 0.05144228 2 8 0.93037023 2
9 0.17105286 8 9 0.90558099 9
10 0.95489495 7 10 0.21003331 1

The first and fourth columns are just hand-entered values from 1 to 10. The second and fifth columns are just random numbers generated by =RAND(). The third and sixth columns are where the magic happens. The formula, shown below, finds the Nth largest value, where N is the hand-entered number from the first or fourth column, and then uses the MATCH function to return the index of that value in the grid. The indexes are 1-based, so I subtract 1 to ensure that my values range from 0 to 9. Here’s what the formula looks like:

=MATCH(LARGE(B$1:B$10,A1),B$1:B$10,0)-1

Now that I have two sets of random numbers, all I need to do is assign them to my squares grid. I did this just by adding a function to each cell (=C1, =C2, etc.). Bam, that’s all there is to it. No more drawing cards or pieces of paper out of a hat: just hit F9 to have Excel recalculate its formulas and generate the whole thing at once.

bar
foo 6 0 7 8 4 3 5 2 9 1
0
9
6
4
1
5
3
2
8
7

Calculated Fields in Excel Pivot Tables

Pivot tables in Excel are awesome. I love them. Drag, drop, slice, and dice data in seconds. Create charts and graphs. Amaze your friends. It’s all about the pivot tables, baby!

Occasionally, you need more than just the data, though. You need to know the difference between two numbers, or the percentage of one number relative to another. This is where Calculated Fields come into play. I haven’t had a huge need for them in my previous pivot tabling, so I never really knew about them. When I needed a calculated field, I would create a column with the calculation I needed in my source data, and that worked fine.

Today, though, I accidentally discovered Calculated Fields, and they are sweet. All you need to do to create a Calculated Field is click on the Fields, Items, & Sets menu in the pivot table ribbon, and select Calculated Fields…. This will open a new dialog box that allows you to create a named field generated from a formula you create from the already-available pivot table fields.

Once created, your Calculated Field is available in the Pivot Table Fields list for you to use as data in your pivot table!