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

Show expandable text

New (tax) year, new help!

Fixed Assets and UltraTax CS 2023 help is now on Help and Support. We're still moving articles, but you can find most content for the 2023 tax year there. Continue using the Help & How-To Center for tax years 2022 and older.

The Excel Export / Import utility allows you to quickly and efficiently export and import allocation information to and from the multi-state allocation spreadsheet 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 spreadsheets.

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

  1. Create an Excel workbook template by exporting data from the multi-state allocation spreadsheet to Excel.
  2. Modify and save the Excel workbook.
  3. Import data from the Excel workbook back into the multi-state allocation spreadsheet.

You can export a multi-state allocation spreadsheet from one client to an Excel workbook and subsequently import the information back into a different 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 multi-state allocation spreadsheet.

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/1041 Multi-state Allocation dialog.

Exporting guidelines

  • Always start by exporting the multi-state allocation spreadsheet into Excel. 
  • When you export information to Excel, all data entered in the multi-state allocation spreadsheet is exported to Excel. If the Excel workbook already exists, its data will be overwritten with the data from the 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 multi-state allocation spreadsheet.

Exporting procedure

  1. Open the return that contains the information you want to export to Excel.
  2. Attach all applicable state returns to the return.
  3. Open the K1 or K1-T multi-state allocation dialog that you want to export and click the Excel Export button.
  4. In the Save in field at the top of the Export Excel dialog, enter the path to the desired Excel file location.
  5. 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.
  6. 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.
  7. 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.

    Note: You must close an existing Excel spreadsheet prior to exporting, otherwise UltraTax CS will abort the export process.

  8. Click the Export button to begin the export process.
  9. Click OK in 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 multi-state allocation spreadsheet.

You must begin with an Excel spreadsheet containing information that was previously exported from the multi-state allocation spreadsheet before you can import allocation information.

Importing guidelines

  • Always save any changes you make to the Excel workbook before importing the data to ensure all recent changes will be imported.
  • When you import allocation information from the Excel workbook back into the multi-state allocation spreadsheet, all data already entered in the 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 modifying 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 modify it by adding rows or columns, you may not be able to successfully import the spreadsheet data back into the multi-state allocation spreadsheet.

Importing procedure

  1. Save the Excel workbook before importing it to ensure that all recent changes are imported.
  2. Open the K1 or K1-T multi-state allocation spreadsheet 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.

Was this article helpful?

Thank you for the feedback!