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!

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.

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.

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.

• 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

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.

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:

Create the simple Excel chart

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

You will immediately see a graph below your data values.

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.

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.

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.

(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.

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.

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.

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.