Microsoft Excel Tutorial: "My spreadsheet must be haunted" is a great opening line to an Excel question sent in just before Halloween. You have 1510 rows in Excel. You try to Delete Sheet Rows from A1511 to A1048576, but the rows keep coming back.
Welcome to the MrExcel Podcast, where we dive into all things Excel. In today's episode, we're tackling a spooky problem how to get rid of those pesky blank rows at the bottom of your Excel worksheet. It's a common issue that can be frustrating to deal with, but fear not, we have a solution for you.
So, you've tried the usual method of selecting the rows and hitting "Delete Sheet Rows", but those blank rows just won't budge. It's almost as if your worksheet is haunted. But don't worry, you're not alone. Many Excel users have faced this issue and we're here to help you solve it.
First, let's clarify that you don't actually have a million rows of data. You only have 1510 rows of data and you don't want to see anything else. So, here's what you can do start by selecting cell A1511 and then hit Control+Shift+Down Arrow to select all the rows below it. Next, go to the Home tab, click on Format, then Hide and Unhide, and select "Hide Rows". This will hide all the blank rows below your data. Repeat the same process for columns J and beyond by selecting cell J and hitting Control+Shift+Right Arrow, then hiding the columns. Voila! Your data now goes from A through I with no white space to the right and no blank rows at the bottom.
But what if you need more rows in the future? No problem. Simply select a cell in the middle of your data and hit Alt+I+R or go to the Home tab, click on Insert, and select "Insert Sheet Rows". This will add more rows without activating the blank rows at the bottom. And if you need multiple rows, just repeat the process. It's that simple.
Now, you may be wondering, what's the harm in just leaving those blank rows there? Well, aside from being an eyesore, it can also cause issues with your data analysis and calculations. So, it's best to get rid of them. And don't worry, this trick may not be in my book, MrExcel 2021, but there are plenty of other useful tips and tricks in there. Click the "I" in the top right corner for more information.
If you found this video helpful, please give it a thumbs up and subscribe to our channel for more Excel tips and tricks. And don't forget to ring the bell to be notified of our latest uploads. If you have any questions or comments, feel free to leave them down below in the YouTube comments section. Thanks for tuning in and we'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/likemrexcel...
Table of Contents
(0:00) Delete rows & they are still there?
(1:20) The solution
(2:10) Adding more rows
(3:09) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets
This video answers these common search terms:
Alt+E D R not deleting rows in Excel
Controlling row and column visibility in Excel
Deleting rows in Excel
Hiding columns in Excel
Hiding rows in Excel
How to delete blank rows in Excel
Inserting rows in Excel
How can you delete all of the whitespace at the bottom of Excel?
How can you delete unused rows at the bottom of Excel?
How can you delete unused columns to the right of your Excel data?
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Note: If you arrived at this video because Excel thinks you have a million rows of active data but you only have 100 rows, then you need to follow the steps in this video instead: • Excel Fixing Excel's Vertical Scrol...