Limitations of Excel

Limitations of Excel For Beginners

In previous article we have discussed about Excel and It's features. Now in this article we will talk about Limitations of MS Excel 2010, which helps us to understand excel better.

As we know excel is a spreadsheet application consist of workbooks, worksheets, columns & Cells so while working on excel we should know how many sheets, cells, column etc. we can have in it. 

Following are limitations of excel:

S. No	Particulars	Maximum limit
1	Open workbooks	Limited by available memory
2	Columns	256 columns
3	Column width	255 characters
4	Rows	65,536 rows
5	Row height	409 points
6	Page breaks	1000 horizontal and vertical
7	Sheets in a workbook	Limited by available memory (default is 3 sheets)
8	Colors in a workbook	56
9	Cell styles in a workbook	4000
10	Names in a workbook	Limited by available memory
11	Windows in a workbook	Limited by system resources
12	Hyperlinks in a worksheet	66,530 hyperlinks
13	Zoom range	10 percent to 400 percent
14	Undo levels	16
15	Fields in a data form	32
16	Number of available worksheet functions	329
17	Earliest date allowed for calculation	January 1, 1900 (January 1, 1904, if 1904 date system is used)
18	Latest date allowed for calculation	31-Dec-99

You can share your feedback and post your excel queries in this blog.



My First VBA Code (Macro)

My First VBA Code (Macro)

For Beginners

This Article helps beginners to write their first Macro in VBA.

Watch Video

People thinks writing a Macro/VBA codes is very difficult however this is not true and anybody can learn VBA by follow some Basic instructions.

VBA (Visual Basic for Applications): As it's name suggest it is extension of VB which is designed by Microsoft for Microsoft applications, such as Excel, Access, Word etc. It's a macro programming language, which used to automate excel and other Microsoft application. To Automate Excel you need to write Code (VBA script) in VBA Editor.
 

You can Access VBA Editor by just pressing Alt+F11 (Shortcut Key) or clicking on Visual Basic under Developer Menu Bar.

  

 

Now you will be able to see VBA Editor, where you can write your first VBA script for your Macro.

To write a code you need to insert a Module. To open the Module you need to go to the Insert Menu Bar and then select Module.

Here is your first Module.

Now you can write your codes. However before moving ahead we need to understand composition of VBA procedure.

As VBA is a structured Language hence, we need to follow its basic composition.

Composition of VBA Script:

A VBA module consists of Sub procedures. A VBA procedure is constructed from three types of statements which needs to be write between Sub and End Sub Statement

Executable: These Statement use to perform an Action, the actual task which you want to perform.

Declaration: This statement is used to define procedure, variables, properties, arrays and constant.  

Assignment: Assignment statements carry out assignment operations, which consist of taking the value on the right side of the assignment operator (=) and storing it in the element on the left.

Now we learnt about basic structure of VBA script, Let’s move ahead and write a program.

To begin with, we will create a Message Box Program, following are the Steps.

Steps:

1.    In Module, Type “Sub <Name of your procedure> ()”, Press Enter.

2.    Now Type Msgbox “Hello”

3.    Your module will look like as given below


 

4.    To Execute your program, Press F5

 
         5.    Now you have created your first Macro and it has been executed successfully.

Click Here to download the excel file along with the codes.


You can share your feedback and post your excel queries in this blog.

Excel & Its Features

Excel & Its Features (For beginners)

Microsoft Excel is a spreadsheet application developed by Microsoft. It is used for calculations, graphs, pivot tables and VBA (Visual Basic for Applications) a macro programming language.

It’s a very useful, dynamic & user friendly application, which used as a reporting solution.

Components of excel:

1.        Workbook: A workbook is a spreadsheet file. By default, each workbook in Excel contains three pages or worksheets.

2.        Worksheet: A worksheet is a single page or sheet in a spreadsheet program such as Excel

3.        Rows: Rows run horizontally in an Excel

4.        Column: Columns run vertically in a worksheet

5.        Cells: The intersection point between a column and a row is a cell.

6.        Range: group of Cells is called Range

Excel Functions: Excel Formulas are divided into following Categories however you can start apply function with basic operators such as + (Plus),- (Minus), / (Divide) & * (Multiply). Example “=1+2”

1.        Compatibility functions

2.        Cube functions

3.        Database functions

4.        Date and time functions

5.        Engineering functions

6.        Financial functions

7.        Information functions

8.        Logical functions

9.        Lookup and reference functions

10.     Math and trigonometry functions

11.     Statistical functions

12.     Text functions

13.     User defined functions that are installed with add-ins*

In Excel 2010, you can find all functions under Formula Menu under formula Library

 

Note:  

·         You can initiate a function by just entering “=” in any excel Cell.

·         *User define function are those functions which are not part of Excel Package however created by the user as per their requirements. It can be done through VBA. We will learn more on this in coming articles

Excel Charts: MS Excel Support various charts to display data in a graphical and meaningful representation and after creating the chart you can use the same by just copy/pasting to your presentation.

 

1.        Column charts

2.        Line charts

3.        Pie charts

4.        Bar charts

5.        Area charts

6.        XY (scatter) charts

7.        Stock charts

8.        Surface charts

9.        Doughnut charts

10.     Bubble charts

11.     Radar charts

In Excel 2010, you can find all charts under Insert Menu under Charts.

The simple way to create chart is just select the data range > Go to insert Menu Bar > Under Charts Select the required Chart.
 

 
VBA (Visual Basic for Applications): As it's name suggest it is extension of VB which is designed by Microsoft for Microsoft applications, such as Excel, Access, Word etc. It's a macro programming language, which used to automate excel and other Microsoft application. To Automate Excel you need to write Code (VBA script) in VBA Editor. 

You can open VBA Editor by just pressing Alt+F11 (Shortcut Key) or clicking on Visual Basic under Developer Menu Bar.

Learn More about VBA

Among all these features, excel have few limitation however these limitations does not impact it's ability to perform high level calculations. Click here to know about Limitations of Excel.

We will talk more about excel in the next article.

You can share your feedback and post your excel queries in this blog.