Excel Chapter 8A of 24: Tips on Excel Functions and Formulas

There is even one function that Excel doesn't offer you when you click on the "Insert Function" icon, it is DATEDIF that allows you to calculate the number of days, months or years between two dates.

To copy an Excel formula all the way down a column you can use the Autofill handle located in the right lower corner of the selected 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. There is a viewlet in the library. Click on the green star.

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 and press Ctrl/Enter.

To write more than 1 line of text in a cell go to the menu bar "Format/Cell/Alignment" , check "Wrap Text" choose "Top" as vertical alignment. To force a line break within such a cell, use "Alt/Enter" .

If on opening your workbook Excel tells you that there are links to other Excel spreadsheets these links can be in formulas (formulas that you have copied from another workbook). Links can also be source data for a chart or a pivot table that you have imported.

A formula must not be longer than 1,024 characters.

Always write your functions in lower case letters Excel will change the case if the spelling is right. If the case doesn't change you will know that the spelling is wrong.

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 without the equal sign, copy, CLICK ENTER, go to the destination cell, enter an equal sign and the 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.

Try to develop a single Excel formula that you will copy/paste in an entire table. To do so, you have to become good at using relative or absolute references. Sometimes you need a relative reference (B4) sometimes you need an absolute reference (B$4$) and sometimes you need something in between (B$4, B4$). To add the "$" , click on the address in the formula bar and use the "F4" key once, twice or three or four times as you need.

You don't need to key in all the characters of your formula. Enter an equal sign "=" in cell A2 and then select cell A1 with your mouse. Click enter and the formula is =A1. In cell A2, key in "=A1+" and then select cell A2 with your mouse. Click enter and the formula is =A1+A2. Key in "=SUM(" in cell A5 then select cells A1 to A4 holding the left button of the mouse. Click enter and the formula is "=SUM(A1:A4)" . This tip becomes very handy when you need to refer to cells on another worksheet or in another workbook. Enter an equal sign "=" in cell A1 of "sheet1" and then select cell A1 of "Sheet2" with your mouse. Click enter and the formula is =Sheet2!A1. This tip will become a regular practice when you start working with powerful formulas like SUMPRODUCT and INDEX/MATCH.

I use as many parenthesis as I need to make my formulas easy to read and to make sure that Excel calculates what I need to be calculated. For example: If you have 1 in cell A1, 2 in cell A2 and 3 in cell A3 this formula "=A1+A2*A3" in cell A4 will result in 7 as this formula "=(A1+A2)*A3" will result in 9. Excel multiplies and divides and then sums and subtracts. To make sure that Excel does what I want it to do and to make my formulas easy to read I use a lot of parenthesis. Extra parenthesis are not considered an error by Excel. The second formula above could be entered as "=(((((A1+A2))*A3)))" as long as you have pairs parentheses ().

If the name of the sheet that you are referring to contains spaces or special characters Excel adds or you need to add apostrophes before and after the sheet's name "='two& two'!A1" or "='two two'!A1"

If you refer to a cell or a range in another workbook the name of the workbook is in between square parenthesis and the workbook name and sheets name are together surrounded by apostrophes like : ='[Second book.xls]Sheet1'!$A$1

With a formula, you can even refer to a workbook that is on the Internet like:
='http://www.excel-vba.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 the same value than the value of cell D8 of sheet1 of excel-online.xls that is on my server. Now download the spreadsheet that feeds the report. If I'd make changes on "excel-online.xls" that is sitting on my server, your workbook would be automatically refreshed.

About circular references: A formula cannot refer to the cell within which it resides or to a cell that is used in calculating its own value. If A1's formula is =A2+A3, A3 cannot be =A1. There are 2 ways around a circular reference, a macro (VBA) or the iteration functionality.

Next Chapter: Naming Fields in Excel

Excel Table of Contents
or use the quick links below

Discover Even More in 50 Excel spreadsheets

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Tips on Excel Functions and Formulas