Modify Excel’s data import and analysis options, like PivotTable defaults, Data Model behavior, add-ins, performance settings, and access to legacy import wizards in the Data options dialog box.
You can also control how Excel automatically converts incoming data (such as numbers, dates, and formats) during import and analysis to better match your data needs.
Find these options by selecting File > Options > Data.Â
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
Power Query, available through the Data tab on the ribbon, provides more modern data connectors and transformational capabilities compared to the legacy data import wizards. However, there may still be times when you want to use one of these wizards to import your data. For example, when you want to save the data source login credentials as part of your workbook.
Security   Saving credentials is not recommended and may lead to security and privacy issues or compromised data.
Enable the legacy data import wizards
-
Select File > Options > Data.
-
Select one or more wizards to enable access from the Excel ribbon:
-
Access (Legacy)
-
Web (Legacy)
-
Text (Legacy)
-
SQL Server (Legacy)
-
OData Data Feed (Legacy)
-
XML Data Import (Legacy)
-
Data Connection Wizard (Legacy)
-
Microsoft Query (Legacy)
-
-
Close the workbook and then reopen it to see the activated wizards.
-
Select Data > Get Data > Legacy Wizards, and then select the wizard you want.
You can disable the wizards by repeating steps 1 through 3 and clearing the check boxes described in step 2.Â
Automatic Data Conversion
Enable all default data conversions below when entering, pasting, or loading text into Excel Clear this box to deselect and choose one or more of the following options:
-
Remove leading zeros and convert to a number Disable this option to store numerical data with leading zeros as text and retain the leading zeros instead of converting it to a number and losing the leading zeros. For example, 00123 remains as 00123 in text and isn't converted to the number 123.
-
Keep first 15 digits of long numbers and display in scientific notation Disable this option to store numerical data with 16 or more digits as text and retain all digits instead of only keeping the first 15 digits and converting it to a number that may be displayed in scientific notation. For example, 12345678901234567890 remains as 12345678901234567890 in text and isn't converted to the number 12345678901234500000 or displayed in scientific notation as 1.23457E+19.
-
Convert digits surrounding the letter "E" into a number in scientific notation Disable this option to store numerical data surrounding the letter "E" as text instead of converting it to a number in scientific notation. For example, 123E5 remains as 123E5 in text, and isn't converted to the number 1.23E+07 in scientific notation.
-
Convert continuous letters and numbers to a date Disable this option to store "date-like" values with consecutive letters and numbers as text, instead of converting to a date. For example, JAN1 remains as JAN1 n text and isn't converted to the date January 1. Data that includes spaces or other characters, such as JAN 1 or JAN-1 may still be treated as dates.
Additional options
When loading a .csv file or similar file, notify me of any automatic data conversions Disable this option to stop showing a dialog box indicating which data conversions are about to occur. By default, the dialog box is displayed. However, the dialog box also doesn't display if you disable all the above options.
