Are you tired of switching between tabs on your single Excel workbook? Want the ability to calculate backward? Fortunately, Microsoft Excel now has functions to solve these demands.
In our newest addition to the Excel Hacks series, I will be showcasing the “New Window” and “Goal Seek” features.
DEALING WITH MULTIPLE WORKSHEETS USING NEW WINDOW
Purpose
Microsoft Excel’s New Window functionality allows the user to open multiple windows from one workbook.
Why use it?
The New Window functionality is especially useful when creating or reviewing workbooks that contain formulas that are linked between sheets. One scenario would be reviewing an inventory detail with a sheet at Tab 1 that summarizes all inventory found on the subsequent sheets. See the screenshots below.
A nice perk to using New Window is that any changes made in the second window will be reflected in the primary window. Additionally, saving in either window will save any changes to your original workbook.
Limitations
When entering a formula on a cell that has a reference to another sheet, the referenced cells will not be outlined.
Where is it on the ribbon?
New Window in Action
Reference A
Notice: The original window has a “:1” tagged onto the end of the file name while the new window has a “:2” tagged onto the end.
Reference B
Creating a formula in the first window that references a cell in the second window is a simple click away. No more switching between sheets to find the cell you want to reference in your formula. Keep in mind, it is also possible to build a formula in the second window that references back to the original window.
Helpful Tip
If you save with both windows open, the next time you open the file, both windows will be displayed. In order to have only window present when opening the file, you must save with only the primary window open.
GOAL SEEK
Purpose:
Microsoft Excel’s Goal Seek functionality is a type of what-if analysis that can be used to solve for an unknown input given a desired output.
Why use it?
Have you ever tried to figure out how much of a given product you would have to sell in order to break even? Sure, you could set up a mathematical equation to solve for your break-even point either through trial and error or actually solving for X.If only we still had our old algebra notes to remind us how to do this! The other alternative is to have Goal Seek do the hard work for you.
Limitations:
Goal Seek can only solve for one variable changing at a time.
Where is it on the ribbon?
Goal Seek in Action
In the below scenario, we want to know how many engine blocks must be sold in order to break even.
Once Goal Seek is activated, a window will open that allows you to set three required fields.
Set cell: This is your desired output and must be a cell reference. The contents of the referenced cell must contain a formula.
To value: This is the desired value that you would like to see returned in the previously referenced cell. It must be a hardcoded value.
By changing cell: This is your unknown variable that you are solving for. It must be a cell reference. The cell referenced must be a component of the set cell formula referenced above.
To incorporate the fields defined above into our scenario below, we would say that we are setting the total revenue to a value of $13,429.83 by changing the number of units sold.
The final step is to click OK and let Goal Seek do the work for you. Did you get a result of 14.17 units sold? If so, you have now mastered Goal Seek!
Be sure to follow Weinstein Spira on Facebook for new Excel hacks and share the knowledge with your industry friends and peers.
You may also like:
