Opening multiple files at the same time. Relative and absolute cell addresses

After installing a new version of Excel, you can learn how you can continue working with workbooks created in an earlier version of Excel, how you can keep those workbooks available to users who do not have the current version of Excel installed, and how the difference in versions will affect your work.

For backward compatibility with earlier versions of Excel, such as Excel 97-2003, use one of the following workbook methods between different versions.

Working in compatibility mode ... Workbooks created in earlier versions of Excel can be opened in Compatibility Mode. At the same time, the file format is preserved, and you can work with the document again in the previous version of the application. Compatibility Mode is not available for Excel 2007 workbooks.

Checking the book for compatibility ... If you need to transfer the workbook to people using earlier versions of Excel while keeping the current file format, you can check if the data is compatible with previous versions of the application. Based on the results, you can make the necessary changes to the document to avoid loss of information and to ensure that the information is accurate when the file is opened in older versions of Excel.

In compatibility mode

In Excel 2010 and later, when you open a workbook that was created in Excel 97-2003, it automatically opens in Compatibility View and Compatibility Mode appears in square brackets next to the file name in the title bar of the Excel window.

File format when running in compatibility mode

In compatibility mode, any new and improved Excel functionality is not available that prevents data and precision loss when opening a workbook in an earlier version of Excel. Additionally, instead of using the current file format (XLSX, .xlsb, .xlsm, .xltx, xltm;), the workbook is stored in the Excel 97-2003 file format (.xls), a file format that is supported in earlier versions of Excel.

Automatically turn on compatibility mode

Unlike other Office programs, such as Word, you can't manually turn on Compatibility Mode in Excel, and you don't have the option to turn on all the new features when you work in a workbook that's open in Compatibility Mode.

Exiting Compatibility Mode

Open a new Excel workbook with file converters in earlier versions of Excel

Certain updates and converters are available at Office.com to help you open an Excel workbook in an earlier version of Excel (Excel 97-2003). If you haven't installed Updates and Converters yet, when you try to open an Excel workbook, you might be prompted to do so.

After installing updates and converters so that you can edit and save them without having to update the Excel version, you can open workbooks. The new version-specific and formatting features may not appear in earlier versions of Excel, but they are still available when you save the workbook and reopen it in a newer version. Learn more about features and formatting that don't appear in the article.

Downloading and using file converters

On a computer with an earlier version of Excel installed, download the Microsoft Office Compatibility Pack from the Microsoft Office Downloads website and install the updates and converters required to open Excel workbooks.

Open the workbook in earlier versions of Excel (Excel 97-2003).

New features not supported in earlier versions of Excel

Not all new features are supported in earlier versions of Excel. If you are running in Compatibility Mode or you need to save in Excel 97-2003 (.xls) format aworkbook, the Compatibility Checker can help you identify issues that could result in significant loss of functionality or minor loss of accuracy in earlier versions of Excel. To avoid losing data or functionality in an earlier version of Excel, you can then make the necessary changes in the current workbook.

The Compatibility Checker runs automatically when you save a workbook in Excel 97-2003 format. However, to ensure that the workbook is compatible with the current version of Excel, you must run a manual compatibility check the first time you save the workbook. You can then specify that the Compatibility Checker runs automatically every time you save the workbook. For more information on how to run the compatibility check:.

The Compatibility Checker displays the issues it finds, many of which have buttons available To find and reference... After identifying problems, you can find all their manifestations and find out how to fix them. When it runs automatically, the tool also indicates the version of Excel that might be experiencing compatibility issues.

Lack of support for some sheet functions may result in the compatibility issues described below, and may cause significant loss of functionality or minor loss of data accuracy.

	Decision
This workbook contains data in cells outside the range of rows and columns of the selected file format. Data types are more than 65,536 rows in height by 256 columns (IV) and will not be saved. Data reference formulas in this area will return #REF! Mistake.	What does this mean. Starting with Excel 2007, the sheet size is 1,048,576 by16 row-height, by 384 columns, but Excel 97-2003 is only 65,536 rows high, 256 columns wide. In Excel 97-2003, data will be lost in cells outside the range of that row and column. What needs to be done. In the Compatibility Checker, click Search to detect cells and ranges out of range, select them and then place them in the range of columns and rows or on another sheet using the commands To cut and Paste.
	What does this mean. The script on the worksheet refers to a cell outside the row and column limit in Excel 97-2003 (65,536 rows by 256 columns) and is no longer available when you resume saving the workbook in an earlier version of Excel format. What needs to be done. In Script Manager, find a script that contains a link that is outside the range of rows and columns of the previous version of Excel, and then modify it to be within the range. In the tab Data in Group Working with data select items What if analysis and Script manager... In field Scripts find the script that is causing the compatibility issue and change the link.
	What does this mean. Starting with Excel 2007, you can create a custom calendar in international formats such as Hebrew Lunar, Japanese Lunar, Chinese Lunar, Saka, Zodiacal Chinese, Korean Zodiacal, Rokuyou Lunar, and Korean Lunar. However, the following calendar formats are not supported in Excel 97-2003. What needs to be done.
The workbook contains dates in a calendar format not supported by the selected file format. These dates should be changed as dates in the Gregorian calendar.	What does this mean. Starting in Excel 2007, you can use non-Western calendars, such as Thai (Buddhist) or Arabic (Hijri). In Excel 97-2003, these types of calendars can only be replaced by Gregorian. What needs to be done. To avoid loss of functionality, you must change the calendar format to the appropriate language (or locale) that is supported in Excel 97-2003.
The number of data cells in the workbook exceeds that supported in earlier versions of Excel. This workbook will not open in earlier versions of Excel.	What does this mean. Starting in Excel 2007, the total number of blocks (cluster) available to a cell is limited by the amount of available RAM. In Excel 97-2003, the total number of available clusters is limited to 64,000 clusters per Excel instance. The block of cells contains 16 lines of the sheet. If all the rows of a sheet contain data, that sheet will have 4096 blocks of cells, and you can only have 16 such sheets in one instance of Excel (regardless of the number of open workbooks in Excel). What needs to be done. To make sure that the workbook does not exceed the 64000 distribution limit and that it can be opened in Excel 97-2003, it must work in Compatibility Mode after you save the workbook in Excel 97-2003 format. In Compatibility Mode, Excel stores the cluster information of the active workbook.
One or more cells in the workbook contain sparklines. Sparklines will not be saved.	What does this mean. In Excel 97-2007, sparklines that show trends in series of values \u200b\u200bare not displayed on a worksheet. However, all sparklines remain available in the workbook and are applied when the workbook is reopened in Excel 2010 or later. What needs to be done. To findto find the cells with the sparklines and make the necessary changes. For example, you can apply conditional formatting instead of sparklines, or in addition to sparklines that will not appear in previous versions of Excel.
	Decision
