how to plot a graph in excel based on date

Then I add the Sheet name in front of all the variables. since the named ranges will come up automatically (as you can see in the picture): =SERIES(Sheet1!$C$6,Xaxis,YTotalFundingBefore,1) When I rewrite the formulas I start like this. Here I want to give a trick to save some time. In the formula bar (marked yellow in the picture) I need to change my references.

Then I select the first chart-serie: 'Total Funding Before'.

I select the whole table and create a Line Graph. Time to apply these ranges for the graph. So the last series I named to ' YTotalFundingAfter' and the formula is almost same, except 3. Notice for every new serie we create a Name range, we change the column offset. We do the same for the following two Series. So we use the same range as the XAxis and offset it by one column to the right. I name it: YTotalFundingBefore and apply the following formula. We start with Column 'Total Funding Before'. I have ' ' as delimiter, while US have ' ,'. Those two together will create the range we need. Then it will see where the end date is located ( End_Date). This formula will search where the first row is for the first date ( Start_Date). Time to create the dynamic range for the X-Axis. Go to: 'Data' -> 'Data Validation' -> Choose: 'List' -> 'Source:' =Dates, which is our range we named in the previous step. Time to create drop-down list for start and end date. The last step is very important!, since we want to make all references dynamic. Notice that the name manager don't use 'B7:B16', rather is a reference to the table column area. Check that the 'Refers to:' is in this format '= TableX'. Then we create a name range for the 'Date' column.

We do exactly the same for the 'End_Date. Go to: 'Formulas' -> 'Name Manager' -> 'New.' Next part, we need to create a couple of 'Named Ranges'. With some dynamic ranges and a lot of patience you could create something like this result:įirst we make the whole table into 'Excel Table'.

How to plot a graph in excel based on date