A few years ago, we had a tutorial about how to build a pivot table, which you can see here. A major advantage of using Pivot Tables is that it’s very easy to make sense of large sets of data and draw summaries.
Last week, I received an email from the Penn State Extension which is working with a database that will help growers determine what pesticides are most effective to use for specific conditions or fruits. She has a huge list populated by the farmers for Apples for example where she is able to see different sets of information, here is a preview (as always, the full file is available at the end of this post):
The first question I was asked was the best way to get the number of times each pesticide had been used. My first answer was a very simple “sumif excel function” which I often use here.
As Sladjana let me know, that would work but it would require:
-Having the list of every single pesticide being used
-Would require updating the list when new ones were being added
Not ideal indeed. A macro could have certainly worked but it seemed unnecessary. I ended up going for a pivot table. How? I simply created it by clicking on the icon seen here:
Then I selected the data (including headers of course!) and was presented with this pivot table:
Then I dragged the column: “Pesticide Trade Name” into both the bottom left section and the bottom right section, which by default gives me the “count” of times it was used, see the result here:
It becomes very easy to update when the data changes, simply right click and select “refresh”
Easy enough? And it gives a complete picture that could become much more detailed depending on what is required/useful!