How to make Excel chart intelligently select data

I got a set of memory usage that I am trying to plot on Excel. I got time, total RAM, used RAM, used SWAP and free SWAP each with its own column.

But if I select all of columns for a line chart the chart displays all the way till the end of the columns. Which squishes everything at the beginning, ie the useful data.

So my question is: How do I specify that the chart should stop displaying once it hits cells that is not used/blank? Because over the next several days I intend to log more RAM usage data and there will be even more data points to plot.

Here is the data set showing the transition.

And here is the chart that is being displayed

3 Answers

Select the range with your data, and on the ribbon, choose Insert > Table. This converts the range into a special type of range called a Table. If you add data right below a table, the table will expand to include the added data.

Create your chart using the table, but not extending below the table. The chart will not leave any space for lots of unused cells. When data is added to the table and the table expands, the chart will update to include the added data.

I'm at work and can't see your pictures, so I hope I'm interpreting your question correctly. This macro looks at column B to figure out how long your columns are, then sets the chart series to that length. On the first two lines of the code, you'll need to update "sheet1" and "Chart 1" to your actual worksheet and chart names. I'm assuming that your data starts at A1, including a header row.

Sub UpdateChart()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("sheet2")
Dim c As ChartObject: Set c = ws.ChartObjects("Chart 1")
Dim lastRow As Long: lastRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
Dim x As Long
For x = 1 To c.Chart.SeriesCollection.Count c.Chart.SeriesCollection(x).Formula = "=SERIES(" & ws.Name & "!R1C" & x & ",," & ws.Name & "!R2C" & x & ":R" & lastRow & "C" & x & "," & x & ")"
Next x
End Sub

A few things:

• Excel does not have any issues with blank cells, it treats them as if they don't exist
• Your actual problem is that you are not plotting the right things, as evidenced by the super bizarre 0:00, 0:00, 0:00, vertical scale and strange horizontal scale.
• Another problem this chart has is that the data is all squished up because the axes are too large.

I had an excruciating time trying to tell Excel how to handle the data. Here's how to give up and let it handle your data intelligently:

• 1. Click on a populated cell inside your chart of numbers
• 2. Go to insert -> linechart .
• 3. A linechart should pop up with all of the series populated correctly.

Using office 2013: