Safely Delete a Worksheet in Excel November 12, 2009
Posted by computertrainer in Excel Tips.Tags: delete worksheet, Excel
add a comment
If you would like to delete a worksheet in Excel, you should first be sure that the worksheet is empty or does not contain any data that you want to save!
NOTE: There is NO UNDO for the deletion of a sheet.
To test for an empty worksheet:
- Click in cell A1 on the worksheet
- Press Ctrl + End
If you are still in A1, then the sheet is empty.
If you are in any cell other than A1, then there is or has been data in the sheet.
If you cannot see the data, then use the method described in the post “Safely Delete a Column or Row in Excel” (posted on 11/12/09) to find the data and then determine if it is okay to delete the sheet.
Read “Permanently Remove Empty Rows/Columns at the End of an Excel Spreadsheet” (posted on 7/28/09) if you don’t find any data and want to understand what has happened.
To delete the worksheet:
- Right-mouse click on the sheet tab
- Select Delete
Safely Delete a Column or Row in Excel November 12, 2009
Posted by computertrainer in Excel Tips.Tags: delete column, delete row, Excel
add a comment
Before you delete either a column or row in Excel you should be certain that it is empty. Just because you don’t see data on your screen does not mean that there is something in the column or row elsewhere in the worksheet.
To test to see if the column is empty:
- Click in row 1 of the column you want to delete
- Press Ctrl + Down Arrow
If you arrive at the bottom of the sheet (row 65,536 in Excel 2000-2003 or row 1,048,576 in Excel 2007), then the column is empty
Ctrl + Up Arrow will take you to row 1 in an empty column
To test to see if a row is empty:
- Click in column A of the row you want to delete
- Press Ctrl + the Right Arrow
If you arrive at column IV in Excel 2000-2003 or Row XFD in Excel 2007, then the row is empty
Ctrl + Left Arrow will take you to column A in an empty row
After testing and finding the column or row is empty, then it is safe to remove the column or row. The easiest method to delete the column/row is to Right-mouse click on the Column Letter or Row Number and then select Delete.
Add a Comment to a Cell October 22, 2009
Posted by computertrainer in Excel Tips.Tags: comment, Excel
add a comment
Comments are notes that you enter for a cell. You can add a comment to a cell that has constants or formulas.
- Right click the cell you want to comment on.
- Select Insert Comment.
- In the box, type the comment text.
If you do not want your name in the comment, select and delete the name. - When you finish typing the text, click outside the comment box.
By default you will see a red triangle in the upper right corner of any cell that has a comment. When you hover the mouse over a cell with the red triangle (comment), then the comment box will appear.
Standard Order of Operations in a Formula in Excel October 12, 2009
Posted by computertrainer in Excel Tips.Tags: calculations, Excel, math, order
add a comment
If you combine several operators in a single formula, Microsoft Excel performs the operations in the order shown in the table below.
If a formula contains operators with the same precedence (for example, if a formula contains both a multiplication and division operator) Microsoft Excel evaluates the operators from left to right.
To change the order of evaluation, enclose the part of the formula to be calculated first in parentheses.
Please Excuse My Dear Aunt Sally
( ) Parentheses — Calculate the values of all expressions inside parentheses or brackets first, using the standard order of operations, and working from the innermost parentheses out.
^ Exponents — Next, raise all numbers to the indicated powers.
* / Multiplication & Division — Next, do all the multiplications and divisions from left to right.
+ - Addition and Subtraction — Last, do the remaining additions and subtractions from left to right.
For example, =15-3*4 equals 3, not 48. Using the standard order of operation, 3 is multiplied by 4 giving you 12 and then that number is subtracted from 15. If you wanted an answer of 48 you would use parentheses … =(15-3)*4.
Permanently Remove Empty Rows/Columns at the End of an Excel Spreadsheet July 28, 2009
Posted by computertrainer in Excel Tips.Tags: delete empty columns, delete empty rows, Excel
add a comment
If you have entered data in columns/rows at the right or end of your spreadsheet’s data area and later deleted that added data, Excel will remember that there was once something in that column or row … and remember … and remember … until you take action.
In the example below, the data ends at D45 but when I pressed CTRL + END the mouse went to G75. At one time there was data in column G and row 75.
I would like to fix it so that CTRL + END will take me to the actual end of the data, in this case D45.
SOLUTION:
- Select the empty columns that you need to remove.
(Remember to check each column to be sure it is empty … start in E1 then press CTRL + the down arrow. If you end up at the bottom of the spreadsheet – row 65536 in Excel 97 to 2003 or 1048576 in Excel 2007 – then the column is empty. Then move over one column and press CTRL + the up arrowto continue checking – you should be in row 1. Repeat as necessary.)
In this example you need to check then select columns E, F, and G. - Right-mouse click on the selected columns and press Delete.
- Immediately SAVE.
NOTE: This is the critical step. Without pressing SAVE at this point, you might as well have not done the previous steps. - Now select the empty rows 46 to 75. Since you can see these rows over to column D (now the last column with data) you do not need to check each row to be sure it is empty.
If you want to check … just to be sure, start in A46 then press CTRL + the right arrow. If you end up in the right most column (IV in Excel 97 to 2003 and XFD in Excel 2007), then the row is empty. Go down one row and then test that row with CTRL + the left arrow. You would continue until you checked down to row 75 in this example. - Right-mouse click on the selected rows and press Delete.
- Again, SAVE immediately.
Now you can test your work.
- Press CTRL + HOME and you will be in the cell at the upper left of the active worksheet (A1 unless you have Frozen Panes).
- Press CTRL + END and you would be in D45.
Excel has finally forgotten that there was once data in row 75 and column G!
Print the ‘Field Titles’ Row at the Top of Each Page July 20, 2009
Posted by computertrainer in Excel Tips, Office 2007 Tips.Tags: Print, repeat titles
add a comment
If you have an Excel sheet that prints on more than one page, it is helpful if the titles print at the top of each page, not just on page one.
Unfortunately you cannot set this feature up from Print Preview.
For Excel 97 – 2003:
- File, Page Set Up, then the Sheet Tab.
- At Rows to repeat at top, click the red marker at the end of the area then click on the row # (#’s) you want to repeat at the top, then press Enter. You can then select Print Preview from this box to see how it works.
For Excel 2007:
- Click on the Page Layout Ribbon, then in the Page Setup group, select the Print Titles icon.
- The dialog box above in the 97-2003 instructions appears. Follow the step 2 above.
NOTE: You can also repeat columns on the left of a printed page. This is helpful is the data will not print one page wide.
Add a Comment in a Cell’s Formula October 26, 2009
Posted by computertrainer in Excel Tips.Tags: comment, Excel
add a comment
If you want to add a comment to a cell with a formula but do not want the traditional Comment (see Add a Comment to a Cell posted on 10/22/09), then follow these steps to add the comment at the end of your formula.
You will not be able to see that there is a comment to the formula unless you click the cell that the formula is in. You’ll see the comment on the Formula Bar.