Excel Hacks for Accountants from Millennial Pros: Volume 6

by | Nov 7, 2018 | Audit, Technology

If you deal with Excel regularly, then you are probably searching for a quick and easy way to add immediate visual clarity to your spreadsheets. Many Excel files include large amounts of data which are not arranged or presented in any useful graphic fashion.

The conditional formatting tool assists you to read the Excel workbook, identify key items and present to others in a manner that few other methods can do so effectively and easily.

Purpose

Microsoft Excel’s Conditional Formatting feature allows the user to highlight, color, and apply other formatting preferences to a group of cells, based on certain criteria. It immediately transforms a jumble of data into useful information in a visual and aesthetically pleasing manner.

Why use it?

The image of the highlighted and formatted cells adds instantaneous clarity for both the user and the viewers. You can identify outliers and problem areas quickly.

Limitations

The Conditional Formatting feature is only as helpful and intuitive as the user, who must input all required criteria to maximize the functionality of the feature. The text amidst numbers may be formatted awkwardly.  

Example and Steps to Follow

You’ve just started a new job at Food Mood, a small local grocery. The owner voices how he is losing money due to inventory being wasted and thrown out as a result of overstocking and expirations. You are tasked with managing the logistics of ordering and stocking food.

Fortunately, you’ve got great data collected for you by the previous accounting professional. Here are their raw numbers compiled for you.

Utilizing some basic formulas, you create the following, which translates the raw data into projected dollar-values for food which will expire next Monday.

Now to identify the key problem items. If you’re thinking a simple sort will provide the answer, then you are correct in this instance. Sorting column G largest-to-smallest will identify the biggest waste dollars.

But, sorting is not always the best solution. Let’s bring in conditional formatting!

Highlight the cells upon which you want to apply the formatting (Column G). Click the circled image above and a drop down with a wide variety of potential criteria at your arsenal will appear. In this case, the owner only wants to investigate wastes above $100 per shelf life. Hover over “highlight cells” and input that as your criteria. Select “greater than”, and then, put in the amount. The data automatically formats to show you the result.

You have pressed OK, and you’re still looking for more clarity. For some items, our vendors can only provide the shipments in bulk, such that we cannot tailor the exact order amount we want. Since the sales are always increasing, we do want at least some bulk. That leads you to the next step:

2. Some products are overstocked by more than double their lives. That’s a problem! Which items have less than double their lives’ on-hand quantities? Select the cells, modify your criteria to be less than, and choose a new “happier” color which indicates these are not problem items:

Done! One more problem to go.

The owner has indicated that too much variety in this small store is leading to logistics confusion and adding to waste. As such, he wants to get rid of all products for which sales are less than 50 units per shelf life.

Our picture is beginning to look like art. Let’s use a different formatting type. The “custom format” will open up additional endless opportunities with which we can express certain cells’ values.

Through applying filters by cell style, you have come up with the only major problem items based on all the information above.

Additional items to keep in mind:

  • Formats can be set on top of other formats to achieve layered conditional formatting.
  • Clearing formats can be done with “clear rules from cells” found in the initial drop-conditional formatting down menu.
  • In this example, we only explored the highlight cells feature within conditional formatting. Play around in the initial drop-down menu for the other useful tools!

Taking it to the next level:

Once you have experimented with this functionality, you will discover that Excel’s capabilities with this feature are wide-reaching. Cells can be formatted based on formulas, based on other cells, based on text and dates, and many more. As always, there are multiple methods by which to accomplish your desired result in Excel.

Have fun with it and keep expanding your Excel prowess!

You may also like:

You may also like:

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

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