First, rich conditional formats
The conditional format in Excel 2007 introduces some novel functions, such as color code, icon set and data column, which enables users to analyze data intuitively in a more understandable way. Depending on the position of the cell in the numerical range, the user can specify different colors, specific icons or data bars with different shadow lengths.
At the same time, Excel 2007 also provides different types of general rules, which makes it easier to create conditional formats. These rules are "Highlight Cell Rules" and "Item Selection Rules". With the highlighted cell rule, you can select the highlighted specified data from the rule area, including identifying the numerical value greater than, less than or equal to the set value, or indicating the date that occurred in a given area. Item selection rules allow users to identify items specified by the maximum or minimum percentage or number in the item, or to specify cells that are larger or smaller than the average value.
Illustration: Highlight Cell Rules
Icons: Item Selection Rules
In addition, conditional formatting in Excel 2007 removes the limit on the number of conditional formatting that can be applied to a cell range. Unlike Excel 2003, which can only recognize three conditions, the number of conditional formats specified in Excel 2007 is only limited by computer memory. In order to help track these massive conditional formatting rules, Excel 2007 provides a conditional formatting rule manager, which can create, edit and delete rules and control the priority of rules.
In addition, Excel 2007 can customize conditional formats and define its own rules or formats.
Second, the new features and functions of Excel tables
The "list" in Excel 2003 is called "table" in Excel 2007, and the function of the table has been improved, which is more convenient for users to create, format and expand.
Excel tables, so as to organize data for analysis in a more appropriate way. The following are some functions of tables in Excel 2007:
You can turn the title on and off.
Automatically expand calculated columns to include added rows.
Each header cell contains an autofilter button for quick sorting and filtering.
Structural references allow users to use column header names and specified items in formulas instead of cell references.
You can use custom formulas and text input to summarize rows.
When rows are added or deleted, the table style will be automatically adjusted and updated.
Third, PivotTable and PivotChart.
Pivottable has long been a powerful analysis tool in Excel. In Excel 2007, the improvement of interface design and the enhancement of functions will be favored by more Excel users.
The ability to undo an operation in a PivotTable report.
Add and subtract drop-down indicators to make it clearer whether the data is expanded or collapsed.
The sorting and filtering of PivotTable data is simpler.
The ability to apply conditional formatting to cells in a PivotChart.
Pivottable Style to Quick Format Pivottable
It is easier to create a pivotchart (at the same time, when the pivotchart changes, the chart format is preserved).
You can apply PivotChart styles.
Easily convert a PivotTable linked to an OLAP library into a formula.
Fourth, classification and screening.
Sorting and filtering in Excel 2007 have enhanced some useful functions. Now, you can sort data by up to 64 levels, and you can also sort by cell color, font color, cell icon, cell value and so on. In fact, the sorting command will change with the sorted data: if the cell contains text, you can choose ascending and descending order; If it is a date, it can be from the earliest date to the latest date or vice versa; If it is a number, it can be from big to small or from small to big.
More complex and dynamic filtering can be performed. For example, for digital data, available filters include equal to, greater than, greater than average, top 10 items, etc. If the selected column contains text, you can select filters, such as beginning with yes, ending with yes and containing. If the filter data contains a date, you can choose tomorrow, this week, last month, next quarter, this year and so on. The list can be automatically filtered up to 10000 items, and can also be filtered by numerical value, cell color, font color or cell icon, and sorting and filtering conditions can be repeatedly applied in the table.
Verb (abbreviation for verb) chart
Like tables and other objects, the chart format in Excel 2007 is based on the theme of the document, and the appearance of the new chart also contains special effects, such as soft edges, shadows, tilt and 3D effects. Another useful function is that you can switch row and column data with one click, and you can save the chart format and layout as a template to apply to future charts.
Another major change is that Excel's chart engine is now also used in Word 2007 and PowerPoint 2007. Excel worksheets can be used as chart data tables in Word and PowerPoint, so these applications can take advantage of the functions provided by Excel, including formulas, filtering, sorting and linking charts to external data sources.
Six, simplify the formula input
Excel 2007 tries to speed up the task of user formula input, especially when it is necessary to write long and complicated formulas.
The formula formula bar has been resized to fit a long formula without overlapping the worksheet or overwriting the title or data. The formula bar can contain about 8000 characters and up to 64 levels of nesting, instead of 1000 characters and 7 levels of nesting in Excel 2003. The number of function parameters has also increased from 30 to 255.
The Formula tab in the ribbon provides a quick reference to the commands needed to create and view formulas, and includes an easy-to-use function library, which shows the main function categories for users to quickly select the required functions. It also shows the automatic sum button, which provides the same function as the previous version. If you want to enter a function using the Insert Function dialog box, you can click the Insert Function button. The calculation option button in the calculation group allows the user to control the calculation of the formula.
Automatic formula completion is a new function, which can help users remember the exact name of the function and input the correct formula grammar. When you start to enter a formula, Excel will display a list of related functions (as well as named areas and table references), and users can use the Tab key or mouse to select the required formula; Then, Excel helps users enter the correct number of parameters, and suggests the name or structure reference of the definition. Tooltips will be displayed for each formula to help users choose the correct usage, without necessarily needing help tools.
Excel 2007 provides a name manager, which can create, view, edit and delete names, thus creating and updating the defined name list more effectively. You can add comments to the definition name as a tool tip in formula autocomplete. In addition, the name box allows resizing to display a longer name.
An important innovation of Excel 2007 is the combination of structured reference function in formulas (that is, you can directly refer to a table or a part of a table by name, instead of cell reference). For example, to summarize all the values of a table named Table 1, you can enter the formula = sum (table1); To summarize all the values in the Sales column of this table, you can enter the formula =SUM(Table 1[Sales]).
Tip: If you want to ignore formula autocomplete, you can turn it off in the Formula tab of Excel options.
Seven, more functions
Excel 2007 extends the function library, including seven new cube functions, which allow information to be extracted from SQL server analysis services. In addition, the analysis toolkit function is integrated into the main function library. Meanwhile, the following general functions have been added.
IFERROR (logic): Identify formula errors instead of using if and ISERROR.
AVERAGEIF: conditionally find the average of a region, similar to SUMIF and COUNTIF.
SUMIFS (Mathematics and Triangle): Similar to SUMIF, but multiple judgment conditions can be used.
COUNTIFS: Similar to COUNIF, but multiple standards can be used.
AVERAGEIF: similar to average if, but multiple criteria can be used.