How Do You Make and Plot Graphs in Excel?

Summary: This blog provides a comprehensive guide on how to make and plot graphs in Excel, covering various graph types, data preparation, and customisation techniques. It emphasises the importance of effective data visualisation for clearly communicating trends and insights, ensuring users can easily create informative charts.

Introduction

Visual data representation is crucial for understanding trends, patterns, and insights in today’s data-driven world. Graphs make complex data accessible, allowing quick decision-making. This blog will teach you how to make and plot graphs in Excel, empowering you to create clear, informative charts. 

Excel is a top choice for graphing due to its versatility, ease of use, and wide range of chart options. With a market share of 10.15% in the productivity market and 2.95% in document management, Microsoft Excel remains a go-to tool for professionals across industries, ensuring effective data analysis and presentation.

Key Takeaways

• Choose the right graph type based on your data’s story.
• Organise data clearly in rows and columns for accuracy.
• Customise graphs for better readability and presentation.
• Utilise advanced features like combo charts and secondary axes.
• Follow best practices to avoid common graphing mistakes.

Understanding the Types of Graphs in Excel

When working with data in Excel, choosing the right type of graph is essential to communicate your information. Excel offers a variety of graph types, each suited to different data visualisation needs. 

Understanding the purpose of each graph type helps you present data in the most meaningful way. Below are the most common graph types and when to use them.

Line Graph

Line graphs are ideal for showing trends over time. They connect data points with a line, making them great for visualising value changes over continuous periods, such as months, years, or hours. Line graphs often display stock market trends, sales growth, or temperature fluctuations.

Bar Chart

Bar charts use rectangular bars to represent data. The length of the bar corresponds to the value it represents, making bar charts useful for comparing different categories. These charts are most effective when comparing distinct groups, such as sales performance by region or product comparisons.

Pie Chart

Pie charts display data as slices of a circle, showing the proportion of each category concerning the whole. This chart type is useful for demonstrating relative percentages. Use a pie chart to highlight how individual components make up a total, such as market share or survey results.

Scatter Plot

Scatter plots use dots to represent values on two axes, helping to identify relationships or patterns between variables. This chart type is commonly used for statistical analysis, showing correlations such as the relationship between advertising spend and sales performance.

Area Chart

Area charts are similar to line graphs but fill the area beneath the line with colour. This chart type is useful for visualising the cumulative magnitude of trends over time. It’s often used to show the total value of multiple data series and how each contributes to the total.

Other Types

Excel offers many other graph types, such as column charts and histograms. Column charts are like bar charts but with vertical bars, and histograms display data distribution across specified intervals. These graphs are ideal for visualising frequency distributions or comparing categories differently.

Preparing Your Data

Before you can start creating graphs in Excel, properly organising your data is crucial. A well-organised dataset makes the graph creation process smoother and ensures that your graphs are accurate and easy to interpret. Here’s how to prepare your data for graphing:

Organising Data in Rows and Columns

The first step in preparing your data is to arrange it in a logical format. Excel uses rows and columns to organise data, with each row representing a record and each column representing a variable or category. 

For example, if you plot sales data over months, list the months in one column and the sales figures in the next column.

Ensure that your data is well-structured by using clear headings for each column. This will help Excel recognise the data’s relationship when you create your graph. Also, avoid leaving blank cells in your dataset, which can lead to errors when plotting your graph.

Ensuring Data Consistency for Better Graphing

Data consistency is key when preparing to plot graphs. All values within a column should follow the same format. For example, if you’re working with dates, ensure all date entries are in the same format. If you plot numerical data, ensure that units (e.g., dollars, kilograms) are consistent throughout.

Inconsistent data can result in misleading graphs or errors. Double-check for outliers, missing values, or data entries that don’t match the rest. Excel’s built-in functions like “Find & Replace” can help spot and correct inconsistencies quickly.

Highlighting Key Data for Graph Creation

Once your data is organised and consistent, highlight the key information you want to graph. Select the data range that includes the variables you need for your graph, such as the periods and the corresponding values. 

Include any labels or headings, as these will be used as chart titles and axis labels in your final graph.

Highlighting the relevant data ensures that Excel only graphs what you need, preventing unnecessary data from cluttering the chart and making it harder to interpret.

How to Create a Basic Graph in Excel

Creating a basic graph in Excel is straightforward, and with just a few steps, you can turn your raw data into a clear visual representation. In this section, we’ll walk through the process of selecting data, inserting a chart, and customising it for clarity and ease of understanding.

Step 1: Organise Your Data

Before you can create a graph, ensure your data is structured correctly. Typically, data should be organised in rows and columns. Each column or row should represent a category (e.g., months, product names, or regions), and each data point should align with these categories. 

For example, when plotting monthly sales, place the months in one column and the sales figures in the adjacent column.

Excel automatically detects the structure of your data, so ensuring it’s adequately formatted is key to creating an accurate graph.

Step 2: Select the Data Range

Now, highlight the data you want to include in your graph. This can be done by clicking and dragging over your data’s cells. Select the categories (e.g., months or product names) and the numerical values (e.g., sales or quantities). 

For example, if you plot monthly sales for a year, select the entire range of months and sales figures.

Selecting the correct data range is crucial because Excel uses this range to plot the graph. The graph may not display as expected if your selection is too large or too small.

Step 3: Insert a Graph via the Ribbon

With your data selected, it’s time to insert a graph. Here’s how to do it:

• Go to the Ribbon: In Excel, the Ribbon is at the top of the screen and contains all the necessary commands. Click on the Insert tab.
• Choose a Chart Type: Within the Insert tab, you will see several options under the Charts section. Common choices for basic graphs include:
  • Bar Chart: Ideal for comparing quantities across different categories.
  • Line Chart: Perfect for showing trends over time.
  • Column Chart: Great for visualising data across categories.
• Select the Chart Style: Click on the desired chart type (e.g., Bar Chart), and a drop-down menu will show different styles. Choose the one that best fits your data and visual preference.
• Insert the Chart: After selecting the style, click on it, and Excel will automatically generate the graph based on your data selection.

Once the chart appears, it will be embedded directly into your worksheet, ready for further customisation.

Customising Graphs

Once you’ve created a graph in Excel, the next step is customising it to represent your data better and make it more visually appealing. Customisation options allow you to adjust various elements of your graph to suit your presentation style or the specific information you want to highlight. 

Here are some common ways to modify your graph in Excel.

Changing Chart Styles and Colours

Excel provides a wide range of chart styles and colour palettes to help enhance the look of your graph. To change the chart style, click on the chart to activate the “Chart Tools” section in the ribbon. Then, under the “Design” tab, you’ll find options for different chart styles and colour schemes. 

Select a pre-set style that adjusts the layout, colours, and overall design to suit your data. Alternatively, click “Change Colors” to choose a custom colour palette for your graph elements, such as the bars, lines, or slices.

Adjusting Axis Labels and Titles

Axis labels and titles are crucial for making your graph easy to understand. To add or adjust these labels, click on the chart, then navigate to the “Chart Elements” button (the plus sign icon) on the right side. 

You can toggle the “Axis Titles” checkbox to add titles for both the X and Y axes. Once the titles appear, simply click on them to edit the text. Ensure the titles clearly describe the variables represented by each axis.

For the chart title, click on the default title and change it to a more meaningful one that describes the graph’s content. Using the “Home” tab in the ribbon, you can further format these titles by adjusting the font size, style, and alignment.

Modifying Legends and Gridlines

Legends and gridlines provide context to your chart, making it easier to interpret. To modify the legend, click on the chart and use the “Chart Elements” button to toggle the “Legend” on or off. For better placement, you can also choose its position (top, bottom, right, or left).

Gridlines help to align data points with axis values visually. To adjust the gridlines, click the chart, go to the “Chart Elements” button, and check or uncheck the “Gridlines” box. You can choose to show major or minor gridlines or even customise the appearance of gridlines by right-clicking on them and selecting “Format Gridlines.”

Adding Data Labels

Data labels are a great way to display specific values directly on your chart, making it easier for your audience to see the exact figures. To add data labels, click on the chart, go to the “Chart Elements” button, and select “Data Labels.” 

Depending on your design preference, you can choose to display labels inside or outside the chart. For further customisation, you can format the labels by changing the font style, size, or number format to make them more readable.

Using these customisation features, you can create a more professional and informative graph highlighting key data points and making your chart easier to understand.

Advanced Graph Features

When you’ve mastered the basics of creating graphs in Excel, it’s time to explore advanced features that can make your visualisations more dynamic and informative. These features allow you to present complex data better, compare multiple variables, and highlight key insights. In this section, we’ll look at some powerful tools that will enhance your graphing capabilities.

Adding Secondary Axes

Sometimes, you may want to compare two data sets with vastly different ranges in the same chart. This is where secondary axes come in handy. By adding a secondary axis to your graph, you can display two sets of data with varying scales of value.

