How to Create Charts and Graphs in Microsoft Excel

Edited 2 months ago by ExtremeHow Editorial Team

Microsoft ExcelData VisualizationReportingSpreadsheetBusinessMacWindowsGraphics

Creating charts and graphs in Microsoft Excel is a basic skill that can visualize data and make it understandable at a glance. Charts allow you to display data visually, showing trends, patterns, and outliers, while graphs enable you to plot data points in a coordinate system for a more analytical view. This guide explains how to create and customize charts and graphs in Excel. We'll cover everything from basic steps to more advanced tips and tricks. Let's dive into the world of Excel charts and graphs.

Understanding different types of charts and graphs

Before we begin, it's essential to know what types of charts and graphs Excel offers and when to use them:

• Column charts: Ideal for comparing values across different categories, especially over time.
• Bar chart: Similar to a column chart, but uses horizontal bars. This is useful when category names are long.
• Line chart: Shows trends over time by connecting data points with lines. Ideal for frequency data.
• Pie chart: It displays data as percentage and shows the ratio between different parts.
• Scatter plot: Helps to identify the relationship between two variables plotted on the XY axis.
• Area charts: Display volume changes over time as shown by line charts. They are useful for showing volume trends.
• Bubble chart: A form of scatter plot with the additional dimension of the data shown in the size of the markers.

Getting started: preparing your data

Before creating any chart, make sure your Excel data is properly prepared. Your data should be organized into rows and columns with proper headers. Each column should represent a variable, and each row should represent a record.

Example of well-formed data:

| Month    | Sales | Profit |
| January  | 5000  | 400    |
| February | 7000  | 450    |
| March    | 12000 | 720    |

Steps to create a basic chart

1. Select your data: Highlight the range of data you want to chart. Remember to include headers for clarity.
2. Insert a chart: Click the "Insert" tab in the Excel ribbon. Look for the "Charts" group where you'll find different chart types. Choose the chart type that best matches your data and objectives.
3. Customize your chart: Once your chart is inserted, you can customize various elements such as the chart title, axis titles, and data labels. Use the "Chart Tools" that appears in the Excel ribbon when your chart is selected to facilitate customization.

Customizing your chart

Add titles and labels

• Chart title: Click the chart area, go to the “Chart Tools Layout” tab, and select “Chart Title” to add or edit a chart title.
• Axis titles: Under “Chart Tools Layout,” select “Axis Titles” to add titles to the axes, making your chart easier to understand.
• Data Labels: Display the values on your chart by selecting a “Data Label” for each data point for easier readability.

Changing the chart type

If your chart type doesn't accurately represent your data, change it as follows:

• Right-click on the chart area and select "Change Chart Type."
• Select a new chart type from the available list.

Formatting elements

Make your chart more attractive by formatting chart elements.

• Colors and Styles: Use the “Chart Styles” option under Chart Tools to select different colors and style templates.
• Gridlines: Add or remove gridlines to improve its readability. Click “Chart Tools Layout” and select “Gridlines” to customize.

Advanced charting techniques

Using a combo chart

Sometimes a single chart type isn't enough to express complex data. This is where combo charts come in. Follow these steps to create a combo chart:

1. Insert two different types of charts for your data.
2. Use the "Combo Chart" option under the "Insert" tab to combine two charts for comparative analysis by combining them and selecting the "Secondary Axis" for one of the datasets.

Working with dynamic data

To keep your chart updated automatically when the data changes:

• Use dynamic named ranges that automatically expand as more data is added.
• Include the dynamic range name in your chart source data.
• Use Excel tables, because charts based on tables update automatically with changes in the data.

Adding a trendline

• Select the data series in your chart to which you want to add a trendline.
• Go to “Chart Tools Layout” and select “Trendline Options”.
• Select the trendline that best suits your data collection to analyze or show trends.

Create a specific chart

Scatter and bubble charts

Scatter plots and bubble charts provide valuable information about the relationships between variables.

• Scatter Plot: Useful for data with correlation properties. Simply select your X and Y values and insert a scatter plot.
• Bubble charts: Provide an additional dimension, that is, the size of the bubble represents another variable. Select and combine three data columns for x, y, and bubble size.

Pie and doughnut charts

To show percentage values of categories or parts of a whole:

• Use a "Pie Chart" to display individual category values as a part of an entire data set.
• A "doughnut chart" provides similar functionality but allows multiple data series.

Tips for creating effective charts and graphs

• Always choose the chart type that best suits your data and purpose. Simplicity often communicates data more effectively than complex graphics.
• Ensure clear labeling to help viewers accurately interpret the data.
• Maintain a balance between visual appeal and readability by using appropriate colors, themes, and fonts.

Conclusion

Creating and customizing charts and graphs in Microsoft Excel can greatly improve the way you present your data. Whether showing sales trends, predicting future growth areas, or analyzing relationships between variables, Excel charts are powerful tools. Customization options allow you to create attractive and clear representations of your data. By following the steps and tips in this guide, you should master creating charts in Excel.

  Share This Article

If you find anything wrong with the article content, you can request an update