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:
- Go to the worksheet containing the source table. Click inside it and confirm that the Table Design tab appears.
- 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.
- 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.
- Choose Data > Refresh All. Wait for the refresh to finish before checking the model or report.
- 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
- HSA Expense Tracker Spreadsheet: Build One in Excel
- PowerPoint Timeline: How to Make One That Looks Polished
- How to Make a Nice Poster in PowerPoint: A Practical Guide
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Radar Chart in Excel: How to Create and Format OneTo make a radar chart in Excel for Microsoft 365 on Windows, arrange categories in the first column…
- Excel Pivot Table: Filter Values Greater Than a NumberTo show PivotTable items whose summarized value exceeds a threshold, open the drop-down for the row or column…
- Excel Pivot Chart Trendline: What Works and What Does NotYou cannot add a trendline directly to a PivotChart in Excel for Microsoft 365 on Windows. To show…
- Excel Pivot Table Exercises: Guided Practice Tasks and AnswersExcel PivotTable exercises help you practise summarizing and exploring data without writing formulas. Start with the sample sales…
- UK Balance of Payments Graph: How to Read ItAn authoritative UK balance of payments graph is available from the Office for National Statistics (ONS), in its…
- Excel Pivot Table Dates: How to Group and Sort by WeekTo sort PivotTable dates by seven-day periods in Excel for Microsoft 365 on Windows, right-click a date in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.