Features of MS Excel 2003

MS Excel can be customized in a way that suits a user to achieve his goal. It is extensively used in financial organizations. The features of MS Excel are as follows:

Workbooks: A document in MS Excel is called a workbook. Each workbook contains sixteen worksheets by default. A user can change this number by resetting the default options. Worksheets within workbooks make it easy to bind files of related information. When a user opens a workbook, he can use all the worksheets to perform a task. To create a workbook, a user will have to select the New command from the File menu. To open an existing workbook, a user will have to select the Open option from the File menu. A user can insert a worksheet between two worksheets by choosing the Worksheet option from the Insert menu.

Using keyboard: The following table provides different keyboard shortcuts for selecting a row, column, current cell, worksheet, etc.

To Select Keyboard Shortcuts

Current Cell Enter

Entire Column CTRL+Spacebar

Entire Row hift+Spacebar

Entire Worksheet CTRL+SHIFT+Spacebar

A user can perform various kinds of entries in a cell. He can enter text, number, data, and time in a cell. He can also enter some special characters such as $ % + - / ( ). He can also enter date and time in a cell. He can select different formats for date and time according to his own requirements.

Entering Data in Series: A user can fill a range of cells either with the same value or with the series of values. This can be done using the Autofill handle (small square on the bottom-right corner of the active cell).

Cell References: MS Excel provides three types of cell references: relative, absolute, and mixed. Relative references refer to the cell relative to the given position. Absolute references refer to the specific cell irrespective of the position of the formula. The '$' sign is used to denote an absolute reference. Mixed references are a combination of both relative and absolute references. It has one absolute co-ordinate and one relative co-ordinate. $CI and C$1 are both examples of mixed references.

MS Excel allows fast searching and instant replacing of data. To search or replace the data entered by a user, he can use the Find or Replace command from the Edit menu.

Inserting or Deleting Rows and Columns:

MS Excel provides the facility to insert rows or columns into the existing worksheet. Inserting a row of data shifts the rest of the rows down and removes the last row of the worksheet. Similarly, inserting a column shifts the rest of the columns to the right and deletes the rightmost column. This occurs because the total number of rows and columns remain the same in any case.

MS Excel provides several formatting features that enhance the appearance of the data, which is presented in the worksheets. Some of them are described as follows: MS Excel allows setting up a page before printing. To set up a page, a user will have to open the File menu and click on the Page Setup. The Page Setup dialog box will appear that will allow the user to set the paper size, orientation of the data, scaling of the area, set the left, right, bottom, and top margin, set the header and the footer, etc.

MS Excel provides the autofit selection that adjusts the width of the column according to the widest entry in that column. A user can hide or unhide certain rows or columns. He can also provide the default column standard width.

MS Excel allows a user to align the characters in different ways by using the Alignment tab page of the Format Cells dialog box. The dialog box provides a number of alignment options.

Horizontal Alignment options:

General: The text is left aligned and the numbers are right aligned. This is the default status.

Left: Aligns the contents to the left of the cell.

Center: Aligns the cell contents to the center of the cell.

Right: Aligns the contents to the right of the cell.

Fill: Fills the selected cells with the single character.

Justify: This option is selected with multiple lines of text as it wraps the contents from left to right.

Vertical Alignment options:

Top: Aligns the text at the top of the cell.

Center: Places the text in the center of the cell.

Bottom: Aligns the text on the bottom of the cell.

Justify: Wraps the text from top to bottom.

MS Excel provides a facility of viewing the same worksheet simultaneously in more than one window. This is accomplished by selecting the New Window command in the Windows menu. A user can also create more than one window to arrange the workspace to promptly access the information needed. Using MS Excel, a window can be arranged in different ways by using the Arrange command from the Windows menu.

If a user wants to move between different windows, he can do so by pressing Ctrl+F6 to go to the next window or by pressing Ctrl+Shift+F6 to go to the previous window. He can temporarily hide or unhide windows by choosing the Hide command in Window menu to hide the active window or by choosing the Unhide command in the Window menu to unhide the hidden window.