To add a secondary axis:

• Create your chart.
• Right-click on the data series you want to move to the secondary axis.
• Select “Format Data Series” and check the box for “Secondary Axis.”

This is especially useful when comparing data points like sales and temperature, where the scales are vastly different but need to be visualised together. The secondary axis helps ensure each dataset is represented accurately and clearly.

Creating Combo Charts

A combo chart allows you to combine different charts, like a bar chart and a line chart, into one. This is useful when highlighting relationships between multiple variables that behave differently. For example, you might use a bar chart for sales figures and a line chart to show trends over time.

To create a combo chart:

• Select the data for your chart.
• Go to the “Insert” tab and choose “Combo Chart” from the Chart options.
• Choose the chart types for each data series.

Combo charts help create visually appealing graphs that display the correlation between data sets.

Using Trendlines and Error Bars

Trendlines and error bars are essential for analysing data trends and showing variability. Trendlines help you identify patterns in your data, such as upward or downward trends, and can be applied to line, scatter, or bar charts. Error bars, on the other hand, show the variability or uncertainty in your data points.

To add a trendline:

• Click on a data point in your chart.
• Right-click and select “Add Trendline.”
• Choose the type of trendline (linear, exponential, etc.) that best fits your data.

Error bars are similarly easy to add by selecting the “Error Bars” option from the “Chart Elements” menu.

Creating Dynamic Graphs Using PivotTables

Dynamic graphs are perfect when updating your charts based on changing data. PivotTables are a powerful way to summarise and analyse large datasets, and when combined with charts, they create dynamic, interactive visuals.

To create a dynamic graph:

• Insert a PivotTable to summarise your data.
• Create a chart based on the PivotTable.
• As you update the PivotTable (filter, sort, or add new data), the chart will automatically adjust.

Dynamic graphs enable you to track and analyse trends in real time without manually adjusting your chart each time the data changes.

By mastering these advanced graph features, you can create more sophisticated and insightful visualisations, which will help you better understand and communicate your data.

Tips for Effective Data Visualisation

Creating effective data visualisations goes beyond simply plotting graphs in Excel. The goal is to convey information clearly and engagingly. Here are some essential tips to ensure your charts deliver the right message.

Choosing the Right Graph for Your Data

The first step in creating an effective graph is selecting the right type. Different datasets require different visualisations. A line graph or area chart is often best for time-based data, as it shows trends over time.

If comparing quantities across categories, opt for a bar or column chart. A pie chart works well for percentages or parts of a whole. Scatter plots are ideal for showing relationships between variables. Always match the chart type to the data’s story to clarify your insights.

Avoiding Common Mistakes in Graph Creation

Many graphs become ineffective due to common mistakes. One common error is overloading the graph with too much information. Limit the number of data points or categories displayed to keep your data concise and avoid clutter. 

Another mistake is misleading axis scales—ensure that your axis starts at zero or adjust it appropriately to avoid distorting the data’s message. Inconsistent labelling is problematic, so use clear and consistent titles, axis labels, and legends. 

Lastly, avoid using too many colours, as it can make your graph look chaotic and more challenging to interpret.

Making Your Graph More Readable

A clear graph is easy to read at a glance. To achieve this, focus on font sizes and colour contrast. Use a larger font for titles and axis labels, and choose legible, professional fonts. 

Stick to a maximum of two or three colours for clarity. Ensure that the colours contrast well so the data stands out, especially when presenting in a crowded setting. 

Avoid using colours that may be hard to distinguish for those with colour vision deficiencies. When appropriate, use bold or italic text to emphasise key points.

By following these tips, you can create more effective, visually appealing graphs that enhance the clarity of your data.

Bottom Line

Creating and plotting graphs in Excel is essential for effective data visualisation. By understanding various graph types and mastering the tools available in Excel, users can transform raw data into clear, informative visuals. This skill enhances data analysis and presentation, making it easier to communicate insights and trends.

Frequently Asked Questions

What Types of Graphs Can I Create in Excel?

Excel offers various graph types, including line graphs, bar charts, pie charts, scatter plots, and area charts. Each type serves different visualisation needs, such as showing trends or comparing categories.

How do I Prepare My Data for Graphing in Excel?

Organise your data in rows and columns with clear headings. Ensure consistency in data formats and avoid blank cells to facilitate accurate graph creation.

Can I Customise My Graphs in Excel?

Yes, Excel allows extensive customisation options. You can change chart styles, colours, axis labels, and titles and add data labels to enhance clarity and presentation.