If 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
I 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
About 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.
Vinicius 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.
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.
