I purchased the office home & business 2016 for PC version. The idea was to learn features like power pivot/Power queryl. but to my dismay this version of the excel does not have this feature in built. Isn't this supposed to be a standard feature

Make a setting to allow dates to display un-grouped by default. Currently when you add dates to the pivot table fields it creates the quarter, month, year deal and you have to right click and ungroup. I see the usefulness of both ways but ungrouped is used far more by some users.

Refresh All for Pivots and data connections should have a way to set up the order - at least how it progresses from tab to tab, or from top to down on a sheet. Right now, it seems like a black hole on which pivot/tab is getting updated first and which one is last if I have a workbook with 10 tabs and close to 30 pivots and few data connections. I do not see a way to either set an order or find the order in which Refresh All is working.

In the current version all the folders are open when you launch PQ. It would be useful if PQ would remember their stat at the moment of last closing PQ window.

There does not seem to be in power pivot any way to access a sheet directly. There is the Excel.CurrentWorkbook function, but that does not give sheet details and these are needed if, for example, there are a number of identically named tables but on different sheets.

Furthermore links to Excel sheets are broken if the user changes the sheet name and there is no way in Excel of trapping this (no RenamedSheet event is exposed).

Combined together this makes accessing Excel data in Power Pivot problematical.

Power pivot is great but its integration with Excel is poor. it would be good if we could link the output from Power Pivot directly to be read into a VBA array rather than having to output it to a table first then read the table. Not least because there doesn't appear to be an easy way to ensure that query has finished running before the VBA code continues.

The transform File groups created by binary transformation are for the most part never touched by the user and yet have been implemented in the graphic query hierarchy to create nodes at the top - couldn't be more in the way and hamper productivity more - make them essentially invisible by putting them in their own top tab next to Queries and COnnecitons

What I would like to see in Excel is this: When we create a PivotTable, and double-click an item it opens a new tab, it could be a window so we can see the details, and when we want, we just close the window , the way it is today is very annoying, have to be deleting the worksheets that are created

In Power BI we can easily create quick measures: https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/
This makes it so much easier for newbies or occasional users to adopt the new technologies, so please add in Excel as well.

With two or more value fields on a pivot table, the auto-generated "Values" field gets added to the rows or columns of the pivot table, and acts somewhat like other row/column fields. However, it would be great if the user could expand/collapse/filter the Values field. Collapse would help to provide asymmetric reporting and would also allow the user to effectively put a Grand Total on the left side of the pivot table by collapsing the Values field for that particular item.

I see a request already to add the ability to run medians in pivot tables--would love to be able to do this, and also to run other percentiles.

Currently, the "roles" functionality, which enables row-level security, is available in tabular data models and Power BI desktop. I would like to use this feature in Power Pivot. To elaborate, I'd like to load a full data set, and have Power Pivot filter the data model shown to the current user, based on their security profile. I can set this fairly quickly in Power BI Desktop, not sure why it isn't an option for Power Pivot.

Expand/Collapse function in pivot charts for the 'Legend' (Pivot Column Group)

I need to disable/enable frequently for reporting and it is frustrating to dig through advanced options. After I toggle the option I have to copy my data to a new workbook for the setting to take effect. I should be able to turn this feature on and off within the pivot table ribbon, without opening a new workbook.

Allow for a quick toggle to quickly show the index number next to the column letter. When selected, the columns would read A(1), B(2), C(3)...etc This would allow us to quickly reference what the index should be for the pivot table. Then we can turn the toggle off and it goes back to normal A, B, C. Thanks!

On pivot table, a new format can be include to show a report in column and row.
For eg: when a pivot report prepared for country/state wise, eg. India should be in Merge-center & its states should be in below row;

India
TN KER KAR AP