While Access or a database program is undoubtedly the best place to store complex data, Excel is great for simple lists and even more so if you’re using Excel 2003.
By Helen Bradley
You and I use lists for everything from Christmas shopping to packing for a trip. We also use lists at work where phone lists, products lists and even sales results are all part of our work day. While Access or a database program is undoubtedly the best place to store complex data, Excel is great for simple lists and even more so if you’re using Excel 2003. It has a new list handling tool which, for most of us, will prove to be the biggest plus in upgrading to Excel 2003.
Because list handling is so different and so much improved in Excel 2003, this week, I’ll show you some of the problems that occurred with list handling in previous versions of Excel and how the new Excel 2003 lists tools solve these.
WHAT MAKES EXCEL 2003 LISTS DIFFERENT?
Past versions of Excel exhibit some problems with list handling that makes using these versions for list management more difficult than with the new version. One of these problems is that of totaling list items when you have a filter in place. If you use the Sum function – and, for most of us it is an obvious choice – it fails spectacularly. The Sum function doesn’t differentiate between hidden and visible cells so it totals everything regardless of whether it is visible or not. The solution, in earlier Excel versions is to use the Subtotal function to sum items in a filtered list so the results make sense.
Luckily the AutoSum button on the toolbar inserts a Subtotal function automatically for you when you’re working with filtered data, so the solution isn’t difficult to create – it’s just not obvious that this is the solution. You probably only ever find this information after you’ve tried a Sum function, discovered it doesn’t work and then gone looking for help to resolve it.
Another problem with pre 2003 Excel lists is creating a chart with the list data when the list grows over time. For example, consider the case where you record the weekly sales from your business. At the start of the year you have only one or two weeks worth of data, at the end you have 52 weeks worth. If you chart this data you have to keep altering the plotted area as the data is added. The alternative was to use a very complex workaround to create a chart which would expand as data was added.
The new Excel 2003 list tool solves this problem and you can base your chart on the data in your list and it will grow automatically as your list grows.
One final problem with a pre Excel 2003 list occurred where you had data in the cells to the side of the list. If you added extra rows into the middle of the list the rows are also added to the side of the list potentially throwing out a second set of calculations. Excel 2003 lists are independent of data outside the list so you can add more rows or columns to the list and the cells around the list remain unaffected by the insertion.
CREATE A LIST
Creating a new Excel 2003 list from existing data is as easy as clicking in the list and choose Data, List, Create List. A small dialog appears suggesting the range covered by the list (alter this if it is not correct). Check the box to indicate the selected list includes column headings or not and then, when you click Ok the list appears with a narrow blue border around it indicating its dimensions.
At the bottom of the list (when you click inside the bordered area), a very Access like insert line appears with an asterisk in the left most column. This is where you insert the next list item, although you can also click anywhere in the list and choose Insert, Row to add a row and type your entry there.
To total list items, click the Toggle Total Row button on the List toolbar – if you click the total that Excel provides you’ll see it’s written using the Subtotal function which solves the problems which would otherwise occur if the list were filtered. To change the calculation click the down arrow to the right of the total and choose another function such as Average, Maximum or Minimum – all these are created using the Subtotal function so they calculate on visible values only when you filter the list.
If there is no total or you want to total columns other than the one at the far right, click the Toggle Total Row button and then choose the calculation from the dropdown list to the right of the cell – The down arrow indicator appears when you click in a cell in the total row.
To create a chart of the list data, select it and then use the Chart wizard or press F11 for a default chart. When you add data to the list, it will automatically appear on the chart – there’s nothing special you need to do.
EXPANDING THE LIST
You have already seen how new rows can be added inside the list or at the bottom of it. You can also add new columns in the middle by clicking in a cell to the right of where the column will go and choose Insert, Column – this leaves unaffected any data which is below or above the list. If you click the column letter before choosing Insert, Column, a new column is inserted the full length of the sheet not just inside the table.
You can also add a column immediately to the right of the list just by typing a heading in the top cell – when you do this the list expands to encompass it automatically. This feature can be disabled if desired if you choose Tools, AutoCorrect Options, AutoFormat As You Type tab and disable the Include new rows and columns in list checkbox.
If you decide an Excel 2003 list is not the tool for you, you can return the list to a regular Excel range by clicking in the list and choose Data, List, Convert to Range and answer Yes to the prompt to return the list to a normal range.
In the next article, I’ll look at filtering lists in both Excel 2003 and earlier versions.
