Sometimes in a chart we want to highlight certain bars so the reader can quickly see the important information. An example would be to highlight our company when it is compared with its competitors (see the chart below).
In this chart, it is very easy to see how Fox Books compares to its competitors. There is very little brain power required as the key information clearly stands out. Firstly, the data is presented is size order, so we can easily see the rank and value. Secondly, the bar relating to Fox Books is highlighted in a different color. This post will show you how to create a similar chart, starting with the source data and working through to a final visualization.
The source data
The source data we are using is based on an unsorted list. In this scenario, we are assuming we cannot or do not want to sort the data manually. We will use basic formulas to put the data into the correct order before creating the chart.
Ranking the source data
The first task is to get the data into descending order. To do this we will use the RANK function.
The RANK function takes 3 arguments:
- Number – The value you wish to find the rank for.
- Array – An array of numbers you wish to rank against
- Order – this argument is optional, 0 or blank = descending order, or 1 = ascending order.
We can use the RANK function to find out the order of our bars should be in.
The formula in Cell A2 is:
This formula can be copied down to the bottom of the list. Now our data is ranked.
Note, we will have an issue where there are duplicate values in the data, as the RANK function will give both values the same rank. To prevent this, we could change the formula in Cell A2 to be as follows:
The COUNTIF adds 1 to the RANK if there is a tie between values, this ensures all the rank numbers are unique. This formula can be copied down to the bottom of the list.
Creating the chart data
In Cells E2-E8 I have listed the numbers 1 to 7 in order, as there are 7 companies in the source data.
In the previous section, we inserted the RANK function into the first column, this is so we could use VLOOKUP to get the ordered data. We do not need to insert the rank into the first column, if we use the INDEX/MATCH or some other functions. However, for this example we will use VLOOKUP as that is a more well-known function.
The formula in Cell F2 is:
The formula in Cell G2 is:
The formulas in F2 and G2 can be copied down to the bottom of the list.
We now have our data listed in descending order. The next step is to guarantee the values associated with our company is always highlighted. To do this we will create two columns of data, one for the normal values, and one for the highlighted value.
We can use a simple IF function. The formula in Cell H2 is:
The formula in Cell I2 is:
The formulas in H2 and I2 can be copied down to the bottom of the list.
The NA() function is used to force an #N/A error as the result of the IF function. We use this because Excel charts ignore #N/A values completely. If a value is blank or zero Excel will show the value as zero, but #N/A does not display at all. This is important, as we only want data labels displayed for the bars which are displayed.
Formula Magic with Dynamic Arrays
Have you ever faced these spreadsheet scenarios?
- How can I use VLOOKUP to return all the matching items, not just the first?
- How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
- How can I quickly create unique lists of items to use with my SUMIFS calculation?
- How can I stop copying down formulas every time my source data changes.
- How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.
Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂
Creating the chart
Once the data is ready, we can create a Stacked Bar Chart. Select Cells F1-I8 then click Insert -> Charts -> Stacked Bar (this is based on Excel 2016; other versions may vary slightly).
This will create a chart with 3 sections, Market Share, Normal and Highlight. Delete the section of the chart which relates to the Market Share as we do not need this information in the chart (simply click on the first color and press delete)
Right-click on the label axis, click Format Axis. From the Format Axis window select Categories in reverse order. This will put the data into the correct order.
Note: If we sorted our data in ascending order when using the RANK function it would not be necessary to put the Categories into reverse order.
The next steps are down to personal preference, but to replicate the chart I have made do the following:
- Delete the chart title, legend, numbered axis and gridlines (this removes unnecessary noise from the chart).
- Add data labels and format them as required.
- Increase the Gap Width to make the bars wider.
- Make the bar associated with the Normal value a mid-grey color
- Make the bar associated with the Highlight value a suitable color for it to stand out.
The chart should look similar to this:
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: