How to Calculate Percentage of Filled Cells in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Certainly, Excel is a popular and useful tool for organizing data and tracking the progress of a task. Now, wouldn’t it be great if we could calculate the percentage of filled cells in Excel? Sounds complex, right? Wrong! In this article, we’ll explore all the nitty-gritty of how to calculate percentage of filled cells in Excel.

Table of Contents hide

How to Calculate Percentage of Filled Cells in Excel: 7 Ways

Method 1: Using COUNTA Function

Method 2: Using COUNTA and ROWS Functions

Method 3: Utilizing COUNTA and COLUMNS Functions

Method 4: Applying COUNTA and COUNBLANK Functions

Method 5: Combining COUNTIF and COUNTA Functions

Method 6: Employing COUNTIFS and COUNTA Functions

Method 7: Using SUBTOTAL Function

Practice Section

Conclusion

How to Calculate Percentage of Filled Cells in Excel: 7 Ways

Undoubtedly, Microsoft Excel offers numerous ways to combine various functions to calculate the percentage of filled cells.
For instance, consider the Project Checklist dataset shown in the B4:C13 cells which shows the list of Duties and their completion Status. On this occasion, we want to obtain the percentage of filled cells, that is to say, cells marked as Done. So, let’s go through each method one at a time with appropriate illustrations.

In this situation, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.

Method 1: Using COUNTA Function

Let’s start with the simplest and the most obvious way to compute the percentage of filled cells. Simply put, we’ll use the COUNTA function to count the number of non-blank and the total number of cells in the given range and hence obtain the percentage of filled cells. Therefore, let’s see it in action.

📌 Steps:

At the very beginning, go to the C15 cell >> enter the formula given below.

=COUNTA(C5:C13)/COUNTA(B5:B13)

Here, the B5:B13 and C5:C13 range of cells refer to the Duties and Status columns respectively.

Formula Breakdown:

COUNTA(C5:C13)/COUNTA(B5:B13) → counts the number of cells in a range that are not empty. Here, the C5:C13 is the value1 argument that refers to the Status column. Additionally, the B5:B13 is the value1 argument that refers to the Duties column.
COUNTA(C5:C13)/COUNTA(B5:B13) → becomes
- 6/9 → 0.666666667

Next, use the CTRL + SHIFT + % shortcut keys on your keyboard to format the values as a percentage. In addition, you can use the Percent Style button in the Number ribbon group from the Home tab.

📃 Note: You can open the Format Cells dialog box by pressing CTRL + 1 and changing the cell formatting to percentage.

Finally, the results should look like the image given below.

Method 2: Using COUNTA and ROWS Functions

For one thing, we’ll combine the COUNTA and ROWS functions. Now, the COUNTA function returns the number of filled cells while the ROWS function gives the total number of cells in the specified range. Thus, dividing the former by the latter yields the percentage of filled cells.

📌 Steps:

First and foremost, move to the C15 cell >> type in the equation given below.

=COUNTA(C5:C13)/ROWS(C5:C13)

In the above equation, the C5:C13 range indicates the Status column.

Formula Breakdown:

ROWS(C5:C13) → returns the number of rows in a reference or array. Here, the C5:C13 is the array argument that indicates the Status column.
COUNTA(C5:C13)/ROWS(C5:C13) → becomes
- 6/9 → 0.666666667

Lastly, press the CTRL + SHIFT + % keys to apply percentage formatting >> your output should look like the picture shown below.

Similar Readings

Method 3: Utilizing COUNTA and COLUMNS Functions

What if your data spans across columns instead of rows? Lucky you! Our next method has you covered. Here, we’ll utilize the COUNTA and COLUMNS functions which count the number of non-blank cells and the total number of cells spanning across the columns respectively.

📌 Steps:

Initially, navigate to the D8 cell and insert the expression into the Formula Bar.

=COUNTA(C5:K5)/COLUMNS(C5:K5)

In this expression, the C5:K5 cells point to the Status row.

Formula Breakdown:

COLUMNS(C5:K5) → returns the number of columns in a reference or array. Here, the C5:K5 is the array argument that refers to the Status row.
COUNTA(C5:K5)/COLUMNS(C5:K5) → becomes
- 6/9 → 0.666666667

Following this, change the cell formatting to percentage using the CTRL + SHIFT + % keys.

Consequently, the results should appear like the screenshot given below.

Method 4: Applying COUNTA and COUNBLANK Functions

Conversely, we’ll apply the COUNTA and COUNTBLANK functions. Now, the COUNTBLANK function counts the number of empty cells within the selected range of cells. Now, allow me to demonstrate the process in the steps below.

📌 Steps:

In the first place, jump to the C15 cell and enter the formula given below.

