Excel Hacks for Accountants from Millennial Pros: Volume 4

by | Sep 12, 2018 | Technology, Audit

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:

Is ASC 842 Lease Software Worth Utilizing?

Does your company have a significant number of leases? Are you required to apply accounting under Accounting Standards Codification 842, Leases (ASC 842)?  If so, you may want to explore purchasing software to assist with accounting for those leases. Lease accounting...

Picking the Right QuickBooks Version

Deciding which QuickBooks version is a best fit for your business can be a challenge. There are quite a few offerings to meet the needs of a variety of business models and industries. This articleprovides a brief description of each QuickBooks version highlighting...

Your Audited Financial Statements: 8 Steps to Success

Working on the audit side of public accounting for 10+ years, I have performed audits of countless sets of financial statements. There are various reasons that a client may need this service, ranging from fulfilling a periodic requirement in their debt agreements to...

New Year, New You: New Audit, New Report!

Did you know? The American Institute of Certified Public Accountants (AICPA) issued several new auditing standards a few years ago, which are effective for the December 31, 2021 audits. Many of the standards affect what auditors do and how we document our work, so you...

Latest Posts