Advertisment

jueves, 8 de septiembre de 2022

How to Make a Chart or Graph in Excel [With Video Tutorial]

Building charts and graphs are one of the best ways to visualize data in a clear and comprehensible way.

However, it's no surprise that some people get a little intimidated by the prospect of poking around in Microsoft Excel.

Download Now: Free Excel Graph Generators

I thought I'd share a helpful video tutorial as well as some step-by-step instructions for anyone out there who cringes at the thought of organizing a spreadsheet full of data into a chart that actually, you know, means something. But before diving in, we should go over the different types of charts you can create in the software.

Types of Charts in Excel

You can make more than just bar or line charts in Microsoft Excel, and when you understand the uses for each, you can draw more insightful information for your or your team’s projects.

Type of Chart

Use

Area

Area charts demonstrate the magnitude of a trend between two or more values over a given period.

Bar

Bar charts compare the frequency of values across different levels or variables.

Column

Column charts display data changes or a period of time.

Line

Similar to bar charts, they illustrate trends over time.

Pie

Pie charts show values as percentages of a whole.

Radar

Radar charts compare the aggregate of multiple data series.

Scatter

Scatter charts show the positive or negative relationship between two variables.

Stock

Stock charts are used to report the fluctuation of stock prices over given periods.

Surface

Surface charts plot sets of values in the form of a three-dimensional surface.

The steps you need to build a chart or graph in Excel are simple, and here’s a quick walkthrough on how to make them.

Keep in mind there are many different versions of Excel, so what you see in the video above might not always match up exactly with what you'll see in your version. In the video, I used Excel 2021 version 16.49 for Mac OS X.

To get the most updated instructions, I encourage you to follow the written instructions below (or download them as PDFs). Most of the buttons and functions you'll see and read are very similar across all versions of Excel.

Download Demo Data | Download Instructions (Mac) | Download Instructions (PC)

1. Enter your data into Excel.

First, you need to input your data into Excel. You might have exported the data from elsewhere, like a piece of marketing software or a survey tool. Or maybe you're inputting it manually.

In the example below, in Column A, I have a list of responses to the question, “Did inbound marketing demonstrate ROI?”, and in Columns B, C, and D, I have the responses to the question, “Does your company have a formal sales-marketing agreement?” For example, Column C, Row 2 illustrates that 49% of people with a service level agreement (SLA) also say that inbound marketing demonstrated ROI.

How to enter data into an excel spreadsheet

2. Choose from the graph and chart options.

In Excel, your options for charts and graphs include column (or bar) graphs, line graphs, pie graphs, scatter plots, and more. See how Excel identifies each one in the top navigation bar, as depicted below:

The types of graphs and charts in excel

To find the chart and graph options, select Insert.

(For help figuring out which type of chart/graph is best for visualizing your data, check out our free ebook, How to Use Data Visualization to Win Over Your Audience.)

3. Highlight your data and insert your desired graph into the spreadsheet.

In this example, a bar graph presents the data visually. To make a bar graph, highlight the data and include the titles of the X and Y-axis. Then, go to the Insert tab and click the column icon in the charts section. Choose the graph you wish from the dropdown window that appears.

How to highlight your data and insert your desired graph into the spreadsheet

I picked the first two dimensional column option because I prefer the flat bar graphic over the three dimensional look. See the resulting bar graph below.

A 2-dimensional chart of data in an excel spreadsheet

4. Switch the data on each axis, if necessary.

If you want to switch what appears on the X and Y axis, right-click on the bar graph, click Select Data, and click Switch Row/Column. This will rearrange which axes carry which pieces of data in the list shown below. When finished, click OK at the bottom.

how to make an excel graph steps: switch the data if necessary

The resulting graph would look like this:

A chart with swapped data on the x and y-axis

5. Adjust your data's layout and colors.

To change the labeling layout and legend, click on the bar graph, then click the Chart Design tab. Here, you can choose which layout you prefer for the chart title, axis titles, and legend. In my example below, I clicked on the option that displayed softer bar colors and legends below the chart.