Earlier versions of Excel do not support color formatting for text in headers and footers. The text in the headers and footers will be displayed as normal text.	What does this mean. Starting in Excel 2007, you can apply formatting to text, header, and footer color. Unable to use formatting color in headers and footers in Excel 97-2003. What needs to be done. In the compatibility check window, click Fixif you want to remove color formatting.
The workbook contains worksheets that define headers and footers for the first or even pages that will not appear in earlier versions of Excel.	What does this mean. Starting in Excel 2007, you have the ability to display a different header and footer on even pages or on the first page. In Excel 97-2003, the even-numbered page or first page of headers and footers cannot be displayed, but they remain available for display when you open the workbook in Excel 2007 and later again. What needs to be done. If you frequently save workbooks in the Excel 97-2003 file format, it is best not to specify headers and footers for the even-numbered pages or the first page.
Some cells or styles contain formatting that is not supported by the selected file format. These formats will be converted to the closest available format.	What does this mean. Starting in Excel 2007, various formatting and cell style options are available, such as special effects and shadows. These options are not available in Excel 97-2003. What needs to be done. When you save the workbook, Excel uses the closest available format, and as a result, it may match the format of some other object. To avoid duplicating formats, you can modify or delete unsupported cell formats and styles before saving the workbook in the Excel 97-2003 file format.
The number of unique cell formats in this workbook exceeds the allowed for the selected file format. Some cell formats will not be saved.	What does this mean. Starting in Excel 2007, you can use 64,000 unique cell formats, and in Excel 97-2003, up to 4,000. Unique cell formats include any combination of formatting options applied in a workbook. What needs to be done. To avoid losing certain cell formats in Excel 97-2003, you can delete some of the less important ones.
The number of unique font formats in this book exceeds the allowed for the selected file format. Some font formats will not be saved.	What does this mean. Starting in Excel 2007, there are 1,024 global font types available, of which up to 512 can be used in a single workbook. Excel 97-2003 supports fewer unique font formats. What needs to be done. To avoid losing certain font formats in Excel 97-2003, you can remove some of the less important ones.

For more information on how to resolve these compatibility issues, see the articles listed below.

Unsupported Excel spreadsheet functions

Unsupported Conditional Formatting Features

Lack of support for some of the conditional formatting features can lead to the compatibility issues described below, as well as significant loss of functionality.

