How to Create Excel Charts and Graphs

Excel charts and graphs are tried-and-true instruments for visualizing data clearly and understandably. However for individuals who aren’t native tech gurus, it may be a bit intimidating to poke round in Microsoft Excel.

I’m right here to share the foundational info you want, useful video tutorials, and step-by-step directions for anybody feeling like they’re in over their heads.

Organizing a spreadsheet full of knowledge into an correct and engaging chart isn’t sorcery — you are able to do it! Let’s go over the method from A to Z.

What an Excel Chart or Graph is — and Why to Use Them

The very first thing to know is which you can create several types of charts and graphs within the software program.

The distinctive info in your information set(s) and the viewers you’re speaking to are components that go into selecting the suitable chart or graph in your venture, so let’s chat charts.

However why use them? Do you want to visualise information when you’ll be able to simply clarify it? The reply is usually sure if you wish to assist an viewers perceive and retain the related findings.

I’ll always remember a specific graph from a TED talk I saw on parenting taboos — and I’ve by no means seen something play out so precisely in my very own life, both:

Image Source

This chart from Rufus Griscom + Alisa Volkman exhibits a imply line of happiness in blue and extra of a moment-by-moment breakdown in yellow of the information factors that construct the imply you see.

The highs and lows are usually much less erratic throughout single maturity as a result of you could have extra management over your surroundings and circumstances, they clarify, however after you have youngsters? Chaos.

They joked about Grand, explosive moments of affection accompanied by mind-numbing, soul-punching lows — normally round bedtime routines. (It’s no joke.)

From this, we will glean that it’s at all times a good suggestion to distill the knowledge into one thing visually digestible so you’ll be able to talk clearly and effectively.

The very last thing you need is to lose your goal’s consideration in a sea of incomprehensible numbers.

Particularly for big information units, an Excel chart or graph will get to the guts of your findings in a method that’s simple to see and perceive at a single look, particularly whenever you incorporate comparisons.

In case your information has a couple of discovering to speak — equivalent to a comparability or if you wish to illustrate modifications going down over time — Excel charts and graphs supply a number of choices for creating impactful visuals.

By the top, you’ll have some concepts about which charts may enable you to inform the tales contained inside your information.

 

The 18 Kinds of Charts in Excel (So Far)

Whew, Microsoft has been busy! Final I did a deep dive, there have been solely 9 forms of charts, so it has doubled in the previous couple of years — which is good news for analysis communicators.

Once you perceive their makes use of, you’ll be able to current materials optimized to be extremely beneficial and insightful in your workforce’s initiatives.

We’ll go over the most effective, tried-and-true choices completely. Then, on the finish, I’ll briefly summarize the superior chart varieties and those who will not be as helpful to entrepreneurs.

Excel Charts Most Helpful to Entrepreneurs

1. Space Chart

Image Source

Excel space charts mean you can see tendencies over time — or another related variable. It’s basically a line graph with colored-in sections emphasizing development and giving a way of quantity.

You may also use stacked space charts. This denser space chart permits you to present extra info directly, equivalent to evaluating tendencies in a number of classes or monitoring modifications throughout completely different variables.

Finest for: Demonstrating the magnitude of a pattern between two or extra values over a given interval.

2. The New and Improved Bar Graph (Now Known as a Clustered Bar Graph)

Image Source

An Excel bar graph represents info horizontally and compares completely different information collection. You may simply see the proportions between numerous classes or components of your information.

As an illustration, you should use clustered bar graphs to match the gross sales of various merchandise, for instance, in different retailer places over months or quarters.

This may help you perceive which merchandise promote properly in numerous geographies throughout the identical timeframe.

Finest for: Evaluating the frequency of comparable values between completely different variables.

3. Ditto for Column Charts (Renamed Clustered Column Charts)

Image Source

Column charts are much like bar graphs, however they differ in a single important method: they’re vertical, not horizontal. The vertical orientation lends itself to serving to viewers rank completely different information components.

Like bar graphs, column charts evaluate information, show tendencies, and present proportions.

As an illustration, if you wish to rank your gross sales groups’ numbers in numerous states throughout 1 / 4, you’ll be able to visualize them in a clustered column chart and see which workforce in every state is within the lead — the tallest within the cluster. 

You may also see which workforce is main amongst all states — the tallest amongst all clusters.

Finest for: Displaying numerous information components over a while to rank them visually.

Professional tip: I’ve personally realized that column charts displaying T-bars of statistical significance are extraordinarily helpful in serving to individuals in management dispel doubtless however in the end unfaithful interpretations of knowledge.

Generally, information displaying significant change continues to be inside regular parameters. Generally, what looks as if a slight distinction is important.

Managers and administrators could need assistance seeing these realities in order that they don’t oversteer at choice time.

4. Line Graph

Image Source

A line graph is a straightforward however extremely efficient method to see tendencies over time at a look — even with out the frills of bars, columns, or additional shading. You may also evaluate a number of information collection.

As an illustration, the variety of natural visits from Google versus Bing over a 12-month interval.

You may also see the speed or pace at which the information set modifications. In our Google vs. Bing instance, a steep incline would imply you had a sudden spike in natural site visitors. A extra gradual decline implies that your site visitors is slowly lowering.

Finest for: Illustrating tendencies over time, equivalent to spikes or drops in gross sales on account of holidays, climate, or different variables.

5. Pie Chart

Image Source

A pie chart is a useful method of seeing how completely different information components proportionally evaluate to 1 one other. Curious what proportion of your natural site visitors is from Google versus Bing?

Or how a lot market share do you could have in comparison with rivals? A pie chart is a becoming method to visualize that info.

It’s additionally an effective way to see and talk progress towards a selected aim.

As an illustration, in case your aim is to promote a product day by day for 30 days in a row, then you definately may create a pie chart with 30 slices and shade a slice every day you promote the product.

Finest for: Displaying values as percentages of a complete and viewing information components proportionately.

6. Radar Chart

Image Source

A radar chart may look acquainted to you in the event you’ve ever taken a persona check, but it surely’s additionally helpful outdoors of that trade. Radar charts show information in a closed, multi-pointed form.

Every level is known as a spoke, and a number of variables “pull” spokes of the form. Then, shapes will be stacked up for comparability.

Such a chart is well-designed for evaluating completely different information components, equivalent to attributes, entities, individuals, strengths, or weaknesses. It additionally helps you see the distribution of your information and perceive if it is overly skewed a technique or one other.

Finest for: Evaluating the mixture values of a number of information collection directly.

7. Scatter Plot

Image Source

Scatter plots look much like line graphs however with one important distinction: They consider the connection between two variables, proven on the X- and Y-axes. This allows you to establish correlations and patterns between them.

As an illustration, you may evaluate the quantity of natural site visitors (X-axis) with the variety of leads and signups (Y-axis).

For those who see an upward pattern within the dots the place these two converge, then you definately’ll have an concept of how a rise in natural site visitors impacts your leads and signups.

If in case you have a leads/signups aim, you’ll be able to then create a extra data-driven plan for rising your natural site visitors.

You may even take it a step additional to match the variety of leads and signups with every day gross sales or conversions to maintain extra packages on data-driven paths.

Finest for: Visualizing optimistic or detrimental relationships between two variables.

8. Funnel Chart

Image Source

Funnel charts are extraordinarily well-suited to entrepreneurs who need to optimize processes and pipelines.

Within the picture above, it’s clear that you simply drop essentially the most candidates between Certified Prospects and Wants Evaluation, in order that portion of your funnel could also be attention-grabbing to look into extra deeply to grasp why.

Finest for: Visually representing modifications by processes helps to make clear the place the largest modifications happen alongside the best way.

Professional tip: My expertise has taught me that in the event you solely use two ranges — particularly if there’s not an important change between them, it’s simple to mistake this for a bar graph, which capabilities utterly otherwise.

You’ll undoubtedly need to use at the very least three ranges so it’s extra clearly distinguished as a funnel form.

9. Histogram Chart

Image Source

Histograms are a stable possibility whenever you need to clarify information that happens most usefully in ranges. Let’s say that you simply need to present your purchasers the shopping for habits of varied age demographics in your product area of interest.

It’s possible you’ll discover that your target market has moved, probably even jumped a variety up or down.

In case your shopper has bought child merchandise for the final 100 years, you’d undoubtedly see that the target market of first-time dad and mom is getting older as individuals wait longer to have youngsters.

This may occasionally change your advertising methods to satisfy the wants and problems with this older first-time dad or mum demographic.

Finest for: Demonstrating information findings which are most noticeable and helpful when the information is grouped in ranges.

 

Superior Excel Charts

It’s good so that you can know that the next chart varieties exist, however they will not be the most effective place to start out in the event you arrived right here at present a bit overwhelmed.

They’re extra sophisticated and higher suited to audiences who can already learn advanced-level charts:

Business-Particular Excel Charts

The remaining Excel chart varieties don’t sometimes lend themselves to advertising. However, hey — in case your area of interest requires it, these charts are there to assist you:

Summarizing the Charts

That was a ton of knowledge. For those who’re nonetheless undecided which to decide on, right here’s a concise comparability of the Excel charts I discover to be most helpful to entrepreneurs:

Kind of Chart	Use
Space	Space charts reveal the magnitude of a pattern between two or extra values over a given interval.
Clustered Bar	Clustered bar charts evaluate the frequency of values throughout completely different ranges or variables.
Clustered Column	Clustered column charts show information modifications over a time frame to clarify visualizations of rank amongst information units.
Line	Much like bar charts, they illustrate tendencies over time.
Pie	Pie charts present values as percentages of a complete.
Radar	Radar charts evaluate the mixture worth of a number of information collection.
Scatter	Scatter charts present the optimistic or detrimental relationship between two variables.
Funnel	Funnel charts excel at visualizing modifications to 1 information level over numerous processes.
Histogram	Histograms present variations in information which are greatest represented as a variety of values.

For those who’re searching for a deeper dive that can assist you determine which sort of chart/graph is greatest for visualizing your information, take a look at our free e book, How to Use Data Visualization to Win Over Your Audience.

How one can Create a Graph in Excel

The steps to construct a chart or graph in Excel are comparatively easy. I encourage you to comply with the step-by-step directions under or obtain them as PDFs if that’s extra environment friendly for you.

Many of the buttons and capabilities you may see and browse are very related throughout all variations of Excel.

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

Featured Resource: Free Excel Graph Templates

Earlier than we soar in, it’s time for one more professional tip. You needn’t begin from scratch. You’re welcome to make use of these free Excel Graph Generators. Simply enter your information and alter as wanted for a fantastic information visualization.

It’s an important time-saver in the event you don’t want one thing as customized as constructing your Excel charts and graphs up from zero.

Once you do have to create and customise from the very begin, right here’s the way to sort out it:

1. Enter your information into Excel.

First, that you must enter your information into Excel. You might need exported the information from elsewhere, like a bit of marketing software or a survey software — or possibly you are inputting it manually from spreadsheets. I don’t decide!

Within the instance under, in Column A, I’ve an inventory of responses to the query, “Did inbound advertising reveal ROI?” and in Columns B, C, and D, I’ve the responses to the query, “Does your organization have a proper sales-marketing settlement?”

For instance, Column C, Row 2 illustrates that 49% of individuals with a service level agreement (SLA) additionally say that inbound advertising demonstrated ROI.

2. Select from the graph and chart choices.

In Excel, your choices for charts and graphs embody column (or bar) graphs, line graphs, pie graphs, scatter plots, and extra. See how Excel identifies each within the high navigation bar, as depicted under:

To seek out the chart and graph choices, choose Insert.

3. Spotlight your information and insert your required graph into the spreadsheet.

On this instance, a bar graph presents the information visually. To make a bar graph, spotlight the information and embody the titles of the X- and Y-axis. Then, go to the Insert tab and click on the column icon within the charts part.

Select the graph you would like from the dropdown window that seems.

I picked the primary two-dimensional column possibility as a result of I choose the flat bar graphic over the three-dimensional look. See the ensuing bar graph under.

Nonetheless, if I have been reporting statistics about skyscrapers to a builder’s union, I’d undoubtedly choose the three-dimensional look to customise the chart to appear like constructed constructions.

There are optimization selections to make alongside the best way to greatest suit your information and viewers.

4. Change the information on every axis, if essential.

If you wish to change what seems on the X and Y axis, right-click on the bar graph, click on Choose Knowledge, and click on Change Row/Column. It will rearrange which axes carry which items of knowledge within the record proven under.

Generally, you’ll see that your info simply presents higher a technique versus the opposite. On this case, I believe the primary X and Y orientation is simpler and less complicated to grasp.

Take note, although, that it’s not about me.

If the presentation focuses on SLAs and government selections on whether or not or to not safe one, a Sure/No SLA configuration (the second XY orientation) is probably going a greater match for the viewers and their wants. When completed, click on OK on the backside.

The ensuing graph would appear like this:

5. Modify your information’s structure and colours.

To vary the labeling structure and legend, click on on the bar graph, then click on the Chart Design tab. Right here, you’ll be able to select which structure you like for the chart title, axis titles, and legend.

In my instance under, I clicked on the choice that displayed softer bar colours and legends under the chart.

To additional format the legend, click on on it to disclose the Format Legend Entry sidebar, as proven under. Right here, you’ll be able to change the fill coloration of the legend, which is able to change the colour of the columns themselves.

To format different components of your chart, click on on them individually to disclose a corresponding Format window.

6. Change the scale of your chart’s legend and axis labels.

Once you first make a graph in Excel, the scale of your axis and legend labels could be small, relying on the graph or chart you select (bar, pie, line, and so on.)

When you‘ve created your chart, you’ll need to measurement these labels up in order that they’re legible.

