Blogs

2015/04/20

Excel-erate Your Spreadsheet Skills

Sorting and Filtering Worksheets with Merged Cells 
To use Sort, Filter or some other functions, you sometimes need to unmerge cells and replace the data from merged cells first. 

Unmerge all merged cells

1. Select all cells in the worksheet.
2. On the Home tab, in the Alignment group, click Merge & Center.

To Replace the Gaps in the Cells

1. Select the range that has the gaps
2. On the Home tab, in the Editing group, choose the Find & Select drop-down list and then click Go To Special 
3. In the Go To Special dialog box, select the Blanks option and click OK
   
   This action selects the blank cells in the original selection.
   
   
4. On the Formula bar, type an equal sign (=) followed by the address of the first cell with an entry in the column and press Ctrl+Enter
5. Reselect the original range and press Ctrl+C to copy the selection.
6. On the Home tab, in the Clipboard group, choose the Paste drop-down list and then click Paste Values to convert the formulas to values.

Now the gaps are filled with the correct information and you can continue to filter or sort, etc., on the data.


Submitted by Claire Morris, IT Training Team Lead