how to make an excel graph steps: adjust your data's layout and colors

To further format the legend, click on it to reveal the Format Legend Entry sidebar, as shown below. Here, you can change the fill color of the legend, which will change the color of the columns themselves. To format other parts of your chart, click on them individually to reveal a corresponding Format window.

How to use the format legend entry tool to format the legend of an excel chart or graph

6. Change the size of your chart's legend and axis labels.

When you first make a graph in Excel, the size of your axis and legend labels might be small, depending on the graph or chart you choose (bar, pie, line, etc.) Once you've created your chart, you'll want to beef up those labels so they're legible.

To increase the size of your graph's labels, click on them individually and, instead of revealing a new Format window, click back into the Home tab in the top navigation bar of Excel. Then, use the font type and size dropdown fields to expand or shrink your chart's legend and axis labels to your liking.

how to make an excel graph steps: change the size of your chart's legend

7. Change the Y-axis measurement options if desired.

To change the type of measurement shown on the Y axis, click on the Y-axis percentages in your chart to reveal the Format Axis window. Here, you can decide if you want to display units located on the Axis Options tab, or if you want to change whether the Y-axis shows percentages to two decimal places or no decimal places.

how to make an excel graph steps: change the y axis measurement options

Because my graph automatically sets the Y axis's maximum percentage to 60%, you might want to change it manually to 100% to represent my data on a universal scale. To do so, you can select the Maximum option — two fields down under Bounds in the Format Axis window — and change the value from 0.6 to one.

The resulting graph will look like the one below (In this example, the font size of the Y-axis has been increased via the Home tab so that you can see the difference):

How to change the Y-axis measurements in an excel spreadsheet in the minor field

8. Reorder your data, if desired.

To sort the data so the respondents' answers appear in reverse order, right-click on your graph and click Select Data to reveal the same options window you called up in Step 3 above. This time, arrow up and down to reverse the order of your data on the chart.

how to make an excel graph steps: reorder your data

If you have more than two lines of data to adjust, you can also rearrange them in ascending or descending order. To do this, highlight all of your data in the cells above your chart, click Data and select Sort, as shown below. Depending on your preference, you can choose to sort based on smallest to largest, or vice versa.

How to reorder data from smallest to largest for a chart or graph in an excel spreadsheet

The resulting graph would look like this:

how to make an excel graph steps: reordered data

9. Title your graph.

Now comes the fun and easy part: naming your graph. By now, you might have already figured out how to do this. Here's a simple clarifier.

Right after making your chart, the title that appears will likely be "Chart Title," or something similar depending on the version of Excel you're using. To change this label, click on "Chart Title" to reveal a typing cursor. You can then freely customize your chart's title.

When you have a title you like, click Home on the top navigation bar, and use the font formatting options to give your title the emphasis it deserves. See these options and my final graph below:

how to make an excel graph steps: title your graph or chart

10. Export your graph or chart.

Once your chart or graph is exactly the way you want it, you can save it as an image without screenshotting it in the spreadsheet. This method will give you a clean image of your chart that can be inserted into a PowerPoint presentation, Canva document, or any other visual template.

To save your Excel graph as a photo, right-click on the graph and select Save as Picture.

how to make an excel graph steps: export your graph or chart

In the dialogue box, name the photo of your graph, choose where to save it on your computer, and choose the file type you’d like to save it as. In this example, it’s saved as a JPEG to a desktop folder. Finally, click Save.

How to name a graph or chart in excel using the dialogue box

You’ll have a clear photo of your graph or chart that you can add to any visual design.

how to make an excel graph steps: add any additional visual design enhancements to finish

Visualize Data Like A Pro

That was pretty easy, right? With this step-by-step tutorial, you’ll be able to quickly create charts and graphs that visualize the most complicated data. Try using this same tutorial with different graph types like a pie chart or line graph to see what format tells the story of your data best.

Editor's note: This post was originally published in June 2018 and has been updated for comprehensiveness.

excel graph templates



from Marketing https://blog.hubspot.com/marketing/how-to-build-excel-graph

Building charts and graphs are one of the best ways to visualize data in a clear and comprehensible way.

However, it's no surprise that some people get a little intimidated by the prospect of poking around in Microsoft Excel.

Download Now: Free Excel Graph Generators

I thought I'd share a helpful video tutorial as well as some step-by-step instructions for anyone out there who cringes at the thought of organizing a spreadsheet full of data into a chart that actually, you know, means something. But before diving in, we should go over the different types of charts you can create in the software.

Types of Charts in Excel

You can make more than just bar or line charts in Microsoft Excel, and when you understand the uses for each, you can draw more insightful information for your or your team’s projects.

Type of Chart

Use

Area

Area charts demonstrate the magnitude of a trend between two or more values over a given period.

Bar

Bar charts compare the frequency of values across different levels or variables.

Column

Column charts display data changes or a period of time.

Line

Similar to bar charts, they illustrate trends over time.

Pie

Pie charts show values as percentages of a whole.

Radar

Radar charts compare the aggregate of multiple data series.

Scatter

Scatter charts show the positive or negative relationship between two variables.

Stock

Stock charts are used to report the fluctuation of stock prices over given periods.

Surface

Surface charts plot sets of values in the form of a three-dimensional surface.

The steps you need to build a chart or graph in Excel are simple, and here’s a quick walkthrough on how to make them.

Keep in mind there are many different versions of Excel, so what you see in the video above might not always match up exactly with what you'll see in your version. In the video, I used Excel 2021 version 16.49 for Mac OS X.

To get the most updated instructions, I encourage you to follow the written instructions below (or download them as PDFs). Most of the buttons and functions you'll see and read are very similar across all versions of Excel.

Download Demo Data | Download Instructions (Mac) | Download Instructions (PC)

1. Enter your data into Excel.

First, you need to input your data into Excel. You might have exported the data from elsewhere, like a piece of marketing software or a survey tool. Or maybe you're inputting it manually.

In the example below, in Column A, I have a list of responses to the question, “Did inbound marketing demonstrate ROI?”, and in Columns B, C, and D, I have the responses to the question, “Does your company have a formal sales-marketing agreement?” For example, Column C, Row 2 illustrates that 49% of people with a service level agreement (SLA) also say that inbound marketing demonstrated ROI.

How to enter data into an excel spreadsheet

2. Choose from the graph and chart options.

In Excel, your options for charts and graphs include column (or bar) graphs, line graphs, pie graphs, scatter plots, and more. See how Excel identifies each one in the top navigation bar, as depicted below:

The types of graphs and charts in excel

To find the chart and graph options, select Insert.

(For help figuring out which type of chart/graph is best for visualizing your data, check out our free ebook, How to Use Data Visualization to Win Over Your Audience.)

3. Highlight your data and insert your desired graph into the spreadsheet.

In this example, a bar graph presents the data visually. To make a bar graph, highlight the data and include the titles of the X and Y-axis. Then, go to the Insert tab and click the column icon in the charts section. Choose the graph you wish from the dropdown window that appears.

How to highlight your data and insert your desired graph into the spreadsheet

I picked the first two dimensional column option because I prefer the flat bar graphic over the three dimensional look. See the resulting bar graph below.

A 2-dimensional chart of data in an excel spreadsheet

4. Switch the data on each axis, if necessary.

If you want to switch what appears on the X and Y axis, right-click on the bar graph, click Select Data, and click Switch Row/Column. This will rearrange which axes carry which pieces of data in the list shown below. When finished, click OK at the bottom.

how to make an excel graph steps: switch the data if necessary

The resulting graph would look like this:

A chart with swapped data on the x and y-axis

5. Adjust your data's layout and colors.

To change the labeling layout and legend, click on the bar graph, then click the Chart Design tab. Here, you can choose which layout you prefer for the chart title, axis titles, and legend. In my example below, I clicked on the option that displayed softer bar colors and legends below the chart.

how to make an excel graph steps: adjust your data's layout and colors

To further format the legend, click on it to reveal the Format Legend Entry sidebar, as shown below. Here, you can change the fill color of the legend, which will change the color of the columns themselves. To format other parts of your chart, click on them individually to reveal a corresponding Format window.

How to use the format legend entry tool to format the legend of an excel chart or graph

6. Change the size of your chart's legend and axis labels.

When you first make a graph in Excel, the size of your axis and legend labels might be small, depending on the graph or chart you choose (bar, pie, line, etc.) Once you've created your chart, you'll want to beef up those labels so they're legible.

To increase the size of your graph's labels, click on them individually and, instead of revealing a new Format window, click back into the Home tab in the top navigation bar of Excel. Then, use the font type and size dropdown fields to expand or shrink your chart's legend and axis labels to your liking.

how to make an excel graph steps: change the size of your chart's legend

7. Change the Y-axis measurement options if desired.

To change the type of measurement shown on the Y axis, click on the Y-axis percentages in your chart to reveal the Format Axis window. Here, you can decide if you want to display units located on the Axis Options tab, or if you want to change whether the Y-axis shows percentages to two decimal places or no decimal places.

how to make an excel graph steps: change the y axis measurement options

Because my graph automatically sets the Y axis's maximum percentage to 60%, you might want to change it manually to 100% to represent my data on a universal scale. To do so, you can select the Maximum option — two fields down under Bounds in the Format Axis window — and change the value from 0.6 to one.

The resulting graph will look like the one below (In this example, the font size of the Y-axis has been increased via the Home tab so that you can see the difference):

How to change the Y-axis measurements in an excel spreadsheet in the minor field

8. Reorder your data, if desired.

To sort the data so the respondents' answers appear in reverse order, right-click on your graph and click Select Data to reveal the same options window you called up in Step 3 above. This time, arrow up and down to reverse the order of your data on the chart.

how to make an excel graph steps: reorder your data

If you have more than two lines of data to adjust, you can also rearrange them in ascending or descending order. To do this, highlight all of your data in the cells above your chart, click Data and select Sort, as shown below. Depending on your preference, you can choose to sort based on smallest to largest, or vice versa.

How to reorder data from smallest to largest for a chart or graph in an excel spreadsheet

The resulting graph would look like this:

how to make an excel graph steps: reordered data

9. Title your graph.

Now comes the fun and easy part: naming your graph. By now, you might have already figured out how to do this. Here's a simple clarifier.

Right after making your chart, the title that appears will likely be "Chart Title," or something similar depending on the version of Excel you're using. To change this label, click on "Chart Title" to reveal a typing cursor. You can then freely customize your chart's title.

When you have a title you like, click Home on the top navigation bar, and use the font formatting options to give your title the emphasis it deserves. See these options and my final graph below:

how to make an excel graph steps: title your graph or chart

10. Export your graph or chart.

Once your chart or graph is exactly the way you want it, you can save it as an image without screenshotting it in the spreadsheet. This method will give you a clean image of your chart that can be inserted into a PowerPoint presentation, Canva document, or any other visual template.

To save your Excel graph as a photo, right-click on the graph and select Save as Picture.

how to make an excel graph steps: export your graph or chart

In the dialogue box, name the photo of your graph, choose where to save it on your computer, and choose the file type you’d like to save it as. In this example, it’s saved as a JPEG to a desktop folder. Finally, click Save.

How to name a graph or chart in excel using the dialogue box

You’ll have a clear photo of your graph or chart that you can add to any visual design.

how to make an excel graph steps: add any additional visual design enhancements to finish

Visualize Data Like A Pro

That was pretty easy, right? With this step-by-step tutorial, you’ll be able to quickly create charts and graphs that visualize the most complicated data. Try using this same tutorial with different graph types like a pie chart or line graph to see what format tells the story of your data best.

Editor's note: This post was originally published in June 2018 and has been updated for comprehensiveness.

excel graph templates

via Perfecte news Non connection

No hay comentarios:

Publicar un comentario