Sunday 2 December 2007

Excel cheatsheet

Excel is a powerful and flexible program; the Help area is also well-presented and tells you most things you need to know. Here's a few tips, though.

* Excel is NOT A DATABASE. It isn't. It doesn't pretend to be. It might look a bit like a data input form, but that is not the same thing. (It is common these days in interviews for people to mention Excel when you ask them about database experience). It does, however, have some clever text-handling properties, as well as dealing with figures.

* Problem: cell showing no data, just ########. This perplexes new users and looks fatal. It is completely harmless: this is displayed when the content for a cell is longer than the column width. Widen the column, and the data is shown. (There is a fairly good reason for this feature of the program: if you are showing financial data then you wouldn't want a column showing a deficit of £1003 when the figure was actually £100,300,000).

* Use 'Print selection' and define the area of interest unless you want pages of blank squares

* Use 'Hide columns' when you are working on two widely-separated groups of data (unfortunately this part of the program is not very well developed: an alternative is to copy the data to a new worksheet and delete the unwanted columns from there) (note also that copied and printed data will show Hidden columns) (for this reason it as well to think about the sequence of data elements when you are setting up your worksheet)

* To quickly sum a group of figures, highlight the area; the total is shown at the bottom righthand corner of the screen

* Entering repetitive data: enter the same data in two rows, then select those rows and a block of subsequent rows: it will be entered in all

* Be careful with column/cell properties: unless set to text then any leading zeroes will be dropped (eg an entry listing context "0096" will become "96"). If a column is set to text it will be displayed as entered, but mathematical functions will not work.

* The formula bar should be used for any calculation which may be needed more than once. It is possible to copy a formula into another cell. The default behaviour is if you have created formula "=sum(a2.a12)" in cell a13, to total the column, and then paste it into b13, it will automatically alter the formula to "=sum(b2.b12)". This is very useful but may cause problems if you lay out the data in an unusual way.

* The formula bar can also be used to assemble text strings (for example generating a series of urls by combining the elements: a_href="http://domain.name.com/ | pagename.htm"/ | Page title text | /a using "=concatenate(a1.a12)" to create the string a_href="http://domain.name.com/pagename.htm"/Page title text/a for each row).

* Make sure you save changes when you close the file; it is easy to discard them by mistake

* Excel data can be readily shared and imported into databases by saving as a .csv format file

No comments: