BET: Microsoft Excel Rows & Columns - Advanced Techniques

Leading the way in Microsoft Office Development

|

|

|

|

|

|

Microsoft Excel > Rows & Columns > Advanced Techniques

< Previous | Next >

Displaying the Column Width in a cell

	You can easily check the widths of your columns by displaying them in the first row of your worksheet.
	It is possible to create a named formula that can display the width of the column enabling the widths to be easily checked without moving the mouse and checking them all one by one.

Column Letters to Number Reference

This reference table is handy when you are using large Lookup tables

Col	No.	Col	No.	Col	No.	Col	No.	Col	No.	Col	No.	Col	No.	Col	No.	Col	No.	Col	No.
A	1	L	12	W	23	AH	34	AS	45	BD	56	BO	67	BZ	78	CK	89	CV	100
B	2	M	13	X	24	AI	35	AT	46	BE	57	BP	68	CA	79	CL	90	CW	101
C	3	N	14	Y	25	AJ	36	AU	47	BF	58	BQ	69	CB	80	CM	91	CX	102
D	4	O	15	Z	26	AK	37	AV	48	BG	59	BR	70	CC	81	CN	92	CY	103
E	5	P	16	AA	27	AL	38	AW	49	BH	60	BS	71	CD	82	CO	93	CZ	104
F	6	Q	17	AB	28	AM	39	AX	50	BI	61	BT	72	CE	83	CP	94	DA	105
G	7	R	18	AC	29	AN	40	AY	51	BJ	62	BU	73	CF	84	CQ	95	DB	106
H	8	S	19	AD	30	AO	41	AZ	52	BK	63	BV	74	CG	85	CR	96	DC	107
I	9	T	20	AE	31	AP	42	BA	53	BL	64	BW	75	CH	86	CS	97	DD	108
J	10	U	21	AF	32	AQ	43	BB	54	BM	65	BX	76	CI	87	CT	98	DE	109
K	11	V	22	AG	33	AR	44	BC	55	BN	66	BY	77	CJ	88	CU	99	DF	110

Filling In Blank cells between unique entries

	Select the cells A1:A10
	(Edit > GoTo) Click Special and select blanks.
	Type "=a1" and press (Ctrl + Enter)

	Select the cells A1:A10
	(Edit > Copy)
	(Edit > Paste Special) Click Special and select values

Sometimes cells are not wide enough to display the values. In this case the cell displays ####. You can check for these by checking the Value property of the cell is numeric and that the first character of the left of the text property is equal to “#”.

Proportional and Non Proportional Fonts

	If the default font is a non-proportional (fixed width) font, such as Courier, 8.43 characters of any type (numbers or letters) fit into a cell with a column width of 8.43 because all Courier characters are the same width.
	If the font is a proportional font, such as Arial, 8.43 integers (numbers such as 0, 1, 2, and so on) fit into a cell with column width of 8.43.
	This is because numbers are fixed-spaced with most proportional fonts.
	However, because letters are not fixed-spaced with proportional fonts, more "i" characters fit and fewer "w" characters fit.
	When you change the width of a column to a fractional number, the column width may be set to a different number depending on the font used in the Normal style.
	For example, with a Normal style font of Arial, if you attempt to change the width of a column to 8.5, the column is set to 8.57 or 65 pixels.
	This behavior occurs because of the translation of font characters to pixel units.
	Fractional pixel units cannot be displayed; therefore, the column width rounds to the nearest number that results in a whole pixel unit.

Pixels to Inches

	If you want to know wxactly how large something on a worksheet will be when it is printed you need to be able to convert inches into pixels
	Pixels to Inches: inches = pixels / dots per inch.
	Inches to Pixels: pixels = inches * dots per inch.
	You can obtain your DPI number from your Control Panel > Display settings.
	The exact number will depend on which driver you have installed but selecting the Settings tab and pressing the Advanced button will give you more information about your current driver.
	A typical DPI is 96.

Pasting Column Widths

	When you Copy and Paste cells in Excel (Ctrl-C, Ctrl-V) you can't automatically paste in the column widths that accommodate that data. But, you can paste in the column width by using Paste Special.
	After using a normal Paste to paste in the data and cell formats, right-click the new range of data you just pasted, choose Paste Special, and select the Column Widths radio button.

Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.

< Previous | Top | Next >