The 2 Most Important

Excel Functions AND
Formulas

SUMPRODUCT

AND

INDEX/MATCH

Lesson 16: General Tips on Excel Functions and Formulas

5 Short and Useful Tips

Tip 1: To enter the same value or formula in many cells of your spreadsheet, select the cells, type the value or the formula in the formula bar at the top of the screen (see picture below), press and hold the Ctrl key and click on Enter.

Tip 2: You can write more than one line of text in a cell. To force a line break within such a cell, press and hold the Alt key and click on Enter.

Tip 3: To copy an Excel formula all the way down a column you can use the Autofill handle which is located in the right lower corner of the selected cell and which will show as a plus sign when you set the mouse over the corner of the cell. So, if you have numbers in cells A1 to A1000 and other numbers in cells B1 to B1000, write a formula in C1 (ex. =A1+B1), click Enter, re-select cell C1 move your cursor toward the right bottom corner of the cell and when it changes to a bold " +" sign, double-click). Automatically, the formula will be pasted in cells C2 to C1000.

Tip 4: Within a formula you can even refer to a workbook that is on the Internet like:
='http://www.excel-examples.com/[excel-online.xls]Sheet1'!D8

Open a new workbook copy/paste the formula above and close it. When you re-open it the result is $1,729.75. This value comes from the workbook  "excel-online.xls" that is on the Internet. This means that you can maintain a master report on the Internet and send a copy with formulas to your users. When you make a change to the master report all users see the refreshed results in their workbook with formulas.

Tip 5: When you copy/paste a formula from one workbook to another you take the risk of creating useless links between the workbooks. Select the formula from the formula bar without the equal sign, copy, CLICK ENTER, go to the destination cell, enter an equal sign and then paste.

Use the same approach to copy part of a formula from one cell to the other or to copy a formula without changing the addresses in it. But remember...copy....CLICK ENTER...paste.

Excel Functions and Formulas Sitemap