The other day, I was working on a report that contained a plethora of columns and needed to replicate each column width on another worksheet. This report was obtaining data via SQL and the template needed to be configured. So I did a famous Google search to find the information…
This is what I came across to assist with determining the width of a column and it worked wonders. I was able to do what I needed to do in seconds rather than the time consuming process of using a bunch of mouse clicks.
In the end… my formula was…
This gave me the ‘cell’ width… #winning
MS EXCEL: CELL FUNCTION (WS)
Learn how to use the Excel CELL function with syntax and examples.
The Microsoft Excel CELL function can be used to retrieve information about a cell. This can include contents, formatting, size, etc.
The syntax for the Microsoft Excel CELL function is:
CELL( type, [range] )
PARAMETERS OR ARGUMENTS
type is the type of information that you’d like to retrieve for the cell. type can be one of the following values:
|“address”||Address of the cell. If the cell refers to a range, it is the first cell in the range.|
|“col”||Column number of the cell.|
|“color”||Returns 1 if the color is a negative value; Otherwise it returns 0.|
|“contents”||Contents of the upper-left cell.|
|“filename”||Filename of the file that contains reference.|
|“format”||Number format of the cell. See example formats below.|
|“parentheses”||Returns 1 if the cell is formatted with parentheses; Otherwise, it returns 0.|
|“prefix”||Label prefix for the cell.
* Returns a single quote (‘) if the cell is left-aligned.
* Returns a double quote (“) if the cell is right-aligned.
* Returns a caret (^) if the cell is center-aligned.
* Returns a back slash (\) if the cell is fill-aligned.
* Returns an empty text value for all others.
|“protect”||Returns 1 if the cell is locked. Returns 0 if the cell is not locked.|
|“row”||Row number of the cell.|
|“type”||Returns “b” if the cell is empty.
Returns “l” if the cell contains a text constant.
Returns “v” for all others.
|“width”||Column width of the cell, rounded to the nearest integer.|
For the “format” value, described above, the values returned are as follows:
|“G”||# ?/? or # ??/??|
|“D4”||m/d/yy or m/d/yy h:mm or mm/dd/yy|
|“D1”||d-mmm-yy or dd-mmm-yy|
|“D2”||d-mmm or dd-mmm|
range is optional. It is the cell (or range) that you wish to retrieve information for. If the range parameter is omitted, the CELL function will assume that you are retrieving information for the last cell that was changed.
The CELL function can be used in the following versions of Microsoft Excel:
- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
TYPE OF EXCEL FUNCTION
The CELL function can be used in Microsoft Excel as the following type of function:
- Worksheet function (WS)
EXAMPLE (AS WORKSHEET FUNCTION)
Let’s look at some Excel CELL function examples and explore how you would use the CELL function as a worksheet function in Microsoft Excel:
Based on the spreadsheet above, the following Excel CELL examples would return:
|=CELL(“col”, A1)||would return 1|
|=CELL(“address”, A2)||would return $A$2|
|=CELL(“format”, A2)||would return P2|