In this blog, we will explore a simple and efficient method to fill blank cells with the value above in Excel. This method is not only straightforward but also a great time-saver when working with large datasets, especially when sorting and filtering data. Using this technique will ensure that your spreadsheet is more functional and that your reports and data analysis are more accurate and correct. Knowing this trick will make your life easier and your work more productive. So, let’s dive in and learn how to do it!

Excel Practice Files

Understanding the Importance of Filling Blank Cells

Before we get into the steps, it’s essential to understand why filling blank cells with the data above can be so useful:

  • Functionality: Blank cells can hinder the functionality of your spreadsheet, making tasks such as sorting and filtering data difficult and less effective.
  • Consistency: When working with data, it’s crucial to maintain consistency. Blank cells can disrupt the data flow and lead to errors in the analysis; e.g. VLOOKUP or XLOOKUP functions.
  • Efficiency: Manually filling in blank cells can be time-consuming, especially in large datasets. This method can also easily lead to errors.
  • Accuracy: The steps below ensure the data is filled correctly, significantly reducing the chances of mistakes. This will give you more confidence in your data analysis and reporting.

Now that we understand its importance let’s move on to the application.

Fill Blank Cells Practical Applications in Excel

Let’s look at some practical applications where filling blank cells with the data above can be extremely useful.

1. Data Cleaning – Sorting, Filtering, Pivot Tables

When importing data from external sources, it often contains blank cells. Filling these cells with the value above ensures consistency. It makes the dataset more straightforward to analyse, which is particularly useful for data cleaning,  filtering, sorting, and generating pivot table reports.

2. Creating Reports – Lookup Functions

In reports, having blank cells can make the data look incomplete. Additionally, blank cells can cause issues when using lookup functions like XLOOKUP or VLOOKUP. For instance, using a lookup value from a column with blank cells may lead to #N/A errors because Excel cannot find a match. Filling the blank cells with relevant data ensures your lookup functions work smoothly.

3. Data Analysis – Counting and Averaging

In data analysis, missing data can lead to wrong conclusions. For example, empty cells can cause incorrect COUNT or AVERAGE calculations, which might make the reports inaccurate. Filling all cells with the right data helps ensure your analysis is accurate.

Steps to Fill Blank Cells with Value Above

Follow these simple steps to fill blank cells with the value above in Excel:

1. Selecting the Range

Highlight the area with blank cells that you need to fill. Make sure to include the cells with the value above that are to be used to fill in the blanks.

A Microsoft Excel 365 spreadsheet table showing product types, codes, and total sales. Products include Bread, Cans, Flour, and Fresh Fruit. In column A, there are blank cells within the range A2 to A16.

2. Use the Go To Special Command

Press Ctrl + G to open the Go To dialog box. Click on the Special button to open the Go To Special dialogue box.

A screenshot of the "Go To" dialog box in a Microsoft Excel 365 application. The dialog box has a text field labeled "Go to:" and another labeled "Reference:". There is an empty list area under the "Go to:" label. At the bottom, there are three buttons: "Special..." highlighted with an orange border, "OK", and "Cancel".

3. Choose Blanks

In the Go To Special dialog box, check the Blanks option [1]. Click OK [2]. This will select all the blank cells in the highlighted range.

The image shows the "Go To Special" dialog box in the Microsoft Excel 365 application. Blanks is selected with a green number 1 icon next to it. At the bottom of the dialog box are two buttons: "OK" with a green number 2 icon next to it and "Cancel".

4. Enter the Formula

With the blank cells still selected, type = (the equal sign) and then press the up-arrow key. This tells Excel to use the value from the cell directly above each blank cell, which in the example below is cell A2.

The image shows an Microsoft Excel 365 worksheet with three columns labeled "Product Type," "Product Code," and "Total Sales." The rows contain data for various products. In the "Product Type" column, there are entries such as Bread, Cans, Flour, and Fresh Fruit. Cell A3 contains the formula "=A2". The blank cells in the Product Type column are selected.

5. Enter the Formula

Press Ctrl + Enter to apply the formula to all selected blank cells. The data from the cell above will fill all the blank cells.

