0

I have an excel sheet where I keep track of my daily self-improvement activites, sorted into different categories.

One row with the day's date, the rest of the rows activites with how many minutes I spent on them (and others for better visual representation, f.e. Total Day's Time & Baseline). Below the rows is a 2D line chart representing these values, X axis being the dates. The effect is, as I scroll along the time entries, I see a nice chart showing my daily dedication, which is very motivating. (Can only recommend it!)

For selecting the chart's data so far, I just clicked the row and it selected everything of that row for me, or so I thought. In reality it only selected a certain range (like, up to 20 cells behind the end of my existing data), which is insufficent as I want to expand the records each day of this year.

Now I want to manually set it (with f.e. =Sheet!$9:$9). It does select the entire row, however that includes all the empty entries after my data ends. In effect, my chart gets cramped into this corner because there's way more detected entries than set dates. It's curious to me because as mentioned earlier, when I selected the rows by clicking them it did select the cells after the end of my existing data, but those empty entries didn't show up in the table (if their dates weren't set).

How do I select the entire row while leaving out empty fields after the end of my data set?

How it should look / looks when using the "normal" data click selecting

How it looks when using manual formula for the entire row

Improve this question

1 Answer 1

Reset to default
0

Please consider converting your data to a Table.

Put your cursor anywhere in the source data and use Ctrl+T or Home>Format As Table

enter image description here

It should look something like this:

enter image description here

Now you can set your chart's source data to be the table itself. Put your cursor in the table and use Insert>Chart>Line Chart

This will create a chart based on all the data rows in the table.

enter image description here

But most importantly, when you add a new row to the table at the bottom, the chart will update automatically:

enter image description here

Improve this answer
1
  • Thank you so much, you really put a lot of effort into your answer, truly shows how Excel should be used! I'll flip my data to go downwards and use a bar chart to accompany it. Again, thank you a lot!
    – AverageJoe1245
    Commented Sep 5, 2022 at 18:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .