Microsoft Excel: The Good and The Bad

Excel is widely used spreadsheet program in the world at this point of time. Let's see what is so good about Excel and what is not so. However it is not humanly possible to list down all the advantages and disadvantages, so let's see about a few of them.

The Good

1. Functions: For me, functions are what Excel is for. Excel offers variety of functions for different uses and I find myself using lots of functions to work with text, numbers in office. With around 410 functions (in Excel 2010), almost any number crunching task will look like a cakewalk. What's more, you can even create custom functions using VBA if you feel what is there is not enough for you.
2. Customization: If you don't like the way Excel is visible to you, you can just change the look of it. You can change the default blue color scheme of Excel to either Silver or black. Sometimes it is fun. You can change the number of sheets that can appear in a newly created workbook (3 by default). You can add new menus, new buttons, change the layout of existing menus.
3. Programming: Want to automate a routine? No problem, Excel comes with a programming language built in which can be used to automate your regular tasks with click of a button. It is even possible to eliminate the requirement of that click of a button with the help of events. You can even create new functions and routines using VBA programming.
4. Large user base: Excel is the most used spreadsheet program. This makes Excel portable and sharing of your working file to any other user easy. You just send the file through e-mail and the receiver most likely be able to open it. Since Excel is so popular among users, there are many books, training materials and forums are available which helps any one who is in need.
5. Open document format: Starting Excel 2007, Microsoft started using open XML file format instead of proprietary software for Excel, this makes Excel files open-able in most of open source soft-wares like Google docs and OpenOffice.
6. Powerful Help: If you are in doubt about any feature of Excel or just want to explore, there is a powerful help system available in Excel. It works on-line and offline as well. Just type in your query and you get answer right on your screen.
7. OLAP database: You can use Excel as a database. On-line Analytical Processing (OLAP) is a technology that is used to organize large business databases and support business intelligence. With Excel you can connect to OLAP data sources just as you do to other external data sources.
8. Powerful analysis tools: Excel gives you tools to play with your data. Consider variety of charts, trends and Pivot tables you can meddle with and all required info for that stakeholder meeting is ready in minutes.

The Bad

1. Size limitation: Excel has various limitations with respect to size. You have only 65,536 rows and 256 columns to work with in Excel 2003. Excel 2007 increased these limits to 10,48,576 and 16,384 respectively. But if you have a full year journals dump to work with, these number of rows are not enough. Excel has many other limits on how many characters you can enter in a cell, how many nesting  you can use in a function, how many different types of formatting can be applied in a workbook.
2. Slow execution: If your data in a workbook is enormous, be prepared to see Excel work as slow as a snail or even crash.
3. Protection: Password protection is possible for any Excel workbook but it is not really that hard to crack. Microsoft never announced that Excel is a safe program to share sensitive information. If you know a couple of 'secrets' on how password protection works in Excel, you can crack any complex password in matter of minutes.
4. Viruses: Since Excel supports VBA macro language, a series of codes that can be used to find personal information of the user or corrupt files can be written and spread using Excel files. Many viruses were written and spread to infect Excel files in the past. Over the time, Microsoft introduced security features to disable macros and protect files.
5. Loss of data: Data loss is a possibility during Excel crashes unless you frequently save current working file. Excel introduced file recovery options in its later versions.
6. Accuracy: Character limitation in a cell creates a big mess while working with large numbers.  Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes. In my office, whenever I want to enter my 16 digit travel card number to request foreign exchange load while travelling, this limitation comes into play. Last digit is rounded off, what if no one notices and money goes to a different account? To work around this behaviour, format the cell as text, then type the numbers. The cell can then display up to 1,024 characters. But this won't work if you want to perform any calculations with this cell.
7. Bugs: Yes. There are number of bugs in Excel. Though Microsoft is taking care of eliminating these bugs, there are still some bugs lingering around. For example: If you are using Excel 2007, try multiplying 850 and 77.1 in any cell and see the result. Excel shows it as 100,000 where as my calculator says result is 65,535. There are plenty of bugs like this and we will see about them in near future.

