I need an excel 2010 macro, where I can enter a number (value) into cell B1, click on button below, and the page will automatically go to the corresponding shape, with that same number.

Answer:

1. Enter a number in cell B1.
2. Click "Find shape" button

The macro finds a shape.

VBA Code

Sub FindShape()
Dim sht As Worksheet
Dim shp As Shape
For Each sht In ActiveWorkbook.Worksheets
    For Each shp In sht.Shapes
        If shp.TextFrame.Characters.Text = Worksheets("Sheet1").Range("B1").Value Then
            sht.Activate
            shp.Select
            ActiveWindow.ScrollRow = shp.TopLeftCell.Row
            ActiveWindow.ScrollColumn = shp.TopLeftCell.Column
            Exit Sub
        End If
    Next shp
Next sht
End Sub

Download excel *.xlsm file
Locate a shape.xlsm

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

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.

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

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

Multiplies corresponding components in the given arrays, and returns the sum of those products.

What does that mean? I´ll demonstrate with a few examples.

Example 1 - The basics

Formula in cell B7:

Step 1  - Multiplying corresponding components in the given arrays

The first array is in cell range B2:B4 and the second array is in cell range C2:C4.

B2:B4*C2:C4

becomes

{1;2;3} * {4;5;6}

becomes

{1*4; 2*5; 3*6}

and returns {4; 10; 18}. The same calculations are done in column D and shown in column E, see above picture.

Step 2 - Return the sum of those products

{4; 10; 18}

becomes

4 + 10 + 18

and returns 32 in cell B7. The same calculation is done E5, the sum of the products in cell range E2:E4 is calculated in cell E5. See above picture.

Now you know the basics. Let´s move on to something more interesting!

Example 2 - Use a logical expression

Formula in cell G4:

Step 1  - Multiplying corresponding components in the given arrays

There is only one array in this formula but something else is distorting the picture. A comparison operator (equal sign) and a second cell value (G2) or a comparison value. With these we have now built a logical expression. This means that the value in cell G2 is compared to all the values in cell range B2:B6 (not case sensitive).

B2:B6=$G$2

becomes

{"Alaska";"California";"Arizona";"California";"Colorado"}="California"

and returns

{FALSE;TRUE;FALSE;TRUE;FALSE}.

They are all boolean values and excel can´t sum these values. We have to convert the values to numerical values. There are a few options, you can:

• Add a zero - (B2:B6=$G$2)+0
• Multiply with 1 - (B2:B6=$G$2)*1
• Double negative signs  --(B2:B6=$G$2)

They all convert boolean values to numerical values.

--( {FALSE;TRUE;FALSE;TRUE;FALSE})

becomes

{0;1;0;1;0}

Step 2 - Return the sum of those products

{0;1;0;1;0}

becomes

0 + 1 + 0 + 1 + 0

and returns 2 in cell G4. There are two cells containing the value "California" in cell range B2:B6. You accomplish the same thing using the countif function or count multiple values in different columns using the countifs function. In fact, you can count entire records in a table.

Example 3 - Use multiple logical expressions

Formula in cell D10:

This formula contains three arrays, you can use up to 30 arrays. You can make the formula somewhat shorter:

This allows you to have a lot more expressions, if you like. It is only the available computer memory that is the limit. The formula looks like an array formula but don´t be fooled, you are not required to enter it as an array formula.

Step 1  - Multiplying corresponding components in the given arrays

(B2:B8=B10)*(C2:C8=C10)*D2:D8

becomes

({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})*({FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE})*{10; 20; 40; 10; 20; 30; 10}

becomes

{0;0;0;1;0;1;0}*{10; 20; 40; 10; 20; 30; 10}

and returns

{0; 0; 0; 10; 0; 30; 0}

Step 2 - Return the sum of those products

{0; 0; 0; 10; 0; 30; 0}

becomes

10 +30

and returns 40 in cell D10.

Example 4 - Use multiple logical expressions and mathematical operators

Mathematical operators between arrays allows you to do more complicated calculations, like this:

Formula in cell D10:

These expressions check if California is found in cell range B2:B8 or Las Vegas is found in cell range C2:C8. They are found in row 3, 5,7 and 8. The sumproduct function sums the corresponding values in column D and returns 70 in cell D10. 20+10+30+10 equals 70.

Download excel *.xlsx file

Sumproduct function.xlsx 

Hi Oscar,
I have the following issues. Any suggestions.

Sheet 1 :
Grade Male Female
Primary
Secondary
High School
College
Gender

Sheet 2 :
Col A : Student Id
Col B: Gender : Here we specify whether the student is male or female
Col C : Here we specify category to which the student falls : Primary, secondary, high school, college
Col D : Age : To which age they are in

Problem :

In Sheet 1,
# I should give the count of students who are male and are in
* primary and they are between 4 to 6 years.
* Count of Secondary students and between 6 to 10 years ...

# I should give the count of students who are female and in
* primary and they are between 4 to 6 years.
* Count of Secondary students and between 6 to 10 years

Kindly let me know of any specific formula e that I can use.

Answer

Sheet 2 - Example data

Sheet 1 - Count students

Formula in cell B2:

You can copy this formula to cell range B2:C5. The formula takes advantage of both absolute and relative cell references . For this to work, copy the cell, not the formula in the cell or formula bar.

Example,

1. Select cell B2
2. Copy cell B2 (Ctrl + c)
3. Select cell range B2:C5
4. Paste (Ctrl + v)

Read more about absolute and relative cell references

Formula in cell B6:

If you are familiar with array formulas, use this array formula in cell B6:

The formula is smaller but needs to be entered as an array formula.

How to enter an array formula

1. Select cell B6
2. Paste formula to formula bar
   
3. Press and hold CTRL + SHIFT
4. Press Enter simultaneously
5. Release all keys

