Excel – Displaying data in Excel graph only up to current date

Two options I see in Excel, one bland and one more interesting. Bland first:

1. Create a Surface Contour Chart in Excel.

To do this, just select your three columns of data, then insert a Contour chart (listed under Other Charts->Surface in Excel 2007). You'll have to add your series manually, but it can be done with a little effort. Please note that Excel does interpolation between your data points to create the contour map. Most of the time this is a nice feature, but beware that how you define your ranges can have a major effect on the contours Excel interpolates.

Quick Sample:

2. Use Conditional Formatting to Create a Heatmap Within Your Spreadsheet

This requires some data transformation, but the output looks pretty nice, especially for large fine-grained representations. First, you'll need to add a column to the left of your table. This will hold a lookup value that will be used to populate your heat map. In this column you need a key that uniquely identifies each x-y pair in your data. In my example below, I used the formula `=B2&"-"&C2'. Fill this formula down.

Next, you'll need to set up a table on your sheet that mimics an x-y graph. So, x on the horizontal (at the top or bottom, your preference), and y on the vertical with descending values. Once you've done this, you can use a VLOOKUP function to populate the heat map. You'll need something like this: =VLOOKUP(G$11&"-"&$F2,$A$2:$D$46,4,FALSE). Note that in the first argument, the row number for the x labels and the column for the y labels is fixed. This allows you to fill this formula throughout the table for your heat map.

Finally, select the values in the table you just created and apply conditional formatting to them. You can either use one of the pre-defined color scales or create your own to match your needs.

Example:

FWIW, I laid out a data table as you described, and in Excel 2007 got the default chart to appear like you described wanting. Here's my example:

If that's not working, you can try this (to avoid Excel being "to smart" and applying all it's own formatting decisions).

1. Create a blank chart-click in a blank cell and choose insert line chart.
2. Right-click on chart and choose Select Data.
3. Add a new series.
4. For the label, choose your first series; for the series choose your cells B:N
5. In the Horizontal Axis Labels area of the dialog (right side), choose your Week Labels.
6. Select OK for everything and your first series should be set.
7. Select the rest of your series, including your series labels.
8. Re-select your chart and paste the values. If you Special Paste here you can select your values in rows and your first column contains series names.
9. Click OK to finish pasting your remaining series to the chart.

Also, you can try the Switch Row/Column button in the Select Data Dialog box.