Though Excel has bugs that can affect the user experience, it is by and large considered as best spreadsheet application. So happy using Excel.

Excel Versions: Excel 12.0 (Excel 2007) To Excel 15.0 (Excel 2013)

We have been discussing about Excel versions and we discussed until Version 8 to 11 in earlier post. Lets see about the right now most used and dramatically changed Excel version. Yes, we are going to talk about Excel 2007 (Excel 12.0) and later.

Excel 12.0 (Excel 2007)

Excel 2007 came as part of Office 2007 suite. Dramatically upgraded in terms of user interface, performances from Excel 2003, Excel 2007 used the new ribbon menu system. This new menu system reduced clumsiness and number of clicks required to perform an action, many users reported that new system is messy and time taking to understand when compared to straight cut menu bars.

Few new features:

1. Brand new interface - New ribbon system is used instead of classic menu style
2. More rows and columns - Excel 2007 has 1,048,576 rows compared to 65,536 and 16,384 columns compared to 256 in Excel 2003. For someone thinking how these magic number are arrived, they all are powers of 2. I will leave you to do the calculation now.
3. New themes & styles
4. Conditional formatting upgrade
5. Status bar shows 'Sum', 'Count' & 'Average', all at once.
6. Better charting

Microsoft has few articles on What's new in Excel 2007 & Specifications and limitations of Excel 2007.

Excel 13.0 (???)

Microsoft had not released Excel 13.0 version (there is no Office 13 either) due to aversion to number 13. From Mr. Google, fear of number 13 is called Triskaidekaphobia.

Excel 14.0 (Excel 2010)

Included in Office 2010, this version included 64-bit support and quite a few enhancements to the program.

1. Ribbon interface improved significantly - you can create custom tabs and groups and rename or change the order of the built-in tabs and groups.
2. Protected view - Excel files opened from internet source are opened in protected view. There is an option to enable editing.
3. Excel 2010 for mobile
4. Sparklines - Tiny charts that fit in a cell to make a better visualisation of data
5. Better conditional formatting
6. Improved filter - You can now search filter items. Start by typing your search term, and relevant items instantly appear in the list.
7. Paste with live preview
8. A button to take screenshot

Excel 15.0 (Excel 2013)

This is the most recent version of Excel. I still have to get my hands dirty on this version. Reading the new features and enhancements over internet makes me wanting to use this beauty sooner. For now Excel 2010 is my companion.

Read few articles about Excel 2013 here and here.

With this posts I conclude posts about Excel versions. We will talk more about Excel in future posts and how you can increase efficiency in daily office routines using power of Excel.

You may be interested to read earlier posts on Excel versions by clicking on links below.

• 1.0 to 4.0
• 5.0 to 7.0
• 8.0 (97) to 11.0 (2003)

Excel Versions: Excel 8.0 (Excel 97) to Excel 11.0 (Excel 2003)

We saw about earlier version of Excel in earlier post. Now let's continue our journey towards the later versions.

Excel 8.0 (Excel 97) 

Released in 1997 and included in Office 97, Excel 8 was a major upgrade that introduced Office Assistant (Remember the paper clip with eyes used to appear when you need help with Excel??). It introduced the now-removed Natural Language labels. A full VBA editor with separate code modules, user forms, and class modules along with event procedures was introduced (more on these in future posts). Also included are the Natural Language Labels (more information here)

Below are few new features added in Excel 97!

1. Conditional Formatting 
2. Data Validation 
3. Multiple Undo – Upto 16 steps undo 
4. Easy entry for range reference dialog - Dialog boxes that accept range references now have a button that collapses the dialog box to a smaller size so that it's not in the way while you select ranges on your worksheet. 
5. ‘Yes to all’ while closing books - When you quit Microsoft Excel 97 with multiple files open, you can choose to save all the files before exiting, instead of being prompted to close each open file. "A popular request from our customers.
6. Row and column headings indicate the active cell - As you move the highlight for the active cell, the row number and column letter "light up," so it's easy to see exactly where you are 
7. Up to 32,000 characters in a cell – From 255
8. Up to 65,536 rows per worksheet – From 16,384 
9. Macro virus protection – Message to enable or disable macros while opening new book 
10. Page break preview 
11. Merge multiple cells 
12. Indent text & rotate text in cells
13. Comments for cells 

