How to Enable Missing Excel Import Wizards

The traditional Excel import wizard has been disabled in newer versions of Excel by Microsoft in favor of having users utilize Power Query to import data and transform it prior to loading it into an Excel workbook. We’ll explain what the differences are and how to enable the traditional interface that many are used to seeing.

Excel has evolved greatly over the last several years, with Microsoft pushing to have more of their software work together more easily and unifying a number of interface elements across different software platforms within the Microsoft ecosystem. A big part of this has involved hiding or eliminating buttons that Microsoft’s telemetry data tells them are not frequently used and pushing the Power Query interface out to a number of different products.

Let’s jump in!

How to Import Text, CSV and Files in Modern Excel

To import files in recent versions of Microsoft Excel, go to the Data Tab on the Excel Ribbon, and select Get Data or use one of the Get & Transform quick buttons. A dialog box will appear to choose a file and launch the Power Query Editor.

One of the major benefits of using Get & Transform features is the number of connection types that are supported. Power Query enables Excel to import individual files or folders of files while also allow you to connect to Power BI datasets, SQL databases, import folders of files at one time, and much more.

Using the Get Data section of Excel Get & Transform to import data with Power Query

Once you make a selection from this dropdown you’ll have an option to load or transform the incoming data. Once loaded into Power Query you’ll have to de-limit or extract the text that you want to work with.

What is the Traditional Excel Import Wizard?

Anyone that’s worked in Excel for a number of years will be intimately familiar with the traditional Excel import wizard. It’s a mostly grey dialog box that allows you to split columns based on delimiter, fixed width, set the row to start importing, identify headers, additional qualifiers etc.

Screenshot of Excel's old file import wizard that is missing in newer versions of Excel

Unfortunately, Microsoft decided to turn the option off by default in newer versions of Excel. Here’s how to turn it back on.

How to Enable the Traditional Excel Import Wizard

The classic legacy data import wizard for text, csv, and other data types can be turned on in Excel by going to File > Options > Data > Show Legacy Data Import Wizards. Once enabled, you can use the Get Data dropdown on the Get & Transform menu to select Legacy Wizards.

Each of the different legacy import wizards can be enabled or disabled individually. There is a section under Show Legacy Data Import Wizards to check box each one that you want to enable, as shown in the screenshot below.

The option menu to enable Excel's missing import wizard

After they have been enabled, navigate to the Data section of the Excel Ribbon, and a new section will be available under Get Data, Legacy Wizards.

The legacy wizards section of Get & Transform once legacy wizards are enabled to use the old missing import wizard

Once selected this will launch the traditional import wizard that’s missing in the most recent versions of Excel.

Excel Import Wizard vs. Power Query

We find it odd that Microsoft essentially hid one of the most useful and frequently used features in Microsoft Excel without much notification as many people don’t utilize the capabilities of Microsoft Power Query. In many instances, such as working with fixed width delimiting in Excel, the import wizard is often more straight-forward and easier to use than Power Query.

However, people that already utilize Power Query may find this change a non-event. Power Query is not only the backbone of data transformation in Excel but also underpins Microsoft Power BI, Microsoft Data Wrangler, and Microsoft Data Flows Gen 2 making it a very widely used interface across data science, data engineering, and business intelligence.

Importing Files with Power Query

To import a CSV or Text file in Power Query, go to the Data tab of Excel and select Get File from the Get & Transform Section. Choose your file and a dialog box will appear to preview the first records in the file, choose a delimiter, and select whether to load the data into Excel or transform it further.

The modern file import experience in Excel utilizes Power Query

When you choose to transform it further, you will be brought into the Power Query editor. You can delimit files based on a chanter, by number of character, position or text patterns.

To split text to columns in Power Query, select the column you want to split and then use the Split Column button on the Transform section of the Power Query Ribbon to define a method to break a single column apart.

How to delimit or parse data in Power Query after importing a file

After the method is applied, you will have multiple columns. Press Close & Load from the Home Tab to exit the Power Query editor and load the data into an Excel worksheet.

After getting used to Power Query, you may find that it has a number of useful advanced features that go behind the traditional import wizard.

Learning More About Excel Power Query

Excel Power Query is an incredibly useful set of tools. It helps people automate Excel processes, work with datasets too big for normal Excel and has a lot of one step buttons or wizards that replace the need to write complex Excel formulas.

Check out our guide to getting started with Power Query for Excel, or take a look at the video below from Microsoft that highlights a number of the Excel Power Query Editor features.

Many people who invest time in learning Power Query will find it to be a very versatile, time saving and useful addition to their Excel toolbox.

Conclusion

When looking for the Excel file Import Wizard, it’s missing in newer versions of Excel because Microsoft purposely disabled it as the functionality largely overlaps with what is available in Excel Power Query. Unfortunately, there are a number of people who don’t use Power Query or familiar with it’s capabilities.

To enable the old file import wizard for Excel, go to File, Options, Data, Show Legacy Wizards. Then when you go to the Get Data button on the Data tab there will be a legacy option to use the old import method to import and delimit files.

Scroll to Top