How to Create Excel Charts for Calls by Type and Month
Question details
The user wants to create interactive charts to visualize call data categorized by type and time period (month or quarter), including the ability to filter the charts by date.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing and visualizing categorized time-series data, such as police calls, for interactive reporting and dashboards.
- Observed behavior
- Needs a method to summarize raw source data into interactive visual reports with date filters that update dynamically.
Ensure your source data is organized in a tabular format with clear column headers (e.g., Date, Call Type, Number of Calls) and contains no empty rows or columns.
Create Interactive PivotCharts with Slicers
Use PivotTables to summarize your data by category and time, then insert PivotCharts and a Slicer to make the visualization interactive.
PivotTables are the most efficient way to aggregate large datasets without writing complex formulas. By creating separate PivotTables for 'Call Type' and 'Month/Quarter', you can generate independent charts that are controlled by a single master date filter.
Select your source data range, go to the 'Insert' tab, and click 'PivotTable'. Place 'Call Type' in the Rows area and 'Call ID' or 'Count' in the Values area to summarize the total calls per type.
Create another PivotTable from the same data source. Drag the 'Date' field into the Rows area (right-click the dates and select 'Group' to group them by Months or Quarters) and 'Count' into the Values area.
Click anywhere inside the first PivotTable, go to the 'Insert' tab, and select 'PivotChart' (e.g., a Bar or Pie chart). Repeat this process for the second PivotTable, choosing a Line or Column chart to show trends over time.
Click on one of the PivotCharts, navigate to the PivotChart Analyze tab, and click 'Insert Slicer'. Check the 'Date' field. To make the slicer control both charts, right-click the slicer, select 'Report Connections', and check the boxes for both PivotTables.
Create Dynamic PivotCharts Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful data analysis tools, including PivotTables, PivotCharts, and Slicers, allowing you to build interactive dashboards and reports in minutes.
- 1. Import Data: Open your raw dataset in WPS Spreadsheet.
- 2. Summarize with PivotTable: Go to the 'Insert' tab and click 'PivotTable' to group your data by type and month.
- 3. Visualize: Select 'PivotChart' to visually represent your summarized data with column or line charts.
- 4. Add Filters: Click 'Insert Slicer' under the PivotTable options to add interactive date filters.
Frequently Asked Questions
Can I group my daily dates into months or quarters automatically?
Yes, in a PivotTable, right-click any date in the row labels and select 'Group'. A dialog box will appear where you can select to group by Months, Quarters, or Years.
Why is the slicer not updating my second chart?
The slicer must be explicitly linked to all PivotTables that drive your charts. Right-click the slicer, choose 'Report Connections', and ensure the checkboxes for both PivotTables are selected.
What chart type is best for showing data by month?
A Line chart or a Column chart is typically best for showing trends over time, making it easy to identify spikes or drops in call volume across different months or quarters.