The image shows a Microsoft Excel 365 table with three columns labeled "Product Type," "Product Code," and "Total Sales." The table lists different product types (Bread, Cans, Flour, Fresh Fruit) along with their corresponding product codes and total sales figures. Column A show no blank cells.

These steps are straightforward and can be done in just a few clicks, making them highly efficient.

Fill Blank Cells With Value Above Using Fill Handle

While the above method is very efficient, there are alternative methods you can use to achieve the same result:

1. Select the cell with the data you want to copy.

A Microsoft Excel 365 worksheet with three columns labeled "Product Type," "Product Code," and "Total Sales." The first row under "Product Type" contains the word "Bread. An orange callout box points to a small black square at the bottom right corner of cell A2, indicating that double-clicking this fill handle will copy the selected cell's content into blank cells below.

2. Double-click the Fill Handle (a small square at the bottom-right corner of the cell). This will copy the selected cell’s contents into the blank cells and stop where a cell with content was found. This method is quick and easy for smaller ranges.

A Microsoft Excel 365 worksheet with three columns labeled "Product Type," "Product Code," and "Total Sales." The rows contain data for different products. Rows 2 to 5 list the product type as "Bread" after the fill handle has been used to copy the cell above into the blank cells below.

Tips and Tricks

Here are additional tips and tricks to help you master working with blank cells.

Shortcut Keys

Familiarise yourself with these Excel shortcut keys to speed up the process.

  • To select blank cells in a selected range: Ctrl + G, Alt + S, Alt + K, Enter
  • To apply a formula to all selected cells: Ctrl + Enter

Conditional Formatting

Use conditional formatting to highlight blank cells, making identifying and filling them easier. Here are the steps to do this:

  1. Select the range of cells where you want to identify blank cells.
  2. Click the Home tab
  3. Click on Conditional Formatting in the Styles group.
  4. Choose New Rule from the drop-down menu.
The image shows a Microsoft Excel 365 spreadsheet with a table containing three columns labeled "Product Type," "Product Code," and "Total Sales." The table lists data for products such as Bread and Cans, with corresponding product codes and total sales figures. The Home tab is selected in the Excel ribbon. A dropdown menu for Conditional Formatting is open on the right side. New Rule is highlighted.
  1. In the New Formatting Rule dialogue box, select Format only cells that contain.
  2. In the Format only cells with section, from the first drop-down box, choose Blanks from the menu.
  3. Click the Format button to choose the formatting options (e.g., fill colour) you want to apply to blank cells.
The image shows the "New Formatting Rule" dialog box in a spreadsheet in Microsoft Excel 365. “Format only cells that contain” is selected. A dropdown menu is open under "Format only cells with," highlights the “Blanks” option. There are buttons labeled "Format..." and "OK" at the bottom.
  1. Click OK twice to apply the rule.

Watch The Excel Video Tutorial

Excel Practice File Download

  • Sign up to unlock the practice file

Conclusion

Filling blank cells with the value above in Excel is a handy trick that can streamline your data processing tasks. With these simple steps, you can complete your work more efficiently and keep your spreadsheets organised. Whether dealing with large datasets, creating reports, or conducting research, this method will save you time and ensure accuracy.

So, next time you encounter blank cells in your Excel spreadsheet, remember these steps and tips to fill them quickly with the value above. Happy Excel-ing!

Loved This Tip? Learn Even More in my Self-Paced Intermediate Excel Course

Collection of devices with different parts of the Excel at Work Excel self-paced online courses being displayed with captions.

If you’re ready to improve your skills and stop doing things the long and hard way, the Excel Stage 2 course is perfect for you! It will help you boost your productivity, working smarter, not harder.

$347+gst


Sharyn Baines
Sharyn Baines
Certified Microsoft Office Specialist

Was this blog helpful? I’m here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.

Share your thoughts in the Comments below – your insights not only enrich others, they also help me tailor future content to your needs.

And if you’re looking to take a step further, join our exclusive ‘Insider Group‘. As a member, you’ll receive Weekly Super-Tips, and early access to in-depth tutorials. Sign up Today!

Happy Excel-ling!!

2 Comments

  1. Really helpful about filling in blanks on the Pivot table, thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *