Transcription

Continue

Microsoft excel 2007 advanced tutorial pdf free download55e87d357a2cbde81af62188 Introduction to Excel Excel Basics Doing More with Excel Extras INDEX Module-I Extend Your Insight Office Excel 2010 02 Extend Your Insight Office Excel 2010 For many people, Microsoft Office Excel 2010 is one of those programs you grow into. Unlesstheir job responsibilities demand a detailed working knowledge of spreadsheets right from the start, their earliest experiences with Office Excel 2010 might have been creating simple financial documents—maybe in response to a request from a manager, a need in their own business, or a question from a financial advisor.Over the years, Office Excel has evolved from a basic spreadsheet tools to a major business application that enables information workers to create, analyze, consolidate, report on. And share critical information that affects the entire business life cycle. This is not your grand-mothers calculator! Office Excel 2010 faces thesame challenge that Microsoft* Office Word 2010 takes on: offering powerful, sophisticated tools in an accessible way that enables both experts and novices to find what they need easily and produce professional-quality, accurate, and insightful results. The major changes in Office Excel 2010 offer exciting possibilitiesthat can simplify and extend the way users create, work with, and share financial documents—spreadsheets, work-books, reports, and more. Specifically, the additions and enhancements in Office Excel 2010 help users create better spreadsheets faster, understand, visualize, and share important information more easilywith others, and make data analysis easier and more effective, resulting in better insight for those decisions that affect the bottom line. This chapter introduces some of these significant changes. The Design of Office Excel 2010 The new results-oriented look of Office Excel 2010 brings to your work area the tools youneed-and only the tools you need-to complete what you’re trying to create, analyze, or illustrate in your worksheet. Consistent with other major Microsoft Office system applications, the user interface is designed to help you be more productive by offering a series of command tabs, as well as command sets and contextualcommands related to specific objects in your worksheet. Here’s a list of the command tabs you’ll find in Office Excel 2010: The Home tab includes the commands you need to work with the Clipboard; choose and change fonts; control the alignment of cell content; select number formats; choose cell style and format; andedit, sort, and search your data. The Insert tab houses the commands for the objects you add to your worksheets; for example, tables, charts, illustrations, links, and various kinds of text items, such as column or row labels. The Page Layout tab offers all things related to setting up the worksheet, including choosingthemes, selecting page setup options, controlling the scaling of individual objects, selecting worksheet options, and arranging items on the sheet. The Formulas tab includes the Function Wizard, the Function Library, the commands you need for creating and working with named cells, commands for formula auditing, andcalculation options. The Data tab offers commands for getting external data; managing the connections to external links; sorting and filtering your data; removing duplicates, validating and consolidating your data, and grouping and ungrouping cells. The Review tab includes what you need to proof, comment on and shareand protect the sheet. The View tab provides commands for choosing different workbook \views. Hiding and redisplaying worksheet elements (gridlines. the ruler, the formula bar and more), magnifying or reducing the display, and working with the worksheet window. Tip: To find out more about the new elements in theMicrosoft Office system user interface— including the new command sets, contextual tools live preview, galleries, and more—refer to Chapter 2 “A New Look.” Page Layout: A New View for Better Printing Printing worksheets-getting the margins just right and including everything you want on the page-can be a bit of achallenge (and time-consuming!) for both new and experienced Office Excel users. Office Excel 2010 includes a new view, called Page Layout new which gives you the ability to see how your worksheet is shaping up while you’re working on it. Display the worksheet in Page Layout view by clicking the View tab andselecting Page Layout View. Page layout view makes printing easier by taking away the guesswork. Rulers along the top and left side of the work area enable you to plan for specific content areas on the page. Because everything in Page layout view is fully editable, you can make data changes, select new for-mattingoptions, and save and print in this view. You can also view multiple pages in Page Layout view by reducing the size of the pages using the Zoom control. Tip: Make changes that relate to the appearance of the overall document—for example, adjusting margins, changing orientation, or filtering data—while you’re workingin Page Layout view. This enables you to get the best “big picture” sense of how the changes will affect your worksheet when printed. Page Layout view is also available in the view controls in the lower-right corner of the Office Excel 2010 window. The three views displayed in the control (to the left of the Zoom control)are Normal new. Page layout view and Page Break preview. Tip: The Custom Views feature in Office Excel 2010 is now available in the Page Layout tab of the user interface. Set the display features the way you want them click Custom Views, and then click Add to create a view you can apply to other worksheets as well.More Room, More Speed, More Choices Some of the changes in Office Excel 2010 will be most important for power users who create, update, modify, and report on mega worksheets that demand the utmost in processing power and speed. Here’s an overview of some of the changes that affect the way Office Excel 2010manages and supports large, complex worksheets. If you were mixing out the row and column limits of Office Excel 2003. You’ll be glad to know that the Office Excel 2010 worksheet has been greatly enlarged According to David Gainer. Group Program Manager for Microsoft Office Excel. “Specifically, the Excel 12 gridwill be 1,048,576 rows by 16,384 columns. That’s 1.500 percent more rows and 6, 30 percent more columns than in Excel 2003. And for those of you that are curious, columns now end at XFD instead of IV.-1 And even on huge, formula-intensive worksheets you will experience faster calculations because Office Excel2010 now supports dual processors and multithreaded chipsets, which enable Office Excel 2010 to take full advantage of your system’s processing power and dramatically reduce your wait time. Another speed booster: The memory management in Office Excel 2010 has been increased from 1 GB to 2 GB. giving youmore room to work with complex, sophisticated spreadsheets that previously mixed out your system resources. Additionally Office Excel 2010 moves the bar on some of the limits in the earlier version; for example, now you can set unlimited formatting in the same workbook (this used to be capped at 4.000 format types).The color palette of Office Excel 2010 has grown to support the full 16 million colors, and now you can create as many cell references to a single cell as your computer’s memory will allow (this used to be limited to 8.000 references per cell). Create Better Spreadsheets, Faster Most professionally created worksheetstoday—if there are shared with an audience out of house-include a least a little bit of design. The titles are formatted to stand out; rules or shading might be used to call attention to special areas of interest. Worksheets, when they are included in financial reports, business plans, or presentations, often are given the samelook and feel as the surrounding material, so the font, color, and arrangement of the information become important. Office Excel 2010 makes it easier to create better looking documents by providing a number of easy-to-apply formatting options. And if you’re working in Page layout view, you can see how those designchanges look when you print them. Easier Access to New Templates When you start a new worksheet in Office Excel 2010 by choosing New from the File menu, the New Workbook window opens, immediately offering you a list of template categories from which you can choose. This greatly simplifies connecting toMicrosoft Office Online (which Office Excel 2010 does automatically when you choose a template type) and searching for the template categories you might want to try. Click the template category you want to see (for example. Business), and if the category includes subcategories, click the one you want (such asAccounting). Office Excel 2010 connects to Microsoft Office Online and displays a collection of templates available in that category. The selected template appears in a preview pane on the right side of the window. If that’s the template you want, click Download to download it to your system. Three Things to Try MarkDodge and Craig Stinson authors of Microsoft Office Excel 2010 Inside Out, recommend these as their favorite new features: 1. Use Page Layout view to see how your worksheet will be distributed between pages, to switch quickly between portrait and landscape modes, and to enter headers and footers directly on theworksheet. 2. Use Table Styles in conjunction with Themes to give your workbooks a consistent, professional appearance. 3. Use the new conditional formatting features to highlight dates that meet dynamic conditions, such as yesterday, today, last week, next week, or next month. Choosing Themes and Setting CellStyles The way in which you can find, try out and apply cell styles and themes in Office Excel 2010 is a huge improvement over the text-based, buried-in-the-menu options available in Office Excel 2003. Now you can highlight an area of your worksheet; click Themes in the Page layout tab of the user interface; andsample a gallery of theme styles that change the color, font, and spacing of the selected cells. Tip: As you can see choosing a different theme changes a collection of format elements. Several characteristics are included in each theme: the colors used the fonts, and the effects. The three tools to the right of the Themescommand enable you to set each one of these items separately, if you prefer Setting the format of a cell used to be something that required up-front thought and effort; now you can apply predesigned formats to cells and ranges by simply clicking the Cell Styles command and choosing the format from the gallery thatappears. You’ll find the Cell Styles command in the Home tab; just click the command to view and select an available format. Tip: You can create formats for your own cell styles and add them to the gallery. Start by applying the format you want to a specific cell then click Ceil Styles in the Sheet command tab and chooseNew Cell Style in the Cell Styles gallery. Review the information in the Style dialog box and click Format if you need to make any changes type a name for the style in the Style Name field and then click OK to save the style. The new style you created appears at the top of the gallery in the Custom category. Click-andType Headers and Footers If you ever had a problem trying to get headers and footers to print correctly on your work sheets in the past, you will appreciate the simplified way of adding and editing headers and footer in your Office Excel 2010 worksheets. Now you can simply click the Insert command tab and click Header& Footer in the Text command set. The worksheet is displayed in Page layout view, and the user interface changes to offer a collection of header and footer tools. A header box opens on the worksheet; you can simply click and type your header and use the elements shown to add items you need; for example, the pagenumber, date, time, and worksheet name. Tip: The Auto Header and Auto Footer commands on the left side of the header and footer tools give you a collection of ready-made headers and footers you can apply to your worksheet. Simply click the command and dick the selection to add it automatically to your worksheet.The Benefits of Microsoft Office Open XML The applications in the Microsoft Office system now use Microsoft Office Open XML format as the default file format. Open XML offers users several major benefits that relate directly to issues that Office Excel 2010 users care about: % Open XML enables you to save hugeworksheet files by using just a fraction of the space required by the previous format required. Open XML saves data independent of the format or schema, used to display the data in a particular way. This means the content can be preserved and used-independent of its particular form-in an almost unlimited number ofways. The information you create and share in your Office Excel 2010 worksheet, chart, or report can be incorporated in other worksheets or documents you create at a later time, saving you time and reducing the margin for error involved in rekeying important data. Major Charting Enhancements Charts provide you with away to communicate-visually and quickly—the numeric story your worksheet is telling. Charts help others understand how you are interpreting your data, enabling you to show trends and comparisons quickly and colorfully. Office Excel 2010 includes a huge array of chart improvements with galleries of predesignedformats you can apply instantly, great new 3-D options, and a full set of contextual tools that you can use to communicate your message in just the right way. Begin the process by selecting the data range(s) you want to chart. Then click the insert tab and choose the chart type you want to create. The chart appears onyour worksheet, and the Chart Tools contextual tab offers three