Tips To Become Excel Pro #1

There are very, very few people on planet Earth who could ever say they've completely mastered every intricate little thing about Microsoft Excel. It's the world's premiere spreadsheet application, and has been the industry standard for over 35 years, replacing the once-venerable Lotus 1-2-3, the first killer app for PCs in the 1980s

Paint Cells to a New Format

Let's say you change not only the wrapping in a cell, but also 

the entire look—the font, the color, whatever. And you want to apply it to many, many 

other cells. 

The trick is the Format Painter tool, the one that is on the Home tab that 

looks like a paint brush.

Select the sell you like, click the icon, and then click on a different 

cell to paint in the format—they'll match in looks, not in content. 

Want to apply it to multiple tabs? Double-click the paint brush icon, then

click away on multiple cells.

Line Breaks and Wrapping Text

 

Typing into spreadsheet cells can be frustrating, as the default  for text you type is to continue on forever, without wrapping back down to a new line. You can change that. Create a new line by typing Alt + Enter (hitting Enter alone takes you out of the cell). Or, click the Wrap Text option under the Home tab at the top of the screen, which means all text wraps right at the edge of the cell you're in. Resize the row/column and the text re-wraps to fit. If you've got multiple cells that have text overruns, select them all before you click Wrap Text. Or, select all the cells before you even type in them and click Wrap Text. Then whatever you type will wrap in the future.

AutoFill Your Cells

This is a no-brainer, but so easily overlooked. You start typing a series of repetitive things like dates (1/1/20, 1/2/20, 1/3/20, etc.) and you know you're in for a long day. Instead, begin the series and move the cursor on the screen to the lower-right part of the last cell—the fill handle. When it turns into a plus sign (+), click and drag down to select all the cells you need to fill.They'll magically fill using the pattern you started. It can also go up a column, or left or right on a row.

Even better—you can Auto Fill without much of a pattern. Again, pick a cell or cells, move to the fill handle, right-click, and drag. You'll get a menu of options. The more data you input at first, the better the Fill Series option will do creating your AutoFill options.

 

Flash Fill, Fastest Fill Alive 

Flash Fill will smartly fill a column based on the pattern of data it sees in the first column (it helps if the top row is a unique header row). For example, if the first column is all phone numbers that are formatted like "2125034111" and you want them to all look like "(212)-503-4111," start typing. By the second cell, Excel should recognize the pattern and display what it thinks you want. Just hit enter to use them.This works with numbers, names, dates, etc. If the second cell doesn't give you an accurate range, type some more—the pattern might be hard to recognize. Then go to the Data tab and click the Flash Fill button.


Ctrl + Shift to Select

 There are much faster ways to select a dataset than using the mouse and dragging the cursor, especially in a spreadsheet that could contain hundreds of thousands of rows or columns. Click in the first cell you want to select and hold down Ctrl + Shift, then hit either the down arrow to get all the data in the column below, up arrow to get all the data above, or left or right arrow to get everything in the row (to the left or right, of course). Combine the directions, and you can get a whole column as well as everything in the rows on the left or right. It'll only select cells with data (even invisible data).If you use Ctrl + Shift + End, the cursor will jump to the lowest right-hand cell with data, selecting everything in between, even blank cells. So if the cursor is in the upper-left cell (A1), that's everything. Ctrl + Shift + * (the asterisk) might be faster, as it will select the whole contiguous data set of a cell, but will stop at cells that are blank.

Text to Columns

 

Say you've got a column full of names, first next to last, but you want two columns that break them out. Select the data, then on the Data tab (at the top) click Text to Columns. Choose to separate them by either delimiters (based on spaces or commas—great for CSV data values) or by a fixed width. Fixed width is utilized when all the data is crammed into the first column, but separated by a fixed number of spaces or period. The rest is like magic, with extra options for certain numbers.

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

 

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.

BE SURE TO CHECK THE SECOND PART 
CLICK HERE -- Tips To Become Excel Pro #2

BE SURE TO CHECK THE THIRD PART

CLICK HERE -- Tips To Become Excel Pro #3