Ok people. Repeat after me – "A color is not data".
Every so often I get a spreadsheet from someone who thinks that colors play a major role in data management. You know – those people who happily say "I tagged those records with Yellow". Oh really? I don't think my version of Excel has a "Yellow" data type.
A week ago, Dick Kusleika posted an excellent article outlining some Formula Tips. I've already made plans to steal and use his ideas in my training. But my plagiaristic habits are not the reason for this post.
?
.
» More: Excel Defunct Defaults
Today, I want to show you how to use the Skip Blanks option in the Paste Special dialog box. You've all probably seen it there, but you've never used it.
Here it is in the expertly placed square box.
I'm no Tiger Woods, but I get my share of lady fan mail. Today, I get an email from Dawn.
She asks: "Do you have an easy way to flip a range so that the bottom row is at the top and the top row is at the bottom?"
I have two methods for doing this.
Nothing makes you feel like more of a monkey than manually adjusting formulas while, all along, you have the sense that you’re doing it the hard way. This is the feeling you are likely to get when manually adjusting VLOOKUP formulas.
I'm fresh back from the Excel Power Analyst Boot Camp we had here in Dallas. If you were there, thanks for all the great tips – I had lots of fun. If you weren't there, you missed quite the event. The Spice Girls showed up to talk about their new movie: Spice Racks. It was awesome. Lesson learned eh? Next time you'll join us.
One of the topics that kept surfacing at last week's event was the use of Tables/Lists. These are auto-expanding ranges that allow anything built on top of them to keep up with your data changes (similar to the dynamic named ranges you can create by using OFFSET and COUNTA, but much simpler). Today, I'll show you how to employ Tables/Lists to protect your VLOOKUPS from breaking when you add data to your lookup range.
I love using the Switch function in Access. It's a real timesaver in situations where complex nested IF functions are needed. Ever spend 10 minutes trying to get a nested IF formula right?
The Switch function allows for a easier method, evaluating a list of test expressions and returning the result for the first test that evaluates to true.
The basic syntax for the Switch function is: Switch2(Test1, Result1, Test2, Result2, etc…)
Here's an example.
Ahh…data types; Currency, Number, Percent. What a joy to have such a diverse set of numerical variations designed to make your life hell.
Anyone who has had to handle varying numerical formats knows how tedious it can be to build a reporting structure that can handle all formats.
If you've ever built one chart for Percents, one for Currencies, and one for Numbers, then you're reading the right blog post.
Today, I'll to show you a technique that allows one chart to accurately show any data type.
In the another display of passive aggressive behavior, I want to show you a trick you can use to prevent anyone from adding or deleting rows or columns by using a simple array formula.
I'm pretty sure I got this trick from Bob Umlas. Most Bob tricks, they are extremely cool, but I never seem to find situations where I can use them. But, maybe this trick will hit the spot for some of you.
If you want to commit Excel heresy, tell people you don’t like Array formulas. Oh the blasphemy – the sacrilege!
The truth is I really do try to avoid using array formulas when there is a reasonable non-array alternative. The long-and-short of it is that I think array formulas are difficult to understand and too easy to break.
I’m man enough to admit that at least part of the reason I avoid array formulas is because I’m not smart enough to understand all of the array formulas I see. In fact, I have to wrap duct tape around my head before looking at some of the fancy array creations I see other gurus employ.
