Pivot Tables are the most powerful tool for analyzing data in Excel. With Pivot Tables, you can uncover discreet relationships. They allow you to compare variables using filters and functions. Simply put, they make reading your data quick and easy. That saves you time and money.
You might have a basic understanding of pivot tables, but how do they work? How can you make one? How can you use pivot tables to speed up your business?
You’ll learn all that and more.
The best pivot tables have a good data source. Make sure your spreadsheet is clean and clear. It’s also important that you have an objective in mind before you start. What information do you want to understand? Here’s a short list of things to do beforehand.
I’m using this data set from Excel Campus. It contains sales data for a wholesale food and beverage company. I want to see how each sales team-member is performing, but I want that information separated by the state they made the sale in. That will show me if there are markets where we’re underperforming.
Making a pivot table is as simple as clicking a button. Getting value and insights is the real challenge. Here is a step-by-step guide to creating your pivot table.
Those are the very basics of creating and using pivot tables in Excel, but there are a few other handy tricks that help make pivot tables the most powerful analytical tool in Excel.
These useful tricks will help clarify your analysis. Hopefully, we’ve provided enough information to let you experiment with pivot tables on your own. Find out what works best for your business and start using pivot tables in your decision-making process.
When you place a variable in the value field of your pivot table, it automatically sums the values for that variable. If you want to perform a different summary function, you can. All you have to do is click your variable and select value field settings. You can then choose from several options including count, average, and max.
In my example, if I wanted to see how many sales each salesperson made, and not how much revenue they earned, I could switch the value field setting to count. Now my table shows each salesperson and the number of sales they made in each state.
While Jan Kotas had the lowest overall revenue, Robert Zare’s 3 sales in Utah were the fewest for any salesperson, and Nancy’s 2 sales in Idaho were the fewest in any state. Now I can see we could invest more in Idaho and Utah because the few sales made were enough to make decent gains. Also, we want to leave Colorado where we made the least revenue and only four total sales.
Another great way to make your data more readable is the sort by value feature. You can access the sort by value dialogue box by clicking “Sort by Value…” in the drop-down menu next to one of your variables.
Then you can select a variable from your values field to sort by. In my example, you can see that Nancy Freehafer made the most sales, and New York and Illinois are tied for the state with the most sales.
At Sheetgo, I wrote for their blog, website and product. To see more of the writing I did while at Sheetgo, visit my author page. You can also check out some of the ghost writing I did, this high ranking blog on the Importrange function, for example, or this piece on Google Sheets templates.