Since our data for the graph is now finalized, all we have to do is create our line chart. If there is not a “Y” value in the cell, the resulting cell value will retrieve the value in Cell A2 (the Original Horizontal Axis).įinally, we don’t need to do anything with the data that will be represented in the line chart, so in cell G2, we can simply put in the formula =B2 to copy over the original data values to match our new horizontal category labels. This will create our highlight for that category label. If yes, then the formula will insert a carat “^” as the formula result. The formula in cell F2 =IF(C2=”y”,”^”,A2) is looking at cell C2 (our Highlight Y/N column of data we setup in the previous step) to see if there is a “Y” value. It shouldn’t be that way, so we need to force each axis label into one column with the Space in the formula. Notice that the California horizontal multi-level category label now spans 2 different states. If you do not, then the same chart will look like this: Line Chart Axis Highlight with No Space in the Formula Note that you must include this space in the formula. If there is not a “Y” value in the cell, then it will put in a Space ” ” as the formula result. If yes, then the formula will retrieve the value in Cell A2 (the Original Horizontal Axis). The formula in cell E2 =IF( C2=”y”,A2,” “) is looking at cell C2 (our Highlight Y/N column of data we setup in the previous step) to see if there is a “Y” value.
Let’s investigate these formulas to see what we are doing. So we should create the following columns of data in cells E1:G1 and formulasĪfter you create your formulas in cells E2, F2 and G2, then copy the formulas down to the end of your data range. If you found the website and tutorials helpful, please consider donating to keep the lights on. You can also put this on another worksheet tab, but it is your personal preference.
I will typically create the chart data range adjacent to the actual data. Now that we have a way to determine the horizontal axis labels that will be highlighted, we can create our formulas. If so, you should put a formula like this in cell c2 and copy it down to the end of your data set: =IF(RANK(B2,$B:$B)<=5,”y”,””). For instance, if you want to highlight any row that has more than a certain threshold or if you want to highlight the top 5.
Highlight Horizontal Axis Labels Picker ColumnĪlso, you can create a formula here if you like if you want to highlight different types of data. So our final chart will show something different for the horizontal axis for the Florida and California labels. Now you can just create another column and put a text “y” or in the row you want to highlight. If our original data is in columns A and B, then we just need to add a column C where we will indicate if the horizontal axis label should be highlighted. Step-by-Step 1) Add a Horizontal Axis Picker/Highlight Column to Data Setįirst, we need to add a column next to our original data set.
0 kommentar(er)