Significant loss of functionality	Decision
The number of conditional formats in some cells exceeds that supported by the selected file format. In earlier versions of Excel, only the first three conditions will appear.	What does this mean. Starting in Excel 2007, conditional formatting can include up to 64 conditions, but in Excel 97-2003, only the first three conditions will be visible. What needs to be done. In the Compatibility Checker, click To findto find cells that have been conditionally formatted with more than three conditions, and reduce the number of conditions to three.
Some cells belong to multiple ranges of conditional formatting at once. In earlier versions of Excel, not all conditional formatting rules will be applied to such cells. Different conditional formatting will be used for these cells.	What does this mean. Excel 97-2003 does not support overlapping conditional formatting ranges and does not display conditional formatting as expected. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excel 2007 and later if they were changed in Excel 97-2003. What needs to be done. In the Compatibility Checker, click To findto find cells with overlapping conditional formatting ranges, and remove the overlapping ranges.
Some cells in the workbook contain conditional formatting that is not supported by earlier versions of Excel, such as bar charts, color scales, or icon sets.	What does this mean. Excel 97-2003 does not use conditional formatting types such as bar charts, color scales, icon sets, first or last values, above or below average, unique or duplicate values, and table column comparisons to determine the cell format. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excel 2007 and later if they were changed in Excel 97-2003. What needs to be done. In the Compatibility Checker, click To findto find cells with conditional formatting types that were introduced in Excel 2007 and later, and make the necessary changes to use only formatting types that are supported in earlier versions of Excel.
Some cells contain conditional formatting with the Stop if true option unchecked. Earlier versions of Excel do not know this parameter, so execution will stop after the first true condition.	What does this mean. In Excel 97-2003, conditional formatting without stopping when the condition is met is not an option. Conditional formatting is no longer applied after the first true condition. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excel 2007 and later if they were changed in Excel 97-2003. What needs to be done. In the Compatibility Checker, click To findto find cells containing conditional formatting with the option unchecked Stop if true, and press the button Fix to fix a compatibility issue.
One or more cells in this workbook contain conditional formatting for an unrelated range (for example, first / last N, first / last N%, above / below average, or above / below standard deviation). This conditional formatting is not supported by earlier versions of Excel.	What does this mean. In Excel 97-2003, conditional formatting does not appear in non-adjacent cells. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excel 2007 and later if they were changed in Excel 97-2003. What needs to be done. In the Compatibility Checker, click To findto find cells with unrelated range conditional formatting, and make the necessary changes to use the conditional formatting rules available in previous versions of Excel.
Some PivotTables in this workbook contain conditional formatting that may not function correctly in earlier versions of Excel. Conditional formatting rules can produce different results when using PivotTables in earlier versions of Excel.	What does this mean. Conditional formatting results displayed in Excel 97-2003 PivotTable reports will differ from PivotTable reports created in Excel 2007 and later. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excel 2007 and later if they were changed in Excel 97-2003. What needs to be done. In the Compatibility Checker, click To findto locate the PivotTable report fields that contain conditional formatting rules, and apply the conditional formatting rules available in previous versions of Excel.
One or more cells in the workbook contain conditional formatting that uses references to values \u200b\u200bin other sheets. These conditional formats will not be supported in earlier versions of Excel.	What does this mean. Excel 97-2007 does not display conditional formatting with references to values \u200b\u200bin other sheets. What needs to be done. In the Compatibility Checker, click To findto find conditionally formatted cells that reference values \u200b\u200bin other sheets, and apply conditional formatting without referencing values \u200b\u200bin other sheets.
One or more cells in the workbook contain conditional formatting using the Text That Contains format, including the cell address or formula. These conditional formats will not be supported in earlier versions of Excel.	What does this mean. In Excel 97-2007, use formulas for text that contains conditional formatting rules does not appear on the worksheet. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excel 2010 and later if they were changed in Excel 97-2007. What needs to be done. In the Compatibility Checker, click To findto find cells with conditional formatting that use formulas for text with rules, and apply conditional formatting that is supported in previous versions of Excel.
One or more cells in the workbook contains a rule that will not be supported in earlier versions of Excel because there is a formula error in its range.	What does this mean. In Excel 97-2007, conditional formatting that use range-based rules may not display correctly on the worksheet when the range-based rules contain formula errors. What needs to be done. In the Compatibility Checker, click To findto find cells containing range-based rules with formula errors and correct the errors.
One or more cells in the workbook contain a conditional formatting icon set location that is not supported in earlier versions of Excel.	What does this mean. In Excel 97-2003, conditional formatting that displays a specific icon set placement is not supported and the icon set placement does not appear on the worksheet. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excle 2007 and later if they were changed in Excel 97-2003. What needs to be done. In the Compatibility Checker, click To findto find cells with conditional formatting that displays the icon set, and prevent the conditional formatting from displaying the icon set.
One or more cells in the workbook contains a histogram rule that uses the Negative Value option. Such bar charts are not supported in earlier versions of Excel.	What does this mean. In Excel 97-2007, conditional formatting that contains a bar chart rule that uses a negative value is not displayed on the worksheet. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excel 2010 and later if they were changed in Excel 97-2007. What needs to be done. In the Compatibility Checker, click To findto find conditionally formatted cells containing negative histograms due to the fact that the dialog box Creating a formatting rule negative value format is set to Automatically or in a dialog box Setting negative values \u200b\u200band axis for parameter Axis parameters value selected Automatically or Middle of the cell, and then make the desired changes.
One or more cells in the workbook contain conditional formatting that references more than 8192 non-contiguous cell ranges. Such conditional formats will not be saved.	What does this mean. In Excel 97-2007, conditional formatting that references more than 8192 non-contiguous cell ranges does not appear on the worksheet. However, all conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excel 2010 and later if they were changed in Excel 97-2007. What needs to be done. In the Compatibility Checker, click To findto find cells with conditional formatting that reference more than 8192 non-contiguous cell ranges, and change the number of non-contiguous cell ranges that the conditional formatting refers to.
Insignificant loss of accuracy	Decision
One or more cells in the workbook contain a histogram rule that uses the fill, borders, or direction of the histogram. Such bar charts are not supported in earlier versions of Excel.	What does this mean. In Excel 97-2007, conditional formatting that contains a bar chart rule that uses solid color or borders or left-to-right and right-to-left in the row of direction options for bar charts does not appear on the worksheet. However, any conditional formatting rules remain available in the workbook and are applied when the workbook is reopened in Excel 2010 or later if they were changed in Excel 97-2007. What needs to be done. In the Compatibility Checker, click To findto locate the cells containing the conditional formatting histogram rule that uses a solid color fill or border, or left-to-right and right-to-left column settings, and make the necessary changes.

For more information on how to resolve these compatibility issues, see the following article:

Lack of support for some chart functions can result in the compatibility issues described below, as well as significant loss of functionality.

Significant loss of functionality	Decision
The chart contains a title or label for data longer than 255 characters. Characters after 255 are not saved.	What does this mean The maximum length for chart titles or axis labels and data labels in Excel 97-2003 is 255 characters. Data outside this limit is lost. What needs to be done. In the Compatibility Checker, click To findto find headers or labels for data that are longer than 255 characters. Highlight these headers or data labels and modify them to be 255 characters or less.
Some formatting in charts in this workbook is not supported by earlier versions of Excel and will not appear.	What does this mean. Custom shape fills, shape outlines, and shape effects (such as glow and emboss effects or gradient formatting) are not supported in Excel 97-2003 and will not appear. What needs to be done. In the Compatibility Checker, click To findto find unsupported custom formatting elements, and change that formatting to one that is supported in earlier versions of Excel.
Earlier versions of Excel only support line colors from the color palette. When you open this workbook in an earlier version of Excel, all line colors will be replaced with the closest colors from the color palette, so some data series may appear in the same color.	What does this mean. Beginning with Excel 2007, support for up to 16 million colors is provided, but Excel 97-2003 limits the colors to those available in the standard palette. Colors that are not supported will be changed to the closest color on the standard palette, which may be a color that is already in use. What needs to be done. In the Compatibility Checker, click To findto find unsupported colors, and change the formatting of unsupported colors using the standard palette.
The workbook contains charts with more data points than can be displayed in earlier versions of Excel. When you open a workbook in an earlier version of Excel, only the first 32000 data points for the 2D chart series and the first 4000 data points for the 3D chart series will be shown.	What does this mean. Starting in Excel 2010, you can use more than 32,000 data points per row in a 2D chart and more than 4,000 data points in a single row in a 3-D chart. Exceeded the limit on the number of data points per row in Excel 97-2007. What needs to be done. In the Compatibility Checker, click To findto find charts with more data points per series than the limit, and reduce the number of points.

Unsupported functions for working with graphics, objects and ActiveX controls

Lack of support for some of the graphics, object, and ActiveX features can result in the compatibility issues described below, as well as significant loss of functionality.

