Have you ever wondered, “I wonder what X needs to be in order for Y to be 50%?” Good news! Excel thought of that as well! If you know what a certain results needs to be (i.e. profit margin needs to be 35%) but don’t know what input value (i.e. Sales) will get you there, then use Goal Seek to find it for you. It sure beats trying to guessing 100 iterations in order get there yourself. Here is how it works.
You will notice on my income statement below that my profit margin (Net Income/Sales) is 26%.
I now want to know what sales need to be in order for my profit margin to be 35% (holding everything else constant). On the Data tab, you will see What-If Analysis. Click on that and select Goal seek.
A box will now appear. In our example we are going to Set Cell C12 (our profit margin), To Value 35%, By Changing Cell C3 (our sales).
Note that the cell you choose for By changing cell needs to be a hard coded value (no formulas). After I press OK, I see that I need to have sales of $623,049 to get to a profit margin of 35%. Pretty neat!
After you show your boss Goal Seek, you are sure to get a promotion, pay raise, and have all of your hopes and dreams fulfilled. Stay tuned for our next Excel tip!
Now that you have installed the Analysis Toolpak, let’s get down to analyzing your data!
In Column A and B we have the weekly closing stock price of the Dow Jones from 1/1/13 to 6/30/13.
If you wanted to statistically analyze the data, you could go through and manually enter all of the formulas. But who has time for that?! Instead, lets use our new-found friend, Analysis Toolpak.
1. Select all of the data in Column B. It does not matter if you include the heading. Go to the the Data tab and to the far right you will see Data Analysis. Click on that icon.
2. A menu will appear. Select Descriptive Statistics and OK.ana
3. The next form that appears should already be partially populated with the Input Range. Make sure if you included the heading that you click the “Label in first row”. The output is about 2 columns wide and 14 rows. Also, be sure to select the “Summary statistics” box. Once you have made all of the changes, click OK.
4. You are all done! Now all of your statistics are in a single place and all you had to do was click a few buttons. Now the big decision…what to do with all of your extra time…
Let’s say you have 3,000 rows of data, for instance, historical stock prices. You want to quickly see the following information: average, standard deviation, confidence level, minimum, and maximum
You could create a separate area on your spreadsheet and manually input all of this information…OR you could have Excel automatically do it for you!
In this 2 part tutorial, we’ll teach you how to get going on this tool. The first part will teach you how to put the Analysis ToolPak on Excel. The second part will teach you how to use it.
1) Go to File on the Ribbon and click on Options at the bottom of the left hand column.
2) A menu will pop up. One the left hand column, select Add-Ins
3) At the bottom-center of the page, select Excel Add-ins from the drop down menu and click Go
4) Once the next menu appears, click the Analysis ToolPak option and click OK
Your new favorite statistics toy is now conveniently located on your Data tab on the far right side!
Now that you have the tool loaded, tune in next week where we will teach you some of the cool items you can do with the Analysis ToolPak!
Imagine your boss comes in to your office one day and gives you a giant spreadsheet that goes on for pages and pages and pages. He wants you to highlight every single cell that contains word “NO” and says it is a top priority. There are two ways you can do this.
If you prefer to do things the tedious and time-consuming way, you could go through every single sheet and cell in the entire workbook and highlight each and every cell. OR if you like to be quick and efficient, you can use Excel’s powerful Conditional Formatting tool. If that way sounds appealing to you, read on! We will explain how to make this daunting and formidable task as easy as 1, 2, 3!
1. Select the cells where you want the formatting applied. On the Home tab, click the Conditional Formatting icon and select “New Rule.”
2.Select the option “Format only cells that contain”. In the first drop down menu, select Specific Text (or Cell Value, etc). In the third input box, type “No”.
3. Select the custom format you want by clicking the Format… button. We selected the font color to be red but you can format it anyway you like. Hit OK.
And there you have it! What could have taken you hours and hours now literally takes you minutes!
There are many other great ways to format conditionally. Be adventurous and play around with some of the other options and you will be impressing your boss and getting that promotion you deserve in no time!
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!
In Excel, just as in life, sometimes you want to set your data apart by highlighting every other row a certain color. You can do it manually, but that would be silly. Keep reading to see how you can perform this task in less than 15 seconds.
Let’s say you have the following set of data.
Highlight the area where you want to apply the formatting (A2:C9) and go to Conditional Formatting on the Home tab and select New Rule.
Under “Select a Rule Type:” click “Use a formula to determine which cells to format”. Type in the following formula: =MOD(ROW(),2)=1
Click the Format button, go to the Fill tab, choose your color you want to use as the highlight and click Ok
Next hit Ok on the New Format menu and BOOM! You have mastered the art of highlighting every other row. Now, go show this to your kids and see how amazing they think you are.