1. Select All Cells on the worksheet by clicking the Select All button.
2. Remove any formulas by choosing "Paste Special" from the Paste menu:
You can also access the copy and paste special commands various ways other than using the menu. Paste Special can also be used to transpose data.
To make them appear, select the two rows where the gap occurs, right click, and choose "Unhide.
4. Delete rows and columns of unwanted data
Click on the column (or row) heading, right click, and choose "Delete." This will delete the entire column (or row) and not leave empty columns (or rows) of data. You will want to delete any empty rows of data between years the same way… leaving them in will impact your graph.
5. Next look for the first cell in the “Year” column that has a green triangle in the upper left corner. Select that cell and all cells below it. Click on the drop-down box with the exclamation point. Choose <Convert to Number>
6. The result should be a spreadsheet which is much easier to work with!