Significant loss of functionality	Decision
Effects applied to this object will be removed. Text within the borders of the picture will appear clipped.	What does this mean. Starting in Excel 2007, you can use special effects, such as transparent shadows, that are not supported in Excel 97-2003. The special effects will be removed. Additionally, starting in Excel 2007, when you insert text in a shape that is larger than the shape, the text appears outside the shape's bounds. Excel 97-2003 truncates this text. To avoid truncated text, you can adjust the size of the shape to better position it. What needs to be done. In the Compatibility Checker, click To findto find objects with decoration effects applied and remove those effects if necessary.
The object will become uneditable.	What does this mean. Embedded objects created in Excel 2007 and later cannot be edited in Excel 97-2003. What needs to be done. In the Compatibility Checker, click To findto find objects containing text that cannot be changed, and make the necessary changes.
Uninitialized ActiveX controls cannot be saved in the specified file format. These controls will be lost if you continue.	What does this mean. If a workbook contains ActiveX controls that are considered unsafe to initialize, they will be lost if the workbook is saved in an earlier version of Excel format. You might need to mark these controls as safe for initialization. What needs to be done. If you open a workbook with uninitialized ActiveX controls set to high security, you must first enable those controls using the message bar before initializing.
One or more objects in the book, such as shapes, WordArt, or text boxes, allow text to wrap around the object's boundaries. Earlier versions of Excel are not aware of this feature, so text that wraps around borders will be hidden.	What does this mean. Beginning in Excel 2010, you can display text boxes on objects such as shapes and display text outside of these objects. In Excel 97-2007, text that goes beyond the bounds of the object will not appear. What needs to be done. In the Compatibility Checker, click To findto find text boxes with text outside the shape. Make the necessary changes to keep the text within the object, and then turn off the text wrap feature (right-click the shape, choose the Shape Formatand then in the category Inscription uncheck the box allow shape to overflow with text).
Insignificant loss of accuracy	Decision
The workbook contains text boxes with text formatting not available in earlier versions of Excel. Therefore, in earlier versions of Excel, the text in such fields will display differently.	What does this mean. Starting in Excel 2007, you can use text box objects, such as shapes, that display more than one column of text. In Excel 97-2003, the text will be displayed, but in a different format. What needs to be done. In the Compatibility Checker, click To findto find text boxes that contain more than one column, and make the necessary changes so that the text appears in only one column (right-click the shape, choose the menu Shape Formatand then in the category Inscription press the button Loudspeakers).

Unsupported customization features

Lack of support for some customization functions may cause the compatibility issues described below and may result in a minor loss of data accuracy.

Insignificant loss of accuracy

Decision

The workbook contains a customizable quick access toolbar and / or special user interface blocks that are not supported by earlier versions of Excel. These settings will not be available in earlier versions of Excel.

What does this mean. Because the ribbon interface that was introduced in Office 2007 differs significantly from the menus and toolbars in Excel 97-2003, all Quick Access Toolbar customizations made in Excel 2007 and later 060392 are not available in Excel 97-2003.

What needs to be done. In Excel 97-2003, you can add similar custom commands to toolbars and menus.

Reviewing unsupported features

Such unsupported features can lead to compatibility issues as well as a small loss of data accuracy.

Insignificant loss of accuracy

Decision

This workbook will be read-only and the shared workbook functionality will not be available if you open it in an earlier version of Excel using a file converter. For users to use this workbook to collaborate in earlier versions of Excel, you must save it in the format of an earlier version.

What does this mean. If you enable the option Allow multiple users to modify the file ... (Review tab\u003e Workbook access button), select users who with Excel 97-2003 will not be able to use the file if it is in a new formatting file (for example, .xlsx).

What needs to be done. Save the workbook in Excel 97-2003 format, and then work with that workbook in compatibility mode.

Convert a book to XLS format

When you open a workbook that was created in Excel 97-2003 and is no longer scheduled for any user to work with that workbook in an earlier version, you can convert the workbook to the current XML-based file format (XLSX, .xlsb, .xlsm, .xltx, xltm ;). When converting to the current file format, you will have access to all the new and improved features that later versions of Excel offer and the file size will usually be smaller.

You can:

Excel 97-2003 features that are not supported in later versions

When you open a workbook in Excel 2007 or a later version that was created in Excel 97-2003, some functionality in earlier versions of Excel is not supported in the workbook. Unsupported features have either been superseded by new features and functionality, or they will be removed due to being rarely used.

Council. If features are not available in the Ribbon but are still available in Excel, you can still use these features by adding them to the Quick Access Toolbar or the Ribbon.

The following Excel 97-2003 functions behave differently, are no longer available, or have been removed from the ribbon.

Unsupported sheet functions

In Excel 97-2003

You can use the AutoFormat feature to quickly format a range of data using one of the available AutoFormats.

Style libraries for tables, cells and pivot tables are available, containing a range of professional formats that you can quickly apply. You can choose from a variety of ready-made styles or, if necessary, create your own. AutoFormat has been replaced by Styles because it is the simplest way to apply formatting to a range of cells.

You can continue to use the AutoFormat command, but first you need to add it to the Quick Access Toolbar.

Unsupported spreadsheet functions

In Excel 97-2003	In Excel 2007 and later versions
You can create an Excel list to make it easier to manage and analyze groups of related data in a worksheet.	As with other Microsoft Office programs (such as Word and PowerPoint), Excel lists are now called Excel tables.
To quickly add a new row of data to the end of the list, you can use the insert row (a special row in Excel lists).	Row insertion is no longer available. To add new rows to the table, press the Tab key, or type, insert the data you want to include below the table. You can also insert rows to include additional rows of data.
You can specify names for lists.	When you create a table, a specific name is simultaneously created for the same range. This name can be used to refer to a table in formulas that use structured links (new). The names that are used for lists in earlier versions of Excel may not meet the requirements for range names in Excel 2007 and later, and therefore cannot be used to reference a table in formulas that use the new structured referencing function. To use table names in structured references, you must change certain names.
	Bidirectional synchronization of Excel tables with SharePoint lists is no longer supported. When you export tabular data to a SharePoint list, you can only connect to it unidirectionally. Changes made to data from a SharePoint list with One-way data connection to a SharePoint list can be incorporated into Excel 2007 and later. When you refresh the table data in Excel 2007 and later, the latest data from the site will overwrite the table data in the worksheet, including any changes made to the data table. In Excel 2007 and later versions, you no longer update your SharePoint list to include the changes that you made to the data table in Excel after the exported data.

Unsupported Formula Functions

In Excel 97-2003

In Excel 2007 and later versions

