To hide the 100% baseline column in an Excel Pivot Chart when using "Show Value As % Of Another Base Item," you must remove the base field from the Legend (Series) area. This isolates the calculated percentages for your primary categories while retaining the underlying data structure required for the calculation, resulting in a cleaner chart that displays only the relevant comparative values.
Understanding the Calculation Context
When you apply the "Show Values As" feature in Excel and select "% Of Another Base Item," Excel creates a dynamic relationship between two distinct fields in your pivot table. One field serves as the numerator (the values you are measuring), and the other serves as the denominator or base (the reference point). By default, if both fields are present in the Rows, Columns, or Legend areas, Excel generates separate series for each combination of these fields. This often results in a cluttered visualization where the base item itself appears as a bar or line with a value of 100%. While mathematically correct, this is frequently redundant for presentation purposes. The goal is to show how other items compare to this base without visually emphasizing the base item's own 100% status as a separate data series. Removing the base field from the visual legend eliminates this redundancy without breaking the underlying calculation logic.Step-by-Step Removal of the Base Field
The most common mistake users make is deleting the base field entirely from the Pivot Table Fields pane. This action removes the reference point needed for the percentage calculation, causing all values to revert to simple counts or sums rather than percentages. To fix this, you must manipulate the layout areas specifically. Follow these precise steps to isolate the percentage view:- Identify Your Fields: Determine which field contains the values you want to measure (e.g., Sales Amount) and which field acts as the base (e.g., Product Category).
- Check the Legend Area: Look at the "Legend (Series)" box in the PivotTable Fields pane. If both your value field and your base field are listed here, they are being treated as separate series.
- Move the Base Field: Click and drag the base field out of the "Legend (Series)" area. You can move it to the "Rows" or "Columns" area, or simply leave it there if it was already positioned correctly for grouping.
- Verify the Calculation: Ensure that the "Values" area still contains your measure and that the "Show Values As" setting remains set to "% Of Another Base Item." The base field should remain active in the Row or Column hierarchy to serve as the denominator.
Handling Nested Categories
If your data involves nested categories, such as Year over Month, ensure that the higher-level category (Year) is not placed in the Legend. Instead, place it in the Rows area above the lower-level category (Month). This allows the month-to-month or year-over-year percentage calculations to function correctly while keeping the visual series clean. For more complex hierarchical structures, consider reviewing How to Create a Hierarchy Between Tables Following Relationship to understand how field placement affects aggregation depth.Alternative Approaches for Complex Data
In some scenarios, simply moving fields may not provide the desired visual outcome due to multiple bases or complex filtering requirements. If you find that removing the base field causes other unintended changes in your chart, you might need to adjust the report layout. Switching to Tabular Form in the Design tab can help you visualize exactly which rows correspond to which series, making it easier to identify where the base field is influencing the output. Another approach involves using slicers to filter the data dynamically. If you are comparing specific subsets of data, applying a slicer to the base field can effectively hide certain baselines without altering the field placement in the pivot table structure. This method is particularly useful when you need to toggle different base comparisons on and off for different stakeholders. For advanced filtering techniques that complement this approach, see Excel Pivot Table Advanced Filtering for Product BOMs.Troubleshooting Common Issues
Users often encounter situations where the 100% column reappears after refreshing the pivot table or changing filters. This usually happens because Excel resets the field placement upon significant structural changes. Always double-check the Legend area after any major modification to ensure the base field has not been inadvertently dragged back into the Series list. Additionally, if your chart displays gaps or missing bars, verify that the base item exists for every category in your dataset. If a base item is missing for a specific period or segment, Excel cannot calculate the percentage, resulting in blank cells. Ensuring complete data coverage for your base field is essential for maintaining consistent visual representation across all time periods or categories.Frequently Asked Questions
Why does my chart show duplicate bars for the same category?
This occurs when both the value field and the base field are placed in the Legend (Series) area. Each unique combination creates a separate series. Remove the base field from the Legend to consolidate them into a single series per category.
Can I keep the base field in the Rows area?
Yes, keeping the base field in the Rows or Columns area is necessary for the calculation to work. It provides the denominator for the percentage. Just ensure it is not also in the Legend area to prevent duplicate visual series.
What happens if I delete the base field entirely?
Deleting the base field from all areas will break the "% Of Another Base Item" calculation. Excel will revert to showing raw values like Sum or Count, as there is no longer a reference point for the percentage comparison.
Does this method work for clustered column charts?
Yes, the principle applies to all standard pivot chart types. Whether you use clustered columns, lines, or bar charts, removing the base field from the Series area will unify the data points into a single logical series.
How do I handle multiple base items in one chart?
If you need to compare against multiple bases simultaneously, you cannot use a single "% Of Another Base Item" calculation for all. You would need to create separate calculated fields or measures for each base and add them as individual series in the Legend.