Excel Power Pivot: Add Rows to a Table and Refresh

Charts & pivot tables · Updated November 21, 2026

To add a row to a Power Pivot table, add it to the table or other source that feeds the Data Model, then refresh the model in Excel: choose Data > Refresh All. You cannot type a new data row directly into the Power Pivot window; edit the source data instead.

How Power Pivot tables work

Power Pivot is Excel’s tool for building and managing a Data Model: a collection of tables that can be related to one another and used in PivotTables and other reports. The model is separate from the worksheet grid. A table displayed in the Power Pivot window is a model table, not an editable worksheet range, so you cannot click its bottom row and enter another record there.

The route for adding data depends on how that model table was created. A common setup starts with an Excel Table on a worksheet and adds it to the Data Model. Another setup imports data from a database, text file, or other connection, sometimes using Power Query. In either case, the source is where you add or change records. Refreshing brings those changes into the model.

Power Pivot does support calculations, including calculated columns and measures, but those are not substitutes for adding a source record. A calculated column creates values for rows already in the model; a measure summarizes data for a report. Neither creates a new transaction, customer, or other source row.

Add a row when the source is an Excel Table

For a workbook table that is already connected to the Data Model, use these steps in Excel for Microsoft 365 on Windows:

  1. Go to the worksheet containing the source table. Click inside it and confirm that the Table Design tab appears.
  2. Add the record as a new table row. You can type in the first cell immediately below the table if Excel expands the table, or select its last cell and press Tab to create a row at the bottom. If the table has a Total Row, insert a row above it rather than typing into the totals.
  3. Enter values in the appropriate columns. Use the same data types and conventions as the existing records, such as real dates in a date column and consistent category names.
  4. Choose Data > Refresh All. Wait for the refresh to finish before checking the model or report.
  5. Open the PivotTable or report based on the Data Model and refresh it if it does not update as expected. Check that the new record appears in the relevant fields or totals.

If Excel does not expand the table when you type below it, click in the table and choose Table Design > Resize Table. Adjust the displayed range to include the new row, then refresh. You can also use Insert > Table Row Above when inserting a row within the table. Do not add a record merely by typing into a nearby worksheet cell: if that cell is outside the table’s defined range, a model refresh may not include it.

Check that the table is in the Data Model

Refreshing a worksheet table will not put it into Power Pivot if it was never added to the Data Model. To check, select a cell in the source table and look for the Power Pivot tab. If the table is not already in the model, choose Power Pivot > Add to Data Model. In Excel for Microsoft 365 on Windows, the Power Pivot tab may need to be enabled first through File > Options > Add-ins. At the bottom of the dialog, set Manage to COM Add-ins, choose Go, and select Microsoft Power Pivot for Excel if it is listed.

Adding a table to the model is distinct from adding one row. Once the table is in the model, later source-row additions can be brought in by refreshing. If you add a second table rather than adding records to the existing one, you may need to create relationships and update your PivotTable layout; a new model table does not automatically become part of an existing report.

When Power Query or an external source is involved

If the model table is produced by Power Query, edit the upstream source rather than typing into the query’s worksheet output. Query output is refreshed from its source and may be overwritten. For example, if a query reads a CSV file, add the record to the CSV and then refresh the query and model. If the query reads a worksheet table in the same workbook, add the row to that source table instead.

For a database connection, add the record in the database or in the application that manages it, subject to the access and permissions you have. Then refresh the relevant connection or choose Data > Refresh All. If the connection uses a query, filter, or transformation, check that the new record meets those conditions. A row excluded by a query filter will not appear just because the connection refreshed successfully.

Excel for Mac and Excel for the web do not provide the same Power Pivot authoring and management experience as Excel for Windows. If you need to open the Power Pivot window or manage the Data Model directly, use Excel for Microsoft 365 on Windows. A workbook may still contain model-based reports when opened elsewhere, but available editing and refresh options can differ.

Verify the refresh and the report

After refreshing, verify the data at more than one level. First, check that the source table contains the new row and that the table range includes it. Next, open Power Pivot > Manage and inspect the model table, if the Power Pivot window is available. Finally, check the PivotTable or chart that uses the model. These checks help distinguish a source-range problem from a report-layout issue.

A new record may be present in the model without being obvious in a report. A PivotTable can filter out a category, group dates, or show only selected items. A measure may also calculate a total that does not visibly list individual records. Clear relevant filters or inspect the source table before concluding that refresh failed. If the row affects a relationship, make sure its key value matches a corresponding value in the related table; an unmatched key can leave the record out of expected summaries.

Refresh All can update several workbook connections, not just the table you changed. Depending on the workbook, that can take time or require credentials. If you want to refresh a particular connection, inspect the workbook’s query or connection controls and refresh the appropriate source. Avoid repeatedly adding the same record to the source while a refresh is still running; refresh updates the model from the source and is not a command to append a second copy of the last model row.

Common problems and how to fix them

  • The row is missing after refresh: Confirm it is inside the Excel Table or included in the external source range. Then check query filters, connection errors, and whether the model refresh completed.
  • The row appears in the model but not the PivotTable: Refresh the PivotTable, clear its filters, and check whether the field is grouped or the row’s category is hidden.
  • Totals do not change: Confirm the relevant measure or PivotTable is actually using the table you edited. Also check relationships, data types, and any report filters that could exclude the new record.
  • Excel changes the table’s values or formatting: Match the column’s existing type and format. For example, enter a date as a date value, not text that only looks like a date.
  • The new row is overwritten: You may have edited a Power Query output or other imported range rather than its source. Make the change in the upstream table, file, or database and refresh again.

One frequent misconception is that a refresh discovers every new row anywhere on the worksheet. It does not: the row must be part of the defined source table or included by the external connection or query. Another is that Power Pivot’s data view works like an editable Excel grid. It is primarily for viewing and managing model data, not for appending records manually.

Choose the right way to maintain the data

For a small, manually maintained list, an Excel Table is often the simplest source: its structured range can expand as records are added, and the model can be refreshed afterward. For repeatable imports from files or databases, Power Query is usually more suitable because it can apply the same cleanup and transformation steps whenever data is refreshed. For records that must be shared or controlled by multiple users, a database or managed business system may be a better source than a workbook.

Keep one authoritative source for each set of records. Editing both a worksheet copy and a database copy creates uncertainty about which version a refresh will use. Before changing a workbook that contains important model data, save a separate copy. If a report is used by others, tell them when the source changes and whether they need to refresh their own copy.

Frequently Asked Questions

Can I add a row directly in the Power Pivot window?
No. Add or change the record in the source table, query source, or external system, then refresh the Data Model.

What is the fastest way to add a row to an Excel Table?
Enter data in the row directly below the table if it expands automatically, or select the table’s last cell and press Tab. Then refresh the model.

Do I need to refresh after adding a row?
Yes, if the row must appear in the Data Model or a report based on it. Use Data > Refresh All, then check the PivotTable if necessary.

Why does my new row not appear in Power Pivot?
Common causes include a row outside the source table range, an unrefreshed or failed connection, a query filter, or editing a query output instead of its source.

Can I add rows to Power Pivot in Excel for Mac?
Excel for Mac does not provide the Windows Power Pivot management window. Add data at its source, but use Excel for Windows when you need to manage the Data Model with Power Pivot.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.