Comparative Histogram in Excel 2010: Build a Mirrored Bar Chart

A comparative histogram in Excel 2010 is usually a mirrored horizontal bar chart: one group extends left from zero and the other extends right. It compares two values across the same categories, such as student counts by department. The negative numbers are a plotting device, not negative counts.

This chart is not a statistical histogram. A true histogram groups numeric measurements into bins and shows how often values fall in each bin. Microsoft documents histograms and bar charts as separate chart types; use this mirrored method when you want to compare two groups category by category.

Prepare the data without changing the source

Keep one row per category and put each group in its own column. For example:

Do not replace your original counts with negative values. Make a separate, adjacent chart-ready block and use formulas so the source table stays intact.

Create the comparative bar chart

Step 1: Check the source table

Use clear column headings and numeric values, with no blank rows inside the range. Each row should represent the same department for both groups. Each row must use the same category for both groups.

Step 2: Add a helper block

In an empty area, create three adjacent columns headed Department, Women (left), and Men. If the source columns are A:C, enter =A2 in the helper department cell, =-C2 for Women, and =B2 for Men. Fill the formulas down for each department. The minus sign makes the women’s bars extend left; the original counts remain positive in the source table.

Step 3: Insert a 2-D clustered bar chart

Select the full chart-ready range, including the headings. On the Insert tab, open Bar in the Charts group and choose 2-D Clustered Bar. In Excel 2010, the command appears on the classic Ribbon; the exact button layout can vary with window size. Avoid stacked or 3-D bars because this comparison needs two series to share each category row.

Step 4: Confirm the series and direction

Check that department names appear as the vertical category labels, Women extends left of zero, and Men extends right. The initial value axis will show negative numbers on the left; that is expected. If Excel plotted the wrong fields, right-click the chart and choose Select Data to check the category-label range and each series. If both groups extend the same way, check the helper formula and the selected range.

Step 5: Move category labels to the left

Right-click a department label and choose Format Axis. Under Axis Options, set Major tick mark type and Minor tick mark type to None, then set Axis labels to Low. This places the category names at the left edge of the plot and removes small ticks that add clutter. If the first department appears at the bottom, use Categories in reverse order in the same dialog to choose the order you prefer.

Step 6: Balance and format the value axis

Right-click the horizontal number axis and choose Format Axis. In Axis Options, give the axis symmetric minimum and maximum bounds based on the largest count. For a largest value of 42, for example, use -50 and 50 so both sides have the same scale. Then open Number, choose Custom, and enter 0;0;0 as the format code. In this three-section format, positive values, negative values, and zero each display without a minus sign on the axis. Click Add if Excel 2010 shows that button, then close the dialog. Number formatting changes the labels, not the helper values used to draw the bars.

Step 7: Adjust bar alignment and spacing

Right-click a bar and choose Format Data Series. Under Series Options, set Series Overlap to 100% so each department’s two bars line up around the same row. Set Gap Width to about 60% to start; lower values make thicker bars, while higher values leave more space between departments. Adjust it to fit.

Step 8: Label the finished chart

Keep the legend and name the series Women and Men. On Chart Tools > Layout, add a title such as Students by Department. Because the number axis now shows positive-looking values on both sides, add a small text box below the plot that says Women ← | → Men, or otherwise label which group is on each side. In Excel 2010, choose Text Box on the Layout tab. Leave data labels off unless you also format negative labels; the axis number format does not change labels attached directly to bars.

Check the result

• Women bars point left and Men bars point right from zero.
• The horizontal axis has equal absolute bounds on both sides.
• The left and right groups are identified clearly even though both sets of axis labels look positive.
• The source table still contains the original positive counts.

This chart compares counts. If group sizes differ and you need rates or proportions, calculate those percentages first. Microsoft’s current chart guidance sometimes uses newer pane names, but the same chart and axis controls apply to Excel 2010’s classic dialogs.

Official references

• Microsoft: Available chart types in Office
• Microsoft: Create a histogram
• Microsoft: Custom number format sections
• Microsoft: Create a chart from start to finish