This tutorial shows you how to create and customize bar graphs in Excel, including sorting data automatically. We'll cover creating various bar chart types, adjusting bar width and colors, and handling negative values.

Bar graphs, alongside pie charts, are frequently used for comparing numerical data like percentages, frequencies, or measurements across different categories. A specialized bar graph, the Gantt chart, is common in project management.

This tutorial explores:

• Bar chart fundamentals
• Creating bar charts in Excel
• Different bar chart types
• Customization:
  • Adjusting bar width and spacing
  • Handling negative values
• Sorting data in bar charts

Bar Chart Basics

A bar graph (or bar chart) uses rectangular bars to represent data categories, with bar lengths proportional to the data values. They can be vertical or horizontal (vertical bar graphs are called column charts in Excel).

The image below shows a standard 2-D clustered bar chart with three data series (grey, green, blue) and four categories (Jan-Apr).

Creating Bar Charts in Excel

Creating a bar chart is straightforward: select your data, go to the "Insert" tab, and choose your desired bar chart type from the "Charts" group.

The image below shows creating a standard 2-D bar chart.

The resulting chart (shown below) displays one data series if your data has a single numerical column; multiple columns create multiple data series, each with a different color.

To view all available bar chart types, click "More Column Charts..." and select from the subtypes. Adjust layout and style using the "Design" tab's "Quick Layout" and "Chart Styles" options.

Excel Bar Chart Types

Excel offers several bar chart subtypes:

• Clustered Bar Charts: Compare values across categories.

• Stacked Bar Charts: Show the proportion of individual items to the whole.

• 100% Stacked Bar Charts: Show the percentage contribution of each value to the total in each category.

• Cylinder, Cone, and Pyramid Charts: Similar to rectangular bar charts, but with different shapes. In Excel 2013 and later, create a 3-D bar chart and then change the column shape in the "Format Data Series" pane.

Customizing Bar Graphs

Customize your chart title, axes, data labels, legend, gridlines, and more using standard Excel chart formatting options.

Changing Bar Width and Spacing

Right-click a data series, choose "Format Data Series...", and adjust the "Gap Width" and "Series Overlap" (2-D) or "Gap Depth" (3-D) sliders to control bar width and spacing.

Bar Charts with Negative Values

Excel handles negative values, but you may need to adjust the vertical axis label position ("Low" in "Format Axis...") and use different colors for negative bars ("Invert if Negative" in "Format Data Series...").

Sorting Data on Bar Charts

By default, Excel reverses the order of data categories in bar charts. To sort correctly, sort your source data accordingly (ascending for ascending order on the chart, and vice versa).

To sort without changing source data, in "Format Axis...", select "At maximum category" and "Categories in reverse order". This also affects data series order.

To reorder data series independently, use the "Select Data Source" dialog or edit data series formulas.

This comprehensive guide helps you master Excel bar graph creation and customization.

The above is the detailed content of How to make a bar graph in Excel. For more information, please follow other related articles on the PHP Chinese website!