Search

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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 your information your 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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]

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. [ABSTRACT FROM AUTHOR]