Using the Excel Export / Import utility for multi-state allocation information (1040)

Alerts and notices

The Excel Export / Import utility allows you to quickly and efficiently export and import allocation information to and from UltraTax CS/1040 and a Microsoft Excel workbook.

Note: The Excel Export/Import utility for allocation information is available only in the K1 and K1-T multi-state allocation dialogs.

With the Excel Export / Import utility, you can do the following:

  • Create an Excel workbook template by exporting data from UltraTax CS to Excel.
  • Modify and save the Excel workbook.
  • Import data from the Excel workbook back into UltraTax CS.

You can export a multi-state allocation spreadsheet from one UltraTax CS/1040 client to an Excel workbook and subsequently import the information back into a different UltraTax CS/1040 client, or a different activity in the same client.

You cannot export or import statement dialog detail. When you import allocation data from Excel, total amounts are imported into the UltraTax CS/1040 multi-state allocation spreadsheet. You can then open the statement dialogs and update the statement detail.

Video overview

The following video outlines the exporting and importing of multi-state allocations.

Exporting multi-state allocation information to Excel

Use the Excel Export utility to export allocation information to a Microsoft Excel workbook.

Note: You must export the data to an Excel spreadsheet before you can import the data into the UltraTax CS/1040 Multi-state Allocation dialog.

Exporting guidelines

  • Always start by exporting the multi-state allocation spreadsheet into Excel. UltraTax CS assumes the initial Excel workbook template was generated via the process of exporting the allocation spreadsheet from UltraTax CS.
  • When you export information from UltraTax CS to Excel, all data entered in the UltraTax CS/1040 multi-state allocation spreadsheet is exported to Excel. If the Excel workbook already exists, its data will be overwritten with the data from the UltraTax CS/1040 multi-state allocation spreadsheet.
  • You cannot insert or delete rows in the Excel spreadsheet. Do not add or delete any column heading rows.
  • When you modify data in the Excel spreadsheet, cells that should not be modified are protected. Rows and columns should not be added or deleted. If you need to unprotect the spreadsheet, the password is "password."

    Note: If you unprotect the spreadsheet and then add rows or columns, you may not be able to successfully import the spreadsheet data back into the UltraTax CS/1040 multi-state allocation spreadsheet.

Exporting procedure

  1. Open the UltraTax CS/1040 return that contains the information you want to export to Excel.
  2. Attach all applicable state returns to the return.
  3. Enter a state code in the State field.
  4. Open the K1 or K1-T multi-state allocation dialog that you want to export and click the Excel Export button.
  5. In the Save in field at the top of the Export Excel dialog, enter the path to the desired Excel file location.
  6. In the File name field, enter a file name for the Excel spreadsheet. The default file name is different depending on which spreadsheet option you choose.
  7. Select the desired spreadsheet options.

    One spreadsheet per activity: This option is marked by default and will create an export to Sheet 1 of the spreadsheet named. If the spreadsheet already exists, it will be overwritten by the new export. The default file name for this option is in the form [client ID] - [tax year] - [activity description].xls.

    One spreadsheet for multiple activities: Marking this option will export multiple K-1s to the same spreadsheet. Each activity will create its own worksheet within the spreadsheet. The sheet name defaults to the activity name. The default file name for this option is in the form [client ID] - [tax year].xls

  8. Mark the checkboxes to open the Excel spreadsheet after the export is completed and to enable shading on tab section titles, row descriptions, non-applicable amounts and the federal column cells, as desired.

    Close an existing Excel spreadsheet prior to exporting, otherwise the export process will be aborted.

  9. Click the Export button to begin the export process.
  10. Click OK at the "Export successful" prompt.

Importing multi-state allocation information from Excel

Use the Excel Import utility to import allocation information from a Microsoft Excel workbook to the UltraTax CS/1040 Multi-state Allocation dialog.

You must begin with an Excel spreadsheet that contains information that was previously exported from UltraTax CS/1040 before you can import allocation information.

Importing guidelines

  • Always save any changes you make to the Excel workbook before importing the data into UltraTax CS. This ensures all recent changes will be imported into UltraTax CS.
  • When you import allocation information from the Excel workbook back into UltraTax CS, all data already entered in the UltraTax CS/1040 multi-state allocation spreadsheet is overwritten with the information from the Excel workbook.
  • You cannot insert or delete rows in the Excel spreadsheet. Do not add or delete any column heading rows.
  • When you modify data in the Excel spreadsheet, cells that should not be modified are protected. Rows and columns should not be added or deleted. If you need to unprotect the spreadsheet, the password is "password."

    Note: If you unprotect the spreadsheet and then add rows or columns, you may not be able to successfully import the spreadsheet data back into the UltraTax CS/1040 multi-state allocation spreadsheet.

Importing procedure

  1. Save the Excel workbook before importing it into UltraTax CS/1040 to ensure that all recent changes are imported.
  2. Open the K1 or K1-T multi-state allocation dialog for the activity to which you want to import allocation data from Excel.
  3. Click the Excel Import button.
  4. Click Yes at the "Importing from an Excel spreadsheet will overwrite all amounts currently in the allocation spreadsheet. Do you wish to proceed?" prompt.
  5. In the Look in field at the top of the Import Excel Worksheet dialog, enter the path to the Excel file location.
  6. Enter the Excel file name in the File name field or select it from the drop-down list.

    If the spreadsheet was exported as one spreadsheet for multiple activities, select the sheet name in the Sheet name drop-down list.

  7. Click the Import button.

    Note: If the color of the Statement dialog Purple statement dialog button indicating amounts are different button is purple, then the amount imported from Excel does not equal the amount within the statement dialog. Open the statement dialog and update the detail.

  8. Click OK at the "Export successful" prompt.