Excel 97 includes a flight simulator as an easter egg. If you have access to this version of Excel, follow these steps to reach there.

1. Open a new workbook
2. Press F5
3. Enter X97:L97 and press Enter
4. Press Tab
5. Press Ctrl + Shift and click the Chart Wizard button on the toolbar.
6. You'll be greeted with a full-screen animated image. Use the mouse to move around. 

Trigger the egg. You'll be looking at a purple 3D landscape. Use the mouse to "fly" over the landscape (the mouse changes direction, left button moves forward and left button moves backwards). Fly around a bit and you'll see a grey stone pyramid. Fly to the side of the pyramid that's black and you'll see the credits scroll by.

Note: If this doesn't work, select the Tools Options command, then click the Transition tab. Remove the checkmark from Transition navigation keys and click OK. Then try it again.

Excel 9.0 (Excel 2000)

An updated version of the VBA language (VBA 6.0) was introduced incorporating modeless Userforms (more on this in future), and some functions, such as Join and Split. Excel 2000 was the first version to support the COM Add-In model, which allows you to write add-ins that can work in any Office application (e.g., Word, PowerPoint, etc).

Included in Office 2000, this version was a minor upgrade, but introduced the upgrade to the clipboard where it can hold multiple objects at once. The Office Assistant, whose frequent unsolicited appearance in Excel 97 had annoyed many users, became less intrusive.

Excel 9 (Excel 2000) featured 2 easter eggs.

Excel 2000 Credits

1. Open up a blank excel sheet 
2. Hit F5 
3. Type x2000:L2000 and hit enter 
4. You should now be on row 2000 
5. Hit tab once so that you are in column M
6. Hold control and shift then right click on the Bar Graph Icon at the top of the screen
7. Watch the list of developers for Excel scroll by. I know you've seen something like this before. 

Microsoft Office 2000 Web Components  (Requires Excel 2000, the Microsoft Web components, and DirectX)

1. Open a new Excel workbook. 
2. Select File * Save as Web Page 
3. In the Save As dialog, select 'Publish Sheet' and 'Add Interactivity' 
4. Save to an html file on your hard drive (any file name).
5. Open the html file with Internet Explorer 5.
6. Select cell WC2000 and scroll the sheet such that cell WC2000 is the first cell on the left. Highlight the entire row.
7. Press Shift + Ctrl + Alt and click the Office logo in the upper-left. 

Your screen will be transformed into an auto racing game, with developer credits (and other things) visible on the roadway. Use the following keys: 

• Arrow keys: to steer and accelerate 
• Space bar: To fire at other cars 
• O: To drop an oil slicks 
• H: To turn on your headlights 

Excel 10.0 (Excel 2002)

This version of Excel is included in Office XP. Smart Tags and the Formula Evaluation tool are probably the most prominent. The overall appear of Excel was modified to provide a softer colour pallet. The ability to recover corrupt files was substantially improved.

Apparently many complaints received from users of Microsoft that the easter eggs are using up useful system resources and bloating up the space. Microsoft stopped including easter eggs in its products from 2002 as a part of its Trustworthy computing initiative.

I have included couple of interesting features of Excel 2002 but they are not easter eggs. (Courtesy: John Walkenbach, Spreadsheet page)

Introduction of BAHTTEXT function:

In Excel 2002, Microsoft introduced a new worksheet function: BAHTTEXT. This function converts a number to Thai text, and adds a suffix of "Baht." The figure below shows this function in action.

Now, I'm sure there are many people who welcome this new function. But here's why BAHTTEXT is listed in the "Oddities" section: Excel does not have a similar function to convert numbers to English text. In fact, BAHTTEXT is the only function that converts numbers to text.

