Formulas and functions are great features in Excel, that can make your life a lot easier. But if you don’t know some of the essentials, they can be really frustrating. Here are a five things I think everyone should know about formulas and functions:
- When you enter a number over 999 in a formula or function, never put a thousands separator comma in the number. In a function, it will move you on to the next argument prematurely. In a formula, you will get an error.
- A formula cannot reference itself. This is called a circular reference. (Using iterations is an exception; search the Excel Help files or read here for more on this advanced topic). If you want to do a calculation on a value in A1, you cannot enter the formula in A1. You will have to enter it in another cell. Learn more.
- Copy formulas down a column or across a row using the fill handle.*
- Use cell references instead of constants in a formula whenever possible. This way, if you update the cell, the formula will update automatically.*
- Use an absolute cell reference when you are going to copy a formula somewhere else on your worksheet, and don’t want the cell reference to change relative to where you pasted the formula.*
* I’ve made a video explaining the last three tips. Have a look!
Can you think of another essential? Leave it in the comments.
PLS ADV THE EASY WAY TO CONVERT US STATES INTO
JUST ABBREVIATION ON A SPREADSHEET.
NEW YORK = NY
Hi Lissett,
I think the best way is to get a list of the states and abbreviations (in two separate columns) in your worksheet and then use a VLOOKUP function to get the appropriate abbreviation.
Here’s a video on lookup functions – http://www.mccombs.utexas.edu/tech/training/tutorials/Excel/files/Lookup_functions/Lookup_functions.htm.
I think you can get a list of states and abbreviations off the post office’s web site.
Hope this helps!