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.

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:

You may also like:

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...

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...

Latest Posts