Simplify your data to better understand your small business

By Grace Frenson

Conditional formatting is one of the cool features of Excel that allows you to analyze your business data with ease. Instead of formatting your data into big pie charts or bar graphs, conditional formatting allows you to highlight the cells and toggle through a variety of simple options to see the options on how the data can be viewed.

Turn your information into simple icons

Sometimes it is hard to wade through a lot of text in a spreadsheet. Turning data into an image enables the user to gain knowledge at a glance, and thus work more efficiently.

Here’s an easy example. You are in charge of keeping track of how much money each employee can spend in a different category each month. Instead of looking at the totals throughout the month, you can assign a total an icon based on a traffic light. A green light means that the employee can keep spending money. A yellow light could say that he or she is approaching the budgeted amount. A red light may indicate that the employee is over budget.

[amazon_link asins=’111907715X’ template=’ProductAd’ store=’succeedingi0d-20′ marketplace=’US’ link_id=’57026a0d-d600-11e8-ab2b-691a91ecbf28′]Using icons to analyze sales

Sales managers can use the icon conditional formatting to analyze whether sales have increased, decreased, or plateaued for each product. Increased sales can be shown with an “up” arrow, decreased sales can be indicated with the “down” arrow,” and arrows facing right and left can indicate stagnant growth.

Using icons to analyze salespeople

By highlighting the sales records of each salesperson in a spreadsheet, a sales manager can see the ranking of each salesperson by clicking on the conditional formatting icon.

Format your spreadsheet using different colors

Conditional formatting also allows businesses to highlight a row of data based on its specific category.

Using color to analyze purchases

For example, if you want to make sure you are purchasing books in a vast number of genres for your bookstore, you can assign each type a color. With a glance, you can see that you only ordered one biography, but you ordered 15 books of poetry. This information can be garnered by glancing at the colors on your spreadsheet.

[amazon_link asins=’1119067510′ template=’ProductAd’ store=’succeedingi0d-20′ marketplace=’US’ link_id=’5d450ad9-d600-11e8-a3cd-035ca27af205′]Using color to determine inventory

The color format option can also work great for keeping track of the inventory of a business. Each week, the restaurant manager spends time in the stock room analyzing inventory and updating her spreadsheet on how many of each item is left in the house. By conditionally formatting the data, she can determine which items need to be reordered at the glance of her spreadsheet.

Using color to take advantage of discounts while ordering supplies

Perhaps the restaurant receives a discount on the purchase of a certain total spent. The restaurant manager can use conditional formatting to determine what other restaurant supplies are purchased from a particular vendor to help determine what to add to the order to enable the business to get the discount.

Using color to track rule violations

Another use of this color formatting option is that it allows the user to determine if a company rule has been violated or not.

Consider the manager who keeps track of expense reports for each employee. Each department head is given a budget at the beginning of each quarter.  He or she turns in receipts for expenses throughout the month, and the manager inputs the costs on a spreadsheet. With the conditional formatting option, he or she can see at a glance which department heads need to have a “talking to” for going over budget.

[amazon_link asins=’0996712100′ template=’ProductAd’ store=’succeedingi0d-20′ marketplace=’US’ link_id=’72960bda-d600-11e8-b11e-a7698df8b2e2′]Pinpointing spreadsheet inconsistencies with color

Some spreadsheets can be long and complicated. It can be difficult to determine if you have accidentally created a duplicate in your long spreadsheets or not. To do a fast check, consider using the conditional formatting tool. One can easily instruct the spreadsheet to ignore the first instance of a particular piece of data in the spreadsheet, but any time that the same label appears in another place on the spreadsheet, it will be highlighted with a specific color. Duplicates in the spreadsheet can then be deleted, and this allows you to make sure your spreadsheet can evaluate data more accurately.

Pinpointing spreadsheet errors with color

Accountants use a tool called cross footing. This process allows them to double check totals by comparing subtotals across rows and columns. If those totals do not match, there could be an error in their data. Accountants can add conditional formatting, which makes the discrepancies challenging to miss.

Conditional formatting can be easily used to help analyze business data efficiently.


Grace Frenson is a freelance writer from Philadelphia. She recently graduated from Drexel University with a bachelor’s degree in businesses administration. While studying for her degree she interned under a notable financial consultant in King of Prussia, PA. She has a passion for finance and writing.

Leave a Reply

The Self-Employment Survival Guide can help you succeed. Learn all about it here.

Self-Employment Survival Guide book cover