Once you get to a certain point in your career, you realize that you actually spend a lot of time summarizing complicated pieces of work for the managers above. It really becomes a big part of your job.
- How To Create A Benchmark Line In Excel Chart
- Create A Chart With A Benchmark Line In Excel For Mac Pro
One area is projects. Although you might be happy working with the detailed task lists, giving an update to management needs a higher-level view of the day to day work.
- You can use this data to create a Radar Chart in Excel. STEP 1: Select the Data Table. STEP 2: Go to Insert Recommended Chart (Excel 2013 & Above) Insert Other Charts (Excel 2007 & 2010) STEP 3: In the Insert Chart dialog box, Go to All Charts Radar Charts and then Press OK. This is how your Sales Analysis Excel Radar Chart will look like.
- This method will recommend an amazing tool, Add Line to Chart feature of Kutools for Excel, to quickly add a horizontal average line to the selected column chart with 2 clicks only! Kutools for Excel- Includes more than 300 handy tools for Excel.Full feature free trial 30-day, no credit card required! Supposing you have created a column chart as below screenshot shown, and you can.
Something that can easily be copied and pasted into a presentation slide.
With a bit of up front effort it's possible to use a bar chart in a spreadsheet to give a nice, visual overview of your project timeline. Using a bar chart to create a timeline in Excel is less common than using a scatter plot, but still an option depending on what style you prefer. In this tutorial we'll walk you through how to create it.
Also, if you're interested in an faster solution, check out Preceden timeline maker, our easy-to-use timeline software tool.
These data points create the performance trend line. Select the entire chart feeder table and plot the data on a stacked area chart. Right-click the Values series and choose Change Series Chart Type. Use the Change Chart Type dialog box to change the Values series to a Line and to place it on the secondary axis, as shown in this figure. To create a chart, the first step is to select the data—across a set of cells. Sometimes, you may not want to display all of your data. You can choose which so you can choose the specific columns, rows, or cells to include.
This tutorial is written for Excel 2016 for Windows, though the steps are similar for Excel for Mac.
Here's what the final result will look like - you can download a spreadsheet example here: Excel Bar Chart Timeline Example (ZIP, 20kb).
Step 1: Put your actions in a table
First of all, you need to create a table of your actions and events with a column for the date this takes place.
There are a couple of other columns that will be used in the technical set up of the chart.
The Label Height column will determine where our labels are output on the chart. Use equal spacing, with max and min between 20 and -20. You can change this later to get the final chart looking balanced.
The baseline column is always all zeros. This is going to be a visual line with an arrow depicting the timeline.
The two columns will make more sense later.
Step 2: Build the basic chart
Once the table is finished, go to the Insert menu on the top menu ribbon. Find the Charts group and select the dropdown for bar charts.
Select the first 2D chart.
This will give you a blank chart on the page. Move it off your data table and then use the handles round the border to make it bigger.
Select the chart with the mouse, go to Chart tools and then Design on the ribbon.
Click Select data.
This dialog box should appear, patiently awaiting your inputs:
Click Add on the left-hand side to add a new series.
On the box that opens up, leave the series name blank and add E6:E15 as the series values (assuming your data table is in the same position).
Click OK to go back to the main series data dialog box.
You need to add the Baseline column as the next series. Click Add.
Again, leave the series name blank and select the values as F6:F15. Click OK.
On the select data source dialog, click edit on the right to add the horizontal X axis labels (your actions and events).
Select the range C6:C15 and click OK.
The data source box looks like this:
Click OK.
Click on the chart title and change to something more relevant to your project.
It doesn't look like a timeline yet so let's move on...
Step 3: Add the baseline as a horizontal line
Now we're going to add the baseline series (remember the column full of zeroes?) as a line chart on top of the bar chart. That way we can really highlight the movement of time from left to right.
First of all, click on the visible series (any bar will work) and then right click. Select Format Data Series on the dropdown menu.
The sidebar pops up on the right of the window.
Click on the Series Options dropdown and select Series 2.
Add the series to the Secondary Axis.
With this Series 2 still selected, go to Change Chart Type on the top ribbon menu.
Then select the chart type dropdown on Series 2 and select the first line chart on the menu.
Click OK.
Now to format this line that has appeared on the chart. Right click on it and select Format Data Series. Go to Fill & Line on the sidebar menu.
Choose whatever color works for you. In this example we'll leave it orange. Thicken the line to 6pts.
Finally, change End Arrow Type to an arrow.
Step 4: Adding the dates to the timeline
You've probably noticed that our dates haven't been added to the timeline yet. Time to fix that...
Select the chart and then go to Select Data on the Chart Tools, Design ribbon.
Select Series 2, click on edit for the horizontal axis (on the right-hand side of the box).
Select the dates column D6:D15.
Click OK, and then click ok.
Dates don't show yet, but we'll get there when we do the final formatting in a later step.
Step 5: Error bars to connect labels to horizontal axis
Now you will use error bars to 'link' the action descriptions to the timeline. It'll make sense when you see the outcome!
Click on a blue bar to select all of that series.
Go to top left Add Chart Element, go down to Error Bars, More Error Bars Options.
On the right-hand side bar, change Direction to Minus, and the End Style to No Cap.
And the Error Amount should be set as a Percentage at 100%.
Go to Fill & Line and change the thickness to 3pt. Change the color and dash type if you like.
Now click on a blue bar again. Format Data Series should still be open on the right sidebar. If not, right click on a bar in the chart and select Format Series.
Select No Fill and under Border, select No Line.
This will make the bars disappear, just leaving the error lines behind.
Step 6: Finishing the timeline format
Click the vertical Axis Major Gridlines on the chart and hit delete on the keyboard. This clears the lines from the chart plot area.
Then change the scales on both vertical axes. To do this, right click on an axis, select Format Axis.
Under axis options on the sidebar, change the Bounds to Minimum -20 and Maximum +20.
Do exactly the same with the other axis. This will line them up and you should see your baseline now in the middle of the chart.
If you have more or less actions, then you may need to go bigger or smaller than +20 and -20. All comes down to playing with the Label Height column on your data table to get your spacing looking good.
Now the axes have been corrected, we can remove them from the chart: we don't need to see them.
Select an axis with the mouse and press delete. Repeat with the other one.
It's starting to take shape and look like a real timeline. Not much more to do.
You'll notice the dates are still missing even though earlier we added them as a horizontal axis. Now to make them visible.
Select Series 2 (the orange line on the horizontal axis).
Go to Chart tools, Design on the ribbon.
On the top left, click Add Chart Element, then down to Data Labels followed by More Data Label Options.
This opens the sidebar to format the data labels.
Click Label Options and select Category Name under Label Contains.
Change Label Position to Below.
Now use the dropdown to select Series 1 (the hidden bar chart). With it selected go to Add Chart Element on the top left and scroll down to data labels and More Data Label Options.
This opens in the right-hand side bar.
Go to Label Options and then change Label Contains to Category Name only.
Change the Label Position to Outside End.
Now select the horizontal axis on the chart and hit delete on the keyboard.
You should now see your actions as labels at the end of the lines touching the horizontal line. The dates will show below the horizontal line.
If your dates are a bit squashed, you might need to make the chart longer or angle the text (via format data labels).
And that's it. The timeline is done. The formatting in the example is very simple but you can play with it to get the right color combination for you.
The downside of this timeline is it doesn't give a visual idea for the duration between actions. Everything is equally spaced.
But it's a very simple to use once your template is set up.
And it does give a good overview of the project timeline to include on a slide in a meeting.
As we've seen here, it is possible to create a decent looking timeline in Excel only, but if using a specialized tool is an option, you might want to try our timeline maker tool which makes the process much, much simpler:
See also interactive charts.
To create an interactive chart with a drop-down list, do the following:
1. Add additional data to your spreadsheet for drop-downlist values:
- Number as an index of the selected list item,
- All items in one column if you want to create a list from the column names (if you want to createa list of row names, this step isn't needed).
For example:
- Enter in the cell H2 the number 1,
- Column names in the range H3:H5 (seeHow to change columnsto rows and vice versa):
2. Create data ranges, which will be used for theinteractive chart:
2.1. On the Formulas tab, in the Defined Namesgroup, click Define Name (seeCreating and using named rangesfor more details):
2.2. In the New Name dialog box:
- In the Name field, enter the name of this new range, for example, SeriesA,
- In the Refers to field, enter this formula:
= IF (<condition>, <series data>, 0)
Where the condition is the option to show this data series.
In this example:
= IF ($H$2=1, $C$3:$C$14, 0):
2.3. Add the new data range for the other series.
In this example:
- Second data range:
- Name: SeriesB,
- Refers to: = IF ($H$2=2, $D$3:$D$14, 0),
- Third data range:
- Name: SeriesC,
- Refers to: = IF ($H$2=3, $E$3:$E$14, 0).
3. Create a chart that includes all data series:
4. Change the data series:
4.1. Do one of the following:
- Under Chart Tools, on the Design tab, in the Data group, choose Select Data:
- Right-click in the chart area and choose Select Data... in the popup menu:
4.2. On the Select Data Source dialog box,for the Legend Entries (Series), select the first data series that you would like to show using thedrop-down list and click the Edit button:
4.3. In the Edit Series dialog box,change Series name and Series Y values (or Series values) to the appropriate names:
4.4. Repeat the previous steps for all other data seriesthat you will show using the drop-down list.
5. Add the drop-down list to the chart:
5.1. On the Developer tab (seeShow the Developer tab), in theControls group, click the Insert drop-down list and then choose Combo Box:
5.2. Right-click on the drop-down list and chooseFormat Control... in the popup menu:
5.3. In the Format Control dialog box, on theControl tab:
How To Create A Benchmark Line In Excel Chart
- In the Input range field, select the range of item names (in this example,H3:H5),
- In the Cell link field, select the cell with the index of the selected item(in this example, H2),
- In the Drop down lines field, type the number of items in the list:
Make any other adjustments to get the look you desire.
Note: To show data series, Excel will use the colors from the theme by default. To use yourcolors for checked data series, create a new theme with custom colors. For example:
See Howto change the default colors that Excel uses for chart series for more details.
Now you will use error bars to 'link' the action descriptions to the timeline. It'll make sense when you see the outcome!
Click on a blue bar to select all of that series.
Go to top left Add Chart Element, go down to Error Bars, More Error Bars Options.
On the right-hand side bar, change Direction to Minus, and the End Style to No Cap.
And the Error Amount should be set as a Percentage at 100%.
Go to Fill & Line and change the thickness to 3pt. Change the color and dash type if you like.
Now click on a blue bar again. Format Data Series should still be open on the right sidebar. If not, right click on a bar in the chart and select Format Series.
Select No Fill and under Border, select No Line.
This will make the bars disappear, just leaving the error lines behind.
Step 6: Finishing the timeline format
Click the vertical Axis Major Gridlines on the chart and hit delete on the keyboard. This clears the lines from the chart plot area.
Then change the scales on both vertical axes. To do this, right click on an axis, select Format Axis.
Under axis options on the sidebar, change the Bounds to Minimum -20 and Maximum +20.
Do exactly the same with the other axis. This will line them up and you should see your baseline now in the middle of the chart.
If you have more or less actions, then you may need to go bigger or smaller than +20 and -20. All comes down to playing with the Label Height column on your data table to get your spacing looking good.
Now the axes have been corrected, we can remove them from the chart: we don't need to see them.
Select an axis with the mouse and press delete. Repeat with the other one.
It's starting to take shape and look like a real timeline. Not much more to do.
You'll notice the dates are still missing even though earlier we added them as a horizontal axis. Now to make them visible.
Select Series 2 (the orange line on the horizontal axis).
Go to Chart tools, Design on the ribbon.
On the top left, click Add Chart Element, then down to Data Labels followed by More Data Label Options.
This opens the sidebar to format the data labels.
Click Label Options and select Category Name under Label Contains.
Change Label Position to Below.
Now use the dropdown to select Series 1 (the hidden bar chart). With it selected go to Add Chart Element on the top left and scroll down to data labels and More Data Label Options.
This opens in the right-hand side bar.
Go to Label Options and then change Label Contains to Category Name only.
Change the Label Position to Outside End.
Now select the horizontal axis on the chart and hit delete on the keyboard.
You should now see your actions as labels at the end of the lines touching the horizontal line. The dates will show below the horizontal line.
If your dates are a bit squashed, you might need to make the chart longer or angle the text (via format data labels).
And that's it. The timeline is done. The formatting in the example is very simple but you can play with it to get the right color combination for you.
The downside of this timeline is it doesn't give a visual idea for the duration between actions. Everything is equally spaced.
But it's a very simple to use once your template is set up.
And it does give a good overview of the project timeline to include on a slide in a meeting.
As we've seen here, it is possible to create a decent looking timeline in Excel only, but if using a specialized tool is an option, you might want to try our timeline maker tool which makes the process much, much simpler:
See also interactive charts.
To create an interactive chart with a drop-down list, do the following:
1. Add additional data to your spreadsheet for drop-downlist values:
- Number as an index of the selected list item,
- All items in one column if you want to create a list from the column names (if you want to createa list of row names, this step isn't needed).
For example:
- Enter in the cell H2 the number 1,
- Column names in the range H3:H5 (seeHow to change columnsto rows and vice versa):
2. Create data ranges, which will be used for theinteractive chart:
2.1. On the Formulas tab, in the Defined Namesgroup, click Define Name (seeCreating and using named rangesfor more details):
2.2. In the New Name dialog box:
- In the Name field, enter the name of this new range, for example, SeriesA,
- In the Refers to field, enter this formula:
= IF (<condition>, <series data>, 0)
Where the condition is the option to show this data series.
In this example:
= IF ($H$2=1, $C$3:$C$14, 0):
2.3. Add the new data range for the other series.
In this example:
- Second data range:
- Name: SeriesB,
- Refers to: = IF ($H$2=2, $D$3:$D$14, 0),
- Third data range:
- Name: SeriesC,
- Refers to: = IF ($H$2=3, $E$3:$E$14, 0).
3. Create a chart that includes all data series:
4. Change the data series:
4.1. Do one of the following:
- Under Chart Tools, on the Design tab, in the Data group, choose Select Data:
- Right-click in the chart area and choose Select Data... in the popup menu:
4.2. On the Select Data Source dialog box,for the Legend Entries (Series), select the first data series that you would like to show using thedrop-down list and click the Edit button:
4.3. In the Edit Series dialog box,change Series name and Series Y values (or Series values) to the appropriate names:
4.4. Repeat the previous steps for all other data seriesthat you will show using the drop-down list.
5. Add the drop-down list to the chart:
5.1. On the Developer tab (seeShow the Developer tab), in theControls group, click the Insert drop-down list and then choose Combo Box:
5.2. Right-click on the drop-down list and chooseFormat Control... in the popup menu:
5.3. In the Format Control dialog box, on theControl tab:
How To Create A Benchmark Line In Excel Chart
- In the Input range field, select the range of item names (in this example,H3:H5),
- In the Cell link field, select the cell with the index of the selected item(in this example, H2),
- In the Drop down lines field, type the number of items in the list:
Make any other adjustments to get the look you desire.
Note: To show data series, Excel will use the colors from the theme by default. To use yourcolors for checked data series, create a new theme with custom colors. For example:
See Howto change the default colors that Excel uses for chart series for more details.
Create A Chart With A Benchmark Line In Excel For Mac Pro
See also this tip in French:Comment créer un graphique interactif avec une liste déroulante dans Excel.