When a user works on a very large worksheet with row and column headings, the row headings scroll off to the left when he moves towards the right. Similarly, when he moves down to see the data, the column headings scroll up. To overcome this problem, he can split the active window into two, with row or column headings in one window and the data on the other window.

Controls: The controls are the special objects, which enhance user interface and facilitate user input. MS Excel provides several custom controls such as list boxes, check boxes, and dialog boxes, etc. A large number of toolbars are found in the View menu. Users can add a toolbar or a number of toolbars into the working document depending upon the work involved and use different controls in their documents.

Functions and formulas: The built-in formulas are called functions. MS Excel provides analyzing data and manipulating text by using different functions. Users can easily calculate percentage, interest, average, etc. by using built-in functions. This can be done either by typing in the function-based formulas or by using function wizards. Formulas are widely used in simple computing (such as addition, subtraction, multiplication and division) and advanced computing. They provide the power to analyze data extensively.

Auto-calculation: MS Excel spreadsheet allows a user to automatically recalculate the whole worksheet every time a change is made in a single cell. There are basically two kinds of recalculations.

Automatic: In this type of calculation, the change in the value of the cell automatically recalculates the whole worksheet.

Manual: In this type of calculation, the recalculation of the complete worksheet is performed by pressing the F9 key. This option can be selected on the Calculation tab page of the Options dialog box, which is opened by choosing Options from the Tools menu.

Charts: One of the most important features of MS Excel is a chart. MS Excel allows users to view data entered as tables in a graphical form as charts, which helps a user to easily understand, analyze, and compare data. Excel allows its users to create either two-dimensional charts or three-dimensional charts. A user can enhance the chart by adding chart items, such as data labels, a legend, titles, text, and gridlines. He can also do formatting on these items by using colors, alignment, fonts, and other formatting attributes. MS Excel also allows users to view charts along with the data by using embedded charts. These charts are included in the worksheet and can be copied, moved, and resized in the same way as can be done with any other graphical object.

Database: Data are raw facts, information is processed data, and a database is an organized collection of information. Every organization heavily depends upon databases to store, retrieve, and maintain different kinds of data. MS Excel provides all this in the form of its database feature. In MS Excel, database can be created in two ways: Enter the data in the form of table in the worksheet.

Use the Data Form command

Records can be inserted, deleted, and sorted by using the Data menu.

With the above-described features of MS Excel, users can perform almost all the operations they want in a very efficient and easy way. Its advanced features have made it the first choice for professionals working in a financial organization to perform their lengthy tasks in an easy way and in a quick manner. Therefore, MS Excel has become the most preferred choice for most of the users as well as professionals throughout the world.

uCertify was formed in 1996 with an aim to offer high quality educational training software and services in the field of information technology to its customers. uCertify provides exam preparation solutions for the certification exams of Microsoft, CIW, CompTIA, Oracle, Sun and other leading IT vendors. To know more about uCertify, please visit http://www.ucertify.com/

 

Microsoft Excel

Microsoft Excel is a popular spreadsheet program that was developed by Microsoft. It is designed for computers that use the Windows operating system, and it can also be used on computers that use Mac OS as well. It has an easy to use interface with a number of tools that can make creating a spreadsheet fast and simple. This combined with a powerful marketing campaign has made Excel one of the most popular software programs in the world. Excel is typically bundled in the Microsoft Office package of programs.

Excel is not the first spreadsheet program to be produced by Microsoft. A spreadsheet program called Multiplan was first released by the company in 1982, but it eventually lost market share due to the release of Lotus 1-2-3. Because of this, Microsoft decided to create a spreadsheet program that could effectively compete against the dominance of Lotus. The very first version of Excel was introduced in 1985, and was available on the Mac. The first version for Windows would be released two years later. Because Lotus didn't bring their spreadsheet program to Windows quickly enough, Excel begin to gain a larger share of the market. By 1988 Excel had surpassed 1-2-3, and it is one of the factors behind the success of Microsoft as a software company.

