Paste Special to Transpose
You've got a bunch of rows. You want them to be columns. Or vice versa. You would go nuts moving things cell by cell. Copy that data, select Paste Special, check the Transpose box, and click OK to paste into a different orientation. Columns become rows, rows become columns.
Multiple Cells, Same Data
For some reason, you may have to write the same thing over and over again in cells in a worksheet. That's excruciating. Just click the entire set of cells, either by dragging your cursor, or by holding the Ctrl key as you click each one. Type it on the last cell, then hit Ctrl+Enter (not Enter alone)—what you typed goes into each cell selected.
This also works with formulas, and will change the cell references to work with whatever row/column the other cells are in.
Paste Special with Formulas
Let's say you've got a huge amount of numbers in decimal format you want to show as percentages. The problem is, that numeral 1 shouldn't be 100%, but that's what Excel gives you if you just click the Percent Style button (or hit Ctrl-Shift-%).
You want that 1 to be 1%. So you have to divide it by 100. That's where Paste Special comes in.
First, type 100 in a cell and copy it. Then, select all the numbers you want reformatted, select Paste Special, click the "Divide" radio button, and boom goes the dynamite: you've got numbers converted to percentages. This also works to instantly add, subtract, or multiply numbers, obviously.
Work With Cells Across Sheets
This one, called 3D Sum, works when you have multiple sheets in a workbook that all have the same basic layout, say quarterly or yearly statements. For example, in cell B3, you always have the dollar amount for the same corresponding week over time.
On a new worksheet in the workbook, go to a cell and type a formula like =sum('Y1:Y10'!B3). That indicates a SUM formula (adding things up) for all the sheets that are titled Y1 to Y10 (so 10 years' worth), and looking at cell B3 in each. The result will be the sum of all 10 years. It's a good way to make a master spreadsheet that refers back to ever-changing data.
you have any query or want any help related to excel feel free to join our Telegram channel and message us..
Channel link :
0 Comments