The entire row or entire column is automatically referenced to resize cells in Excel 2007 and later. This means that the link \u003d: aThat refers to cells A1: A65536 in earlier versions of Excel refers to cells A1: A1048576 in Excel 2007 and in a later file format.

Converting a workbook in an earlier version of Excel to Excel 2007 and a later file format can cause problems if the workbook uses an entire row or column reference, and data has been entered in the cells that are bey that should not be included in the references. ond is the number of rows and columns in previous versions of Excel.

Also, if certain functions were used to count the number of cells, rows, or columns in a reference (such as COUNTBLANK, ROWS and COLUMN) and they referenced an entire row or column, they may return different results than expected.

In Excel 97-2003, you can define and use some letter and number names in formulas (for example, USA1, FOO100 and MGR4) since they do not conflict with cell references.

With the new 16384 column limit, XFD column expands on columns in Excel 2007 and later. This means that some names (for example, USA1, FOO100and MGR4) that you can determine in earlier versions of Excel will conflict with valid cell references in Excel 2007 and later versions. Additionally, Excel 2007 and later reserves names that begin with XL for internal use.

If incompatible names are found when converting a workbook from an earlier version of Excel to Excel 2007 and a later version of the format file, you will receive a conflict alert. Invalid names are distinguished from cell references, underscores (_) are automatically prefixed with incompatible names.

Excel 2007 and later versions also do not change certain names that are referenced through Microsoft Visual Basic for Applications (VBA). VBA code that references incompatible names will not work and must be updated.

Unsupported chart functions

In Excel 97-2003	In Excel 2007 and later versions
Chart creation.	To create a diagram, you must select the desired type on the ribbon (tab Insert, Group Diagrams). Once the chart is created, you can modify and format it using the tools available on the tab Working with charts.
Multiple charts can be resized at the same time.	Each chart is resized separately.
If an existing chart sheet containing data is selected, you can press F11 to create a chart sheet with the same data.	You can no longer create copies of chart sheets using the F11 key. Selecting a chart sheet and pressing F11 creates a blank sheet.
The rotation angle of the 3-D chart can be changed with the mouse.	The rotation angle of the 3-D chart cannot be changed with the mouse. The amount of rotation of a 3-D chart can only be changed using category options 3-D rotation in the dialog box Chart area format.
You can use a pattern fill for chart elements.	You can use images and textures instead of patterned fills on chart elements. Pattern filled charts created in an earlier version of Excel display the same when opened in Excel 2007 and later, but you cannot use a pattern fill on other elements.
To automatically resize charts along with the window size, you can use the command Fit to window.	Instead of the command Fit to window command available Scale selection.
The text box is created automatically when you click anywhere in the chart and start typing.	You cannot create a label by clicking anywhere in the diagram and starting to enter text. To add a label to the diagram, use the command Add label.
The copied diagram is pasted into a Word document or PowerPoint presentation as a drawing by default.	By default, the copied chart will be pasted into Word 2007 or a later PowrPoint document or a later presentation as an object linked to the Excel chart. You can change how you paste the copied diagram (as a picture or a whole book) in the menu Paste options.
You can use a Word table as data for a new chart.	Tables in Word 2007 or later cannot be used as data for a new chart. When you create a chart in Word 2007 or later, you receive a worksheet with sample data instead of the data in the selected Word table. To display the data in the Word table into a new chart, you must copy the desired data on the worksheet, replacing the existing sample data.
The plot size and scaling options can be adjusted in the dialog box Page settings in the tab Diagram... Tab Diagram appears only when the diagram is selected (menu File, command Page settings).	The plot size and scaling options no longer appear in the dialog box Page settings in the tab Diagram... Tab Diagram appears only when the chart is selected (the Page layout, Group Page settings, dialog box button ).
Data can be visualized by highlighting it on the sheet and dragging it onto the chart.	Data can no longer be dragged from worksheet to chart. There are other ways to add values \u200b\u200bto a chart.
You can drag data points on the chart and change their original values \u200b\u200bon the worksheet.	Dragging data points to change their original values \u200b\u200bon a sheet is no longer supported.
Shapes are displayed in a format other than the shape format that is used in Excel 97-2003.	Shapes that appear in earlier versions of Excel cannot be grouped with shapes that appear in Excel 2007 and later, even if the shapes are updated to the latest version. Unable to select shapes created in different versions of Excel at the same time. Shapes created in different versions of Excel are positioned on top of the current shapes. Like shapes, charts created in Excel 2007 and later cannot be stacked on top of sheets created in earlier versions of Excel, and you cannot view charts below those dialog boxes. To highlight existing charts, use the Chart Element Selector ( Working with charts, tab Format, Group Current snippet). To highlight hidden charts or shapes created in a previous version of Excel, you must first add the command to the Quick Access Toolbar or Custom Ribbon Group Select multiple objects.

For more information about replacing or working around unsupported features, see the following article:

Unsupported PivotTable Features

In Excel 97-2003	In Excel 2007 and later versions
PivotTable reports can be named.	The names used for PivotTable reports in earlier versions of Excel may not meet the requirements for the naming range in Excel 2007 and later, and therefore cannot be used to refer to a table in formulas that use the new structured referencing feature. To use PivotTable report names in structured links, you must change certain names.
Calculated members defined in OLAP cubes appear by default in PivotTable reports.	Instead of calculated members that are defined in OLAP cubes, PivotTable reports display calculated measures (calculated members of the Measures dimension) by default. Calculated members can be displayed by opening in a dialog box Pivot table options tab Conclusion and selecting the item Show calculated components from OLAP server (Working with pivot tables, tab Options, Group Pivot table, Options).
Versions of Microsoft SQL Server Analysis Services earlier than Microsoft SQL Server 2005 Analysis Services are used to support OLAP PivotTable reports.	New filtering capabilities in OLAP PivotTable reports require support for select subqueries on the OLAP server. SQL Server 2005 Analysis Services and SQL Server 2008 Analysis Services support select subqueries, so all filtering options are available in these versions. When you convert a workbook from an earlier version of Excel to Excel 2007 and later versions of the file format, only the filtering capabilities that are available in Excel 2003 PivotTable reports will be available in Excel 2007 and later to PivotTable Reports because earlier versions of SQL Server Analysis Services Services not supported Selection subqueries.
You can use the OLAP Cube Wizard to create OLAP cube files from relational data sources. This capability allows you to implement a hierarchical structure in relational data so that it can be viewed in PivotTable reports and saved in separate files.	The OLAP Cube Wizard is no longer available. To create PivotTable reports from relational data, you can connect to the relational data directly or import it into an Excel workbook.
You can use the PivotTable Wizard to create PivotTable reports, and you can use the PivotChart Wizard to create PivotCharts.	The PivotTable Wizard and PivotChart Wizard are not available in the Microsoft Office Fluent ribbon. Instead, you can use the commands Pivot table and Pivot chartto create a PivotTable report or a PivotChart report in one step (the Insert, Group Tables, button Pivot table). However, the PivotTable and Chart Wizards can be used by adding them to the Quick Access Toolbar or to a custom group on the ribbon. Some of the older features, such as server-defined page fields, memory optimization and explicit pivot table reporting from other reports, and multiple consolidation ranges, are only available in the wizards.
Custom labels and item formatting are lost when fields are collapsed.	PivotTable reports retain item settings (such as custom captions and item formatting) even if those items are temporarily not displayed in the PivotTable report. When you convert a workbook from an earlier version of Excel to Excel 2007 and later versions of the file format, custom signatures and item formatting are applied when the fields are collapsed. Custom signatures are always available in the workbook, even when fields are removed from PivotTable reports and then added again after a while.

