Power Query does not set Excel’s display number formats. It assigns data types—such as whole number, decimal number, date, or text—while Excel applies display formats after the query results are loaded. Set the correct type in Power Query, then format the resulting worksheet column in Excel if you need currency symbols, percentages, commas, or a particular date appearance.
Data type and number format are different
A data type describes what a value is and how Power Query should handle it. A number format controls how Excel displays a value in a cell. For example, Power Query can load the numeric value 0.25 as a decimal number; Excel can then display it as 25% without changing the underlying value. Similarly, a value such as 1234.5 can remain numeric while Excel displays it as $1,234.50.
This distinction matters because formatting does not repair incorrect data. Applying a currency format to text that contains a dollar sign does not necessarily turn that text into a usable number. Conversely, a number can be stored correctly while appearing without a currency symbol or with an unexpected number of decimal places. Diagnose the data type and the display format separately.
- Power Query data type: affects conversions, calculations, sorting, filtering, and the kind of values loaded to Excel.
- Excel number format: affects how numeric values and dates look in worksheet cells, without changing the stored value.
- Column label or header: is text. Naming a column Amount or Date does not determine its type or format.
Set a column’s data type in Power Query
In Excel for Microsoft 365 on Windows, open the query in the Power Query Editor, select the column, and use Transform > Data Type to choose the appropriate type. You can also use the type icon beside a column heading. If prompted, choose whether to replace the existing conversion step or add a new one. The type icon in the heading is a useful quick check: it distinguishes types such as text, date, and decimal number.
- In Excel, select Data > Queries & Connections, then double-click the query you want to edit.
- Select the column that needs correction. To select several columns, use the usual Ctrl-click selection on Windows.
- Choose Transform > Data Type and select a suitable type, such as Whole Number, Decimal Number, Date, or Text.
- Review the preview for errors or unexpected conversions, then select Home > Close & Load to return the results to Excel.
- Apply a worksheet number format to the loaded column if its appearance needs to change.
Power Query often detects types automatically, especially in imported files, but automatic detection is a convenience rather than a guarantee. Inspect important columns instead of assuming the first guess is right. A later refresh can bring in new values that reveal a type mismatch that was not visible in the original sample.
Choose the right type for the job
Use a type that matches the role of the data, not merely how a sample value looks. Whole Number is appropriate for counts with no fractional component. Decimal Number is suitable for measurements and many ordinary calculations. Date and Date/Time types let Power Query and Excel interpret calendar values as dates or timestamps. Text is often the right choice for codes and identifiers, even when every character happens to be a digit.
Power Query’s Fixed Decimal Number type is intended for values that need fixed precision, including many currency-style amounts. It supports four decimal places. Decimal Number is a floating-point type, so some decimal fractions cannot be represented exactly in binary; small precision differences can occur in calculations. The choice depends on the source and the precision your work requires. Neither type adds a currency symbol: that is a display decision made in Excel.
Consider an imported customer code such as 004281. If it is treated as a number, the leading zeros can disappear, and a later custom number format may only make the value look padded again. If the zeros are part of the code, set the column to Text in Power Query so they remain part of the value. This is also a good approach for postal codes, account references, and product IDs that are labels rather than quantities.
Apply a number format after loading
After loading a query to a worksheet, select the relevant cells or table column and use the number-format controls on Excel’s Home tab. For more control, press Ctrl+1 on Windows to open Format Cells, choose the Number tab, and select a category such as Currency, Percentage, Date, or Custom. The format changes how Excel shows the values; it does not modify the Power Query transformation or convert one currency into another.
For example, if a query returns numeric sales amounts, select the sales column and choose Currency or Accounting in Excel. If a query returns decimal fractions such as 0.25, choose Percentage to display 25%. If it returns dates, choose a date format such as 14-Mar-2026 or 3/14/2026 according to the convention readers expect. A date format will not fix a column that Power Query loaded as text; correct the type first.
When you want the same appearance every time, format the query output table column rather than just a few visible cells. If the query is loaded to the Data Model rather than a worksheet table, there is no ordinary output column on the sheet to format; choose a format in the report or PivotTable that displays the data instead.
Handle dates, percentages, and identifiers carefully
Some common surprises come from confusing the value with its display. Excel stores dates as serial values and displays them using a date format. A date-looking value loaded as text may sort alphabetically and fail date calculations; set it to Date or Date/Time in Power Query. A numeric value of 0.08 displayed as 8% is still 0.08 underneath. If the source instead contains the number 8 to mean eight percent, formatting it as Percentage will display 800%; the source value or a transformation must be corrected.
- Currency: keep the amount numeric, then choose a currency display in Excel. A symbol is not an exchange-rate conversion.
- Percentages: confirm whether the source represents eight percent as 0.08 or 8 before choosing a display format.
- Dates: convert recognizable date text to a Date type, then select the preferred Excel date display.
- Identifiers: use Text when leading zeros or exact character sequences must be preserved.
- Measurements: keep the values numeric and add units in a separate column or through an appropriate custom display format.
Use locale when imported numbers or dates are misread
Files created under different regional settings may use different decimal and thousands separators. For instance, one source may write one thousand and a half as 1,000.5, while another uses 1.000,5. A value that looks numeric can be interpreted incorrectly or produce an error if Power Query assumes the wrong convention. Dates can also be ambiguous: 04/05/2026 can mean April 5 or May 4 depending on the source’s locale.
When the source convention is known, select the column and use Transform > Data Type > Using Locale (or the equivalent command from the column’s type menu). Choose the intended data type and the locale that matches how the source text is written. This tells Power Query how to parse the text; it does not merely change the later Excel display. Check several converted rows, especially dates where day and month can be swapped without causing an obvious error.
What happens when the query refreshes?
A refresh reruns the query’s import and transformation steps, then updates its destination. Data types defined in those steps are part of the query, so they are reapplied to incoming data. Excel’s worksheet number format is separate from those steps. A table’s formatting often remains when its data refreshes, but the result can depend on the destination, changes to the table structure, and refresh settings. Do not treat cell formatting as a substitute for a type step in the query.
If a format changes or fails to cover newly loaded rows, check that the query still loads to the expected table and that the table column is formatted. In Excel, open Data > Queries & Connections, right-click the query, and choose Properties to review available refresh and output options. Names and available settings can vary with the connection and Excel build. After changing output or refresh behavior, refresh and inspect the result, including any rows added since the previous load.
Common problems and how to troubleshoot them
If a number format appears to have no effect, first check whether the cells contain numbers or text. Text values may be left-aligned by default and may not respond to numeric formats as expected. In Power Query, inspect the type icon and preview; then convert the column using the correct type and, where needed, the correct locale. An error after conversion usually indicates that one or more source values do not match the chosen type, such as a word in an otherwise numeric column.
If zeros disappear from an ID, do not solve it only by choosing a custom format unless the value is genuinely meant to be numeric. Change the query column to Text so the identifier is preserved as supplied. If a percentage displays 100 times too large, inspect the underlying source scale before changing the format. If dates appear as numbers, the values may be date serials with a General or numeric display; choose a date format in Excel. If dates remain unchanged as text, fix the type conversion in Power Query instead.
Power Query versus Excel formatting
Think of Power Query as the place to clean, parse, and shape data, and Excel’s worksheet formatting as the place to control its appearance for readers. Power Query can turn a text value into a date, split a column, remove unwanted characters, or assign a numeric type. Excel can show a valid numeric result with a currency symbol, a chosen number of decimals, or a readable date pattern. Keeping these responsibilities separate makes refreshes easier to troubleshoot and helps prevent cosmetic changes from being mistaken for data corrections.
The Windows menu paths above describe Excel for Microsoft 365. Power Query authoring features and menu layouts can differ in Excel for Mac, and availability depends on the Mac release and data source. Excel for the web may offer different query editing and refresh capabilities from the Windows desktop app. If a command is missing, check the options available in the installed Excel version; the key concept remains the same: set the data type in the query, and set worksheet display formatting in Excel.
Frequently Asked Questions
Can I format numbers directly in Power Query?
Not with Excel’s worksheet number formats. Power Query sets data types and transforms values; apply currency, percentage, decimal-place, or date display formatting after loading the results into Excel.
Does changing a Power Query data type change the number format?
No. Changing a column to Decimal Number or Date determines how the value is interpreted. Excel’s number format separately determines how a loaded value appears in a worksheet cell.
How do I keep leading zeros in a Power Query column?
Set the column to Text in Power Query when the values are identifiers, such as ZIP or product codes. A numeric type can discard leading zeros because they do not change a number’s value.
Why does my currency format disappear after refreshing?
Worksheet formatting is separate from query steps, and refresh behavior can depend on the output table and its settings. Reapply the format to the table column, review the query’s Properties, and test another refresh to confirm the result.
Can Power Query convert text to a date using a specific regional format?
Yes. Use the column’s Using Locale type-conversion option and select the locale that matches the source text. Then check the converted dates before loading, particularly when day and month could be confused.
Related
- New Hire Checklist Template in Excel: What to Include
- Can’t Insert an Image in Excel? Causes, Checks, and Fixes
- Negative Number Format in Excel: How to Change It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Adjust Page Breaks for a Better Print LayoutTo adjust a print page break in Excel for Microsoft 365 on Windows, choose View > Page Break…
- How to Double Space Bullet Points in Microsoft WordTo double-space bullet points in Microsoft Word, select the bullet paragraphs, then choose Home > Line and Paragraph…
- How to Delete a Continuous Page Break in WordTo delete a continuous page break in Word, show formatting marks, find the line labeled Section Break (Continuous),…
- Turn Off Flash Fill in Excel (and Keep Manual Fill)Yes. In Excel for Microsoft 365 on Windows, go to File > Options > Advanced, clear Automatically Flash…
- How to Add Footnotes in Microsoft Word, Step by StepTo add a footnote in Microsoft Word, place the cursor where the reference belongs, then select References >…
- How to Delete a Single Page in Word SafelyTo delete a single page in Word, click anywhere on it, press Ctrl+G on Windows (Option+Command+G on Mac),…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.