Modify The Data Options in Excel 365

Posted on

Are you looking for options on how to Import Data and Analysis in Excel 365 and cannot find them anywhere? The reason is that they have moved them in the latest version of Excel. They have their own location in the Data section of the Excel Options dialog box. In order to find out How To Modify The Data Options in Excel 365, just keep on reading this post.

Data Options View In Microsoft Excel 365

We must select the File tab, in order for us to move to Backstage View and then from the drop-down menu that appears we select the category Options from the left as shown in the image below.

File - Options

Once the Options command is selected the Excel Options dialog box appears as we can see below. From the left we select the category Data and then from the center of the dialog box where we can Change Options Related To Data Import And Data Analysis

Excel Options - Data

We then locate the Data Options area where underneath we have the following commands in order for us to modify or to activate and deactivate:

  • Make Changes To The Default Layout Of PivotTables:

By selecting this command, we can choose from multiple default layout options for new PivotTables.

  • Disable Undo For Large PivotTables Refresh Operations To Reduce Refresh Time
  • Disable Undo For PivotTables With At Least This Number Of Data Source Rows (In Thousands)

By selecting the Undo operations, we 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

By selecting this command, the Data Model integrates data from multiple tables, building a relations data source inside an Excel Workbook.

  • Disable Undo For Large Data Model Operations:
  • Disable Undo For Data Model Operations When The Model Is At Least This Large (In MB):

By selecting the Undo operations, we can select the number o 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:

By selecting this command, we enable the Data Analysis Add-ins, instead of using the Add-ins tab.

  • Disable Automatic Grouping Of Date/Time Columns In Pivot Tables:

By selecting this command, we disable the default of grouping date and time columns with plus (+) signs next to them.

Data Options Area in Excel Options

All we need to do is to activate or deactivate the check boxes at the left of the commands or modify the up and down arrows in order to change their defaults. Once we have done all the adjustment we press the Ok button which is located at the bottom right corner of the Excel Options dialog box, in order for them to take place and for us to return to our Workbook.

In my next post I will describe how to Turn On The Legacy Wizards In Microsoft Excel 365, command which is also available in the Data Options dialog box.

Below you can check out the video describing How To Modify the Data Options in Excel 365.

Don’t forget to Subscribe to My YouTube Channel.

YouTube Channel 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s