The latest version of the software is Excel 11, and it is part of the Microsoft Office 2003 bundle. An Excel file will come in the form of .xls. A number of changes can be made to the interface of the program, but the GUI will always be composed of rows and cells. Information can be placed in cells which will have an effect on the data that may be present in other cells. In addition to this, Excel gives the user a large amount of control over the look of cells and the information that is placed in them. Both Microsoft Word and PowerPoint were designed to conform to Excel.

The introduction of Visual Basic with Excel allowed a number of tasks to be automated. Since 1993, Visual Basic has become an integral part of Excel, along with the introduction of the integrated development environment. However, the automated properties of Excel with Visual Basic has caused a number of macro viruses to be created, though many of them are now blocked by standard antivirus programs. Microsoft also allows users to disable the use of macros if they choose to, and this has largely eliminated the problem.

While Microsoft Excel was not well known during the late 1980s, it has now become the most widely spreadsheet software, though it is facing competition from a number of companies, most notably Google. Despite this, Microsoft has made a name for itself with the release of Excel, and next to Windows, it is one of the most well known software packages in the world. It has excellent calculation tools, and it can effectively be used for graphing as well. However, the software wouldn't have the dominance that it has today if it hadn't been for Multiplan, the predecessor that started it all.

Michael Colucci is a writer for Microsoft Excel which is part of the Knowledge Search network

Excel Tutorial - The Secret to Excel

Yes, there is a "Secret" to using Excel.

But what does that mean? Is this a gimmick? No, it’s honest to goodness advice from a computer training consultant. Be patient, read this page, and you will be a much stronger Excel user in the next 3 minutes. If I have only 5 minutes to give anyone an Excel tutorial, I would teach them the "Secret".

I was amazed when I learned what I call the "Secret" to Excel. I was amazed that Microsoft hadn't placed the feature as an icon on one of their toolbars. I was amazed that all of the "real" books I owned regarding Excel failed to mention the "Secret". Finally, in an Excel reference book, I found the "Secret" to Excel somewhere on page 350. The "Secret" had been buried so deeply in the book no one would ever read, see, or use the "Secret".

A Brief History Lesson.

When you inherit someone's "old" spreadsheet you need to analyze the formulas of the spreadsheet to see what they are doing. Even if the spreadsheet is one you created, you may have created the spreadsheet so long ago that you have forgotten what formulas you used.

Over the years Excel has improved its quality control features for analyzing formulas. But even today, 95% or more of all users research their spreadsheet formulas the old fashioned way. They click on a cell to see if it's a formula. Then they study the formula. Then they might click on another cell to see if it's a formula. And do the same thing over and over again.

Checking formulas with the mouse-click method is slow, tedious, and not thorough. It's a bad process. But that's the way over 95% of all Excel users research their spreadsheets. But there's a much better way. It is the "Secret" to using Excel.

Life with Excel spreadsheets when using the “Secret”

After using the "Secret" the spreadsheet makes sense again. I can remember all of my formulas. Since I can’t use pictures in this article, read this next passage carefully:

Learning the "Secret" to Excel helps you display all your formulas not as numbers but as logical text. You can see the logic of every single formula on your spreadsheet.

The "Secret"

The "Secret" to using Excel is a keystroke command named CTRL gravé. This is pronounced "control gra-vay". (Gra rhymes with the word pa, and vay rhymes with the word bay).

Because I can't use pictures in this article, I need to write some steps for you. Don't skim over these steps, do them. You'll be glad you did. (It's really quite simple, it's a CTRL gravé.)

First, open up an Excel spreadsheet file that has formulas in it.

Second, using your Windows (non-Apple) keyboard, look to the lower left hand corner of the keyboard.

Third, hold down the CTRL key with any finger.

Fourth, locate the number 1 key towards the upper left hand corner of the keyboard.

Fifth, find the key one key left of the number 1. It has the symbols ` and ~. The first symbol, the ` is called a gravé.

Sixth, still holding down the CTRL key, tap the gravé key.

