Nothing kills a finely tuned Excel workbook faster than someone wondering whether the data is up to date. Instead of manually typing dates, you can make Power Query do the heavy lifting.

Imagine you’re managing a workbook that combines tables from multiple files. Even if everything is set up correctly, you still need to know when the data was last refreshed. A visible “Last Updated” timestamp removes that uncertainty and helps everyone trust the numbers they’re working with. Depending on your setup, you might want a single report-level timestamp or a more granular one for individual tables. Here’s how to create both.

Creating a report-level timestamp in Excel

Building a standalone “Refresh All” tracker

If you generally update all your data at once, a standalone “Last Refreshed” box is the cleanest solution. This is ideal for a README or dashboard worksheet that stores all the information about the workbook.

This method creates a dedicated query that updates whenever you click Data > Refresh All.

Here’s what you need to do:

1. In Excel, click Get Data in the Data tab.
2. Hover over From Other Sources.
3. Select Blank Query.

Now, in the Power Query Editor:

1. In the Query Settings pane on the right, change the Name to something descriptive like Manual_Refresh_Timestamp.
2. In the formula bar, type = DateTime.LocalNow() and press Enter. This adds the date and time to the query output.
3. In the rightmost Transform tab, click To Table > To Table.
4. Double-click the column header and rename it Last Manual Workbook Refresh.
5. Click the ABC123 icon in the column header and choose Date/Time.

At this point, you have a functional clock sitting in the Power Query Editor. Just keep in mind that this uses the system time of the machine performing the refresh, which can matter if multiple people in different locations are updating the workbook.

To finish the process, you need to load it to your dashboard:

1. In the Home tab, click Close & Load > Close & Load To….
2. In the Import Data dialog, select Table and Existing Worksheet.
3. Click the collapse icon (the up arrow), select the specific cell on your dashboard where you want the timestamp to live, and press Enter.
4. Click OK.

Now, you have a general status indicator for your file. All that’s left to do is format it so that it looks the part. For example, remove the filter buttons and choose a different table style in the Table Design tab, apply any direct formatting like changing the font size or applying bold in the Home tab, and add seconds to the time via the Format Cells dialog (Ctrl+1).

It’s important to note that if an individual query refreshes separately, this workbook-level timestamp can become outdated. It only updates when you click Refresh All in the Data tab or when the specific query tied to this cell is executed. To address this limitation, the next section shows how to create a table-level timestamp in Power Query.

Inserting independent Power Query table refresh timestamps

Using the hidden column failsafe

Because the report-level timestamp can be bypassed by individual table updates, it’s helpful to have a more granular failsafe for individual queries.

You might be tempted to try other methods to fix this, such as creating a separate reference query or appending a timestamp to the bottom of your loaded dataset. However, while reference queries are tied to the original query, they don’t provide an independent timestamp. And appending a timestamp to the bottom of your table will only confuse Excel when you sort the data or use dynamic array formulas to pull information from it.

For these reasons, building a timestamp directly into the data via a custom column is the most secure approach:

1. In Excel, open the Data tab and click Queries & Connections.
2. In the sidebar, right-click your main query and click Edit.
3. In the Power Query Editor, click Add Column > Custom Column.
4. Name the new column Query Last Updated and paste the following into the Custom column formula field: = DateTime.LocalNow().
5. When you click OK, a new Query Last Updated column is added to the table. Although the timestamp appears on every row, Power Query evaluates it once per refresh—not once per row. Click the ABC123 icon in the column header and choose Date/Time.
6. Click the top half of the Close & Load button in the Home tab to see the updated query in its existing location in your workbook.

As it stands, your table looks untidy with the date and time repeated on each row, so to polish things off, you need to create a linked cell that references the table’s data while keeping the source column hidden:

1. If you want to make your time more specific with seconds, create a Custom Format in the Format Cells dialog (Ctrl+1).
2. Reduce the width of the column immediately to the right of your query output to create a single buffer column.
3. In a cell in the next column, type =, then select the header and the first data cell of your Query Last Updated column so that both cell references are added to your formula.
4. When you press Enter, the data is duplicated in those two cells, but the date-time cell might lose formatting. So, select a date-time cell from your table, and use the Format Painter to duplicate the formatting to your independent duplicated cell.
5. Finally, right-click the header of the Query Last Updated column and click Hide.

After applying any manual formatting (such as adding borders and center-aligning your text), you have a professional table-specific refresh timestamp that updates when:

1. You click Data > Refresh All.
2. You right-click the table and click Refresh.
3. The query refreshes automatically based on a scheduled interval you’ve configured.

By using this “passenger” column approach, you ensure the timestamp stays in sync with both the query’s last successful refresh and any workbook-wide data refreshes. If the table updates, the column updates, and if the column updates, your Query Last Updated cell reflects it instantly.

Automating your “Last Updated” timestamp keeps your Excel reports transparent and trustworthy without manual effort. It’s a small change that significantly improves usability while keeping your sheets clean. Did you realize that you used M language in this process? It’s the powerful engine behind Power Query, and it’s surprisingly approachable once you know the basics. Take a few moments to explore other M language snippets that can automate your workbook in ways standard formulas can’t.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.