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