One of the easiest ways to visualize your data is by making a line graph in Excel. It is most often used to show trends over time, uncover outliers, and help drive decision making. This post will show you how to do it in 3 easy steps
Step 1: Prepare your Data for a Line Graph
In this example we’re looking at a relatively simple example: Sales by Month. Our data goes back to January of 2020 and goes through June of ’22.
You’ll want to make sure of a few things
- Format Dates Correctly – If analyzing a similar kind of data, make sure your dates are formatting correctly. I prefer the format of MM/1/YYYY (M = Month, Y = Year). You could also use MM-YYYY (Feb-2020), “MM YYYY” (Feb 2020), or any number of date formats so long as you include Month, Day, AND Year in the column. For what you’re working with, you may even need data by the hour or second!
- Whatever the chart you’re trying to create, including all of the information into the cell. Excel is fantastic in dealing with dates. I’ve seen charts where the Month and Year are in separate columns (below), and while this could work, you’re just creating more problems for yourself once you start creating the chart.
- Delete Blank Columns and Rows – You’ll also want to make sure there are no missing rows or columns in your data. Excel will either be so confused about what you’re trying to create, or at best create a chart with big sections of your data missing
Missing Column of Data:
Missing Row of Data:
- Label Your Columns Properly – The headings at the top of your column will act as the title of the X and Y axis, so those are obviously important to spell/label correctly
- Format Metric Data Before Making the Graph – While it is possible to format your metrics (in this case Sales) AFTER you’ve created the graph, it’s much easier to create a graph with data that’s already been formatted in the way we’d like it to appear on the graph ($$ in this case). See the unformatted data below
Step 2: Create the Line Graph
Now that we have properly labeled data formatted in the way we’d like it to appear on the graph, the easiest part is to create the line graph
- Click anywhere inside our data set
- Click the “Insert” tab
- Click the “Line chart” icon
- Click the first “2-D line” icon
- Done!
Pro Tip: If you like keyboard shortcuts in Excel, you could also create the graph by clicking anywhere inside the data and typing ‘ALT+N+N+1″. You get the same result!
Check out the result:
Right out of the box, our graph shows the upward trend of sales since Jan 2020, and our X and Y axes are formatted exactly as they are in our original data!
Step 3: Format the Line Graph
Excel has made it very easy to add the most commonly used things needed for charts, like Titles, labels, a Legend and trendlines. To activate this helper box, click anywhere on the chart and then click the green + box (shown below).
Before we add titles or anything else, a little trick I use to make charts ‘pop’ a bit more off the page is to hide gridlines. To do this, type ALT+W+V+G. Much better! This way, if you do have to present this to someone while its still in your Excel workbook, the whole page just looks less busy.
Lastly, we’re going to add Axis Titles, a Legend, and Change the Title. To do, activate the “helper box” (2 pictures above) and check “Axis Titles” and “Legend”. In our case, we only have one metric (Total sales), but if we had multiple charts we wanted to plot alongside each other the Legend comes in very handy (chart 2 below)! Adding the axis titles and chart title really makes it clear what we’re looking at.
