What is a reference?

Excelforpoets is adamant about using references whenever possible. The word will come up over and over again, relentlessly clearing your brain of any possible negative notions you might have had concerning the word "reference". Without references, Excel is no better than a fancy calculator (and trust me, Excel is far superior to a fancy calculator). Even though this may be one of the most basic Excel concepts, it is often passed over as assumed knowledge. Excelforpoets has only one assumption: you have come here to up your game. What follows is a list of the different types of information you can put into a cell.

1. String: a string is anything that is not a number or a reference. Most strings are just plain text. A string can also be anything that you surround with " "s. The frequent use (and important nature) of " "s in Excel creates explanatory ambiguity, so as a convention, '25' or 'A3' will indicate that you should use no quotes when actually typing it into a cell, and "25" or "A3" will indicate that you should use double quotes when typing it into a cell. Glad that's out of the way.

2. Number: you know what a number is. You can perform all sorts of operations with numbers. An important thing to remember is that as soon as you add anything to a cell that is not a number, it will no long act like a number, but will become a string. For instance, you can't multiply '25 lives' by '50 lives' because they are both strings, but you can multiply '25' by '50' because they are numbers.

3. Special Numbers: Sometimes Excel acts cute and thinks it knows what you're thinking. One of these situations is when you put what Excel believes is a date in a cell, such as '4/20'. I'll talk more about the bewildering things Excel does to dates in a later post, but for now, just be aware that sometimes Excel will do strange things if you put in dates.

4. Booleans: TRUE and FALSE hold a special place in Excel's heart and are not considered mere strings (unless you type "true" or "false"). Despite Excel's protests, we are going to ignore them until we explore the IF function, and conditional arrays later on.

5. References: The beauty of Excel is in reference text. The spreadsheet is set up with numbered rows going down and lettered columns going across. At the intersection of each row and column is a cell, which is labeled with that letter and number, such as A4 and B30. If you type 'Donkey' in cell A3 and '=A3' in cell A4, you will end up with 'Donkey' showing twice. In this way, the reference represents the data that is in the cell. In order for text to act as a reference, you must put an '=' in front of it, which signals that the following is a function (in this case the most simple function). If you don't put the '=' in front, the text in A4 will not read "Donkey", but "A3". Going forward, I will refer to the "reference cell," which means the cell that has the data you want to reference in a formula (in the 'Donkey' case, A3 is the reference cell and A4 is the destination cell). To create a reference in Excel, you do not need to type 'A3' in the destination cell, but can instead type '=' in the destination cell and then click on the reference cell - Excel will know what you mean.