=COUNTA(C5:C13)/(COUNTA(C5:C13)+COUNTBLANK(C5:C13))

In this instance, the C5:C13 range indicates the Status column.

Formula Breakdown:

COUNTBLANK(C5:C13) → counts the number of empty cells in a given range. Here, the C5:C13 is the range argument that refers to the Status column.
COUNTA(C5:C13)+COUNTBLANK(C5:C13) → becomes
- 6+3 → 9
COUNTA(C5:C13)/(COUNTA(C5:C13)+COUNTBLANK(C5:C13)) → becomes
- 6/9 → 0.666666667

📃 Note: Press the CTRL + 1 keys to open the Format Cells window >> select the Percentage option >> hit the OK button.

Consequently, the results should look like the image shown below.

Similar Readings

Method 5: Combining COUNTIF and COUNTA Functions

Alternatively, you can also employ the COUNTIF and COUNTA functions to obtain the percentage of filled cells. For instance, the COUNTIF function gives the number of occurrences when a particular condition like, if a cell is empty or not, is met. Then, let’s see the process in detail.

📌 Steps:

To begin with, type in the following expression into the C15 cell.

=COUNTIF(C5:C13,"<>")/COUNTA(B5:B13)

Here, the B5:B13 and C5:C13 arrays represent the Duties and Status columns.

Formula Breakdown:

COUNTIF(C5:C13,”<>”) → counts the number of cells within a range that meet the given condition. Here, the C5:C13 cells represent the range argument that refers to the Status, while the “<>” indicates the criteria argument that represents the Not Blank criteria.
- Output → 6
COUNTIF(C5:C13,”<>”)/COUNTA(B5:B13) → becomes
- 6/9 → 0.666666667

Ultimately, after completing the above steps, your answer should look like the picture shown below.

Method 6: Employing COUNTIFS and COUNTA Functions

Similar to the prior method, you can also utilize Excel’s COUNTIFS function to compute the number of instances where the cells are filled and thus, get the percentage of filled cells. Now, the process is simple & easy, just follow along.

📌 Steps:

First, copy and paste the equation given below into the C15 cell.

=COUNTIFS(C5:C13,"<>")/COUNTA(B5:B13)

Formula Breakdown:

COUNTIFS(C5:C13,”<>”) → counts the number of cells specified by a given set of conditions and criteria. Here, the C5:C13 cells represent the criteria_range1 argument that refers to the Status, whereas the “<>” indicates the criteria1 argument that represents the Non-Blank criteria.
- Output → 6
COUNTIFS(C5:C13,”<>”)/COUNTA(B5:B13) → becomes
- 6/9 → 0.666666667

Finally, the results should look like the image depicted below.

Method 7: Using SUBTOTAL Function

Last but not least, we’ll apply the versatile SUBTOTAL function to count the number of filled cells and the total number of cells to calculate the percentage of filled cells. So, let’s glance at the procedure in the steps below.

📌 Steps:

To begin with, proceed to the C15 cell >> enter the expression given below into the Formula Bar.

=SUBTOTAL(3,C5:C13)/SUBTOTAL(3,B5:B13)

Formula Breakdown:

SUBTOTAL(3,C5:C13) → returns a subtotal in a list or database. Specifically, 3 is the function_num argument that refers to the COUNTA function while the C5:C13 cells represent the ref1 argument from where the COUNTA function counts the values.
- Output → 6

SUBTOTAL(3,B5:B13) → Here, the B5:B13 represents the ref1 argument from where the COUNTA function counts the values.
- Output → 9
SUBTOTAL(3,C5:C13)/SUBTOTAL(3,B5:B13) → becomes
- 6/9 → 0.666666667

Lastly, your output should look like the screenshot below.

Practice Section

Furthermore, we have provided a Practice section on the right side of each sheet so you can practice yourself.

Download Practice Workbook

Calculating Percentage of Filled Cells.xlsx

Conclusion

To sum up, all of the methods for calculating percentage of filled cells in Excel will now prompt you to apply them in your spreadsheets more effectively. Now, if you have any questions or feedback, please let us know in the comment section. Moreover, you can check out our other articles related to Excel functions on this website.

How to Calculate Percentage of Filled Cells in Excel (7 Easy Ways)

How to Calculate Percentage of Filled Cells in Excel: 7 Ways

Method 1: Using COUNTA Function

Method 2: Using COUNTA and ROWS Functions

Method 3: Utilizing COUNTA and COLUMNS Functions

Method 4: Applying COUNTA and COUNBLANK Functions

Method 5: Combining COUNTIF and COUNTA Functions

Method 6: Employing COUNTIFS and COUNTA Functions

Method 7: Using SUBTOTAL Function

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply