If you are a professional working for a company it is likely that Microsoft Excel – popularly referred to as Excel – is an important tool in your kit and how to make a graph in Excel is an even more important skill.
Excel is versatile.
Spreadsheets color code team member schedules. Formulas update changes in customer data automatically. The import/export function transports information from one application to another.
But it is also quite dry and boring.
Rows and columns of numbers do not mean much to most people.
And this is the reason why Excel spreadsheets are considered to be rather poor in terms of engagement.
Don’t Be Dry. Go Visual!
Visualization of data is the process through which raw data points are processed, normalized (adjusted to create smoother more contiguous visuals) and presented in the form of graphs or charts.
There are obvious advantages to this approach.
Fortunately Excel users can leverage the power of visuals without any additional extensions. The software comes with the ability to create a graph or a chart baked into it.
No more boring spreadsheets that confuse recipients and waste opportunities of mining insights!
Bring the firepower and punch of visuals right to your Excel booklet.
Can Charts and Graphs be Used Interchangeably?
Technically? No, they mean separate things and the terms are independent of each other. In the real world? Yes, they are used interchangeably.
Graphs represent variations in values of data points over a given duration of time. They are simpler than charts because you are dealing with different data parameters. Comparing and contrasting segments of the same set against one another is more difficult.
So if you are trying to see how the number of hours worked per week and the frequency of annual leaves for employees in your company has fluctuated over the past 5 years, you can create a simple line graph and track the spikes and dips to get a fair idea.
On the other hand a chart is more aesthetically pleasing. Something like a pie chart is used to convey to readers the relative share of a particular segment of the data set with respect to other segments that are available. If instead of the changes in hours worked and annual leaves over 5 years, you want to present the percentage contributions of the different types of tasks that make up a 40 hour work week for employees in your organization then you can definitely insert a pie chart into your spreadsheet for the desired impact.
Types of Graphs Available in Excel
Excel offers three varieties of graphs:
- Line Graphs: Both 2 dimensional and three dimensional line graphs are available in all the versions of Microsoft Excel. Line graphs are great for showing trends over time. Simultaneously plot more than one data parameter – like employee compensation, average number of hours worked in a week and average number of annual leaves against the same X axis or time.
- Column Graphs: Column graphs also help viewers see how parameters change over time. But they can be called “graphs” when only a single data parameter is used. If multiple parameters are called into action, viewers can’t really get any insights about how each individual parameter has changed. As you can see in the Column graph below, average numbers of hours worked in a week and average number of annual leaves when plotted side by side do not provide the same clarity as the Line graph.
- Bar Graphs: Bar graphs are very similar to column graphs but here the constant parameter (say time) is assigned to the Y axis and the variables are plotted against the X axis.
It is clear that Line graphs are the most suited to presenting changes in multiple data parameters over a period of time and thus in the step by step tutorial on how to make a graph in Excel, we will be using it.
How to Make a Graph in Excel?
Fill the Excel Sheet with Your Data & Assign the Right Data Types
If you have imported this data from a software solution or an enterprise application, then chances are the information has been compiled in the .csv (comma separated values) format. If this is the case use an online CSV to Excel converter like the one here to generate the Excel file.
Post the conversion, you might still need to clean up the rows and the columns. It is better to work with a spreadsheet that is without clutter so that you can understand how accurate the end rendition of the graph is.
You may also need to manually enter the data into the spreadsheet and then create the graph from it.
Excel has two components to its spreadsheets:
- The rows that are horizontal and marked with numbers
- The columns that are vertical and marked with alphabets
After all the data values have been accommodated, make sure that you visit the Number section under the Home tab and assign the right data type to the various columns. If you do not do this, chances are your graphs will not show up right.
For example if column B is measuring time, ensure that you choose the option Time from the drop down menu and assign it to B.
Choose the Type of Graph You Want to Create
This will depend on the type of data you have and the number of different parameters you will be tracking simultaneously.
If you are looking to take note of trends over time then Line graphs are your best bet. This is what we will be using for the purpose of the tutorial.
Let us assume that we are tracking Average Number of Hours Worked/Week/Employee and Average Number of Leaves/Employee/Year against a five year time span.
Choose the Data Sets by Highlighting Them
For a graph to be created, you need to choose or select the different data parameters. To do this bring your cursor over the cell marked A. You will see it transform into a tiny arrow pointing downwards. When this happens, click on the cell A and the entire column will be selected.
Repeat the process with columns B and C, keeping the Ctrl (Control) button pressed if you are working on a Windows operating system enabled machine. Mac users press the Command key.
Your final selection should look something like the image given below.
Create the Basic Graph
I am calling it the basic graph because in Excel, adjusting the axis title and the legends is very important for viewers to get a complete and accurate idea of what the data sets mean.
With the columns selected, visit the Insert tab and choose the option 2D Line Graph.
You will immediately see a graph appear below your data values.
Sometimes if you do not assign the right data type to your columns in the first step, the graph may not show in a way that you want it to. For example, Excel may plot the parameter Average Number of Leaves/Employee/Year along the X axis instead of the Year. In this case, you can use the option Switch Row/Column under the Design tab of Chart Tools to play around with various combinations of X axis and Y axis parameters till you hit on the perfect rendition.
Refine Your Graph with the Chart Tools
There are three tabs under the Chart Tools header of an Excel file. They are “Design”, “Layout” and “Format”.
Design: Design allows you to move your graph and re-position it. It gives you the freedom to change the chart type. You can even experiment with different chart layouts. In some the legend or the definition of what each graph line stands for is at the top. In some it is to the right or even completely turned off.
Layout: This allows you to change the title of your axes, the title of your chart and the position of the legend. You might go with vertical text along the Y axis and horizontal text along the X axis. You can even adjust the gridlines. You have every formatting tool conceivable at your fingertips to improve the look and feel of your graph.
Format: The Format tab allows you to add a border in your chosen width and color around the graph so that it is properly separated from the data points that are filled in the rows and columns.
And there you have it. An accurate visual representation of the data that you have imported or entered manually to help your team members and stakeholders better engage with the information and utilize it to create strategies or be more aware of all the constraints while taking decisions!
Some Challenges You Might Face While Creating an Excel Graph
When manipulating simple data sets, the process goes off without glitches. But when you have several types of data in the form of multiple parameters, the following are a couple of challenges you are likely to face:
- Data sorting is not favored when creating graphs. Online tutorials, might recommend data sorting to make your “charts” look more aesthetically appealing. But beware of when the X axis is time based parameter! Sorting data values by magnitude may disrupt the flow of the graph because the dates are sorted randomly. Your ability to spot the trends accurately may be affected!
- You may forget to remove duplicates. This is especially true if you have imported the data from a third party application. Generally this type of information is not filtered of redundancies. And you might end up corrupting the integrity of your information if duplicates sneak into your pictorial representation of trends. When working with copious volumes of data, it is best to use the Remove Duplicates option on your rows.
Many professionals refrain from using graphs in Excel because they feel that the process will be needlessly complicated. I hope that through the answer to the question “How to Make a Graph in Excel?” it is now clear that the nominal efforts are very much worth the pay-off.