Excel Fun - Data Grouping and Color Rules

avatar

Hello fellow Hiveians and Excel mavens,

I've been doing so much work in Excel lately, my head is spinning. I've definitely been loving it though, as it's an awesome program that's very powerful! It has loads of excellent tools and more I learn about almost each week.

One of the things I've been doing a hell of a lot lately is gathering data during this whole pandemic scenario we are living in. There is a huge demand for data requests from interested parties and I'm more than happy to supply all of it!

In doing this though, I've had a couple of things pop up where I was thinking "hmm how can I improve that, or make that easier?" I love being in that scenario where I'm curious and thirsty to learn something!

So I've got a huge raw data file filled with all kinds of measures and metrics but the people I work with want that cleaned up and much easier to look at. How can I do that? Well, grouping data and adding colors are very easy ways to perform these actions!

Below is a small chunk of very finessed data but it gets the point across of what I'm looking at doing.

excel 2.JPG

So I have a set of values in column A that are mostly unique but not always and then I have dates. Column B is schedule date and column C is cancelled date, if it exists. Alright so that I had an understanding, I needed to kind of just visually orient myself to the data and what I was looking to accomplish based on the feedback.

My plans were: how do I highlight potential duplicates in column A, highlight specific dates in column B as well as column C to make it much easier to understand? Thankfully "Conditional Formatting" was an incredible tool to have available to me!

excel 3.jpg

So now that I had a plan of what I was trying to do, it was now coming up with a way to do what I needed. So I had thankfully already known about highlighting duplicate values which was nice, so I immediately did that in column A. What I wasn't yet familiar with, but am very familiar with now, was the "Between" function. This was key with what I was trying to elucidate!

excel 4.jpg

Now that I had a way to highlight the information, I needed to give it a try. For the first column, I highlighted duplicates, which was easy. Next came the Between function and I highlighted column B and clicked Between to launch it and it was thankfully pretty straight forward.

excel 5.JPG

I wanted the highlight colors to be a bit unique so that unless color blind, it's visually easy to determine which values are which. It was also important to click on the column I am working in first so that it didn't apply the same thing to the other column. That wasn't what I wanted!

excel 6.JPG

That was an awesome thing to learn, given that the files I was working with had thousands of data points in them. Anything you can do to improve the process for the end user, the better! Visual differences like that really worked out, they were quite pleased with the result. Score for me! Below is a completed example of what it ended up looking like. Red for duplicate in column A, green for B and yellow for C. As you can see the colors were potentially all over the place but they did match up with each row. Some had multiple entries and this was the case across the whole file.

excel 1.JPG

Now for the next thing I learned, grouping and subtotals. This was important because there was a difference in the gross total of values in column A. It had like 6,000 of them but not all of them were unique.

excel 7.jpg

The subtotal function lets you tell excel to look at all of the values in a particular column, ideally text values in some scenarios, numerical in others. I was interested in text. I had the formula set up so that it would subtotal at the bottom of the unique value.

excel 8.JPG

Just like in the highlight rule, you have to select the range of data first before you can accurately subtotal it. It’s also important to have a header of some kind so that excel understands it’s a header versus another entry. You can also specify what mathematical formula it will use. I haven’t looked into the others yet but I wanted count, which is what it was set to.

excel 9.JPG

Once the process is completed (sometimes depending on the size of your data this may take a while, my other ones took 30 seconds to process roughly, pretty intensive) you then have groupings and breaks at each unique value. This let’s you then have a count in the grand total section to ensure the numbers are right. The best part for me though is that it expressed the count of unique entries. This was important to differentiate, the unique number was about 1,500 lower than the aggregated total. That makes a big difference!

Excel 10.JPG

In the bottom right of the sheet if you highlight the subtotal column, it will tell you the count of unique values. This is the easy place to see the count which was really convenient to just select the column from the top and check the count on the right.

excel 11.JPG

Hope you enjoyed my excel fun I’ve had! I’ve been trying to do some other components of it as well but this is what I’ve been using the most heavily right now. Figured sharing the benefits would be good for anyone, even excel whizzes like my wife! She’s the one that showed me the grouping by subtotal. Awesome stuff!

