Last week, we had a giveaway for the new Excel Tips Workbook from Vertex42. Thanks to Jon Wittwer for donating the prizes, and here are the winners:

- Tim, with comment 5
- Ute-S, with comment 34

Congratulations, and I will email you later today, to arrange sending your copy of the file.

### PivotPower Premium

We’ve been fortunate to have some great prizes for the summer giveaways, and I really appreciate the generosity of my Excel colleagues.

Now it’s my turn to provide the prizes. This week, you’ll have a chance to win a copy of my Excel add-in for working with pivot tables – PivotPower Premium (Ribbon Version). There will be 2 winners, so be sure to enter!

The add-in is easy to install, and is designed for Excel 2007 and later (Windows only). One of its time-saving features is Set Defaults, which lets you store your favourite pivot table settings. Then, select any pivot table, click Apply Defaults, and all those settings are applied.

### More Tools for Pivot Table Work

There are many more tools in the PivotPower Premium add-in, including number formats, and a button that changes all the fields to Sum. That’s handy when Excel decides that half of your fields should be “Count of”.

There are even a few tools for changing your workbook and worksheet settings, so your pivot tables can look their best. The screen shot below shows the Ribbon in Excel 2010, and the one above is in Excel 2013.

### Enter the Giveaway

I’m picking 2 winners for this giveaway. If you’d like a chance to win a copy, please read the rules, and then make a comment below.

- In your comment,
**tell me one thing that you love about pivot tables, AND/OR one thing that pivot tables should do better**. - Include your email address, so I can contact you if you win. Your contact information won’t be publicly visible, and it won’t be used for any other mailings.
- The deadline is Wednesday, August 21st, 2013, at 12 noon Eastern Daylight Time.
- One entry per person.
- The 2 winners will be announced on Thursday, August 22nd, 2013.
- Each winner will have 24 hours to claim the prize, and if not claimed, another name will be selected.

__________________________

TX for these opportunities to win these books and add-ins. All of them are amazing!

What I love about Pivot Tables is their efficiency and flexibility. Everything is automatic, from sorting, grouping and calculating. Then the ability to swop data into different positions to view different combo's is a joy! One of my ultimate favourite things is the grouping options on dates. Give a pivot a column of dates or numbers and it auto-groups it into various intervals with summaries! Awesome!

I have nothing to say about what Pivots can do better...they are already so refined and well-tuned!

TX

I love almost everything about PivotTables, but what would be really awesome if pivot tables could be incorporated in Excel data tables. I'd love to be able to add additional columns to the right of the pivot table, perform calculations based on the data in the pivot table and have those additional columns automatically resize with the pivot table and move to the left or right if the pivot table expands or contracts.

PivotTable has already saved tremendous amount of time managing large data-sets.

Although, I would like to see improvement in "Calculated Field" & "Calculated Item" so that I able to use other Excel functions, and also faster calculation (in secs) when dealing with large data.

The one feature I like & learned through Contexures.com is PivotTable's functionality to grab data from multiple sheets. That was rocking!

Using pivot tables is a great way to create quick and easy summaries of data. They are very easy to use and are extremely flexible. Thanks so much for the opportunities to win these great tools!

Sean

I love how flexible my data is with a pivot table - they allow me to recalculate in seconds what could take hours of fussing otherwise.

One change I'd like to have - an 'easy' way to convert a pivot table into a non-pivotable output. Sometimes I need to take the results of a pivot table and give them to someone else who will be using the data in such a way that the pivot table won't work for them. Copy/Paste Special does work to get the results of the table into a new spreadsheet - but I don't get to keep the nice formatting set I had from the pivot table, so I still have to do additional work to make it look decent.

@Heather R:

I used to face this problem too, so I now copy-paste the pivot table to get 'values only', in this sequence :

1. Copy the pivot table

2. In the new sheet, PasteSpecial > Column Widths

3. PasteSpecial > Formats

4. PasteSpecial > Values

You could also put steps 2, 3 and 4 into a simple macro to make it a 1-click process.

With Selection

.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

.PasteSpecial Paste:=xlPasteValues

.PasteSpecial Paste:=xlPasteFormats

End With

Application.CutCopyMode = False

Hope this helps :)

I've been using PivotTables since 1995 (Excel 5.x I think), and found them really useful for summarizing information and playing around with various views of the same data.

I think the best part about pivot tables is their core ability to reduce large amounts of data into an interactive table for easy analysis.

I still remember an amusing incident many years ago, when I had shown a pivot table to a media research person who had limited knowledge of Excel. He kept asking me : "Where are the formulas ? Show me the formulas ! How did you get the summary figures without using formulas ?" :)

I offered to show him how to make a PivotTable in a couple of minutes, but he was too busy to learn "something that looked so complex" !

What I'd like to see pivot tables do better ? - perhaps allowing to position a text column (displayed as text !) within the data area for easier reading of the numbers.

PivotTables allow for such complex calculations without writing any code.

Creating calculatedfields can be a bit tedious.

I have seen a demo of a power pivot one day. This is a powerful tool.

I like that pivot tables are powerful, so much so that 20 years or so after my first use of pivot tables, I still haven't made the time to investigate them fully.

I don't like that I don't find it particularly easy to get the necessary help tips in various screens related to pivot tables as I'm building or modifying them.