For more information on replacing or working around unsupported features, see the articles listed below.

Unsupported file formats

Support for the following file formats has been removed from Office 2007 and later. You will not be able to open or save books in these file formats.

File formats not supported in Excel 2007 and later

WJ1 (1-2-3 Japanese) (WJ1)

WJ3 (1-2-3 Japanese) (WJ3)

WKS (Works Japanese) (WKS)

WJ2 (1-2-3 Japanese) (WJ2)

WJ3, FJ3 (1-2-3 Japanese) (WJ3)

DBF 2 (dBASE II)

WQ1 (Quattro Pro / DOS)

Microsoft Excel Charts (XLC)

Support options have been changed for the formats listed below.

File format	In Excel 2007 and later versions
Web page (HTM, HTML)
Single file web page (MHT, MHTML)	Office 2007 and later versions no longer use Function Information to store a specific Excel in this file format. You can open the file in this format while preserving all Excel-related functions created in earlier versions of Excel. However, you can save such a file (or any other book) in this file format for a publication nature.

Check an Excel workbook for compatibility with earlier versions of Excel

You can run the Compatibility Checker to verify that Excel 2007 or a later version of the workbook does not have compatibility issues that cause significant loss of functionality or minor loss of precision in an earlier version of Excel. The Compatibility Checker returns potential compatibility issues and helps you generate a report so you can fix it.

Important When working with a workbook in compatibility mode where the workbook is in the Excel 97-2003 (.xls) file format instead of the newer Excel 2007 and later versions of the XML-based (XLSX) or binary file format (format), the Compatibility Checker runs automatically while saving the book.

additional information

You can always ask the Excel Tech Community a question, ask for help in the Answers community, or suggest a new feature or improvement on the website.

Alle erweitern | Alle zuklappen

Worin besteht der Unterschied zwischen Office 2010 und Office 365?

Office 2010-Suites enthalten Anwendungen wie Word, Excel, PowerPoint und Outlook, die Sie als Dauerlizenz (einmaliger Kauf) für die Nutzung auf einem PC erwerben können.

Office 365-Pläne umfassen die Premium-Versionen dieser Anwendungen plus weitere Dienste, die über das Internet angeboten werden, darunter der Onlinespeicherdienst OneDrive und Skype-Gesprächsminuten für die private Nutzung. Mit Office 365 erhalten Sie die umfassenden Funktionen einer installierten Office-Version auf PCs, Macs, Tablets (einschließlich iPad® und Android ™ -Tablet) and Smartphones. Office 365-Pläne sind als Monats- oder Jahresabonnement erhältlich. Weitere Informationen.

Wie kann ich feststellen, ob Office 365 auf meinem Computer ausgeführt werden kann?

Ist für Office 365 ein Internetzugang erforderlich?

Für die Nutzung der Office-Anwendungen wie Word, Excel und PowerPoint ist keine Verbindung mit dem Internet erforderlich, da die Anwendungen vollständig auf Ihrem Computer installiert sind.

Sie benötigen jedoch einen Internetzugang, wenn Sie die aktuelle Version der Office-Suites oder einen der Office 365-Abonnementpläne installieren und aktivieren möchten. Bei Office 365-Plänen wird der Internetzugang auch benötigt, um Ihr Abonnementkonto zu verwalten, z. B. zum Installieren von Office auf anderen PCs oder zum Ändern der Abrechnungsoptionen. Auch für den Zugriff auf Dokumente, die auf OneDrive gespeichert sind, ist ein Internetzugang erforderlich, es sei denn, Sie haben die OneDrive-Desktopanwendung installiert.

Sie sollten darüber hinaus auch regelmäßig die Verbindung zum Internet herstellen, um Ihre Office-Version auf dem neuesten Stand zu halten und die Vorteile der automatischen Upgrades zu nutzen. Wenn Sie sich nicht mindestens alle 31 Tage mit dem Internet verbinden, wechseln Ihre Anwendungen in den Modus mit eingeschränkter Funktionalität, was bedeutet, dass Sie Ihre Dokumente zwar anzeigen oder drucken bear, jedoch nichtenbeiten. Darüber hinaus können Sie in diesem Modus auch keine neuen Dokumente erstellen. Um Ihre Office-Anwendungen wieder zu aktivieren, stellen Sie einfach eine Verbindung mit dem Internet her.

Habe ich mit Office 365 die volle Kontrolle über meine Dokumente?

Ja. Von Ihnen erstellte Dokumente bleiben vollständig in Ihrem Besitz. Sie können die Dokumente online auf OneDrive oder lokal auf Ihrem PC oder Mac speichern.

