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.
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.
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.
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
Ultimately, after completing the above steps, your answer should look like the picture shown below.
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
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
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
- Calculate Percentage Using Absolute Cell Reference in Excel
- Calculate Percentage in Excel VBA
- How to Calculate Percentage above Average in Excel
- How to Apply Percentage Formula in Excel for Marksheet
- IF Percentage Formula in Excel
- How to Calculate Contribution Percentage with Formula in Excel
- How to Use Food Cost Percentage Formula in Excel
- How to Calculate Percentage for Multiple Rows in Excel
- How to Calculate Variance Percentage in Excel
<<Go Back to Calculate Percentage with Criteria in Excel | Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
