Tip of the week # 429
7 September 2014

Quickly format and enable filtering of your data (Excel 2007 onwards)

Have you ever struggled to read the data in an Excel spreadsheet and you find yourself using a piece of paper as a ruler to ensure you are matching up the data on the same line? Do you still switch on the filtering functionality, using Excel's Filter button? Since Excel 2007 you can quickly turn a range of cells into something resembling stock listing paper that mainframe computers used to churn out (remember those faint green horizontal lines?) and enable the filtering functionality.

Here's how:

  1. Click anywhere in the data that is to be formatted and analysed.
  2. Press CTRL + T or CTRL + L. (A table used to be called a list, hence the L.)
  3. Press ENTER. (If your data does not contain headers, first tick the check box.)

If you don't like the default style, rummage through the various predefined table styles, not forgetting the More button in the bottom left hand corner. A sizing handle in the lower-right corner of the table allows you to drag the table to the size that you want. Click the arrow in the column header to choose a filter so that you display only those records containing the data you want.