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.


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.

how to calculate percentage of filled cells in excel

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

Using COUNTA Function

  • 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.

Using Shortcut to Format to Percentage

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

how to calculate percentage of filled cells in excel with COUNTA Function


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

Using COUNTA and ROWS Functions

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

how to calculate percentage of filled cells in excel Using COUNTA and ROWS Functions


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

Utilizing COUNTA and COLUMNS Functions

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

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

how to calculate percentage of filled cells in excel Utilizing COUNTA and COLUMNS Functions


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.

Applying COUNTA and COUNBLANK Functions

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

how to calculate percentage of filled cells in excel Applying COUNTA and COUNBLANK Functions

Read More: How to Apply Percentage Formula for Multiple Cells in Excel


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

Combining COUNTIF and COUNTA Functions

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

how to calculate percentage of filled cells in excel Combining COUNTIF and COUNTA Functions

Read More: How to Add Percentage to Price with Excel Formula


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

Employing COUNTIFS and COUNTA Functions

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

how to calculate percentage of filled cells in excel Employing COUNTIFS and COUNTA Functions


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

Using SUBTOTAL Function

Lastly, your output should look like the screenshot below.

how to calculate percentage of filled cells in excel Using SUBTOTAL Function


Practice Section

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

Practice Section


Download Practice Workbook


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.


Related Articles


<<Go Back to Calculate Percentage with Criteria in Excel | Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo