When working with time-based data in Excel, grouping entries into 15-minute intervals is a smart way to spot patterns. This is especially useful if you’re tracking things like restaurant traffic, production steps, or website logins. Hourly summaries can miss those quick bursts of activity, but quarter-hour grouping doesn’t.
👉 In this article, we’ll take the example of a restaurant analyzing the number of entrances during lunchtime.
Step 1 – Log the Entry Time for each client
You can add the entry time by filling each cell manually, or you can use the shortcut: Ctrl + : (colon). This key combination insert the current time in your cell.
Step 2 – Round Time to Nearest 15 Minutes
Excel has many functions to round numbers. To round Time to 15-minute intervals, the best function is FLOOR (or FLOOR.MATH)
The second argument is significance (or multiplication by a number). So, we will write the following formula.
Step 3 – Grouping Data by Time Intervals
The next step is to group your data by 15-Minute intervals. According to your Excel version, this task could be done with a single function, or more than one.
GROUPBY function – Only Excel 365
GROUPBY is a recently introduced function in Excel, currently exclusive to Excel 365 and Excel Online. It offers a unified solution for grouping and summarizing data within a single formula.
- Automatic grouping of unique values (e.g., time slots)
- Aggregation via SUM, COUNT, AVERAGE, etc.
- Optional output customization (e.g., headers, totals, sorting)
- … And more. Have a look at this article to lean more about GROUPBY
Analysis with the other Excel version
If you don’t have GROUPBY, you can still analyze your data, but this time, you must create a formula for each row of your document.
- Extract each 15-minute interval in a cell
- You can do it manually 🤯
- Or use the UNIQUE function with Excel 2019 or later
- Then use COUNTIF to count how many times each time block shows up
While this approach yields the same results, it requires manually writing formulas for each summary row.
Step 3 – Insert a Bar Chart to visualize your data
The best practice is to complete your analysis with a chart. Below is a standard bar chart. A bar chart provides a clear view of peak activity and helps communicate insights quickly.
- Select your summary table with time intervals
- Go to Insert > Bar Chart > Clustered Column
- Customize the labels and axis to match your time bins
