StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In Excel 2007 turn your data matrices into tables.</p> <p>This can be done by clicking on a matrix and then on the <strong>Insert</strong> tab select <strong>Table</strong>. The keyboard shortcut for this functionality is <strong>Ctrl-L</strong> or <strong>Ctrl-T</strong>. Multiple tables can exist on the same worksheet.</p> <p>Once your data is marked as a table, the table will dynamically expand when new data is added. </p> <p>Each table is automatically given a name, starting with <em>Table1</em>. The name can be change via the <strong>Table tools - Design</strong> tab.</p> <p>In formulas each table can be referenced by it's name.</p> <pre><code>=SUM(Table1) </code></pre> <p>Each column heading in the table is also usable in formulas.</p> <pre><code>=SUM(Table1[Column1]) </code></pre> <hr> <p>In versions of Excel prior to 2007, '<strong>Dynamic named ranges</strong>' can be used.</p> <p>These can be created via the <strong>Insert - Name - Define</strong> menu.</p> <p>Give the '<strong>Dynamic named range</strong>' a name (e.g. Table1) and enter a formula similar to the following assuming your matirx starts in cell A1:</p> <pre><code>=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!A:A)-1,3) </code></pre> <p>If your matrix starts in cell D10 the formula would look like this</p> <pre><code>=OFFSET(Sheet1!$D$10,1,0,COUNTA(Sheet1!D:D)-1,3) </code></pre> <p>This formula excludes any column heading in the matrix. It selects data on a 'n x 3' basis.</p> <p>In formulas each '<strong>Dynamic named range</strong>' can be referenced by it's name.</p> <pre><code>=SUM(Table1) </code></pre> <p>You will need to review the layout of your worksheet as the dynamic named range works out it's number of rows by counting all items that appear in the first column of data.<br> If you have cells populated above and/or below your matrix they will be included in the calculation and the '<strong>Dynamic named range</strong>' will include rows below your data matrix.</p> <p>To see which cells are included in a '<strong>Dynamic named range</strong>' or table press <strong>F5</strong> and type in it's name, then click on <strong>OK</strong>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload