When you have missing values in Excel, particularly in a sequence of records, it’s crucial to fill these gaps correctly to maintain the integrity of your data and enable accurate reports and analyses.
For example, if your source data on orders provides Customer ID and Sales amount as shown below.
Customer ID is only populated in the first row for each order. We need to ensure that missing values are populated with the Customer ID from the prior row (essentially, filling down). Only then we can generate a report on sales by customer.
Let’s assume the data starts from cell C7. (Customer IDs in column C).
Let us see how to Fill Down: To copy the Customer ID down to associate each sale in column G, we write the following formula in cell G7.
Let us understand our function here, If the corresponding ID in C7 is missing, return the value from the previous row in column G. The IF function handles this by returning C7 itself if it is not missing.
To return the value from the previous row, we use Excel’s OFFSET function. The syntax for this is:
Check our dedicated video on our YouTube channel for fill-down: