Data import and analysis options

Beginning with Excel 2016 for Office 365 subscribers, Data import and analysis options have been moved to their own Data section in the Excel Options dialog box. You can reach these options through File > Options > Data. In earlier versions of Excel, the Data tab can be found in File > Options > Advanced.

Data options have been moved from File > Options > Advanced section to a new tab called Data under File > Options.

Note: This feature is only available if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

Data Options

  • Make changes to the default layout for Pivot Tables.

    You can choose from multiple default layout options for new PivotTables. For instance, you can choose to always create a new PivotTable in Tabular Form versus Compact, or turn off Autofit column widths on update.

  • Disable undo for large PivotTable refresh operations to reduce refresh time.

    If you choose to disable undo, you can select the number of rows as a threshold for when to disable it. The default is 300,000 rows.

  • Prefer the Excel Data Model when creating PivotTables, Query Tables and Data Connections.

    The Data Model integrates data from multiple tables, effectively building a relational data source inside an Excel workbook.

  • Disable undo for large Data Model operations.

    If you choose to disable undo, you can select the number of megabytes in file size as a threshold for when to disable it. The default is 8 Mb.

  • Enable Data Analysis add-ins: Power Pivot, Power View and 3D Maps.

    Enable Data Analysis add-ins here instead of through the Add-ins tab.

  • Disable automatic grouping of Date/Time columns in PivotTables.

    By default, date and time columns get grouped with + signs next to them. This setting will disable that default.

Show legacy data import wizards

The new Get & Transform experience, as found through the Data tab on the ribbon, is superior in terms of data connectors and transformational capabilities compared to legacy wizards. However, there may still be times when you want to use one of the old wizards to import your data. For example, when you want to save the data source login credentials as part of your workbook. Fortunately, those wizards can be turned on in File > Options > Data.

Get & Transform Restore Legacy options

Once you’ve selected your legacy options, you can expose them from any worksheet, by clicking the Data tab, then > Get Data > Legacy Wizards > launch one of the legacy data import wizards you added from Data import options:

Get & Transform Legacy Menu options

You can uncheck those options at any time to hide them from the Get & Transform menu.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Set PivotTable default layout options 

Create a Data Model in Excel

Unified Get & Transform experience

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×