by contextures.com
Halloween is a couple of days away, and you’ll see many frightening things that night. Keep an eye out for vampires, witches, ghosts and monsters!
Something else that scares some people is a pivot table. Even though it’s a beautiful thing, a pivot table can be scary if you’re not sure how to handle one.
Many years, and thousands of pivot tables ago, I wrote a couple of articles for Jon Peltier’s Excel charting website.
Jon has been updating his website, and asked me to write new versions of those articles. He has just published them, and you can follow the links below, to read them.
As much as I love Excel pivot tables, there are a few annoying problems that you’ll run into, while working with them. I made a list of my top 5 annoyances, and you might have other problems to add to the list.
Thank you for following my pivot table tips, here on the Excel Pivot Tables Blog. I appreciate it!
There are many more tips and tutorials on my Contextures website, and I’ve just finished setting up a Pivot Table index, so you can find everything.
If you’ve bought a copy of my PivotPower Premium add-in, I sent out an update email last week, so you can download the latest version.
There are a few new features, that I hope you will find useful. A big thank you to my lovely customers, who suggested these new features!
Last week, I did a Pivot Table presentation, and someone asked why you get an absolute reference, if you try to link to a pivot table cell. For example, in the screen shot below, I typed an equal sign in cell E4, then clicked on cell C4, which has the quantity for the Bars category.
That created a GETPIVOTDATA formula, instead of a simple reference to cell C4.
Continue reading “Link to Pivot Table Creates Absolute Reference”
After you create a pivot table, you might add new data, or change the existing data. When you refresh the pivot table, it might not show all the new records, or pick up the changes.
To find the problem, you can check the pivot table’s data source. It might not include all the rows, and you’ll have to adjust it.
Continue reading “Find the Source Data for Your Pivot Table”
When you create a pivot table it groups the items from your data, and calculates a total for each group. In the screen shot below, you can see the total sales for each category, in each city in the West region.
There is a new feature in Excel 2013, that makes it even easier to get started with pivot tables.
Just select a cell in your source data table, and on the Ribbon’s Insert tab, click Recommended PivotTables.
Sometimes you have to move a pivot table, after you’ve created it. That’s not too difficult if the pivot table is small – just select all the cells, cut them, and paste in a new location.
With a very large pivot table, it’s a bit harder – you have to scroll through the worksheet, and try to select all the cells, including any report filters.
