The best way to track and visualize your progress to a targeted amount in Excel is with an Actual vs. Target Chart. To create and Actual vs. Target chart, we’ll first select our data, then go to the Insert tab, open the Combo Chart dropdown, and select “Clustered Column - Line.” This inserts our chart, and we can now quickly clean it up by selecting and deleting the titles and gridlines with the Delete key.
Next, we need to transform the target data series from a solid line to dashes. To do this, we’ll first remove the solid line by right-clicking the line within the chart and selecting “Format Data Series” - this will open the Format Data Series window, where we can select “No Line” under the Line dropdown. Now we’ll just change the data points to dashes by selecting the Marker icon, opening the Marker Options dropdown, and selecting “Built In.” Finally, we can update the icons to dashes by selecting the Dash icon under the “Type” dropdown.
Now we’ve got a data series that is nicely formatted with dashes, but the dashes are a little bit too small. We can make the dashes more proportionate to the data bars by using the Size Box to change their size - much better!
