Catálogo de publicaciones - libros

It is in formulas and functions that Excel’s true power resides because it is through them that calculations and evaluations are processed and relationships between items of data are created. Formulas can range from simple addition or subtraction calculations to formulas that handle nonnumerical data and more sophisticated three-dimensional or array formulas. Functions are basically predefined formulas built into Excel to facilitate complex or cumbersome calculations and to enable you to carry out a wide variety of analytical tasks. Most of Excel’s analytical tools, like the PivotTables or data tables discussed in previous chapters, are simply user-friendly facades for the multiple complex formulas and functions required to produce particular analyses and reports.

Office 2007 introduces a variety of changes to Excel’s charting element, the most obvious being the new formatting features and special effects that are available. Creating eye-catching charts has never been easier, and in Excel 2007 you can choose to manually format or change individual elements of your chart or to quickly apply a predefined chart layout and chart style. The implementation of document themes in the latest version of Office also stretches to Excel charts, and the added ability to store chart formats and layouts as a template will help ensure the consistency of charts within your overall workbook or document portfolio.

Excel allows you to insert a variety of graphics into a worksheet, including pictures, clip art, WordArt, and shapes. These graphics are often integral to the usability of a spreadsheet, such as product images included in a spreadsheet used as an order form or a diagram used to explain a process. Or they can be purely aesthetic, like shapes used to emphasize text or arrows added to draw attention to particular results or forecasts. Either way, many Excel users will probably use some type of graphics within their workbooks at some stage.

Excel incorporates several tools to help you to prevent or correct errors in your spreadsheets. Starting with the actual data entry process, you can apply restrictions to the type of data that a cell can accept using data validation criteria. For example, you can specify that a cell can only contain a whole number or a date that falls within a particular timeframe. This can help avoid errors resulting from a cell containing the wrong type of data or a value outside the permitted range.

Spreadsheets are often used to store sensitive or confidential data, from client details to corporate finances. When a workbook is only intended to be viewed by the author or a limited group of users, it is advisable to apply some form of protection to it to prevent any unauthorized access. At other times, a number of people may require access to a spreadsheet, leaving it vulnerable to accidental or deliberate alteration. A spreadsheet designer will want to protect his or her work from tampering and may even want to keep elements of the workbook, such as formulas or particular sheets, hidden. If your spreadsheet is being used to record important data, you may wish to apply protection to reduce the risk of data being corrupted or being used by those it is not intended for.

Excel data can come from a variety of sources. If the data you want to analyze is available in another file or electronic format, you may be able to import it directly into Excel. For example, perhaps you want to view a subset of data from a large database in Excel, or you want to evaluate some of the enormous wealth of raw data that is available on the Web. Data sources that can be used with Excel include text files, Microsoft Access, Microsoft SQL Server Analysis Services (for OLAP databases), Microsoft Visual FoxPro, dBASE, Oracle, and Paradox. Excel 2007 has simplified the process of obtaining data from other sources and of managing the connections to these sources with most of the relevant commands available on the Data tab of the Ribbon. This chapter will show you how to import data from other sources like Access or the Web so that you can work with it in Excel.

In many organizations spreadsheets are often not the work of a sole author but include input from various users, such as the members of a project team. If several people need to be able to edit the contents of a workbook simultaneously, you can create a shared workbook and store it on a network location. You can then control which users should have access to the workbook and resolve any conflicting changes. When collaborating with colleagues on a workbook, it is usually important to keep a record of who edited what and any comments that reviewers have made. In this chapter I will discuss the reviewing features that are available in Excel and how you can track the changes that are made to a document.

If your organization is using SharePoint 2007, or Microsoft Office SharePoint Server (MOSS) 2007 to give it its official title, you can expand the functionality of Excel to facilitate interactive access to workbook elements using a browser. In MOSS 2007 Microsoft has produced a versatile platform for building web pages for both internal and external users. Excel Services is a new component of SharePoint that allows you to host spreadsheets on a browser and offload calculations onto a separate server while retaining control centrally.