Tips To Become Excel Pro #3

 If you have already gone through "Tips To Become Excel Pro" part #1 and #2, you are probably looking for part #3. You are at the right place where I welcome you to the Last part of becoming Pro In Excel

Screenshot Insertion

Excel makes it ultra-easy to take a screenshot of any other open program on your desktop and insert it into a worksheet. Just go to Insert tab, select Screenshot, and you'll get a drop-down menu displaying a thumbnail of all the open programs. Pick one to insert the full-sized image. Resize it as you desire.

Insert Excel Data Into Word

Thirty-five years ago, the thought of placing data from Excel into Word or PowerPoint was mind-blowing in the world of Office Suites. Today, there's nothing to it. Whether you're taking data cells or a full-blown graphical chart, copy and paste into the other program. The thing to be aware of is, this is a link-and-embed process—if you change the data in the spreadsheet, it'll change in the Word DOC or PowerPoint PPT, too. If you don't want that, paste it as a graphic. Use Word's own Paste Special tool for that. Or, when taking it from Excel, go to the Home tab at top, select the Copy menu, and use the Copy as Picture option. Then you can paste the graphic into any program at all.

Use $ to Prevent Shift

When you write a formula, you reference cells by their position, such as A1. If you copy a formula and paste it in the next cell down, Excel will shift that referenced cell, so it would say A2 instead. To prevent shifting, use the dollar sign ($). Type $A1 and cut and paste it to a new cell, for example, which prevents a shift in the column (A); A$1 prevents the shift in the row (1), and $A$1 prevents the shift change in any direction when copying a formula.

This is handy when you have a single cell to use in a whole bunch of formulas. Say you want to divide everything by 100. You could do a formula like =(A1/100), but that means you can't change the 100 easily across the board. Put the 100 in cell B1 and use =(A1/B1)—but then when you cut and paste it down, it turns to =(A2/B2), then =(A3/B3), etc. The $ fixes that: =(A1/$B$1) can be cut and pasted down a row, but the $B$1 reference never changes. Then you can change the value of 100 in the cell as needed to experiment with other changes.

Perform Quick Analysis

If you don't know exactly what info you'd like to apply to data in Excel, try the Quick Analysis menu to run through options quickly. Select the data and click on the Quick Analysis box that appear on the lower right. You'll get a menu that pops up with options to swiftly apply conditional formatting, create charts, handle totals, show spark lines, and more.

Great Excel Shortcut Keys

Excel, like any great software, has many excellent keyboard shortcuts. Here are some of the best.

Ctrl+; —Inserts today's date.
Ctrl + Shift+:—Inserts the current time (the colon is what is in a clock reading, like 12:00).
Ctrl + Shift+#—Changes the format of a date.
Ctrl+5—Applies a strike-through to the text in a cell.
Ctrl+0— Hides the current column.
Ctrl+9—Hides the current row.
Ctrl+F6—Switches between open workbooks (that is, open Excel files in different windows).
Ctrl+`—That's the accent mark, up by the 1 key. This combo toggles the view in the sheet to show all the formulas.
Ctrl+PageUp or PageDown—Quick shift between the sheets in the currently open workbook.
F2—Start editing the current selected cell (much faster than double-clicking).
Shift+F10—Opens the right-click menu for the cell you're in.

Quickly Add Without Formulas

Got numbers in a spreadsheet you want a quick calculation on, without the hassle of going to a new cell and creating a SUM formula for the job? Excel now offers a quick way to do that. Click the first cell, hold down the Ctrl key, and click a second cell. Look at the status bar at the bottom and you'll see the sum of the cells calculated for you.

Keep your finger on Ctrl and click as many cells as you like, the status bar will continue to show the sum for all the cells. (Click a cell with letters/words as the content, it gets ignored.) Better yet, right click the status bar to get the Customize Status Bar menu and you can choose to add other elements that can be quick-calculated like this, such as seeing the average or count of how many cells you clicked (or the numerical count, which is how many cells you clicked that actually have numbers).

Freeze Headers for Scrolling

Working with a massive data set in a spreadsheet can be difficult, especially as you scroll up/down or left/right and the rows and columns may be hard to track. There's a simple trick for that if you've got a header row or column, where the first row/column has a descriptor. You freeze it so when you scroll, that row and/or column (or multiple rows and/or columns) don't move.

Go to the View tab and look for Freeze Panes. You can easily just freeze the top row (select Freeze Top Row) or first column (select Freeze First Column). You can do both at once by clicking the cell at B2 and just selecting Freeze Panes. This is where it's fun—select any other cell and also Freeze all the panes above and left of it. Select cell C3 for example and the two rows above and two columns to the left won't scroll. You can see it in the screenshot above, indicated by the darkened grid lines.

When you want to get rid of the freeze, you can just select Unfreeze Panes from the menu.

New Window for Second View

Spreadsheets can be enormous, and you may have to interact with different areas of the spreadsheet at what seems like the same time, such as cutting and pasting info from the top to the bottom over and over. If it's hundreds of thousands of cells, the scrolling that would take could make you nauseous.

Or, you could just open a second window on your desktop with a view of exactly the same spreadsheet. It's easy. In the View tab, click New Window. You can also click Arrange All to get them ordered on screen in a way that works for you. You can see them Horizontally arranged above. Then, type something into a cell in one window, you can see it appear in the other window. This trick is especially handy if you've got dual monitors.

BE SURE TO CHECK THE FIRST PART 

CLICK HERE -- Tips To Become Excel Pro #1

BE SURE TO CHECK THE SECOND PART

CLICK HERE -- Tips To Become Excel Pro #2