Rudy asks in this post Use a mouse hovering technique to create an interactive chart:

Is it possible to create this interactive chart into interactive candlestick chart to compare two or more charts ?

Hover over a company name and the chart instantly changes data source.

Formula in cell J2:

Formula in cell J3:

Formula in cell K2:

Formula in cell K3:

Formula in cell L2:

Formula in cell L3:

User defined function

Function MouseHover(str As String)
With ActiveSheet.ChartObjects("Chart 2")
    If str = "Google" And Sheet1.Range("A1") <> str Then
        Sheet1.Range("A1") = "Google"
        .Chart.SetSourceData Source:=Sheets("" & Range("A1") & "").Range("A2:A54,C2:F54")
        .Chart.Axes(xlValue).MaximumScale = Range("K2")
        .Chart.Axes(xlValue).MinimumScale = Range("L2")
    ElseIf str = "Apple" And Sheet1.Range("A1") <> str Then
        Sheet1.Range("A1") = "Apple"
        .Chart.SetSourceData Source:=Sheets("" & Range("A1") & "").Range("A2:A54,C2:F54")
        .Chart.Axes(xlValue).MaximumScale = Range("K3")
        .Chart.Axes(xlValue).MinimumScale = Range("L3")
    End If
End With
End Function

Download excel *.xlsm file

Interactive candlestick chart.xlsm

Column B contains random dates. The array formula in column D returns consecutive dates from column B.

Array formula in cell D3:

How to enter an array formula

1. Select cell D3
2. Copy array formula (Ctrl + c)
3. Click in formula bar
4. Paste formula (Ctrl  + v)
5. Press and hold CTRL + SHIFT
6. Press Enter simultaneously
7. Release all keys

How to copy array formula

1. Select cell D3
2. Copy (Ctrl + c)
3. Select cell range D4:D5
4. Paste (Ctrl + v)

Find consecutive numbers

Since dates are numbers in excel you can also use the array formula to identify consecutive numbers.

Find non-consecutive dates

Array formula in cell D3:

Explaining the array formula in cell D3 (Consecutive dates)

Step 1 - Find dates that have a consecutive date except the last consecutive date

COUNTIF($B$3:$B$8,$B$3:$B$8+1)

becomes

=COUNTIF({41430; 41436; 41432; 41441; 41431; 41438}, {41430; 41436; 41432; 41441; 41431; 41438}+1)

becomes

=COUNTIF({41430; 41436; 41432; 41441; 41431; 41438}, {41431; 41437; 41433; 41442; 41432; 41439})

and returns

{1;0;0;0;1;0}

If you are interested in how the COUNTIF function works, read this post: COUNTIF function

Step 2 - Find dates that have a consecutive date except the first consecutive date

COUNTIF($B$3:$B$8,$B$3:$B$8-1)

becomes

=COUNTIF({41430; 41436; 41432; 41441; 41431; 41438}, {41430; 41436; 41432; 41441; 41431; 41438}-1)

becomes

=COUNTIF({41430; 41436; 41432; 41441; 41431; 41438}, {41429; 41435; 41431; 41440; 41430; 41437})

and returns

{0;0;1;0;1;0}

Step 3 - Add arrays using OR logic

COUNTIF($B$3:$B$8,$B$3:$B$8+1)+COUNTIF($B$3:$B$8,$B$3:$B$8-1)

becomes

{1;0;0;0;1;0} + {0;0;1;0;1;0} and returns {1;0;1;0;2;0}

Step 4  - Find values in array larger than 0 (zero)

(COUNTIF($B$3:$B$8,$B$3:$B$8+1)+COUNTIF($B$3:$B$8,$B$3:$B$8-1))>0

becomes

{1;0;1;0;2;0}>0

and returns {TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}

Step 5 - Replace TRUE with corresponding date and FALSE with blanks

IF((COUNTIF($B$3:$B$8, $B$3:$B$8+1) + COUNTIF($B$3:$B$8, $B$3:$B$8-1))>0, $B$3:$B$8, "")

becomes

