How To: Use MS Excel Effectively

Welcome to the first chapter of Excel is Simple, a place where we will burst all myths around excel and try to make it simpler and fun to use.

I would like to share an important tip…if you want to be fast with Excel, try to avoid using the mouse as much as you can. We will soon be speaking about keyboard shortcuts and how to go to the individual menu's etc later and there you can see how using the keyboard increases your speed.

The main components of the excel user interface are the quick access toolbar, ribbon, formula bar, the worksheet area and the status bar.

Let's look at each one of them individually.

The Quick Access Toolbar:

This small toolbar can be seen at the top left corner of the excel interface. By default you can see the save, redo and undo commands there but then if you click the small down arrow icon, you get options to customize the quick access tool bar. There are some options like new, open, print that are available plus an option to fully customize the toolbar by clicking the 'more commands' option. Now almost all commands in excel are available in this dialogue box. Click on the one that you most frequently use; then click add and click okay and the command is available in the quick access toolbar. You can also change the position of the quick access toolbar by clicking "Show below the ribbon" and if you want it back at the top "show below the ribbon".

Ribbon:

Ribbon contains most of the excel commands and I find this good when compared to the older versions of excel that had pull down menus, task panes and tool-bars. Ribbon brings excel commands at your fingertips; click any one tab and you will see all the commands contained in that ribbon. Beautiful interface and easy to use. Now if you are using excel to create forms and assign macros, you would want to add the developer tab to the ribbon. It is simple! Go to file, click options and in the excel options window and click "customize ribbons". By default the developer tab here is unchecked in ribbons; put a check mark on this and click okay. You will see the developer tab in the ribbon with all options available. You will possibly see more tabs in the ribbons when you are working with charts, tables and graphics. This is the beauty of the ribboned interface; only relevant tabs are visible

Formula Bar:

This formula bar will display the content of the current cell and if you have used a formula in a cell, it will display the exact formula. While working on formula or long text you can click on the base of the bar and drag it to see more content. While working on a formula if you are stuck, you can click on the fx icon and you will see all possible formula that you can use plus information about the formula. We will talk about this in detail when we work on the formula.

On the left of the formula bar, you can see what is called as the name box. It identifies you current cell, your selection or the named list that you are using

The Worksheet:

Worksheet is the area where you work. It is comprised of cells, these are the building blocks of a spreadsheet. What you have in this array is rows and columns.

Row is a horizontal area that goes from left to right. Rows are indicated by numbers. When you click on the indicative number of the row you are in turn selecting the entire row like this

Similarly, column is a vertical area that goes from top to bottom and indicated by letters. It starts from A up to Z and then AA to AZ and then BA to BZ and so on.

The number of rows and columns is one of the differences in the 2003 and later versions. While excel 2003 has 65K rows the later versions have more than a million. Similarly excel 2003 has a limited 256 columns while the later versions have 16K columns

Rows and columns make the array and the junctions are called CELLS. So, each cell will have its own address and this is what defines the cell. Let me help you with this.

The Status Bar:

You will find quite some information in the status bar. A spreadsheet can has n number of tabs or worksheets where you can enter data. You can add or remove worksheets here. You can even change color or rename these tabs which by default are named sheet1, sheet 2 and so on. You have got scroll buttons to manage the tabs and to move between them.

Then you also have an auto calculate indicator in the status bar. Lets say I have 3 cells with the following values 2,3, 4 and I just select this area and here you will find the sum, count and average. You will also find the various layouts and the zoom options available in the status bar. Right click on the status bar and you can see this is customizable as well.