Every now and then, you encounter a table where color is supposed to mean something. In this table, each color represents a certain attribute (red = below target, yellow = in danger). If you're thinking that tagging records with a color is stupid, I agree. But we live in a world full of goofballs that think it's clever to do things like this.
Posts Tagged ‘Formulas’
Sort by Color in Excel 2003
May 18th, 2010Highlight Named Ranges Part 2
April 6th, 2010Last week, I posted 'Highlight All Named Ranges Part 1', where I shared a macro that will literally color all the cells that belong to any Named Range yellow.
This allows you to get a visual indication of how many Named Ranges exist and where they are.
Highlight All Named Ranges Part 1
April 1st, 2010If you spend your time auditing other people's worksheets, you'll know that Excel users love their named ranges. I've run across some spreadsheets where 50 or more individual cells are given individual names. This makes auditing a spreadsheet an extremely muddy experience.
It sometimes helps to know where the named ranges are.
» More: Highlight All Named Ranges Part 1
Formula Auditing Art
March 31st, 2010I was auditing a spreadsheet the other day and I noticed that when I used the formula auditing tools to trace dependents, a pattern came up that looked like a dog. I know…too much silly juice for me.
So anyway, I thought it would be cool to purposely write formulas so when you applied formula auditing, it made a picture.
Like this:
» More: Formula Auditing Art
Getting Data from Previous Sheet
March 29th, 2010About a week ago, a client of mine asked me if I could write him a function to pull data from the previous worksheet. Upon further interrogation, I found out that he has a workbook where he manages his monthly budget. Each tab consists of a month's data. The first tab is named January, the second tab is named February, etc.
What he needed was a function that would pull data from the previous month's sheet without having to specify the sheet name. Why? Because at the end of each month, he would add another sheet and he did want to re-jigger his formulas to change sheet names.
Why FIND when you can SEARCH
March 15th, 2010Vinicius writes to ask:
"Dear Mr. Pig. I would like to know what the difference is between Excel's FIND and SEARCH. Which one should I use?"
I love the respect Vinicius shows by calling me Mr. Pig. Anyway, here is your answer Mr. Vinicius:
There are two differences between Excel's SEARCH and FIND functions.
A Color is Not Data
February 25th, 2010Ok 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.
Excel Defunct Defaults
February 17th, 2010A 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
Paste Special Skip Blanks
January 28th, 2010Today, 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.
Flipping a Range Upside Down
December 9th, 2009I'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.
