How to Remove Formula 1 and Formula 2 from Excel Pivot Charts
Question details
The user needs to remove unwanted 'Formula 1' and 'Formula 2' series that appear in a PivotChart and disrupt trendlines after manually rearranging month items.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Manually changing the order of text-based month names in an Excel PivotTable and PivotChart to sort them chronologically instead of alphabetically.
- Observed behavior
- Unwanted series named 'Formula 1' and 'Formula 2' automatically appear in the PivotChart, which interferes with the generation of trendlines.
Before proceeding, check your source data to verify if the months are stored as text (e.g., 'January') rather than actual date values, which causes alphabetical sorting.
Remove the PivotTable and Use Actual Date Formats
The most effective way to eliminate phantom formulas created by manual sorting is to remove the affected PivotTable and ensure your source data uses properly formatted date values.
When months are stored as text, Excel sorts them alphabetically. Manually dragging them to force a chronological order creates custom calculated items in the background, manifesting as 'Formula 1' and 'Formula 2'. Modifying the source data format is the definitive fix.
Select the entire problematic PivotTable by clicking and dragging over its cells, then press the Delete key on your keyboard to remove it and its related corrupted chart entries.
Navigate to your original source data table and change the text-based month names into actual date values (for example, change 'January' to '01/01/2023').
Select the newly updated date column, right-click, and choose 'Format Cells'. Go to the 'Custom' category and type 'mmmm' (or 'mmm') so the cells display as month names while retaining chronological sorting properties.
Select your updated source data, go to the Insert tab, and create a new PivotTable and PivotChart. The months will now sort chronologically automatically without generating unwanted formulas.
Attempt to Remove Unwanted Series via Chart Data
You can try removing the formula series directly from the chart's data source settings, though this may be restricted depending on the PivotTable's configuration.
Easily Manage Pivot Tables and Charts with WPS Office
WPS Office provides robust Spreadsheet tools that seamlessly handle PivotTables and PivotCharts. With intuitive data formatting and charting tools, you can avoid manual sorting errors and generate clean, accurate reports.
- 1. Open your data file: Launch WPS Spreadsheets and open your existing Excel (.xlsx) workbook.
- 2. Format your dates accurately: Highlight your date columns, right-click, and select 'Format Cells' to easily apply a 'Month' custom format.
- 3. Insert a fresh PivotTable: Select your data range, go to the 'Insert' tab, and click 'PivotTable' to dynamically arrange your fields.
- 4. Generate a clean PivotChart: With your PivotTable selected, navigate to the Analyze tab and click 'PivotChart' to create a visual representation free of unwanted formula series.
Frequently Asked Questions
Why do Formula 1 and Formula 2 appear when I reorder months?
When months are stored as text, Excel sorts them alphabetically by default. Manually dragging these text items to force a chronological order causes Excel to create custom calculated items in the PivotTable's background. These automatically manifest as 'Formula 1' and 'Formula 2' series in your associated PivotChart.
Can I just delete the unwanted series from the PivotChart directly?
Usually, you cannot delete them directly from the chart. A PivotChart's data and structure are strictly controlled by its connected PivotTable. To remove series or modify the underlying data structure permanently, you generally must make the changes within the PivotTable itself or correct the source data.
How do I sort months chronologically without getting these errors?
Ensure your source data contains actual date values (like 1/1/2023) rather than text strings (like 'January'). Then, apply a custom number format of 'mmmm' or 'mmm' to the cells to display the month name. When you create a PivotTable from this data, it will recognize the underlying dates and automatically sort them chronologically.
