Chart & Visualization Issues

How to Add Daily Totals and Weekly Averages to an Excel Chart

Steve K Sep 30, 2026 870 views

Question details

The user needs to create an Excel chart that visualizes time-based data by displaying daily event totals, weekly averages, a 24-hour time axis, and specific Sunday gridlines.

How to Add Daily Totals and Weekly Averages to an Excel Chart

Product: Excel
Device & OS: not provided
Scenario: Summarizing raw date and time logs to build an organized chart tracking daily and weekly events with specialized axis formatting.
Observed behavior: The goal is to accurately group date/time data into daily counts, calculate weekly averages, format the axis from 00:00 to 24:00, and emphasize Sundays with bold gridlines.

Before you start

Ensure your raw data is formatted as valid Date/Time values so that Excel's grouping features and PivotTables can accurately interpret and summarize the information.

Solution 1Recommended

Use a PivotTable to Group Daily Data and Generate the Chart

This is the most reliable method for summarizing raw date and time data into daily counts and weekly averages to populate a dynamic chart.

By utilizing a PivotTable, you can automatically aggregate individual event logs into daily summaries without manually writing complex formulas. From there, creating a PivotChart allows for deep customization of axes and gridlines.

Insert a PivotTable

Select your source data range. Go to the 'Insert' tab on the ribbon and click 'PivotTable'. Choose to place it on a New Worksheet and click 'OK'.

Group Data by Days

Drag your date-time field to the 'Rows' area and your event or ID field to the 'Values' area (ensure it is set to 'Count'). Right-click any date in the PivotTable, select 'Group', select only 'Days', and click 'OK'.

Calculate Weekly Averages

To add weekly averages, you can either create a secondary grouping by 7 days or create a separate helper column outside the PivotTable using the AVERAGE function based on the daily counts, then include this helper column in your chart data.

Insert and Format the Chart

Select your summarized data, go to the 'Insert' tab, and choose a Line or Column Chart. To set a 24-hour time axis, right-click the vertical axis, choose 'Format Axis', set the Minimum bound to 0.0 and Maximum to 1.0, and apply a 'hh:mm' format under the Number section.

Add Sunday Gridlines

To add bold gridlines for Sundays, create a secondary data series that only contains values for Sunday dates. Plot this on a secondary axis or add 100% vertical error bars to these specific points, formatting the line as bold.

Use a PivotTable to Group Daily Data and Generate the Chart

Data Format Warning: If the 'Group' option is greyed out when right-clicking dates in your PivotTable, it means some cells in your date column contain text or blank values. Clean the data to ensure all cells are strictly formatted as Dates.

Advanced Data Visualization

Create Advanced Charts and PivotTables with WPS Spreadsheet

WPS Spreadsheet offers powerful, intuitive tools to summarize complex time data. You can easily insert PivotTables, group data by days or weeks, and deeply customize chart axes to display precise daily totals and weekly averages.

1. Insert a PivotTable: Highlight your date and time data, navigate to the 'Insert' tab, and click 'PivotTable'.
2. Group by Date: Drag the date field to Rows, right-click the dates, and select 'Group'. Choose 'Days' to calculate daily event totals.
3. Generate the Chart: Select the PivotTable summary, go to 'Insert', and select your preferred chart type. Right-click the axes to apply custom bounds and time formats.

Easily group date and time logs into days, weeks, or monthsFully compatible with Microsoft Excel (.xlsx) formats, PivotTables, and ChartsAdvanced axis formatting and custom gridline stylingLightweight, fast-loading, and free to use

microsoft office alternative - wps office

Frequently Asked Questions

How do I group dates by weeks in a PivotTable?

Right-click a date cell in your PivotTable and select 'Group'. Deselect 'Months' or any other default options, highlight 'Days', and change the 'Number of days' setting at the bottom of the dialog box to 7.

Why isn't my chart axis showing a 24-hour format correctly?

Time in spreadsheets is calculated as a fraction of a day. To show a 24-hour axis, right-click the axis and click 'Format Axis'. Set the Minimum bound to 0.0 (00:00) and the Maximum bound to 1.0 (24:00). Then, apply a custom Time number format like 'hh:mm'.

Is there a built-in button to highlight Sundays on a chart?

There is no single button for Sunday-only gridlines. The standard workaround is to add a dummy data series that isolates Sunday dates using an IF formula, add it to your chart, and use vertical error bars formatted with a heavier weight or different color to simulate custom gridlines.

Excel & Data Problems