Connect with me!
Did you enjoy this post or my other posts? Please consider following me for more! @cmplxty

Do you want to get paid, in crypto, for searching the internet? Try using and signing up for Presearch to earn some great crypto! I've currently got 1,278 PRE tokens, with a market value of $20.36 It doesn't sound like a lot but when you search using sites like Google you get paid $0! Join Presearch to break Google's stranglehold on the internet searches. If you'd like to sign up, use my referral link below and spread the word!
https://www.presearch.org/signup?rid=513043


peak.JPG

Still using hive.blog? Upgrade! PeakD is a much better user-interface, gives loads more information at your fingertips and has some of the best developers behind it. Give it a try, go to www.peakd.com and see how much easier it is. You just need to use Hivesigner or Peaklock some of the most secure ways to log into other Hive frontends.



0
0
0.000
16 comments
avatar

I report using Excel and have designed all my own reports but I keep it quite simple. Auto sum and formulas is about as complex as it gets for me but I get by and the Directors don't complain. I also use Excel at home for th budget and holiday planning, costs and schedules. It's a nice little program, but far more powerful than I'll ever use.

0
0
0.000
avatar
(Edited)

Yeah there is definitely a lot of Excel components that are insanely advanced. Paula knows a lot of it, being an accountant, and it's way beyond my league but one of the really cool things I learned that helps a lot is Power Query. Depending on what you do with your data, it could be pretty badass and beneficial.

We just recently used it for our taxes, we started our own little business and were tracking expenses. That's where my wife introduced me to subtotal and grouping. Blew my mind! She has a lot of excel tricks up her sleeve! Those accountants, (wife is one as well, Masters degree in Tax lol) they know the stuff in and out!

0
0
0.000
avatar

I only use it as needed and it's a fairly basic level to be honest, nothing like you guys would be doing. I get by and whilst I'd like to know more, there's other things I'd rather learn than excel...Maybe one day, after I've learned the other billion things I want to learn.

0
0
0.000
avatar

Yeah I hear you. Until I needed to use it, I kind of made fun of my wife for being so excited learning something on it. Once you use it for many different things, it becomes pretty awesome. I want to eventually data analytics type of work so that’s another reason I’ve been using it so much and learning whatever I can.

There are so many other things I would like to learn as well lol lots of home improvement stuff at the moment.

0
0
0.000
avatar

Haha, teasing the Excel geek huh? Nice one! Lol.

I have seen you're doing a few DIY projects lately. I like seeing them...Will curate that over almost anything else really, as it shows passion and effort. Me likey.

0
0
0.000
avatar
(Edited)

Thanks! One of the things we realized recently..... don't paint stair treads! With painting them last year, we are now stuck with it. The paint will not come off without spending hours of effort to strip off a single tread. Live and learn, but that's the beauty (or pain?) of home improvement I guess lol

Next place we will definitely be staining them, not painting lol

0
0
0.000
avatar

Are the painted steps slippery or you just don't like the look now?

0
0
0.000
avatar

They thankfully aren’t slippery but a very poor choice of color. Walking on these in the dark is treacherous lol

f9pn0l.jpg

0
0
0.000
avatar

Oh yeah, dark and looks like a satin, or matt, finish. Not a good combination as they won't reflect much light. Live and learn though.

0
0
0.000
avatar

great post, nice work on the conditional formatting to show duplicates, it's a nifty trick.

0
0
0.000
avatar

Thanks! I loved learning the conditional formatting. It is definitely useful, especially to visually narrow down what you're looking at. Whatever we can do on the technical side to improve the managerial decisions, the better I think.

0
0
0.000
avatar

This one is quite complex for me. If you are aware of Ms. Sharyn Baines. She teaches excel online. I have downloaded some of her pdf files regarding learning more about the power of Excel.

0
0
0.000
avatar

Very interesting to know this educational👏👏🙇

0
0
0.000
avatar

Thanks! It’s a lot of fun for me to do and learn this, as well as share it with people!

0
0
0.000