How to select cells with data
Author: Oscar Cronquist Article last updated on April 10, 2018
The picture above shows data in column B, some cells contain nothing, they are blank.
I will now go through the steps on how to select cells containing data excluding blank cells and header cell.
- Select cell B3
- Press CTRL + SHIFT + END keys to select all values including blanks.
- Press function key F5
- Press with left mouse button on "Special..." button located at the bottom left corner of the dialog box.
- Double press with left mouse button on "Constants"
This will select all cells that contain numbers, text, boolean and error values.
Note that cells containing a space character will be selected as well.
Excel basics category
Convert column number to column letter
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Create a numbered list ignoring blank cells
The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]
The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]
How to add a macro to your Excel Quick Access Toolbar
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]
How to find blank cells
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
How to find errors in a worksheet
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
How to format numbers as text
A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to […]
A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to […]
How to quickly select a cell range
Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]
Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]
How to quickly select a non contiguous range
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]
How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]
How to replace part of formula in all cells
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
How to select and delete blank cells
This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]
This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]
How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]
Improve worksheet readability
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]
Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]
Rotate text to save space
If your cell text is taking to much space Excel allows you to rotate text in any angle. Here are […]
If your cell text is taking to much space Excel allows you to rotate text in any angle. Here are […]
Excel formula categories
AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values
Excel categories
Latest updated articles.
More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.
More than 1300 formulas organized in subcategories.
Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.
Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.
Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.
Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.
Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.
The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.
An Excel feature that lets you visualize data in a graph.
Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.
Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.
VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.
A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.
UDF stands for User Defined Functions and is custom built functions anyone can create.
A list of all published articles.
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
