Calculation Issues

How to Calculate Averages in Blank Excel Cells Efficiently

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to calculate averages and efficiently fill blank cells located above specific data sets in a large Excel worksheet.

Product: Excel
Device & OS: not provided
Scenario: Working with a large data set where intermediate blank cells need to be filled with the average of the grouped data below them.
Observed behavior: The user wants a bulk-action method to insert AVERAGE formulas into multiple scattered blank cells at once without manually typing each one.

Before you start

Before proceeding, ensure you clear any active filters in your worksheet so that all data rows are visible, preventing your formulas from skipping hidden cells.

Solution 1Recommended

Use Go To Special and Find/Replace to Fill Averages

This method allows you to quickly highlight all empty cells in your data range and apply a bulk formula calculation using keyboard shortcuts.

Instead of typing the AVERAGE formula into every single blank cell, you can use Excel's Go To Special feature combined with the AutoSum shortcut. Since AutoSum defaults to addition, you can quickly adjust it to calculate averages using the Find and Replace tool.

Select the target range

Highlight the entire column or specific data range containing the blank cells you need to fill.

Highlight blank cells

Press Ctrl + G on your keyboard to open the Go To dialog box. Click the 'Special' button, select 'Blanks' from the options, and click 'OK'.

Insert the AutoSum formula

With all blank cells now highlighted, press Alt + = (the AutoSum shortcut) to automatically insert a SUM formula into all selected cells.

Replace SUM with AVERAGE

Press Ctrl + H to open the Find and Replace dialog box. Type 'SUM' into the 'Find what' field and 'AVERAGE' into the 'Replace with' field. Click 'Replace All'.

Verify the calculation ranges

Click on a few of the newly filled cells to verify in the formula bar that the selected cell ranges accurately match the data subsets below them.

Quick Tip: Ensure that your data sets are properly separated by these blank cells before using the AutoSum shortcut, as it automatically detects contiguous numbers.

Powerful Spreadsheet Editor

Quickly Calculate Data Averages with WPS Spreadsheet

WPS Spreadsheet offers powerful built-in tools like Go To Special and Advanced Find & Replace to help you manage large datasets and perform bulk calculations with ease.

1. Select Data Range: Open your file in WPS Spreadsheet and select the data column containing the blank cells.
2. Find Blanks: Go to the Home tab, click 'Find and Replace', choose 'Go To', and select 'Blanks' to highlight them.
3. Apply Formula: Use the AutoSum shortcut (Alt + =) to insert calculations, then press Ctrl + H to replace SUM with AVERAGE.

100% compatible with Microsoft Excel (.xlsx) file formats.Easily locate and fill blank cells using the Go To Special tool.Comprehensive suite of calculation functions including AVERAGE, SUM, and more.Lightweight software with an intuitive, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does AutoSum select the wrong range when filling blank cells?

AutoSum automatically detects contiguous numeric data above or adjacent to the active cell. If there are unexpected blanks, text, or formatting irregularities in your data, the detected range might be truncated. Always verify the formula range after insertion.

Can I use the Go To Special method for non-adjacent columns?

Yes, you can hold down the Ctrl key while clicking and dragging to select multiple non-adjacent ranges. Once selected, pressing Ctrl + G and choosing 'Blanks' will highlight all empty cells within those specific areas simultaneously.

What if the Find and Replace tool changes 'SUM' in text fields accidentally?

To prevent accidental changes to standard text cells containing the word 'sum', ensure that you only highlight the specific cells containing the newly created formulas before running the Find and Replace operation. You can also check 'Match case' if appropriate.

Excel & Data Problems