Sorting out empty rows

Sorting out empty rows

In the workbook named BlankCells.xlsx, we have several blank rows in the dataset. To remove all the blank rows from the workbook, follow these steps:

• Make sure you are working on the Rows worksheet.
  • Go to Find & Select | Go To Special….
    
  • Select Blanks, then click OK to see the blank rows highlighted on the worksheet.
• To delete the rows, use the Ctrl + – keys (or go to Home | Delete | Delete Sheet Rows). As we can see, there are no more empty rows in the dataset.
  

  Sorting out blank cells
  

  To highlight blank cells within a dataset, we can use the Go To Special… tool in Excel. At times, we may want to fill blank cells with a value, such as 0, so that our formula and tools work correctly when we’re manipulating data. Let’s learn how to achieve this:

  • Access the Cells worksheet.
    
  • We can use the same method that we used in the previous example to highlight the blank cells on the worksheet, then fill those cells with, for instance, a value such as 0.
  • First, add a 0 to an empty cell on the worksheet. Then, use the Copy button to copy the 0, then delete the 0 from the cell. Now, we can paste this over again if required.
  • Go to Find & Select | Go To Special….
    
  • Select Blanks, then click OK to see the blank cells highlighted on the worksheet.
  • Now, click Home | Find & Select | Replace….
    

 

• Leave the Find what field blank and enter a 0 in the Replace with field:
  

Figure 9.56 – Using Find and Replace to replace blank cells with 0

• Click Replace All, after which the cells will be filled with 0s.

There is so much more to explore here, but we hope that this will get you thinking about different scenarios where you can handle data in Excel. Now, let’s learn how to join data.

Joining data

Just like the other features within Excel, we can join data using many methods. The following table shows a few examples:

Table 9.3 – Examples of joining data in Excel 2021

When using either the & method or the CONCAT function, we can add delimiters manually for each text entry in the string. We cannot have the delimiter repeat throughout the text string automatically. The TEXTJOIN function allows a delimiter to be included automatically for each text entry in the string. The TEXTJOIN function also includes a condition that sets whether the selected range includes or excludes blank spaces in the result.

Let’s look at a few examples.

Using & to combine data

Follow these steps:

• Open the workbook named SSGJoin.xlsx.
  
• The ampersand (&) operator works well if you want to pick data from different places in the work area that you wish to include in one text string. If you have an invoice, for example, and you need to include an invoice number, the date, and the item that was purchased to form a sentence, you can use & to do so. In this example, we will combine the three items listed in the range, with a comma between each value in the string.
• Click inside cell Q2 of the worksheet, then enter the =L2&”, “&M2&”, “&N2
  

  formula to collect cells. Add delimiters where appropriate.

  Let’s break down the formula:

• Click inside cell Q2, then press the = sign to start the formula.
• Click inside cell L2 to collect the Initial Consultation values.
  
• Press the & key on your keyboard.
• As we need to include a comma and a space in between the two text strings, we need to include this before we click on the next item. Type an inverted comma, a comma, a space, and close with another inverted comma; that is, “, “.
• Type another &, then click on the next item – that is, cell M2.
• Repeat the process to type an inverted comma, a comma, a space, and close with another inverted comma as follows: “, ” to add a comma and a space after the second item.
  
• Press the & key on your keyboard.

• Click on cell N2 to complete the formula:

Figure 9.57 – The & formula’s construction

• Press Enter to confirm and display the formula’s result:

Figure 9.58 – The result of using the & formula

The CONCAT function is very similar to the & method. You can collect cells and add the relevant delimiter in between each value of the string if required.