In a previous post I created unqiue distinct column headers (A20:C20) and then categorized adjacent cell values into each column (vba).

In this post I´ll show you how to do the same using only excel formulas.

Create unique distinct column headers

Array formula in B20:

=INDEX($B$4:$B$13, MATCH(0, COUNTIF($A$20:A20, $B$4:$B$13), 0)) + CTRL + SHIFT + ENTER.

Copy (CTRL + C) cell B20 and paste (Ctrl + V) into cells C20 and D20. See picture below.

Categorize cell values into each column

Array formula in B21:

=INDEX($C$4:$C$13, SMALL(IF($B$4:$B$13=B$20, ROW($B$4:$B$13)-MIN(ROW($B$4:$B$13))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER

Copy (CTRL + C) cell B21 and paste (Ctrl + V) into cells B21:C23 and D21:D24. See picture below.

Download excel example file
Categorize data into multiple columns (formulas).xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

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

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

ROW(reference) returns the rownumber of a reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

SMALL(array,k) returns the k-th smallest row number in this data set.

I am fairly new to vba and I am amazed of how much you can automate in excel.

In this post I am going to categorize values from a list into unique columns.I am sure there are more efficent ways to accomplish this task using better written code. In  a year from now  I hope I´ll be laughing at this post.

Before:

After:

The code

Sub Categorizedatatocolumns()

Dim rng As Range

Dim dest As Range

Dim vrb As Boolean

Dim i As Integer

Set rng = Sheets("Sheet1").Range("A4")

vrb = False

Do While rng <> ""

Set dest = Sheets("Sheet1").Range("A20")

Do While dest <> ""

If rng.Value = dest.Value Then

vrb = True

End If

Set dest = dest.Offset(0, 1)

Loop

If vrb = False Then

dest.Value = rng.Value

dest.Font.bold = True

End If

vrb = False

Set rng = rng.Offset(1, 0)

Loop

Set rng = Sheets("Sheet1").Range("A4")

Do While rng <> ""

Set dest = Sheets("Sheet1").Range("A20")

Do While dest <> ""

If rng.Value = dest.Value Then

i = 0

Do While dest <> ""

Set dest = dest.Offset(1, 0)

i = i + 1

Loop

Set rng = rng.Offset(0, 1)

dest.Value = rng.Value

Set rng = rng.Offset(0, -1)

Set dest = dest.Offset(-i, 0)

End If

Set dest = dest.Offset(0, 1)

Loop

Set rng = rng.Offset(1, 0)

Loop

End Sub

Download excel tutorial file

Remember to backup your excel workbook, you can´t undo macros.
Categorize-data-into-multiple-columns.xls
(Excel 97-2003  Workbook *.xls)
You need to enable macros.

Count unique distinct numbers in a 3D range

Array formula in E8:

=SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0)) + CTRL + SHIFT + ENTER

Count unique numbers in a 3D range

Array formula in E10:

=SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)=1)) + CTRL + SHIFT + ENTER

Count duplicate numbers in a 3D range

Array formula in E12:

=SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)>1)) + CTRL + SHIFT + ENTER

Download excel tutorial file

Count unique and duplicate numerical data entries from multiple sheets.xls
(Excel 97-2003  Workbook *.xls)

Functions in this article:

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

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 Bins_array.

By coincidence I seem to have created a "unique" formula in this post: Combine data from multiple sheets in excel (See comments). I have no clue if that is true or not but my intention now is to develop that formula a bit further.

Values across multiple sheets are sometimes refered to as a 3D range.

Calculate min and max values in 3D range

Formula in C6:

=MIN(Sheet1:Sheet3!$B$2:$D$4) + Enter

Formula in E6:

=Max(Sheet1:Sheet3!$B$2:$D$4) + Enter

Extract unique distinct values from a 3D range

Unique distinct values are all values in a range or column but duplicates are merged into one distinct value. See example picture below.

I am using the calculated min and max values (bolded) in the array formula in B9:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW($1:$7))<>0, ROW($1:$7), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Extract unique values from a 3D range

Unique values are values existing only once in a list or range. See example picture below.

