Benefits of Using MS Excel

What are the benefits of Microsoft Excel? The advantages of MS Excel are many--especially to users who take some time to get acquainted with Excel's features.

One benefit is timesavings. Excel saves you time by offering an intuitive GUI (graphical user interface). A key feature of this interface is the Ribbon, which prominently displays icons for the tools you'll likely use most often.

When you think of what Excel excels at, you likely think of data manipulation first: sorting, filtering, tabulating and calculating data. Each of the tools to do these tasks is immediately available on the Home tab of the Ribbon.

Locate the sorting icon in the "Editing" tool grouping, and locate the filter tool through the same icon. To use this tool, create a list of numbers on a worksheet. Type a "1" in any worksheet cell, then a "2" in the cell below it. Select both cells together, then drag their lower right corner down for several cells. Click "Sort & Filter" on the toolbar, followed by "sort largest to smallest." Excel will resort the list to progress from 10 to 1.

Timesavings from other tabs

Excel's other tabs display tools as commonly used in their area as the Home tab. Click the toolbar's "Insert" tab to see icons for objects you can insert in your worksheets. These objects include charts, tables, artwork, and links to other, non-spreadsheet documents like Word files and web pages.

Select "Page layout" tab for the layout options you use most: fonts and styles for individual cells, and themes for unifying your workbook's whole appearance. Set your page's margins with tools in the "Page setup" group, and arrange your charts and art with the "Arrange" group's tools.

Formulas and more

Click the "Formulas" tab to insert statistical, text, date and other types of formulas -- including your own, custom formulas. (See this article for details.) Find functions for importing text from the Web, Word, Access and other data sources in the "Data" tab.

Spell check your workbook with the "Spelling" icon in the "Review" tab--and translate your workbook to another language in this tab.

Arrange your workbook's workspace with the "Arrange all" and related icons in the "View" tab, and extend Excel with macros and GUI controls in the "Developer" tab.

Excel's extensive data portability tools provide the benefit of a shortened work pipeline. In other words, Excel can import and export common and not-so-common data formats, which reduces your need for specialized format-conversion software.

Watch one of these portability tools in action. Start by opening Excel. Press "alt-F-o," then paste the following web address in the file name box: http://www.w3schools.com/XML/note.xml. When you press the "Open" button, Excel will correctly interpret the "file" as the URL to an XML file, and start an import wizard appropriate to that type.

Click the option that reads "As an XML table" in the "Open XML" dialog box, then press "OK" on the subsequent boxes. Excel interprets the XML file as a mini database, and creates a table based on it.

Excel can import many other formats, including CSV (comma-separated text), Access databases, and documents from its open source competitor, "Calc," from OpenOffice.org.

Read more about Excel's importing abilities here.

Exporting

You might expect that Excel can save each of the file types it can import, though it cannot. But Microsoft Office has workarounds for some types it can't read in through the "File>Save" tool.

For example, you can read in a regular Excel spreadsheet as a database table, if you're working in Microsoft Access. Inside Access, select the "External Data>Excel" icon on the toolbar. Complete the wizard that this action invokes to create either a link or a complete copy of the Excel table.

Notice the other formats that Excel can convert its default format to: Adobe Acrobat files (PDF), HTML files (web pages), and also the CSV and Open Office formats mentioned earlier.

Read more about Excel's export formats here.

Another benefit and advantage you gain from Excel comes from Excel's extensibility: using Visual Basic for applications (VBA), you can tailor Excel to your exact needs.

Open Excel and press "alt-F11" to enter the VBA integrated development environment (IDE). Press "f2," then scroll through the list of "Classes." Each of those classes is the blueprint for an object that you can make work for you.

One practical application of an Excel object is a custom function. With VBA, you can create a function that will appear with Excel's built-in functions, under Excel's "Formulas>Insert function" tool. Try the following example to create a custom function that extracts the domain name from an email address.

Caution: The following instructions involve changing the security settings of Excel. Do the instructions only with a full awareness of the risks involved.

Open Excel and create a new workbook. Enable macros in Excel by doing the following: press "alt-f," followed by pressing "Options." Select the "Trust Center" item, then press the "Trust Center Settings" button. Select "Macro settings," then choose the "Enable all macros" option, and also the "Trust access to the VBA project object model" checkbox. Press "OK" on all open dialog boxes.

Enter the IDE

Press "alt-f11" to enter the Visual Basic IDE, then press "Insert>Module" to insert a new code module in which to paste macro code. Paste the following macro code into that module:

Public Function extractDomain(emailAddress)

Dim n n = Len(emailAddress) - InStr(emailAddress, "@")

extractDomain = Right(emailAddress, n)

End Function

Return to Excel, type any valid email address in any worksheet cell, then select a blank cell. Press "Formulas>Insert function," then select the "User Defined" function category. Double-click on the "extractDomain" item. In the "function arguments" dialog box, enter the address of the cell in which you entered the email address. Press "OK" to complete entering the function. Notice the text displayed in the current cell: it's the domain name of the email address you entered.

Learn more about Excel's user-defined functions here.

What are the benefits of Microsoft Excel? As you've just learned, the benefits of MS Excel are improved workflow, saved time, and ability to extend this already powerful tool.

References

Microsoft: Excel Developer Center