Wenn Sie Ihr Abonnement kündigen oder das Abonnement abläuft, können Sie weiterhin auf alle Ihre Dateien zugreifen oder diese herunterladen, indem Sie sich direkt mit dem Microsoft-Konto, das Sie zum Einrichten von Office 365 verwendet haben, bei. Sie verlieren allerdings den zusätzlichen Speicher, den Ihr Abonnement beinhaltet. Daher müssen Sie Ihre Dateien an einem anderen Ort speichern oder weiteren OneDrive-Speicher erwerben, wenn Ihr OneDrive-Konto die Größe des kostenlos verfügbaren Speichers überschreitet.

Wann beginnt mein Abonnement?

Wenn Sie ein Abonnement mit automatischer Verlängerung erworben haben, beginnt Ihr Abonnement mit dem Kaufabschluss. Abonnements mit automatischer Verlängerung können Sie auf Office365.com, MicrosoftStore.com, iTunes® und bei einigen Fachhändlern erwerben. Wenn Sie ein Prepaid-Abonnement erworben haben, beginnt Ihr Abonnement, sobald Sie es aktiviert haben und die Seite "Mein Konto" angezeigt wird. Prepaid-Abonnements können Sie im Fachhandel oder über einen Microsoft Support-Agenten erwerben.

Wie kann ich Office 365 mit den übrigen Mitgliedern meines Haushalts teilen?

Wenn Sie über ein aktives Office 365 Home-Abonnement verfügen, können Sie die Vorteile Ihres Abonnements mit bis zu vier Mitgliedern Ihres Haushalts teilen. Jedes Mitglied des Haushalts, mit dem Sie das Abonnement teilen, kann jede der für Sie verfügbaren Installationen auf einem PC, Mac, iPad, Android-Tablet, Windows-Tablet, iPhone® oder Android-Smartphone nutzen, erhält 1 TB zusätzlichen OneDrive-Speicher und kann die eigenen Installationen unter www.office.com/myaccount verwalten.

Um Ihrem Abonnement einen weiteren Nutzer hinzuzufügen, rufen Sie www.office.com/myaccount auf, und folgen Sie den Anweisungen auf dem Bildschirm zum Hinzufügen eines Benutzers. Jede Person, die Sie hinzufügen, erhält eine E-Mail mit Anweisungen. Sobald die Einladung akzeptiert und die Schritte in der E-Mail ausgeführt wurden, werden die Informationen der Person, einschließlich der verwendeten Installationen, auf der Seite "Mein Konto" angezeigt. Unter www.office.com/myaccount können Sie das Teilen Ihres Abonnements beenden oder ein Gerät entfernen, das von einem der hinzugefügten Nutzer verwendet wird.

Was ist "die Cloud"?

Der Begriff "Cloud" ist eine gängige Bezeichnung für webbasierte IT-Dienste, die außerhalb Ihres Haushalts / Ihres Unternehmens gehostet werden. Wenn Sie cloudbasierte Dienste verwenden, befindet sich die IT-Infrastruktur außerhalb Ihres Standorts (wird nicht lokal gehostet) und wird von einem Drittanbieter (Host) unterhalten und nicht auf einem selbst verwalteten Server beh Im Fall von Office 365 erfolgen Datenspeicherung und -verarbeitung z. B. remote auf Servern, die von Microsoft gehostet und verwaltet werden und auf denen sich auch die Software befindet. Viele Dienste, die Sie tagtäglich verwenden, befinden sich in der Cloud, ob webbasierte E-Mails, Onlinebanking oder Online-Fotoportale. Da sich diese Infrastruktur online oder "in der Cloud" befindet, können Sie von praktisch überall aus darauf zugreifen - mit einem Desktop-PC, einem Mac, einem Tablet, einem Smartphone oder einem anderen Gerät mit Internetzugang.

This program can be used for the following purposes:

· Accounting. The program has quite powerful functions for calculating financial and accounting statements.

· Budgeting. In this program, you can draw up different types of budget for business and personal needs, for example, you can draw up a marketing budget plan.

· Reports. In this program, you can create reports to analyze and summarize data, for example, reports that measure project performance.

· Planning. The program is an excellent tool for creating professional plans.

· Using calendars. The workspace of the program is grid-like so you can use it to create different types of calendars, such as a fiscal year calendar for keeping track of business events and outstanding business events.

The program window consists of:

· Tab bar (ribbon), which is located at the top of the program window. The ribbon contains certain sets of commands (program functions), which are grouped into commands and groups (by default, the ribbon is displayed containing 7 tabs; when the program is launched, the "Home" tab is active). When moving to new objects of work (formula diagrams), new tabs appear.

· Above the tab bar is the quick access bar, which includes the most used commands (by default, the bar contains a button for saving data, undoing and redoing an action). To the right is an icon, by clicking on which with the left mouse button, you can open a list of additional tasks. There you can select the buttons to be displayed on the Quick Access Toolbar.

· Below the ribbon is the name bar and the formula bar. The name bar displays the name of the active object or cell, and the formula bar displays the formula for the active cell.

Name string.

Formula bar.

· The main part of the window is occupied by the working area.

· In the lower right window of the program there are commands that serve for the most advantageous viewing of the document.

Book in Excel 2010

The book consists of:

3 sheets is the standard, 255 sheets maximum. Each sheet consists of cells, which are formed by the intersection of rows and columns. The lines are numbered with Arabic numerals (1,2,3) and the columns are numbered in capital English letters (A, B, C).

Editing tables in Excel 2010

In order to change the column width or row height, you need to insert between the letters with the column names or numbers with the row names and stretch in the desired direction.

To set a rule for filling cells, you need to enter data in 2 adjacent cells, select them and, using the filling marker, which is in the lower right corner, stretch it to the required amount.

Creating formulas in Excel 2010

The formula begins with the "\u003d" sign, cell addresses are written only in English. In order not to write down the cell address, you can simply select it with the left mouse button when writing a formula.

How to build a function in Excel 2010 (How to make calculations in Excel 2010)

Microsoft Excel 2010 is an updated version of the spreadsheet editor that contains many unique new features, as well as improved tools and features familiar from previous releases. This program is indispensable for users who are often faced with the need to perform complex calculations containing a large amount of data.

You can download Excel 2010 both as a separate application and as a component of the office suite of Microsoft Office 2010. In the second case, you will also receive a set of multifunctional applications for working with text documents, databases, e-mail, notes, etc. It is worth noting that the fact that you can freely exchange data between programs, moving spreadsheets into text documents, sending by e-mail, or vice versa, enter data into the spreadsheet editor by importing them from text files, notes or attachments to e-mails.

