#How to create box plot excel 2011 mac how to#
#How to create box plot excel 2011 mac download#

#How to create box plot excel 2011 mac how to#

To complete this Instructable you will need the following: A PC that runs on Windows and has Microsoft Excel Basic knowledge of how to operate a computer The Instructable should take you 5-10 minutes to complete.

#How to create box plot excel 2011 mac download#

Excel 2011 Scatter Plot Download Step 1.

Excel 2011 Scatter Plot How To Create A.

This example is in the 2010/2007 version of the download file, on the ColorIconsNum sheet. To download the sample file, please visit the Conditional Formatting Examples page on my Contextures website. When you create the custom number formats, paste in the symbols, and don’t enter the other characters. TIP: If you want the number in black font, show the icons in separate cells, that are linked to the score cells. 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. Because we used the question mark characters, the digits line up correctly. The Completed FormattingĪfter you’ve applied the formatting, the icons will appear to the left of the number in column B, separated by a space. Repeat the steps for the other two symbols, then close the Conditional Formatting window.

Click OK, twice, to close the Conditional Formatting windows.

The preview will show the formatting, with the icon.

On the Font tab, select Green as the font colour, then click OK.

Click the Format button, and on the Number tab, select the custom format – ○ ?0.

TIP: Make sure that the first reference is to the active cell – B2 in this example

Click Use a Formula to Determine Which Cells to Format.

On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule.

Select all the cells with scores – cells B2:B9 in this example.

The final step is to apply the conditional formatting to the column of Scores. Visit the Microsoft website for more information on custom number formatting. NOTE: The question mark is a spacer, and it will ensure that the numbers line up correctly.

Click OK, then repeat the steps for the other two symbols.

You can even create a number format without an icon – just use a space character, so nothing will appear in the cell. TIP: If you just want the icon, don’t include the other characters in the number format.

Type a space, then two question marks, and a zero: ○ ?0.

Click the Number tab, and click the Custom category.

Select an empty cell on the worksheet, and press Ctrl + 1, to open the Format Cells window.

In the formula bar, select the icon, and press Ctrl + C, to copy it.

Select the cell with the empty circle symbol.

Next, we’ll create a number format for each symbol: NOTE: The symbols are in the table for reference only – you could delete these later, after you set up the number formatting.

(Optional) Change the font colour for each symbol, as shown in the screen shot below.

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.

In D2:D4, type the minimum score for each level - 0, 35 and 65.

So, we’ll build a table that shows where each level starts, and the symbol for each level. 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. We’ll use Jim’s technique to create Harvey balls in red, yellow and green.

This doesn’t work in Excel 2011 for the Mac, where number formats aren’t part of conditional formatting.

The worksheet cells can use any font – a symbol font isn’t required.

Icon and number can be shown in the same cell, or separate cells.

No formulas - formulas could slow down a large file.

Brilliant idea, Jim, and thanks for sharing it! Then, he used those custom formats in the conditional formatting. Instead of using a formula to pull the icon into the cell, Jim created custom number formats. Last week, Jim McGarity sent me a sample file with his version of icon sets. That technique used formulas to get a symbol from a lookup table, and the cells had to be formatted in Wingding font.

I showed one way to do this last year, and you can see the details here. One way to overcome this limitation is to create your own icon set.

We’ll see how to create colored Harvey Balls in Excel. For example, you can show Harvey Balls (the 5 Quarters icon set), but only in black and white. However, you still can’t get all the icons in any colour. These were introduced in Excel 2007, and improved in Excel 2010. It’s easy to add conditional formatting icons in Excel, by selecting one of the built in options.