Cool Tips

Has this Excel feature been hiding from you
in plain view?

AutoSum, now fully exposed…

Let’s suppose you have a spreadsheet similar to the one below:

You want to quickly add up the January Sales that occurred for Account 101-4: King
Fair.  There are a number of ways to do this (i.e., write a SUM() formula, perform
subtotals, create a PivotTable), but you just want to be able to look at the screen
and do something very easy to get the answer.

It is called the AutoSum feature.
Select Cells B6 through B9 with your mouse.

Look at the bottom right portion of your screen and you should see a box
that contains the script Sum=32,501.

This part of your screen is called the Status Bar. If you don’t see the Status
Bar or the script Sum=32,501, then click on Tools, Options on the Main
Menu Bar, click on the View tab of the Options dialog box, and make sure
the Status bar check box is checked.

If the AutoSum box reads something other than Sum=32,501
(like Average=8,125), right-click anywhere in the AutoSum box and
choose Sum (or any other option you want).

This tip is courtesy of http://www.excel-black-belt.com, advanced
Excel training specifically designed for accountants and financial
professionals.


Dipping your paintbrush for every stroke?
Well, now you don’t have to.

Locking the Paintbrush, now revealed…

I hope you’ve used the Paint Brush when formatting numbers in Excel. It is
very useful when you want to format a number exactly like another number
in your spreadsheet.

However, what if you have a lot of numbers in different rows and columns
that you wanted formatted the same way? When you click on the Paint Brush
icon and click on one of the cells, the Paint Brush formats ONLY that one
cell then disappears. To use the Paint Brush again, you have to click on it
again.

BUT… if you double-click the Paint Brush icon, you can click on as many
cells as you want and the Paint Brush will still work. To disable Paint Brush
after you double-clicked on it, simply click on it again.

For example, look at the illustration below.

3a

Suppose you want to format all numbers in the spreadsheet just like Cell B2.
Put your cursor on Cell B2 and double-click the Paint Brush icon. Then click
on Cell B3. The formatting of Cell B2 is now ‘painted’ onto Cell B3.

Now click on each of the cells you want to format. There will be a moving dotted
line around Cell B2 to let you know that is the base cell for the formatting.

3b

Another way to use the Paint Brush icon to format entire column(s) all at once is
to select the column you want to use as the base, click (or double-click) the
Paint Brush icon and then select the entire column you want to format.

This tip is courtesy of http://www.excel-black-belt.com, advanced
Excel training specifically designed for accountants and financial
professionals.


Mindlessly re-typing? You shouldn’t be.
Maybe this will help.

How to pull numbers out of text,
now revealed…

Sometimes you will have item numbers or some other type of text string that
has a number embedded in the string and you need to extract that number and
perform some kind of calculation on it.  For example, If you look at the back of
a Hallmark® card or a book you will see a code like IBXMT-0395QR8W. The
numbers in the middle (0395) is the price of the item.

If you want the extract the price from the code, a common approach is to
mindlessly type in the price for each code.  Well, any time you are doing
anything mindless in Excel, you should stop and look for a better way.  And
this is no exception.

Let’s say you are dealing with the following list:

4a

The four numbers in the middle of that string are the sale price of the item. To
extract the sale price is easy — just use a MID() function and copy down:

4b

This pulls the numbers out, but notice that the numbers are not adding up
in the AutoSum (see the Autosum tip above) box. That is because when you
used the MID() function, it brought in the numbers as part of a text string.
You need to let Excel know you want to treat them like numbers, not text.

To convert number text to a mathematical number, simply perform some
type of mathematical function on it. In this example, you can divide the
formula by 100 to get a sale price.

4c

Now the numbers add up in the AutoSum box. You can also use the VALUE()
function to do the same thing, but using a function instead is faster and
easier to remember.

This tip is courtesy of http://www.excel-black-belt.com, advanced
Excel training specifically designed for accountants and financial
professionals.