How to Move Colored Excel Columns and Dates to the Left in WPS Office Spreadsheets
Question details
The user needs a worksheet in WPS Office Spreadsheets to automatically move colored date columns left as dates advance from a TODAY() formula, but the correct design rule is not yet specified.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet uses TODAY() in H1, links B3 to H1, and calculates later dates across row 3; colored cells below each date should move left as the dates advance.
- Observed behavior
- The current layout does not define whether the worksheet should maintain a fixed rolling range or add a new date and colored column each day, and the color meaning and current-date cell behavior are unspecified.
Before choosing a solution, confirm whether the sheet should keep a fixed number of day columns, such as an 11-day rolling window, or add a new date and colored column each day. Also record what each color represents and what should happen to the cell that corresponds to the current date.
Clarify the Worksheet Date Window and Color Rules Before Automating
Use this first when the workbook design is ambiguous, so you choose the correct rolling or expanding layout.
Because the original worksheet links B3 to H1 and uses TODAY(), the right solution changes depending on whether columns are fixed or keep expanding. Defining the date window, color meaning, and current-date behavior prevents formula and conditional formatting rework.
Open WPS Office Spreadsheets, display the worksheet with the colored date columns, and check H1, B3, and row 3 in the formula bar to confirm how the dates are generated.
Decide whether the sheet must keep a fixed number of day columns—for example an 11-day window—or insert a new date and colored column every day.
In a blank area of the sheet, note what each color represents and specify what should happen to the cell under the current date, such as whether B6 should receive a new color when B3 equals H1.
Create a Fixed Rolling Date Window Using WPS Formulas and Conditional Formatting
Use this when the worksheet should keep the same number of columns and let the dates and colors shift left automatically.
Build an Expanding Schedule That Adds a New Date and Colored Column Each Day
Use this when the layout should grow daily instead of keeping a fixed number of columns.
Set Up Automatic Colored Date Columns in WPS Office Spreadsheets
Use WPS Office Spreadsheets to define a fixed rolling date range or an expanding daily schedule, apply conditional formatting, and automate column shifts with formulas or macros. WPS Office works with Microsoft Excel .xlsx files and preserves TODAY() formulas, formatting, and layout.
- 1. Download and install WPS Office: Download WPS Office from the official site, install it, and open your existing .xlsx workbook in WPS Office Spreadsheets.
- 2. Set up the date row: Select H1 or your date cell, enter =TODAY(), and link B3 to H1 with =H1. Fill the remaining date cells with formulas such as =B3+1 for an expanding range, or =H1-10 and =B3+1 for a fixed 11-day window.
- 3. Apply conditional formatting to colored cells: Select the cells below the dates, click Home > Conditional Formatting > New Rule, and create rules based on the date row or helper cells so colors recalculate with each new day.
- 4. Automate column shifts: For a fixed window, keep a set number of columns and let formulas shift the displayed dates; for an expanding schedule, use the Developer tab to insert a macro that adds a new date column daily.
- 5. Save and test: Save the workbook as .xlsx and edit H1 temporarily or change the system date to confirm that the colored columns move left or extend as intended.
Frequently Asked Questions
How can I keep an 11-day rolling date window in WPS Office Spreadsheets?
Use a fixed set of date columns, such as B3:L3. Enter =H1-10 in the first column and =B3+1 in the next columns, then apply conditional formatting to the cells below row 3 so the colors follow the recalculated dates.
Will colored cells move left automatically when the date changes?
Yes, if the colors come from conditional formatting rules instead of manual fill. Select the colored range, click Home > Conditional Formatting > New Rule, and base the rules on the date row or helper cells so the formatting shifts with the date values.
Does WPS Office support VBA macros for inserting new date columns daily?
WPS Office Spreadsheets supports macro automation in supported editions. Open the Developer tab, access the macro editor, and create a macro that inserts a column, fills the next date from H1, and copies existing color rules.
What is the difference between a rolling date range and an expanding schedule?
A rolling date range keeps the same number of day columns and shifts the displayed dates, while an expanding schedule adds a new date and colored column every day. Choose the layout before writing formulas or macros.
Can WPS Office open an Excel workbook that uses TODAY() and conditional formatting?
Yes, WPS Office Spreadsheets opens and saves .xlsx workbooks and supports functions such as TODAY(), date formulas, and conditional formatting, so existing Excel files work without rebuilding the sheet.
