How to create a chart in Excel and add visual elements to your reports (2023)

Most businesses (and people) don't want to be scrolling through pages and pages of spreadsheets when it's so quick to turn those rows and columns into a visual chart. But someone has to do it... and that person has to be you.

Are you ready to transform your boring Excel spreadsheet into something more interesting?

Everything you need is at your fingertips in Excel. Excel users can harness the power of images without additional extensions. You can create a chart or table directly in Excel instead of exporting it to another tool.

What is the difference between tables and graphs?

Respectivelyreferenz.com“The difference between graphs and tables lies mainly in the way the data is compiled and presented. Charts typically focus on raw data, showing trends and changes in that data over time. Charts are best used when the data can be categorized or calculated to create simpler, easier-to-understand numbers.

So, technically, tables and graphs mean different things, but in the real world, the terms are used interchangeably. People generally accept both, so don't worry too much about it!

How to create a chart in Excel and add visual elements to your reports (1)

This post will show you exactly how to create a chart in Excel and improve your charts and reports... but let's talk about charts first.Understanding exactly how charts in Excel work will help you understand charts in Excel.

diagram in excel

Graphics are generally considered more aesthetically pleasing than graphics. Something like a pie chart is used to give readers the relative share of a particular segment of the data set relative to other available segments. If instead of showing fluctuations in hours worked and annual leave over 5 years, you want to see thepercentage contributions of the different types of tasks that make up a 40-hour week for workersIn your organization, you can definitely add a pie chart to your spreadsheet to create the impact you want.

How to create a chart in Excel and add visual elements to your reports (2)

diagram in excel

The graphs represent changes in data point values ​​over time. They are simpler than graphs because they deal with different data parameters. It is more difficult to compare and contrast segments of the same sentence.

So if you try to see how thatNumber of hours worked per weekmithe frequency of annual leavefor your company's employees has fluctuated over the last 5 years, you can create a simple line chart and track the peaks and valleys to get a reasonable idea.

(Video) Excel Charts & Graphs: Learn the Basics for a Quick Start

How to create a chart in Excel and add visual elements to your reports (3)

Chart Types Available in Excel

Excel offers three types of charts:

  • line charts: Two-dimensional and three-dimensional line charts are available in all versions of Microsoft Excel. Line charts are great for showing trends over time. Simultaneously draw more than one data parameter, such asEmployee compensation, average number of hours worked in a weekmiaverage number of annual leavesagainst the same x axis or time.
  • column charts: Column charts also help viewers see how parameters change over time. However, they can be called "graphs" if only a single data parameter is used. When multiple parameters are triggered, viewers can't really get information about how each individual parameter changed. As you can see in the following column chart,average hours worked in a weekmiaverage number of annual leavesWhen plotted side by side, they don't offer the same clarity as the line chart.
How to create a chart in Excel and add visual elements to your reports (4)
  • Bar graphic: Bar charts are very similar to column charts, but here the constant parameter (for example, time) is mapped to the Y axis and the variables are plotted on the X axis.
How to create a chart in Excel and add visual elements to your reports (5)

How to create a chart in Excel

1. Fill in the excel sheet with your data and assign the correct data types

The first step is to fill out an Excel spreadsheet with the data you need. If you imported this data from other software, it was most likely compiled into a document in .csv (comma-separated values) format.

In this case, use an online CSV to Excel converter likeHereto generate the Excel file, or open it in Excel and save the file with an Excel extension.

After converting the file, you may need to clean up the rows and columns. It's best to work with a clean spreadsheet so the Excel chart you create is clean and easy to edit or modify.

If that doesn't work, you may also need to manually type or copy and paste the data into the worksheet before creating the Excel chart.

Excel has two components to its spreadsheets:

  • olineswhich are horizontal and marked withPay
  • ocolumnswhich are vertical and marked withAlphabet

After all data values ​​have been defined and considered, be sure to visit theNumbersection under thelartab and assign the righttype of datato the different columns. If you don't do this, your charts may not display correctly.

(Video) Excel Quick and Simple Charts Tutorial

How to create a chart in Excel and add visual elements to your reports (6)


For example, if column B measures time, be sure to select the optionTempofrom the dropdown menu and assign it to B.

Select the type of Excel chart you want to create

This depends on the nature of your data and the number of different parameters you are tracking at once.

If you want to see trends over time, line charts are your best bet. This is what we will use for the purpose of the tutorial.
Let's say we trackAverage hours worked/week/employeemiAverage departures/employee/yearagainst a period of five years.

Check the records you want to use

For a graph to be created, you must select the various data parameters.

To do this, hover the mouse pointer over the selected cellA. You will see that it becomes asmall arrowPoint down. In this case, click cell A and the entire column will be selected.