To extend the scale of your graph’s labels, click on on them individually and, as an alternative of unveiling a brand new Format window, click on again into the House tab within the high navigation bar of Excel.

Then, use the font sort and measurement dropdown fields to develop or shrink your chart’s legend and axis labels to your liking.

7. Change the Y-axis measurement choices if desired

To vary the kind of measurement proven on the Y-axis, click on on the Y-axis percentages in your chart. This reveals the Format Axis window.

Right here, you’ll be able to resolve if you wish to show items situated on the Axis Choices tab. You may change whether or not the Y-axis exhibits percentages to 2 decimal locations or no decimal locations.

As a result of my graph routinely units the Y axis’ most proportion to 60%, you may need to change it manually to 100% to symbolize information on a common scale.

To take action, you’ll be able to choose the Most possibility — two fields down below Bounds within the Format Axis window — and alter the worth from 0.6 to 1.

The ensuing graph will appear like the one under (On this instance, the font measurement of the Y-axis has been elevated by way of the House tab as a way to see the distinction):

8. Reorder your information, if desired.

To type the information so the respondents’ solutions seem in reverse order, right-click in your graph and click on Choose Knowledge to disclose the identical choices window you known as up in Step 3 above.

This time, arrow up and all the way down to reverse the order of your information on the chart.

If in case you have greater than two strains of knowledge to regulate, you can too rearrange them in ascending or descending order. To do that, spotlight your whole information within the cells above your chart, click on Knowledge, and choose Type, as proven under.

Relying in your desire, you’ll be able to select to type primarily based on smallest to largest or vice versa.

The ensuing graph would appear like this, which is tremendously higher. Why? As a result of it exhibits your viewers the development of outcomes and turns into visually persuasive.

9. Title your graph.

Now comes the enjoyable and straightforward half: naming your graph. By now, you might need already discovered how to do that. This is a easy clarifier.

Proper after making your chart, the title that seems will doubtless be “Chart Title” or one thing related, relying on the model of Excel you‘re utilizing. To vary this label, click on on “Chart Title” to disclose a typing cursor.

You may then freely customise your chart’s title.

When you could have a title you want, click on House on the highest navigation bar, and use the font formatting choices to offer your title the emphasis it deserves. See these choices and my last graph under:

10. Export your graph or chart.

As soon as your Excel chart or graph is precisely the best way you need it, it can save you it as a picture with out screenshotting it within the spreadsheet.

This technique offers you a clear picture of your chart that may be inserted right into a PowerPoint presentation, Canva doc, or another visible template.

To save lots of your Excel graph as a photograph, right-click on the graph and choose Save as Image.

Within the dialogue field, title the photograph of your graph, select the place to reserve it in your laptop, and select the file sort you’d like to reserve it as. On this instance, it’s saved as a JPEG to a desktop folder. Lastly, click on Save.

You’ll have a transparent photograph of your graph or chart which you can add to any visible design.

Visualize Knowledge Like A Professional

Prepared for one last step to make this entire course of quicker? Now you can swap your information into numerous graph varieties — like a pie chart or line graph — to rapidly and extra simply decide what format greatest tells the story of your information.

Right here’s the way you do it:

Step 1. Choose the chart.

Have a look at your Excel chart or graph to discover a clean spot inside it. Click on on a clean spot. As soon as the chart is highlighted throughout the border, you’ll comprehend it’s chosen.

Step 2. Proper-click or go to the Chart Design tab.

As soon as the chart is chosen, the ribbon above will present a Chart Design tab. You may go click on on that or just right-click your chosen chart to avoid wasting time. Both method, you’ll see numerous chart choices to select from.

Step 3. Change the chart sort.

When the chart choices pop up — both on the ribbon or below your cursor in the event you right-clicked — you’ll subsequent click on Change Chart Kind. A menu will pop up with quite a lot of chart sort choices on the left.

To the proper shall be a visible instance of the chart sort you click on on.

Step 4. Store in your chart.

The final bit is essentially the most enjoyable! You may click on the Beneficial Charts tab or the All Charts tab and begin clicking your method down the record of chart varieties. As you see ones that curiosity you, click on the Okay button.

Your initially chosen chart will change varieties earlier than your eyes. Give it a glance and ensure all the information and labels make sense to you. Nonetheless curious?

Choose your chart once more and repeat the method to see your information on as many forms of charts as you’d like.

No sorcery, as promised. Maintain these step-by-step tutorials helpful. You’ll be capable of create charts and graphs that rapidly, cleanly, and clearly visualize your information for presentation.

Editor’s observe: This put up was initially revealed in June 2018 and has been up to date for comprehensiveness.