 <p>I found a solution! Thanks largely to <a href="https://turbofuture.com/computers/Creating-Dynamic-Charts-using-the-OFFSET-function-and-Named-Ranges-in-Excel-2007-and-Excel-2010" rel="nofollow noreferrer">this page</a>. Here's what I did:</p> <p>I created made a named range using this formula: <code>=OFFSET(Report!$B$1,1,0,COUNTIF(Report!$B$2:$B$30,"&lt;&gt;-"),1)</code></p> <p>For that to work, I had to change all the empty cells to output <code>"-"</code> when empty in stead of <code>""</code>. I couldn't get <code>COUNTIF</code> to accept <code>"&lt;&gt;"""</code> or <code>"&lt;&gt;"</code> or any other weird tricks I tried. Using <code>"-"</code> was easier.</p> <p>That makes a dynamic named range that changes size as I put data into the sheet. I named a similar range for everything I'm trying to chart (Approved Status, Call Ready, Not Ready). The chart was able to accept those names and now it's dynamically sized. If I only have three agents on the sheet, it shows three huge bars. With twenty, it shows twenty bars (exactly what I was looking for).</p> <p>One other tip: I changed my first row to output <code>""</code> when empty, so that <code>COUNTIF(Report!$B$2:$B$30,"&lt;&gt;-")</code> always returns at least 1 (otherwise you get annoying errors because you have a named range referencing a 0-length array).</p>