How to Filter Blanks or Non-Blanks
Contents
[
Hide
]
Possible Usage Scenarios
Filtering data in Excel is a valuable tool that enhances data analysis, exploration, and presentation by enabling users to focus on specific subsets of data based on their criteria, making the overall data manipulation and interpretation process more efficient and effective.
How to Filter Blanks or Non-Blanks in Excel
In Excel, you can easily filter blanks or non-blanks using the filtering options. Here’s how you can do it:
How to Filter Blanks in Excel
- Select the Range: Click on the letter of the column header to select the entire column or select the range where you want to filter blanks.
- Open the Filter Menu: Go to the “Data” tab in the ribbon.
- Filter Options: Click on the “Filter” button. This will add filter arrows to the selected range.
- Filter Blanks: Click on the filter arrow in the column you want to filter blanks. Unselect all options except “Blanks” and click OK. This will display only the blank cells in that column.
- The result as follows:
How to Filter Non-Blanks in Excel
- Select the Range: Click on the letter of the column header to select the entire column or select the range where you want to filter non-blanks.
- Open the Filter Menu: Go to the “Data” tab in the ribbon.
- Filter Options: Click on the “Filter” button. This will add filter arrows to the selected range.
- Filter Non-Blanks: Click on the filter arrow in the column you want to filter non-blanks. Unselect all options except “Non-blanks” or “Custom” and set the conditions accordingly. Click OK. This will display only the cells that are not blank in that column.
- The result as follows:
How to Filter Blanks using Aspose.Cells for JavaScript via C++
If a column contains text such that few cells are blank, and filter is required to select those rows only where blank cells are present, AutoFilter.matchBlanks(number) and AutoFilter.addFilter(number, string) functions can be used as demonstrated below.
Please see the following sample code that loads the sample Excel file which contains some dummy data. The sample code uses three methods to filter blanks. It then saves the workbook as output Excel file.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Apply Filter for Blank Cells Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
const workbook = new Workbook(new Uint8Array(arrayBuffer));
const worksheet = workbook.worksheets.get(0);
worksheet.autoFilter.addFilter(1, null);
worksheet.autoFilter.refresh();
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'FilteredBlanks.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download FilteredBlanks.xlsx';
resultDiv.innerHTML = '<p style="color: green;">Filter applied and file ready for download.</p>';
});
</script>
</html>
How to Filter Non-Blanks using Aspose.Cells for JavaScript via C++
Please see the following sample code that loads the sample Excel file which contains some dummy data. After loading the file, call the AutoFilter.matchNonBlanks(number) function to filter non-blank data, and finally save the workbook as output Excel file.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Filter NonBlanks Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Call MatchNonBlanks function to apply the filter
worksheet.autoFilter.matchNonBlanks(1);
// Call refresh function to update the worksheet
worksheet.autoFilter.refresh();
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'FilteredNonBlanks.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Filtered Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>