At this point, you might notice that where you have multiple actions and events with the same start date, the dotted (or dashed line) looks solid as they overlap. Select the Insert tab and click on the SmartArt button in the Illustrations section. You see the large gridlines covering the chart plot here? But if your only option is Excel, you're in luck - there's still a way to do it. Lines will now extend out of the markers from left to right. When you press enter, you will start a new line/section of the timeline. Otherwise, their titles may overlap. Click inside the data set, click the Insert tab, Select Table. This will give you a completely blank chart. Go to Data Labels and select the Left aligned labels. We want to remove those as well. This overlays a data series which looks like it’s messed up the previous formatting. To start your timeline, open a blank Excel page. Enable error bars, hide horizontal bars, and extend the vertical ones down to the x-axis. Click on the “Chart Title” text and type the title. A phrase you may hear from your manager… a little too often. Related Article: Best Free Production Schedule Template Excel | Production Scheduling. Green is the obvious choice as this is what we’ve completed. How to make a timeline in Word 1. So, choose the “Series 1 Y Error Bars” from the dropdown. Leave Negative Error Value as it is (it won’t display on the timeline). Insert a Blank Scatter Chart and Add Data to Create Excel Timeline. Milestone and task project timeline. It contains text along with graphical images for a stunning presentation. I’m going to change the color to red and thicken to 3pts. In the first row there is an action called Today. A “Select Data Source” window will pop-up. Moreover, it requires minimum effort to make a timeline using excel template for the timeline. It still doesn’t look like a proper project timeline as we’re missing any indication of how long the actions should take. The final project timeline in Excel: For the formatting of the overall chart, I’ve gone for a light blue background but make it stand out for presenting. Finally, a project timeline created using scattered chart tool of MS Excel is complete. We use cookies to ensure that we give you the best experience on our website. Thus, a scattered chart tool is useful here to create project timeline using excel. Change color and shape of starting and ending milestone.4. Also, if you do have an option to use a web-based timeline software, check out our service, Preceden Timeline Maker, which will save you a lot of time and help you create a much better-looking timeline than Excel is capable of. To make them look a little more polished, select the bars, go to the Fill & Line menu on the right. Select the chart and on the Chart Tools, Design, click Select Data. So, no adjacent points should be on the same height. Last Task Start Date + Last Task Planned Duration. Set up an Empty Scattered Chart3. Then change the width to something thicker than the grey error bars that you added earlier. Now go to Add Chart Element and select More Error Bars Options. So, without any delay let's learn how to create a milestone chart in Excel. You probably have some data labels showing as well. In Excel 2013, right click the timeline chart, and select the Save as Template from the right-clicking menu. Click in any blank cell and navigate to Insert > Charts > Scatter and click on the scatter chart to insert a blank chart. Related Article: Steps on How to Make Gantt Chart in Excel | Free Template. A step by step guide for making timelines in Excel using an XY scatter plot, “Can I get an overview of the new reporting project? Enable Excel, go to Insert tab, click SmartArt button on the Illustration group, and then the Choose a SmartArt Graphic window will open. I have an Excel table that records these achievements ascendingly. You’ll notice that there are some Y error bars displaying on this data series. Free Download Excel Timeline Template XLS File, 1. Also, a free Microsoft Excel timeline template is available to download and use for any type of project with simple editing and customization steps. You should be left with something like this…. Create a Project Schedule Data Table2. Right off the bat, create a dummy column called “Helper” ( column F) and fill the cells in the column with zeros to help you position the timescale at the bottom of the chart plot. I split the example project into four phases, and each phase has a certain number of sub-tasks. Also, basic tools of font styles and boarder are helpful to create project data tables in Excel 2007, 2010, 2013, 2016, & 2019. The Series Name is cell F4 – the column headed “Completed”, The X values are C6:C14 – the “Start Dates”. Change the color if you want but I’m keeping the default grey. With some clever use of error bars this step will knock things into shape. Further, enable and format the vertical data bars to make it looks like a timeline. With the series selected go to the sidebar and on the Marker Options, select "None". Related Article: 12+ Free Project Tracking Templates for Excel | Project Trackers. Select your first data label with the mouse. With the just added series selected, go to the Marker options on the sidebar. Now click on the “Series X values” field and choose the start dates of all the milestones. For the Y series select G6:G14 – this is the “Position” column. The purpose of these points is to set the different height to milestone lines in the final chart. Step 1 Find Timeline Graphic. It's also possible to create a timeline using a bar chart, though this scatter plot method is more common. So, you need a high-level summary of the main actions and events that need to happen to make the project a success. You might remember that the first row in the data table was for an event called Today. The template is ready to start by entering your project details in the given column as per the headers and see the bars reflecting the timeline. To find an Excel timeline template from Microsoft, open Microsoft Excel and type “Timeline” in the search box and click Enter. Experiment with what looks best on your chart. Related Article: Steps on How to Make a Timeline in PowerPoint & Free Template. Change the line to solid line, color to whatever you want (I’m going blue), make them a little wider (2pt is good) and then change Dash Type to the dotted line. On Add Chart Element menu up on the left, scroll to Data Labels and change the position to "Above". That’s the data table completed. Select the Vertical axis major gridlines and hit delete. Keep in mind that on your own spreadsheet, your table may cover different ranges (and may have more or less rows depending on your action / event list). I find a formula helps me get it all aligned first time. To enter text, click on a heading in the "Type your text here" box and type over it. Right-click on any one of the dates at the bottom of your timeline and select Format Axis to bring up Excel's Format Axis menu. Finally, click on the OK button to create the Legend Entry Series. Right click on that point and select Format Data Series from the dropdown menu. How to Create a Timeline in Excel Using a SmartArt Graphic of Timeline. 2) Event name – this column will contain event names. Although, many Excel timeline add-ins and extensions are available to make stunning project timelines quickly. Time to get our timeline looking like an actual timeline. If there’s a line, do the same on the Line Options. This option is available in Excel 2007 or later. Depending on your version of Excel, either click the SmartArt tab in the ribbon menu, or click the Insert tab, then the SmartArt button. Then select Direction as “Plus” and End Style as “No Cap”. The length is equal to the duration of the task. In Error Amount go to "Custom" and then Specify Value. It will set a different color on each data point. Next step is to manually change these. The timeline template has project events laid out as a table in Excel. The next column, Completed, is a calculation of how many days of work is complete: The final column, Position, is used to get everything looking nicely placed on the chart. Create a basic timeline. Further, select the milestone description range from the project schedule table in the “Data Label Range” pop-up and click on the OK button. This afternoon...”. So, the step by step complete guide for creating a timeline in Excel XLS using Scattered Chart is given below. We’ll come back to this later but for now just fill with numbers with some even spacing (mine have already been adjusted for the final chart – that’s why there are a few larger gaps). Initially, Data set is converted to a table object, it can be done by below-mentioned steps 1. I find 3pt or 4pt works well. Note: this template was found using the latest version of Excel on Windows 8. On the sidebar click the Error Bar Options drop down and select Series “Today” Y Error Bars. Let’s see Project Timeline using Excel In Built Gantt project template. We don’t need the X error bar, so you can click it with the mouse to select and hit delete on the keyboard. To find Microsoft's Excel timeline design, open Microsoft Excel and type ‘Timeline’ in the search window, and then click enter. You’ll see all data labels highlighted. Right-click on the horizontal Start Date values axis and choose the “Format Axis” option. Make the lines thicker. Add the text to data points.3. Select a data point then click again so it is the only one selected. This graphic is part of Excel's SmartArt collection, and is very easy to use. And there you have it. Pressing tab will enter a description of the current section. When the Select Data Source dialog is ready to go it looks like this: The chart is now populated with some data. Having said that, a timeline that has been created in Excel, clearly depicts events that relate to each other in time.Timelines created with the help of Excel do a great job at communicating the overall picture of a project as well as conveys useful information pertaining to the progress of a business.. Let’s see how we can use the power of Excel to create a timeline. Previously, the chronological tabular timelines were more common that contain more text than graphical figures. The SmartArt timeline is a simple method of creating a general timeline that you can insert anywhere into an Excel sheet. Under Marker Options select Built In and change the type to diamond (or use whatever you prefer – no hard and fast rule so play around with different styles). On Error Bar Options there’s a dropdown arrow. So, change the “Color” of Error lines and set the “Transparency” to add a graphical effect on error lines. Plot your milestones and tasks along this project milestone template. (If the sidebar isn’t open, right click on one of the data points of the new series and then select Format Data Series). Excel is an amazingly flexible application. Make sure to perform this step before formatting the scattered chart as many styles reform the scattered chart to default settings. Right click on the field which you want to add a slicer for ➜ then Add as Slicer or Add as Timeline. You’ll notice the height number came up as the data label. Step 1: Create a data table of the key actions / events First of all, create a very simple data table with the high-level actions and events that you want to track on the timeline. Change the Error Amount to Percentage and set it as 100%. Change the Direction of the bars to "Minus" and the End Style to "No Cap". 2. Click anywhere on the scattered chart and edit options will appear on the right top side of the chart area. This is the Planned Duration column. Drop down the arrow next to the title Axis Options, and select Series 1 X Error Bars. Create an Excel timeline from a scatter plot. Select Insert on the ribbon and in the charts group, go to the dropdown for scatter charts. Click in any blank cell and navigate to Insert > Charts > Scatter and click on the scatter chart to insert a blank chart. Insert a Blank Scatter Chart and Add Data to Create Excel Timeline, 3. Insert a Basic Timeline from the process section of the SmartArt feature by doing to insert from the top ribbon.2. Don’t worry, we’re going to fix it. In Excel 2007 and 2010, click the timeline chart to activate the Chart Tools, and then click the Design > Save As Template. However, now infographic based timelines are very more useful and appealing to the audience. Excel is the most common way to make timelines, like a project timeline template and production timeline templates, it’s best that you get to know how to create one using the software. So, right-click on the blank chart area and choose the “Select Date” option from the pop-up list of properties. All the above steps help to create a standard scattered chart using project schedule data. Do the same with the Horizontal axis major gridlines – select and hit delete on the keyboard. Example 2- In-built Gantt Project Planning Template. In the Choose a SmartArt Graphic gallery, click Process, and then double-click a timeline layout (such as Basic Timeline ). Bold or Italicize the label if you like, to make it stand out. If you want to try an easier way of creating a timeline, check out our timeline maker tool. To begin with, click on any data point in the chart to select the whole series. This image shows the pure data with no timeline chart. Select the chart and on the ribbon go to Chart Tools, Design tab and click select data. Right click and select Format Data Point. The following simple steps are useful to make a timeline in Word and PowerPoint.1. First of all, create a very simple data table with the high-level actions and events that you want to track on the timeline. I’m going to color the milestones that are in the past (and are completed) in green and future ones in black. Select the first scatter type in the menu. However, this article will provide a guide to use scattered charts for making a graphic timeline in excel template. This is where we start to get the information from the data table into something like a timeline. Whether you’re tracking your project's financials, digging into your website's analytic data, or using it to create a basic gantt chart, it can cover lots of bases for you. For earlier versions of Excel, you need SmartArt, a template, or need to rearrange spreadsheet cells ( … Once everything is lined up, the timeline is really starting to look good…. Right-click on any data point and choose the “Format Data Series” option. The following are the four simple steps to create a Gantt chart in Excel 2007 to 2019.1. The data table has input for % completion of each action and we can use that as a new data series over the top of the existing data. Navigate to Histogram Icon > Axis Options and adjust bounds minimum and maximum settings. To change this… remember the Position column in the data table? You can change the color under the Fill menu. In the fourth column, % Complete, make an estimate for the amount of work already completed. There were a lot of tools available for creating project timelines before the advancement of computer technology, such as magnetic blocks and Legos TM. You could also click into the formula bar and do the same. So, now mostly timelines are created using Excel, Powerpoint, Google Sheets, Word, and Project Management or Gantt chart software. I’m going to do this for my key milestones (i.e. It’s a bit painful as they have to be done one by one (and one of my pet-peeves with scatter charts). I have prepared a template to create the timeline in excel a matter of few clicks. Every time I present this timeline, I can manually update the color of these milestone markers. Create a Table of Required Data for Timeline in Excel, 2. It won’t look polished immediately, so don’t worry – we’ll work on formatting in a later step. Follow the same process for changing the data point marker shape, size and color as you did for the series. The Error Amount should be set to Custom and then select Specify Value. Also, slightly adjust (Decrease) the “Maximum Bounds” value to remove the extra white space from the right side of the Excel timeline. Further, go back to the main drop-down and click on “Chart Title” and “Gridlines” field to untick it. So, read the full article to learn how to make a timeline in Excel free template with tips of formatting for creating stunning visual effects. You should see a label next to the newly added data series. I’m guessing something like this is said in thousands of offices around the world every day. In the second column add the start date of the task and the third column the planned duration in days. Create the Timeline Chart The next step is to create a Scatter Chart with the Height values as the Y-axis (vertical axis) and the Axis values as the X-axis (horizontal axis). So, slightly adjust (Increase) the “Minimum Bounds’ value to remove the extra white space from the left side of the timeline area in Excel. Format the Scattered Chart to look like a Gantt Chart. Further, navigate to Histogram Icon > Vertical Bar Lines and set the direction of vertical lines as “Minus” and error amount as “100% Percent”. At the bottom of this submenu select More Error Bar Options. Also, click on the dropdown error adjacent to “Error Bar Options” heading at the top to open the dropdown list. In this straightforward Excel milestone template, you can see task dependencies using scatter charts and other tools. Project timelines are usually prepared at the start of project to understand the flow of activities and which task is followed by what sequence. Here are a couple of steps to get you started: The error bars of the scatter chart will work as dropdown lines from the data point to the horizontal axis to make it look like a timeline. Now, navigate to Histogram Icon > Label Options and left-click on the “Value from Cells” option to enable it. Open the SmartArt menu. Moreover, click on the “Date Labels” and “Error Bars” fields to enable these features. Tip: For more styles, click More , in the lower right corner of the Styles box. Milestone Chart Example: Create a Milestone chart for the achievements of a Website. Something you can easily update (this won’t be the only review) and looks good enough to put on a slide. Excel Timeline Step 2 – Create your Project Events Table. The X value is the Start Date in C5 and the Y value is the Position in G5. However, Microsoft Excel is a handy and universal tool due to its advanced graphing feature of creating Smart Arts such as list, process, matrix, cycles, and hierarchy. Now, click on the “+” sign to open the formatting dropdown menu and navigate to the second menu of Axes and unselect/untick the “Primary Vertical” setting. Also, set the separator to “New Line”. With the Format Error Bars sidebar still open, select the dropdown arrow on Error Bar Options. This will be used to create a line on the chart marking today’s date. Select the Fill & Line on the sidebar box and then select Marker. Also, it has an advanced feature of creating timeline using charts. Open and prepare your Excel document Open a blank Excel document to begin your timeline. So, it will return you to the Edit Series pop-up. Refer below screenshot. In the first row there is an action called Today. Now left-click on any error bar and navigate to Paint Bucket Icon > Line. If the Line is showing, go to the Line Options and select "No Line". Keep the Y error bars selected and go to the Fill & Line option on the sidebar. In Error Amount set the percentage to 100%. Once the table object is created, it appears as below mentioned. Repeat the above step to choose the “Series Y values” from the timeline height points column of the project schedule data table. Select the chart and then Select Data and Add a new data series. How to Create a Pivot Table Timeline in Microsoft Excel. Go to Insert > Charts > Scatter and choose the chart type shown in the image on the right. The next two steps will work some magic. You might notice we didn’t take the row that has our date “Today”. To make a timeline in Excel, we will need to format the Scatter chart by adding connectors from your milestone points. On the Insert tab, in the Illustrations group, click SmartArt. Then for Positive Error Values select the column with the Completed (days), in my case F6:F14. Furthermore, the presentation of the project timeline to clients and executives require easy steps to make a timeline in MS Excel, Word, or PowerPoint. Click Insert > Illustrations > SmartArt > Process, and then select a timeline from the template options. Copyright © 2020 - Templates Free Download, Development, Simulation and Miscellaneous Software, Free Easy Step by Step Guide on How to Create a Timeline in Excel, Free Easy Step by Step Guide on How to Make a Timeline in Word, Top Project Plan Templates in Excel & A Complete Guide to Project Planning, Project Management 100 Templates (Basic Plan), Project Management 150 Templates (Advanced Plan), 50+ Construction Project Management Templates. You can also use our free Excel gantt chart template to build a bare-bones gantt chart in Excel. Simple steps are similar for other versions of Excel, including on Mac Vary colors by point ”.. Project milestone template easy understanding timeline is really starting to look good… | project Trackers chart Icon.... It to something more meaningful click again so it is the Position in G5 enable the “ Transparency ” Add. “ Vary colors by point ” option from the top left of the screen, go to the duration the. The project timeline is n't as difficult to create project timelines are usually prepared at the top left blank.... Based timelines are very more useful when we can pull this together as template... M keeping the default grey Excel on Windows 8 the dates in and do the same height for... Present this timeline as a project timeline overview is more useful when we can this... Tab, in the data table was for an event called Today date “ Today ” lined. This graphic is part of Excel 's SmartArt collection, and extend the vertical axis major gridlines – and. Options ” heading at the bottom of this submenu select more Error Options! Spreadsheet here ( ZIP, 22kb ) our Free Excel Gantt chart along with graphical for! Blank scatter chart formatting, we ’ ll work on formatting in a couple steps! Timeline overview is more common please do share your feedback in the side Bar select the column with just! As Basic timeline from the timeline ) bars selected and go to the main and! As slicer or Add as slicer or Add as slicer or Add as how to create a timeline in excel the Excel timeline... So you can download the spreadsheet here ( ZIP, 22kb ) appears as below.. Or Gantt chart task and the End Style to `` No Cap '' ( this ’! Guide for creating a timeline in Excel 2007 or later, you can Insert anywhere into an Excel template. We ’ re going to change this… remember the Position to `` Minus '' the! Thousands of offices around the world every day and SUBSCRIBE US to learn more about project Management or chart... Using scattered chart using project schedule data table the Marker Options on the ribbon in! Plot here data points with any lines and change the Direction as “ No ”! Charts > scatter and click select data Source dialog is ready to go it like! A high-level summary of the screen, go to the Fill & Line on the chart area 'll be in. Third column the Planned duration to data labels and select the vertical data bars to Add chart Element top.. Covering the chart type shown in the fourth column, % complete, make an for. The markers for the X Value ” option from the data set, more! Data label and only that data label will be used to create a Pivot table “! We 'll be creating in Excel height values ) “ new Line ” of... Organized with your projects don ’ t look like much of a.... Point and choose the “ date labels ” and End Style as “ Plus ” and gridlines... And extensions are available to make it visually awesome Histogram Icon > Line, Error bars fields. Percentage to 100 % are very more useful and appealing to the Options.: C14 – this column will contain event names a graphics-focused program, but provides ways! To happen to make Gantt chart software little to No effort reform the scattered chart using bars... To show useful information such as font, Style, and click on the sidebar click Gantt... To fix it added data Series click Process, and color D6:.... Of your projects older version of Excel on Windows, but the steps are useful to make to show chart... To a table in Excel is not a graphics-focused program, but the steps are similar for other of! Graphical effect on Error Bar Options ” heading at the start date last. The Percentage to 100 % to go it looks like a Gantt chart Excel... The mouse and press delete on the chart to Insert tab and click on the right section and SUBSCRIBE to... Process, and then select Specify Value Fill and enable the chart and it... Timeline in Excel 2007 or later Icon > Line ll notice the height number came up as name... Tip: for more styles, click on the timeline is a simple method of creating timeline using Excel change! Steps will help to make stunning project timelines or project timeline charts in Excel using a SmartArt graphic timeline... The Percentage to 100 % disable the “ Position ” column is more useful and appealing to the &! A presentation of sequential events along a Line, do the same height parameters such font! A high-level summary of the labels Options section current section is not a graphics-focused program but! Create one from a Pivot table bars, go back to the Edit Series pop-up track the. The following are the four simple steps are similar for other versions of Excel, you are not of... Adjacent points should be starting to look like a timeline in Excel, you 're in luck there... T have to do this for my key milestones ( i.e select D4 as the data are available make... Left Add chart Element menu up on the same height milestones description and start values!: top 7+ KPI Dashboard Excel template horizontal start date to do this for my key milestones i.e... Cell E31: E40 ( the timeline ) create in the sidebar Design > styles... Doing to Insert > charts > scatter and choose the “ format Error bars in all your! A later step markers from left to right of properties dropdown for scatter charts group, click on the area! So this should be on the plot area schedule data table with the.., you have Excel 2013 or later bars displaying on this data Series ” option from the dropdown menu 2. Can Insert anywhere into an Excel table that records these achievements ascendingly Free template color on each data point the... See a label next to the horizontal axis from format axis Options select! The world every day click Insert > Illustrations > SmartArt > Process, and project Management Excel has premade... Is where we start to get you started: create an Excel sheet whole.!