How to Create a Timer Chart in Excel

Create a countdown timer chart in Excel by putting the duration, elapsed time, and remaining time in worksheet cells, then charting elapsed versus remaining time as a doughnut. To make it update automatically every second, add the optional VBA timer below; without VBA, recalculate the sheet manually. You will need Excel for Microsoft 365 on Windows, a workbook you can save as a macro-enabled file if using VBA, and permission to run macros.

What You’ll Need

Excel for Microsoft 365 on Windows. The formulas and chart work in Excel for Mac too; the VBA editor and ribbon paths differ slightly, as noted below.
A blank worksheet, which you will name Timer, and a duration in seconds. For example, five minutes is 300 seconds.
For automatic one-second updates, macros enabled and a workbook saved as an Excel Macro-Enabled Workbook (.xlsm). If you do not want to use VBA, you can still build the chart, but Excel will not refresh it continuously on its own.

Step-by-Step

Set up the timer cells. Start on a blank worksheet and rename its tab Timer, since the optional macro uses that name. Enter these labels and values: A2, Duration (seconds); B2, 300; A3, Started at; leave B3 empty; A4, Elapsed (seconds); A5, Remaining (seconds); and A6, Time left. Change B2 to the number of seconds you want. For a 10-minute timer, enter 600. Use a positive whole number to keep the chart easy to interpret.
Calculate elapsed time. With the start time in B3 and duration in B2, enter this in B4: =IF($B$3="",0,MIN($B$2,MAX(0,INT((NOW()-$B$3)*86400)))). The formula converts the difference between the current time and the start time into seconds, rounds down to a whole second, and caps the result at the timer duration. Before you start the timer, it returns zero rather than trying to calculate from an empty start cell.
Calculate the remaining time. With duration in B2 and elapsed seconds in B4, enter this in B5: =MAX(0,$B$2-$B$4). For the example duration of 300 seconds, the timer starts with 300 seconds remaining. When elapsed time reaches 300, remaining time is zero and cannot become negative.
Show a readable clock value. With remaining seconds in B5, enter this in B6: =IF($B$3="","Ready",TEXT($B$5/86400,"[m]:ss")). Excel stores times as fractions of a day, so dividing seconds by 86,400 converts them for the display. The format [m]:ss shows total minutes and seconds, including durations longer than an hour without resetting the minute count. Widen column B if the result is hard to read.
Create a small chart data table. In A8 enter Status, in B8 enter Seconds, in A9 enter Elapsed, and in A10 enter Remaining. With elapsed time in B4, enter =B4 in B9. With remaining time in B5, enter =B5 in B10. These links keep the chart source connected to the timer calculations. At the start, the two chart values are 0 elapsed and 300 remaining for a five-minute timer.
Insert the doughnut chart. Select A8:B10, then choose Insert > Insert Pie or Doughnut Chart > Doughnut. Excel creates a chart with one segment for elapsed time and one for time left. Give it a clear title such as Timer progress. The chart shows the share of the total duration that has elapsed; it is not a clock face and does not display a precise time by itself.
Make the chart easy to read. Select the chart and use Chart Design > Add Chart Element to add a legend or data labels if they help your audience. Choose contrasting fills for the elapsed and remaining segments, and remove any unnecessary border or chart background. Select the chart series and use Format Data Series to adjust the doughnut hole size. Keep the data table and the large B6 time readout near the chart, so viewers can see both the progress and the exact time remaining.
Test the formulas before adding automation. Temporarily enter a start time in B3, such as the current time, and press F9 to recalculate. B4 should rise from zero as time passes, B5 should fall, and the chart should change. Pressing F9 recalculates formulas in a workbook; on a Mac, use Fn+F9 if the function keys control hardware features. Clear B3 again before using the start macro. A manual test confirms the cell references and chart source are correct.
Add automatic updates with VBA (optional). Save the workbook as Excel Macro-Enabled Workbook (*.xlsm). On Windows, press Alt+F11 to open the Visual Basic Editor. On Mac, use Option+F11 (you may need to hold Fn) or open the editor from the Developer tab. In the editor, choose Insert > Module, then paste the code below into the module. It expects the worksheet to be named Timer and the duration to be in B2. The start button resets the timer; the stop button cancels future updates but does not pause the countdown.

Option Explicit

Public nextRun As Date

Public Sub StartTimer()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Timer")

    If Not IsNumeric(ws.Range("B2").Value) Or ws.Range("B2").Value <= 0 Then
        MsgBox "Enter a positive duration in seconds in B2."
        Exit Sub
    End If

    ws.Range("B3").Value = Now
    Application.Calculate
    nextRun = Now + TimeSerial(0, 0, 1)
    Application.OnTime EarliestTime:=nextRun, Procedure:="TimerTick"
End Sub

Public Sub TimerTick()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Timer")

    Application.Calculate
    If ws.Range("B5").Value <= 0 Then Exit Sub

    nextRun = Now + TimeSerial(0, 0, 1)
    Application.OnTime EarliestTime:=nextRun, Procedure:="TimerTick"
End Sub

Public Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=nextRun, Procedure:="TimerTick", Schedule:=False
    On Error GoTo 0
