It’s a minor thing but the options for markers showing the data points on Excel charts are a little limited. This isn’t normally a big issue however it is remarkably simple to change them.
Take a normal chart with data markers:
It’s a minor thing but the options for markers showing the data points on Excel charts are a little limited. This isn’t normally a big issue however it is remarkably simple to change them.
Take a normal chart with data markers:
To change the dot markers into stars (for instance) we first have to display the clip art on the page at the desired size:
- Insert / Clip art or on later versions of Excel Insert / Online pictures
- Search for your clip art and select it
- Add to page
- Resize
Now copy the clip art from your page. Then select the line chart and paste.
Now this might be a bit bling for the average business presentation however it can also be used to highlight only one data point – maybe the top sales month. To do this simply select the single data point:
- Click the line and then click the desired data point
- Paste (<CTRL><V> works very well for this
This method works well when you know which data point needs to be highlighted however often we want Excel to work this out. To do this we need to chart two series - one for all the data and one showing only the max value.
To do this first set up the new series with dummy data which will make it easier to select each series.
- Insert a new column "Max" along site the existing data and populate this with dummy data, in this case 10
- Copy the new data and paste it onto the chart - this will generate a new line chart
- Select the original line and remove the data markers
- Copy the star onto the new line to set all markers on the "max" chart to be stars
Then replace the dummy data with the real calculation to only show the Max value and have error values in the other cells which can't be charted.
- In cell C2 enter the formula =IF(B2=MAX($B$2:$B$7),B2,NA())
- Copy this down to replace all the Max data