I am using the calculated min and max values (bolded) also in the array formula in E9:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW($1:$7))=1, ROW($1:$7), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Extract duplicate values from a 3D range

Once again I am using the calculated min and max values (bolded) in the array formula in G9:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW($1:$7))>1, ROW($1:$7), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Putting it all together

In cell B18, E18 and G18 I have "merged" the min max formulas with the unique/duplicate formulas.

Array formula in B18:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))))<>0, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in E18:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))))=1, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in G18:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))))>1, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Keep in mind, indirect is a volatile function.

Download excel tutorial file

Return unique and duplicate numerical data entries from multiple sheets.xls
(Excel 97-2003  Workbook *.xls)

Functions in this article:

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

ROW(reference) Returns the rownumber of a reference

SMALL(array,k) Returns the k-th smallest row number in this data set.

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

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 Bins_array.

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

INDIRECT(ref_text;[a1])
Returns the reference specified by a text string

In this post I am going to show how to create a new sheet for each airplane using vba. The macro copies airplane and model values into each new sheet.

Before:

After:

The Code

Option Explicit

Sub Splitdatatosheets()
'
' Splitdatatosheets Macro
'

'
Dim rng As Range

Dim rng1 As Range

Dim vrb As Boolean

Dim sht As Worksheet

Set rng = Sheets("Sheet1").Range("A4")

Set rng1 = Sheets("Sheet1").Range("A4:D4")

vrb = False

Do While rng <> ""

For Each sht In Worksheets

If sht.Name = rng.Value Then

sht.Select

Range("A2").Select

Do While Selection <> ""

ActiveCell.Offset(1, 0).Activate

Loop

rng1.Copy ActiveCell

ActiveCell.Offset(1, 0).Activate

Set rng1 = rng1.Offset(1, 0)

Set rng = rng.Offset(1, 0)

vrb = True

End If

Next sht

If vrb = False Then

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = rng.Value

Sheets("Sheet1").Range("A3:B3").Copy ActiveSheet.Range("A1")

Range("A2").Select

Do While Selection <> ""

ActiveCell.Offset(1, 0).Activate

Loop

rng1.Copy ActiveCell

Set rng1 = rng1.Offset(1, 0)

Set rng = rng.Offset(1, 0)

End If

vrb = False

Loop

End Sub

Download excel tutorial file

Remember to enable macros and backup your excel file because you can´t undo macros.

Split data across multiple sheets.xls
(Excel 97-2003  Workbook *.xls)

Question:

Problem description (simplified of course):

I have a list of employees (by ID number) and date (by yr & mon) of when they were assigned a certain duty (task). This is in a Work book, on a TAB. Each TAB is a separate month (first is Jan, 2nd is Feb, etc.). I have 12 tabs (12 worksheets) in workbook. Each TAB, a single month, has a list of ID numbers. Some IDs may repeat on different worksheets, that is, some may be in multiple months and some may be in just two or three months or just one month. An ID number will shown only once in a month for a single task (duty). Abbreviated example is below.

Is it possible to combine the data, by function, or formula, or VBasic) to a 13th worksheet automatically and:

1. Show a list of all ID numbers in order (without repeating).
2. Show Jan data in col B, Feb data in col C, etc., and some columns will be blank because the ID had no assignment that month, and will not be on the worksheet for that month.

Is there a formula, or function, or does it have to be done in VBasic? (Is it even possible?)

I have the workbook with 12 tabs in it, and now have to manually put the ID columns side by side and copy and slide down one side on the other to get them to match, and repeat the process 12 times to get the yearly data on one worksheet.

Ex:
For Jan:
ID Duty Asgn.
01 C
05 F
09 D
15 X
23 P

For Feb:
ID Duty Asgn.
02 M
05 Q
08 A
12 R
20 W

Combing Jan and Feb would be:
ID Duty Asgn.
01 C
02 M
05 F Q
08 A
09 D
12 R
15 X
20 W
23 P

This would be repeated for each month to build all 12 col months.

Very Respectfully,
Dave Bonar

Answer:

