Office Tips & Tricks: Unmerge and Fill Cells in Excel

Office Tips & Tricks: Unmerge and Fill Cells in Excel

Unmerge and fill

 

I’ve recently discovered a great time saving trick in Excel so wanted to share it with you.

 

So I have spreadsheet with lots of merged cells. In the example below, I merged cells in ‘Module name’ and ‘Event type’ columns. It looks good visually – repetitive information is grouped and I also didn’t have to type module names and event types in each row.

merged

 

But then I realise that I need to sort my spreadsheet by column ‘Day’ or ‘Time’. Or to create a Pivot table. None of these will be possible while I have merged cells.

If I unmerge them I will have a lot of blank cells.

 

Blank

 

For this amount of data, I can simply copy and paste data to fill in the blanks. But what if my spreadsheet contains lots of data? Copying and pasting will be very time consuming… Thankfully, there is a quicker way.

Note: The instructions below are applicable to  Excel on PCs. If you have a Mac, you need to use a slightly different approach – please see comments to this post.

 

1. Unmerge cells

  • Highlight all columns that have merged cells
  • In the Ribbon > Home tab > unclick the Merge & Center icon
  • Keep the columns highlighted

 

2. Highlight all blank cells

  • While the columns where you unmerged the cells are highlighed, click Find & Select icon in the Home tab
  • Select Go to Special
  • In the Go to Special window tick Blanks > OK

Go to special

  • You will see that only cells that are blank are highlighted

 

3. Fill the blank cells

  • While the blank cells are highlighted, type = and press the Up arrow on the keyboard
  • Then press Ctrl and Enter on the keyboard
  • All blank cells will be automatically filled in.

type in blank cell

 

4. Watch a Lynda.com tutorial

  • NTU staff can watch a Lynda.com tutorial about filling in blank cells quickly using Go to Special  -  Handling Blank Cells.
  • If you’ve never used Lynda.com, you can find out how to access it in one of the previous blog posts – Learn with Lynda.com.

 

NTU staff and students can visit the Office Central learning room to find out more about Excel and other Microsoft programs. You will need to log in to NOW to view the learning room.  (Please note: Office Central is an internal resource only accessible by NTU staff and students).

Did you find this Tip & Trick useful?

Why not let us know using the comments? And feel free to share our post with your colleagues.

About the author

Elena is responsible for design and implementation of development initiatives and learning resources to support NTU staff in their use of university systems. Prior to joining NTU, Elena worked in the British Council supporting bidding for and implementation of international development and scholarship management programmes. She has extensive experience as a Learning and Development consultant, specialising in SAP, business process analysis and managing personal and professional development schemes.


4 comments on “Office Tips & Tricks: Unmerge and Fill Cells in Excel

  • PedanticPete

    Thanks, Elena,

    What a nice little tip! Merged Cells whilst visually nice do make manipulating a sheet a pain …

    Might I suggest adding ‘On a PC’ to your title … Excel 2016 on a MAC doesn’t have the Pair of Binoculars icon for ‘Find & Select’ …

    On a Mac you need to go …

    Edit Menu | Find | Go To…
    <>
    Click ‘Special’ button
    <>
    Select ‘Blanks’ radio button …

    Obvious, intuitive and memorable NOT …

    Pete

  • Peter Bowcott

    A nice thing about this post is it has a link to a very succinct Lynda.com tutorial that helps reinforce the information for different learning styles…

Leave a comment

Your email address will not be published.

Required fields are marked with required


required

required

required