Calculation Issues

How to Keep Previous Monthly Excel KPI Scores When Changing Drop-Downs

WPS Content Manager Oct 1, 2026 868 views

Question details

The user needs a method to retain previous monthly KPI data on a master sheet when updating a drop-down menu for the new month, rather than having the old data overwritten dynamically.

How to Keep Previous Monthly Excel KPI Scores When Changing Months

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking full-year monthly KPI scores using a reporting dashboard controlled by a month-selection drop-down list.
Observed behavior: Changing the month in the drop-down list recalculates the dashboard for the current month but wipes out or overwrites the previous month's KPI scores on the master history sheet.

Before you start

Ensure your master sheet is properly formatted with dedicated columns or rows for each month (January to December) before implementing a data preservation method.

Solution 1Recommended

Use a VBA Macro to Append and Archive Monthly Data

VBA can automate the process of copying the current month's dynamically calculated KPI scores and pasting them as static values into a historical archive before you change the drop-down.

Formulas linked to a drop-down will always overwrite data when the selection changes. By using a VBA macro, you can permanently lock in the previous month's data as static text and numbers.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.

Insert a New Module

Click Insert > Module from the top menu, and write a script to copy your calculated KPI range (e.g., Range("B2:B10").Copy).

Paste as Static Values

Direct the macro to find the next empty column or row in your master sheet and use the PasteSpecial Paste:=xlPasteValues command to drop the data without the underlying formulas.

Run the Macro via a Button

Go to Developer > Insert > Button (Form Control), draw a button on your dashboard, and assign your new macro to it. Click this button to save the current data before selecting a new month.

Use a VBA Macro to Append and Archive Monthly Data

Macro Security: Remember to save your workbook as an Excel Macro-Enabled Workbook (.xlsm) so your VBA script runs successfully next time.

Solution 2

Manually Paste as Values into Separate Columns

If you prefer not to use macros, you can restructure your layout and manually paste the formula results as values.

Solution 3

Automate Archival Using Power Query

Use Power Query to append your new monthly inputs to a historical data table, creating an ongoing log that won't be deleted.

Advanced Spreadsheet Data Management

Easily Manage and Archive KPI Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools, including advanced formulas, full macro (VBA) support, and seamless pivot tables. You can automate data archiving effortlessly and maintain full-year history logs without complicated workarounds.

1. Open Your KPI Workbook: Launch WPS Spreadsheet and open your existing KPI tracking file.
2. Set Up Drop-Down Lists: Navigate to the Data tab and click Validation to easily configure your monthly drop-down lists.
3. Record or Write a Macro: Use the Developer tab to record a macro that copies your calculated month data and pastes it as values into your master history log.
4. Insert an Automation Button: Click Insert > Shapes to create a save button, right-click it, and select Assign Macro to archive your data with one click.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats and macros.Built-in advanced data validation tools for intuitive drop-down lists.Free, lightweight, and fast processing for complex KPI dashboards.Familiar UI making it easy to migrate and automate existing sheets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my historical data disappear when I change the drop-down list?

This happens because your output sheet is populated by formulas dynamically linked to the drop-down cell. When the drop-down changes, the formulas recalculate for the new month, which inherently replaces the previous calculation results.

Can I keep previous data without using VBA macros?

Yes, you can either manually copy the calculated results for the current month and paste them as 'Values' into a dedicated historical archive, or use Power Query to append new entries to a master table.

How do I create a drop-down list for months in a spreadsheet?

Go to the Data tab, click Data Validation, select 'List' under the Allow criteria, and type the months separated by commas (e.g., Jan, Feb, Mar) in the Source box.

Excel & Data Problems