News & Publications

7 Excel Productivity Hacks You Need to Know

Microsoft Excel is a key software that is especially relied upon by tax professionals who, despite seeing excellent innovations in tax technology in recent years, still rely upon Excel heavily to conduct their duties. This article from Allyn Tax’s business analyst team identifies some helpful tips and tricks you can use when working in Excel.

Advanced Select

This helps you make changes to a particular object in an entire sheet.

Consider the below scenario where you would like to highlight the delayed status as red.

Go to: Find & Select

Click Find.
Type the item that you would like to highlight, Eg: Delayed.
Click Find All.

Press Ctrl + A

Once you see all cells are selected like above, click Close.
Go to Fill Color and click the color you would like to highlight.

This is one of the ways where you could modify the objects like highlight, color, clear, etc. all at once.

Goal Seek

This allows you to determine the input value needed to achieve a particular goal.

Consider the following scenario:

To meet the desired monthly expense, you would look to make adjustments with one of the items. For example, you would like to adjust your monthly shopping expense to meet the desired target here.

Select the dataset -> Go to Data Tools -> What-If -> Goal Seek and enter the following:

Set Cell -> actual output value

To Value -> desired value

By Changing cell -> value you want to adjust/compromise

Click Ok
Excel calculates and adjusts the value for you.

Camera Function

The feature allows you to take a snapshot of any selected range of data, table, or graph, and paste it as a linked picture.

This can be copied and pasted into any excel sheet, dashboard, Word, or PowerPoint as well.

For example,

Select a range of cells for which you want an image.
Click the Camera icon in the Quick Access Toolbar.
Click anywhere on the worksheet.
Congratulations, you have an image of the selected cell range ready to be placed.

If there is any change/update on the originally selected cell range, the data will be automatically reflected in the corresponding image.

Open Multiple Excel Files at Once

Cases where you want to open and work on multiple excel files at the same time, please follow the below steps:

Select all files you like to open.
Press Enter
All files open altogether.

Select Cells that Contain Errors

When you receive raw data from clients, your datasets will have huge chunk of errors which is harder to handle single handedly for further computation.

To tackle this case, you could follow the below process to select and deal with all errors at once.

Click on “Find & Select” -> Go To or Ctrl + G.

Go To Dialog box will appear.
Click Special
In the Formulas section, uncheck all except errors. Click OK.
All cells containing errors gets selected.

Scenario Manager

Let’s say, we want to calculate the profit for our dataset as shown below:

Profit value is dependent on Cost Price & Selling Price calculated as:

Profit % = (S.P – C.P)/C.P * 100

If you have 1 or 2 changing variables and would want to determine and analyze profit % for best or worst scenarios, the “Scenario Manager” feature excel could be very powerful in this case.

On the Data tab, in the Data Tools group -> click What-If Analysis -> Scenario Manager.

In the Edit Scenario box, type the scenario name and enter the changing cells (reference for the cell that contains the value that you want to adjust.)

Click OK.

Excel prompts you to enter the value that you would like to calculate:

Click OK. This creates the Original Scenario for this dataset. You can repeat the steps and create scenarios for the Best and Worst-Case.

Once you have created all scenarios, click Summary. Also, specify result cells where you have the output for this calculation. Click OK.

Scenario Manager runs and produces the result in the next tab:

This is one of the most powerful excel feature to keep track, analyze all types of scenarios with the existing data and make better business decisions.

Tips for The Reader

Review your current processes and excel files and evaluate whether any of your excel steps are ripe for innovation. Implement what you can and keep an eye out for more tips and tricks.

How Can We Help?

Excel is still one of the most used programs in tax and accounting. Use of excel is a key area for reducing friction, cost, and labor in your tax function.

Allyn's tax team is staffed with seasoned tax professionals experienced in all aspects of Federal, multi-state, and local tax compliance and consulting for large US and global corporations. Our analysts use excel every day in their furnishing of tax services and go through several rounds of training to learn how to leverage the program more effectively. We use those skills to make tax procedures as smooth and effortless as possible, which results in lower consulting costs, lower taxes, and a better bottom line.

Contact us and we can provide a customized cost-effective solution to meet your company’s needs. For further information on Allyn Tax services, please contact: tax@allynintl.com.

For More Information

If you are interested in learning more about this topic or other tax topics, please visit our Tax Publications under News & Publications at www.allynintl.com.

Contributor: Ashwitha Shetty

About Allyn International

Allyn International is dedicated to providing high quality, customer centric services and solutions for the global marketplace. Allyn's core products include transportation management, logistics sourcing, freight forwarding, supply chain consulting, tax management and global trade compliance. Allyn clients range from small local businesses to Fortune 500 firms. Allyn conducts business in more than 20 languages and has extensive experience in both developed and emerging markets. Highly trained experts are positioned throughout North America, Europe and Asia and Allyn regional headquarters are strategically located in Fort Myers FL USA, Shanghai P.R. CHINA and Prague, CZECH REPUBLIC. For more information, go to www.allynintl.com.