Excel Error Codes

Fix Excel Get Data from Web Dropping Authorization Header (HTTP 401)

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to resolve an issue where Excel's 'Get Data from Web' feature drops the custom Authorization header on the second HTTP request, leading to an authentication failure.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing data from a web API or service that requires a custom HTTP Authorization header via the Power Query Navigator.
Observed behavior: The first HTTP request sends the Authorization header correctly, but subsequent requests omit it, resulting in an HTTP 401 Unauthorized response. This workflow operates correctly in Excel 2016 but fails in newer versions.

Before you start

Verify your API credentials and test the endpoint with a third-party tool like Postman to ensure the server is actively accepting your custom Authorization headers before troubleshooting Excel.

Solution 1Recommended

Report the Authentication Transport Issue to the Excel Community

Since this is a known transport behavior regression specific to newer Excel Power Query builds, the most effective step is gathering diagnostic data and reporting it to Microsoft for a patch.

Newer versions of Excel handle HTTP requests through updated Power Query engines. These updates sometimes aggressively strip custom headers like 'Authorization' during Navigator previews for security or redirection reasons. Because the same workflow functions correctly in Excel 2016, submitting a detailed technical report helps the development team analyze the specific transport behavior causing the regression.

Document your Excel version

Open Excel, click on 'File', navigate to 'Account', and note down your exact build number and update channel.

Capture the request sequence

Use a web debugging proxy tool like Fiddler to record the exact sequence of HTTP requests made when you attempt to use the 'Get Data from Web' feature.

Identify the missing headers

Locate the second request in your proxy logs and visually confirm that the custom Authorization header is missing, resulting in the HTTP 401 Unauthorized response.

Submit the bug report

Navigate to the official Microsoft Excel Tech Community or use the 'Help > Feedback' option in Excel. Submit your findings, clearly including the Excel version, request sequence, authentication method, and the HTTP 401 response logs.

Workaround Availability: While waiting for a fix, some users bypass this by using Web.Contents in the Power Query Advanced Editor to explicitly define headers, or by utilizing standard Basic Authentication if the target API supports it.

Free Microsoft Office alternative

Switch to WPS Office for Reliable Spreadsheet Management

If Microsoft Excel's newer updates are causing compatibility bugs or breaking your established workflows, consider WPS Office. It provides a lightweight, highly compatible alternative for handling your daily spreadsheet tasks efficiently without unexpected feature regressions.

1. Download the installer: Visit the official WPS Office website and download the free installation package.
2. Install the software: Run the setup file and follow the quick on-screen instructions to install WPS Office on your device.
3. Open your workbooks: Launch WPS Spreadsheet and instantly open your existing Excel files with full format compatibility.

Free and lightweight complete office suiteHighly compatible with Microsoft Excel (.xlsx, .xls, .csv) file formatsFamiliar user interface allowing seamless migration without a steep learning curveStable performance for data management without disruptive background updates

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel Get Data from Web return an HTTP 401 Unauthorized error?

This error occurs because the destination web server requires authentication, but Excel's Power Query engine drops the custom Authorization header during subsequent connection requests in the Navigator, causing the server to reject access.

Does the Get Data from Web feature work differently in older Excel versions?

Yes, the transport behavior handling HTTP requests was updated in newer Excel builds. Workflows relying on custom Authorization headers often work perfectly in Excel 2016 but experience header drops in newer Microsoft 365 versions.

Can I bypass the Authorization header drop natively in Excel?

It can be challenging if the Navigator UI aggressively strips the header. Advanced users occasionally work around this by writing custom M code using Web.Contents in the Advanced Editor to force explicit header declarations, though this is not guaranteed for all endpoints.

Microsoft Office Error Codes