|
11- Why use a spreadsheet? Play Video A spreadsheet is a computer application that simulates a paper worksheet. It displays multiple cells that together make up a grid consisting of rows and columns, each cell containing either alphanumeric text or numeric values. A spreadsheet cell may alternatively contain a formula that defines how the contents of that cell is to be calculated from the contents of any other cell (or combination of cells) each time any cell is updated. Spreadsheets are frequently used for financial information because of their ability to re-calculate the entire sheet automatically after a change to a single cell is made. A formula identifies the calculation needed to place the result in the cell it is contained within. A cell containing a formula therefore has two display components; the formula itself and the resulting value. The formula is normally only shown when the cell is selected by "clicking" the mouse over a particular cell; otherwise it contains the result of the calculation. A formula assigns values to a cell or range of cells, and typically has the format:
Where the Expression consists of:
Custom Sorts
Filtering:
12- What’s changed in excel 2007? Play Video In a new results-oriented user interface, Microsoft Office Excel 2007 provides powerful tools and features that you can use to analyze, share, and manage your data with ease. Results-oriented user interface * The new results-oriented user interface makes it easy for you to work in Microsoft Office Excel. Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with drop-down galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option. * No matter what activity you are performing in the new user interface—whether it’s formatting or analyzing data—Excel presents the tools that are most useful to successfully complete that task. More rows and columns, and other new limits * To enable you to explore massive amounts of data in worksheets, Office Excel 2007 supports up to 1 million rows and 16 thousand columns per worksheet. Specifically, the Office Excel 2007 grid is 1,048,576 rows by 16,384 columns, which provides you with 1,500% more rows and 6,300% more columns than you had available in Microsoft Office Excel 2003. For those of you who are curious, columns now end at XFD instead of IV. * Instead of 4 thousand types of formatting, you can now use an unlimited number in the same workbook, and the number of cell references per cell are increased from 8 thousand to limited by available memory. * To improve the performance of Excel, memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007. * You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports dual-processors and multithreaded chipsets. * Office Excel 2007 also supports up to 16 million colors. Office themes and Excel styles * In Office Excel 2007, you can quickly format the data in your worksheet by applying a theme and by using a specific style. Themes can be shared across other 2007 Office release programs, such as Microsoft Office Word and Microsoft Office PowerPoint, while styles are designed to change the format of Excel-specific items, such as Excel tables, charts, PivotTables, shapes, or diagrams. * Applying a theme A theme is a predefined set of colors, fonts, lines, and fill effects that can be applied to your entire workbook or to specific items, such as charts or tables. They can help you create great-looking documents. Your company may be providing a corporate theme that you can use, or you can choose from the predefined themes that are available in Excel. It’s also easy to create your own theme for a uniform, professional look that can be applied to all of your Excel workbooks and other 2007 Office release documents. When you create a theme, the color, font, and fill effects can be changed individually so that you can make changes to any or all of these options. * Using styles A style is a predefined theme-based format that you can apply to change the look of Excel tables, charts, PivotTables, shapes, or diagrams. If built-in predefined styles don’t meet your needs, you can customize a style. For charts, you can choose from many predefined styles, but you cannot create your own chart styles. * As in Excel 2003, cell styles are used to format selected cells, but you can now quickly apply a predefined cell style. Most cell styles are not based on the theme that is applied to your workbook, and you can easily create your own. * In 2007 Office release, you can use conditional formatting to visually annotate your data for both analytical and presentation purposes. To easily find exceptions and to spot important trends in your data, you can implement and manage multiple conditional formatting rules that apply rich visual formatting in the form of gradient colors, data bars, and icon sets to data that meets those rules. Conditional formats are also easy to apply—in just a few clicks, you can see relationships in your data that you can use for your analysis purposes. * The following improvements make formula writing much easier in Office Excel 2007. * Resizable formula bar The formula bar automatically resizes to accommodate long, complex formulas, which prevents the formulas from covering other data in your worksheet. You can also write longer formulas with more levels of nesting than you could in earlier versions of Excel. * Function AutoComplete With Function AutoComplete, you can quickly write the proper formula syntax. From easily detecting the functions that you want to use to getting help completing the formula arguments, you will be able to get formulas right the first time and every time. * Structured references In addition to cell references, such as A1 and R1C1, Office Excel 2007 provides structured references that reference named ranges and tables in a formula. * Easy access to named ranges By using the Office Excel 2007 name manager, you can organize, update, and manage multiple named ranges in a central location, which helps anyone who needs to work on your worksheet interpret its formulas and data. Improved sorting and filtering * In Office Excel 2007, you can quickly arrange your worksheet data to find the answers that you need by using enhanced filtering and sorting. For example, you can now sort data by color and by more than 3 (and up to 64) levels. You can also filter data by color or by dates, display more than 1000 items in the AutoFilter drop-down list, select multiple items to filter, and filter data in PivotTables. * In Office Excel 2007, you can use the new user interface to quickly create, format, and expand an Excel table (known as an Excel list in Excel 2003) to organize the data on your worksheet so that it’s much easier to work with. New or improved functionality for tables includes the following features. * Table header rows Table header rows can be turned on or off. When table headers are displayed, they stay visible with the data in the table columns by replacing the worksheet headers when you move around in a long table. * Calculated columns A calculated column uses a single formula that adjusts for each row. It automatically expands to include additional rows so that the formula is immediately extended to those rows. All that you have to do is enter a formula once—you don’t need to use the Fill or Copy commands. * Automatic Auto Filtering AutoFilter is turned on by default in a table to enable powerful sorting and filtering of table data. * Structured references This type of reference allows you to use table column header names in formulas instead of cell references, such as A1 or R1C1. * Total rows In a total row, you can now use custom formulas and text entries. * Table styles You can apply a table style to quickly add designer-quality, professional formatting to tables. If an alternate-row style is enabled on a table, Excel will maintain the alternating style rule through actions that would have traditionally disrupted this layout, such as filtering, hiding rows, or manual rearranging of rows and columns. * In Office Excel 2007, you can use new charting tools to easily create professional-looking charts that communicate information effectively. Based on the theme that is applied to your workbook, the new, up-to-date look for charts includes special effects, such as 3-D, transparency, and soft shadows. * The new user interface makes it easy to explore the available chart types so that you can create the right chart for your data. Numerous predefined chart styles and layouts are provided so that you can quickly apply a good-looking format and include the details that you want in your chart. * Visual chart element pickers Besides the quick layouts and quick formats, you can now use the new user interface to quickly change every element of the chart to best present your data. In a few clicks, you can add or remove titles, legends, data labels, trendlines, and other chart elements. * A modern look with OfficeArt Because charts in Office Excel 2007 are drawn with OfficeArt, almost anything you can do to an OfficeArt shape can also be done to a chart and its elements. For example, you can add a soft shadow or bevel effect to make an element stand out or use transparency to make elements visible that are partially obscured in a chart layout. You can also use realistic 3-D effects. * Clear lines and fonts Lines in charts appear less jagged, and ClearType fonts are used for text to improve readability. * More colors than ever You can easily choose from the predefined theme colors and vary their color intensity. For more control, you can also add your own colors by choosing from 16 million colors in the Colors dialog. * Chart templates Saving your favorite charts as a chart template is much easier in the new user interface. * Using Excel charts in other programs In 2007 Office release, charting is shared between Excel, Word, and PowerPoint. Rather than using the charting features that are provided by Microsoft Graph, Word and PowerPoint now incorporate the powerful charting features of Excel. Because an Excel worksheet is used as the chart data sheet for Word and PowerPoint charts, shared charting provides the rich functionality of Excel, including the use of formulas, filtering, sorting, and the ability to link a chart to external data sources, such as Microsoft SQL Server and Analysis Services (OLAP), for up-to-date information in your chart. The Excel worksheet that contains the data of your chart can be stored in your Word document or PowerPoint presentation, or in a separate file to reduce the size of your documents. * Copying charts to other programs Charts can be easily copied and pasted between documents or from one program to another. When you copy a chart from Excel to Word or PowerPoint, it automatically changes to match the Word document or PowerPoint presentation, but you can also retain the Excel chart format. The Excel worksheet data can be embedded in the Word document or PowerPoint presentation, but you can also leave it in the Excel source file. * Animating charts in PowerPoint In PowerPoint, you can more easily use animation to emphasize data in an Excel-based chart. You can animate the entire chart or the legend entry and axis labels. In a column chart, you can even animate individual columns to better illustrate a specific point. Animation features are easier to find and you have a lot more control. For example, you can make changes to individual animation steps, and use more animation effects. * In Office Excel 2007, PivotTables are much easier to use than in earlier versions of Excel. By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away—you no longer have to drag data to drop zones that aren’t always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list. * And after you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data. * Using Undo in PivotTables You can now undo most actions that you take to create or rearrange a PivotTable. * Plus and minus drill-down indicators These indicators are used to indicate whether you can expand or collapse parts of the PivotTable to see more or less information. * Sorting and filtering Sorting is now as simple as selecting an item in the column that you want to sort and using sort buttons. You can filter data by using PivotTable filters, such as Greater than, Equals, or Contains. * Conditional formatting You can apply conditional formatting to an Office Excel 2007 Pivot Table by cell or by intersection of cells. * PivotTable style and layout Just like you can for Excel tables and charts, you can quickly apply a predefined or custom style to a PivotTable. And changing the layout of a PivotTable is also much easier to do in the new user interface. * PivotCharts Like PivotTables, PivotCharts are much easier to create in the new user interface. All of the filtering improvements are also available for PivotCharts. When you create a PivotChart, specific PivotChart tools and context menus are available so that you can analyze the data in the chart. You can also change the layout, style, and format of the chart or its elements the same way that you can for a regular chart. In Office Excel 2007, the chart formatting that you apply is preserved when you make changes to the PivotChart, which is an improvement over the way it worked in earlier versions of Excel.
13- The Ribbon Play Video
Excel 2007 has eight standard ribbon tabs (nine if you count Developer – shown in the image above; has to be turned on by the user) and an occasional “contextual” tab that shows up when you have a certain item selected. For example, if you have a picture selected, a Picture Tools: Format ribbon is available, as show in the figure below.
Home Ribbon:
The Home ribbon has common formatting and edit commands.
Insert Ribbon:
The Insert ribbon allows you to insert common objects, charts, links, images, and more. Page Layout Ribbon:
The Page Layout ribbon is used to layout your spreadsheets for printing.
Formulas Ribbon:
The Formulas ribbon allows access to different formulas so you don’t have to memorize all of the functions. Data Ribbon:
The Data ribbon has commands for accessing external data, sorting and filtering, and managing data in the spreadsheet.
Review Ribbon:
The Review ribbon contains the proofing and reviewing tools. If you have a tablet PC, you can also mark up the spreadsheet with “Ink.”
View Ribbon:
The View ribbon has different views available, allows you to control the zoom view of the document, and lets you access Macros.
Developer Ribbon:
If you are an Office developer, you may want to turn on the Developer ribbon. The Developer ribbon is used to access the underlying code and projects for writing modules for Microsoft Excel 2007.
14- The Microsoft Office Button Play Video
If you've ever used a previous version of Excel then you'll notice that the new version looks quite different! Microsoft have completely redesigned the look of the software. It has now what's called the Ribbon running right across the top. It's supposed to be more intuitive. Emphasis is placed on the tools and menu options appropriate to what you're doing. Here's a closer look at the Ribbon when Excel first starts (it's split in half, here):
In the top left of the left side of the Ribbon there's a big circle. This one:
Click on this, and you'll see all the file operations: Close, Open, New, Exit, etc:
The
Office Button, From this menu, you can create a new document, open an existing document, save the document, print it, prepare it for distribution, send it, publish it to the web, close the document, access Excel 2007 options, or exit Excel. Additionally, you can access recent documents in this menu. As you navigate the menu options, the Recent Documents pane changes with options for Save As, Print, Prepare, Send, and Publish. Each of these options has several sub-options.
For example, when you select Save As, the pane on the right changes to options available for that menu option. The Microsoft Office Button performs many of the functions that were located in the File menu of older versions of Excel. This button allows you to create a new workbook, Open an existing workbook, save and save as, print, send, or close.
Back To Top Back To Excel Index Page (Open Exercise file)
|
|
