data

Excel Trick: Using Formulas and Formatting to View Repetitive Data

rawpixel-com-296621-unsplash

With the approval of the good friend and colleague who first showed me, I wanted to share a helpful way to view repetitive data. Let’s assume that you are working with a list of companies and their associated contacts. After ordering the list by Company and scrolling down the list, it is easy to lose track of what you are looking at due to the repetitive nature of the data.

rep data

Try out this mixture of formulas and conditional formatting!

Before you begin, you’ll need a value that can identify each company which is present in each row, in this case I am using Company Name but you could also use Website, or a Company Number if available. You can use the data at the bottom of this post to follow along.

  • In the first row after the last column of the sheet, put a ‘0’. In this example, this will be in column J

0

  • In the cell below, write the following formula =if(A2=A1,J1,J1+1) and press ENTER

form1

  • For the sake of the example, the 0 will be in column J, and column A will store our Company Name
  • Here, we are saying IF the Company Name in this row equals the Company Name above it, return the value in J1, in this case a 0 and if not, return J1 + 1, in this case 0+1
  • Continue the formula all the way down
    • The formula knows to increment the values based on it’s current row’s values
    • The result is that the same company has the same value in Column J and when the Company Name changes, the value increments by one! Cool, huh?

continue

  • Next, we will nest our IF formula inside another, like a formula Inception without Tom Hardy or Leonardo DiCaprio. We will use the MOD function.
    • To do this, click into J1 where the formula resides. Add ‘MOD’ after the = sign followed by an open parenthesis.

MOD pt 1

  • At the end for the formula, add a comma followed by the number ‘2’ and a closed parenthesis

MOD pt 2

  • Here, we are telling excel to divide the number in J by 2 and return the remainder. A 2 will have a remainder of 0 when divided by 2 and a 3 will have a remainder of 1.
  • Drag the formula down the column
  • You’ll notice that each cell in Column J has either a 0 or 1

0 and 1

Great! We have the foundation to put some conditional formatting!

  • Select everything in the sheet and navigate to Conditional Formatting, under the Home Menu.
    • Select ‘New Rule’

Conditional

  • Select ‘Use Formula to Determine Which Cell to Format’

condi2

  • Under the Rule Description, enter the following Formula
    • =$J1=1
  • Select ‘Format’, then ‘Fill’ and select any color

fill

  • Click ‘OK’ and ‘Ok’ again
  • See you data color organized!

Oranize

It may seem like a bunch of steps now, but after some practice you’ll be able to recreate quickly. I still find many cases where this comes in handy!

Example Data:

Company Name Website Address City State Country Postal Code Contact Name Email 0
Intelligence Network Committee http://www.theintelcom.com 654 Dangerzone Iceville TX United States 829918 John Erich john@theintelcom.com 1
Intelligence Network Committee http://www.theintelcom.com 654 Dangerzone Iceville TX United States 829918 Grant Christian grant@theintelcom.com 1
Intelligence Network Committee http://www.theintelcom.com 654 Dangerzone Iceville TX United States 829918 Jeff Gulder jeff@theintelcom.com 1
Intelligence Network Committee http://www.theintelcom.com 654 Dangerzone Iceville TX United States 829918 Brain Burke brain@theintelcom.com 1
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Karen Lyons klyons@thesalezone.com 0
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Jeff Lyons jlyons@thesalezone.com 0
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Sandy Hookshank shookshank@thesalezone.com 0
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Johnny Boy jboy@thesalezone.com 0
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Pupper Doggo pdoggo@thesalezone.com 0
The Mobile Phone Store http://www.mphonestore.com 123 Fake Street Fakevill KY United States 92011 Goldi Sampson goldi@mobilephonestore.com 1
The Mobile Phone Store http://www.mphonestore.com 123 Fake Street Fakevill KY United States 92011 Aaron Sampson aaron.sampson@mobilephonestore.com 1
The Office Gentleman http://www.theofficegentleman.org 555 Example Street Mainville CA United States 99221 Grant Ongstad grant.ongstad@theofficegentleman.org 0
The Office Gentleman http://www.theofficegentleman.org 555 Example Street Mainville CA United States 99221 Sarah Connor sarah.connor@theofficegentleman.org 0
The Office Gentleman http://www.theofficegentleman.org 555 Example Street Mainville CA United States 99221 Maggie May maggie.may@theofficegentleman.org 0
Tim’s Tool Shack http://www.timstoolshack.net 829 Rochester Way New York NY United States 291 Tim Anderson Tim@timstoolshack.com 1
Tom’s Baseball http://www.tomsbaseballstore.com 705 Mainstreet Hoopville IN United States 77266 Tom Johnson tjohnson@tbaseballstore.com 0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s