A Better Way to Work with Tables

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.

Notice how the column names (A, B, C...) above the table have changed into the header names (City, Apples, Oranges...) for these columns of the selected table

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.

Excel 2007 creates a special row at the end of each table to perform aggregation calculations	Use conditional formatting to have in-cell graphs for a range of cells

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.