The following steps show how to remove empty columns from an Excel spreadsheet using Excel 2016. Note: this process does not account for partially empty columns. For example if a row in column 2 was empty that column would also be deleted.
To hide cells in Excel for Mac, first create your table, making sure to leave room for expansion if necessary. Next, select the first column to the right of your data. Now we need to select all columns from this starting point to the end of the spreadsheet. Because Excel gives users spreadsheets with tens of thousands of rows and columns, we’ll use keyboard shortcuts to quickly jump to the end. With the rightmost empty column selected, press and hold the Shift key and then press Command + Right Arrow. This will jump you to the end of the spreadsheet while the Shift key automatically selects every cell in between.
Now we have to tell Excel to hide these cells. With your cells still selected, go to Excel’s Menu Bar and choose Format Column Hide. You’ll now see all the cells to the right of your data disappear. Next, we need to deal with the cells below your data. Similar to the steps above, this time select the first row beneath your data.
With the bottommost cell selected, press and hold Shift and then press Command + Down Arrow. This will jump you to the very bottom of the spreadsheet and select all the rows in between.
Finally, head back up to Excel’s Menu Bar and choose Format Row Hide. You’re now left with a spreadsheet that features only cells containing your data, resulting in a much cleaner look. If you need to expand your table, or simply don’t like the new look, you can easily unhide the cells. First, click the top-left heading cell that looks like a white arrow pointing to the bottom-right corner.
Clicking this cell will select all cells in your spreadsheet, both hidden and not. Once selected, go to Excel’s Menu Bar and choose both Format Column Unhide and Format Row Unhide. Your spreadsheet will now be restored to the default look. In our example the hidden cells were empty, but these steps can also be used to hide cells that contain data. This can be useful for quickly hiding irrelevant or sensitive data during a presentation, for example.
In either case, note that hiding data in this manner is not a secure solution, and these steps should only be used to hide data for purposes of convenience.
Hi, Excellent tutorial! One question I still have. I have a sheet with 110,000 rows, with 35381 blank rows. When I try your second method 'Remove blank rows using a key column' after I select the delete row option I get the following error: 'Excel cannot create or use the data range reference because it is too complex. Try one or more of the following: - use data that can be selected in one contiguous rectangle - use data from the same sheet' obviously I'm working on the same sheet, and this method is only works because we're not selecting a contiguous rectangle. I'm wondering what my options are. Thanks in advance for your help, Francois.
Actually, I just found a great, super quick way to get rid of extra rows. 1) save the original Excel file as 'tab-delimited' text file, then.
2) open that file in notepad or TextEdit (Mac), then 3) in the text app select the whole empty row (triple click on Mac) 4) do a 'find' in the text app, paste the empty-row and click 'replace' with empty field (nothing) 5) then just click 'replace all' and boom! All those damn empty rows gone! Then just copy-paste back into Excel:) At any rate, I tried this on my Mac, and it worked like a charm, I'm not sure if Notepad on the PC has the same find-replace as the Mac's TextEdit, I assume it does, but if it doesnt then maybe you can try another basic word-processing app that allows simple find-and-replace Good luck! Again, only in case I'm not the only one. Here's what works for me: 1) Save a copy because the following process will delete the header row and you'll need it back. 2) Click in Cell A1. 3) Click Cmd+Shift+End to select to the end of used cells.
4) Go to Data and click on Filter 5) There's now a drop down arrow in every column. Click on the one at A1. 6) Everything you do in this dialog box kind of only happens to the items you can see, believe it or not. So go to the lower right of the dialog box and stretch it down as far as you can.
7) Uncheck 'Select All.' This may or may not actually deselect all. Scroll down to see if any other items are still selected. If so, try clicking on it. It selects that one and probably deselects many other items but not necessarily all of them.
The taller the dialog box, the better your chances of getting most of them. Repeat this process until you truly have nothing selected. 8) Scroll down to the bottom and check '(Blanks)'. Close the dialog box by clicking outside it anywhere.
9) Go to Edit. What used to say 'Delete' now says 'Delete Row'. This is such a big deal to Microsoft that there'll be a warning box.
Click that, too. At this point, you may notice that your header row is gone. 10) Click to the left of Row 1 and on the Home tab, click Insert to make a blank header row.
11) Open the copy you made in Step 1 and copy Row 1 to the clipboard. 12) Paste that into your new Row 1.