More Excel Ideas – Part 2

A couple of posts ago, I outlined some ideas on how you can creatively use Excel to solve some problems or to automate your work. That post took so long to create I decided to break it up into two posts. This one will be the second post.

So, here’s a few more examples…..

Idea 5: Unique Formatting to Focus Attention

We can use conditional formatting in a very unique way to focus attention on what you want to emphasis. Sometimes you may want to focus attention on certain columns (or rows) and you want to fade out the other columns (or rows) because they may not be relevant to the current discussion. Instead of having a lot of colors or boldfacing a lot of cells, here’s a quieter way to directing attention – scroll to the right to see what I mean. There are two tabs in this example – one a simple version and the other a fancy version. The fancy version includes scroll bars so you can change the emphasis live without reformatting everything. I have used the simple version, but I haven’t use the fancy version because I was not doing a presentation in front a bunch of people.

Idea 6: Warning Messages About Formulas

If you often pull in data or copy and paste data from elsewhere into a tab, you might often encounter a situation that you have more rows of data than you have rows of formula doing some kind of special calculation, VLOOKUP or conversion. I often have, off to the side, special formulas for every row of the data to do some kind of work. But you might pull in more rows of data than there are rows of formulas, which could lead to incorrect results, so I create special warning messages to forestall this occurance.

In the example below, go to the “Data tab” and copy down the formulas – down to B23. Then go back to the “Working Sheet” and see what happened.

Idea 7: Automated filtering

There is a project that I sometimes have to do and I always have to check the data because of the way dates are handled. There are different filtering permutations I go through to check the numbers, except there are about 200+ different filtering possibilities. I often forget whether I have already looked at one filtering possibility and I might end up looking a couple of possibilities multiple times. Here’s where a checklist of filters and a macro come into play. The macro uses the checklist to filter a set of data for me to look at and when I’m done, I mark it as done and the macro goes to the next filtering permutation. I don’t have to keep track of which filtering permutation I have checked because the macro does it for me.

Now I won’t provide an example macro because it is large and would take too long to create a dummy version.

[Okay, I’m getting tired so I’m going to quickly wrap up.]

But as you can see, there are so many creative ways of using Excel (or whatever other application you may be using – it doesn’t have to be spreadsheets) beyond simple addition and subtraction to help you do your work. You just have to stretch your mental boundaries. The best way I know of is to do some reading and do a lot of experimenting and playing around.