End Sub

Run and control the timer. Return to Excel, enable macros if prompted, then press Alt+F8 on Windows to open the Macro dialog. Select StartTimer and choose Run. To stop scheduled updates, open the dialog again and run StopTimer. Mac users can open the Macro dialog from the Developer tab or use Option+F8 where that shortcut is available. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z. If you change B2, run StartTimer again to begin a fresh countdown.

Common Mistakes

Expecting NOW to update by itself: Excel recalculates NOW when calculation occurs; it is not a continuously running clock. Without the macro, press F9 to refresh. With the macro, check that macros are enabled and that StartTimer ran successfully.

Entering minutes where seconds are expected: B2 is a number of seconds, not minutes. Enter 5 for five seconds, 60 for one minute, or 300 for five minutes. If you enter 5 intending five minutes, the timer will finish in five seconds.

Using a pie chart instead of a doughnut: A pie chart can display the two values, but it does not leave a center for a prominent timer readout. A doughnut is usually clearer for a progress indicator. Keep the B6 display visible because the chart alone does not communicate the exact countdown time.

Overwriting formulas or changing the layout: The formulas and macro rely on specific cells. If you move the duration from B2, start time from B3, or remaining time from B5, update the references in both the worksheet formulas and the VBA. An accidental value pasted over B4 or B5 can also stop the chart from changing.

Saving in the wrong file format: An .xlsx file cannot preserve VBA macros. Save as .xlsm if you want automatic updates. If you choose not to keep macros, the formulas and chart remain usable, but updates will require recalculation.

Tips

Use a separate copy of the worksheet for each timer if you need different durations, and adjust the macro to name the intended sheet before running it. For a classroom activity or presentation, enlarge B6, center its text, and place it next to the doughnut chart. A simple visual convention helps: one color for elapsed time and a muted color for time remaining.

For a one-time visual that does not need to run live, you can skip VBA entirely. Enter a start time in B3 and press F9 as needed. This approach is convenient when macros are blocked by workplace policy or when you only need to demonstrate how the chart responds. It is not suitable when the timer must update unattended.

The VBA schedule aims for updates about once a second, but Excel may run late if the computer is busy, a dialog is open, or the workbook is not active. Treat the displayed countdown as a visual aid rather than a precision timing device. For a timed test, public event, or process where exact timing matters, use a dedicated timer and follow the relevant procedures.

Keep the source table near the chart while building it. If you later hide the table, verify that the chart still displays its values; chart behavior can depend on how the source data is configured. You can also place the chart and timer display in a clean presentation area while leaving calculation cells together elsewhere on the same sheet.

Troubleshooting

The chart stays empty or does not move: Confirm that B9 links to B4 and B10 links to B5, and that the chart was created from A8:B10. Check that calculation is set to automatic in Formulas > Calculation Options > Automatic. If you are not using the macro, press F9. If you are, run StartTimer and allow macros when opening the workbook.

B6 shows Ready after starting: Check that StartTimer wrote a date and time into B3. If B3 is blank, the display formula intentionally returns Ready. Also confirm the worksheet tab is named Timer; the VBA will not find a sheet with a different name.

The timer finishes immediately: Inspect B2 and make sure it contains the intended duration in seconds, not a time-of-day value or a formula returning a small number. For five minutes, B2 should be 300. Then run StartTimer again, since starting resets the B3 timestamp.

The macro reports that it cannot find a worksheet: Rename the worksheet tab to Timer, or edit the sheet name in the VBA code to match the tab exactly. If Excel says macros are disabled, check the workbook’s security prompt or your organization’s macro policy; do not enable macros in files from untrusted sources.

The display shows a strange time or a number: Confirm the B6 formula is present and that B5 contains seconds. The formula converts seconds to Excel time for display. If you changed the cell layout, restore the stated references or update the formula accordingly.

Frequently Asked Questions

Can Excel make a timer chart without VBA?
Yes. The formulas and doughnut chart work without VBA. The chart updates when Excel recalculates, so press F9 when you want a refresh. For regular one-second refreshes, the optional macro schedules recalculation.

Does the doughnut chart show the exact time remaining?
No. It visualizes elapsed and remaining portions of the duration. Use the B6 text display for the exact minutes-and-seconds countdown.

Can I use a different timer length?
Yes. Enter the duration in seconds in B2, then run StartTimer to reset the start time. For example, enter 90 for one minute and 30 seconds.

Will the countdown keep running if I close Excel?
No. The scheduled VBA updates depend on Excel being open and able to run macros. Closing the workbook or Excel stops the updates; opening the file again does not automatically resume the old countdown.

Can I use this in Excel for the web?
You can view a workbook and its chart in Excel for the web, but VBA macros do not run there. Use desktop Excel for the automatic macro timer, or use manual recalculation in a desktop workbook for a non-automated version.

Can I pause and resume the timer?
The supplied StopTimer macro cancels scheduled refreshes; it does not pause and preserve the remaining time. Running StartTimer begins a new countdown from the full duration in B2. A true pause-and-resume control needs additional VBA to store the remaining time and restart from it.

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.