To Count blank or Non-blank cells in a range that contains values, use the COUNTA function.

When counting cells, you may choose to disregard any blank cells since only cells with values are significant to youyou can understand this with excel tutorial videos. For instance, suppose you wish to determine the total number of salesmen who made a transaction (column D). COUNTA ignores the blank values in D3, D4, D8, and D11 and counts only the cells in column D that contain values. The program finds six cells in column D that have values and returns 6 as the result.

Fastest Fill, Flash Fill Alive

Flash Fill will intelligently fill a column depending on the data pattern it observes in the first column (it helps if the top row is a unique header row). Start typing if the first column has all phone numbers that are formatted as “2125034111” and you want them to all appear like “(212)-503-4111.” Excel should identify the pattern and display what it believes you want by the second cell. To utilize them, simply press enter. This applies to numbers, names, dates, and so forth. If the second cell doesn’t give you an appropriate range, try again—the pattern may be difficult to discern. Then, on the Data tab, press the Flash Fill button. Look at this Microsoft.

To select, press Ctrl+Shift.

There are many faster ways to choose a dataset than moving the cursor with the mouse, especially in a spreadsheet with hundreds of thousands of rows or columns. Hold down Ctrl+Shift and click on the first cell you wish to choose, then use the down arrow to obtain all the data in the column below, the up arrow to get all the data above, or the left or right arrow to get everything in the row (to the left or right, of course). When you combine the instructions, you may receive an entire column as well as everything in the rows to the left and right. It will only choose cells that have data (even invisible data).

Columns with Text

Assume you have a column full of names, first next to last, but you want two columns that separate them. Select the data, then click Text to Columns on the Data tab (at the top). Choose between delimiters (based on spaces or commas—ideal for CSV data values) and a defined width to divide them. When all of the data is jammed into the first column but divided by a defined number of spaces or periods, fixed-width is used. The rest is magic, with additional possibilities for specific numbers.