First I thought your question required vba but here is the answer using only Excel formulas.

Maximum ID number from multiple sheets

Formula in cell B1:

=MAX(Jan:Mar!A2:A1000) + Enter

Create a sorted unique distinct list of numbers originating from multiple sheets

We are going to use the number calculated in B1 (28) in array formula in cell A4 (bolded):

=SMALL(IF(FREQUENCY(Jan:Mar!$A$2:$A$1000, ROW($1:$28))<>0, ROW($1:$28), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Combine data from multiple sheets

Formula in B4:

=IF(ISERROR(MATCH(A4, Jan!$A$2:$A$10, 0)), "", INDEX(Jan!$B$2:$B$10, MATCH(A4, Jan!$A$2:$A$10, 0))) + CTRL + SHIFT + ENTER copied down as far as needed.

Formula in C4:

=IF(ISERROR(MATCH(A4, Feb!$A$2:$A$10, 0)), "", INDEX(Feb!$B$2:$B$10, MATCH(A4, Feb!$A$2:$A$10, 0))) + CTRL + SHIFT + ENTER copied down as far as needed.

Formula in D4:

=IF(ISERROR(MATCH(A4, Mar!$A$2:$A$10, 0)), "", INDEX(Mar!$B$2:$B$10, MATCH(A4, Mar!$A$2:$A$10, 0))) + CTRL + SHIFT + ENTER copied down as far as needed.

Download excel tutorial file

Get data from each sheet.xls
(Excel 97-2003  Workbook *.xls)

Functions in this article:

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

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

ROW(reference) Returns the rownumber of a reference

SMALL(array,k) Returns the k-th smallest row number in this data set.

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

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 Bins_array.

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

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both vba and formula.

I will explain how I created this calendar in an upcoming post. You can download the excel calendar file here: Excel calendar.xlsm You need to enable macros to use this calendar.

Instructions:

Select a week

1. Select a week using spinner buttons or type a date in date cells

How to add a record

1. Double click a cell
2. Type text in title window and text window
3. Click Save button on userform

How to delete a record

1. Double click a cell
2. Click Delete button on userform

Overview Calendar

The overview calendar makes spinner button navigation easier. The selected week is colored gray and the date today is yellow.

Download excel tutorial file

Excel calendar.xlsm
(Excel 2007  Workbook *.xlsm)

Table of contents

• Introduction
• Create a lookup table
• Create a drop down list
• Change web query parameter
• Add dynamic ranges to stock chart

Introduction

I have previously posted how to create  a dynamic stock chart in excel. It is really easy to use:

• Select Company
• Click "Update chart"

In this post I have added a new feature. You can now select a date range in a drop down list and the stock chart uses the new date range.

• Select Company
• Select date range
• Click "Update chart"

I am working with the attached file in this post:
Create a dynamic stock chart using a web query and a drop down list in excel
or download the complete tutorial file for this post here:
dynamic_stock_chart_change_date_range.xls

Create a lookup table

Select sheet "Data".

Formula in B1:=TODAY() + ENTER
Formula in B2:=YEAR(B1)+ENTER
Formula in B3:=MONTH(B1) + ENTER
Formula in B4:=DAY(B1)
Formula in B5:=INDEX(Overview!C3:C33, MATCH(Overview!E3, Overview!B3:B33, 0)) + ENTER

Formula in E2:=INDEX(G3:K3, MATCH(Overview!$E$4, Data!$G$1:$K$1, 0)) + ENTER
Formula in E3:=INDEX(G4:K4, MATCH(Overview!$E$4, Data!$G$1:$K$1, 0)) + ENTER
Formula in E4:=INDEX(G5:K5, MATCH(Overview!$E$4, Data!$G$1:$K$1, 0)) + ENTER

Formula in G2: =DATE(YEAR($B$1), MONTH($B$1)-3, DAY($B$1)) + ENTER
Formula in G3:=YEAR(G2) + ENTER
Formula in G4:=MONTH(G2)-1 + ENTER
Formula in G5:=DAY(G2) + ENTER

Formula in H2:=DATE(YEAR($B$1), MONTH($B$1)-6, DAY($B$1)) + ENTER
Formula in H3:=YEAR(H2) + ENTER
Formula in H4:=MONTH(H2)-1 + ENTER
Formula in H5:=DAY(H2) + ENTER

Formula in I2:=DATE(YEAR($B$1), MONTH($B$1)-12, DAY($B$1)) + ENTER
Formula in I3:=YEAR(I2) + ENTER
Formula in I4:=MONTH(I2)-1 + ENTER
Formula in I5:=DAY(I2) + ENTER

Formula in J2:=DATE(YEAR($B$1), MONTH($B$1)-24, DAY($B$1)) + ENTER
Formula in J3:=YEAR(J2) + ENTER
Formula in J4:=MONTH(J2)-1 + ENTER
Formula in J5:=DAY(J2) + ENTER

Formula in K2:=DATE(YEAR($B$1), MONTH($B$1)-60, DAY($B$1)) + ENTER
Formula in K3:=YEAR(J2) + ENTER
Formula in K4:=MONTH(J2)-1 + ENTER
Formula in K5:=DAY(J2) + ENTER

Create a drop down list

1. Select sheet "Data"
2. Type "3 months" in cell M1
3. Type "6 months" in cell M2
4. Type "1 year" in cell M3
5. Type "2 years" in cell M4
6. Type "5 years" in cell M5
7. Select M1:M5 and create a named range. I named it "daterange".
8. Select sheet "Overview"
9. Select cell E4
10. Select tab "Data"
11. Click "Data validation"
12. Click "Data validation..."
13. Select List
14. Type "=daterange" in source.
15. Click ok!

Change web query parameter

I have changed web query parameter "ticker" to cell reference:B5. Remaining parameters.

Add dynamic ranges to stock chart

Dynamic ranges

1. Click tab "Formulas"
2. Click "Name Manager"
3. Click "New.."
4. Name it "High"
5. Copy and paste this formula into "Refers to:": =OFFSET(Data!$C$11, 0, 0, COUNTA(Data!$C$10:$C$10000)-1, 1)
6. Click OK!
7. Click "New.."
8. Name it "Low"
9. Copy and paste this formula into "Refers to:": =OFFSET(Data!$D$11, 0, 0, COUNTA(Data!$D$10:$D$10000)-1, 1)
10. Click OK!
11. Click "New.."
12. Name it "Close"
13. Copy and paste this formula into "Refers to:":=OFFSET(Data!$E$11, 0, 0, COUNTA(Data!$E$10:$E$10000)-1, 1)
14. Click OK!

Update stock chart with dynamic ranges

1. Right click on stock chart
2. Click "Select Data"
3. Select "High"
4. Click "Edit"
5. Copy and paste this into "Series values:" =dynamic_stock_chart_change_date_range.xls!High
6. Click OK
7. Select "Low"
8. Click "Edit"
9. Copy and paste this into "Series values:" =dynamic_stock_chart_change_date_range.xls!Low
10. Select "Close"
11. Click "Edit"
12. Copy and paste this into "Series values:" =dynamic_stock_chart_change_date_range.xls!Close
13. Click OK!
14. Click OK!

Download excel tutorial file

dynamic_stock_chart_change_date_range.xls
(Excel 97-2003 Workbook *.xls)

Question: How to create unique distinct year and months from a long date listing (column A)?

You can find the question in this post: Extract dates using a drop down list in excel

Answer:

Array formula in C2:

=TEXT(INDEX($A$2:$A$135, MATCH(0, COUNTIF($C$1:C1, TEXT($A$2:$A$135, "mmm-yyyy")), 0)), "mmm-yyyy") + CTRL + SHIFT + ENTER copied down as far as needed.

Download excel tutorial file

unique distinct year and month.xls
(Excel2007  Workbook *.xlsx)

Functions in this article:

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

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

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

TEXT(value, format_text)
Converts a value to text in a specific number format

In this blog post I´ll show you how to update a stock chart in excel using a drop down menu and a web query connection.

• Create list of companies and corresponding tickers
• Create a dynamic range
• Create a drop down list
• Calculate dates
• Create a web query
• Create a macro to refresh web query and automate "text to columns"
• Create a stock chart
• Create a button

Here is a picture of the final result:

At the end of this post is an attached excel tutorial file.

Create list of companies and corresponding tickers

In this tutorial I have two sheets, Overview and Data. On sheet Overview I created a small list of companies and  corresponding ticker. You can search tickers at http://finance.yahoo.com/

Create a dynamic range

As I add new companies and tickers to the list I want the drop down list automatically updated. To accomplish this I need to create a dynamic named range.

1. Click tab "Formulas"
2. Click "Named ranges"
3. Click "New"
4. Name your range (formula in this tutorial).
   I named it "Dynrange".
5. Type in "Refers to:" =OFFSET(Overview!$B$3, 0, 0, COUNTA(Overview!$B:$B)-1, 1)
6. Click OK!

Create a drop down list

1. Select cell E3
2. Click "Data" tab
3. Click "Data validation"
4. Click "Data validation..."

   

5. Select Allow: List
6. Type in "Source:" =Dynrange
7. Click OK!

Calculate dates

To download the latest three months of historical data we need to calculate the the date three months back.

I did this calculation on sheet "Data". The formulas use the current date today automatically.

Cell H1 matches the corresponding ticker to the selected company in the drop down list.

See formula details on the attached file at the bottom of this post.

Create a web query

1. Click "Data" tab
2. Click "From web"
3. Paste this into address field:http://table.finance.yahoo.com/table.csv?a=["fm","fm"]&b=["fd","fd"]&c=["fy","fy"]&d=["tm","tm"]&e=["td","td"]&f=["ty","ty"]&s=["ticker", "ticker"]&y=0&g=d&ignore=.csv
4. Click "Go"
5. Click "Import"
6. Select cell "A10" on sheet "Data"
7. Click OK

   
   

8. Select cell E3 (fm) . Enable "Use this value/reference for future refreshes". Click OK.
9. Select cell E4 (fd) . Enable "Use this value/reference for future refreshes". Click OK.
10. Select cell E2 (fy) . Enable "Use this value/reference for future refreshes". Click OK.
11. Select cell B3 (tm) . Enable "Use this value/reference for future refreshes". Click OK.
12. Select cell B4 (td) . Enable "Use this value/reference for future refreshes". Click OK.
13. Select cell B2 (ty) . Enable "Use this value/reference for future refreshes". Click OK.
14. Select cell H1 (ticker) . Enable "Use this value/reference for future refreshes". Click OK.

VBA code to refresh web query and automate "text to columns".

1. Click "Developer" tab
   How to show the Developer tab or run in developer mode
2. Click "Visual Basic"
3. Create a "Module" for your workbook
   How to Copy Excel VBA Code to a Regular Module
4. Copy this vba code into module:

Sub Refreshwebq()

Set qryTableStocks = ThisWorkbook.Worksheets("Data").QueryTables(1)

With qryTableStocks
.BackgroundQuery = False
End With

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("Data").Select
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

ActiveWorkbook.Connections("Connection").Refresh

Sheets("Data").Select
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A10"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True

Sheets("Overview").Select

End Sub

Create a stock chart

1. Select High, Low and Close columns on sheet "Data"
2. Click "Insert" tab
3. Click "Other charts"
4. Click High-Low-Close stock chart
5. Cut and paste stock chart to sheet "Overview"
6. Right click on x-axis values
7. Click "Select data"
8. Click "Edit" in Horizontal (Category) axis labels
9. Select Dates on sheet "Data", =Data!$A$11:$A$71
10. Click OK!
11. Click OK!
12. Right click on x-axis values
13. Click "Format axis"
14. Click Axis Options
15. Click Axis Type: Text axis
16. Click Categories in reverse order
17. Click Vertical axis crosses: At maximum category
18. Click Close

Create a button and assign macro Refreshwebq()

How to Run an Excel Macro With a Worksheet Button

Download excel tutorial file

dynamic stock chart using web query.xls
(Excel 97-2003 Workbook *.xls)