If you did it right, the formula now begins and ends with curly brackets, like this:

Explaining formula in cell B2

Step 1 - Compare value in cell A2 (sheet1) to values in cell range C2:C169 (sheet2)

$A2=Sheet2!$C$2:$C$169

becomes

"Primary"={"College"; "College"; "High school"; ... ; "Secondary"}

and returns

{FALSE; FALSE; FALSE; ... ; FALSE}

Step 2 - Compare value in cell B1 (sheet1) to values in cell range B2:B169 (sheet2)

B$1=Sheet2!$B$2:$B$169

becomes

"Male"={"Male"; "Female"; "Male"; ... ; "Female"}

and returns

{"TRUE"; "FALSE"; "TRUE"; ... ; "FALSE"}

Step 3 - Multiplying arrays

($A2=Sheet2!$C$2:$C$169)*(B$1=Sheet2!$B$2:$B$169)

becomes

("Primary"={"College"; "College"; "High school"; ... ; "Secondary"})*("Male"={"Male"; "Female"; "Male"; ... ; "Female"})

becomes

({FALSE; FALSE; FALSE; ... ; FALSE})*({"TRUE"; "FALSE"; "TRUE"; ... ; "FALSE"})

and returns

({0; 0; 0; ... ; 0})

Step 4 - Sum all values in the array

=SUMPRODUCT(($A2=Sheet2!$C$2:$C$169)*(B$1=Sheet2!$B$2:$B$169))

becomes

=SUMPRODUCT({0; 0; 0; ... ; 0})

and returns 5 in cell B2.

Explaining formula in cell B6

Step 1 - Compare value in cell B1 (sheet1) to values in cell range B2:B169 (sheet2)

B$1=Sheet2!$B$2:$B$169

becomes

"Male"={"Male"; "Female"; "Male"; .. ; "Female"}

and returns

{"TRUE"; "FALSE"; "TRUE"; .. ; "FALSE"}

Step 2 - Convert boolean values to numerical values

SUMPRODUCT function (and the sum function) can´t sum boolean values (TRUE, FALSE). Multiplying with 1 converts TRUE to 1 and FALSE to 0.

(B$1=Sheet2!$B$2:$B$169)*1

becomes

{"TRUE"; "FALSE"; "TRUE"; .. ; "FALSE"}*1

and returns

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

Step 3 - Sum values

SUMPRODUCT((B$1=Sheet2!$B$2:$B$169)*1)

becomes

SUMPRODUCT({1; 0; 1; .. ; 0})

and returns 71 in cell B6.

More to note

You can also use the SUMIF and SUMIFS function to count values. See this post:
Sum values between two dates with criteria

Download excel *.xlsx file

Count students.xlsx

Functions in this post

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

SUM(number1,[number2],)
Adds all the numbers in a range of cells

Step 1 - Insert a scatter chart

1. Go to "Insert" tab on the ribbon
2. Click "Scatter" button
3. Click "Scatter with only markers" button

Step 2 - Create a drop down list

1. Select cell B14
2. Go to tab "Data" on the ribbon
3. Click "Data Validation" button
4. Go to "Settings" tab
5. Select "List"
6. Select source range: B3:B10
7. Click OK

Formula in cell C14:

Formula in cell D14:

Step 3 - Select chart data source

1. Right click on chart
2. Click "Select Data"
3. Click "Add" button
   
4. Select name, x value and y value.
   
5. Click Ok.

Step 4 - Insert a background picture

1. Right click on chart
2. Click "Format Plot Area..."
3. Click "Fill"
4. Select "Picture or texture fill"
5. Click "File..." button
6. Select a picture
7. Click "Insert"
8. If you like, change "Transparency" value
9. Click "Close"

Step 5 - Chart settings

1. Delete Legend and chart titel
   
   
2. Delete chart grid lines
   
3. Change x and y axis minimum and maximum value to 0 and 100
   
4. Make sure x and y coordinates in the table are ok
   
5. If you like, delete x and y axis
   
6. Select data series on the chart
   
7. Right click on data series
8. Click "Format Data Series..."
   
9. Click "Marker Options"
10. Select "Built-in"
11. Select a type
12. Click "Marker Fill"
13. Select "Solid fill"
14. Pick a color
15. Click Close

Final notes

First I thought of using longitude and latitude coordinates but I gave that up really quickly. The map is downloaded from Wikimedia Commons.

Tip! You can add a data label and use the series name to show the city name on the map.

Download excel *.xlsx file

Maps in a xy scatter chart.xlsx

Functions in this post

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

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

SEARCH(find_text,within_text, [start_num])

Arguments

find_text - Is the text you want to find. ? and * wildcard characters are allowed.

within_text - is the text in which you want to search for find_text

start_num - is the character number in within_text, counting from the left at which you want to start searching. Optional, if omitted 1 is used.

Formula in cell C5:

The function returns 11 in cell C5. Text string "blue" is found and begins in the 11-th character in "My car is blue". See cell range F5:S6.

I am using a absolute cell reference to cell D5 so I can copy the function to cell C11 without changing the argument. If you are interested in how absolute and relative cell references work, read this post:  Absolute and relative cell references

Formula in cell C11:

becomes

=SEARCH("blue","My car is white")

and returns #VALUE!. Text string "blue" is not found in "My car is white".

FIND function
The function works the same as the search function except case-sensitive.

SEARCH function returns an array

Array formula in cell B5:B9: 

becomes

=SEARCH("is", {"My car is blue"; "My car is white"; "My house is grey"; "My bird was green"; "My dog is here"})

and returns {8; 8; 10; #VALUE!; 8} in cell range B5:B9. I have highlighted the beginning character in cell range D5:T9.

Download excel *.xlsx file

Search and Find functions.xlsx