A better way to work with tables

Sujay V Sarma

This month, we'll look at some of the features of the upcoming Excel 2007 and how will it help enhance user productivity in an organization.

Intelligent tables: Things have gone more intelligent at the table level. You are likely to have more than one table of information. When you scroll between them, it is hard to keep track of which table you're on and what its columns are. The usual solution has been the Freeze Panes option. This will work for one table, but not when you have more in the same sheet. In Excel 2007, solve the problem using the 'Format as Table' option.

When you do this, you can choose from various table format options, which are automatically applied to new rows and columns as you add them. You can also click and drag on the borders of the table to include adjacent rows and columns into the table. When you have blank columns, they will get headings of 'ColumnX' where X is a sequential number from 1. 

Also, all columns are configured with an Auto Filter and it is impossible (as of Beta 2) to remove the filter. Formulae are automatically applied to new rows based on what you have set up in preceding columns-so you no longer need to add a formula and drag-fill it to the other rows.

Similarly, once you've done the formatting, you can have Excel add aggregate formulae like row/column totals, averages, min/max calculations automatically. For this, add a new row at the end of the cell and tell Excel that this is a 'Totals Row'. The easiest way to do this is to select any of its cells and type in an aggregate function (=SUM(A2:A5)) and Excel will add drop down arrows for the other columns on this row. You can now delete your formula and select the one you want from the dropdown. You can also select a different function for each column.

Range references: In Excel 2007, there is a new way to refer to cell ranges as well. For instance, earlier when you had a column called 'Totals' and they ranged from A2 to F2, you would do AVERAGE (A2:F2) to get an average. Now, you can simply say AVERAGE(). If they are in a different sheet, or you want to pull the value from another table instead, you could do: AVERAGE (Shoes) where Shoes is the name of a table. For this purpose, it becomes more important to name your tables and/or cell ranges.

Sometimes, when you're saving a 2007 spreadsheet with such formulae into the older XP/2003 format, the converter in Office 2007 Beta 2 will strip out the table name but leave the cell reference intact. When you open this file later in Excel 2007, you would end up getting a lot of 'Circular Reference' errors, especially if your current and source columns have the same title.

Conditional formatting: This is a new feature in Excel and deals with the ability to have differently formatted cell graphics depending on the value of the range. For instance, if you have a column with scores of performances of different employees, systems or departments, it is a little difficult to find out at a glance where they stand vis-à-vis each other, without creating a chart of some sort out of that data. And if you have 15 such tables on a single sheet, creating a chart for each of them is time consuming. This problem is solved very neatly using Conditional Formatting.

To enable it for a set of cells, simply select the range by highlighting them and click on Conditional Formatting and pick one of the ready made formats. As your data changes, the graphics will also update to show you their relative standings. And, you can do this for subsets of cells within a single column and compare those sets against each other too. For instance, if you have a column giving overall departmental performance scores, and within this column you have region-wise data as well, you can first select each region's data individually and use one conditional formatting on them and then select the regional totals and do another conditional formatting. There are three (plus custom formats you can set up in each) conditional formats that you can use:

• 1. Data Bars: Choose from 6 horizontal colored bars that scale to the relative values in each cell of the selected range.
• 2. Color Scales: Similar to data bars but the entire cell is color-filled with shades for each value.
• 3. Icon sets: A selection of arrows, flags, shaded circles and other icons of different types to indicate relative values in the selected range. These usually work only on the top 3 to 5 of the data selected.

Next month, we will be back with more tips to improve your productivity with Office 2007.

Source: PC Quest