Although I do like using Pivot Tables within Excel, the thing that has proven most useful for me is using them to access data in an Access database and other external files. Data beyond the usual size limit, even in the newer Excel's, can be accessed. Initial access can be slow, but subsequent manipulations are reasonable. Power Pivot potentially takes it much further, but I haven't had to use it yet.

The thing that I like the most about Pivot Tables is the quantity of data that can be summarized in just a few clicks, and also the calculated fields. I can't remind any thing about Pivot Tables that I think should be improved.

Thanks,

Arturo López.

Guadalajara, México.

The thing that I like most about pivot tables is the ability to very quickly modify the entire table by tweaking just one setting (or several settings). What would have taken half an hour (or more) with a standard table can very easily be accomplished in a matter of seconds with a pivot table. It's a great tool for data manipulation.

The best thing about Pivot tables, is that they make me look a lot cleverer than I really.

I love Pivot tables and found half the time when I add more for display (not calculate) I convert them so they are not nested under go across and set it so the column repeats down.

In many cases I am counting as much as summing. I think some forget about counting. I don't even like to make a pivot table with out a slicer next to it.

One feature I would like to see is when an Excel Pivot table is linked to an Access query is to have the ability to alter the query directly from the Pivot.

I love how quickly and flexible I can analzye data with a pivot table. As a beginner I dont know, what Excel pivot table should doe better

Its easier to create relationships between tables and manage them easily.

One of the best features is using the power of pivot tables to filter down really complex data ranges and then outputting just those results to a separate worksheet tab that can then be easily emailed or exported for someone else to review or action depending on content, great for simplifying large database results.

Something that would be a huge benefit in my organisation would be if Pivot Tables could be used on shared workbooks. It seems they can not be changed and will not update when shared.

Pivot Tables They enable me to create new views of worksheet data in seconds. Thousands of pieces of information swing into one place, revealing the meanings behind the data. And now after PowerPivot... it rocks !!!

Definatly the formula =GETPIVOTDATA its simply powerfull..

Most wanted pivot fuction I miss is percentage calc in pivot subtotals =100%

Without having to create help columns with row percentage calculation.

And It should work dynamic as rest of the page/row/columns Dynamics'

Example below:

Region Fruit Sum of Amount Sum of % of Region

Central Apples 10 28.57%

Oranges 25 71.43%

Central Total 35 100.00%

South Bananas 19 36.54%

Oranges 33 63.46%

South Total 52 100.00%

Workaround used today is below dataset (help column = % of region)

Fruit Region Amount % of Region

Apples Central 10 0.285714286

Oranges Central 25 0.714285714

Bananas South 19 0.365384615

Oranges South 33 0.634615385

I LOVE pivot tables. I learn something new about creating pivot tables almost every day. This is a powerful tool. I would love to learn how to use information from multiple tables into one pivot table. I just learned how to use slicers with pivot tables and I'm sure there's much much more for me to learn. Don't really know what they could do better as I do not know everything about them.

What I like is ease in which Pivot Table can be filtered or data presented differently. I love when boss shows me print out he has and asks "now how did I get it to look like this?" He is actually very good about asking critical questions that can often be answered by Pivot Table. Now he does not need me.

one thing that I love about pivot tables is that for a complex data, it generates hundreds of sheets with its pivot table based on the page field criteria which the navigation is pivottabel tools > options then "Show Report Filter Pages"

one thing that pivot tables should do better is the filtering option. because for novice users they always find it difficult to put a filter on the pivot table, becuase when you place the cursur in pivot the filter option gets grayed out and then only way to then activate the filter option is to click to the column next to the pivot table area and then filter works.

this looks a simple issue, but lots of users run around until they find this trick.

The ability to pull so much data from a database and summarize the data so quickly when the number of records will easily exceed the number of rows Excel can hold is a huge benefit.

One thing I wish Pivot tables would allow is to do vlookups to other data within the workbook. There are many times when I have multiple pivot tables in one workbook and have to summarize pull that data together on one tab and do calculations.

Pivot tables - don't understand them - no idea how they work, have a faint clue of how they can help me, so - seems like I need to start digging into it considering all of the data I manulipate! As far as improving? You no sey? maybe after I get into them.

Pivot tables have changed the way I think about data and data structure. I am ready to move to the PowerPivot step but just haven't gotten a chance to put in the study time yet. One thing that bothers me about Pivot Tables in the newer Excel versions is that I learned them in Excel 2003 and so I always go into the options to change it back to the 2003 layout, with the ability to drag and drop items to move them around. I don't understand why the newer way is better and don't like the way the data lays out visually. I should note that I have Excel 2010, so I don't know what changes were made in 2013.

Pivot tables are a great way to quickly summarize data.

It can be tedious retaining formatting.

1. Pivot Tables allows me to organise (for classifications, groups and patterns) and present multidimensional data/information.

2. I want PivotTables to add value in the handling of increased number of rows/columns combination per table.

I need these tips and tools to perform better in Excel. I wish myself good luck.

Thanks for your entries, and the deadline has now passed.

The winners will be announced tomorrow, Thursday, August 22, 2013.

[...] Heather R, with comment 5 [...]

I tried to teach a collegue about pivot tables, but he said he was too busy, I responded that he too busy because he was so inefficient.