• Automatic resizing of table using dynamic named range ◇ If you build a pivot table based on the Excel table, you will never have to update the data source range again ! • No need to freeze the header row as you scroll down the table • When data gets added to a table, 3 things automatically happen: 1. Formatting is applied 2. Formula copied (down) to new rows ◇ if formula is present and is consistent in the column 4. Table cell range expands ◇ Range do not include header row and Total row (see below)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• Rename table to something meaningful • Edit table name: Formula > Name Manager
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Row and Aggregate Type
• Total row: automatically sum a column
# Show Total row Table Tools > Design > Total Row
• To change the aggregate type (avg, min, max, stdDev etc.) hover over to the right side of the cell in Total row and click on the down arrow • To quickly summarize data with Pivot Table, Table Tools > Design > Summarize with Pivot Table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Making a Selection
• To select the whole column, hover over column heading (4-sides arrow). Click again to include the Header • To select column with data, hover over just little bit below column heading (down arrow)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Constraints
• Avoid formatting the whole column (with and with data), this will increase the file size. Instead, change format in one cell and Excel table will format the rest cells in the column for you • If number of rows > 100,000 and there are lots of formula in the table, the machine may get slow
Index