IF({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, $B$3:$B$8, "")

becomes

IF({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {41430; 41436; 41432; 41441; 41431; 41438}, "")

and returns {41430; ""; 41432; ""; 41431; ""}

Interested in how the IF function works, read this post: IF function explained

Step 6 - Find the n-th smallest date

SMALL(IF((COUNTIF($B$3:$B$8, $B$3:$B$8+1) + COUNTIF($B$3:$B$8, $B$3:$B$8-1))>0, $B$3:$B$8, ""),ROW(A1))

becomes

SMALL({41430; ""; 41432; ""; 41431; ""}, ROW(A1))

becomes

SMALL({41430; ""; 41432; ""; 41431; ""}, 1)

and returns 41430 (June 5, 2013) in cell D3.

Interested in how the SMALL function works, read this post:
SMALL function and LARGE function

The ROW function: ROW function explained

Conditional formatting consecutive dates

Conditional formatting formula:

=(COUNTIF($B$3:$B$8,B3+1)+COUNTIF($B$3:$B$8,B3-1))>0

How to apply conditional formatting

1. Select cell range B3:B8
2. Go to "Home" tab
3. Click "Conditional formatting" button
4. Click "New Rule..."
5. Click "Use a formula to determine which cells to format"
6. Format values where this formula is TRUE:
   =(COUNTIF($B$3:$B$8,B3+1)+COUNTIF($B$3:$B$8,B3-1))>0
7. Click "Format.." button
8. Go to "Fill" tab
9. Pick a color
10. Click OK
11. Click OK

Read more about conditional formatting:

• Working with formulas in conditional formatting
• Search with conditional formatting
• How to use a table name in data validation lists and conditional formatting formulas

Download excel *.xlsx file

Consecutive dates.xlsx

Functions in this post

COUNTIF(range, criteria)
Counts the number of cells within a range that meet a single criterion that you specify.

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array, k)
Returns the k-th smallest value in a data set

ROW(reference)
Returns the row number of a reference.

FREQUENCY(data_array, bins_array)

Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than the bins_array

data_array - is an array of or reference to a set of values for which you want to count frequencies.

bins_array - is an array of or reference to intervals into which you want to group the values in the data_array

Note! The FREQUENCY function returns two or more values in an vertical array and you enter it as an array formula. Blanks and text strings are ignored.

Example 1

Array formula in cell range D3:D6:

Explaining the calculations

FREQUENCY(data_array, bins_array)

FREQUENCY(B3:B10,C3:C5)

becomes

FREQUENCY({1; 2; 2; 5; 4; 7; 7; 10},{2; 5; 10})

and returns a vertical array of numbers: {3; 2; 3; 0}.

There are 3 values that are smaller or equal to the first value (2) in the bins_array: 1, 2, 2.

There are 2 values that are larger than 2 and smaller or equal to the second value (5) in the bins_array: 4, 5

There are 3 values that are larger than 5 and smaller or equal to the third value (10) in the bins_array: 7, 7, 10

There are 0 (zero) values  that are larger than 10 in the bins_array.

Remember! The FREQUENCY function returns an array of numbers and it has one more element than the bins_array. In this example the bins_array has 3 values, FREQUENCY function returns 4 values.

Example 2

Array formula in cell range F3:F6:

Explaining the calculations

Step 1 - Filter values

IF(B3:B10="A",C3:C10,"")

becomes

=IF({"A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"}="A",{1; 2; 2; 5; 4; 7; 7; 10},"")

and returns

{1; ""; 2; ""; 4; ""; 7; ""}

Step 2 - Frequency

FREQUENCY(IF(B3:B10="A",C3:C10,""),E3:E5)

becomes

FREQUENCY({1; ""; 2; ""; 4; ""; 7; ""},E3:E5)

becomes

FREQUENCY({1; ""; 2; ""; 4; ""; 7; ""},{2; 5; 7})

and returns {2; 1; 1;0}

Download excel *.xlsx file

FREQUENCY function.xlsx

Functions in this post:

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Here is how I did it:

Step 1 - Create a drop down list

1. Select cell J2
2. Go to tab "Data"
3. Click "Data Validation" button
4. Allow: List
5. Source: B19:F19

Step 2 - Copy table

1. Select first table (table1: A19:F31)
2. Copy (Ctrl + c)
3. Paste to H19:M31
4. Clear values

Step 3 - Formula in duplicated table

1. Select the first empty value (cell I20) in the second table (table2)
2. Click in formula bar
3. Use the following formula:
   =IF($J$2=Table2[[#Headers],[A]],Table1[@A],"")
4. Press Enter
5. Select cell I20
6. Click and drag black dot to the right, all the way to table column E.
   
7. Select cell range J20:M20
8. Double click black dot. The formulas are copied to the remaining cells below in the table.

Step 4 - Insert a line chart

1. Select table1
2. Go to tab "Insert"
3. Click "Line"
4. Click "Line chart"

Step 5 - Color lines

1. Select a data serie
2. Right click on the selected data serie
3. Click "Format Data Series..."
4. Select "Line Color"
5. Select "Solid line"
6. Pick a light gray color
7. Click Close

Repeat line 1 to 7 with remaining data series.

Step 6 - Add the duplicated table to the second axis

1. Right click on chart
2. Click "Select Data..."
   
3. Click "Add" button
4. Series name: I19
5. Series values: I20:I31
6. Click OK
   
7. Click "Edit" button
8. Select cell range H20:H31
9. Click OK
10. Click OK
11. Select item A in the drop down list
12. Right click on data series A on the chart
13. Click "Format Data series..."
14. Select secondary axis
15. Click OK

Repeat above steps 1 - 15 with table column B, C, D and E.

Remove secondary y axis, see picture below.

Step 7 - Remove series in first axis from legend

Remove A to E colored gray from the legend. (Click on each character and press Delete)

Delete major gridlines

Download excel *.xlsx file

Highlight series in a line chart.xlsx

Functions in this post

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

I made a heat map calendar a few months ago and it inspired me to do this post. The heat map calendar changes background color of each cell unlike the technique used here where I change the brightness of each picture.

Here is how I did it:

Step 1 - Find and copy a map

1. I found a map at the Wikimedia commons website
2. Copy the map and paste it to your favorite image editing software.

Step 2 - Copy each state/country/territory

1. Select the "Magic Wand" tool. I made this in paint.net.
2. Tolerance : 0%
3. Click inside a state
4. Copy (Ctrl + c)

Step 3 - Paste to excel

1.  Start excel
2. Paste the picture
3. Select the picture and name it using the name box

Step 4 - Select a transparent color

1. Double click picture. This takes you to the "Format" tab on the ribbon.
   
   
2. Click "Color" button
3. Click "Set Transparent Color"
4. Click a "white" area on the picture.

Repeat step 2 - 4 and copy all your regions to excel.

Step 6 - Organize pictures

Step 7 - Create a group (optional)

Make a group of all pictures and you will be able to resize the entire map in one step.

1. Select all pictures. Tip! Use the "Selection pane" on the "Format" tab.
   
   
2. Right click on a picture
3. Click Group | Group
4. Resize the entire group

Step 8 - Create a table

I downloaded data from wikipedia and created this table:

Table column Column1 contains a single value. The brightness property is ranging from 0 to 1. 0 - darkest and 1 brightest. It is hard to see pictures with a brightness ranging from 0.5 to 1 so I am using values from 0 to 0.5.

Formula in table column %:

Formula in table column %2:

Formula in table column %3:

These formulas index values from 0 to 0.5. I have made a post about indexing data before: Compare your stock portfolio with S&P500 in excel

Step 9 - Create a drop down list

1. Select cell D26
2. Go to tab "Data"
3. Click "Data Validation" button
4. Select "List"
5. Source: United states of America,Population, Area, Population / area
6. Click OK

There is also a hidden formula in cell F26:

1. Select cell F26
2. Press CTRL + 1
3. Select Custom
4. Type ;;;
5. Press OK

Step 10 - Change picture brightness for each picture (vba)

1. Right click on sheet name
2. Click "View Code"
3. Paste code below
4. Exit VB Editor

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Single
Dim c As Integer
Dim Arr() As Variant
If Target.Address = "$D$26" Then
    Arr = Worksheets("Table").Range("Table1").Value
    For r = LBound(Arr, 1) To UBound(Arr, 1)
        With ActiveSheet.Shapes.Range(Arr(r, 1)).PictureFormat
            .Brightness = Arr(r, Range("F26"))
        End With
    Next r
End If
End Sub

Download excel *.xlsm file

US map.xlsm

If you like maps, check out this post: Use a map in an excel chart

Functions in this post:

MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value in a specific order