Updates and additions in Microsoft Excel 2010

The first thing the user pays attention to after starting the program is the updated Fluent interface. Introduced in Excel 2007, the Ribbon is even more powerful. Now the user can independently select the tools and options that he wants to see on the quick access panel, as well as group them at his discretion. This customizable dashboard allows you to maximize your spreadsheet productivity.
Similar to other products in the Office 2010 suite, Excel has a Microsoft Office Backstage view. This section of file management allows you to perform operations with a file as conveniently and quickly as possible: saving, moving, printing, setting up sharing, setting the necessary document security settings, etc.
function of document recovery in case of incorrect completion of work with it. This allows you to avoid the loss of important information in case you did not have time to save the changes made in the event of any system failures or problems with the device you were working on.
Thanks to the Excel Online function, users can view and edit spreadsheets from SharePoint or OneDrive in the browser, without having to install the program on the device.
A fully functional version of the editor for devices running on Windows Phone 7.
It is convenient to use the updated comparison tool, which provides comprehensive information in pivot tables to identify patterns and trends in data presented in different books. The use of sparklines (charts that fit in one cell) allows you to clearly demonstrate the identified trends.
In addition to refined functions that are even more accurate, Excel 2010 introduces new features to enhance your calculations. Added to the list of functions such values \u200b\u200bas: ranks,% of the sum of rows and columns, cumulative total, etc.
Improved conditional formatting, which tools allow you to highlight specific data. The use of special symbols makes it possible to graphically indicate the processes of decreasing or increasing values, to describe the dynamics of changes. Excel 2010 also updated options for formatting bar charts. In addition to the possibility of plotting values \u200b\u200bin reverse order (from right to left), now histograms displaying negative numbers are plotted on the opposite side relative to the axis of values \u200b\u200babove zero.
Improved search, sorting and filtering algorithm that is not limited to the location of data in specific rows or columns of the spreadsheet. The search bar allows you to quickly find the information you need, even if you do not remember the exact wording or name. Just start typing a search phrase in the line and the program will instantly start displaying matching results.

As you can see, in addition to new features that were not available to users in previous versions, the developers have significantly modified and improved the already familiar tools. Despite the functionality of the editor and its efficiency, each user can download Microsoft Excel 2010 for free, and the system requirements of the application allow you to install it even on devices that do not have high technical characteristics.

Macros can significantly expand the capabilities of Excel. They automate workflows and take over most of the user's routine work. You just need to learn how to use macros and your productivity will increase tenfold!

You don't even need to be a programmer and know the "VBA" programming language to create your own macro programs using the macro recorder tool.

First you need to enable the developer panel. To do this, open the "Parameters" group of options in the "File" menu. In the "Excel Options" window that appears, open the "Customize Ribbon" group. Pay attention to the right column of settings under the same name "Ribbon customization". In it, tick the "Developer" option as shown in the figure below:

Now we have a new tab "Developer" available on the ribbon with all its tools for automating work in Excel and creating macros.

Using VBA and Macros in Microsoft Excel

Macros are internal applications that take care of all the routine work, making life easier for the user. Each user can create a macro without knowledge of programming languages. For this there is a macro recorder, which is launched using the "Record Macro" button.

In this mode, the macro recorder in Excel records all user actions, automatically translating the VBA code into the programming language. After completing the recording, we get a ready-made program that itself performs the actions that the user performed during recording.

How to record a ready-made macro in Excel? Very simple:

Using macro programs, you can increase the user's productivity tenfold. But in order to use the recording of custom macros 100%, you should follow simple rules that significantly affect their quality at the time of recording and efficiency during execution.

How to work with macros in Excel

5 simple tips to help you create macros without coding. Use these simple tips to quickly and easily create high-quality macros automatically:

1 Correct names in macros.

Give macros short but meaningful names. Once you get a taste, over time you will have to create a lot of macros. When selected in the dialog box, they are easier to find with short and meaningful names. The VBA system gives you the ability to provide a description for a name. Be sure to use it.

Macro name must start with letters and cannot contain spaces, symbols or punctuation marks. After the first character, you can use more letters, numbers or underscores, but the maximum name length is 80 characters.

2 Use relative (not absolute) cell addresses

An absolute cell address is the exact location of the cursor when information about its location is written to macro addresses with a hard link to a specific cell at the time of writing. Absolute addresses limit the capabilities of the macro if data is added / deleted on an Excel sheet or the list of data becomes larger. Relative facilities do not bind the cursor to a specific cell address.

By default, Excel is set to Absolute mode, but you can change it by enabling the Relative References button below the Record Macro button on the Developer tab toolbar:

3 Always start recording with the cursor in A1

The absolute counting of cells is always conducted from the starting position (cell address A1) - to the address of the cursor with your data. If you have saved your macro in your personal macro book (it is recommended to do so), then you can use your program on other sheets with similar data. No matter where your cursor is positioned when you start recording a macro! Even if it is already in cell A1, your first macro is best recorded after keystrokes should be Ctrl + Home.

Example: Imagine that every month you receive dozens of spreadsheets from all branches. You are required to organize the data and calculate the metrics in order to produce another monthly report. You can record a macro to perform all of these functions, including opening and connecting all sheets into one combo table.

4 Always navigate with directional keys while recording a macro

Use the arrow keys to control the cursor (Ctrl + Up, etc.). Position the cursor so that you can add, change, or delete data within the table as needed.

Using the mouse for navigation is more difficult and not as reliable at the time of recording. When it comes to macros, it's best to use the mouse only to bring up menus.

5 Create macros for specific small tasks

Keep your macros for small, specific tasks. The more code in a macro, the slower it will run, especially if it is required to perform many functions or calculate many formulas in a large spreadsheet.

If you run each process separately, you can quickly view the results to verify that they are being performed accurately.

If you cannot break a long macro into short applications, but you want to check its functionality step by step ("debugging"). Press the F8 key every time you want to go to the next step in the task. The program execution process stops when it sees an error. You can fix a bug that is easy to find with "debug" or write it in a new way.