A function to convert numbers to words is a common request in the Excel newsgroups. Yet, for some reason, Microsoft chose to use its development resources to create a BAHTTEXT function rather than a more generally useful NUMBERTEXT function.

So why did they choose Thai?

It's been suggested (by an anonymous Excel MVP) that the Excel programmers enjoy Thai food, and they created this function to facilitate email orders to Redmond Thai restaurants. This theory has not yet been confirmed -- but then again it hasn't been denied either.

Hijri Dates:

If you use the English language version of Excel 2002, try entering the text below into a cell:

A1-1

You'll get the result shown below! 

Furthermore, check the formula bar and you'll see that Excel interpreted your entry as a date -- March 25 of the current year. Take a look at the cell's number format, and you'll see that it's a custom format: B2d-mmm 

This same weirdness occurs with other text strings that begin with A1 followed by a hyphen. This, I've been told, is a Hijri (Islamic) date. But I haven't been told why a Hijri date pops up in an English language version of Excel.

If your company uses part numbers with this format, that's a good reason to avoid upgrading to Excel 2002.

Excel 11.0 (Excel 2003)

Only minor updates have been done in this version compared to Excel 2002. This version added additional support for XML. ‘Lists’ is another development worth noting. Information rights management was introduced where in you can specify who can read the document and who can edit it.

Microsoft is ending support for Excel 2003 on 8th April 2014.

We will continue to look about more recent versions of Excel in next post. Click here for next post.

Excel Versions: Excel 5.0 to 7.0

We started discussing about Excel 1.0 through Excel 4.0 in earlier post. Here is brief about Excel 5.0 to Excel 7.0

Excel 5.0

Released in 1993, Excel 5.0 was the first Excel version and office application to include Visual Basic for Applications (VBA), also called as Macro language. We will have series of posts on VBA in future but to touch base shortly, VBA is a programming language based on Visual Basic to add ability to automate tasks in excel and also create user defined functions. You can also record you actions that Excel saves as a code which can be used multiple times to automate simple tasks. However addition of VBA in Excel made it target for macro viruses. Antivirus applications started detecting these viruses and alerted users. Microsoft later came up with steps to prevent misuse by adding option to disable macros completely and enable trusted macros etc.

Excel 5.0 was the first spreadsheet to support multiple worksheets with in a single workbook.

Excel 5.0 contains an easter egg called ‘dot matrix credit display’. I don’t suppose anyone of us is using Excel 5.0 version now (Microsoft stopped support to this version very long back) but if you do have access to it, below are the steps to reach to the easter egg. 

1. Begin with a new (blank) worksheet displaying the standard toolbar. 
2. Right click on the toolbar. 
3. Choose CUSTOMIZE. 
4. Under CATAGORIES, scroll down to CUSTOM. 
5. Click and drag the solitaire (deck of cards) icon to the toolbar. 
6. When the ASSIGN MACRO box comes up, click OK. 
7. Close the CUSTOMIZE box. 
8. Press and hold CTRL-ALT-SHIFT, then left click on the solitaire icon. 

Excel 6.0

There was no version 6 of Excel. The version numbers jumped directly from 5 to 7 in order to bring all Office applications into a consistent version numbering system.

Excel 7.0 (Excel 95)

Excel 7.0 (Excel 95, we will use these two names interchangeably) was released with Windows 95 and as a part of Microsoft Office 95 version. This was the first time all major Office products like Word, Excel, PowerPoint has the same version number. This was the first version of Excel to use full 32-bit code. While this represented an improvement in the internal workings of Excel, it doesn't change the user's experience. Microsoft discontinued support for Office 95 programs in 2001/02.

Excel 7.0 has an impressive game (Easter egg) named 'Hall of Tortured Souls'. If you have access to excel 95, follow below steps to get to this game.Open Excel 95 with a blank work sheet 

