• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Go To Special is Very Special Indeed!

You are here: Home / Excel / Excel Go To Special is Very Special Indeed!
Excel Go To Special is Very Special Indeed!
April 3, 2012 by Mynda Treacy

I can’t believe I haven’t already written a tutorial about Excel’s Go To Special feature. It is one of my favourite (a.k.a. favorite if you’re in the U.S.) closely behind VLOOKUP.

If you import data from other systems you’ll invariably have to manipulate it to get it the way you need before you can analyse it.

And data interspersed with blank cells/rows is one of the most common problems.

Thankfully there is a dead easy tool you can use to get rid of these, like the blank rows below, in seconds.

Excel GoTo

How to Remove Blank Rows with Go To Special

  1. Select range of data containing spaces. Taking the example above just the cells A1:A30 will do (row 30 is the end of my table), you don’t need to select the whole table.
  2.  

  3. CTRL+G to open the Go To dialog box.
  4.  
    Excel GoTo

  5. Click the ‘Special…’ button and select ‘Blanks’ > click OK.
  6.  
    Excel GoTo Special

  7. Your table should now look like this with the blank cells selected.
  8.  
    Excel Go To

  9. Now you can right-click your mouse and choose Delete > Entire Row.
  10.  
    Excel Go To

  11. Voila, blank rows are gone.
  12.  
    Excel Go To Special

Now you know about the Go To Special tool why not take a look at some of the other criteria you can Go To.

Like Go To Special > Objects.

Excel Go To Special Objects

I use Shapes quite a bit and Grouping them together makes them easier to handle. A quick way to select them all so they can be Grouped is to use Go To Special > Objects.

And then there’s Go To Special > Conditional formats. This makes it quick and easy to edit cells containing Conditional Formats.

If you liked this tool then you'll probably like Text to Columns too. It's great for splitting data from one column to multiple, which is often required when you import data from other systems.

Excel Go To Special is Very Special Indeed!

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.
Category: Excel
Previous Post:Excel EOMONTH FunctionExcel EOMONTH Function
Next Post:Invest $x at 4.75% for 5 Years to Make $15,000Invest $x at 4.75% for 5 Years to Make $15,000

Reader Interactions

Comments

  1. Namrata

    May 13, 2013 at 10:22 pm

    Hi,
    How to add the intermitant text of one cell in a column to below cells of the same column?

    Reply
    • Mynda Treacy

      May 13, 2013 at 11:06 pm

      Hi Namrata,

      I’m sorry I don’t understand your question. Are you able to send me an example Excel file and specific instructions on what you’re trying to do?

      Thanks,

      Mynda.

      Reply
  2. Jaz

    May 9, 2013 at 1:26 am

    Hi,

    How do I eliminate all empty rows (even the ones that come after my data rows)? I’m trying to import names and addresses into my database program and all the empty rows (after my data rows) are there being imported. I just want my excel sheet to contain rows with data and not empty rows. Thanks in advance for your help. Jaz

    Reply
    • Mynda Treacy

      May 9, 2013 at 9:17 pm

      Hi Jaz,

      Excel by default has thousands of rows. Your database isn’t importing blank rows, they’re already there. If you really want to remove them then you can hide them – they’re still there, you just can’t see them.

      My advice is to get used to them. They’re on every sheet. You’ll be wasting your time hiding them every time you set up a new worksheet.

      Kind regards,

      Mynda.

      Reply
  3. Peter

    April 16, 2013 at 7:27 pm

    can you please tell me how to get rid of all the entries in the go to dialog box? they just seem to accumulate and can’t declutter them; thank you!

    Reply
    • Mynda Treacy

      April 16, 2013 at 8:11 pm

      Hi Peter,

      Those ‘entries’ are the named ranges in your workbook. If you want to delete them you have to delete the named ranges in the Name Manager, which you’ll find on the Formulas tab of the ribbon.

      Note: you should check the names aren’t in use before you delete them otherwise you’ll break something you might later find you need.

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x