Others

How to Display Whole Dollar Values in Microsoft Access Charts

Phi Hung Vo Oct 7, 2026 870 views

Question details

The user needs to remove decimal places from currency values displayed on the data labels and Y-axis of a Microsoft Access report chart.

How to Display Whole Dollar Values in Microsoft Access Charts

Product: Microsoft Access
Device & OS: not provided
Scenario: Formatting a chart within an Access report where currency values need to be displayed as whole dollars (zero decimal places).
Observed behavior: The chart continues to display cents in its data labels and Y-axis, ignoring the rounded dollar values from the underlying table, and the standard decimal-place controls found in Excel appear to be missing.

Before you start

Ensure your Microsoft Access report is open in Design View and that you have permission to modify both the chart properties and the underlying queries feeding data to the chart.

Solution 1Recommended

Use the Format() Function in the Underlying Query

When Access chart properties do not expose decimal settings for data labels, formatting the field directly in the query as a text string forces the chart to display the exact format.

Microsoft Access charts often pull raw numerical data from queries, bypassing display formats set at the table level. By converting the number to a formatted string within the query, you guarantee the chart displays whole dollars.

Open the query in Design View

Locate the query that acts as the Record Source for your chart. Right-click it in the Navigation Pane and select Design View.

Modify the value field

In the Field row of the query design grid, wrap your currency field with the Format function. For example, change 'Sales' to 'FormattedSales: Format([Sales], "$#,##0")'.

Save and verify the chart

Save the query, then open your report in Print Preview to confirm the chart labels and Y-axis now display whole dollar amounts without cents.

Use the Format() Function in the Underlying Query

Sorting Considerations: Using the Format() function converts the numeric value to text. If your chart relies on this field for sorting, you may need to include the original hidden numeric field in the query to maintain numerical order.

Solution 2

Format Axis and Data Labels via the Chart Edit Mode

Access sometimes hides chart formatting options until you double-click the chart object to enter the dedicated OLE chart editing mode.

Free Microsoft Office alternative

Looking for a Simpler Way to Handle Data and Charts? Try WPS Office

If you find Microsoft Access chart formatting overly complex or lacking basic Excel-like controls, consider handling your data visualizations in WPS Spreadsheet. It offers a highly intuitive, lightweight, and free environment for all your charting needs.

1. Open WPS Spreadsheet: Launch WPS Office and open WPS Spreadsheet to easily input or import your database values.
2. Create a Chart: Highlight your data, navigate to the Insert tab, and select your preferred chart type.
3. Format Currency Labels Easily: Right-click any axis or data label, select Format, and instantly set decimal places to zero with straightforward Excel-style controls.

Completely free and lightweight alternative to Microsoft OfficeSeamlessly open, edit, and save Microsoft Excel (.xlsx), Word, and PowerPoint formatsIntuitive right-click chart formatting menus that easily control decimal places and currency symbolsFamiliar user interface requiring zero learning curve for Office users

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access chart ignore the table's currency formatting?

Charts in Microsoft Access pull the raw, underlying data from your tables or queries rather than the display format (like Currency or decimal limits) set in the table properties. You must reapply the formatting directly within the chart object or format the data as a string in the query.

Why can't I find the 'Format Data Labels' option when I right-click my chart?

In Access, right-clicking a chart in the standard Report Design View only shows report-level properties. To access specific chart element formatting, you must first double-click the chart to enter its dedicated OLE editing mode, then right-click the specific labels or axis.

Can I format the Y-axis and the data labels independently in Access?

Yes. Once you double-click the chart to enter edit mode, you can right-click the Y-axis to select 'Format Axis' and right-click the data points to select 'Format Data Labels'. They have independent property menus for number formatting.

Will formatting currency as text in a query affect my chart's data?

Using the Format() function turns numeric values into text strings. While this fixes the display issue by removing cents, it may cause the chart to sort values alphabetically instead of numerically. You can resolve this by keeping an unformatted numeric field in the query solely for sorting purposes.

Office Quick Solutions