Is it possible to show both the sum of amount and percentage of the total?
Yes, you can show the same amount summarised in multiple ways. Just drag the field to the “Values” area multiple times and then set up each individually.
I would like to know how to use the "show report filter pages".
I demonstrated this live in the webinar, but for a more full account of the process, check out this recent Excel Tip of the Week post.
Can you set a default format for numbers so that, by default, my pivots have a usable format?
While you can change the default layout of PivotTables from the Options => Data menu, there is unfortunately no option for default number formatting. You would have to create a VBA macro to make this easier.
Should you always change a range of data into a table?
I think it’s always worth doing – by creating a Table you make it more likely that Excel will notice new data and add it to your pivot, which reduces the opportunities for error. It’s also mandatory if you’re working with Power Pivot.
How did you fix the blank issue on the TB? If there is a blank cell within a data - sometimes the pivot table doesn't work? How do I get around this please?
Sometimes Excel can think that apparently empty cells contain a null character. You can diagnose this not only if Excel doesn’t want to work with your values, but also because when you use Ctrl + arrow keys Excel will skip over the whole range as if all the cells were filled.
To fix it, use filters to select only the rows that should be empty, then press Delete to properly clear out the cells and leave them truly blank.
I’ve heard about ‘Power Pivot’ but my work Office suite is only 2013 and there is no mention of ‘Power’ add-ins. Separately however, I’ve recently been provided with MS Power BI (Pro) – has this any relevance to how I can use the Excel 2013 or are they totally separate programs?
Power Pivot is available for the most common versions of Excel 2013, but needs to be enabled.
Power BI is a separate program, but it is based on the same data manipulation language as the Power Query add-in for Excel, and started life as another Excel extension.
Using Power Pivot can you create one pivot from two different spreadsheets? i.e. if I have one spreadsheet containing forecast data and another separate spreadsheet containing budget data. Is it possible to create one summary pivot table that allows to compare forecast against budget data?
Yes, you could use Power Pivot to help with this – perhaps using Power Query to grab the data in the external file(s) and add it to the data model first. Note that you might not always be able to get up-to-date data from closed files, however.
Does GETPIVOTDATA work with a Power Pivot?
Yes, it works fine – although the syntax inside the automatically-written GETPIVOTDATA function will look a bit different. You can even get Measures directly into formula cells without creating a pivot if you want, using the Cube functions such as CUBEMEMBER.
How do you update Power Pivot with new updated data tables?
You can refresh these from within the Power Pivot window.
Is there a forum on the community site upon which I can ask a question?
We are working on bringing back commenting currently, but you can always send your questions to excel@icaew.com.
Join the Excel Community
Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join.
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
