Excel’s dedicated graphing tools allow you to quickly switch between a variety of chart types and customize them once you’ve chosen.
A bar graph, or bar chart, is one of the simplest and best ways to present data. Its clear nature has made it a staple and as a result it’s essential that anybody who processes data knows how to make a bar graph in Excel.
Before we continue with this guide, however, it’s important to consider whether a bar graph is the best choice for your data. Bar charts are best used when you can make direct comparisons between your data. For example, you have a list of countries and their life expectancies. Once your data gets more complex, or you want to show things like trends or distribution, scatter, line, bubble, and pie graphs are usually better.
How to Make a Bar Graph in Excel
Creating a bar graph in Excel is a fundamental skill for anyone dealing with data visualization. Microsoft Excel offers a range of graphing tools that simplify the process of transforming your data into a visually appealing bar chart.
- Select Your Data
Before we start, you’ll naturally need your data. It’s best if this data is in two labeled columns – these will represent your X and Y-axis. If you don’t have any data, you can quickly copy ours from below.
- Insert a Chart
Navigate to the “Insert” tab in the Excel ribbon and click on the bar chart icon. Choose between 2D or 3D bar chart types. For simplicity, we recommend starting with the 2D column chart.
- View Your Chart
Excel will automatically generate a chart based on the selected data. The default settings usually provide a clear visualization, but customization options are available if adjustments are needed.
How to Format Your Excel Bar Graph
After creating your bar graph, Excel offers numerous formatting options to tailor the chart to your preferences. This section covers how to modify chart elements, including titles, axis labels, and styles.
- Edit Chart Title
Double-click the chart title to edit its text. This allows you to provide a more descriptive title for your graph.
- Toggle Chart Title Visibility
For a cleaner look, you can hide the chart title. Click on the graph, press the “+” icon on the right, and uncheck the “Chart Title” option.
- Enable/Disable Axis Titles
Similarly, axis titles can be toggled by clicking on the graph, pressing the “+” button, and checking or unchecking the “Axis Titles” option.
- Customize Axis Titles
To edit an axis title, double-click on it and type the desired text. This helps in providing clearer labels for your data.
- Change Chart Style
To alter the visual style of your graph, click on the paintbrush icon next to your chart and select a style from the “Style” tab.
- Alternative Style Selection
You can also change the chart style via the “Chart Design” tab in the ribbon, where a gallery of styles is available next to the “Change Colors” button.
- Modify Chart Color
If the default color scheme doesn’t fit your needs, click on the graph, select the paintbrush icon, and choose a new color from the “Color” tab under “Chart Styles“.
- Format Chart Area
For more detailed formatting options, right-click the chart and select “Format Chart Area…” from the context menu.
- Adjust Fill, Border, and Formatting
In the “Chart Options” tab, accessible from the “Format Chart Area” menu, you can change the fill, border, and other formatting options.
- Text Options
Click on the “Text Options” tab within the “Format Chart Area” menu to adjust text fill, color, transparency, and outline.
- Change Font and Size
To modify the font and size of your chart’s text, click on a label or title and use the “Home” tab in the ribbon to access font options.
- Or: Use the Pop-Up Formatting Menu
An in-line formatting menu appears when you select text, allowing you to adjust font, size, color, and more directly.
How to Make a Pie Chart in Excel
Pie charts are an incredibly useful tool when you want to highlight the differences between certain data points. However, though great, they aren’t exactly precise – it’s hard to discern an accurate number from the angle of a particular section. In our other guide, we show you how to create a Pie Chart in Excel, explode it, and customize its colors, labels, and positioning.
FAQ – Frequently Asked Questions About Excel Bar Graphs
How do I select non-adjacent data ranges for my Excel bar graph?
To select non-adjacent data ranges in Excel for your bar graph, you first click on one data range you wish to include. Then, hold down the Ctrl key (or Cmd key on a Mac), and while holding this key, click on each additional range you want to include in your graph. This method allows you to select and include multiple data sets that are not next to each other in your chart.
Can I add secondary axes to my Excel bar graph for additional data series?
Yes, adding a secondary axis for an additional data series in an Excel bar graph is easily doable. First, insert your bar graph with the primary data series. Then, add your additional data series to the chart. Afterward, right-click on the added series (it will appear in your chart overlaying the first series) and select “Format Data Series.” From there, choose “Secondary Axis.” This additional axis will allow you to visually compare two data sets that have different scales.
How do I apply a logarithmic scale to the axes of my bar chart in Excel?
To apply a logarithmic scale to the axes of a bar chart in Excel, right-click on the axis (X or Y) that you wish to modify, and select “Format Axis” from the dropdown menu. In the Format Axis pane, under the Axis Options category, you’ll find a checkbox for “Logarithmic scale.” Check this box to apply a logarithmic scale, which is particularly useful for data that spans several orders of magnitude, as it can make such data easier to interpret and visualize on the chart.
How can I make a dynamic bar chart that updates based on dropdown selections?
Creating a dynamic bar chart that updates based on dropdown selections involves using Excel’s “Data Validation” feature and the “INDIRECT” function. First, set up data validation to create a dropdown list in a cell, which will serve as your selector. Then, organize your chart data into named ranges corresponding to each selection. Use the INDIRECT function to refer to these named ranges dynamically based on the current dropdown selection. When the selection changes, the INDIRECT function updates the chart’s data source to the corresponding named range, thus updating the chart.
Is it possible to create a Pareto chart using Excel’s bar graph features?
Creating a Pareto chart manually using Excel’s bar graph features involves a few steps. First, you’ll need to order your data points by descending value, as Pareto charts display information in a priority order. Then, calculate the cumulative percentage of your data points. After organizing your data, create a bar chart with your values. Next, add a secondary axis for your cumulative percentage and use it to plot a line graph over the bars. Although this process can be manual, note that Excel 2016 and later versions offer a built-in Pareto chart type under the “Histogram” chart options, simplifying this task.
How do I animate my Excel bar chart for presentations?
Since Excel does not support direct animation within charts, you can create a semblance of animation through PowerPoint for presentations. One approach is to replicate your chart across multiple slides, with each slide revealing a part of the chart progressively. Another method involves using PowerPoint’s animation features; you can copy your chart into PowerPoint and then apply custom animations such as ‘Appear‘ or ‘Wipe‘ to each element of your chart, thus creating a dynamic effect as you present each part of the bar chart in sequence.
Can I use Excel to create a bar chart that compares data across different periods?
To create a bar chart that effectively compares data across different periods in Excel, structure your data table so that you have one column for the periods (e.g., years, months) and additional columns for the data points you’re comparing in those periods. When creating the bar chart, Excel will automatically use the period column as the category axis and space the bars according to the periods, allowing for easy comparison across time. For more detailed comparison, consider using clustered or stacked bar charts to show multiple data series side-by-side or in the same bar, respectively.
How to create a bar chart with a break in the axis for very high values?
Despite Excel not directly supporting axis breaks, a visual workaround involves editing the chart to include a custom visual break symbol (e.g., a zigzag or gap on the axis) to imply discontinuity. You could format specific data points or bars to represent the break visually by using different colors or adding annotation texts. However, this approach requires manual adjustments and creative use of chart formatting options to imply the break rather than directly alter the axis scale.
How do I overlay a line graph on top of a bar chart in Excel?
To overlay a line graph on a bar chart in Excel, first, create your bar chart with the initial data series. Then, add your line graph data series to the chart by right-clicking the chart, selecting “Select Data“, and then “Add” to input the new series. Once added, right-click the new series in the chart, choose “Change Series Chart Type“, and select a line graph for this series. This approach combines the visual comparison strengths of both bar and line charts, suitable for showing different types of data relationships in a single chart, such as volume and trend.
Can Excel bar charts display real-time data updates?
To display real-time data updates in Excel bar charts, the chart must be linked to a data source that updates in real time. If you have a dynamic data source like an external database or a live data feed, you can use Excel Power Query to connect to and import this data. Excel VBA scripts or macros can be written to refresh data connections and update the chart periodically. These setups require a fundamental understanding of Excel’s data connection features and possibly some VBA programming knowledge.
How do I format individual bars differently in Excel?
To format individual bars in an Excel chart for emphasis or differentiation, first, click the bar series to select it, then click the specific bar you wish to format a second time to select only that bar. Right-click the selected bar and choose “Format Data Point“. Here, you can adjust fill color, border, and other formatting options for that specific bar. This feature is helpful for highlighting significant data points like maximums, minimums, or outliers.
How can I show the percentage of total for each bar in a chart?
To display the percentage of the total for each bar in an Excel chart, the most straightforward method is to add data labels to your chart and then format these labels to show percentages. Right-click a bar, select “Add Data Labels“, then right-click the newly added labels and choose “Format Data Labels“. From here, select the “Percentage” option under “Label Options” to display each bar’s value as a percentage of the total. Note, this might require your data to be pre-calculated or structured to reflect percentages directly if automatic calculation does not meet your needs specifically.
Can I display both the count and percentage on a single bar graph in Excel?
To display both count and percentage on a single bar graph in Excel, you will likely need to use a combination of direct labeling and leveraging a secondary axis. You can add data labels for the count directly on the bars. Then, calculate percentages in your dataset and plot as a secondary data series against a secondary axis, using a different chart type (like a line) to represent the percentages. This setup allows both metrics to be displayed simultaneously, offering a comprehensive view of the data. Custom formatting of data labels may be required to precisely control the display.
How do I create a bar chart that compares groups of data side by side?
To create a bar chart in Excel that compares groups of data side by side, organize your data into a table with one column for categories and subsequent columns for each group you wish to compare. Ensure each group has its own column with the group name as the column header. When you select this table and insert a bar chart, Excel will automatically format it to display groups of data side by side in clustered bars, facilitating direct comparison between groups within each category.
How can I share my Excel bar chart with others who do not have Excel?
To share an Excel bar chart with individuals who do not have Excel, you can export the chart as an image (PNG, JPEG) or as a PDF by selecting the chart, then going to the “File” menu, choosing “Save As“, and selecting your desired format. For interactive viewing, consider using Excel Online by uploading your workbook to OneDrive or SharePoint; you can then share a link to the workbook. Recipients can view and interact with the chart in their web browser without needing a local installation of Excel.
Related: How to Customize the Ribbon in Microsoft Word, PowerPoint, Excel, and Outlook
For many years now, Microsoft Office has used an intuitive ribbon system to help users easily find and organize the tools they require. Though many are familiar with the interface, fewer realize that you can actually customize the ribbon in MS Word, PowerPoint, Excel, and Outlook. In our other guide, we show you how to customize the ribbon in Microsoft Word, PowerPoint, Excel, or Outlook, including how to add, remove, and rename tabs and items.
Related: How to Structure Collected Data in Excel
Excel is a powerful tool for collecting, analyzing and presenting data. However, to make the most of Excel’s features, you need to structure your data properly. In our other guide, we show you how to structure collected data in Excel using some best practices and tips.
Last Updated on November 7, 2024 9:22 pm CET
