Catálogo de publicaciones - libros

Microsoft Office 2007 heralds the most significant changes to the Office suite for years. The user interface across the main applications has been given a new image aimed at improving usability as well as modernizing the overall look of the software. The regenerated appearance penetrates down to the documents with the introduction of new Office styles and themes and a new family of SmartArt graphics intended to give your creations an increased depth of professionalism. But the improvements are not simply aesthetic. Behind the contemporary façade lie some important enhancements that will allow you to experience more flexibility and functionality than ever before with Excel from the new XML file formats to improved analysis tools, although some critics may feel that many potential improvements have been omitted in this release.

It is always worthwhile taking some time to get to know an application and to adjust elements of it to suit your own preferences and requirements. Whether you are an ad hoc user or your job revolves around spreadsheets, by creating a personalized workspace within the Excel environment you can make sure that your workbooks are displayed the way you want them and that commands you repeatedly use are always near at hand.

Excel is often misconstrued, by those who are not familiar with it, as being a glorified calculator or an alternative to creating a table in Microsoft Word. Those who take the time to become acquainted with the application soon realize that Excel has much more to offer, and with a few fundamental techniques, users can easily create purposeful and flexible worksheet models. Excel is used by corporations and individuals worldwide for a gamut of purposes, from statistical analysis of numerical results to sorting and filtering lists of data. With each new version, Microsoft has streamlined and enhanced the multitude of worksheet operations that can be carried out in Excel to help users make the most of this versatile application.

For most Excel users who take pride in their creations, factors like the consistency and professional presentation of the finished document are almost as important as the validity of the data contained within it, particularly when the spreadsheet is intended to be viewed by others. The AutoFormat feature in previous versions of Excel automated at least part of the process of formatting lists and tables but had limited adaptability and, frankly, had begun to look rather dated. Excel 2007 has taken worksheet formatting to new lengths by providing a large selection of built-in styles for both individual cells and entire tables. Furthermore, if your spreadsheet is part of a range of documents, you can apply one of Microsoft Office’s document themes to ensure ultimate consistency and coordination across your spreadsheets, word processing documents, and presentations.

To make the data in your spreadsheet easier to work with, you may need to display the data in a particular order or view only those records that meet specified conditions. Sorting and filtering are the most basic types of analysis carried out on data, and Excel 2007 has built on the functionality of previous versions to allow you to carry out more complex sorting and filtering. This includes revising these tools to accommodate the new sophisticated conditional formats described in Chapter 4, such as the ability to sort and filter by cell color or cell icon. Excel 2007 has also expanded the sorting and filtering features to make them more versatile, in particular making it easier to sort and filter by multiple items. This chapter will look at how you can use sorting and filtering to display the data you require, arranged in the order you want to view it. It will also introduce you to Advanced Filtering in Excel, a powerful tool that is worth understanding if you need to create more sophisticated filters to sift through your data.

More important than its ability to record and display data are Excel’s analytical capabilities. When you use Excel to store related information in an organized structure, you can make full use of its various tools for analysis, as we have already seen when we looked at sorting and filtering in the previous chapter. This chapter discusses some of the other ways you can summarize and manage your data when it is arranged as a list or table.

PivotTables are a well-respected feature of Excel and are used in a myriad of ways for dynamically exploring and analyzing large datasets in order to summarize data and make informed decisions. Once you create your initial PivotTable, you can quickly rearrange (or pivot) it in order to view your data in various ways. PivotCharts are interactive charts that can be used to visualize PivotTable reports to convey information about patterns and trends within the data. PivotTables and PivotCharts can take their source data from within Excel or from external sources like databases, Online Analytical Processing (OLAP) cubes, and text files. One of the big advantages of using PivotTables and PivotCharts is that they create new elements within the workbook that can be updated and formatted, leaving the original data source unaltered.

Once you have created the basic layout of your PivotTable or PivotChart report, you have laid the groundwork for creating a vast range of summary reports and charts. In this chapter, you will learn how you can edit your PivotTable or PivotChart report so that it displays information way. As with lists and tables, you can sort and filter the data within your PivotTable to control which items are shown and the order in which they are displayed. You can also change the settings for a field to alter the function used to summarize data or to amend the layout of the data within the field, or if you want to perform any custom calculations on a PivotTable, you can insert a calculated field or a calculated item. The location of the PivotTable or PivotChart and the source of the data can also be updated, and you can quickly remove any filters or clear a PivotTable entirely so that you can begin constructing it again. By the end of this chapter, you could find that PivotTables are the only data analysis tool you’ll ever need.

What-if analysis basically involves asking questions of the type “What if a value changes?” and is used every day by those involved in data analysis and decision making. “What will our profit be if our sales increase?” or “What will the future value of an investment be if we increase the annual deposit?” are common examples of what-if type questions. The flexible nature of spreadsheets makes them ideal for carrying out what-if analyses, and Excel provides a number of tools to simplify the process of creating dynamic models, such as data tables, the Scenario Manager, Goal Seek, and Solver. These tools will allow you to explore various outcomes or solve complex problems without having to enter multiple versions of formulas.