# Have Excel Chart only show current month and YTD

I’m currently working on creating a dynamic chart which is updated monthly in accordance with the information imported into the excel file.

The table is updated with the information, where the chart is supposed to only show the current month based on a specific cell, shown in the attached excel file.

Example text “Period 01JAN18 0001 to 31JAN18 2259”

What the chart needs to do is match the text with a month, so if the text says “JAN” it’s supposed to grab the data for January, if it says “FEB” it’s supposed to grab the data for February.

Currently the chart shows the data for every single month, where I then have to filter out the current month and Year To Date.

I hope this explains it properly :)

• First, please remove the "Edit" regarding "offtopic". That concerned your post on Stack Overflow only. Secondly, you are telling what you are currently working on. That's interesting, but Superuser is a question/answer site. What is your question? What do you expect somebody here to tell you? Why do you want to tell us what you are working on? Perhaps you are hoping that somebody does your work for you. No, I guess you actually want to ask about something, so please formulate your post as a question and be specific about the detail you want to ask about. – Tom Brunberg May 9 '18 at 17:17
• The chnart is supposed to show_ the current month, that would be my question in this case. I could do a TL;DR I need the "Intended Chart" to show the current month based on the period and the year to date data, not showing every single month. – MSL May 9 '18 at 17:24
• By all means, good luck! – Tom Brunberg May 9 '18 at 17:33

The answer below relies on the text in `B25` always having 9 characters before the 3-letter name of the current month.

To make the graph dynamic, you can add a new column that always has the current month's data and use that as the y-values for your graph.

This expression: `MID(B\$25,10,3)`

uses `MID()` to get the 3-letter name of the current month from `B25`. It starts at position 10 and takes the next 3 letters. So see the caveat above.

Then this expression appends the wildcard character to the month name, and searches for the 3-letter month name (plus anything else) in `Row 1`: `MATCH(MID(B\$25,10,3)&"*",B\$1:M\$1,0)` It returns the column number of the month that matches.

Finally, that expression for the column number is used in an `INDEX()` formula, along with the row number, to get the first data point for the current month. This formula:

`=INDEX(B\$2:M\$21,ROW(O1),MATCH(MID(B\$25,10,3)&"*",B\$1:M\$1,0))`

filled down from `O1`, populates `Column O` with the data from whatever month is specified in `B25`. That's September in the screenshot below.

O23 has this formula: `=INDEX(B\$1:M\$1,,MATCH(MID(B\$25,10,3)&"*",B\$1:M\$1,0))` to allow the chart legend to show the name of the current month.

To change the graph, simply select it, and drag the selection box for the y-data over to `Column O`. Also, use the Source Data dialog to make the Name of the series point to `O23`.

• Hi, thank you! it worked wonderfully! Had to do some very small modifications but apart from that it was exactly what I was looking for. – MSL May 10 '18 at 18:02