Office Tips and Hints Blog

Sunday, December 10, 2006 – Permalink –
Display Row, Column Headings
User Function

Here's an odd little use of functions.

If you want to display the Row number on a spreadsheet, the formula
=Row()

works just fine.
You could then hide the Row and Column headings and format the Row numbers any way you want. If a Row is deleted the numbers will automatically update.

Column headings are a little harder. The formula =Column() will show the number of the Column, not the letter, i.e. "2" instead of "B".

The following formula extracts the Column letter:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

To break it down:

=ADDRESS(row_num,column_num,abs_num)

This finds the address at Row number "1" and current Column number. The abs_num of "4 " says make the result a relative address.

The formula will produce a result such as "AA1".

SUBSTITUTE(text,old_text,new_text)

This function looks at the address, i.e. "AA1".
It replaces the Row number character ("1") with a null or empty value ("").
The formula will produce a result such as "AA".

Also see Daily Dose of Excel by Dick Kusleika.

Dick mused:'
"Sometime before the year 3,000, Microsoft will hopefully increase the number of columns in Excel (Hey, I can dream can’t I). The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns - maybe more than I need."

Of course Office 2007 has taken it up to 16,284 columns.

[Edited entry from 5/31/2004]

Most email servers only allow for a small document attachment size. When you have larger files, such as Excel spreadsheets, the attachment may not go through. Eliminate the headaches of bouncebacks with a pdf converter. Converting to the
PDF file format makes your documents smaller. You can go either way with converters. Either from Excel to pdf, or the other way, from PDF to Excel. Once you have a smaller file, you can send it easier.

See all Topics

<Doug Klippert@ 4:59 AM

Comments: Post a Comment

Links to this post:

<$BlogBacklinkTitle$>

<$BlogBacklinkSnippet$>
posted by <$BlogBacklinkAuthor$> @ <$BlogBacklinkDateTime$>

Create a Link