Tag: Excel Tips

Excel Tips

Shortest Excel Tip Ever: <F4> and <Ctrl>-Y

Posted on by Eric Peterson

I’ll put my standard big, fat, hairy disclaimer here that this blog is not about Excel tips. There are lots of resources for that. As a matter of fact, the Contextures blog is one that I stumbled across after Debra commented on my last Excel tip.

Nevertheless, here’s a handy one that requires no customization of Excel, but that I guarantee you’ll be hooked on if you start using it: <F4>

<F4> and <Ctrl>-Y do the same thing, actually, and they work in MS Word, too. What do they do? Pretty simple:

Repeat the Last Action Taken

That’s it. If you’ve just formatted a cell or set of cells with a new border and background color, then you can click on a cell and press <F4> and it’ll apply the same formatting to the new cell. And then do it again! In this case, it does the same thing as the Format Painter…but does it faster (with limitations, as described below).

If you’ve just inserted a row and you want to insert another row lower down on the spreadsheet, highlight the next row and press <F4>.

As you can imagine (if you stop and try to imagine it…and I recognize it’s got to be a pretty bleak day of creativity for this to bubble up as worthy of your imagination), this is particularly handy when doing some oddball work on non-contiguous cells.

This is handier than you might think. And, it does have it’s limitations. The main one is that it only repeats the immediately preceding action. In the Format Painter example above, <F4> is no use if you have a cell already formatted as you want and you want to copy that format to other cells. That’s what the Format Painter is for.

And, another limitation is that it doesn’t work with every possible action. For instance, if you type a value into a cell and then want that same value in another cell…<F4> doesn’t work. You’ll have to copy and paste. It becomes pretty intuitive in a hurry as to where it works and where it doesn’t.

Happy repetition!

0
Excel Tips

Quick Excel Tip: The FASTEST Way to Sum Numbers

Posted on by Eric Peterson

Categorize this as the most tactical of Excel posts ever. But, doggonit, it sometimes amazes me how many really, really, really handy features of Excel most people don’t even know exist. Even in Excel 2003. This is one that I showed to a co-worker several weeks back as I was looking over her shoulder at a spreadsheet. It’s worth sharing.

Question: What is the fastest way to get the sum of a small range of cells in Excel?

If you immediately think of something along the lines of: “Click on an empty cell, hit the sigma icon, and then highlight the range of cells you want to sum and press <Enter>,” then this tip is for you.

The approach I just described is great if you actually want to keep the sum in the spreadsheet as a permanent calculation. But, what if you are on the phone and discussing the data with somebody who asks, “What was the total from July through October?” You don’t already have that calculated, and you don’t really want to keep that calculation on the spreadsheet. The approach above would work. But there is a better way.

The tip put really, really simply: Highlight the cells you want totaled and look at the bottom right of your screen. They’re totaled for you there (assuming you haven’t gone in and turned off the Status bar under the View menu, and, let’s face it, if you were that desperate for screen real estate, you really should be out shopping for a larger monitor rather than tooling around the internet reading blogs).

Now, With Pictures…

You’ve got a table with numbers in it:

(The formatting of this table offends my data presentation sensibilities in many, many ways, but it’s the default way that many people format tables, and I don’t want to detract from the core of this tip.)

Let’s say you want to get the sum for January through April. Highlight the data you want to total:

Look down in the bottom righthand corner of Excel (if you don’t see this, select View»Status Bar):

Lookie there! Sum=54,200. Chances are, you never noticed that, and yet it’s been industriously summing away every time you’ve highlighted a range of cells in Excel for years!

“That’s all well and good, Gilligan, but what if I want to see the average for these four months?”

Well, that’s easy, too. Just right-click on the Sum= area on the status bar, and you will get a menu that lets you pick what you want math function that box should perform.

Change it to Average, and, henceforth and forthwith (until you change it to something else), that area will show the average of any set of cells you select.

Pretty handy. And, of course, you don’t have to drag a contiguous set of cells. You can hold down <Ctrl> and select multiple non-contiguous cells (e.g., “What was the total for Jan-07 and Jan-08?”).

Be honest. Did you already know that?

0