How to Refresh Data in an Excel Software Testing Spreadsheet

If you use Excel to coordinate software testing tasks, you’ll probably want to add Pass/Fail columns to track their status. In our Acceptance Criteria Log templates, we have a Pass/Fail column that lets you see at a glance the testing progress.

If you are not used to Excel, if can be a bit confusing to work out the steps to update the chart. In other words, even if you update the spreadsheet values, the charts don’t update. This can be very frustrating is your new to Excel and expect the charts to dynamically update. There is a way to do this and we’ll show this in a later tutorial.

How to Refresh Data in an Excel Software Testing Spreadsheet

For now, let’s look at how to refresh data in an Excel chart.

Here are the steps to change the values and update the chart.

In Excel, when you add a new chart, the values are stored in a separate tab (spreadsheet), typically called Data. The first thing to note is that the Data sheet is READ ONLY.

I think this is what may be causing the confusion. Its purpose is only to ‘store’ the values only. Think of it as a report that gets updated when you update the parent spreadsheet.

So how do I update the cells?

In this Acceptance Log Criteria sheet, the count is currently 5 Fail and 7 Pass.

We’re going to change this to 6, 6

1. In the Acceptance Log Criteria sheet, click in F4. The first cell in the Pass/Fail Column.

Type in Fail. The cell changes to Red – but the chart has not updated!

To update the chart:

2. Right–click on the chart.
3. Click Refresh Data (the first option on the context menu)

The chart is updated.

It now shows 6 Fails and 6 Passes.

Note: If you click the Data sheet, it now shows the same values.

Does this make sense?

So, to update the values in the main spreadsheet, click Refresh Update, and then the chart is updated.

Let me know if this works for you or if you have other questions about Excel.

Download this Excel Acceptance Criteria Template

Download the MS Excel Acceptance Criteria Log template to help your Software Testers improve the quality of their QA documentation.

Learn more about the Software Testing Templates.

What is Acceptance Criteria?

Acceptance Criteria are the conditions that a product must satisfy before it can be accepted by a user and passed in the Production phase for deployment. In software testing, you define Acceptance Criteria to determine if a piece of software has passed or failed a specific criterion/criteria.