 Ctrl S - My All Time Favorite Excel Tip
Summary: I often get asked what my all time favorite Excel Tip is and I always answer Ctrl S:
I have been around too long so I grew up in the computer world using the keyboard. As a result I use as many of the over 175 Excel keyboard shortcuts as possible.
Ctrl S which saves the current workbook has saved my life many times. Every couple of minutes I hit those two keys. My brain is programmed to to this.
Note: If you are interested in a list of the over 175 Excell keyboard shortcuts drop me a note at alan@k2e.ca and I will be happy to send it to you.
 Summing Visible Cells
The Sum function in Excel adds all of the cells in a column or row, even if the columns and rows are hidden. The solution to just adding the visible cells is to use the =SUBTOTAL function.
Assume that you have a column of numbers in column A from A1:A100 and that some of the cells are hidden.
To get the total for just the visible cells you would use the following formula:
=SUBTOTAL(109,A1:A100)
The formula only calculates the sum of the visible cells in the range.
Deleting Duplicates from a Dataset
- Select a cell in the dataset.
- Go to the Data Tab.
- Click on the Remove Duplicates in the Data Tools group.
- Excel will highlight the dataset. If there are columns missing in the selection, go back and make sure there are no blank columns in the selection.
- Make sure that My Data has Headers is selected if your dataset has headers.
- By default, all the columns are selected. A selected column means that the tool will use all the columns to check for duplicates. Duplicates in an unselected column will be ignored. Select the columns that you want to match for duplicates.
- Click OK. The dataset will update, deleting any duplicate rows. A message will appear to tell you how many rows were deleted and how many rows remain in the data set.
Note there is no undo for this process. Save your work before you run this process.
|
Stopping Word from Capitalizing a Word Automatically
Summary: Microsoft Word will sometimes change the capitalization of a word that you type when it shouldn't be captialized. If this is happening to you frequently here are some steps that you can take to prevent this from happening.
If you want to prevent Word from capitalizing proper names, such as Alan or Nancy, you can try the following:
- Go to the Word Options dialog box. In Word 2007 click on the Office Button and then click on Word Options. In Word 2010 , 2013 and 2016 click on the File tab in the ribbon and then click Options.
- Click Proofing.
- Click on AutoCorrect.
- Clear the Correct Two Initial Capitals check box.
- Clear the Capitalize First Letter of Sentences check box.
- Clear the Capitalize Names of Days check box.
- Then go through the list of AutoCorrect entries and delete or modify any entries that capitalize a word.
- Clear the Automatically Use Suggestions from the Spell Checker check box.
- Click on OK.
|