Repeat the process with the columns.BmiC, Press theControl (control)in Windows or through the buttoncommand keywith Mac users.

Your final selection should look like this:

How to create a chart in Excel and add visual elements to your reports (7)

Create the simple Excel chart

With the columns selected, visit theinsertiontab and select the option2D line chart.

(Video) How to Create Charts and Graphs in Microsoft Excel - Quick and Simple

You will immediately see a graph below your data values.

How to create a chart in Excel and add visual elements to your reports (8)

If you don't assign the correct data type to your columns in the first step, sometimes your chart may not appear the way you want it to. For example, Excel can represent the parameterAverage departures/employee/yearalong the x axis instead of theOne more time. In this case, you can use the optionChange row/columnlowProjectleader ofDiagramm-ToolsPlay around with different combinations of X-axis and Y-axis parameters until you get the perfect representation.

How to create a chart in Excel and add visual elements to your reports (9)

Enhance your Excel chart with charting tools

To change the colors or layout of your chart, go toDiagramm-Toolsin the Excel header.

You can choose between design, layout and format. Each one changes the appearance of the Excel chart.

Project:design allowsmove your chartand replacement. gives you the freedom to do itChange chart type. You can even try differentgraphic designs. This may be more in line with your brand guidelines, personal style, or your manager's preferences.

How to create a chart in Excel and add visual elements to your reports (10)

The plan:This allows youto change the title of the axis, the title of your graphis inlegend position. You can use vertical text along the Y axis and horizontal text along the X axis.Adjust grid lines. You have every imaginable formatting tool at your fingertips to enhance the appearance of your chart.

How to create a chart in Excel and add visual elements to your reports (11)
(Video) Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)

Format: In the Format tab you can do thisAdd a border of the chosen width and coloraround the graph to properly separate it from the populated data points in the rows and columns.

How to create a chart in Excel and add visual elements to your reports (12)

And there it is. An accurate visual representation of the data you've imported or manually entered to help your team members and stakeholders better interact with the information and use it to strategize or better consider constraints in decision making!

Challenges when creating a chart in Excel

By editing simple data sets, you can easily create a chart.

But when you start adding multiple data types with multiple parameters, there will be failures. Here are some of the challenges you will face:

  • Sorting data can be problematic when creating charts. Online tutorials may recommend data classification to make your "graphs" more aesthetically pleasing. But be careful if the x-axis is a time-based parameter! Sorting data values ​​by size can break the flow of the chart because the data is sorted randomly. You may not be very good at spotting trends.
How to create a chart in Excel and add visual elements to your reports (13)

You may forget to remove the duplicates.This is especially true if you imported the data from a third-party application. In general, this type of information does not filter out of redundancies. And you could end up corrupting the integrity of your information if duplicates infiltrate your pictorial representation of trends. If you are working with large amounts of data, it is better to use theeliminate duplicatesoption in your lines.

How to create a chart in Excel and add visual elements to your reports (14)

However, creating charts in Excel doesn't have to be overly complex.Similar to creating Gantt charts in Excel, maybe there issimpler toolsto help you with that. If you're trying to chart workloads, budget allocations, or track projects, check out project management software.

Many of these functions are automated and do not require manual data entry. And you don't have to wonder who has the latest records. Most project management solutions like Workzone have built-in file sharing and some viewing features.

(Video) Infographics: Progress Circle Chart in Excel

FAQs

How do you add chart elements in Excel? ›

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then select the chart element that you want to format.

How do you make a chart more visually appealing in Excel? ›

  1. Tip #1: Always pick the right chart type.
  2. Tip #2: Remove unnecessary axes.
  3. Tip #3: Distribute bars evenly.
  4. Tip #4: Remove background lines.
  5. Tip #5: Remove unnecessary styling.
  6. Tip #6: Never use 3D effects.
  7. Tip #7: Clean your financial chart.
  8. Tip #8: Always remain consistent.
Sep 15, 2021

What is one method for creating a visual in Excel? ›

Create a visualization
  1. Create a table on the Power View sheet by checking a table or field in the field list or dragging a field from the field list to the sheet. ...
  2. Convert the table to a visualization by choosing a visualization type on the Design tab.

What are some things you can do with a chart to make it more visually appealing? ›

Colors are the most important factor in charts to make them more visually appealing. So, always choose a good set of eye-catching colors that will highlight your story for the user.
...
Color palettes can be commonly divided into three categories:
  • Qualitative palette.
  • Sequential palette.
  • Diverging palette.
Oct 7, 2022

What are charts in Excel and how they are used? ›

Charts are used to display series of numeric data in a graphical format to make it easier to understand large quantities of data and the relationship between different series of data. Excel supports many types of charts to help you display data in ways that are meaningful to your audience.

