Let's be honest - working on spreadsheets can be very tedious! That is why it is good to know the little ways you can speed up your work by taking advantage of certain features. In this post, I want to cover two of the most useful, but possibly least known features that can really help when you deal with a lot of data.
PASTE SPECIAL - OPERATION
Most people know about the more common uses for Paste Special - copying only the formula, format, or values of one cell to another cell or range. Perhaps lesser known is one of the better features of Paste Special: Paste Special - Operation. It is an invaluable tool if you have to convert a range of numbers.
For example, let's say you have a column of numbers that range from 100,000 to 1 million. You want to show the numbers as thousands so your spreadsheet is easier to understand and looks less cluttered. Here's what you do:
Enter 1000 in a blank cell.
Select the entire range of cells that you want to divide by 1000.
Choose Paste Special Divide
The entire range of numbers will now show as Thousands.
If you want to learn more about other ways you can use this feature, there is an excellent article on the Trump Excel website. The article is titled How to Multiply in Excel Using Paste Special and you can read it by clicking here.
GO TO SPECIAL
Sometimes you only want to work with specific types of cells - blank cells, objects (charts), cells with formulas, cells with constants. For example, you may want to fill all blank cells in a data table with the value 0, or color all blank cells yellow.
You could hold the control key while you individually select all blank cells with the mouse. A faster way, though, is to use Go To Special, which allows you to quickly select only the specific areas of a worksheet you want to modify.
Here is how to use Go To Special:
Press Ctrl + G to open the Go To dialog box.
Press Alt + S to open the Go To Special dialog box.
Select the type of cells you want to select.
If you chose Blanks, then all blank cells will be highlighted so you can perform your operation on all of them at once.