Seventh, see that all your formulas now have logical text. So instead of saying 150, a cell might display =a1+a2

CTRL gravé is a toggle. Do it once to see your formulas in the spreadsheet. Do it again to display the numbers again.

Do you remember how when you were little you learned how to read at age 4, 5, or 6? Wasn’t it fun beginning to understand what all those neat symbols meant on the white paper? It was like discovering some secret code the grownups used.

That’s what the “Secret” to Excel is like. Every time someone gives you a spreadsheet, just use the “Secret” on the spreadsheet to understand its logic. You’ll enjoy this technique so much you’ll start showing it to your friends.

Best wishes from Chicago, IL USA.

To see the "Secret" to Excel training video, go to http://TheWorldsShortestExcelBook.com Read the first page to find the 5 minute training video on the "Secret". You can also sign up for Richard's free Excel courses delivered to you once a week.

Richard Kraneis is an author of Excel e-books and training videos. If you need on-site advanced Excel training for your company, please visit http://www.techspectrum.com/AET.html for further information. Thank you.

Excel: Five Issues to Consider When Developing an Excel Template

Microsoft Excel Templates are fantastic tools that allow you to get your team to put in their data for your business quickly and easily whilst still allowing for the consolidation of the data with very little work. There is however five key issues you need to consider when developing the Excel Template.

Issue 1: Protect the Worksheet and Cells

The first issue when creating a template is to ensure that your worksheet and cells are protected. For example the cells you want people to put data into should not have protection applied and the headlines you need to use to explain where to put the data into should in fact be protected.

Further to this in Microsoft Excel 2003 you can set that cells that are protected cannot be selected and I recommend that all headline areas are set in this way..

Issue 2: Lock the Workbook against Structural Changes

There are different levels of security in Microsoft Excel, such as security at the cell level, worksheet level and workbook level. Previous to this, I recommended that you use protection at the worksheet and cell level to restrict the access people can have to certain cells. The workbook also should be protected to ensure that your users cannot modify the underlying template. You can set two levels of security which are the Modify option and Read Only Option. The only time you would use the Read Only option is if your template was only being used to print out the data.

Issue 3: Use Formatting to make it easy to know where to put the data

There are some basic fundamentals I always follow when it comes to formatting in Microsoft Excel. The background of the area where I want my team to enter the data is always a lighter color, with the headings backgrounds being formatted with a darker color. This strategy is in line with that used in software development. You will notice in applications like Microsoft Word, you always type the text in the lighter area and the darker area is the headings.

The flip side is that if your background is a light color then the text being entered must be a dark color and for the headings, if your heading backgrounds are dark then the text should be light.

Always be carefully when using the colors Red, Green and Blue on templates as people with color blindness can find those colors confusing. The other issue to watch with formatting is to ensure that your colors print out correctly, especially if you have used color on the screen but it prints out in black and white. Some colors do not print out well on black and white printers.

Issue 4: Hide Unused Cells

One of the biggest mistakes I see people do with templates is to not hide any unused cells. Having unhidden cells runs the risk that your team may put in information in the wrong position or they may believe they need to put more information in than they need to. Always hide your unused cells.

Issue 5: Use Formatted Gridlines

When ever you are setting up a spreadsheet where a range of data needs to be entered always use dark gridlines to indicate exactly where the data should be entered. Generally I will use gridlines only where I want my team to enter the data. I do not generally use them around the headlines.

These five key issues will help ensure that your team uses the template in the manner that you intended. To finish off I would like to outline in one list the Five Key Issues you need to consider when developing a Microsoft Excel template:

Issue 1: Protect the Worksheet and Cells
Issue 2: Lock the Workbook against Structural Changes
Issue 3: Use Formatting to make it easy to know where to put the data
Issue 4: Hide Unused Cells
Issue 5: Use Formatted Gridlines

If you are looking for specific templates we have available Microsoft Excel Timesheet and a Microsoft Excel Invoice Template. If you would like to learn more about Excel check out our Microsoft Excel Training Kits.