What do you use to create a chart? ›

  • Chart Wizard use to create a chart.
  • A wizard found in the Microsoft Excel program that takes users step-by-step through the process of creating a chart in Microsoft Excel. ...
  • Select a range of data, click the button and Excel produces an embedded chart.

How would you add data elements to a chart? ›

Right-click the chart, and then choose Select Data. The Select Data Source dialog box appears on the worksheet that contains the source data for the chart. Leaving the dialog box open, click in the worksheet, and then click and drag to select all the data you want to use for the chart, including the new data series.

How do I create a beautiful report in Excel? ›

How to make reports in Excel
  1. Display data in charts. One method to create a report is to display your data in a graph or chart. ...
  2. Create a pivot table to organize data. ...
  3. Separate the data types of your reports. ...
  4. Add page headers. ...
  5. Format and print reports.
4 days ago

Which chart provides best visual display in Excel? ›

Scatterplot. Scatter plots are useful for showing precise, data dense visualizations, correlations, and clusters between two numeric variables. - a bubble chart is a variation to the scatter plot.

Which graph is the most visually appealing type of graph? ›

The pictograph or a pictogram is one of the more visually appealing types of graphs and charts that display numerical information with the use of icons or picture symbols to represent data sets. They are very easy to read statistical way of data visualization.

How do you use visual in Excel? ›

Excel
  1. On the Developer tab, click Visual Basic. If you don't see the Developer tab: ...
  2. In the Visual Basic Editor, on the Help menu, click Microsoft Visual Basic for Applications Help.
  3. In the Search box, type the method, property, function, statement, or object that you want help with, or type a query.

What is data visualization in Excel? ›

Data visualizations in Excel are representations of numerical data in a visual format. You can organize data in a spreadsheet into a more accessible and organized format like a line graph, Gantt chart or bar graph.

What is the best way to display data in Excel? ›

Following are the most popular Excel charts and graphs:
  1. Clustered column chart.
  2. Combination chart.
  3. Stacked column chart.
  4. 100% stacked column chart.
  5. Bar chart.
  6. Line chart.
  7. Number chart.
  8. Gauge chart (Speedometer chart)
Jan 3, 2023

What types of charts can be used to visualize data? ›

14 Best Types of Charts and Graphs for Data Visualization [+ Guide]
  • Bar Graph.
  • Column Chart.
  • Line Graph.
  • Dual Axis Chart.
  • Area Chart.
  • Stacked Bar Graph.
  • Mekko Chart.
  • Pie Chart.
Jun 3, 2022

What is a data visualization chart? ›

Data visualization charts are graphical representations of data that tell a story using symbols in order to improve the understanding of large amounts of data.

What is data visualization charts and graphs? ›

Data visualization is the graphical representation of information and data. By using visual elements like charts, graphs, and maps, data visualization tools provide an accessible way to see and understand trends, outliers, and patterns in data.

What are the 4 most commonly used types of chart? ›

There are several different types of charts and graphs. The four most common are probably line graphs, bar graphs and histograms, pie charts, and Cartesian graphs.

What are the different types of chart you can make in Excel? ›

Excel Charts - Types
  • Column Chart.
  • Line Chart.
  • Pie Chart.
  • Doughnut Chart.
  • Bar Chart.
  • Area Chart.
  • XY (Scatter) Chart.
  • Bubble Chart.

What are the types of charts in Excel? ›

Choose your charts wisely
Chart Type
1.Column Chart
2.Line Chart
3.Bar Chart
4.Area chart
2 more rows

What is the best way to visualize data? ›

The two most common formats for visualizing data are dashboards and reports. This allows you to showcase several different images to paint a more compelling story. In fact, the average dashboard, according to our experts, contains 3-5 charts or graphs.

What are the best ways to use visuals effectively? ›

Developing Effective Visuals
  1. Keep it simple. Your slides should complement your verbal message, not detract from it by unnecessary visual clutter. ...
  2. Limit bullet points and text. ...
  3. Use high-quality graphics. ...
  4. Use appropriate charts. ...
  5. Choose your fonts well. ...
  6. Spend time in the slide sorter.

What do you think is the most effective chart to use to read and interpret data? ›

Use a bar or column chart to compare independent values. We, as readers, are particularly good at comparing the length of bars in a bar chart (in contrast to the segments of a pie chart, for example), making bar and column charts the best charts for showing comparisons.

What is the difference between a graph and a chart in Excel? ›

Graphs are mostly a numerical representation of data as it shows the relation of change in numbers and how one number affects or changes another. However, charts are the visual representation of where categories may or may not be related and how the information is displayed in graphs and charts.

How did you format your chart to help you analyze your data? ›

Create a chart

Use the Quick Analysis tool to pick the right chart for your data. Select the data you want to show in a chart. to the bottom-right of the selected cells. Select Charts, hover over the options, and pick the chart you want.

How do I create a chart in Excel with multiple data? ›

How to show two sets of data on one graph in Excel
  1. Enter data in the Excel spreadsheet you want on the graph. ...
  2. Select the data you want on the graph. ...
  3. Click the "Insert" tab and then look at the "Recommended Charts" in the charts group. ...
  4. Choose "All Charts" and click "Combo" as the chart type.
Aug 8, 2022

How to create a table in Excel? ›

You can create and format a table, to visually group and analyze data.
  1. Select a cell within your data.
  2. Select Home > Format as Table.
  3. Choose a style for your table.
  4. In the Format as Table dialog box, set your cell range.
  5. Mark if your table has headers.
  6. Select OK.

What is a chart element in Excel? ›

The title is a text box you can place anywhere on the chart. The plot is the area on the chart that displays the data in the chart type you choose. A data point is one piece of data appearing on the chart.

Why can I add chart element in Excel? ›

Excel allows you to add chart elements—such as chart titles, legends, and data labels—to make your chart easier to read. To add a chart element, click the Add Chart Element command on the Design tab, then choose the desired element from the drop-down menu.

Which chart elements can be added to your chart in Excel by default? ›

For most chart types, the vertical axis (aka value or Y axis) and horizontal axis (aka category or X axis) are added automatically when you make a chart in Excel.

How can I make my report attractive? ›

Consider Using Visuals To Enhance Appeal and Reinforce the Content. Photos, illustrations, or other design elements can add warmth and visual interest to your report. Choose visuals that are clear, uncluttered, and culturally appropriate for your intended audience.

What are the three chart types most commonly used to visualize and display data? ›

There are many types of data visualization. The most common are scatter plots, line graphs, pie charts, bar charts, heat maps, area charts, choropleth maps and histograms.

Which chart types work best to visualize a lot of data? ›

Line charts work better with bigger datasets, so, if you have a small one, use a bar chart instead.

Which graph is best for visualization? ›

Bar Chart. Bar charts are one of the most common data visualizations. You can use them to quickly compare data across categories, highlight differences, show trends and outliers, and reveal historical highs and lows at a glance.

How do I create a chart with multiple data in Excel? ›

How to show two sets of data on one graph in Excel
  1. Enter data in the Excel spreadsheet you want on the graph. ...
  2. Select the data you want on the graph. ...
  3. Click the "Insert" tab and then look at the "Recommended Charts" in the charts group. ...
  4. Choose "All Charts" and click "Combo" as the chart type.
Aug 8, 2022

What are visuals in Excel? ›

Data visualizations in Excel are representations of numerical data in a visual format. You can organize data in a spreadsheet into a more accessible and organized format like a line graph, Gantt chart or bar graph.

Why do we use Visual Basic in Excel? ›

VBA is used to automate tasks and perform several other functions beyond creating and organizing spreadsheets. For example, users need to automate some aspects of Excel, such as repetitive tasks, frequent tasks, generating reports, etc.

Why do we need data visualization in Excel? ›

The main goal of data visualization is to make it easier to identify patterns, trends and outliers in large data sets. The term is often used interchangeably with others, including information graphics, information visualization and statistical graphics.

Can Excel create combinations of charts? ›

A combination chart or most commonly known as a combo chart in Excel. It is a combination of two or more different charts in Excel. We can create a combo chart from the “Insert” menu in the “Chart” tab to make such combo charts. To combine two charts, we must have two different data sets but one common field combined.

Can you have multiple variables in an Excel chart? ›

While many charts only involve one variable, you can create charts that have multiple variables. To do this, you need only to create a table with multiple columns. The variables for the chart are the values listed in each column.

Videos

1. Create a Column Chart in Excel
(Excel Functions)
2. 5 Design Tips for Better Excel Reports & Dashboards
(Leila Gharani)
3. 📊 How to Build Excel Interactive Dashboards
(Kevin Stratvert)
4. This Excel Chart will grab your attention (Infographic template included)
(Leila Gharani)
5. 4 Hidden Excel Dashboard Design Tips for Beautiful Reports
(Leila Gharani)
6. Displaying a Chart in a UserForm dynamically
(TheDataLabs)
Top Articles
Latest Posts
Article information

Author: Prof. Nancy Dach

Last Updated: 11/20/2022

Views: 6043

Rating: 4.7 / 5 (77 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Prof. Nancy Dach

Birthday: 1993-08-23

Address: 569 Waelchi Ports, South Blainebury, LA 11589

Phone: +9958996486049

Job: Sales Manager

Hobby: Web surfing, Scuba diving, Mountaineering, Writing, Sailing, Dance, Blacksmithing

Introduction: My name is Prof. Nancy Dach, I am a lively, joyous, courageous, lovely, tender, charming, open person who loves writing and wants to share my knowledge and understanding with you.