1. Go down to the 95th row 
2. Select the whole row 
3. Tab over to column B 
4. Go to Help/About 
5. Hold down ctrl-alt-shift and click on the tech support button 
6. A window appears called 'Hall of Tortured Souls' 
7. At the end of the hall and all the programmers names do a 180 turn and type 'excelkfa' without quotes. 
8. Walk-through the wall and see the pictures.

We shall talk about next versions of Excel in later posts. Click here for Excel 8.0 to Excel 11.0.

Excel Versions : Excel 1.0 to 4.0

In this post, we will see a brief about MS Excel initial releases. This may help to know the history of Excel and how it came up as a market leader in its market.

Excel 1.0

Microsoft initial spreadsheet product was named as ‘Multiplan’ and was marketed in 1982. This was released for CP/M operating system (Control Program for Microcomputers released for Intel computers). Multiplan became very popular in CP/M OS but lost to its competitor Lotus 1-2-3 in MS-DOS platform. 

Microsoft released the first version of Excel (Excel 1.0) for Macintosh on September 30, 1985. Excel 1.0 was not released for Windows or MS-DOS. There never was a DOS version of Excel.

Excel 1.0 Screenshot

Excel 1.1

Excel 1.5

Excel 2.0

The first Windows version was labelled ‘Excel 2.0’ to correspond to the Mac version (Remember? Excel 1.0 was released only on Macintosh OS). First version of Excel released in 1987 for Windows 2.0 which completely outclassed the market leading Lotus123 spreadsheet both in terms of GUI and core functionality. Almost overnight Lotus started losing market share and within a few years it was no more than a memory. Lotus was the largest software company in the world and the spreadsheet was the most complex and profitable program around. Yet Microsoft steam-rolled right over it with the first release. Remarkable!

Excel 2.0 screenshot

Excel 2.1 Screenshot (notice grey borders and 3D)

Excel 2.2

Excel 2.2a

Excel 3.0

Excel 3.0 was released on 1990. It included toolbars, drawing capabilities, outlining, add-in support, 3D charts, and many more new features.

Excel 3.0 (First application to use a modern toolbar)

Excel 4.0

Released in 1992, Excel 4.0 introduced ‘Auto-fill’ feature for the first time.

Excel 4.0 had an easter egg (an intentional joke or hidden message in an application or program). It has a hidden animation of a dancing set of numbers 1 through 3, representing Lotus 1-2-3, which was then crushed by an Excel logo.

Continue to Excel 5.0 to 7.0 here.

A Brief History of Spreadsheet

The name Spread-sheet is used to represent ‘spread’ which is a paper that has two facing pages (think of centre page in a book or newspaper) and format used to present book-keeping ledgers—with columns for categories of expenditures across the top, invoices listed down the left margin, and the amount of each payment in the cell where its row and column intersect.

VisiCalc:

Developed by Dan Bricklin and Bob Frankston, VisiCalc is known as first electronic spreadsheet. This software was implemented in Apple II in 1979 and IBM PC in 1981. It has many features that modern day spreadsheet contain, for example: interactive user interface, automatic recalculation, status and formula lines, range copying with relative and absolute references, formula building by selecting referenced cells.

VisiCalc soon became so famous, people started buying PCs just to work on VisiCalc. Experts often attribute Apple II success with VisiCalc. 

Below is the screenshot of VisiCalc:

If you are enthusiastic on how it used to work, you can experience it by yourself by downloading a version of VisiCalc on your PC, it works on DOS. Here is the link to download and further information on this awesome age-old tool.

Lotus 1-2-3:

Late 1982, IBM introduced Lotus 1-2-3 specially for IBM PC. It started outselling VisiCalc soon after release and was in market as leader.

Microsoft Excel:

First developed on Macintosh platform, Excel was released first in 1985 and was quick to take over lion’s share of Lotus spreadsheet market. Since this is what this blog is created upon, we will see more on Excel in coming posts.

Quattro Pro, Gnumeric etc are other somewhat popular spreadsheets available.

Source: wikipedia.org