“Is this formula in cell A10 used for anything?” “Where are these inputs in cell Y54 coming from?” If you ever asked yourself these questions, read on.
1. Tracing Precedents
“Precedent cells are cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.” Microsoft
To easily see which cells are referred to in a particular formula:
- Click on the cell you want to evaluate
- Click on the Formulas -> Formula Auditing -> Trace Precedents
When you click on this, Excel will create blue lines from the cells that make up the formula in the cell back to the cell you’re analyzing.
2. Tracing Dependents
“Dependent cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.” Microsoft
To easily see which cells use a particular formula:
- Click on the cell you want to evaluate
- Click on the Formulas -> Formula Auditing -> Trace Dependents
When you click on this, Excel will create blue lines from the cells that are dependent on the analyzed cell back to the cell you’re analyzing. In the image below we see that cell F2 is dependent on the Payment cell in C5. Trace Dependent is especially helpful when you are trying to clean up a spreadsheet. Many times people will put random calculations to the side or have redundant calculations. You can quickly see which formulas and cells are actually being used and delete the others.
If there are no Dependents or Precedents, the following message will appear:
To remove the blue arrows, click on Remove Arrows:
Excel data dumps are very great at combining lots of individual pieces of good information into one string. Unfortunately, this makes it very frustrating to the end user who then needs to extract that data. What if we could use formulas to do that dirty work for us…
No doubt you’ve received a data dump that includes an employees ID number, last name, first name, date of birth and dog’s name all in one string that looks like this: 589493CoughranNate01011986Fluffy. Good information but useless in its current form. Let’s learn how to extract that data.
=LEFT() allows you to extract a certain number of characters from a string, starting from the left-most character. This is useful when the number of characters will remain constant, like an employee number or zip code. The syntax for the formula is =LEFT(the cell you are extracting data from, number of characters you want to extract) as shown below:
=RIGHT() is the same exact concept except this will extract data starting from the right-most character. I will switch the Raw Data so the employee number is at the end:
One thing to watch out for! Since we are pulling a number from a text, the result will be text, and not a number. In other words, if I tried to sum the employee numbers, the result would be zero (0). If you require the extracted data to be a number, change your formula ever so slightly by multiplying it by 1 as shown below:
This is part II of amazing date formulas where we’ll learn about EDATE() and EOMONTH().
EDATE(): This formula returns a date that is a certain number of months before or after a specific date. This is very useful for when you need to calculate a date that falls on the same day a certain number of months in the future or past. In this example, I have an investment that compounds on the 14th day every three months. In cell C9 is the beginning date that we will increment by 3 months.
Put the formula =EDATE(B9,C5). This is telling Excel to start at 1/14/14 and increment by 3 months. You can now drag the formula down as many rows as needed.
=EOMONTH(): This formula returns the last day of the month. This formula can be used in a similar way as the formula above (except it returns the final day of the month). We’ll use the same example as above, except we’ll use the EOMONTH() function. In cell B9, we’ll put “0” in for Months which returns the current dates end of month.
Now in cell B10, we’ll put a similar formula to the EDATE() formula above. This will return the end of month every 3 months. We can then drag this down.
Enjoy these amazing date functions and never manually type a date again!
Date formulas can become your best friends, if you just take the time to get to know them. The next couple of posts will be dedicated to ‘various Excel date functions and how to use them to optimize your spreadsheet (and maybe even win the heart of your next date).
=TODAY(): Returns the current date. To use this, simply type =TODAY(), hit Enter and Excel will spit out the current date. I use this quite a bit, especially when I’m too lazy to type out the actual date.
=DAYS(): Returns the number of days between two dates. The formula’s syntax is =DAYS([end date], [start date]). For this formula, you must use a cell reference or a date formula. You cannot type “9/1/15” in the formula.
Here is how you use the formula. Let’s say your birthday is in cell C2 and today’s date is in cell C3. In cell C4 type =DAYS(C2,C3) and hit enter.
Or better yet, we just learned how to use the TODAY() formula. You could modify the formula to say =DAYS(C2,TODAY()).
Have you ever looked at a formula and thought, “What in the world does this mean! What items make up this crazy formula?” Or have you ever found your self constantly linking back to a cell and thought, there has to be a better way? If so, you are not alone. That is why named ranges exist! After you use named ranges, you’ll wonder how you ever got through life before.
Microsoft sums up perfectly why you should use named ranges: they make your “formulas much easier to understand and maintain”. Here is how they work…
By default, each cell has a name that corresponds with the column letter and the cell number. To see the cell’s name, look at the top left hand corner of your spreadsheet.
You can change the name of that particular cell to any name you’d like (although you should be somewhat descriptive). In this case, I’ll rename the cell “Age”. To change the name, click in the area where the name appears, type the name and hit Enter.
Now whenever you refer to that cell, you will see “Age” appear in the formula bar instead of the cell reference.
This also means that you can easily refer back to the cell by typing simply typing the name of range. No more going back and forth between spreadsheets to refer to cells!