Yes, Excel 2016 has Power Query — but it’s not called Power Query anymore, and you won’t find it on the ribbon unless you know where to look (and even then, it’s tucked under a confusing label).
That confusion cost me three hours last Tuesday when I tried to merge supplier data from four CSV files for a procurement review. I kept clicking around the Data tab, expecting a ‘Get & Transform’ group like in Excel 365 — only to stare blankly at ‘From Text’, ‘From Web’, and a mysterious ‘Get Data’ dropdown that didn’t do what I thought it would.
The Problem
You’re working with messy, inconsistent source data — maybe weekly sales exports from different regional offices, each formatted slightly differently, with mismatched column names, extra header rows, or inconsistent date formats. You need to clean and combine them fast, but Excel 2016’s built-in tools feel clunky: copy-paste leads to errors, Text-to-Columns doesn’t handle delimiters consistently, and VLOOKUP fails when keys contain leading spaces you didn’t notice until row 847.
| Source File | Rows | Issues | Last Modified |
|---|---|---|---|
| APAC_Sales_Q1.csv | 1,284 | Header starts on row 3; 'Revenue' column labeled 'Rev ($)' in some rows | 2024-03-15 |
| EMEA_Sales_Q1.xlsx | 921 | Date column as text ('01/03/2024'); 'Region' spelled 'Región' in first 12 rows | 2024-03-14 |
| NA_Sales_Q1.csv | 1,552 | Extra blank column (C); currency values lack commas ($12450.75 → $12450.75) | 2024-03-16 |
| LATAM_Sales_Q1.xlsx | 673 | Sales Rep names in column D instead of B; 'Q1 Target' missing for 42 rows | 2024-03-13 |
| Global_Summary.xlsx | 5 | Merged manually — already outdated by 2 days | 2024-03-12 |
The Solution
Excel 2016 *does* include Power Query — Microsoft just renamed it ‘Get & Transform’, and buried it under the Data tab. Worse: it only appears if you’ve installed the free Power Query add-in for Excel 2016 — which most people don’t realize is required for full functionality (yes, even though it ships with the app).
Here’s how to activate and use it properly:
- Install the add-in (if missing): Go to File > Options > Add-ins > Manage ‘COM Add-ins’ > Go… > Check ‘Microsoft Power Query for Excel’ > OK. If it’s not listed, download and install it first — no restart needed.
- Enable the ribbon group: Right-click any ribbon tab > Customize the Ribbon > Under ‘Main Tabs’, check ‘Data’ > Expand it > Check ‘Get & Transform’. Click OK.
- Load your first file: Go to Data > Get Data > From File > From Text/CSV. Navigate to APAC_Sales_Q1.csv. Click ‘Import’ — not ‘Load’.
- Clean in Power Query Editor: In the new window, right-click the first column header > ‘Use First Row as Headers’. Then select column ‘Rev ($)’ > Transform tab > Rename to ‘Revenue’. Select all date columns > Transform > Data Type > Date. Click ‘Close & Load To…’ > Choose ‘Only Create Connection’.
- Repeat for other sources, then go to Data > Queries & Connections > Right-click each query > ‘Load To…’ > Select ‘Table’ and check ‘Add this data to the Data Model’. Now use PivotTables or DAX measures across all sources.
After applying these steps, your consolidated view looks clean and refreshable — no more manual rework every week.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | File > Options > Add-ins > COM Add-ins > Go… > Enable ‘Power Query for Excel’ | Add-in active; ‘Get & Transform’ appears on Data tab | Alt+F+T → I → G |
| 2 | Data > Get Data > From Text/CSV > APAC_Sales_Q1.csv > Import | Power Query Editor opens with raw file contents | Alt+A → T → I |
| 3 | Home tab > ‘Use First Row as Headers’ + Rename ‘Rev ($)’ → ‘Revenue’ | Headers standardized; column name consistent across queries | Ctrl+Shift+H (for headers) |
| 4 | Transform tab > Data Type > Date (on date column) > Close & Load To… > Only Create Connection | Query saved but not dumped into worksheet — ready for modeling | Alt+T → D → D → Alt+C → L → O |
Going Further
You can write M code directly — press Alt+Q in Power Query Editor to open Advanced Editor. Try pasting this to trim whitespace and standardize region names across all files:
= Table.TransformColumns(Source,{{"Region", each Text.Trim(_), type text}})
Also: Power Query in Excel 2016 supports parameterized queries. Create a cell (say, F1) with a folder path like C:\Reports\Q1_2024\, then reference it in M code using Excel.CurrentWorkbook(){[Name="PathParam"]}[Content]{0}[Column1]. That way, changing one cell updates all your imports.
Surprising tip: You can use Power Query to pull live data from SharePoint lists — even in Excel 2016 — but only if the list URL ends in /AllItems.aspx and you replace it with /Lists/[ListName]/AllItems.aspx before pasting into ‘From Web’.
When NOT to Use This
Don’t reach for Power Query if your task takes less than 90 seconds to do manually — like fixing one typo in cell B7. Over-engineering wastes time.
Avoid it entirely for real-time dashboards requiring sub-second refresh. Power Query in Excel 2016 caps at ~2 minutes per refresh, and background refresh isn’t reliable — you’ll see ‘Loading…’ frozen in the status bar while users wait.
Also skip it if your source is a password-protected .xlsx file. Excel 2016’s Power Query can’t auto-enter passwords — you’ll get an auth error every time. Either remove protection or pre-process the file with a macro before loading.
And never use it to parse PDFs. Yes, there are workarounds involving Adobe Acrobat scripting — but they break constantly and require admin rights. Just export the PDF to Excel first, even if it’s ugly.
Keyboard Shortcuts
| Shortcut | Action | Notes |
|---|---|---|
| Alt+A → T → I | Open ‘From Text/CSV’ dialog | Fastest way to start importing |
| Ctrl+Shift+H | Promote first row to headers | Works even if row contains blanks |
| Alt+Q | Open Advanced Editor | Edit M code directly — safer than recording |
| Alt+F5 | Refresh all queries | Bypasses prompt if ‘Enable background refresh’ is unchecked |
| Alt+D → Q → R | Open ‘Queries & Connections’ pane | Essential for managing dependencies |
