Filling in the Gaps: A Guide for Data Imputation for Libraries

Making the most of incomplete datasets

Posted on September 22, 2025 in Blog Posts

Ever wonder how Netflix seems to recommend shows you end up loving even if you have barely rated anything? Behind the scenes, systems like Netflix rely on a process called data imputation. Data imputation works by filling in the missing values so predictions can still be made.

Libraries face similar challenges. There could be gaps in circulation logs, survey responses, or even vendor usage reports. Working with imperfect data is part of the job. But don’t worry—incomplete data does not mean useless data, and it does not require coding knowledge to fix.

In this post, we will explore practical data imputation techniques you can use in Excel or Google Sheets to make both smarter and more complete decisions. You do not have to be a data analyst to learn these strategies that can help turn those missing numbers into meaningful insights.

Types of Missing Data

Before filling in missing data, it helps to understand why it is missing in the first place. Statisticians usually define missing data into three types, and each affects how trustworthy your imputation will be.

• Missing Completely at Random (MCAR), where the absence of data has no patterns. For example, a few circulation numbers might be missing simply because of a random system glitch. In these cases, averaging or carrying forward previous values usually works pretty well. 

• Missing at Random (MAR), where missing data is related to other observed information. For example, a department may have incomplete survey responses, but you also know their budget and circulation trends. MAR data benefits from smarter methods, like regression or interpolation, that take relationships between variables into account.

• Missing Not at Random (MNAR), where the missingness itself is meaningful. For example, low-use materials might be more likely to be withdrawn and thus missing from your dataset. MNAR is tricky because, by filling in values, you may hide underlying patterns.

Recognizing the type of missing data you’re dealing with helps you choose the best approach and ensures your dataset remains useful to decision-making. 

---

🔥 Stay up-to-date with LibTech Insights by signing up for our free newsletter. Just one weekly email with our new blog posts, top tech news stories, and other bonus content. Check out some posts from our archive:

• Using Data Forecasting to Improve Community College Collections Development
• Starting a GenAI Community of Practice
• Uncovering Hidden Needs Through Participatory Design

🌟 Register now for our free email micro-course on AI literacy!

---

Simple Methods

Sometimes the simplest methods are all you need, especially when the missing data is limited or the dataset is small. Common methods include mean and median substitution. This method involves replacing the missing values with an average or median substitution from the available data. For example, if March circulation data was missing but January, February, and April are 50, 60, 70, you could fill in March with a mean of 60. The approach is quick and easy to implement, but keep in mind that this method can reduce variability and bias the totals if used extensively.

Another useful technique is Last Observation Carried Forward (LCOF). This imputation method is implemented by using the most recent value to fill in the known gaps. The method works really well when you have time-based data and the values don’t change dramatically from month to month. For example, if April circulation data was missing, you could carry forward March’s value. The main issue with this method is that it assumes little-to-no change between periods, so if your data has strong seasonal trends, you might want to consider something else.

Both of these methods are strong starting points for anyone working with incomplete datasets. They do not require advanced tools, and they are accessible enough for library staff who want to see results right away. By starting with these approaches, missing data can be more manageable.

Smarter Methods

If you are dealing with missing data that is more frequent or could impact important decisions, the basic methods above may not be sufficient. When that happens, we can use smarter methods like regression and interpolation. With these methods, they can help maintain the patterns and relationships in your data while estimating missing values.

Regression imputation is a smarter approach because it takes into account relationships with other variables when imputing missing values. For example, if your library sees an increase in the number of enrolled students but no usage data, you could use regression to estimate what those usage numbers might be. Excel has a few tools to accomplish this, including Excel Data Analysis ToolPak and the FORECAST.LINEAR function. This method is more accurate than mean imputation but requires attention to the relationships you are modeling.

Another approach is interpolation. Interpolation is useful when the missing data falls between two known data points in the series. For example, if February and April gate counts are known but March is missing, interpolation can estimate the missing value by assuming the trend between the two months.

You might also want to consider the nearest neighbors imputation. This approach fills in a missing value with a data point that is most similar to the one that’s missing. For example, if a library branch has a missing value, you could estimate the value from a similar branch’s records. Excel does not perform this function natively, but it is useful to know if you want to do a more advanced analysis.

Please note that these methods take a little more diligence to implement, but help preserve the trends and patterns in your data. This is in order to help you have stronger insights and more reliable estimates.

Practical Example with Tools

Let’s work through a small dataset using Excel. Imagine a monthly circulation data for a new e-resource with missing values in March and May.

We can handle the missing data with simple methods or smarter methods.

• Mean Substitution
  • Calculate the average of the available months (50 + 60 + 70 + 80) / 4 = 65
  • Fill in the missing months with 65

• Last Observation Carried Forward (LCOF)
  • Carry forward the last known value: March = 60, May = 70

• Interpolation
  • Estimate the missing values between known months:
    • March = (60 + 70) / 2 = 65
    • May = (70 + 80) / 2 = 75

• Regression
  • To perform regression, you need to convert your months into numbers. Create a new column to serve as your independent variable and assign a number to each month (e.g., January = 1, February = 2, March = 3, etc.)

• Use Excel’s FORECAST.LINEAR function
  • Formula to use: =FORECAST.LINEAR(target_month, known_circulation month, known, month_range)
    
• Example to fill in for March
  • =FORECAST.LINEAR(3,{50,60,70,80},{1,2,4,6})
  • March=63.5

Excel also has functions like AVERAGE, TREND, and MEDIAN to make these calculations quick. Google Sheets also has similar functions. By combining these approaches, you can turn your incomplete data into usable insights for library decision-making. 

Conclusion

Handling missing data does not have to be intimidating. By combining these simple and smarter approaches to data imputation, library staff can turn incomplete datasets into actionable insights. However, please keep in mind that it is important to remember that imputation is essentially making educated guesses. You are filling in gaps where data does not exist. You must be transparent about this because values that have been imputed reflect uncertainty in the dataset. Thoughtfully applied, imputation is a powerful tool to support forecasting and improve overall decision-making. And this all  can be done without misleading stakeholders. So even if you have imperfect data, these techniques allow libraries to make more informed choices and plan more effectively for the future.

Related Posts