WPS Spreadsheets: Create Line and Bar Charts from Excel Cache Data
Question details
The user needs to create comparable line and bar charts from cache data, with each cache size displayed as a separate data series.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The dataset includes Block Size, Hit Ratio, and Cache Size, and the user wants Block Size on the horizontal axis while comparing cache sizes.
- Observed behavior
- The chart may incorrectly treat Block Size as a data series instead of using it as the horizontal-axis labels, making the cache-size comparisons unclear.
Check whether multiple rows contain the same Block Size and Cache Size combination. Decide whether those Hit Ratio values should be summed, averaged, or otherwise summarized before creating the chart.
Arrange cache data into separate series columns before inserting a chart
Use this approach when each Block Size and Cache Size combination can be placed in a clear summary table.
The chart source should contain one horizontal-axis column for Block Size and one value column for each Cache Size. If the source has repeated combinations, summarize them first with a PivotTable or formulas such as SUMIFS.
In WPS Spreadsheets, create a table with Block Size in the first column, each Cache Size as a separate header column, and the corresponding Hit Ratio values beneath each cache-size header.
If combinations repeat, choose a blank result cell and use a formula such as =SUMIFS(Hit_Ratio,Block_Size,A2,Cache_Size,B$1), adjusting the named ranges and cell references to match your worksheet.
Select the complete summary table, including the Block Size header, cache-size headers, and Hit Ratio values.
Open the Insert tab, choose Chart, and select Line or Bar. WPS Spreadsheets creates one series for each Cache Size column.
Select the chart, open the chart data or Select Data controls, remove Block Size from the series list if it appears there, and assign the Block Size column to the horizontal-axis labels.
Build the comparison with a PivotTable and PivotChart
Use this option when the source contains many repeated Block Size and Cache Size combinations and you want WPS to summarize them automatically.
Create Accurate Cache-Size Comparison Charts in WPS Spreadsheets
WPS Spreadsheets lets you summarize repeated cache records, arrange each Cache Size as a separate series, and insert line or bar charts with Block Size correctly assigned to the horizontal axis.
- 1. Open the workbook: Launch WPS Spreadsheets and open the Excel workbook containing Block Size, Hit Ratio, and Cache Size.
- 2. Summarize the data: Use Insert > PivotTable or a SUMIFS formula to create one value for each Block Size and Cache Size combination.
- 3. Arrange the series: Place Block Size in the first column and each Cache Size in its own column with Hit Ratio values below the headers.
- 4. Insert the chart: Select the summary table, choose Insert > Chart, and select a Line or Bar chart.
- 5. Fix chart data assignments: Use the chart's Select Data controls to remove Block Size from the series list and set it as the horizontal-axis labels.
Frequently Asked Questions
How should the source table be arranged for a cache-size comparison chart?
Put Block Size in the first column and create one additional column for each Cache Size. Use the Hit Ratio values in those cache-size columns, with the cache sizes as the column headers.
Why does the chart show Block Size as a separate series?
The chart has interpreted the first column as numeric data instead of category labels. Select the chart, open Select Data, remove the Block Size series, and assign that column to the horizontal-axis labels.
How can repeated Block Size and Cache Size rows be summarized?
Create a PivotTable with Block Size in Rows, Cache Size in Columns, and Hit Ratio in Values. You can also use SUMIFS or another aggregation formula when a formula-based summary is preferred.
Should I use a line chart or a bar chart for this data?
Use a line chart when you want to show how Hit Ratio changes across ordered Block Sizes. Use a bar chart when you want to compare individual Block Size and Cache Size values more directly.
Can WPS Spreadsheets open an Excel workbook containing this chart data?
Yes. WPS Spreadsheets supports Microsoft Excel XLSX files. Open the workbook, organize or summarize the data, and save the finished chart in the required Excel-compatible format.
