The boy is now doing a lot of math that involves multiplication, so he’s been reviewing the dreaded multiplication table.
It occurred to me that you could build a quick multiplication table in Excel.
» More: Building a Multiplication Heat Map
Posts Tagged ‘Formulas’
Building a Multiplication Heat Map
May 11th, 2011Changing Font Size of Just the Formula Bar
April 25th, 2011I just got back from a whirlwind week of live training events. The most exciting one was the live Xcelsius training we did in Miami. We had a blast, as we always do at our live events. I was on my game – of course – and everyone walked away with coffers full of new techniques.
.
During my live training events, I have gotten into the habit of using a trick I learned from John (a Senior VP at Ernst &Young) a few months ago.
With this trick, I can increase the font of just the Formula bar. Like this:
Using HLOOKUP in Dashboard Models
January 17th, 2011Debra D. is currently on a mission at the Contextures Blog to cover 30 Excel Functions in 30 days. Quite the feat, considering since she also cranks out a video tutorial with each one. Anyway, the 10th function she covered was HLOOKUP. And as you may have guessed, poor old HLOOKUP didn’t even get one comment. So I’d like to give you my take on why I use the HLOOKUP function is some of my dashboard models.
Safely Move Formulas without Absolute Reference
January 10th, 2011I can’t believe how busy the first ten days of this year have been. I’ve been tucked away in my office trying to finish off a dashboarding project. I’ve finally been able to come up for air and to update the old blog. So here it goes.
.
We all know you can make the formulas in your worksheet “Absolute Reference”. This locks the formulas so you can move them around without changing the references. But you don’t always have the luxury of applying absolute references to all the formulas you need to move – maybe your formulas have mixed references or maybe absolute references don’t fit your model. In these cases, you can use a simple trick that allows you to safely move your formulas around without having to apply absolute references.
.
In this example, I need to sort the formulas in column J so that the time periods are sorted by the sort key. Note that these formulas are relative reference formulas.
If I apply the sort as is, the formula references will get all wacky, causing the formulas to become incorrect.
.
To safely move the formulas, I perform a Find and Replace – replacing all equal (=) signs with the pound or hash (#).
.
This essentially converts the formulas into hard-coded text strings which can be moved around.
.
At this point, I can safely apply my sort.
.
Once I’m done moving things around, I can perform another Find and Replace to find all pound or hash (#) signs and replace them with equal (=) signs.
Emailing from Excel Using the Hyperlink Function
December 9th, 2010Did you know that you can use the HYPERLINK function to create emails from Excel?
It’s true. Today, I’ll show you that the HYPERLINK function is not just for getting to your E-Harmony account.
.
Excel’s HYPERLINK function has only two arguments: the URL you are trying to hit, and the friendly name. So to get to the E-Harmony site, you could use something like:
=HYPERLINK(“http://www.eharmony.com”, “Woman Shopping”)
» More: Emailing from Excel Using the Hyperlink Function
Sorting Numbers and Text Together
November 22nd, 2010Last week, Elaine asked:
“I have project numbers that are both numeric and text which follow a parent and child relationship. The problem is when I sort by project number, all the numbers are sorted at the top, and all the text at the bottom. How do I sort, but keep parent projects with the child projects?”
So Elaine would like to see something like the table shown here – all projects sorted so that the numeric parent stays with the textual children.
» More: Sorting Numbers and Text Together
Testing for Null Cells
October 13th, 2010Yesterday, I saw a spreadsheet that was filled with this type of formula:
=If(A1= “”, 0, A1)
I mean there were hundreds of them. I don’t know why, but these rub me the wrong way. I somehow feel it’s a waste, adding conditional processing where it’s not really needed.
Creating a Zero Centered Scroll Bar
October 12th, 2010Last January, I published a Pulitzer-worthy post about configuring the Scroll Bar control to work with Percentages. In the comments of that post, m-b
shared a technique he uses to create a zero-centered scrollbar. That is, configuring your Scroll Bar so that the middle value is 0, the lowest value is -100% and the highest value is 100%.
Sort by Color in Excel 2003
May 18th, 2010Every 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.
Highlight 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.
