Excel Hacks for Accountants from Millennial Pros: Volume 4

by | Sep 12, 2018 | Audit, Technology

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.



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.


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.



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.


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:

You may also like:

401(k) Basics: What You Need To Know

For many Americans, saving money for retirement can be challenging. While some employees prefer to take home a few extra dollars rather than participate in their company’s 401(k) plan, many simply do not understand how these plans work or the many benefits that come...

Does Your 401(K) Plan Still Need To Be Audited?

Around this time of year, CFOs and HR professionals often ask me, “Does my 401(k) plan need an audit?” Due to recent changes made by the Department of Labor for the 2023 plan year, many will be very happy to hear that they can avoid an audit for a few more years....

New Year, New Auditing Standards

In October 2021, the American Institute of Certified Public Accountants (AICPA) Auditing Standards Board (ASB) issued Statement on Auditing Standards (SAS) No. 145, Understanding the Entity and Its Environment and Assessing the Risks of Material Misstatement in...

Cybersecurity Trends: What You Need To Know

Cybersecurity is the practice of protecting data, networks, devices and systems from unauthorized access, theft or damage. It is a constantly evolving field that requires businesses and individuals to stay alert and informed of the latest threats and solutions. In...

Are Client Accounting and Advisory Services (CAAS) For You?

Accounting also personally referred to me once as “the necessary evil”, has a rough reputation.  Pocket protectors, ten keys, panty hose, and even maybe an abacus is what people imagine when they think of my profession. Although, not entirely inaccurate, I believe the...

Latest Posts