How to Create an Excel Trendline Without Blank Temperature Values in WPS Spreadsheets
Question details
The user needs to create a temperature trendline in WPS Spreadsheets while excluding dates that have empty temperature cells so the trend is based only on complete records.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Irregular temperature readings are stored with dates in adjacent columns, and the user wants to calculate or chart a trend only for dates that contain a temperature value.
- Observed behavior
- Blank temperature cells can distort trend calculations or appear as unwanted gaps in charts unless incomplete records are removed or filtered first.
Confirm that your workbook contains dates in one column and temperature values in the adjacent column. Check whether blank temperature cells are truly empty rather than containing spaces, because spaces are not treated as blanks by filters or formulas.
Filter Blank Temperature Cells with AutoFilter Before Charting
Use this visual method when you want to quickly remove empty temperature rows and create a chart without changing the original data.
AutoFilter hides rows where the temperature cell is blank. The chart uses only visible cells, so the trendline ignores missing readings without deleting any source data.
Select the date and temperature columns including their header row.
Go to the Data tab and click the AutoFilter button to add filter arrows to the header row.
Click the filter arrow in the temperature column, uncheck the (Blanks) option, and click OK.
Select the visible date and temperature cells, go to the Insert tab, insert a line chart, then use Chart Tools to add a trendline.
Use the FILTER Function to Build a Clean Date and Temperature Range
Use this formula-based method if your WPS Office version supports dynamic arrays and you want a reusable cleaned range for trend calculations or charting.
Create a Blank-Free Temperature Trendline in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, filter out blank temperature rows, insert a line chart, and add a trendline. WPS Spreadsheets keeps the familiar Excel workflow while remaining free and compatible with .xlsx files.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx file containing your date and temperature columns.
- 2. Filter out blank temperatures: Select the dataset, click the Data tab, choose AutoFilter, open the temperature column filter arrow, uncheck (Blanks), and press OK.
- 3. Insert a line chart: Select the filtered date and temperature cells, go to the Insert tab, click Insert Line Chart, and choose a line style.
- 4. Add the trendline: Click the chart, open Chart Tools, select Add Trendline, choose the trendline type, and format it as needed.
Frequently Asked Questions
Why does my trendline flatten or drop when some temperature cells are blank?
WPS Spreadsheets may plot empty cells as zero or connect across gaps, depending on chart settings. In the temperature column filter, uncheck (Blanks), or use =FILTER(A2:B100,B2:B100<>"") to create a clean range before adding the trendline.
Can the FILTER function remove blank temperature rows before charting?
Yes, if your WPS Office version supports dynamic array functions. Enter =FILTER(A2:B100,B2:B100<>"") in an empty cell, then select the spilled range as the chart source. If the function is unavailable, use Data > AutoFilter.
How do I keep the trendline updated when new temperature readings are added?
Convert the date and temperature range into a formatted table by pressing Ctrl+T, apply AutoFilter to the temperature column, and base the chart on the table. New rows added to the table are included after you refresh the filter.
Does excluding blank temperature cells work in older Excel .xls files opened in WPS Office?
AutoFilter works in legacy .xls files. Dynamic array formulas such as FILTER may require a newer file format and a WPS Office version that supports dynamic arrays; save or convert the file to .xlsx if needed.
