Build a comparison chart
I found a chart that I wanted to show you how to build.
The printable planting guide is found here. This is what the color means:
Light green : plants grow well together
Red : don´t plant together
Dark green : the combination helps bug control
Yellow : carrots will have good flavor but stunted roots.
Grey : beneficial to garden in general
Instructions
- Start typing the plants in cell A2 and below.
- Copy the plant names and right click on cell B1. Click "Paste Special...".
- Click "Transpose" and then click OK button.
- Select and right click on cell range B1:AE1.
- Click "Format Cells...".
- Go to tab "Alignment".
- Change "Orientation" to 90 degrees. Click OK.
- Select column B:AE and change column width to 20px.
- Type 1 for green, 2 for red, 3 for grey, 4 for dark green and 5 for yellow in cell range B2:AE31
- Select cell range B2:AE31 and go to tab "Home" on the ribbon.
- Click "Conditional formatting" button.
- Click "New Rule.."
- Select "Use a formula to determine which cells to format"
- Type =B2=1
- Click "Format..." button
- Go to tab "Fill"
- Pick a color
- Click OK button twice
- Repeat step 11 - 18 for 2, 3, 4 and 5 but the formulas become =B2=2, =B2=3 , B2=4 and B2=5 and the fill colors are different.
- Hide the numbers in cell range B2:AE31. Select B2:AE31 and press CTRL + 1. Click "Custom" category. Type ;;; click OK.
- Apply borders. Select A1:AE31. Go to "Home" tab on the ribbon. Click "Borders" and select "All Borders".
You could color each cell manually, but in my opinion building this chart is a lot easier using numbers in cells.
Chart posts that may inspire you
- Heat map using pictures
- Use a map in an excel chart
- Shift Schedule
- Dynamic Gantt charts in excel 2007
- Dynamic chart – Display values from a table row or column
Download excel *.xlsx file
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are […]
This chart is an arrow chart that has horizontal and vertical lines, positive arrows are green and negative arrows are […]
The picture above shows the following equation x^3+3*x^2-3 plotted on an x y scatter chart. Here are the instructions on how […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.