Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below.
We recently updated our Terms and Conditions for TechRepublic Premium. By clicking continue, you agree to these updated terms.
Invalid email/username and password combination supplied.
An email has been sent to you with instructions on how to reset your password.
By registering, you agree to the Terms of Use and acknowledge the data practices outlined in the Privacy Policy.
You will also receive a complimentary subscription to TechRepublic’s News and Special Offers newsletter and the Top Story of the Day newsletter. You may unsubscribe from these newsletters at any time.
Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).
3 ways to quickly deal with blank cells in an Excel sheet
Your email has been sent
Filling blanks isn’t difficult at all if you apply one of these three tips. They’re quick and easy!
Blanks in a Microsoft Excel spreadsheet aren’t always wrong, but they can throw a monkey wrench into expressions, analyzing and reporting. I don’t recommend leaving blanks unless you have specific reason for doing so. Blanks can be troublesome and downright destructive because some functions evaluate blanks differently than values. In this article, I’ll show you three quick and easy ways to deal with blank cells:
I’m using Microsoft 365, but you can use earlier versions. Methods two and three won’t work in Excel Online because there’s no Go To Special feature. For your convenience, you can download the .xlsx and .xls demonstration files.
SEE: 83 Excel tips every user should master (TechRepublic)
Blank cells are easy to miss if you’re working with a lot of data, so you might want to highlight them. The best way to do so is with a conditional format. As you can see in Figure A, at least one cell in each column is blank. In such a small sheet, you’d quickly see them all, but in a busy sheet with many rows and columns, blanks are easy to miss unless you do something to make them stand out, which is what we’ll do now:
Figure A
Figure B
While this visual clue is helpful, you might not want blank cells at all. That means filling them with some kind of value.
Sometimes you’ll want to fill blanks with a specific value. For instance, the Accounting format enters a dash when you enter a 0, but that’s not exactly the same thing as filling a blank with a specific value. When this is the case, use the Go To dialog as follows:
Figure C
Figure D
This isn’t a dynamic solution. As you update data and add more blanks, you’ll need to run this quick task again. In this example, you entered a specific value, but that won’t always be what you want. In the next section, we’ll use same method to repeat the value above the blank cell.
Depending on where you receive your data, you might find that some blank cells should contain the value in the cell above. When this happens, you’ll probably want to fill in those blanks right away. If you have only a few, use the fill handle. If you have several, the fill handle is too time consuming. We’ll continue to work with the same data, even thought it’s a bit contrived within this context. Specifically, we’ll use the Go To feature to fill the missing values, but this time, we’ll enter a formula instead of a specific value:
Figure E
Figure F
At this point, you have a mixture of literal values and expressions. If you sort, those expressions won’t repeat the right data. You might think that won’t ever happen, but I do recommend that you change those expressions to literal values, just in case. It’s easy to forget that you’re working with a mixture of literals and expressions, and you might end up with erroneous data. Fortunately, this task is quick and easy:
Figure G
These three techniques aren’t interchangeable; the way you’re using your data will determine which method you choose.
3 ways to quickly deal with blank cells in an Excel sheet
Your email has been sent
Your message has been sent
Google Tasks has never had a solid interface … until now. Jack Wallen shows you how to turn Tasks into a kanban board-like workflow with the help of TasksBoard.
Don’t pack your bags and move to Bozeman yet. This micro-scale warp bubble won’t be capable of propulsion, but it could have myriad other applications, says its discoverer Dr. Sonny White.
Based on job demand, Python is the top language, followed by Java, JavaScript, C++, C# and C, says CodingNomads.
If you’re not certain whether your Java project is free from Log4j vulnerabilities, you should try this easy-to-use scanning tool immediately.
If you missed in-person tech events in 2020 and 2021, here’s some good news: It’s looking promising that in 2022 many conferences will get back to (almost) normal.
The comprehensive bundle covers a range of subjects, including app development, game development, machine learning, computer vision, deep learning, NFTs, Java and website building.
When computer systems fail, business grinds to a halt, and that can cost your enterprise a considerable amount of money. With the help of TechRepublic Premium’s Systems Downtime Expense Calculator, you can estimate just how much each system failure is going to cost you. When to use this tool Even if the entire business does …
Results from the latest TechRepublic Premium survey suggest that businesses continue to deploy multicloud solutions, but the IT pros who support them are uncertain how they’ll change in the future. From the introduction: Heading into the third year of the COVID-19 global pandemic, industries, businesses, consumers and the information technology professionals who support them are …
This TechRepublic Premium download includes a checklist for PC and Mac migrations to minimize frustrations and improve productivity. Regardless of the process, there are several elements to track when replacing an existing computer with a new system. Whether your organization begins by imaging drives or manually configures new systems, many questions must be addressed, including: …
The Microsoft 365 Services Usage Policy from TechRepublic Premium defines the practices and behaviors that organization representatives must follow when using any organization-provided Microsoft 365-powered application, site or service. Whether organization representatives access Exchange-hosted email, applications, SharePoint portals, Teams sites or OneDrive cloud file storage using an organization-provided, organization-reimbursed or even personal Microsoft 365 services …