In a column chart in Excel, all columns are usually the same color. How do you automatically make one column stand out with its own color?
Create table
We take as an example the fictitious turnover of twelve months. In a column chart, we want to highlight the month (the column) with the highest revenue.
The trick is: we display two series of numbers in the graph, in two series of columns, overlap those columns and then make sure that we only see one of the second series of numbers. Copy the sales figures from B5 through B16 to C5 through C16.
Create chart
For the graph you select the months and the two series of numbers: A5 to C16. Click on the tab Insert, Insert column or bar chart and choose the first type of chart. That’s under 2D column the Grouped column. click on Chart title and press the Delete key, click (at the bottom) on Series 1 Series 2 and delete that too.
Double click on an orange column; the task pane Format data series appears on the right of the screen. Make sure the right button is selected and refill Overlap of series 100% in.
Do you now only see the orange columns? Good, they are now in the foreground. Click on one of the orange columns, click on the right button (the one with the paint pot) in the task pane and choose Color red. Now all these columns are red. If you at Width spacing 50 narrows the white space between the columns.
Draw up a formula
In order for us to see one red column in the chart, we need to make column C display one number. We just want to see the number of the highest turnover. To do this, a formula must copy the largest number from column B. We find the largest number from B5 to B16 with MAX (B5: B16). In C5 comes a formula that says: if B5 is the largest number in the series, take over B5 and otherwise remain empty.
We specify empty text with two double quotation marks. Since every copy of this formula has to look at B5 through B16, we’ll lock those row numbers with a $. To do this, put the formula in C5:
= IF (B5 = MAX (B $ 5: B $ 16), B5, ”“)
and copy it down. One number of these is the largest, it becomes visible and the other cells remain empty. This makes one red column visible in the graph and the blue columns of the other months reappear. If another month has the highest sales, column C displays that number and its column automatically turns red.
Get started
It Excelfile containing this graph is ready for you www.computeridee.nl. click on Downloads, From the magazine and get the file Chart with featured column.xlsx on. There you will also find a tab in which you can choose the month you want to stand out with a drop-down list.
.