How do I utilize conditional formatting with linked cells?
BigPicture's Link to Cell Formatting feature in conjunction with Excel's conditional formatting feature can add another dimension to any of your maps. By applying conditional formatting to your data and maps, you can quickly identify variances in a range of values with a quick glance. The following examples will demonstrate how this powerful combination can be used in free form maps, data maps and organizational charts.
The Conditional Formatting in Excel steps below show you how to use Excel's built in conditional formatting tools. Please note that you can also utilize BigPicture's ability to link topics to manually formatted cells. (i.e. cells that have been formatted with Excel's standard fill options)
Conditional Formatting in Excel
For those whose may not be familiar with how to utilize Excel's conditional formatting feature. Here are the basic steps to get you started.
- Select the data that you want to conditionally format
- Click on Excel's Home tab
- In the Styles group click the arrow next to Conditional Formatting, (See #1)
- Click Color Scales and choose the color scale that you would like to use. If you would prefer to have icons or data bars appear in your BigPicture Topics, you can choose from those options as well.
NOTE: In some versions of Excel, you can gain quick access to conditional formatting by clicking on the contextual icon that appears after you have made a selection in Excel.
Once you have acclimated to the basic conditional formatting, you can experiment and add some advanced features using the New Rule...and Manage Rules... options.
Conditional Formatting in Free Form Maps
Once you have conditionally formatted your cells, BigPicture can color topics with that conditional formatting by utilizing the Link to Cell Format command. This command picks up both conditional formatted cell colors, as well as conditional formatted icons, and displays them in linked topics. When the data is changes, the Topic color and icons change.
When working with a free form map, there are two ways to access the Link to Cell Formatting command: Via the Topic Pane and Via the Right Click Menu.
Via Topic Pane
- Click on the topic that you would like to link to cell formatting (See #1)
- Click on Topic in the View section of the BigPicture Ribbon (See #2)
- Open the Format section in the Topic Pane (See #3)
- Click in the box to the left of Link to Cell (See #4)
- Manually enter the cell reference in the Use Format from Cell box or click on the icon to the right to select the cell in your worksheet. (See #5)
Conditional Formatting in Org Charts
Conditional formatting in Org Charts can allow you to visualize your data at a quick glance. Does your company have to cut labor costs or reshuffle some employees? With BigPicture you can color code your Org Chart based on salary to see whom is overpaid for their level in the company. Or color code it base on performance to see whom is excelling in their position? Or create a new column of data that you can conditionally format that ties together cost of living factors, salary and performance ratings.
The first image below shows how an example file looks with the Rating column conditionally formatted. (See #1) The second picture shows what the corresponding Org Chart will look like. (See #2) There is no special setting - BigPicture does the work for you by coloring the associated topic based on the attributes of the leftmost cell in a particular row that has formatting assigned to it. Once an Org Chart is made, and a value in the column that has conditional formatting is changed, the appropriate topic color and icon will be updated in the chart by simply pressing the [F9] key.
Conditional formatting icons can also be used in Org Charts. The Conditional formatted icons will appear in the topics if they are assigned as a conditional format option in the corresponding cell. (See #3)
NOTE: Because BigPicture is set to color code the topic based on the leftmost formatted cell for any particular row, you can color code base on different columns of data for different situations in your data set. For instance you could conditionally format your upper management based on performance and the remaining staff could be conditionally formatted based on salary.
Conditional Formatting in Data Maps
Data Maps can also show conditional formatting. End topic tables will pickup up cell colors from the original data set and display the colors on the appropriate entries in the end table. If you choose to display end topics as individual topics, conditional colors and icons will be carried over to the map.
Similarly to Org Charts, there is no special setting to do this. BigPicture does the work for you by color coding the topic based on the attributes of the leftmost cell in the associated row that is formatted.