Think-cell is the leading developer of graphics software to improve professional slide production in Microsoft PowerPoint. Our software automates the creation of charts and layouts to make working.
- Go to Insert Sparkline and select the type of sparkline (you have 3 options – line, column and win-loss chart) Specify a target cell where you want the sparkline to be placed. Optional: Format the sparkline if you want. Here is a short screen-cast showing you how a sparkline is created.
- We think that’s an excellent question, and the answer is, like R-values for rigid foam boards, it depends. Open Cell Foam Insulation Values – R3 – R4. Open cell spray foams are between.5 and 1 lbs per cubic foot, and have an R value of 3.0 – 4.0 per inch of insulation.
It’s easy to add conditional formatting icons in Excel, by selecting one of the built in options. These were introduced in Excel 2007, and improved in Excel 2010. However, you still can’t get all the icons in any colour. For example, you can show Harvey Balls (the 5 Quarters icon set), but only in black and white. We’ll see how to create colored Harvey Balls in Excel.
Create Your Own Icons
One way to overcome this limitation is to create your own icon set. I showed one way to do this last year, and you can see the details here.
That technique used formulas to get a symbol from a lookup table, and the cells had to be formatted in Wingding font.
Show Symbols With Number Format
Last week, Jim McGarity sent me a sample file with his version of icon sets. Instead of using a formula to pull the icon into the cell, Jim created custom number formats.
Then, he used those custom formats in the conditional formatting. Brilliant idea, Jim, and thanks for sharing it!
Advantages and Disadvantage
The advantages to Jim’s technique are:
- No formulas — formulas could slow down a large file
- Icon and number can be shown in the same cell, or separate cells
- The worksheet cells can use any font – a symbol font isn’t required
The disadvantage is:
- This doesn’t work in Excel 2011 for the Mac, where number formats aren’t part of conditional formatting.
We’ll use Jim’s technique to create Harvey balls in red, yellow and green.
Build the Lookup Table
You could use this technique without a lookup table, but it’s easier to manage the range limits if you have them on a worksheet, instead of hard coded into the conditional formatting rules. So, we’ll build a table that shows where each level starts, and the symbol for each level.
To set up the lookup table:
- In D2:D4, type the minimum score for each level — 0, 35 and 65
- In cells E2:E4, use Excel’s Insert Symbols feature to enter the empty circle, half-filled circle, and filled circle, from the Segoe UI Symbol font.
- (Optional) Change the font colour for each symbol, as shown in the screen shot below.
NOTE: The symbols are in the table for reference only – you could delete these later, after you set up the number formatting.
Create the Custom Number Formats
Next, we’ll create a number format for each symbol:
- Select the cell with the empty circle symbol
- In the formula bar, select the icon, and press Ctrl + C, to copy it
- Press Enter, to exit the Formula Bar
- Select an empty cell on the worksheet, and press Ctrl + 1, to open the Format Cells window.
- Click the Number tab, and click the Custom category
- Clear out any text in the Type box
- Press Ctrl + V, to paste the symbol
- Type a space, then two question marks, and a zero: ○ ??0
TIP: If you just want the icon, don’t include the other characters in the number format.
You can even create a number format without an icon – just use a space character, so nothing will appear in the cell. - Click OK, then repeat the steps for the other two symbols.
NOTE: The question mark is a spacer, and it will ensure that the numbers line up correctly. Visit the Microsoft website for more information on custom number formatting.
Apply the Conditional Formatting
The final step is to apply the conditional formatting to the column of Scores.
- Select all the cells with scores – cells B2:B9 in this example
- On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule
- Click Use a Formula to Determine Which Cells to Format
- For the formula, enter: =B2>=$D$2
TIP: Make sure that the first reference is to the active cell – B2 in this example
- Click the Format button, and on the Number tab, select the custom format – ○ ??0
- On the Font tab, select Green as the font colour, then click OK.
- The preview will show the formatting, with the icon.
- Click OK, twice, to close the Conditional Formatting windows.
Repeat the steps for the other two symbols, then close the Conditional Formatting window.
The Completed Formatting
Think Cell Chart
After you’ve applied the formatting, the icons will appear to the left of the number in column B, separated by a space. Because we used the question mark characters, the digits line up correctly. The 9, in cell B3, is at the far right of the cell, and lines up with the final digit in the 100, in cell B6.
TIP: If you want the number in black font, show the icons in separate cells, that are linked to the score cells. When you create the custom number formats, paste in the symbols, and don’t enter the other characters.
Download the Sample File
To download the sample file, please visit the Conditional Formatting Examples page on my Contextures website.
This example is in the 2010/2007 version of the download file, on the ColorIconsNum sheet.
Think Cell Chart 5 2
________________