Quality Assurance and Data Manipulation via Microsoft Excel

I might be a straightaway person, but when it comes to explaining something, I like starting from the start! Hence, before jumping directly onto the topic, let me first explain what it revolves around. So, here we go.

What QA really means?

First of all, we will cover a big and important question: what QA really means? If you Google the question, you will find different meanings on different searches from different sites. But all of them will say that QA is, more or less, testing but this is not quite true.

It simply depends on the way of thinking. Some people might think it is testing with Continuous Integration or certifications. Some people think that it is only testing,…but what is it really? For us, QA is basically a matter of project success.

To get that project success we defined 7 simple rules:

Commitment: The entire team is committed to excellence.

Continuous process: If QA is not only testing, it cannot be involved only at the end of a phase/iteration. QA is a constant process that starts from the first minute till the project ends.

Definition: Definition matters and it is the base of the whole process:

A. Definition of Ready: A functionality is ready to be done if we can answer the following question: Can be a functionality covered by the developer team?

B. Definition of Done: When the project is ended by the developer team and also validated by QA, we can say that a functionality is done. So, we will be able to deliver it.

C. Workflow: The workflow can be defined by and followed by the entire team when we use agile or non-agile approaches. It is strongly proposed not to reinvent the wheel: investigate the existing ones and if you don’t like any of them, edit the one you most liked.

Standards: To follow the standard must be important If the quality starts on the basis.

Testing: QA is not only testing, but it is a necessity to ensure the quality of the product and that it is free of bugs.

Delivering: To deliver and integrate the software timely will help us keep QA as a constant process and also it will help to test and validate it as soon as possible. On the other hand, it will help us to replicate the move to production (MTP) because we should deliver it as same as we are deploying it in production, by this, we will also verify a clean and safe move to production.

Validation: Validating a product is not only testing, it is ensuring we have done what we had to (what the customer wanted).

QA rules: Commitment, continuous process, Definition of workflows, etc.

To follow or to remember those rules are not so difficult that certify for your product at the greatest level of quality, the more customer satisfaction you receive. So saying “let’s do QA” is not so easy: you cannot do QA, you must feel QA.

Why do we need QA?

There are different phases listed when a team starts working on any project. It will start from gathering information related to the project (Requirement Analysis) to planning & making strategies, development, and execution phases. After all these phases, the testing phase will arrive. QA teams take 3-4 phases to get assurance that the product is steady enough for production when the QA team proceeds with testing. The whole process is called Software Testing Life Cycle.

A graph showing the software testing lifecycle

How will Excel help Quality Assurance?

Sometimes, Excel seems too good to be true. All I have to do is enter a formula, and pretty much anything I’d ever need to do manually can be done automatically. Need to merge two sheets with similar data? Excel can do it. Need to do simple math? Excel can do it. Need to combine the information in multiple cells? Excel can do it.

A formula can help you when you encounter a situation where you need to manually update your data. Excel will help in many ways to work on your data to depart on spending hours and hours counting cells or copying and pasting data.

12 Excel Tips to Give A Try

Below are some Excel tips that will change the way you look at it forever:

1) Pivot Tables:

Pivot Tables are used to reorganize data in a spreadsheet. It can be used to sum up values and compare different information in your spreadsheet instead of changing the data that you have.

To create a Pivot Table, I go to Data > Pivot Table. Excel will automatically populate your Pivot Table, but you can always change the order of the data. Then, you have four options to choose from.

⦁ Report Filter:

This permits you to only focus on some rows in your dataset. For example, if anyone wants to create a filter by the house, they can choose to only include students in Gryffindor instead of all students.

⦁ Column Labels: 

It can be said as headers in the dataset.

⦁ Row Labels:

These could be your rows in the dataset. Both Column and Rows labels include data from your columns (e.g. your name can be trawl to either the Row or Column label — it just depends on your behavior to see your data.)

⦁ Value: 

This section permits you to focus on your data differently. We can find the average, count numbers, max, min, the sum or can do other manipulations instead of just pulling in any numeric value.

Since I want to count the number of categories, I’ll go to the Pivot Table and drag the category column to both the Row Labels and the Values. This will count the number of categories.

2) Add More Than One New Row or Column:

As you play around with your data, you might find you’re constantly needing to add more rows and columns. Sometimes, you may even need to add hundreds of rows. Doing this one-by-one would be super tedious. Luckily, there’s always an easier way.

To add any of the rows or columns or multiple in a spreadsheet, you have to highlight the same number of preexisting rows or columns that you need to add. Then, right-click and select “Insert.”

3) Filters:

When you’re looking at very large data sets, you don’t usually need to be looking at every single row at the same time. Sometimes, you only need to focus on data that fits some criteria. That’s where filters come in.

Filters allow you to pair your data to only look at certain rows at one time. In Excel, we can add a filter to each column or a single volume and then choose which cells you want to view at once.

4) Remove Duplicates:

Larger data sets lean to have identical content. You may have a list of numerous contacts in a company and only need to see the number of companies you have. In situations like this, removing the identical comes in quite handy.

To remove your identicals, point out the row or column that you need to remove identicals of. Then, go to the Data tab, and select “Remove Duplicates” (under Tools). A pop-up will arise to verify which data you need to work with. Select “Remove Duplicates,” and you’re good to go.

5) Transpose:

When you have fewer rows of data in your spreadsheet, you might determine that you actually need to transform the items in one of those rows into columns (or vice versa). More time would be taken to copy and paste each individual header – but the transpose feature allows you to solely move your row data into columns, or the other way around.

Start by spotting the column that you need to interchange into rows. Right-click it, and then select “Copy.” Next, select that particular cells in which you need your first row or column to begin. Then Right-click that cell, and select “Paste Special”. You’ll see an option to transpose. Check that box and select OK. Your column will now be interchanged into a row or vice-versa.

6) Text to Columns:

If you need to break information that’s in one cell into two identical cells. For example, you need to stretch out someone’s company name through their email address. Or you need to disparage someone’s full name into a first and last name for your email marketing templates.

Both are possible through excel. First, spot the column that you need to break up. Then, click on the Data tab and select “Text to Columns.” A module will emerge with additional information.

7) Conditional Formatting Formula:

Conditional formatting permits you to change a cell’s color based on the information in the cell. For example, if you need to flag some numbers that are above average or in the top 10% of the data in your spreadsheet, it can be done. If you need to color code commonalities between different rows in Excel, you can do that. This will help you rapidly see the information that is significant to you.

To get started, spot the group of cells you need to use conditional formatting on. Then, choose “Conditional Formatting” from the Home menu and select your logic from the drop-down. A window will pop up that induces you to assign more information about your formatting rule. Select “OK” when you’re done, and you should see your results automatically appear.

8) IF Statement:

Occasionally, we don’t need to count the number of times a value arrives. Ideally, we need to input distinct information in a cell if there is a corresponding cell with that information.

The formula: IF(logical_test, value_if_true, value of false).

9) VLOOKUP Function:

Have you ever had two sets of data on two different spreadsheets that you need to combine into a single spreadsheet?
For example, you might have a list of people’s names next to their email addresses in one spreadsheet, and a list of those same people’s email addresses next to their company names in the other – but you want the names, email addresses, and company names of those people to appear in one place.

I have to combine data sets like this a lot – and when I do, the VLOOKUP is my go-to formula. When you use the formula, you have to be sure that you have at least one column that arrives identically in both places. Scour your data sets which will make sure the column of data you’re using to combine your information is exactly the same, including no extra spaces.

The formula: =VLOOKUP(lookup value, table array, column number, [range lookup]),


The INDEX and MATCH functions, like VLOOKUP, will stretch in data from another dataset into one central location. There are some main differences:
VLOOKUP is a much easier formula. With large data sets that would require thousands of lookups, using the INDEX MATCH function will significantly reduce load time in Excel.

VLOOKUP formulas only work as a left-to-right lookup, whereas INDEX MATCH formulas work right-to-left. In other words, if you want to do a lookup that has a lookup column to the right of the results column, then you’d have to reorder those columns in order to do a VLOOKUP. This can be unvaried with large datasets and/or lead to errors.
So if I need to merge information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren’t the same, then I would need to switch around my columns if you have done a VLOOKUP. In this case, I’d choose to do an INDEX MATCH instead.

The formula: =INDEX(table array, MATCH formula).

11) COUNTIF Function:

Let Excel do the work for you, instead of manually counting how often a certain value or number appears. Excel can count the number of times a word or number arrives in any range of cells With the COUNTIF function.

The formula: =COUNTIF(range, criteria).

12) Combine cells using “&”:

Databases lean to break out data to make it as accurate as possible. For example, instead of having data that shows a person’s full name, a database might have the data as a first name and then the last name in different columns. Or, it may have a person’s location separated by city, state, and zip code. In Excel, you can merge cells with different data into one cell by using the “&” sign in your function.

The formula with variables from our example below: =A2&” “&B2

Follow me!

2 thoughts on “Quality Assurance and Data Manipulation via Microsoft Excel”

Leave a Comment