Power Query Problems

How to Create a Monthly Pivot Chart for Import and Export Data in Excel

Olivia Miller Sep 30, 2026 868 views

Question details

The user needs to create an Excel Pivot Chart displaying average daily energy import and export data by month, ensuring each month appears once in the legend with matching colors, and displaying a 24:00 time axis.

How to Create a Monthly Pivot Chart with Import and Export Data in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Visualizing and comparing daily energy import and export metrics aggregated on a monthly basis.
Observed behavior: The source data is currently structured in a way that prevents the Pivot Chart from properly synchronizing series colors and grouping by month natively without data reshaping.

Before you start

Ensure your source data is organized in a tabular format and that you have the Power Query feature enabled under the Data tab in Excel.

Solution 1Recommended

Use Power Query to Reshape Data and Create the Pivot Chart

By unpivoting your import and export columns in Power Query, you convert your dataset into a format that allows Pivot Tables to easily group dates by month and synchronize legend colors.

Power Query is an essential tool for reshaping source data before visualization. When data is presented in wide format (separate columns for different categories like Import and Export), Pivot Charts often struggle to align colors and group data elegantly.

Unpivoting the columns consolidates these metrics into 'Direction' and 'Value' columns, which easily map to your Pivot Chart's legend and values fields.

Load Data into Power Query

Select your source data table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Calculate and Add Custom Columns

Perform any necessary calculations to find daily average import and export values. If needed, use 'Add Column' to create custom identifiers for your series.

Unpivot the Columns

Select your calculated Import and Export columns, right-click the column headers, and choose 'Unpivot Columns'. Rename the newly created Attribute column to 'Direction' and the Value column to 'Amount'.

Load to Data Model

On the Home tab, click 'Close & Load To...'. In the dialog box, select 'Add this data to the Data Model' and choose to output it as a 'PivotChart'.

Group Data by Month

In your newly created PivotChart, drag the Date/Time field into the Axis area and your 'Amount' field into the Values area. Right-click the date axis in the chart, select 'Group', and choose 'Months'.

Format the Time Axis

To display exactly 24:00, right-click the time axis, choose 'Format Axis', navigate to the Number options, and apply the custom format code [h]:mm.

Use Power Query to Reshape Data and Create the Pivot Chart

Power Query Capabilities: Power Query is also highly effective for importing CSV files with locale-specific date and time formats that might otherwise cause errors in Excel.

Advanced Data Visualization

Create Stunning Pivot Charts effortlessly with WPS Spreadsheet

WPS Spreadsheet offers a robust and intuitive platform for analyzing data, creating PivotTables, and generating dynamic Pivot Charts. You can easily group complex data by month, format custom axes, and achieve precise chart layouts without extensive data manipulation.

1. Open Your Data: Launch WPS Spreadsheet and open the file containing your daily energy import and export data.
2. Insert a PivotTable: Highlight your data range, navigate to the Insert tab, and click 'PivotTable' to begin aggregating your metrics.
3. Group by Month: Drag your Date field to the Rows area, right-click on any date value, select 'Group', and choose 'Months'.
4. Generate the PivotChart: Go back to the Insert tab, click 'Chart', and select your preferred chart type to instantly visualize your monthly grouped data.

Free to download and useSeamlessly compatible with Microsoft Excel (.xlsx and .csv) formatsIntuitive PivotTable and PivotChart interfaces for rapid data aggregationRich chart customization options for legends, labels, and axes

microsoft office alternative - wps office

Frequently Asked Questions

Why do I need to unpivot data before creating the Pivot Chart?

Unpivoting transforms your data from a wide format (multiple columns) into a long format. This restructuring allows the Pivot Chart to treat the data as distinct series grouped by a single dimension (like month) while keeping legend colors perfectly synchronized.

How do I group dates by month in an Excel PivotChart?

Once your data is loaded into the PivotTable or Data Model, right-click on the date field in the axis or row labels area, select 'Group', and choose 'Months' from the grouping dialog box.

Can I format the time axis to show exactly 24:00 in my chart?

Yes. Right-click the axis in your PivotChart, select 'Format Axis', navigate to the Number section at the bottom of the format pane, and type [h]:mm into the Custom format box to allow time values to display up to 24 hours without resetting to 00:00.

Excel & Data Problems