Important features in Microsoft Excel 2010

In my last blog on Basics of Excel, I explained the concepts of Excel file, worksheet and cell. While reading the blog, you might have been wondering the extent of cells or worksheets you could use for a certain file. This blog will discuss some important features in Microsoft Excel 2010

• A worksheet can have maximum 1,048,576 rows and 16,384 columns, which means more than 17 billion or 1700 crores of cells in one sheet – just imagine!!!!
• Maximum column width is of 255 characters and row height is of 409 points
• A cell can contain maximum 32,767 characters
• Number of sheets in a file (also called workbook) is limited by available memory; default is 3 sheets
• You can create 16 million colors in a workbook (32 bit with full access to 24 bit color spectrum)
• There are 64,000 unique cell formats or styles, 256 fill styles and 256 line weight and styles available
• Maximum 66,530 hyperlinks could be created in a worksheet
• Maximum 2,147,483,648 non-contiguous cells that can be selected
• 256 users can open and share a shared workbook at the same time
• 32,767 cells can be highlighted in a shared workbook        
• Excel can handle some humongous numbers

               Smallest allowed number: 2.2251 X 10-308

               Largest allowed number: 9.99999999999999 X 10307

               Largest allowed number via formula: 1.7976931348623158 X 10308

• Excel is also very powerful in handling formulas

               Highest possible length of formula contents: 8,192 characters

               Maximum internal length of formula: 16,384 bytes

               Maximum permissible iterations: 32,767

               Highest number of arguments in a function: 255

               Maximum nested levels of functions: 64

               Maximum user defined function categories: 255

               Maximum number of available worksheet functions: 341

               Cross-worksheet dependency: up to 64,000 worksheets could be referred to other sheets

               Dependency on a single cell: up to 4 billion formulas could depend on a single cell

               Linked cell content length from closed workbooks: 32,767

• Excel is quite flexible in accepting extreme value of time and date

               Earliest date allowed for calculation: January 1, 1900

               Latest date allowed for calculation:   December 31, 9999

               Largest amount of time that can be entered:  9999:59:59

Basics of Excel - File, Worksheet, Cell

Today’s blog covers rudimentary yet important aspects of Excel – worksheet and cell.

Before you start working with Excel, you need to understand how these concepts are linked. When you open an Excel file, you would notice a number of worksheets. Each worksheet has number of cells. You could understand this by an analogy – cells are like houses, worksheets are blocks or wards and file as a city. Now think of writing a letter (hardly people write now though!!). You start with residential or office address, i.e. the address of entity being referenced followed by block , city and so on. Although we write address in this way, in reality the address is accessed in opposite manner, i.e. from larger to smaller entity. Same principle is followed in Excel also. When you refer to a cell from a different file, file name, then worksheet name and then cell name must be mentioned. Good thing about Excel is that you don’t have to remember; just click onto the cell and then the address would be copied. For a cell on same file, but different worksheet, only worksheet  name before cell address is required.

Talking about cell address which is as per our analogy the address of house or office, it has to be unique and easily identifiable. Those who are conversant with Google Map or could remember geography classes of yore, it will not be difficult to locate a place – it’s simple the intersection of latitude and longitude, or vertical and horizontal lines. If you have studied coordinate geometry,  you could also recollect how a point on 2-d plane could be located – intersection of abscissa (X-axis) and ordinate (Y axis). Similarly any cell in Excel  could be conceived to be the intersection of horizontal (represented by numbers- 1,2,3 and so on) and vertical (represented by alphabets -A,B,C and so on; remember these are not case sensitive, hence a,b,c would suffice). Only difference lies in the order of representation. In case of coordinate geometry, X axis, i.e. horizontal value comes first followed by vertical, i.e. Y-axis value. In case of Excel cell address, vertical i.e. column value in terms of alphabets comes first followed by horizontal i.e. row value in terms of numbers. This concept seems to be trivial, but holds great significance in case of absolute referencing to be discussed later. This is also important while it is imperative to link the content of a cell, which will be explained though following video.

Hope you have understood the basics of Excel. In my next session, I will discuss the concept of absolute and relative referencing. Until then, you should practice the cell linking with some of your own data.

Length of surname (First name-Last name format)

A few days back, on my Facebook page, I have posted a question - how to find out the number of characters of surname of a standard name (first name-last name only). This post will try to explain the rationale.

As can be seen, this lesson involved two functions, both belong to Text category, viz. SEARCH and LEN. Now think how these could be used for other cases.

Assignment:

As mentioned in the video, now think how these functions could be used for two other cases:

1. A name having middle name

2. A name with no surname

This blog - Excelable will discuss hands-on Excel techniques with the help of text and video tutorials.